Loading....

I have been asked this question quite a lot as to how do we set up GoldenGate when both the source and target database are physically located on the same server.

I will answer this specific to Oracle as I have not tried it on other RDBMS’s – but I guess conceptually it should be the same.

Basically all we have do is to set up TNS aliases to connect to the source and target databases and make sure that before we do launch GGSCI, we are able to connect to both source and target databases from say SQL*PLUS in the environment from where we are running the GoldenGate software via GGSCI.

If both the source and target databases are Oracle, it would make more sense to perform the initial load using Oracle utilities like say Import/Export, Data Pump, SQL Loader etc because the performance of those utilities are far better as compared to the GoldenGate initial load. I think GoldenGate should come into the picture after the initial data load is done to keep the online changes taking place after that synchronized between source and target.

However just to demonstrate the concept, I will be performing an initial data load using GoldenGate. The source database is an Oracle 11g R2 database ‘ora11g’ and the target database is ‘ora10g’, both running on Solaris 10 box, sun01.

While we could have idealy have two separate GoldenGate installations on the same machine – one for the Oracle 11g database and one for the Oracle 10g database, I am testing the same using a single GoldenGate for Oracle 11g on 64 bit Solaris software installation.

Note however, that I have setup the GoldenGate schema in both source as well as target database and granted the required roles and privileges as well as ensure that the GGS_OWNER schema has got the required privileges on the SYSTEM.MYOBJECTS table which we are replicating here.

sun01:/export/home/oracle/goldengate $ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Solaris, sparc, 64bit (optimized), Oracle 11 on Sep 18 2009 12:50:18

SOURCE

GGSCI (sun01) 9> dblogin userid ggs_owner@ora11g, password ggs_owner
Successfully logged into database.

GGSCI (sun01) 10> versions
Operating System:
SunOS
Version Generic_141444-09, Release 5.10
Node: sun01
Machine: sun4v

Database:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
PL/SQL Release 11.2.0.1.0 – Production
CORE 11.2.0.1.0 Production
TNS for Solaris: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – Production

TARGET

GGSCI (sun01) 7> dblogin userid ggs_owner@ora10g, password ggs_owner
Successfully logged into database.

GGSCI (sun01) 8> versions
Operating System:
SunOS
Version Generic_141444-09, Release 5.10
Node: sun01
Machine: sun4v

Database:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi
PL/SQL Release 10.2.0.4.0 – Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 – Production
NLSRTL Version 10.2.0.4.0 – Production

SOURCE

ADD EXTRACT load1, SOURCEISTABLE

edit params load1

EXTRACT load1
USERID ggs_owner@ora11g, PASSWORD ggs_owner
RMTHOST sun01, MGRPORT 7809
RMTTASK replicat, GROUP load2
TABLE system.myobjects;

TARGET

ADD REPLICAT load2, SPECIALRUN

edit params load2

REPLICAT load2
USERID ggs_owner@ora10g, PASSWORD ggs_owner
ASSUMETARGETDEFS
MAP system.myobjects, TARGET system.myobjects;

SOURCE

GGSCI (sun01) 10> start extract load1

After the extract starts we can see that the extract process is already reading records from the source table which is SYSTEM.MYOBJECTS

GGSCI (sun01) 10> info extract load1
EXTRACT LOAD1 Last Started 2010-08-24 10:22 Status RUNNING
Checkpoint Lag Not Available
Log Read Checkpoint Table SYSTEM.MYOBJECTS
2010-08-24 10:23:21 Record 12269
Task SOURCEISTABLE

GGSCI (sun01) 12> info extract load1

EXTRACT LOAD1 Last Started 2010-08-24 10:22 Status RUNNING
Checkpoint Lag Not Available
Log Read Checkpoint Table SYSTEM.MYOBJECTS
2010-08-24 10:24:03 Record 26004
Task SOURCEISTABLE

GGSCI (sun01) 13> info extract load1

EXTRACT LOAD1 Last Started 2010-08-24 10:22 Status RUNNING
Checkpoint Lag Not Available
Log Read Checkpoint Table SYSTEM.MYOBJECTS
2010-08-24 10:24:45 Record 40207
Task SOURCEISTABLE

TARGET

On the target we can see that the Replicat process is also running – we did not have to explicitly start this initial load replicat process

GGSCI (sun01) 9> info replicat load2

REPLICAT LOAD2 Initialized 2010-08-24 10:21 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:02:06 ago)
Log Read Checkpoint Not Available
Task SPECIALRUN

Once the initial load process has completed, we will see that both the extract as well as the replicat process has stopped.

SOURCE

GGSCI (sun01) 22> info extract load1

EXTRACT LOAD1 Last Started 2010-08-24 10:22 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table SYSTEM.MYOBJECTS
2010-08-24 10:26:30 Record 76358
Task SOURCEISTABLE

TARGET

GGSCI (sun01) 23> send replicat load2 getlag

ERROR: REPLICAT LOAD2 not currently running.

Let us know test the same by connecting to the target database on the same machine

sun01:/export/home/oracle/goldengate $ sqlplus system@ora10g

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 24 10:27:45 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from myobjects;

COUNT(*)
———-
76358

Please follow and like us:

Last Update: May 4, 2017  

August 25, 2010 34 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

3 thoughts on “Using GoldenGate to replicate between databases on same host

  1. Sugandha

    Hi,

    I am new to goldengate. I was trying to use goldengate to replicate data between two databases I created on the same oracle instance Oracle10g on same host. My extract process is abending and the extract report is showing this error message:

    2011-02-12 21:16:25 WARNING OGG-01194 EXTRACT task EMP_REP abended : OCI Error during OCIServerAttach (status = 12505-ORA-12505: TNS:listener does not currently know of SID given in connect descriptor).

    My DB SIDs are xe and n3. I have created corresponding TNS aliases as dbxe and dbn3.

    My extract and replicat params files are:
    EXTRACT emp_ext
    USERID ggate@dbxe, PASSWORD ggate
    RMTHOST Sugandha, MGRPORT 9500
    RMTTASK replicat, GROUP emp_rep
    TABLE u1.EMPLOYEES;

    REPLICAT emp_rep
    USERID ggate@dbn3, PASSWORD ggate
    ASSUMETARGETDEFS
    MAP u1.employees, TARGET u1.employees;

    Please help in this regard

    Thanks,
    Sugandha

  2. Anil

    Does goldengate replication supports for Roles and Privileges? Example: if we grant privilege on a source object, will it reflect on the object at target side?

  3. Gavin Soorma

    Yes – if we have used DDL INCLUDE ALL in the extract parameter file, any system or object level privileges granted on source will be replicated on target

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?