Oracle GoldenGate – Initial Load – File to Replicat Method

INITIAL LOAD FILE TO REPLICAT
SOURCE DATABASE VERSION  11.2.0.3
TARGET DATABASE VERSION  11.2.0.4
ORACLE GOLDEN GATE VERSION  12.1.2.0.0
SOURCE DATABASE  GGDB1
TARGET DATABASE  GGDB2
SCHEMA USED  VEERA
TABLE NAME  MYTABLE

The File to Replicat Method is similar to the normal CDC (Change Data Capture) method used in the GoldenGate. In this method, the Initial Load Extract extracts the data from the Source Tables and writes it to the Extract File in an Canonical Format. This file is read by the Initial Load Replicat Process and this loads the data using the Database Interface.

One thing should be taken in to considerations. During this Initial Load, you can also enable the Change Synchronization Extract and Datapump processes to record or capture the Online or Incremental changes which happens during the Initial Load and later it can be merged or reconciled with the results of the Initial Load.

File to Replicat is slower when comparing to the Other types/methods of Initial Load because, here the records are applied to the target database one by one. It is applied one record at a time.

 

ON TARGET

As I mentioned in my earlier postings, For starting the Initial Load, The structure of the tables should be exported from the Source and Imported to the Target database.
This can be done using the EXPDP / IMPDP utility available in the Oracle.

So in this example I have already exported the Schema VEERA from Source and IMPORTED to the Target System (only the structures).

SQL> select count(1) from mytable;
COUNT(1)

———-
0
SQL> desc mytable
Name          Null?             Type
—————-  ——————– —————————-
ID               NOT NULL     NUMBER

So in Target Table there are no rows inserted. Only the Structure is created.

 

ON SOURCE

[oracle@ogg1 ~]$ sqlplus /nologSQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 11 12:08:20 2015Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> conn veera/laser@ggdb1
Connected.
SQL> select * from tab;

TNAME        TABTYPE  CLUSTERID
————–  ————— ——————
MYTABLE    TABLE

SQL> select count(1) from mytable;

COUNT(1)
—————
10000000

Create the Initial Load Extract Process. 

ADD EXTRACT EXTLOAD, SOURCEISTABLE
GGSCI (ogg1.localdomain) 6> ADD EXTRACT EXTLOAD, SOURCEISTABLE
EXTRACT added.
NOTE: Since it is a one time extract process, the data is extracted from the Source DATA and not from the Transaction Logs(Redo Logs or Archive logs). The Extract Process automatically understands that this is a Initial Load Process since the keyword SOURCEISTABLE is used.

 

Configure the Extract process for the Initial Load. The parameters are below,

EXTRACT EXTLOAD
SOURCEISTABLE
USERID ggadmin, password oracle
RMTHOST OGG2, MGRPORT 7809
RMTFILE /ogg/dirdat/in, PURGE
TABLE veera.MYTABLE;

SOURCEISTABLE This parameter insists GoldenGate Extract process to extract the data directly from the Source Tables instead from redo logs.

RMTFILE – This is the Extract file on the remote system. The extract process writes the extracted data to this file where then the Replicat process reads these files and applies to the Target system.

GGSCI (ogg1.localdomain) 7> EDIT PARAMS EXTLOAD

Configure the parameters and save the file.

GGSCI (ogg1.localdomain) 8> VIEW PARAMS EXTLOADEXTRACT EXTLOAD
SOURCEISTABLE
USERID ggadmin, password oracle
RMTHOST OGG2, MGRPORT 7809
RMTFILE /ogg/dirdat/in, PURGE
TABLE veera.MYTABLE;

In the ggserr.log file you could see the below.,

2015-01-11 16:04:12 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD EXTRACT EXTLOAD SOURCEISTABLE.
2015-01-11 16:05:10 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): EDIT PARAMS EXTLOAD.

To see the Normal or Regular Extract, Datapump or Replicat processes the command is INFO ALL. But in this command the Extract or Replicat processes which are configured for INITIAL LOAD will not be displayed.

To display INITIAL LOAD Processes,

GGSCI (ogg1.localdomain) 11> INFO ALL TASKSProgram Status Group Lag at Chkpt Time Since ChkptEXTRACT STOPPED EXTLOAD

 

ON TARGET

Create the Initial Replicat Process,

ADD REPLICAT REPLOAD, SPECIALRUN
GGSCI (ogg2.localdomain) 3> ADD REPLICAT REPLOAD, SPECIALRUN
REPLICAT added.

Configure the Replicat process for the Initial Load. The parameters are below,

GGSCI (ogg2.localdomain) 4> EDIT PARAMS REPLOADREPLICAT REPLOAD
SPECIALRUN
USERID ggadmin, password oracle
EXTFILE /ogg/dirdat/in
DISCARDFILE /ogg/dirrpt/rep.dsc, PURGE
ASSUMETARGETDEFS
MAP veera.MYTABLE, TARGET veera.MYTABLE;
END RUNTIME

SPECIALRUN – This parameter tells the Replicat that this is a “ONE-TIME” Process. This is an Initial Load process and not the Normal one. This is process ends with END RUNTIME.

EXTFILE – The Filename present in the Remote system with location. This contains the data which are written by the Extract process for Synchronization of the Target database.

ASSUMETARGETDEFS – This parameter tells the Replicat Process that the Structure of the Source Tables are same as the Target Table.

 

To view the Parameters,

GGSCI (ogg2.localdomain) 5> VIEW PARAMS REPLOADREPLICAT REPLOAD
SPECIALRUN
USERID ggadmin, password oracle
EXTFILE /ogg/dirdat/in
DISCARDFILE /ogg/dirrpt/rep.dsc, PURGE
ASSUMETARGETDEFS
MAP veera.MYTABLE, TARGET veera.MYTABLE;
END RUNTIME
GGSCI (ogg2.localdomain) 6> INFO ALL TASKSProgram Status Group Lag at Chkpt Time Since ChkptREPLICAT STOPPED REPLOAD

In the ggserr.log file you could see the below,

2015-01-11 16:27:03 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD REPLICAT REPLOAD SPECIALRUN.
2015-01-11 16:28:43 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): EDIT PARAMS REPLOAD.

 

ON SOURCE

Now Start the Initial Load Extract Process from the SOURCE SYSTEM,

GGSCI (ogg1.localdomain) 14> START EXTRACT EXTLOAD
Sending START request to MANAGER …

EXTRACT EXTLOAD starting
GGSCI (ogg1.localdomain) 15> info all tasksProgram Status Group Lag at Chkpt Time Since ChkptEXTRACT STARTING EXTLOAD
GGSCI (ogg1.localdomain) 32> info all tasks
Program Status Group Lag at Chkpt Time Since Chkpt
EXTRACT RUNNING EXTLOAD

Wait until the Initial Extraction completes. Once it is finished, start the Initial Load Replicat which is on the Target System.

GGSCI (ogg1.localdomain) 12> info all tasks
Program Status Group Lag at Chkpt Time Since Chkpt
EXTRACT STOPPED EXTLOAD

The below is registered in the ggserr.log,

2015-01-12 11:41:50 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, extload.prm: EXTRACT EXTLOAD started.
2015-01-12 11:41:50 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, extload.prm: Output file /ogg/dirdat/in is using format RELEASE 12.1.
2015-01-12 11:41:52 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info all tasks.
2015-01-12 11:41:55 INFO OGG-01226 Oracle GoldenGate Capture for Oracle, extload.prm: Socket buffer size set to 27985 (flush size 27985).
2015-01-12 11:42:17 INFO OGG-00991 Oracle GoldenGate Capture for Oracle, extload.prm: EXTRACT EXTLOAD stopped normally.

 

ON TARGET

Start the Initial Load Replicat and using sqlplus, check if the data are getting loaded to the target table.

GGSCI (ogg2.localdomain) 5> start reploadSending
START request to MANAGER …

REPLICAT REPLOAD starting
GGSCI (ogg2.localdomain) 6> info all tasks
Program Status Group Lag at Chkpt Time Since Chkpt
REPLICAT RUNNING REPLOAD
[oracle@ogg2 dirdat]$ sqlplus /nologSQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 12 11:49:08 2015Copyright (c) 1982, 2013, Oracle. All rights reserved.SQL> conn veera/laser@ggdb2
Connected.
SQL> select count(1) from mytable;

COUNT(1)
—————
1406001

SQL> select count(1) from mytable;

COUNT(1)
—————
9217539

SQL> select count(1) from mytable; COUNT(1)
—————
10000000

As I mentioned before., here the Initial Load Extract extracts the data from the Source Tables and writes it to the Extract File in an Canonical Format. This file is read by the Initial Load Replicat Process and this loads the data using the Database Interface.

We can see the trail file created in the Target system from where the data is loaded to the Target table by the Replicat Process,

[oracle@ogg2 ~]$ cd /ogg/dirdat
[oracle@ogg2 dirdat]$ ls -lart
total 770416
drwxrwxr-x. 27 oracle oinstall 4096 Jan 4 13:48 ..
drwxr-x— 2 oracle oinstall 4096 Jan 12 11:41 .
-rw-r—– 1 oracle oinstall 788890292 Jan 12 11:42 in
[oracle@ogg2 dirdat]$

In ggserr.log file you could see the below.,

2015-01-12 11:46:12 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, repload.prm: REPLICAT REPLOAD started.
2015-01-12 11:46:12 INFO OGG-03522 Oracle GoldenGate Delivery for Oracle, repload.prm: Setting session time zone to source database time zone ‘GMT’.
2015-01-12 11:46:12 INFO OGG-03506 Oracle GoldenGate Delivery for Oracle, repload.prm: The source database character set, as determined from the trail file, is we8mswin1252.
2015-01-12 12:10:15 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info all tasks.
2015-01-12 12:19:21 INFO OGG-00994 Oracle GoldenGate Delivery for Oracle, repload.prm: REPLICAT REPLOAD stopped normally.

As I mentioned earlier in this post, we should start the Change Synchronization Extract processes before starting the Initial Load Extract process. After the Initial Load is completed, you can start the Change Synchronization Replicat process to apply the Incremental Changes to the Target Database.

 

Note – The GGSCI prompt serial number will not be in sequence since many spelling and errors occured which I have not pasted here.

 

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *