- 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
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 ------------ ARCHIVELOGEnable 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.sqlAfter 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/discard5. 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, SQLAs 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).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 * ...
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 RUNNINGCreate 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:066. 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.
