AIM: ONE-WAY DDL replication between an Oracle 10gR2 database and an Oracle 11gR2 database.
1. Pre-requisites
2. Stop GoldenGate Process on Source Oracle 10gR2
3. Stop GoldenGate Process on Target Oracle 11gR2
4. Configure DDL setup on Source Oracle 10gR2
A) Turn off the Oracle Recycle Bin
B) Grant GRANT EXECUTE ON UTL_FILE to GATE
C) Run marker_setup script
D) Run ddl_setup script
E) Run role_setup script
F) Run the ddl_enable script
G) Run ddl_pin script
H) Edit Extract (E10G)
5. Configure DDL setup on Target Oracle 11gR2
A) Run role_setup.sql
B) Grant DBA to GATE
C) ENABLE_GOLDENGATE_REPLICATION to TRUE
D) Edit Replicat R11G
6. Start GoldenGate Process on Source Oracle 10gR2
7. Start GoldenGate Process on Target Oracle 11gR2
LIST AVAILABLE TABLES CURRENTLY
a1) List tables on Source 10gR2
a2) List tables on Target 11gR2
b1) Execute CREATE TABLE on the Source Oracle Database 10gR2
b2) Verify CREATE TABLE Changes on Target Oracle Database 11gR2
c1) Execute ALTER TABLE on the Source Oracle Database 10gR2
c2) Verify ALTER TABLE Changes on Target Oracle Database 11gR2
d1) Execute DROP TABLE on the Source Oracle Database 10gR2
d2) Verify DROP TABLE Changes on Target Oracle Database 11gR2
e1) Execute TRUNCATE TABLE on the Source Oracle Database 10gR2
e2) Verify TRUNCATE TABLE Changes on Target Oracle Database 11gR2
http://www.br8dba.com/unidirectional-dml-replication/ OR http://www.br8dba.com/zero-downtime-migration/
2. Stop GoldenGate Process on Source Oracle 10gR2
GGSCI (rac1.rajasekhar.com) 4> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING E10G 57:25:09 00:00:02 EXTRACT RUNNING P10G 00:00:00 57:25:04 GGSCI (rac1.rajasekhar.com) 5> GGSCI (rac1.rajasekhar.com) 9> stop extract P10G Sending STOP request to EXTRACT P10G ... Request processed. GGSCI (rac1.rajasekhar.com) 10> stop extract E10G Sending STOP request to EXTRACT E10G ... Request processed. GGSCI (rac1.rajasekhar.com) 11> stop mgr Manager process is required by other GGS processes. Are you sure you want to stop it (y/n)? y Sending STOP request to MANAGER ... Request processed. Manager stopped. GGSCI (rac1.rajasekhar.com) 12>
3. Stop GoldenGate Process on Target Oracle 11gR2
GGSCI (rac1.rajasekhar.com) 4> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING JAGENT STOPPED REPLICAT RUNNING R11G 00:00:00 00:00:01 GGSCI (rac1.rajasekhar.com) 5> GGSCI (rac1.rajasekhar.com) 6> stop REPLICAT R11G Sending STOP request to REPLICAT R11G ... Request processed. GGSCI (rac1.rajasekhar.com) 7> stop mgr Manager process is required by other GGS processes. Are you sure you want to stop it (y/n)? y Sending STOP request to MANAGER ... Request processed. Manager stopped. GGSCI (rac1.rajasekhar.com) 8> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER STOPPED JAGENT STOPPED REPLICAT STOPPED R11G 00:00:00 00:00:09 GGSCI (rac1.rajasekhar.com) 9>
4. Configure DDL setup on Source Oracle 10gR2
A) Turn off the Oracle Recycle Bin.
*** Recycle Bin can be ON is only for Oracle 11g or above SQL> select name, open_mode from v$database; NAME OPEN_MODE --------- ---------- DB10 READ WRITE SQL> alter system set recyclebin=off scope=both; System altered. SQL>
B) Grant GRANT EXECUTE ON UTL_FILE Privilege to goldengate user
SQL> GRANT EXECUTE ON UTL_FILE TO GATE;
Grant succeeded.
SQL>
[oracle@rac1 10gogg]$ sqlplus / as sysdba SQL> @marker_setup.sql; Marker setup script You will be prompted for the name of a schema for the Oracle GoldenGate database objects. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter Oracle GoldenGate schema name:gate <--- Marker setup table script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GATE MARKER TABLE ------------------------------- OK MARKER SEQUENCE ------------------------------- OK Script complete. SQL>
[oracle@rac1 ~]$ cd /u01/app/oracle/product/10gogg/ [oracle@rac1 10gogg]$ sqlplus / as sysdba SQL> @ddl_setup.sql; Oracle GoldenGate DDL Replication setup script Verifying that current user has privileges to install DDL Replication... You will be prompted for the name of a schema for the Oracle GoldenGate database objects. NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter Oracle GoldenGate schema name:gate <---- Working, please wait ... Spooling to file ddl_setup_spool.txt Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ... Check complete. Using GATE as a Oracle GoldenGate schema name. Working, please wait ... RECYCLEBIN must be empty. This installation will purge RECYCLEBIN for all users. To proceed, enter yes. To stop installation, enter no. Enter yes or no:yes <---- DDL replication setup script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GATE CLEAR_TRACE STATUS: Line/pos Error ---------- ------------------ No errors No errors CREATE_TRACE STATUS: Line/pos Error ---------- ------------------ No errors No errors TRACE_PUT_LINE STATUS: Line/pos Error ---------- ------------------ No errors No errors INITIAL_SETUP STATUS: Line/pos Error ---------- ------------------ No errors No errors DDLVERSIONSPECIFIC PACKAGE STATUS: Line/pos Error ---------- -------------------------- No errors No errors DDLREPLICATION PACKAGE STATUS: Line/pos Error ---------- -------------------------- No errors No errors DDLREPLICATION PACKAGE BODY STATUS: Line/pos Error ---------- -------------------------- No errors No errors DDL IGNORE TABLE ----------------------------------- OK DDL IGNORE LOG TABLE ----------------------------------- OK DDLAUX PACKAGE STATUS: Line/pos Error ---------- ------------------------------- No errors No errors DDLAUX PACKAGE BODY STATUS: Line/pos Error ---------- ------------------------------- No errors No errors SYS.DDLCTXINFO PACKAGE STATUS: Line/pos Error ---------- ------------------------------- No errors No errors SYS.DDLCTXINFO PACKAGE BODY STATUS: Line/pos Error ---------- ------------------------------- No errors No errors DDL HISTORY TABLE ----------------------------------- OK DDL HISTORY TABLE(1) ----------------------------------- OK DDL DUMP TABLES ----------------------------------- OK DDL DUMP COLUMNS ----------------------------------- OK DDL DUMP LOG GROUPS ----------------------------------- OK DDL DUMP PARTITIONS ----------------------------------- OK DDL DUMP PRIMARY KEYS ----------------------------------- OK DDL SEQUENCE ----------------------------------- OK GGS_TEMP_COLS ----------------------------------- OK GGS_TEMP_UK ----------------------------------- OK DDL TRIGGER CODE STATUS: Line/pos Error ---------- ---------------------------- No errors No errors DDL TRIGGER INSTALL STATUS ----------------------------------- OK DDL TRIGGER RUNNING STATUS ----------------------------------- ENABLED STAYMETADATA IN TRIGGER ----------------------------------- OFF DDL TRIGGER SQL TRACING ----------------------------------- 0 DDL TRIGGER TRACE LEVEL ----------------------------------- 0 LOCATION OF DDL TRACE FILE ---------------------------------------------------------------- /u01/app/oracle/product/10g/admin/DB10/udump/ggs_ddl_trace.log Analyzing installation status... STATUS OF DDL REPLICATION ---------------------------------------------------------------- SUCCESSFUL installation of DDL Replication software components Script complete. SQL>
[oracle@rac1 ~]$ cd /u01/app/oracle/product/10gogg/ [oracle@rac1 10gogg]$ sqlplus / as sysdba SQL> @role_setup.sql; GGS Role setup script This script will drop and recreate the role GGS_GGSUSER_ROLE To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.) You will be prompted for the name of a schema for the GoldenGate database objects. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter GoldenGate schema name:gate <----- Wrote file role_setup_set.txt PL/SQL procedure successfully completed. Role setup script complete Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command: GRANT GGS_GGSUSER_ROLE TO <loggedUser> where is the user assigned to the GoldenGate processes. SQL> -- Run below command as advised by above script SQL> GRANT GGS_GGSUSER_ROLE TO gate; <---- Grant succeeded. SQL>
[oracle@rac1 ~]$ cd /u01/app/oracle/product/10gogg/
[oracle@rac1 10gogg]$ sqlplus / as sysdba
SQL> @ddl_enable.sql;
Trigger altered.
SQL>
[oracle@rac1 ~]$ cd /u01/app/oracle/product/10gogg/ [oracle@rac1 10gogg]$ sqlplus / as sysdba SQL> @ddl_pin.sql gate <----- PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. SQL>
GGSCI (rac1.rajasekhar.com) 3> edit params R11G -- Add below parameters ddl include mapped objname cds.* DDLOPTIONS ADDTRANDATA REPORT GGSCI (rac1.rajasekhar.com) 4> view params E10G EXTRACT E10G EXTTRAIL ./dirdat/ea USERID gate, PASSWORD gate TABLE CDS.*; -- Enable DDL ddl include mapped objname cds.* DDLOPTIONS ADDTRANDATA REPORT GGSCI (rac1.rajasekhar.com) 5>
5. Configure DDL setup on Target Oracle 11gR2
-- conn / as sysdba SQL> @role_setup.sql GGS Role setup script This script will drop and recreate the role GGS_GGSUSER_ROLE To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.) You will be prompted for the name of a schema for the GoldenGate database objects. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter GoldenGate schema name:gate Wrote file role_setup_set.txt PL/SQL procedure successfully completed. Role setup script complete Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command: GRANT GGS_GGSUSER_ROLE TO <loggedUser> where is the user assigned to the GoldenGate processes. SQL> SQL> GRANT GGS_GGSUSER_ROLE TO GATE; <--- Grant succeeded. SQL>
SQL> grant dba to gate;
Grant succeeded.
SQL>
C) ENABLE_GOLDENGATE_REPLICATION to TRUE
SQL> show parameter ENABLE_GOLDENGATE_REPLICATION NAME TYPE VALUE ------------------------------------ ----------- ------------ enable_goldengate_replication boolean FALSE <---- SQL> alter system set enable_goldengate_replication=TRUE; System altered. SQL>
-- Add below parameters -- DDL Report DDLOPTIONS REPORT GGSCI (rac1.rajasekhar.com) 52> view params r11g REPLICAT R11G ASSUMETARGETDEFS userid gate, password gate DISCARDFILE ./dirout/receiver.dsc, purge MAP CDS.*, TARGET CDS.*; -- DDL Report DDLOPTIONS REPORT GGSCI (rac1.rajasekhar.com) 53>
6. Start GoldenGate Process on Source Oracle 10gR2
GGSCI (rac1.rajasekhar.com) 5> start mgr Manager started. GGSCI (rac1.rajasekhar.com) 6> start extract E10G Sending START request to MANAGER ... EXTRACT E10G starting GGSCI (rac1.rajasekhar.com) 7> start extract P10G Sending START request to MANAGER ... EXTRACT P10G starting GGSCI (rac1.rajasekhar.com) 8> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING E10G 06:57:03 00:00:07 EXTRACT RUNNING P10G 00:00:00 06:57:13 GGSCI (rac1.rajasekhar.com) 9>
7. Start GoldenGate Process on Target Oracle 11gR2
GGSCI (rac1.rajasekhar.com) 5> start mgr Manager started. GGSCI (rac1.rajasekhar.com) 6> start replicat R11G Sending START request to MANAGER ... REPLICAT R11G starting GGSCI (rac1.rajasekhar.com) 7> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING JAGENT STOPPED REPLICAT RUNNING R11G 00:00:00 00:00:02 GGSCI (rac1.rajasekhar.com) 8>
LIST AVAILABLE TABLES CURRENTLY
a1) List tables on Source 10gR2
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
A TABLE
TEST TABLE
SQL>
a2) List tables on Target 11gR2
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
A TABLE
TEST TABLE
SQL>
b1) Execute CREATE TABLE on the Source Oracle Database 10gR2
SQL> create table b as select * from a; Table created. SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- A TABLE TEST TABLE B TABLE <----- SQL> SQL> select count(*) from b; COUNT(*) ---------- 50605 <---- SQL>
b2) Verify CREATE TABLE Changes on Target Oracle Database 11gR2
SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- A TABLE B TABLE <----- TEST TABLE SQL> select count(*) from b; COUNT(*) ---------- 50605 <--- SQL>
ALTER TABLE OPERATION
c1) Execute ALTER TABLE on the Source Oracle Database 10gR2
SQL> desc test Name Null? Type -------------- -------- ------------- NAME NOT NULL VARCHAR2(10) ROLE VARCHAR2(10) SQL> ALTER TABLE TEST ADD EMPNO VARCHAR2(30); Table altered. SQL>
c2) Verify ALTER TABLE Changes on Target Oracle Database 11gR2
SQL> desc test; Name Null? Type -------------- -------- ------------- NAME NOT NULL VARCHAR2(10) ROLE VARCHAR2(10) EMPNO VARCHAR2(30) <--- SQL>
d1) Execute DROP TABLE on the Source Oracle Database 10gR2
SQL> drop table b; Table dropped. SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- A TABLE TEST TABLE SQL>
d2) Verify DROP TABLE Changes on Target Oracle Database 11gR2
SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- A TABLE BIN$QnU5q0BiTuPgU2UCqMCWyg==$0 TABLE <---- TEST TABLE SQL> SQL> show parameter recyclebin NAME TYPE VALUE ------------ ----------- ------- recyclebin string on <----- SQL>
e1) Execute TRUNCATE TABLE on the Source Oracle Database 10gR2
SQL> truncate table test; Table truncated. SQL> select count(*) from test; COUNT(*) ---------- 0 <---- SQL>
e2) Verify TRUNCATE TABLE Changes on Target Oracle Database 11gR2
SQL> select count(*) from test; COUNT(*) ---------- 0 <---- SQL>
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
Rajasekhar Amudala
Reference:
https://blogs.oracle.com/imc/entry/oracle_goldengate_configuring_ddl_replication
http://www.vitalsofttech.com/configure-goldengate-ddl-replication/
http://goldengatereplication.blogspot.in/2011/12/steps-for-enabling-ddl-replication-with.html
http://www.askmaclean.com/archives/goldengate-best-parameters-test-from-maclean.html
http://gavinsoorma.com/2014/06/adding-new-tables-to-a-goldengate-extract-and-using-the-tableexclude-parameter/
https://www.pythian.com/blog/truncates-sequences-replication-oracle-goldengate/
http://anuj-singh.blogspot.in/2014/11/ora-04098-trigger-sysggsddltriggerbefor.html
http://www.oracle-scn.com/ddl-replication-configuration-of-oracle-goldengate-11g/
http://www.juliandyke.com/Research/GoldenGate/GoldenGateDDLReplication.php