Overview
Advantages of Using Source Data Pump
* Isolate primary extract from TCP/IP hiccups.
* Add storage flexibility
* Offload filtering and conversion processing from primary extract
Setup Overview
* Source DB -> Extract Process -> Local Trail -> Data Pump
-> Network -> RMTTrail -> Replicat Process -> Target DB
* See this post for additional setup instructions
Setup Source Box
* See this post for additional setup instructions
Setup Extract
* Create extract group named e_dprpt
* Add a extract trail for the new extract
ADD EXTRACT e_dprpt, TRANLOG, BEGIN now ADD EXTTRAIL c:/goldengate/dirdat/dprpt/tx, EXTRACT e_dprpt, MEGABYTES 20
* Create e_dprpt parameter file dirprm/e_dprpt.prm
-- Identify the Extract group: EXTRACT e_dprpt -- 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 remote trail on the target system: EXTTRAIL c:/goldengate/dirdat/dprpt/tx -- Specify tables to be captured: TABLE GG_SRC.GG_TEST;
* Create directory c:/goldengate/dirdat/dprpt
Setup Data Pump
* Add a data pump extract group named p_dprpt
ADD EXTRACT p_dprpt, EXTTRAILSOURCE c:/goldengate/dirdat/dprpt/tx, BEGIN now
* Add a remote trail for the pump
ADD RMTTRAIL /opt/oracle/goldengate/dirdat/dprpt/tx, EXTRACT p_dprpt
* Create data pump parameter file dirprm/p_dprpt.prm
-- Identify the Extract group: EXTRACT p_dprpt -- 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/dprpt/tx -- Allow mapping, filtering, conversion or pass data through as-is: PASSTHRU -- Specify tables to be captured: TABLE GG_SRC.GG_TEST;
* Create directory /opt/oracle/goldengate/dirdat/dprpt on target box
Start Primary Extract and Data Pump
* From GGSCI:
start extract e_dprpt start extract p_dprpt
* Verify both processes started and remote trail file is created in target box.
Setup Remote Box
* See this post for additional setup instructions
Setup Replicat
* Add a replicat group named r_dprpt:
ADD REPLICAT r_dprpt, EXTTRAIL /opt/oracle/goldengate/dirdat/dprpt/tx, BEGIN now
* Create r_dprpt parameter file in the dirprm directory:
-- Identify the Replicat group: REPLICAT r_dprpt -- 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 = smb) USERID GG_TGT, PASSWORD password -- Specify error handling rules: -- REPERROR (DEFAULT, ABEND) -- REPERROR (-1, IGNORE) -- Specify discard file for troubleshooting discardfile ./dirrpt/dprpt.dsc, append, megabytes 20 -- Specify tables to be replicated MAP GG_SRC.GG_TEST, TARGET GG_TGT.GG_TEST;
* Start r_dprpt from GGSCI command interface:
start replicat r_dprpt
Test
Insert Data to Source Table
INSERT INTO GG_SRC.gg_test VALUES ('10','Ten'); commit;
Check Data in Target Table
SELECT * FROM GG_TGT.gg_test; -- You should see ID NAME --------------- -------------------- 10 Ten
References
* gg_wux_admin_v104.pdf page 41.
* See this post for additional setup instructions