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
Recent Comments