OGG step by step process

In this post let's look at the Oracle GoldenGate step by step process for replicating data between source and target databases. For all the installation, prerequisites and OGG set up part, see this link.

Now with all the pre requisites and other setup have been complete, we can start the actual process of GoldenGate replication. The data extraction and replication involves the below steps.

Start real time Extract process (Source)

Start Pump process (Source)

Start Initial extract (Source)

Start Initial replication (Target)

Start real time replication with handle collisions (Target)

Start real time replication without handle collisions (Target)

Let’s see one by one in action below.

1. Start real time Extract process (Source)

ggsci> ADD EXTRACT DEMO1E, TRANLOG, THREADS 2, BEGIN NOW
ggsci> ADD EXTTRAIL /backup/PRNETSDB/ogg/11.1/dirdat/ea, EXTRACT DEMO1E, Megabytes 200
ggsci> start extract DEMO1E

Explanation of commands:

1. Add an extract DEMO1E with transaction log as data source. When TRANLOG is specified it should be accompanied with BEGIN and we are starting with the current time. THREADS is 2 here as per cluster setup.
2. Add a trail dump file to extract DEMO1E with 200MB each
3. Start the extract

2. Start Pump process (Source)

ggsci> ADD EXTRACT DEMO1P, EXTTRAILSOURCE /backup/PRNETSDB/ogg/11.1/dirdat/ea
ggsci> ADD RMTTRAIL /backup/gg_kt/ogg/11.2/dirdat/pa, EXTRACT DEMO1P, Megabytes 100
ggsci> start extract DEMO1P

Explanation of commands:

1. Add an extract DEMO1P with trail dump source name
2. Add remote trial file to extract DEMO1P with size limited to 100MB
3. Start the extract

3. Start Initial extract (Source)

ggsci> ADD EXTRACT DEMO1I, sourceistable
OGG_source $ nohup extract paramfile $GGH/dirprm/DEMO1I.prm reportfile $GGH/dirrpt/DEMO1I.rpt > DEMO1I.out 2>&1 &

Explanation of commands:

1. Add an extract DEMO1I with SOURCEISTABLE keyword which means table is the source for this extract. This is used for initial loading extract and is a one time run extract.
2. Since we don’t know how long the extract would run, we run the extract job as a background job so that it completed without any issues. 
3. Extract using the parameter file DEMO1I.prm and use DEMO1I.rpt as a report file.

4. Start Initial replication (Target)

ggsci> add rep TDEMO1I, exttrail /backup/gg_kt/ogg/11.2/dirdat/ia, nodbcheckpoint
ggsci> start replicat TDEMO1I

Explanation of commands:

1. Add a replicat by name TDEMO1I with trail dump file name (This should be the name from the initial extract file at source). NODBCHECKPOINT ensures that this replicat will not write checkpoints to a checkpoint table
2. Start the replicat

5. Start real time replication with handle collisions (Target)

ggsci> dblogin userid ggs_dba password ggs_dba
ggsci> ADD REPLICAT DEMO1R, EXTTRAIL /backup/gg_kt/ogg/11.2/dirdat/pa, checkpointtable ggs_dba.CHECKPOINT
ggsci> ALTER REPLICAT DEMO1R, BEGIN 2014-23-12 04:30:03 -- (Initial extract start Time)
ggsci> start replicat DEMO1R

Explanation of commands:

1. Login to database via OGG as OGG user
2. Add replicat DEMO1R with trail file name (This should be the name of the pump extract at source) and specify a CHECKPOINTTABLE table name to write check point information.
3. Alter the replicat to begin at a specific time. This is the time when the initial extract is started. You can note the time when extract is started or you can find this from the report file of the initial extract.
4. Start the replicat

The parameter file will contain a keyword HANDLECOLLISIONS which is used to detect and resolve conflicts in data integrity. More on this keyword can be found in this oracle documentation link

6. Start real time replication without handle collisions (Target)
Now with all the initial data has been loaded into the target table and when there is no data to be loaded from the initial extract (see how to see status in the next section 7) we can restart the replicat to apply changes without error and conflicts detection by removing the HANDLECOLLISIONS keyword.

ggsci> stop replicat DEMO1R
ggsci> edit params DEMO1R
/* Remove the HANDLECOLLISIONS lines from DEMO1R.prm file
using edit option or with any other file editor */
ggsci> start replicat DEMO1R

Explanation of commands:

1. Stop the replicat
2. Edit the parameter file DEMO1R.prm and remove handlecollisions
3. Start the replicat

7. Information/status about the processes
One can check the status of any process by using the below commands.

ggsci> info  DEMO1E  -- (we will be able to see the Lag)
ggsci> stats replicat DEMO1E -- (summary of records processed)
ggsci> info manager -- (or info mgr)
ggsci> send rep DEMO1E, getlag -- To get lag time

Happy replicating!!