DDL Replication

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

8. Verify

LIST AVAILABLE TABLES CURRENTLY

       a1) List tables on Source 10gR2
       a2) List tables on Target 11gR2

CREATE TABLE OPERATION

        b1) Execute CREATE TABLE on the Source Oracle Database 10gR2
        b2) Verify CREATE TABLE Changes on Target Oracle Database 11gR2

ALTER TABLE OPERATION

        c1) Execute ALTER TABLE on the Source Oracle Database 10gR2
        c2) Verify ALTER TABLE Changes on Target Oracle Database 11gR2

DROP TABLE OPERATION

        d1) Execute DROP TABLE on the Source Oracle Database 10gR2
        d2) Verify DROP TABLE Changes on Target Oracle Database 11gR2

TRUNCATE TABLE OPERATION

        e1) Execute TRUNCATE TABLE on the Source Oracle Database 10gR2
        e2) Verify TRUNCATE TABLE Changes on Target Oracle Database 11gR2


1. Pre-requisites


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>


C) Run marker_setup script

[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>


D) Run ddl_setup script

[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>


E) Run role_setup script

[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>


F) Run the ddl_enable script

[oracle@rac1 ~]$ cd /u01/app/oracle/product/10gogg/
[oracle@rac1 10gogg]$ sqlplus / as sysdba

SQL> @ddl_enable.sql;

Trigger altered.

SQL>


G) Run ddl_pin script

[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>


H) Edit Extract (E10G)

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


A) Run role_setup.sql

-- 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>


B) Grant DBA to GATE

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>


D) Edit Replicat R11G

-- 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>


8. Verify


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>


CREATE TABLE OPERATION


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>


DROP TABLE OPERATION


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>


TRUNCATE TABLE OPERATION


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