GoldenGate replication with source and target on the same physical host

GoldenGate replication with source and target on the same physical host
I have been asked this question several times about using GoldenGate to replicate between source and target Oracle databases located on the same physical server. Do we need two manager processes, or do we need two separate Goldengate environments or what happens if one database is a version 11g database and the other is 10g – do we need to install two versions of GoldenGate and so on and so forth.

To answer these common questions:

1) You do not need two manager processes configured. In fact, I am not sure if we can have multiple manager processes on the same GoldenGate environment.
2) You do not need to install two GoldenGate environments in this particular case– one for source and one for target
3) You can replicate data from 11g to 10g and vice versa as well.
4) Both extract and replicat processes can run on the same host with one manager process for both

Let us test this with a simple example.

We have a table called MYOBJECTS existing in both source database (11.2.0.2) and target database (10.2.0.4). At present, both the tables have no rows. We will insert rows into the 11g database and see them propagated to the 10g target database on the same host.

SQL> desc  myobjects;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 OBJECT_ID                                 NOT NULL NUMBER

SQL> select count(*) from myobjects;

  COUNT(*)
----------
         0
We create an extract called extlocal and a remote trail file (even though there is actually no ‘remote’ machine) – note the directory path pertains to a location on the source database machine. We are dealing with just one server here which acts as both a local as well as remote server as far as GoldenGate is concerned.

GGSCI (sunos01) 25> add extract extlocal tranlog begin now

2011-08-12 09:03:50  INFO    OGG-01749  Successfully registered EXTRACT EXTLOCAL to start managing log retention at SCN 10185228.
EXTRACT added.


GGSCI (sunos01) 26> add rmttrail ./dirdat/aa extract extlocal

RMTTRAIL added.

These are the contents of the extract parameter file – note rmthost points to the ‘local’ machine.

However, since we have both the source and target database on the same host, we need to define the source database which GoldenGate will connect to using the setenv keyword.


extract extlocal
userid ggs_owner, password ggs_owner
setenv (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_2")
setenv (ORACLE_SID="eleven")
rmthost sunos01 , mgrport 7809
rmttrail ./dirdat/aa
table ggs_owner.myobjects;

We then add a replicat group replocal, which will read from the trail file located on the same server where the extract process is running. So in essence, we are running the extract and replicat processes on the same host!

GGSCI (sunos01) 1> add replicat replocal exttrail ./dirdat/aa
REPLICAT added.

These are the contents of the replicat parameter file – we now are setting the environment for the Oracle 10g database which will be the target.

REPLICAT replocal
SETENV (ORACLE_HOME="/u01/app/oracle/product/10.2.0/dbhome_1")
SETENV (ORACLE_SID="tentwo")
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
MAP ggs_owner.myobjects, TARGET ggs_owner.myobjects;
We now start the extract process.

GGSCI (sunos01) 27> start extract extlocal

Sending START request to MANAGER ...
EXTRACT EXTLOCAL starting
Also, start the replicat process.

GGSCI (sunos01) 2> start replicat replocal

Sending START request to MANAGER ...
REPLICAT REPLOCAL starting
Check the status and we see both are running now (on the same server)

GGSCI (sunos01) 6> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXTLOCAL    00:00:00      00:00:13
REPLICAT    RUNNING     REPLOCAL    00:00:31      00:00:03
Now make some changes in the 11g source database ….

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

SQL> insert into myobjects
  2  select owner,object_name,object_id from dba_objects where object_id is not null;

56656 rows created.

SQL> commit;

Commit complete.
Connect to the target 10g database and we see that the rows have been inserted into the table

devastator:/export/home/oracle/gg $ sqlplus ggs_owner/ggs_owner

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Aug 15 14:28:47 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


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(*)
----------
     56656
Let us use the STATS command to verify that the replicat process has actually done the inserts.

GGSCI (devastator) 4> stats replicat replocal latest

Sending STATS request to REPLICAT REPLOCAL ...

Start of Statistics at 2011-08-15 14:30:11.

Replicating from GGS_OWNER.MYOBJECTS to GGS_OWNER.MYOBJECTS:

*** Latest statistics since 2011-08-15 14:28:20 ***
        Total inserts                            56656.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                         56656.00

Thanks for "http://gavinsoorma.com/"