Oracle GoldenGate Tutorial 6 – configuring Data Pump process

The Data Pump (not to be confused with the Oracle Export Import Data Pump) is an optional secondary Extract group that is created on the source system. When Data Pump is not used, the Extract process writes to a remote trail that is located on the target system using TCP/IP. When Data Pump is configured, the Extract process writes to a local trail and from here Data Pump will read the trail and write the data over the network to the remote trail located on the target system.

The advantages of this can be seen as it protects against a network failure as in the absence of a storage device on the local system, the Extract process writes data into memory before the same is sent over the network. Any failures in the network could then cause the Extract process to abort (abend). Also if we are doing any complex data transformation or filtering, the same can be performed by the Data Pump. It will also be useful when we are consolidating data from several sources into one central target where data pump on each individual source system can write to one common trail file on the target.

Create the Extract process

GGSCI (devu007) 1> ADD EXTRACT ext1, TRANLOG, BEGIN NOW
EXTRACT added.

Create a local trail

Using the ADD EXTRAIL command we will now create a local trail on the source system where the Extract process will write to and which is then read by the Data Pump process. We will link this local trail to the Primary Extract group we just created, ext1

GGSCI (devu007) 3> ADD EXTTRAIL /u01/oracle/software/goldengate/dirdat/lt, EXTRACT ext1
EXTTRAIL added.

Create the Data Pump group

On the source system create the Data Pump group and using the EXTTRAILSOURCE keywork specify the location of the local trail which will be read by the Data Pump process

GGSCI (devu007) 4> ADD EXTRACT dpump, EXTTRAILSOURCE /u01/oracle/software/goldengate/dirdat/lt
EXTRACT added.

Create the parameter file for the Primary Extract group

GGSCI (devu007) 5> EDIT PARAMS ext1

“/u01/oracle/software/goldengate/dirprm/ext1.prm” [New file]

EXTRACT ext1
USERID ggs_owner, PASSWORD ggs_owner
EXTTRAIL /u01/oracle/software/goldengate/dirdat/lt
TABLE MONITOR.WORK_PLAN;

Specify the location of the remote trail on the target system

Use the RMTTRAIL to specify the location of the remote trail and associate the same with the Data Pump group as it will be wriiten to over the network by the data pump process

GGSCI (devu007) 6> ADD RMTTRAIL /u01/oracle/ggs/dirdat/rt, EXTRACT dpump
RMTTRAIL added.

Create the parameter file for the Data Pump group

Note- the parameter PASSTHRU signifies the mode being used for the Data Pump which means that the names of the source and target objects are identical and no column mapping or filtering is being performed here.

GGSCI (devu007) 2> EDIT PARAMS dpump

“/u01/oracle/software/goldengate/dirprm/dpump.prm” [New file]

EXTRACT dpump
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST redhat346, MGRPORT 7809
RMTTRAIL /u01/oracle/ggs/dirdat/rt
PASSTHRU
TABLE MONITOR.WORK_PLAN;

ON TARGET SYSTEM

Create the Replicat group

The EXTTRAIL clause indicates the location of the remote trail and should be the same as the RMTTRAIL value that was used when creating the Data Pump process on the source system.

GGSCI (redhat346.localdomain) 2> ADD REPLICAT rep1, EXTTRAIL /u01/oracle/ggs/dirdat/rt
REPLICAT added.

Create the parameter file for the Replicat group

GGSCI (redhat346.localdomain) 3> EDIT PARAMS rep1

REPLICAT rep1
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
MAP MONITOR.WORK_PLAN, TARGET MONITOR.WORK_PLAN;

ON SOURCE

On the source system, now start the Extract and Data Pump processes.

GGSCI (devu007) 3> START EXTRACT ext1

Sending START request to MANAGER …
EXTRACT EXT1 starting

GGSCI (devu007) 4> START EXTRACT dpump

Sending START request to MANAGER …
EXTRACT DPUMP starting

GGSCI (devu007) 5> info extract ext1

EXTRACT EXT1 Last Started 2010-02-18 11:23 Status RUNNING
Checkpoint Lag 00:40:52 (updated 00:00:09 ago)
Log Read Checkpoint Oracle Redo Logs
2010-02-18 10:42:19 Seqno 761, RBA 15086096

GGSCI (devu007) 6> INFO EXTRACT dpump

EXTRACT DPUMP Last Started 2010-02-18 11:23 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint File /u01/oracle/software/goldengate/dirdat/lt000000
2010-02-18 11:15:10.000000 RBA 5403

Note- the data pump process is reading from the Local Trail file – /u01/oracle/software/goldengate/dirdat/lt000000

ON TARGET SYSTEM

Start the Replicat process

GGSCI (redhat346.localdomain) 4> START REPLICAT rep1

Sending START request to MANAGER …
REPLICAT REP1 starting

GGSCI (redhat346.localdomain) 5> STATUS REPLICAT rep1
REPLICAT REP1: RUNNING

Coming Next! – DDL change synchronization …