Vishal desai’s Oracle Blog

February 24, 2011

Golden Gate Bidirectional Replication Oracle to Oracle example

Filed under: Golden Gate, Replication — vishaldesai @ 1:28 am

In this blog I will try to show how to setup golden gate homogeneous bidirectional replication (Oracle <-> Oracle).

Software Setup:

Virtual Box 1: OEL 6.0, Oracle 11g, Golden Gate 11, Oracle Instance oralin11g
Virtual Box 2: Windows 2008 Server, Oracle 11g, Golden Gate 11, Oracle Instance orawin11g

Pre-requisites:

Install golden gate and create sub directories.
Create golden gate schema in Oracle Database and grant required permissions.
Turn on database into archive log mode and supplemental logging.

Note: Below configuration is not from production environement. In actual setup you have to consider lot of other things such as performance, conflict resolution, test insert/update/delete, testing actual application workflows etc.

Configuration:

Setup Extract and dump trail on Virtual Box 1 (oralin11g):

GGSCI (linux6) 1> ADD EXTRACT ext1, TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (linux6) 2> ADD EXTTRAIL /app/oracle/ggs/dirdat/lt, EXTRACT ext1
EXTTRAIL added.

GGSCI (linux6) 3> ADD EXTRACT dpump, EXTTRAILSOURCE /app/oracle/ggs/dirdat/lt
EXTRACT added.

GGSCI (linux6) 4> EDIT PARAMS ext1

EXTRACT ext1
USERID ggs_owner, PASSWORD vishal
TRANLOGOPTIONS EXCLUDEUSER ggs_owner
EXTTRAIL /app/oracle/ggs/dirdat/lt
TABLE vdesai.emp;

GGSCI (linux6) 5> ADD RMTTRAIL C:\app\oracle\ggsoracle\dirdat\rt, EXTRACT dpump
RMTTRAIL added.

GGSCI (linux6) 6> EDIT PARAMS dpump

EXTRACT dpump
USERID ggs_owner, PASSWORD vishal
RMTHOST 192.168.56.103, MGRPORT 7809
RMTTRAIL C:\app\oracle\ggsoracle\dirdat\rt
PASSTHRU
TABLE vdesai.emp;

Setup Replicat trail on Virtual Box 2 (orawin11g):

GGSCI (WIN-NE9G39A65OR) 1> ADD REPLICAT rep1, EXTTRAIL C:\app\oracle\ggsoracle\dirdat\rt CHECKPOINTTABLE GGS_OWNER.CHKPTAB
REPLICAT added.

GGSCI (WIN-NE9G39A65OR) 2> EDIT PARAMS rep1

REPLICAT rep1
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD vishal
MAP vdesai.emp, TARGET vdesai.emp;

Setup Extract and dump trail on Virtual Box 2 (orawin11g)

GGSCI (WIN-NE9G39A65OR) 3> ADD EXTRACT ext2, TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (WIN-NE9G39A65OR) 4> ADD EXTTRAIL C:\app\oracle\ggsoracle\dirdat\lt, EXTRACT ext2
EXTTRAIL added.

GGSCI (WIN-NE9G39A65OR) 5> ADD EXTRACT dpump, EXTTRAILSOURCE C:\app\oracle\ggsoracle\dirdat\lt
EXTRACT added.

GGSCI (WIN-NE9G39A65OR) 6> EDIT PARAMS ext2

EXTRACT ext2
USERID ggs_owner, PASSWORD vishal
TRANLOGOPTIONS EXCLUDEUSER ggs_owner
EXTTRAIL C:\app\oracle\ggsoracle\dirdat\lt
TABLE vdesai.emp;

GGSCI (WIN-NE9G39A65OR) 7> ADD RMTTRAIL /app/oracle/ggs/dirdat/rt, EXTRACT dpump

RMTTRAIL added.

GGSCI (WIN-NE9G39A65OR) 8> EDIT PARAMS dpump

EXTRACT dpump
USERID ggs_owner, PASSWORD vishal
RMTHOST 192.168.56.101, MGRPORT 7809
RMTTRAIL /app/oracle/ggs/dirdat/rt
PASSTHRU
TABLE vdesai.emp;

Setup Replicat trail on Virtual Box 1 (oralin11g):

GGSCI (linux6) 7> ADD REPLICAT rep2, EXTTRAIL /app/oracle/ggs/dirdat/rt CHECKPOINTTABLE GGS_OWNER.CHKPTAB
REPLICAT added.

GGSCI (linux6) 8> EDIT PARAMS rep2

REPLICAT rep2
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD vishal
MAP vdesai.emp, TARGET vdesai.emp;

Start Extract & Replicate processes on Virtual Box 1 (oralin11g):

GGSCI (linux6) 46> INFO ALL

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     DPUMP       00:00:00      00:00:47
EXTRACT     STOPPED     EXT1        00:00:00      00:00:49
REPLICAT    STOPPED     REP2        00:00:00      00:00:45    

GGSCI (linux6) 47> START EXTRACT ext1

Sending START request to MANAGER ...
EXTRACT EXT1 starting

GGSCI (linux6) 48> START EXTRACT dpump

Sending START request to MANAGER ...
EXTRACT DPUMP starting

GGSCI (linux6) 49> START REPLICAT rep2

Sending START request to MANAGER ...
REPLICAT REP2 starting

GGSCI (linux6) 50> INFO ALL

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DPUMP       00:00:00      00:00:02
EXTRACT     RUNNING     EXT1        00:00:00      00:00:08
REPLICAT    RUNNING     REP2        00:00:00      00:00:03    

Start Extract and Replicat processes on Virtual Box 2 (orawin11g):

GGSCI (WIN-NE9G39A65OR) 34> INFO ALL

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     DPUMP       00:00:00      00:01:38
EXTRACT     STOPPED     EXT2        00:00:00      00:01:40
REPLICAT    STOPPED     REP1        00:00:00      00:01:41

GGSCI (WIN-NE9G39A65OR) 35> START EXTRACT EXT2

Sending START request to MANAGER ...
EXTRACT EXT2 starting

GGSCI (WIN-NE9G39A65OR) 36> START EXTRACT dpump

Sending START request to MANAGER ...
EXTRACT DPUMP starting

GGSCI (WIN-NE9G39A65OR) 37> START REPLICAT rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting

GGSCI (WIN-NE9G39A65OR) 38> INFO ALL

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DPUMP       00:00:00      00:00:04
EXTRACT     RUNNING     EXT2        00:00:00      00:00:07
REPLICAT    RUNNING     REP1        00:00:00      00:00:07

Testing:

-- Insert into orawin11g database

SQL> select * from vdesai.emp;

no rows selected

SQL> insert into vdesai.emp values (1,'Vishal','Desai');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from vdesai.emp;

    ID FNAME    LNAME
---------- ------------ ------------
     1 Vishal    Desai

-- Verify record on oralin11g database

SQL> select * from vdesai.emp;

        ID FNAME        LNAME
---------- ------------ ------------
         1 Vishal       Desai

-- Insert into oralin11g database

SQL> select * from vdesai.emp;

        ID FNAME        LNAME
---------- ------------ ------------
         1 Vishal       Desai

SQL>
SQL> insert into vdesai.emp values (2,'Bob','Peterson');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from vdesai.emp;

        ID FNAME        LNAME
---------- ------------ ------------
         2 Bob          Peterson
         1 Vishal       Desai

-- Verify record on orawin11g database

SQL> select * from vdesai.emp; 

    ID FNAME    LNAME
---------- ------------ ------------
     2 Bob        Peterson
     1 Vishal    Desai

Advertisements

5 Comments »

  1. Hi Vishal,
    Nice example.
    Thanks,
    Srini

    Comment by Sreenivas — December 29, 2011 @ 6:29 pm

  2. Republicou isso em BLOG DO ALEXANDRE PIRESe comentado:
    Add your thoughts here… (optional)

    Comment by Alexandre Pires — August 7, 2013 @ 3:23 pm

  3. Good demonstration vishal.

    Thanks,
    Sunil

    Comment by Sunil — January 21, 2014 @ 9:59 pm

  4. Reblogged this on EasyOraDBA | Shadab Mohammad.

    Comment by easyoradba — April 5, 2015 @ 11:43 pm

  5. Hi Vishal, can provide us information on how handle collision is performed, which parameter or table level settings does this job in oracle ? I am not seeing any settings related to that stuff in oracle.

    Comment by karthikpatcha (Worked with you at Wells Fargo :)) — December 7, 2016 @ 4:47 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: