Setup/Configure Oracle Golden Gate Replication 

In this post I will show how to setup Golden Gate replication (only DML) for specific schema on two different servers. This post also assumes that you are using Oracle on Linux OS. 


when configuring the same for SQL Server the process changes slightly due to different architecture of SQL Server and I will try to cover it the separate post. 

So the Outline of the over all process is as follows

1. Install packages ( if doing on SQL Server)
2. Install sql server mgmt studio (  if doing on SQL Server)
3. restore DB , create login, Create OS user, grant permissions, create DSN ( if doing on SQL Server)
4. Install GG
5. configure manager, defgen, extract, pump on source and replicat on target
6. start extract (and pump on source and replicat on target when required)
7. check replication on the fly…

There is also process called instantiation that requires same flash of the data on source and destination. There are different methods which can be used for it like...

1. Export/import
2. RMAN restore/recover
3. RMAN duplicate database
4. Hardware Storage solutions

In this post I'm using export/import due the fact that size of DB is small. if you are planning to setup GG for VLDBs you can consider the other options.

REMEMBER – always first start extract and dp on source and make sure they can capture records (check using stats ext_name/rep_name)

 Once verified only than take backup to restore at destination. Reason for this is that if they can’t capture updates they will not be able to replicate changes on destination and they data will be lost. 

1) Enable Minimal Supplemental Logging in Oracle on source

SQLPLUS > alter database add supplemental log data;

2) Enable Supplemental Logging at Table Level on source

GGSCI> dblogin userid SCOTT password tiger

GGSCI> add trandata SCOTT.*

3) Add Extract, Add Exttrail, Add Pump, Add Rmttrail on source and  Start Extract, Start Pump on source

[oracle@doggs goldengate]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 10.4.0.19 Build 002

Linux, x64, 64bit (optimized), Oracle 10 on Sep 22 2009 14:18:08

Copyright (C) 1995, 2009, Oracle and/or its affiliates.  All rights reserved.

GGSCI (doggs) 1> DBLOGIN USERID SCOTT,  PASSWORD tiger

Successfully logged into database.

GGSCI (doggs) 4> edit params mgr

--Manager config file

--Port number that manager runs on

PORT 7809

--Forces manager to restart extract, pump and replicat if they shut down

AUTORESTART ER *, RETRIES 12, WAITMINUTES 5, RESETMINUTES 60

--Manages trail files to conserve space

PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPHOURS 1, FREQUENCYMINUTES 30

--Specifies to log the lag time as a warning in the event log

LAGCRITICALMINUTES 5

--Specifies how often to report lag info to the event log

LAGREPORTMINUTES 60

LAGINFOMINUTES 0

GGSCI (doggs) 2> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING

n  Always remember, that manager should be running all the time otherwise your replication will not work

GGSCI (doggs) > START MANAGER

CREATE DEFGEN FILE ON SOURCE AND THEN COPY TO DESTINATION -

GGSCI (doggs) 2> edit params defgen

--paramter file to run the defgen utility

--to document source metadata for heterogenous replication

--from ogg_source directory issue

--DEFGEN PARAMFILE .\dirprm\defgen.prm

--write out source defs file to local directory

--then copy it to targets dirdef directory

DEFSFILE ./dirdef/source.def, PURGE

--log in to get meta data

USERID SCOTT, PASSWORD tiger

--get definitions for these tables

--TABLEEXCLUDE <owner.table>

TABLE SCOTT.*;

[oracle@doggtest goldengate]$ defgen paramfile ./dirprm/defgen.prm reportfile ./dirrpt/defgen.rpt

Copy this defgen file ./dirdef/source.def on target box in same path…

SQL> alter session set nls_date_format = 'MM/DD/YY hh24:mi:ss'

-- log in to create supplemental log groups

GGSCI (doggs) > DBLOGIN USERID SCOTT PASSWORD SCOTT

--add table level supplemental logging

--force logging on non-updated columns with COLS keyword

--because we need values in those 2 columns for

--parameter passing later

--ONE SHOULD MAKE SURE THAT TO ASSIGN THE USER ALL PERMISSION ON MASTER DB IN SQL2K/5 SERVERS OTHERWISE EXTRACT WILL NOT START.

-- SO USER GGSTEST1 SHOULD HAVE PERMSSIONS ON SOURCE DB AS WELL AS ON OTHER BUILTIN DB ( 4 IN TOTAL)

GGSCI (doggs) > ADD TRANDATA SCOTT.*

--verify supplemental log groups were created

GGSCI (doggs) >INFO TRANDATA *

--register log based extract to the manager starting now

GGSCI (doggs) > ADD EXTRACT SCOTT_ext, TRANLOG, BEGIN now

--register the extracts trail info

GGSCI (doggs) > ADD EXTTRAIL .\dirdat\et, EXTRACT SCOTT_ext

GGSCI (doggs) 2>edit params  SCOTT_EXT

-- extract config file

-- name the extract process

EXTRACT SCOTT_ext

--log in to get meta data

-- password can be encrypted

USERID SCOTT, PASSWORD tiger

--have extract write to trailfile called et

EXTTRAIL ./dirdat/et

--truncates ingnored by default

--get them during clean up runs

GETTRUNCATES

--which tables to capture transactions from?

TABLE SCOTT.*;

--register the pump process

--pump reads from exttrailsource file named et

GGSCI (doggs) > ADD EXTRACT SCOTT_pmp, EXTTRAILSOURCE .\dirdat\et BEGIN NOW

--register the remote trail

--pump writes to remote trailfile named rt

--pump writes to remote trailfile named rt

GGSCI (doggs) > ADD RMTTRAIL .\dirdat\rt, EXTRACT SCOTT_pmp

GGSCI (doggs) 2> edit params SCOTT_pmp

--pump is an extract

--name the process

EXTRACT SCOTT_pmp

--send data to remote host

RMTHOST 10.21.40.201, MGRPORT 7810

--send data to remote trail named rt

RMTTRAIL ./dirdat/rt

--pass data from trial file thru to remote trail as-is

PASSTHRU

--get trail file data for these tables

TABLE SCOTT.*;

5) Get the current SCN on the source database:

SQLPLUS> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

3410138433

6) Get a flashback snapshot from the SCN that you obtained in the previous step. You can do this with exp or expdp utility. The following example shows the use of export utility (exp):

#exp <username>/<password> owner=<schema_name> grants=n statistics=none triggers=n compress=n FLASHBACK_SCN=3410138433

#exp SCOTT/tiger file=SCOTT.dmp log=SCOTT.log grants=n compress=n buffer=100000 FLASHBACK_SCN=114136295

Note: Undo_retention has to be set high enough, and the export log needs to be checked for errors. You can speed up exp by running multiple session in parallel but you have to manually configure subsets

of the data you want to export (e.g. different tables for different export files). Make sure to use the same FLASHBACK_SCN for all export sessions to ensure taking a read consistent backup.

7) Start an import to the target database when step 6 is complete.

# imp SCOTT/tiger file=SCOTT.dmp fromuser=SCOTT touser=SCOTT log=impSCOTT.log buffer=100000 commit=y

SQL>  select OWNER,OBJECT_NAME, OBJECT_TYPE, STATUS from dba_objects where OWNER='SCOTT' and status='INVALID';

SQL> select object_type, count(*) from user_objects group by object_type; --- tally objects.

ON DESTINATION

8) Add and Start Replicat:

[oracle@doggtest goldengate]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 10.4.0.19 Build 002

Linux, x64, 64bit (optimized), Oracle 10 on Sep 22 2009 14:18:08

Copyright (C) 1995, 2009, Oracle and/or its affiliates.  All rights reserved.

GGSCI (ggstest) 1> DBLOGIN USERID SCOTT,  PASSWORD tiger

Successfully logged into database.

v  CREATE MANAGER CONFIG FILE ON DEST DB

GGSCI (ggstest) 2> edit params mgr

--Manager config file

--Port number that manager runs on

PORT 7810

--Forces manager to restart extract, pump and replicat if they shut down

AUTORESTART ER *, RETRIES 12, WAITMINUTES 5, RESETMINUTES 60

--Manages trail files to conserve space

PURGEOLDEXTRACTS .\dirdat\*, USECHECKPOINTS, MINKEEPHOURS 1, FREQUENCYMINUTES 30

--Specifies to log the lag time as a warning in the event log

LAGCRITICALMINUTES 5

--Specifies how often to report lag info to the event log

LAGREPORTMINUTES 60

LAGINFOMINUTES 0

GGSCI> add replicat SCOTT_REP, exttrail ./dirdat/rt

GGSCI (ggstest) 3> edit params SCOTT_REP

defgen paramfile ./dirprm/defgen.prm

--name the replicat process

REPLICAT SCOTT_REP

--helpful if more than one instnace on the box

--SETENV (ORACLE_SID="ORA10GR2")

USERID SCOTT, PASSWORD tiger

--use the sourcedefs file to read the trail

SOURCEDEFS ./dirdef/source.def

--throw error records to discard file

DISCARDFILE ./dirrpt/test_rep.dsc, purge

--on encountering an error when applying data to the target db

--set a global response to all errors which is

--log the error ro the discard file but continue processing

REPERROR default, discard

REPORTCOUNT EVERY 1 MINUTES, RATE

--increase the thruput of replicat processing by arranging

--similar SQL statements into arrays and applying them

--at an accelerated rate

BATCHSQL

--ignore truncates is default

GETTRUNCATES

--Mapping section, source to target

--simple like-to-like mapping

MAP SCOTT.*, TARGET SCOTT.*;

--from here forward treat all updates & deletes as inserts

INSERTALLRECORDS

GGSCI> start replicat TEST_REP, aftercsn 114136295

GGSCI> info all 

This will start the replicat and the replicat will start reading from the trail onwards SCN defined with start command.

Check the count of source and destination table and insert some data. Recheck the count, if the count is increasing on destination i.e. you successfully configured the replication.

If you find that somehow your extracts/replicats has been abended, you can check the log files on the respective host under GG home directory.  Most of the times due to typos in parameter file or connectivity issues the processes abends.