Monthly Archives: May 2013

GoldenGate Initial-Load Direct Load

This article will demostrate how to perform a OGG Initial Load using the Direct Load technique.

Other Initial Load techniques and their advantages and disadvantages can be found here.

Pre-requisites

OGG is installed on source and target systems

Environment

OGG : 11.2.1.0.1
Database : 11.2.0.3
Linux : OEL 6 ( 64 bit )

Direct Load Steps

1. Configure the initial-load processes

Extract


GGSCI> ADD EXTRACT icdl, SOURCEISTABLE

GGSCI> INFO EXTRACT *, TASKS

EXTRACT    ICDL      Initialized   2013-05-23 16:20   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE


cat ./dirprm/icdl.prm

EXTRACT icdl
-- --------------------------------------------------
-- environment
-- --------------------------------------------------
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")
SETENV (ORACLE_SID="GGDB1")
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID ggate, PASSWORD ggate
-- --------------------------------------------------
RMTHOST gglab1n2, mgrport 7809
RMTTASK REPLICAT, GROUP irdl
TABLE soe.inventories;
TABLE soe.product_descriptions;

Replicat


GGSCI> ADD REPLICAT irdl, SPECIALRUN

GGSCI> INFO REPLICAT *, TASKS

REPLICAT   IRDL      Initialized   2013-05-23 16:21   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:11 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN


cat ./dirprm/irdl.prm

REPLICAT irdl
-- --------------------------------------------------
-- environment 
-- --------------------------------------------------
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")
SETENV (ORACLE_SID="GGDB2")
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID ggate, PASSWORD ggate
-- --------------------------------------------------
-- options
-- --------------------------------------------------
ASSUMETARGETDEFS
DBOPTIONS SUPPRESSTRIGGERS
-- --------------------------------------------------
-- files
-- --------------------------------------------------
DISCARDFILE ./dirrpt/irdl.dsc, PURGE
-- --------------------------------------------------
-- mappings
-- --------------------------------------------------
MAP soe.inventories , TARGET soe.inventories;
MAP soe.product_descriptions , TARGET soe.product_descriptions;

2. Configure the change-synchronization processes

This is the normal replication methods, so I will not cover them here to keep things short. You can see example of this in some of my other posts.

3. Start the change-synchronization Extract on the Source site

GGSCI> start extract csoea

4. Start the Initial Load Extract

GGSCI (gglab1n1) > START EXTRACT icdl

GGSCI (gglab1n1) > INFO EXTRACT *, TASKS

EXTRACT    ICDL      Last Started 2013-05-23 16:22   Status RUNNING
Checkpoint Lag       Not Available
Log Read Checkpoint  Table SOE.INVENTORIES
                     2013-05-23 16:22:50  Record 14970
Task                 SOURCEISTABLE

Note that both the extract and replicat processes are now running. This is a result of the “RMTTASK” parameter in the extract parameter file.

GGSCI (gglab1n2)> INFO REPLICAT *, TASKS

REPLICAT   IRDL      Initialized   2013-05-23 16:21   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:02:16 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN

5. Check the report on both the source and target systems

Monitoring the report files will show you what tables are being processed and when the load is complete.

...
***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************


Report at 2013-05-23 18:47:03 (activity since 2013-05-23 16:22:32)

Output to irdl:

From Table SOE.INVENTORIES:
       #                   inserts:    900667
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0
From Table SOE.PRODUCT_DESCRIPTIONS:
       #                   inserts:      1000
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0
...

You can also use the info command to check the status.

GGSCI (gglab1n1) > INFO EXTRACT *, TASKS

EXTRACT    ICDL      Last Started 2013-05-23 16:22   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table SOE.PRODUCT_DESCRIPTIONS
                     2013-05-23 18:47:01  Record 1000
Task                 SOURCEISTABLE


GGSCI (gglab1n2) > INFO REPLICAT *, TASKS

REPLICAT   IRDL      Initialized   2013-05-23 16:21   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:08:29 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN

6. Once you verify that the intial load completes, start change synchronization REPLICAT on the Target system

GGSCI> start replicat rsoeas

Monitor the replicat for any errors and the current time and position of the records it is applying.

Next, verify that the change synchronization replicat has passed the point where the initial-load replicat stopped.

7. Turn off HANDLECOLLISIONS

SEND REPLICAT rsoeas, NOHANDLECOLLISIONS

Lastly, edit the replicat parameter file and remove HANDLECOLLISIONS so that it is not enabled next time the replicat starts.

GoldenGate Initial-Load Trail to Replicat

This article will demostrate how to perform a OGG Initial Load using the Trail to Replicat technique.

Other Initial Load techniques and their advantages and disadvantages can be found here.

Pre-requisites

OGG is installed on source and target systems

Environment

OGG : 11.2.1.0.1
Database : 11.2.0.3
Linux : OEL 6 ( 64 bit )

Trail to Replicat Steps

1. Configure the initial-load processes

Extract


SOURCEISTABLE
-- --------------------------------------------------
-- environment
-- --------------------------------------------------
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")
SETENV (ORACLE_SID="GGDB1")
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID ggate, PASSWORD ggate
-- --------------------------------------------------
RMTHOST gglab1n2, mgrport 7809
rmtfile /u01/app/oracle/product/golden_gate/dirdat/il, maxfiles 3, megabytes 2000, PURGE
TABLE soe.*;

Replicat


REPLICAT irttr
-- --------------------------------------------------
-- environment
-- --------------------------------------------------
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")
SETENV (ORACLE_SID="GGDB2")
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID ggate, PASSWORD ggate
-- --------------------------------------------------
-- options
-- --------------------------------------------------
ASSUMETARGETDEFS
DBOPTIONS SUPPRESSTRIGGERS
--DBOPTIONS DEFERREFCONST
-- --------------------------------------------------
-- files
-- --------------------------------------------------
DISCARDFILE ./dirrpt/irttr.dsc, PURGE
-- --------------------------------------------------
-- mappings
-- --------------------------------------------------
MAP soe.* , TARGET soe.*;

2. Configure the change-synchronization processes

This is the normal replication methods, so I will not cover them here to keep things short. You can see example of this in some of my other posts.

3. Start the change-synchronization Extract

GGSCI> DBLOGIN USERID ggate, PASSWORD password ggate

GGSCI> FLUSH SEQUENCE soe.*

GGSCI> start extract csoea

4. Start the initial-load extract

extract paramfile ./dirprm/icttr.prm reportfile ./dirrpt/icttr.rpt

You should observe the following:

* the collector starting on target as seen in the ggserr.log

* the trail files in dirdat directory on target ( $GG_HOME/dirdat/il* )

* report file in dirrpt directory on source

Monitor the icttr.rpt report file for any errors.

5. Start the initial-load replicat on the target environment

Since we are using normal trail files here, the replicat can be started w/o having to wait on the initial-load extract to complete.

add replicat irttr, exttrail ./dirdat/il, nodbcheckpoint
replicat paramfile ./dirprm/irttr.prm reportfile ./dirrpt/irttr.rpt

Monitor the report file for the initial-load replicat and wait until it the intiial-load extract is complete and the replicat is at the EOF with no more records to process. If there are no errors, then you can continue on to the next step.

However, I hit errors here. The following is from the irttr.rpt file, the info command, and the ggserr.log respectively.

...
Reading ./dirdat/il000000, current RBA 3564957, 33000 records

Report at 2013-05-22 21:50:21 (activity since 2013-05-22 21:49:31)

From Table SOE.INVENTORIES to SOE.INVENTORIES:
       #                   inserts:     33000
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0


Last log location read:
     FILE:      ./dirdat/il000000
     SEQNO:     0
     RBA:       3564957
     TIMESTAMP: 2013-05-22 21:29:46.677112
     EOF:       NO
     READERR:   0


2013-05-22 21:50:21  ERROR   OGG-01668  PROCESS ABENDING.
...
GGSCI (gglab1n2.enkitec.com) 2> info *

REPLICAT   IRTTR     Last Started 2013-05-22 21:49   Status ABENDED
Checkpoint Lag       00:20:34 (updated 15:45:19 ago)
Log Read Checkpoint  File ./dirdat/il000000
                     2013-05-22 21:29:46.677112  RBA 3565065
2013-05-22 21:32:18  INFO    OGG-00995  Oracle GoldenGate Delivery for Oracle, irttr.prm:  REPLICAT IRTTR starting.
2013-05-22 21:32:18  INFO    OGG-03035  Oracle GoldenGate Delivery for Oracle, irttr.prm:  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
2013-05-22 21:32:18  INFO    OGG-01815  Oracle GoldenGate Delivery for Oracle, irttr.prm:  Virtual Memory Facilities for: COM
    anon alloc: mmap(MAP_ANON)  anon free: munmap
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /u01/app/oracle/product/golden_gate/dirtmp.
2013-05-22 21:32:18  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, irttr.prm:  REPLICAT IRTTR started.
2013-05-22 21:33:02  ERROR   OGG-00446  Oracle GoldenGate Delivery for Oracle, irttr.prm:  CSN not found in supplemental file /u01/app/oracle/product/golden_gate/dirchk/IRTTR.cps.
2013-05-22 21:33:02  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, irttr.prm:  PROCESS ABENDING.

This turns out to be a know bug in OGG Version 11.2.1.0.1 and later. Unfortunately, the fix will not be available until 11.2.1.0.4.

Check out the follow note for more details.

OGG v11.2 Replicat fails OGG-00868 OCI Error ORA-01400: cannot insert NULL into
(“OGG_ADMIN”.”CHECKPOINT_LOX”.”LOG_CMPLT_CSN”) , ERROR OGG-00446 CSN not found in
supplemental file /u01/app/oracle/goldengate/dirchk/RPBSINI.cps. [ID 1468424.1]

So I leave off here waiting for the future version.

——————————————————————————-

However, if I were able to continue, there are only two more steps.

6. Start the change-synchronization Replicat

GGSCI> start replicat rsoeas

7. Turn off HANDLECOLLISIONS

SEND REPLICAT rsoeas, NOHANDLECOLLISIONS

Lastly, edit the replicat parameter file and remove HANDLECOLLISIONS so that it is not enabled next time the replicat restarts.

GoldenGate Initial-Load File to Replicat

This article will demostrate how to perform a OGG Initial Load using the File to Replicat technique.

Other Initial Load techniques and their advantages and disadvantages can be found here
.

Pre-requisites

OGG is installed on source and target systems

Environment

OGG : 11.2.1.0.1
Database : 11.2.0.3
Linux : OEL 6 ( 64 bit )

File to Replicat Steps

1. Configure the initial-load processes

Extract


SOURCEISTABLE
 -- --------------------------------------------------
 -- environment
 -- --------------------------------------------------
 SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")
 SETENV (ORACLE_SID="GGDB1")
 SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
 USERID ggate, PASSWORD ggate
 -- --------------------------------------------------
 RMTHOST gglab1n2, mgrport 7809
 RMTFILE /u01/app/oracle/product/golden_gate/dirdat/ilCSOE.DAT, PURGE
 TABLE soe.inventories;
 TABLE soe.product_descriptions;

Replicat


SPECIALRUN
 END RUNTIME
 -- --------------------------------------------------
 -- environment
 -- --------------------------------------------------
 SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")
 SETENV (ORACLE_SID="GGDB2")
 SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
 USERID ggate, PASSWORD ggate
 -- --------------------------------------------------
 -- options
 -- --------------------------------------------------
 ASSUMETARGETDEFS
 DBOPTIONS SUPPRESSTRIGGERS
 -- --------------------------------------------------
 -- files
 -- --------------------------------------------------
 EXTFILE /u01/app/oracle/product/golden_gate/dirdat/ilCSOE.DAT
 DISCARDFILE /u01/app/oracle/product/golden_gate/dirrpt/ilCSOE.dsc, PURGE
 -- --------------------------------------------------
 -- mappings
 -- --------------------------------------------------
 MAP soe.inventories , TARGET soe.inventories;
 MAP soe.product_descriptions , TARGET soe.product_descriptions;

2. Configure the change-synchronization processes

This is the normal replication methods, so I will not cover them here to keep things short. You can see example of this in some of my other posts.

3. Start the change-synchronization Extract

GGSCI> DBLOGIN USERID ggate, PASSWORD password ggate

GGSCI> FLUSH SEQUENCE soe.*

GGSCI> start extract csoea

4. Start the initial-load extract

extract paramfile ./dirprm/icsoea.prm reportfile ./dirrpt/icsoea.rpt

You should observe the following:

* the collector starting on target as seen in the ggserr.log

* the file ilCSOE.DAT in dirdat directory on target

* report file in dirrpt directory on source

When I first tried this on the entire schema, errors were reported in both the source and target ggserr logs.


2013-05-22 16:06:17  WARNING OGG-01223  Oracle GoldenGate Collector for Oracle:  Error 0 (Success) getting position in /u01/app/oracle/product/golden_gate/dirdat/ilCSOE.DAT.

2013-05-22 16:07:51  ERROR   OGG-01033  There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. (Remote file used is /u01/app/oracle/product/golden_gate/dirdat/ilCSOE.DAT, reply received is Error 0 (Success) getting position in /u01/app/oracle/product/golden_gate/dirdat/ilCSOE.DAT).

This turns out to be a limitation of this technique where the remote file is limited in size to 2 GB.

I then reduced my dataset down to two tables in order to perform the this intial-load.
I also did a test with the “Trail to Replicat” initial-load technique which can be used with datasets greater than 2GB.

Wait until the extract completes running and review the resulting report file for any errors.

5. Start the initial-load replicat on the target environment

replicat paramfile ./dirprm/irsoea.prm reportfile ./dirrpt/irsoea.rpt

Monitor the report file for the initial-load replicat and wait until it completes. If there are no errors, then you can continue on to the next step.

6. Start the change-synchronization Replicat

GGSCI> start replicat rsoeas

Monitor the replicat for any errors and the current time and position of the records it is applying.

Next, Verify that the change synchronization replicat has passed the point where the initial-load replicat stopped. You can compare both time and the RBA by looking in the irsoea.rpt report file and the current position of the change synchronization replication as shown by the info command.

...
Reading /u01/app/oracle/product/golden_gate/dirdat/ilCSOE.DAT, current RBA 97596089, 901667 records

Report at 2013-05-22 18:49:25 (activity since 2013-05-22 18:44:17)

From Table SOE.INVENTORIES to SOE.INVENTORIES:
       #                   inserts:    900667
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0
From Table SOE.PRODUCT_DESCRIPTIONS to SOE.PRODUCT_DESCRIPTIONS:
       #                   inserts:      1000
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0

Last log location read:
     FILE:      /u01/app/oracle/product/golden_gate/dirdat/ilCSOE.DAT
     RBA:       97596089
     TIMESTAMP: 2013-05-22 18:41:55.063284
...
GGSCI (gglab1n2.enkitec.com) 4> info *

REPLICAT   RSOEAS    Last Started 2013-05-22 18:44   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint  File /u01/app/oracle/product/golden_gate/dirdat/As000001
                     2013-05-22 19:49:32.242676  RBA 97596089

7. Turn off HANDLECOLLISIONS

SEND REPLICAT rsoeas, NOHANDLECOLLISIONS

Lastly, edit the replicat parameter file and remove HANDLECOLLISIONS so that it is not enabled next time the replicat restarts.

Oracle GoldenGate Initial Load Techniques

In order to start using OGG replication, the source and target databases must be in sync. This means that the source data must be copied over to the target and remain in sync while changes are occurring in the source database.

In this article I’m going to discuss the options available to instantiate the target database with the data in sync, their pros and conns, and their pre-requisites.

From Oracle documentation and some MOS notes, it is recommended as a general rule to use native tools if available to instantiate the data in the target database. This works well if the source and target databases are the same, but if they are not the same then an initial load technique may be the best option.

So, why is a native tool recommended? Some of Oracle native tools like RMAN and expdp/impdp can be used to sync the source and target to a particular SCN. Afterwards the OGG replication is started after that SCN; so no chance of duplicates and/or collisions in these cases. In the case of initial load techniques, there are collisions that occur and as a result, there are several steps that must be executed in order.

General Steps for Performing an Initial-Load

1. The continuous standard extract for replication ( or change synchronization extract ) is configured and started; however the corresponding replicat is not started yet.

2. Next the initial load and replicat are configured and executed.

3. Once the intial load and replicat are done, they will stop.

4. The replicat associated with the original change synchronization extract is now started with the HANDLECOLLISIONS parameter set.

5. When the replicat gets past the point where the intial load/replicat stopped, the HANDLECOLLISIONS parameter is turned off.

Here’s a quick time diagram of the order of events.

>-----|-----------|-----------|-----------|-----------|-------->  ( time )
	Time 1		Time 2		Time 3		Time 4		Time 5

	Change		Initial		Intial		Change		Turn
	Sync		Load		Load		Sync		Off
	Extract		Started		Done		Replicat	HandleCollisions
	Started								Started

OGG is now up and running. But did you notice in step 4 that the change synchronization replicat will be applying the same changes that the initial load replicat has already done? This is because the change synchronization was configured to start at a point in time before the intial load technique was. This is necessary in heterogenous systems to ensure that no transactional data is not lost between the time when the intial load is done and when change synchronization is started. The purpose of the HANDLECOLLISIONS parameter is to keep the target data in sync with the source with the same/multiple changes coming in.

There are several different techniques of intial load techniques, each with their own advantages and disadvantages.

Here’s a list of some of the different initial load techniques available:

1 File to Replicat

2 Trail to Replicat

3 File to Database Utility

4 Direct Load

5 Direct Bulk Load to SQL*Loader

Before performing an intial load technique, the following prerequisites must be met.

1 Disable DDL Processing

2 Prepare Target Tables

– Data : the target tables must be empty
– Constraints : disabled on the target
– Triggers : disabled on the target
– Indexes : delete for performance ( they can be added back in after the initial load is complete )

3 Configure and start the MGR Process on both Source and Target

4 Create Data-Definitions File

– this is only necessary if source and target tables differ in structure

5 Create Change-Sync Groups

I have a series of blogs that compare the different techniques advantages and disadvantages as well as show how to perform them.

Visual Diagram of Initial-Load Techniques

Spreadsheet Comparing Initial-Load Techniques