How to setup GoldenGate Replication in Oracle

In the following post we’ll understand to set up a replication using GoldenGate, for instruction on installing the software take a look on my small post on the same (https://nitishanandsrivastava.wordpress.com/oracle-goldengate/installing-golden-gate/).

Now we assume that the GG is installed and we have two servers on which Oracle databases are running, the GG Manager process is up and running on both these servers as GG is installed on both these servers, the TCP/IP network connectivity open from source server to target server’s manager port and basic understanding of GG replication flow which can be quickly revised from my post (https://nitishanandsrivastava.wordpress.com/oracle-goldengate/architecture/).

Basic Replication Steps

Preparing the Source Database

STEP 1:- The Source database should be in archivelog mode, if not, follow the below steps:-

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG


<em>STEP 2:- Enable minimal supplemental logging:</em>


SQL> alter database add supplemental log data;
Database altered.

STEP 3:- Turn off the database recyclebin feature and purge it.

SQL> PURGE DBA_RECYCLEBIN;
SQL> alter system set recyclebin=off scope=spfile;
DBA Recyclebin purged.

STEP 4:- Create GoldenGate Schema (tablespace/user) and grant necessary priveleges:-

SQL>  create tablespace gg_tbs datafile '/u01/app/oracle/oradata/orcl/gg_tbs_data01.dbf' size 100m;
Tablespace created.
SQL>  create user ggate identified by oracle default tablespace gg_tbs quota unlimited on gg_tbs;
User created.
SQL>  grant CREATE SESSION, CONNECT, RESOURCE, ALTER ANY TABLE, ALTER SYSTEM, CREATE TABLE, DBA, LOCK ANY TABLE, SELECT ANY TRANSACTION, FLASHBACK ANY TABLE to ggate;
Grant succeeded.
SQL>  grant execute on utl_file to ggate;
Grant succeeded.

STEP 5:- Now go to the GoldenGate home directory and run scripts for creating all necessary objects for replication.

$ cd /u01/app/oracle/product/gg/
$ sqlplus / as sysdba

SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggate;
SQL> @ddl_enable.sql

STEP 6:- Now edit the parameter file:-

$./ggsci
GGSCI  1> EDIT PARAMS ./GLOBALS
GGSCHEMA ggate

STEP 7:- Now lets create the schema for replication on both source and target database.

SQL> create user source identified by source default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource,unlimited tablespace to source;
Grant succeeded.
SQL>
SQL> create user target identified by target default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource,unlimited tablespace to target;
Grant succeeded.

STEP 8:- Now Creating the directories for trail files on both servers and create directory for discard file on target server only.

Source server

mkdir /u01/app/oracle/product/gg/dirdat/tr

Target Server

mkdir /u01/app/oracle/product/gg/dirdat/tr
mkdir /u01/app/oracle/product/gg/discard

STEP 9:- Now, Configuring extract process on Source Database:-

$ ./ggsci
GGSCI  1> edit params mgr

PORT 7809

GGSCI  2> start manager

Manager started.

GGSCI (db.us.oracle.com) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

STEP 10:- Login into database and add additional information about primary keys into log files.

GGSCI 4> dblogin userid ggate

Password:

Successfully logged into database.

GGSCI 5> ADD SCHEMATRANDATA source

2015-08-03 10:30:20 INFO OGG-01788 SCHEMATRANDATA has been added on schema source.

GGSCI 6> add extract ext1, tranlog, begin now

EXTRACT added.

GGSCI 7> add exttrail /u01/app/oracle/product/gg/dirdat/tr, extract ext1

EXTTRAIL added.

GGSCI 8> edit params ext1

extract ext1

userid ggate, password oracle

exttrail /u01/app/oracle/product/gg/dirdat/tr

ddl include mapped objname source.*;

table source.*;

GGSCI 9> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED EXT1 00:00:00 00:01:29

GGSCI 10> add extract pump1, exttrailsource /u01/app/oracle/product/gg/dirdat/tr , 
begin now

EXTRACT added.

GGSCI 11> add rmttrail /u01/app/oracle/product/gg/dirdat/tr, extract pump1

RMTTRAIL added.

GGSCI 12> edit params pump1

EXTRACT pump1

USERID ggate, PASSWORD oracle

RMTHOST db2, MGRPORT 7809

RMTTRAIL /u01/app/oracle/product/gg/dirdat/tr

PASSTHRU

table source.*;

GGSCI 13> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED EXT1 00:00:00 00:02:33
EXTRACT STOPPED PUMP1 00:00:00 00:02:56

Preparing the Target Database

STEP 1:- Verify if the manager is running on the Target Server and Start if not already started.

$ ./ggsci
GGSCI 1> edit params mgr

PORT 7809

GGSCI 2> start manager

Manager started.

GGSCI 3> info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING

STEP 3:- Create a checkpoint table and change the GLOBAL file.

GGSCI  4> EDIT PARAMS ./GLOBALS

CHECKPOINTTABLE     target.checkpoint

GGSCI  5> dblogin userid target

Password:

Successfully logged into database.

GGSCI  6> add checkpointtable target.checkpoint

Successfully created checkpoint table target.checkpoint.

STEP 4:- Add replicat process

GGSCI  8> add replicat rep1, exttrail /u01/app/oracle/product/gg/dirdat/tr, begin now

REPLICAT added.

GGSCI (db2) 9> edit params rep1

REPLICAT rep1

ASSUMETARGETDEFS

USERID target, PASSWORD target

discardfile /u01/app/oracle/product/gg/discard/rep1_discard.txt, append, megabytes 10 DDL

map source.*, target target.*;

GGSCI  10> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT STOPPED REP1 00:00:00 00:01:32

GGSCI  11>

Starting Replication

At Source Server

STEP 1:- Start extract process

GGSCI  1> start extract ext1

Sending START request to MANAGER ...

EXTRACT EXT1 starting

GGSCI  2> start extract pump1

Sending START request to MANAGER ...

EXTRACT PUMP1 starting

GGSCI  3> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT1 00:00:00 00:00:01

EXTRACT RUNNING PUMP1 00:00:00 00:01:01

At Destination Server

STEP 2:- Start Replicat Process

GGSCI  1> start replicat rep1

Sending START request to MANAGER ...

REPLICAT REP1 starting

GGSCI (db2) 2> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REP1 00:00:00 00:00:06

Finally our replication has been configured between source and destination DB, so now you can create objects under Source schema and it would get replicated to Target Schema. There are several features of GoldenGate which makes it far better than Oracle Streams and CDC.

Please comment below in case you require the automated script for configuring the GoldenGate replication with basic inputs at the run time. Also let me know if there is any step that is ambiguous or doubtful. I tried to mention it but just in case if there are any question regarding from which user to run, where to run, how to run any of the above commands, please comment below.

26 thoughts on “How to setup GoldenGate Replication in Oracle”

Danner says:

June 4, 2016 at 4:30 AM

REPLICAT rep1
ASSUMETARGETDEFS
USERID target, PASSWORD oracle

The password is target not oracle 😀

LikeLike

Reply
- Nitish Srivastava says:
  
  June 7, 2016 at 9:28 AM
  
  Hi Danner,
  
  Thanks for pointing that out, have amended the post.
  
  Cheers
  
  LikeLike
  
  Reply
Rohan says:

July 6, 2016 at 2:31 PM

Hi Nitish,

Awesome work man !!!!

Thanks a ton.

LikeLiked by 1 person

Reply
- Nitish Srivastava says:
  
  July 20, 2016 at 1:04 PM
  
  Thanks for reading Rohan, glad that you liked it.
  
  Regards
  Nitish
  
  LikeLike
  
  Reply
Satyajit says:

July 21, 2016 at 12:39 PM

gr8 help Nitish… really useful…

LikeLike

Reply
- Nitish Srivastava says:
  
  July 22, 2016 at 12:08 PM
  
  Thanks Satyajit, good to hear that you found it useful.
  
  Regards
  Nitish
  
  LikeLike
  
  Reply
Naresh says:

November 13, 2016 at 12:08 PM

Nice Explanation.

LikeLike

Reply
- Nitish Srivastava says:
  
  January 6, 2017 at 7:33 AM
  
  Thanks Naresh
  
  LikeLike
  
  Reply
Gunny says:

December 19, 2016 at 8:08 AM

Hi Nitin,

Did you try replicating New Users, Procedures and Tablespace etc..If yes then let me know too.

Thanks!
Gunny

LikeLike

Reply
- Nitish Srivastava says:
  
  January 6, 2017 at 8:11 AM
  
  Hi Gunny,
  
  I’ll try to cover a post explicitly to cover this, as I want to cover some restrictions and issues in configuring that as well… Stay Tuned.
  
  Regards
  Nitish, where Nitin != Nitish 😉
  
  LikeLike
  
  Reply
  - Gaurav Bhaskar says:
    
    January 14, 2017 at 11:07 AM
    
    Cool Nitish. Sorry for misspell.
    
    LikeLike
Sudheer Godgeri says:

March 13, 2017 at 1:36 PM

Good intro to starting up goldgengate replicqtion.. Thanks Nitish.

LikeLike

Reply
arun singh says:

March 13, 2017 at 5:26 PM

i think intitial load or expdp/impdp not shown here..how to do it

LikeLike

Reply
Odbayar says:

April 5, 2017 at 9:57 AM

REPLICATE REP1 and EXTRACT EXT1 and PUMP1 cannot start, how can I start REP1, EXT1 and PUMP1?
Please help me 🙂

LikeLike

Reply
- Gunny says:
  
  April 5, 2017 at 12:56 PM
  
  Post your actual error to help you out.
  
  LikeLike
  
  Reply
Balu says:

June 21, 2017 at 2:26 PM

Hi Nitish,

While start replicat rep1, I am getting the below error . Could u plz help me here.

ERROR OGG-10152 Oracle GoldenGate Delivery for Oracle, rep1.prm: (rep1.prm) line 4: Parsing error, option [megabytes] for parameter [discardfil
e] has unrecognized value “DDL”

Rep1 parameter file:

REPLICAT rep1
ASSUMETARGETDEFS
USERID target, PASSWORD target
discardfile /u02/app/ogg/discard/rep1_discard.txt, append, megabytes 10 DDL
map source.*, target target.*;

LikeLiked by 1 person

Reply
HariKrishna says:

August 7, 2017 at 10:18 AM

Hi Nitish,
Thanks for the great post , could you please help me how to configure in RAC with ASM database to standalone target.

LikeLiked by 1 person

Reply
Ramesh says:

November 2, 2017 at 4:07 PM

Fanastic Nitish – Excellent Job, also if possible please include how to replication for whole database – is there is way or we need to do schema wise or tablespace wise to achieve whole database replication – please explore it

LikeLiked by 1 person

Reply
Satish says:

March 22, 2018 at 6:19 PM

Excellent Job Nitish, these steps are simple and precise.

LikeLike

Reply
rehan says:

May 13, 2018 at 9:20 PM

can you send me scripts ?

LikeLike

Reply
adidas neo baskets advantage clean femme says:

December 21, 2018 at 11:27 AM

Wow that was strange. I just wrote an really long comment but after I clicked submit my comment didn’t show up. Grrrr… well I’m not writing all that over again. Anyway, just wanted to say superb blog!

LikeLike

Reply
matt ryan stitched jersey says:

December 27, 2018 at 5:48 AM

Hello there! I could have sworn I’ve been to this website before but after checking through some of the post I realized it’s new to me. Anyways, I’m definitely happy I found it and I’ll be bookmarking and checking back often!

LikeLike

Reply
Sibasankar says:

May 8, 2019 at 9:49 AM

Hi I am new to oracle golden gate and trying to configure golden gate 12c on windows server 2012 r2. I am getting below error when i start pump service please help me to resolve the issue.

< b main a… sggparam/paramdict.cpp sggs::ggparam::dict::read sIGNORE_UNRECOGNIZED: %s d— c INHERIT ggparam.dictionary.main cßWARN e¯IÒ\K½ soff A… e¯IÒ\…h
soff aB sggparam/paramrepo.cpp sggs::ggparam::repo::read sParam read: %s with %d opts d• C ggparam.repo suserid j AB srmthost j at S S sError reading param repo file. error %s(%d) e¯IÒ\°¥
sNo error j a S S s%s sAttempt to load parameter [_unified_report] with ID# 1,945 failed. No parameter with that name matches the current running configuration. an S sggs::ggparam::dict::get_optlock S D— sError loading parameter with ID# 1945 from the repository file. At D• e¯IÒ\¸â
sNo error j A sAttempt to load parameter [cpu] with ID# 211 failed. No parameter with that name matches the current running configuration. An
D— sError loading parameter with ID# 211 from the repository file. At D• e¯IÒ\¸ sNo error j A sAttempt to load parameter [pri] with ID# 212 failed. No parameter with that name matches the current running configuration. An
D— sError loading parameter with ID# 212 from the repository file. a s../gglib/ggapp/CParamFile.cpp sCParamFile::getNextCommand s%s d˜ C ggapp.CParamFile uE X T R A C T p u m p 1 At D• e¯IÒ\n\ sNo error j A sAttempt to load parameter [maxetcheckptsecs] with ID# 1,010 failed. No parameter with that name matches the current running configuration. An D— sError loading parameter with ID# 1010 from the repository file. At D• sNo error j A sAttempt to load parameter [ptkirstatsfrequency] with ID# 1,018 failed. No parameter with that name matches the current running configuration. An D— sError loading parameter with ID# 1018 from the repository file. At D• e¯IÒ\•š sNo error j A sAttempt to load parameter [ptkcapturebatchsql] with ID# 1,017 failed. No parameter with that name matches the current running configuration. An D— sError loading parameter with ID# 1017 from the repository file. At D• sNo error j A sAttempt to load parameter [_low_watermark_update_frequency] with ID# 1,917 failed. No parameter with that name matches the current running configuration. An D— sError loading parameter with ID# 1917 from the repository file. At D• sNo error j A sAttempt to load parameter [_print_waiting_transactions] with ID# 1,933 failed. No parameter with that name matches the current running configuration. An D— sError loading parameter with ID# 1933 from the repository file. At D• sNo error j A sAttempt to load parameter [_ignore_cdrinfo_entries] with ID# 1,932 failed. No parameter with that name matches the current running configuration. An D— sError loading parameter with ID# 1932 from the repository file. At ! D• sNo error j A ” sAttempt to load parameter [_purge_old_cdrinfo_entries] with ID# 1,936 failed. No parameter with that name matches the current running configuration. An # D— sError loading parameter with ID# 1936 from the repository file. At $ D• sNo error j A % sAttempt to load parameter [_only_single_pdb_or_no_local_undo] with ID# 1,907 failed. No parameter with that name matches the current running configuration. An & D— sError loading parameter with ID# 1907 from the repository file. At ‘ D• sNo error j A ( sAttempt to load parameter [cryptoengine] with ID# 1,657 failed. No parameter with that name matches the current running configuration. An ) D— sError loading parameter with ID# 1657 from the repository file. At * D• sNo error j A + sAttempt to load parameter [sharding] with ID# 1,600 failed. No parameter with that name matches the current running configuration. An , D— sError loading parameter with ID# 1600 from the repository file. At – D• e¯IÒ\—Ö sNo error j A . sAttempt to load parameter [sourceistable] with ID# 277 failed. No parameter with that name matches the current running configuration. An / D— sError loading parameter with ID# 277 from the repository file. At 0 D• e¯IÒ\ö sNo error j A 1 sAttempt to load parameter [sourceisfile] with ID# 276 failed. No parameter with that name matches the current running configuration. An 2 D— sError loading parameter with ID# 276 from the repository file. At 3 D• e¯IÒ\€ sNo error j A 4 sAttempt to load parameter [nouserid] with ID# 138 failed. No parameter with that name matches the current running configuration. An 5 D— sError loading parameter with ID# 138 from the repository file. At 6 D• e¯IÒ\7Ë sNo error j A 7 sAttempt to load parameter [threadoptions] with ID# 333 failed. No parameter with that name matches the current running configuration. An 8 D— sError loading parameter with ID# 333 from the repository file. At 9 D• e¯IÒ\O
sNo error j A : sAttempt to load parameter [rmttask] with ID# 253 failed. No parameter with that name matches the current running configuration. An ; D— sError loading parameter with ID# 253 from the repository file. At < D• e¯IÒ\EE
sNo error j A = sAttempt to load parameter [br] with ID# 1 failed. No parameter with that name matches the current running configuration. An > D— sError loading parameter with ID# 1 from the repository file. At ? D• sNo error j A @ sAttempt to load parameter [excludehiddencolumns] with ID# 1,706 failed. No parameter with that name matches the current running configuration. An A D— sError loading parameter with ID# 1706 from the repository file. A B D˜ uu s e r i d G G A T E , p a s s w o r d * * * aö C sggdbora/ocicpp/ORAConnection.cpp sggs::gglib::ggocicpp::ORAConnection::initConn sconnect as charset %d ncharset %d dŠ C ocicpp.oraconn cïERROR e¯IÒ\Þ
j j aò D sgglog/LegacyErrorHandling.cpp slogMessage s%-7s %9s %s, %s: %s. dž C ggserr.log CßsWARNING sOGG-02551 sOracle GoldenGate Capture for Oracle sPUMP1.prm sORACLE_HOME is not set to Oracle software directory a E S S
sreconnect as db charset %d DŠ Cïe¯IÒ\€ð jà’ A F D˜ Cße°IÒ\Lß ur m t h o s t 1 9 2 . 1 6 8 . 3 0 . 6 8 , m g r p o r t 7 8 0 9 A G e°IÒ\i ur m t t r a i l d : \ O r a c l e \ p r o d u c t \ g g \ d i r d a t / t r A H e°IÒ\CY uP A S S T H R U A I ut a b l e z p m s . * ; A J uN O T C P S O U R C E T I M E R Aò K Dž e°IÒ\çø sWARNING sOGG-01842 sOracle GoldenGate Capture for Oracle sPUMP1.prm sCACHESIZE PER DYNAMIC DETERMINATION (16G) LESS THAN RECOMMENDED: 64G (64bit system)
vm found: 20.38G
Check swap space. Recommended swap/extract: 128G (64bit system) Aò L CïeÃIÒ\ñÈ sERROR sOGG-25127 sOracle GoldenGate Capture for Oracle sPUMP1.prm sReceived an error reply requesting a graceful shutdown. (Reply received is ‘GSOutput file d:\Oracle\product\gg\dirdat/tr000000 is not in any allowed output directories.’.) >

LikeLike

Reply
Veronika Tyurina says:

September 13, 2019 at 3:16 PM

Where could I look code in the files:
SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggate;
SQL> @ddl_enable.sql

LikeLike

Reply
Purushotham says:

February 23, 2020 at 5:12 AM

to create replication process, do we need to login as ggate user in target server?

LikeLike

Reply
Abdul Taher Iqbal says:

May 28, 2020 at 4:24 PM

Hi Nitish Srivastava,

Thanks for your useful notes, but i have question ./Global should be source or target machine needs to be create and when i am trying add rep1 its getting error !!! Please guide me

GGSCI (server2.localdomain as ggsadmin@racdb2) 12> add replicat rep1, exttrail /u01/ggs/dirdat/tr, begin now
ERROR: No checkpoint table specified for ADD REPLICAT.

LikeLike

Reply