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.