Oracle GoldenGate ---performing initial data load

This example illustrates using the GoldenGate direct load method to extract records from an Oracle 11g database on Red Hat Linux platform and load the same into an Oracle 11g target database on an AIX platform.

The table PRODUCTS in the SH schema on the source has 72 rows and on the target database the same table is present only in structure without any data. We will be loading the 72 rows in this example from the source database to the target database using GoldenGate Direct Load method.

On Source

1) Create the Initial data extract process ‘load1′

GGSCI (redhat346.localdomain) 5> ADD EXTRACT load1, SOURCEISTABLE
EXTRACT added.

Since this is a one time data extract task, the source of the data is not the transaction log files of the RDBMS (in this case the online and archive redo log files) but the table data itself, that is why the keyword SOURCEISTABLE is used.

2) Create the parameter file for the extract group load1

EXTRACT: name of the extract group
USERID/PASSWORD: the database user which has been configured earlier for Extract ( this user is created in the source database)
RMTHOST: This will be the IP address or hostname of the target system
MGRPORT: the port where the Manager process is running
TABLE: specify the table which is being extracted and replicated. This can be specified in a number of ways using wildcard characters to include or exclude tables as well as entire schemas.

GGSCI (redhat346.localdomain) 6> EDIT PARAMS load1

EXTRACT load1
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST devu007, MGRPORT 7809
RMTTASK replicat, GROUP load2
TABLE sh.products;

On Target

3) Create the initial data load task ‘load2′

Since this is a one time data load task, we are using the keyword SPECIALRUN

GGSCI (devu007) 1> ADD REPLICAT load2, SPECIALRUN
REPLICAT added.

4) Create the parameter file for the Replicat group, load2

REPLICAT: name of the Replicat group created for the initial data load
USERID/PASSWORD: database credentials for the Replicat user (this user is created in the target database)
ASSUMETARGETDEFS: this means that the source table structure exactly matches the target database table structure
MAP: with GoldenGate we can have the target database structure entirely differ from that of the source in terms of table names as well as the column definitions of the tables. This parameter provides us the mapping of the source and target tables which is same in this case

GGSCI (devu007) 2> EDIT PARAMS load2

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

REPLICAT load2
USERID ggs_owner, PASSWORD ggs_owner
ASSUMETARGETDEFS
MAP sh.customers, TARGET sh.customers;

On Source

SQL> select count(*) from products;

COUNT(*)
———-
72

On Target

SQL> select count(*) from products;

COUNT(*)
———-
0

On Source

5) Start the initial load data extract task on the source system

We now start the initial data load task load 1 on the source. Since this is a one time task, we will initially see that the extract process is runningand after the data load is complete it will be stopped. We do not have to manually start the Replicat process on the target as that is done when the Extract task is started on the source system.

On Source

GGSCI (redhat346.localdomain) 16> START EXTRACT load1

Sending START request to MANAGER …
EXTRACT LOAD1 starting

GGSCI (redhat346.localdomain) 28> info extract load1

EXTRACT LOAD1 Last Started 2010-02-11 11:33 Status RUNNING
Checkpoint Lag Not Available
Log Read Checkpoint Table SH.PRODUCTS
2010-02-11 11:33:16 Record 72
Task SOURCEISTABLE

GGSCI (redhat346.localdomain) 29> info extract load1

EXTRACT LOAD1 Last Started 2010-02-11 11:33 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table SH.PRODUCTS
2010-02-11 11:33:16 Record 72
Task SOURCEISTABLE

On Target

SQL> select count(*) from products;

COUNT(*)
———-
72