Setting Oracle DB to Oracle DB replication using Golden Gate

In this post I'd like to write about Oracle DB replication by using Golden Gate.I am going to create a Oracle DB replication. Environment (two virtual machines):

• host db (192.168.2.131), OEL 5.2. x86, Oracle DB EE 11.2.0.3.0
• host db2 (192.168.2.132), OEL 5.2. x86, Oracle DB EE 11.2.0.3.0

The goal - replicate all changes in one particular scheme (include DDL) from one Oracle DB host  to another Oracle DB host.

1. First of all, install Golden Gate software on each Oracle DB host. I've already written about it here.

2. Prepare the source database (host db) for replication. Switch the database to archivelog mode:

SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

Enable minimal supplemental logging:

SQL> alter database add supplemental log data;

Database altered.
 
SQL>

Prepare the database for DDL replication. Turn off the recyclebin feature and be sure that it is empty.

SQL> alter system set recyclebin=off scope=spfile;
SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

SQL>

Create a schema that will contain the Oracle GoldenGate DDL objects.

SQL>  create tablespace gg_tbls datafile '/u01/app/oracle/oradata/orcl/gg_tbls.dbf' size 100m reuse autoextend on;

Tablespace created.

SQL>  create user ggate identified by oracle default tablespace gg_tbls quota unlimited on gg_tbls;

User created.

SQL>  grant create session, connect, resource to ggate;

Grant succeeded.

SQL> grant dba to ggate; -- just in case

Grant succeeded.

SQL>  grant execute on utl_file to ggate;

Grant succeeded.

SQL>

Change the directory on Golden Gate home directory and run scripts for creating all necessary objects for support ddl replication.

[oracle@db ~]$ cd /u01/app/oracle/product/gg/
[oracle@db gg]$ sqlplus / as sysdba

SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggate;
SQL> @ddl_enable.sql

After that, add information about your Oracle GoldenGate DDL scheme into file GLOBALS. You should input this row into the file "GGSCHEMA ggate".

[oracle@db gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (db.us.oracle.com) 1> EDIT PARAMS ./GLOBALS
GGSCHEMA ggate

GGSCI (db.us.oracle.com) 2>

3. Create the schemes for replication on both hosts (db, db2).

3.1. Source database (db):  

SQL> create user source identified by oracle default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource,unlimited tablespace to source;

Grant succeeded.

SQL>

3.2. Target database(db2):

SQL> create user target identified by oracle default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource,unlimited tablespace to target;

Grant succeeded.

SQL> grant dba to target; -- or particular grants

Grant succeeded.

SQL>

4. Create the directory for trail files on both hosts and create directory for discard file on db2 host only.

[oracle@db ~]  mkdir /u01/app/oracle/product/gg/dirdat/tr
[oracle@db2 ~] mkdir /u01/app/oracle/product/gg/dirdat/tr
[oracle@db2 ~] mkdir /u01/app/oracle/product/gg/discard

5. Create a standard reporting configuration (picture 1)

 Picture 1

5.1. Configure extracts on source database (db).

Run ggcsi and configure the manager process.

[oracle@db gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (db.us.oracle.com) 1> edit params mgr 
PORT 7809
GGSCI (db.us.oracle.com) 2> start manager

Manager started.

GGSCI (db.us.oracle.com) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

GGSCI (db.us.oracle.com) 4>

Login into database and add additional information about primary keys into log files.

GGSCI (db.us.oracle.com) 4> dblogin userid ggate
Password:
Successfully logged into database.

GGSCI (db.us.oracle.com) 5> ADD SCHEMATRANDATA source

2012-12-06 16:23:20  INFO    OGG-01788  SCHEMATRANDATA has been added on schema source.

GGSCI (db.us.oracle.com) 6>

NOTE. This is a very important step, because if you don't do it you will not be able to replicate Update statements. You will get errors like the following:

OCI Error ORA-01403: no data found, SQL 
Aborting transaction on /u01/app/oracle/product/gg/dirdat/tr beginning at seqno 1 rba 4413
                         error at seqno 1 rba 4413
Problem replicating SOURCE.T1 to TARGET.T1
Record not found
Mapping problem with compressed update record (target format)...
*
ID =
NAME = test3
*
...

As you know, when you write the update statement you usually don't change the primary key, so Oracle log files contain information about changing column values and don't contain information about primary key. For avoiding this situation you should add this information into log files using ADD SCHEMATRANDATA or ADD TRANDATA commands. Add extracts (regular and data pump).

GGSCI (db.us.oracle.com) 6> add extract ext1, tranlog, begin now
EXTRACT added.

GGSCI (db.us.oracle.com) 7> add exttrail /u01/app/oracle/product/gg/dirdat/tr, extract ext1
EXTTRAIL added.

GGSCI (db.us.oracle.com) 8> edit params ext1
extract ext1
userid ggate, password oracle       
exttrail /u01/app/oracle/product/gg/dirdat/tr 
ddl include mapped objname source.*;
table source.*;

GGSCI (db.us.oracle.com) 9> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXT1        00:00:00      00:01:29

GGSCI (db.us.oracle.com) 10> add extract pump1, exttrailsource /u01/app/oracle/product/gg/dirdat/tr , begin now
EXTRACT added.

GGSCI (db.us.oracle.com) 11> add rmttrail /u01/app/oracle/product/gg/dirdat/tr, extract pump1
RMTTRAIL added.

GGSCI (db.us.oracle.com) 12> edit params pump1
EXTRACT pump1
USERID ggate, PASSWORD oracle 
RMTHOST db2, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/tr 
PASSTHRU
table source.*;

GGSCI (db.us.oracle.com) 13> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXT1        00:00:00      00:02:33
EXTRACT     STOPPED     PUMP1       00:00:00      00:02:56

GGSCI (db.us.oracle.com) 14>

5.2. Configure the target database (d2).
Configure the manager process

[oracle@db2 gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.


GGSCI (db2) 1> edit params mgr
PORT  7809

GGSCI (db2) 2> start manager

Manager started.

GGSCI (db2) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

Create the checkpoint table and change the GLOBAL file.

GGSCI (db2) 4> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE target.checkpoint

GGSCI (db2) 5> dblogin userid target
Password:
Successfully logged into database.

GGSCI (db2) 6> add checkpointtable target.checkpoint

Successfully created checkpoint table target.checkpoint.

GGSCI (db2) 7>

Add replicat.

GGSCI (db2) 8> add replicat rep1, exttrail /u01/app/oracle/product/gg/dirdat/tr, begin now
REPLICAT added.

GGSCI (db2) 9> edit params rep1
REPLICAT rep1
ASSUMETARGETDEFS
USERID target, PASSWORD oracle
discardfile /u01/app/oracle/product/gg/discard/rep1_discard.txt, append, megabytes 10
DDL
map source.*, target target.*;

GGSCI (db2) 10> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     REP1        00:00:00      00:01:52

GGSCI (db2) 11>

5.3. Start extracts and replicat.

GGSCI (db.us.oracle.com) 6> start extract ext1

Sending START request to MANAGER ...
EXTRACT EXT1 starting


GGSCI (db.us.oracle.com) 7> start extract pump1

Sending START request to MANAGER ...
EXTRACT PUMP1 starting

GGSCI (db.us.oracle.com) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:01
EXTRACT     RUNNING     PUMP1       00:00:00      00:01:01


GGSCI (db2) 6> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting

GGSCI (db2) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REP1        00:00:00      00:00:06

6. Check Host db.

[oracle@db gg]$ sqlplus source/oracle

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 7 20:09:17 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning and Real Application Testing options

SQL> select * from t1;
select * from t1
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create table t1 (id number primary key, name varchar2(50));

Table created.

SQL> insert into t1 values (1,'test');

1 row created.

SQL> insert into t1 values (2,'test');

1 row created.

SQL> commit;

Commit complete.

SQL>

Check host db2:

[oracle@db2 gg]$ sqlplus target/oracle

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 7 20:09:41 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning and Real Application Testing options

SQL> select * from t1;

        ID NAME
---------- --------------------------------------------------
         1 test
         2 test

SQL>

As you can see, all works. Lets execute some SQL and Update statements of course. Host db:

SQL> delete t1 where id =2;

1 row deleted.

SQL> insert into t1 values (3,'test');

1 row created.

SQL> update t1 set name='test3' where id = 3;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID NAME
---------- --------------------------------------------------
         1 test
         3 test3

SQL>

Let's check host db2:

SQL>  select * from t1;

        ID NAME
---------- --------------------------------------------------
         1 test
         3 test3

SQL>

Conclusion 
Oracle GoldenGate is a software package for enabling the replication of data in heterogeneous data environments. It is easy to use. Of course, in this post I have not consider questions related with initial load, conflict detection, high availability and etc., I am only starting to work with this product and probably write about these features in the future. Additionally, I would recommend you to read Alexander Ryndin's blog  http://www.oraclegis.com/blog/, he is a Golden Gate guru. One more interesting resource is http://gavinsoorma.com/. All the best.