Schema Replication using Oracle Goldengate 11g (Installation & Configuration)

GoldenGate is touted by Oracle as its future for data replication. It will slowly phase out Oracle Streams and will be more closely coupled with Oracle products. Golden Gate is relatively simpler to use and monitor than Oracle streams. Below I will demonstrate how to configure ASYNC Schema Replication one side using Oracle GoldenGate 11g on a Oracle 11gR2 database.

SID of Source : IVRPROD
SID of Target : IVRDR
Source Server name : ivrhodb
Target Server name : ivrdrdb
Source Schema name : IVR
Target Schema name : IVR
Tables : All tables

1. Tell database to log more (supplemental logging ~10% of more redo
writing) on source database only.

alter database add supplemental log data;

2. GoldenGate user creation on source and target side (if on Unix, on windows use same user in ora_dba group with which you installed Oracle)

useradd -d /data/home/gguser gguser

3. Copy the downloaded installation zip file ogg112101_ggs_Windows_x64_ora11g_64bit.zip) to a directory and unzip it.

4. Set the Environment variables in .bash_profile of user gguser on
both source and target database servers.

-- source --
export ORACLE_HOME=/u01/app/oracle/product/10.2.0
export ORACLE_SID=IVRPROD
export LD_LIBRARY_PATH=/u01/app/oracle/product/10.2.0/lib
export PATH=$PATH:$ORACLE_HOME/bin
-- Destination --
export ORACLE_HOME=/u01/app/oracle/product/10.2.0
export ORACLE_SID=IVRDR
export LD_LIBRARY_PATH=/u01/app/oracle/product/10.2.0/lib
export PATH=$PATH:$ORACLE_HOME/bin

5. create the tablespace for both databases (source + target ) like:

Create tablespace ggate datafile ‘/u01/oradata/ggate01.dbf’ size 1000M autoextend on next 100M;

6. GoldenGate schema creation into source and target database

create user ggate identified by ggate default tablespace ggate
temporary tablespace TEMP profile DEFAULT;
alter user ggate QUOTA UNLIMITED ON ggate;
grant CONNECT to ggate;
grant CREATE SESSION to ggate;
grant RESOURCE to ggate;
grant SELECT ANY TABLE to ggate;
grant ALTER SESSION to ggate;
grant CREATE TABLE to ggate;
grant FLASHBACK ANY TABLE to ggate;
grant SELECT ANY DICTIONARY to ggate;
grant DBA to ggate;

logis as sysdba on both source and target run following steps…

SQL> grant execute on utl_file to ggate;
SQL> @C:\ggs_Windows_x64_ora11g_64bit/marker_setup.sql
SQL> @C:\ggs_Windows_x64_ora11g_64bit/ddl_setup.sql
SQL> @C:\ggs_Windows_x64_ora11g_64bit/role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggate;

And more important grant on dictionay views otherwise extract will keep on abending on both source and target schema (in our case IVR and IVR on both source and target. Schema name is same on both databases)

-- on source --
SQL> grant select any dictionary to ivr;
-- on target --
SQL> grant select any dictionary to ivr;

7. Go to installation directory and execute ./ggsci to see if your able to run the golden gate console on both source and target
8.
// Name of schema to be replicated will be IVR residing on IVRPROD database, it will be replicated to same schema name in IVRDR database.Make sure the characterset is same on Source and Target databases. //

-- Source Configuration --
GGSCI (ivrhodb) 1> create subdirs
GGSCI (ivrhodb) 1> status all
GGSCI (ivrhodb) 1> edit params mgr

port 7809
lagreportminutes 5
laginfominutes 1
lagcriticalminutes 2
purgeoldextracts ./dirdat/t*, minkeepdays 2, usecheckpoints

GGSCI (ivrhodb) 1> start mgr
GGSCI (ivrhodb) 1> info all

If manager is running then manager configuration is ok.

GGSCI (ivrhodb) 1> dblogin userid ggate, password ggate
GGSCI (ivrhodb) 1> list tables ivr.*

If you see the list of tables then your configuration is good and you can continue:

GGSCI (ivrhodb) 1> add trandata IVR.*
GGSCI (ivrhodb) 1> info trandata IVR.*

Lets create and configure now the extractor process, edit add these files and save:

GGSCI (ivrhodb) 1> edit params ext1

extract ext1
userid ggate, password ggate
discardfile ./dirrpt/ext1.dsc,purge
reportcount every 15 minutes, rate
exttrail ./dirdat/t1
table IVR.*;

GGSCI (ivrhodb) 1> add extract ext1, tranlog, begin now
GGSCI (ivrhodb) 1> add exttrail ./dirdat/t1, extract ext1, megabytes 100
GGSCI (ivrhodb) 1> status all
GGSCI (ivrhodb) 1> start ext1

Extractor created, now we create the data pump process:

GGSCI (ivrhodb) 1> edit params dpe1

extract dpe1
passthru
rmthost ivrdrdb, mgrport 7809
rmttrail ./dirdat/t1
table IVR.*;

//rmthost is the target server name or ip address. Ensure port 7809 is open on target server

GGSCI (ivrhodb) 1> add extract dpe1, exttrailsource ./dirdat/t1
GGSCI (ivrhodb) 1> add rmttrail ./dirdat/t1, extract dpe1, megabytes 100
GGSCI (ivrhodb) 1> status all

We are not going to start pump process yet, because destination is not configured.

9.
— Destination Configuration —
Target side on your ggsci prompt:

GGSCI (ivrdrdb) 1> create subdirs
GGSCI (ivrdrdb) 1> edit params mgr

port 7809
dynamicportlist 7900-7950
lagreportminutes 5
laginfominutes 1
lagcriticalminutes 2
purgeoldextracts ./dirdat/t*, minkeepdays 2, usecheckpoints

GGSCI (ivrdrdb) 1> start mgr
GGSCI (ivrdrdb) 1> status all
GGSCI (ivrdrdb) 1> view report mgr

Manager created and verified, now we are going to create replicat processes. We’ll create the replicat with the 5 minutes lag, purposely for replication to be behind 5 minutes :

GGSCI (ivrdrdb) 1> edit params rep1

replicat rep1
userid ggate, password ggate
discardfile ./dirrpt/rep1.dsc, purge
assumetargetdefs
reportcount every 15 minutes, rate
batchsql
deferapplyinterval 5 mins
map IVR.*, target IVR.*;

GGSCI (ivrdrdb) 1> add replicat rep1, exttrail ./dirdat/t1
GGSCI (ivrdrdb) 1> ADD CHECKPOINTTABLE GGATE.CHKPTAB

That should be all about configuration , now last two steps, lets start data pump process on source side and replicat process on destination.

-- on source --
GGSCI (ivrhodb) 1> start dpe1


-- on target --
GGSCI (ivrdrdb) 1> start rep1

Now just check statuses on both sides with “status all” command. If everything is running and nothing abandoned you can check event logs on both sides and activity:

stats ext1, totalsonly *, reportrate sec
stats rep1, totalsonly *, reportrate sec
send rep1, status

And we have configured Oracle Golden Gate installation and setup forone asynchronous data replication.

Category: DatabaseUnix

Tags:

6 comments

  1. hi mohammad
    thx for this good note
    when i execute “add replicat rep1, exttrail ./dirdat/t1” in the target , it shows me
    “ERROR: No checkpoint table specified for ADD REPLICAT”
    do you have any idea that what is for ?

    1. You have to add a checkpoint table using command like below on replicat server

      GGSCI > DBLOGIN USERID ggate, PASSWORD ******

      GGSCI > ADD CHECKPOINTTABLE.

      Checkpoint Table is an online synchronisation log which keeps a tab on the position in the trail file from where the Replicat process will start processing after any kind of error or shutdown.

  2. Hi
    I Have 2 Columns In Source Site And Target Site ( Data Type = Number(18,5) In Both Of Them) , When I Insert A Data ( Example : 0.00004 ) In Source Site , I See ( 0.00000 ) In Target Site , While I Have The Other Columns With This Format In This Table , And They Haven’t Any Problem In Replication.
    Can You Help Me?

  3. Sir,

    In target sir, i am getting error,

    GGSCI (hinai.icthealth.com) 3> start mgr

    Manager started.

    GGSCI (hinai.icthealth.com) 4> status all

    Program Status Group Lag at Chkpt Time Since Chkpt

    MANAGER STOPPED

    GGSCI (hinai.icthealth.com) 5> view report mgr

    more: error while loading shared libraries: libncurses.so.3.0: cannot open shared object file: No such file or directory

    GGSCI (hinai.icthealth.com) 14> dblogin userid gguser password gguser
    Successfully logged into database.

    GGSCI (hinai.icthealth.com) 15> ADD CHECKPOINTTABLE GGATE.CHKPTAB

    Successfully created checkpoint table GGATE.CHKPTAB.

    GGSCI (hinai.icthealth.com) 16> add replicat rep1, exttrail ./dirdat/t1
    ERROR: No checkpoint table specified for ADD REPLICAT.

    Thanks,
    MSP

  4. Hi, Is this possible to replicate already created tables (before gg installation) to destination with dml operation. If yes can u suggest where to provide dml configuration.

Leave a Reply

Article by: Shadab Mohammad