Goldengate visão geral e dicas de configurações padrão

Overview

When to Use

* Transaction load is consistent
* Transaction load is moderate and spread evenly among objects to be replicated
NO tables that are
– subject to long running transactions
– large number of columns that change
– columns for which GoldenGate must fetch from the database (e.g. LOBs)

Setup Overview

* Source DB -> Extract Process -> Network -> RMTTrail -> Replicat Process -> Target DB
* Both source and target databases are: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
* Source DB resides on a Windows XP box
* Target DB resides on a Solaris 10 box
* GoldenGate version: Version 10.4.0.19 Build 002

Prepare Databases

* See this post on how to setup databases for GoldenGate replications.

Setup Source Box

Prepare Test User and Table

* Create test user in source db from dba account:

 
-- Create src user
CREATE USER GG_SRC IDENTIFIED BY password;

GRANT connect,resource TO GG_SRC;
GRANT SELECT any dictionary, SELECT any TABLE TO GG_SRC;
GRANT CREATE TABLE TO GG_SRC;
GRANT flashback any TABLE TO GG_SRC;

* Login as GG_SRC user and create test table

 
-- Create src table
CREATE TABLE GG_SRC.GG_TEST
(
ID VARCHAR2(20) NOT NULL,
NAME VARCHAR2(20)
, CONSTRAINT GG_TEST_PK PRIMARY KEY
(
ID
)
ENABLE
);

* Enable trandata for gg_test table:
From GGSCI:

 
-- Login source schema
DBLOGIN USERID GG_SRC, PASSWORD password
-- Enable trandata for gg_test
ADD TRANDATA gg_src.gg_test

Generate Source Table Definition File

* No need for source table definition if source and target tables are identical. Use ASSUMETARGETDEFS in replica parameter file instead.
* Create a parameter file named gg_test.prm in dirprm directory or invoke edit params gg_test command from GGSCI:

 
-- Specify definition file name
DEFSFILE C:\goldengate\dirdef\gg_test.def
USERID gg_src, PASSWORD password
TABLE gg_src.*;

* Generate definition file

 
-- Run dos command from GoldenGate installation directory
defgen paramfile dirprm/gg_test.prm
-- gg_test.def generated in dirdef directory

* Copy generated definition file to target box.

Setup Extract

* Add an extract group named e_stdrpt and assign it a remote trail. Note that remote trail refers to target machine’s path.

 
ADD EXTRACT e_stdrpt, TRANLOG, BEGIN now
ADD RMTTRAIL /opt/oracle/goldengate/dirdat/stdrpt/tx, EXTRACT e_stdrpt, MEGABYTES 20

* Create or edit extract parameter file for e_stdrpt: dirprm/e_stdrpt.prm

 
-- Identify the Extract group:
EXTRACT e_stdrpt

-- Specify database login information as needed for the database:
SETENV (ORACLE_HOME = C:/prog/oracle/product/10.2.0/db_1)
SETENV (ORACLE_SID = orcl)

USERID GG_SRC, PASSWORD password

-- Specify the name or IP address of the target system:
RMTHOST calrissian, MGRPORT 7809

-- Specify the remote trail on the target system:
RMTTRAIL /opt/oracle/goldengate/dirdat/stdrpt/tx

-- Specify tables to be captured:
TABLE GG_SRC.GG_TEST;

* Create directory /opt/oracle/goldengate/dirdat/stdrpt on target box
* Start e_stdrpt from GGSCI:

 
start extract e_stdrpt

* Check that remote trail files are created in the target box.

 
bash-3.2$ ls -ltr /opt/oracle/goldengate/dirdat/stdrpt/
total 10
-rw-rw-rw-   1 oracle   dba         2487 Apr 14 19:56 tx000000

Setup Remote Box

Setup Target Database

* Create test user in target db from a DBA account:

 
-- Create target user
CREATE USER GG_TGT IDENTIFIED BY "password";

GRANT connect,resource TO GG_TGT;
GRANT SELECT any dictionary, SELECT any TABLE TO GG_TGT;
GRANT CREATE TABLE TO GG_TGT;
GRANT flashback any TABLE TO GG_TGT;

* Login as GG_TGT user and create test table to receive replication:

 
-- Create target table
CREATE TABLE GG_TGT.GG_TEST
(
ID VARCHAR2(20) NOT NULL,
NAME VARCHAR2(20)
, CONSTRAINT GG_TEST_PK PRIMARY KEY
(
ID
)
ENABLE
);

Setup Remote GoldenGate Instance

* Add PURGEOLDEXTRACTS to dirprm/mgr.prm file

 
PORT 7809

PURGEOLDEXTRACTS /opt/oracle/goldengate/dirdat/stdrpt/tx

Setup Replicat Checkpoint Table

* Create a Replicat checkpoint table if not already done so:
* Add to GLOBALS file in the GoldenGate root directory

 
CHECKPOINTTABLE GG_TGT.GG_CHKPT

* Point ORACLE_SID env variable to correct instance:

 
export ORACLE_SID=ggdb

* From GGSCI run:

 
DBLOGIN USERID GG_TGT, PASSWORD password

ADD CHECKPOINTTABLE

* Output from GGSCI

 
GGSCI () 1> DBLOGIN USERID GG_TGT, PASSWORD password
Successfully logged into database.

GGSCI () 2> ADD CHECKPOINTTABLE

No checkpoint table specified, using GLOBALS specification (GG_TGT.GG_CHKPT)...

Successfully created checkpoint table GG_TGT.GG_CHKPT.

Setup Replica

* Add a Replicat group named r_stdprt and point to trail file as defined in e_stdrpot parameter file:

 
ADD REPLICAT r_stdrpt, EXTTRAIL /opt/oracle/goldengate/dirdat/stdrpt/tx, BEGIN now

* Edit r_stdrpt parameter file

 
-- Identify the Replicat group:
REPLICAT r_stdrpt

-- State whether or not source and target definitions are identical:
-- Use ASSUMETARGETDEFS if source and target tables are identical
-- ASSUMETARGETDEFS 

-- Use SOURCEDEFS otherwise.Definition file was copied from source box
SOURCEDEFS /opt/oracle/goldengate/dirdef/gg_test.def 

-- Specify database login information as needed for the database:
SETENV (ORACLE_HOME = /opt/oracle/product/10.2)
SETENV (ORACLE_SID = ggdb)

USERID GG_TGT, PASSWORD password

-- Specify error handling rules:
-- REPERROR (DEFAULT, ABEND)
-- REPERROR (-1, IGNORE)

-- Specify discard file for troubleshooting
discardfile ./dirrpt/stdrpt.dsc, append, megabytes 20

-- Specify tables to be replicated
MAP GG_SRC.GG_TEST, TARGET GG_TGT.GG_TEST;

* Start r_stdrpt from GGSCI command interface:

 
start replicat r_stdrpt

Test

Insert Data to Source Table

 
INSERT INTO GG_SRC.gg_test VALUES ('1','One');
commit;
SELECT * FROM gg_test;
ID                   NAME
--------------- --------------------
1                    One

Check Data in Target Table

 
SELECT * FROM GG_TGT.gg_test;
-- You should see
ID                   NAME
--------------- --------------------
1                    One

References

* gg_wux_admin_v104.pdf page 39.

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em GOLDENGATE e marcado , , , , , , . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s