Pre-Requisities Software
Source Database
Target Database
GoldenGate Software
Source Host machine :- db(10.10.13.11) Oracle_SID=SRC
Target Host machine :- db2(10.10.13.12) Oracle_SID=TAR
The goal - replicate all changes in one scheme also support DDL from one Oracle DB host(db) to another Oracle DB host(db2).
Assume :- Both Source and Target DB are ready And want to replicate Source schema(us01) on host DB to Target Schema (us01)
Step 1:-Install Golden Gate software on both Source and Target Oracle DB host
[oracle@db]$ mkdir /u01/app/goldengate
Copy the download file to the new directory and unzip using:
[oracle@db]$ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
output is as :-
inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar
inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
inflating: Oracle GoldenGate 11.2.1.0.1 README.txt
inflating: Oracle GoldenGate 11.2.1.0.1 README.doc
The tar archive is around 230MB in size.
Extract the files from the tar archive using:
[oracle@db]$ tar xfv fbo_ggs_Linux_x64_ora11g_64bit.tar
Configure Environment Variables
The /home/oracle/.bash_profile should already contain the following environment variables.
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 export PATH=$ORACLE_HOME/bin:$PATH
On the source server add the following entries to /home/oracle/.profile
export ORACLE_SID=SRC export PATH=/home/oracle/goldengate:$PATH export LD_LIBRARY_PATH=/u01/app/goldengate:$ORACLE_HOME/lib
Ensure the environment variables are set using
[oracle@db]$ source /home/oracle/.bash_profile
On the target server add the following entries to /home/oracle/.profileexport ORACLE_SID=TAR export PATH=/u01/app/goldengate:$PATH export LD_LIBRARY_PATH=/u01/app/goldengate:$ORACLE_HOME/lib
1.1:-Create Golden Gate Subdirectories
On Both(Source and Target) server create subdirectories for GoldenGate using the CREATE SUBDIRS command. For example:
[oracle@db]$ cd /u01/app/goldengate
[oracle@db goldengate]$ ./ggsciGGSCI (db) 1> CREATE SUBDIRS
Creating subdirectories under current directory /u01/app/goldengateOutput is as :- Parameter files /u01/app/goldengate/dirprm: already exists Report files /u01/app/goldengate/dirrpt: created Checkpoint files /u01/app/goldengate/dirchk: created Process status files /u01/app/goldengate/dirpcs: created SQL script files /u01/app/goldengate/dirsql: created Database definitions files /u01/app/goldengate/dirdef: created Extract data files /u01/app/goldengate/dirdat: created Temporary files /u01/app/goldengate/dirtmp: created Stdout files /u01/app/goldengate/dirout: created
Now Your Golden gate software has been installed
2. Prepare the source database (host db) and Target 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.Forced logging should be enabled on the source database to ensure that all changes are written to the redo logs. DBCA does not enable force logging during database creation.SQL> ALTER DATABASE FORCE LOGGING;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 goldengate datafile '/u01/app/oracle/product/11.2.0/oradata/goldengate01.dbf' size 100m autoextend on;On the target server create a new tablespace for the GoldenGate objects. Again ensure that AUTOEXTEND is enabled.
SQL> create tablespace goldengate datafile '/u01/app/oracle/product/11.2.0/oradata/goldengate01.dbf' size 100m autoextend on;
3. Create the schemes for replication on both hosts (db, db2). 3.1. Source database (db):SQL> create user us01 identified by us01 default tablespace users temporary tablespace temp; User created. SQL> grant connect,resource,unlimited tablespace to us01; Grant succeeded. SQL>3.2. Target database(db2):SQL> create user us01 identified by us01 default tablespace users temporary tablespace temp; User created. SQL> grant connect,resource,unlimited tablespace to us01; Grant succeeded. SQL> grant dba to us01; -- or particular grants Grant succeeded. SQL>As these are test databases, security is not an issue and therefore DBA privilege has been granted to the new user.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/goldengate/dirdat/tr [oracle@db2 ~] mkdir /u01/app/goldengate/dirdat/tr [oracle@db2 ~] mkdir /u01/app/goldengate/discard5. Listener Services Should be Running on both server6.Configure GoldenGate Process like Manager,extract ,datapump on SourceRun 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 gg01@tns_entry Password: Successfully logged into database. GGSCI (db.us.oracle.com) 5> ADD SCHEMATRANDATA us01 2012-12-06 16:23:20 INFO OGG-01788 SCHEMATRANDATA has been added on us01 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 ex1, tranlog, begin now EXTRACT added. GGSCI (db.us.oracle.com) 7> add exttrail /u01/app/golgengate/dirdat/tr, extract ex1 EXTTRAIL added. GGSCI (db.us.oracle.com) 8> edit params ex1 extract ex1 userid gg01@tns_entry, password gg01 exttrail /u01/app/golgengate/dirdat/tr ddl include mapped objname us01.*; table us01.*; GGSCI (db.us.oracle.com) 9> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED EX1 00:00:00 00:01:29 GGSCI (db.us.oracle.com) 10> add extract dp1, exttrailsource /u01/app/goldengate/dirdat/tr , begin now EXTRACT added. GGSCI (db.us.oracle.com) 11> add rmttrail /u01/app/goldengate/dirdat/tr, extract dp1 RMTTRAIL added. GGSCI (db.us.oracle.com) 12> edit params dp1 EXTRACT pump1 USERID gg01, PASSWORD gg01 RMTHOST 10.10.13.12, MGRPORT 7809 RMTTRAIL /u01/app/goldengate/dirdat/tr PASSTHRU table us01.*; GGSCI (db.us.oracle.com) 13> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED EX1 00:00:00 00:02:33 EXTRACT STOPPED dp1 00:00:00 00:02:56 GGSCI (db.us.oracle.com) 14>5.2. Configure the target database (db2). Configure the manager process[oracle@db2 goldengate]$ ./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 GGSCHEMA gg01CHECKPOINTTABLE gg01.checkpoint GGSCI (db2) 5> dblogin userid gg01@tns_entry Password: Successfully logged into database. GGSCI (db2) 6> add checkpointtable gg01.checkpoint Successfully created checkpoint table target.checkpoint. GGSCI (db2) 7>Add replicat.GGSCI (db2) 8> add replicat rep1, exttrail /u01/app/goldengate/dirdat/tr, begin now REPLICAT added. GGSCI (db2) 9> edit params rep1 REPLICAT rep1 ASSUMETARGETDEFS USERID us01@tns_entry, PASSWORD us01 discardfile /u01/app/goldengate/discard/rep1_discard.txt, append, megabytes 10 DDLHANDLECOLLISIONS map us01.*, target us01.*,REPERROR(-1403,EXCEPTION); 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 ex1 Sending START request to MANAGER ... EXTRACT EX1 starting GGSCI (db.us.oracle.com) 7> start extract dp1 Sending START request to MANAGER ... EXTRACT dp1 starting GGSCI (db.us.oracle.com) 8> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EX1 00:00:00 00:00:01 EXTRACT RUNNING dp1 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 goldengate]$ sqlplus us01/us01 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 goldengate]$ sqlplus us01/us01 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>Command Prompt Output file Host DB(db)GGSCI (gg1) 4> start manager Manager started. GGSCI (gg1) 5> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING GGSCI (gg1) 6> dbloging userid gg01@src ERROR: Invalid command. GGSCI (gg1) 7> dblogin userid gg01@src Password: Successfully logged into database. GGSCI (gg1) 8> ADD SCHEMATRANDATA us01 2013-11-27 01:04:14 INFO OGG-01788 SCHEMATRANDATA has been added on schema us01. GGSCI (gg1) 9> add extract ex1 tranlog, begin now EXTRACT added. GGSCI (gg1) 10> add exttrail /u01/app/goldengate/dirdat/tr,extract ex1 EXTTRAIL added. GGSCI (gg1) 11> edit params ex1 GGSCI (gg1) 12> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED EX1 00:00:00 00:03:28 GGSCI (gg1) 13> add extract dp1,exttrailsource /u01/app/goldengate/dirdat/tr ,begin now EXTRACT added. GGSCI (gg1) 14> GGSCI (gg1) 14> GGSCI (gg1) 14> GGSCI (gg1) 14> GGSCI (gg1) 14> add rmttrail /u01/app/goldengate/dirdat/tr,extract dp1 RMTTRAIL added. GGSCI (gg1) 15> edit params dp1 GGSCI (gg1) 16> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED DP1 00:00:00 00:02:32 EXTRACT STOPPED EX1 00:00:00 00:10:00 GGSCI (gg1) 17> start extract ex1 Sending START request to MANAGER ... EXTRACT EX1 starting GGSCI (gg1) 18> start extract dp1 Sending START request to MANAGER ... EXTRACT DP1 starting GGSCI (gg1) 19> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING DP1 00:00:00 00:00:02 EXTRACT RUNNING EX1 00:00:00 00:00:09 GGSCI (gg1) 20> stop dp1 Sending STOP request to EXTRACT DP1 ... Request processed. GGSCI (gg1) 21> stop ex1 Sending STOP request to EXTRACT EX1 ... Request processed. GGSCI (gg1) 22> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED DP1 00:00:00 00:00:13 EXTRACT STOPPED EX1 00:00:00 00:00:06 GGSCI (gg1) 23> start ex1 Sending START request to MANAGER ... EXTRACT EX1 starting GGSCI (gg1) 24> start extract dp1 Sending START request to MANAGER ... EXTRACT DP1 starting GGSCI (gg1) 25> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING DP1 00:00:00 00:03:49 EXTRACT RUNNING EX1 00:00:00 00:00:00 GGSCI (gg1) 26> start ex1 EXTRACT EX1 is already running. GGSCI (gg1) 27> start dp1 EXTRACT DP1 is already running. GGSCI (gg1) 28> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING DP1 00:00:00 00:00:09 EXTRACT RUNNING EX1 00:00:00 00:00:03 GGSCI (gg1) 29> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING DP1 00:00:00 00:00:08 EXTRACT RUNNING EX1 00:00:00 00:00:04 GGSCI (gg1) 30> info trandata us01 ERROR: No viable tables matched specification. GGSCI (gg1) 31> info trandata us01.t1 Logging of supplemental redo log data is enabled for table US01.T1. Columns supplementally logged for table US01.T1: OBJECT_ID. GGSCI (gg1) 32> info trandata us01.t2 Logging of supplemental redo log data is disabled for table US01.T2. GGSCI (gg1) 33> stop ex1 Sending STOP request to EXTRACT EX1 ... Request processed. GGSCI (gg1) 34> stop dp1 Sending STOP request to EXTRACT DP1 ... Request processed. GGSCI (gg1) 35> start ex1 Sending START request to MANAGER ... EXTRACT EX1 starting GGSCI (gg1) 36> start dp1 Sending START request to MANAGER ... EXTRACT DP1 starting GGSCI (gg1) 37> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING DP1 00:00:00 00:00:12 EXTRACT RUNNING EX1 00:00:00 00:00:05 GGSCI (gg1) 38> edit params ex1 GGSCI (gg1) 39> edit params dp1 GGSCI (gg1) 40>Command prompt output for db2Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT STOPPED REP1 00:00:00 00:02:52 GGSCI (gg2) 29> start replicat rep1 Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI (gg2) 30> info al ERROR: Invalid command. GGSCI (gg2) 31> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REP1 00:00:00 00:00:05 GGSCI (gg2) 32> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT ABENDED REP1 00:00:07 01:19:43 GGSCI (gg2) 33> start rep1 Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI (gg2) 34> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT ABENDED REP1 01:19:56 00:00:03 GGSCI (gg2) 35> stop replicat rep1 REPLICAT REP1 is already stopped. GGSCI (gg2) 36> start replicat rep1 Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI (gg2) 37> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT ABENDED REP1 01:24:20 00:00:02 GGSCI (gg2) 38> start replicat rep1 Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI (gg2) 39> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT ABENDED REP1 01:26:48 00:00:06 GGSCI (gg2) 40> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT ABENDED REP1 01:26:48 00:05:58 GGSCI (gg2) 41> start replicat rep1 Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI (gg2) 42> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT ABENDED REP1 01:32:53 00:00:02 GGSCI (gg2) 43> edit replicat rep1 ERROR: Invalid command. GGSCI (gg2) 44> edit replicat rep1 ERROR: Invalid command. GGSCI (gg2) 45> edit rep1 ERROR: Invalid command. GGSCI (gg2) 46> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT ABENDED REP1 01:32:53 00:04:32 GGSCI (gg2) 47> edit params rep1 GGSCI (gg2) 48> start replicat rep1 Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI (gg2) 49> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT ABENDED REP1 01:33:15 00:05:28 GGSCI (gg2) 50> start replicat rep1 Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI (gg2) 51> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT ABENDED REP1 01:33:15 00:16:37 GGSCI (gg2) 52> edit params rep1 GGSCI (gg2) 53> start replicat rep1 Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI (gg2) 54> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT ABENDED REP1 01:33:15 00:23:18 GGSCI (gg2) 55> INFO ALL Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT ABENDED REP1 01:33:15 01:04:40 GGSCI (gg2) 56> start replicat rep1 Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI (gg2) 57> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT ABENDED REP1 01:33:15 01:04:52 GGSCI (gg2) 58> GGSCI (gg2) 58> edit params rep1 GGSCI (gg2) 59> start replicat rep1 Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI (gg2) 60> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REP1 00:00:00 00:00:05 GGSCI (gg2) 61> edit mgr ERROR: Invalid command. GGSCI (gg2) 62> edit params mgr GGSCI (gg2) 63> edit params ./GLOBALS GGSCI (gg2) 64> edit params rep1 GGSCI (gg2) 65>
No comments:
Post a Comment