Tutorial 5: How to Configure Goldengate DDL Replication?

Goldengate supports the replication of DDL commands, operating at a schema level, from one database to another.

By default the DDL replication is disabled on the source database (extract side) but is enabled on the target Database (replicat side). Learn more on how to configure Goldengate DDL Replication.

Configure Goldengate DDL Replication

Prerequisite Setup
Navigate to the directory where the Oracle Goldengate software is installed.

Connect to the Oracle database as sysdba.

sqlplus sys/password as sysdba

For DDL synchronization setup, run the marker_setup.sql script. Provide OGG_USER schema name, when prompted.

Here the OGG_USER is the name of the database user, assigned to support DDL replication feature in Oracle Goldengate

SQL> @marker_setup.sql


Then run the ddl_setup.sql script. Provide the setup detail information below.

SQL> @ddl_setup.sql

For 10g:

Schema Name : OGG_USER
Installation mode : initialsetup
To proceed with the installation : yes

For 11g:

Start the installation : yes
Schema Name : OGG_USER
Installation mode : initialsetup

For 12c:

In Oracle database 12c, DDL replication does not require any setup of triggers as it is natively supported at the database level.

So none of the marker, ddl_setup or any of the other scripts need to be run. All that is required is including the “DDL INCLUDE MAPPED” parameter in the Extract parameter file as shown in the last step.

Run the role_setup.sql script. Provide OGG_USER schema name, when prompted.

SQL> @role_setup.sql

Then grant the ggs_ggsuser_role to the OGG_USER.

SQL> grant ggs_ggsuser_role to OGG_USER;

Run the ddl_enable.sql script as shown in below command:

SQL> @ddl_enable;

Run the ddl_pin.sql script as shown below.

SQL> @ddl_pin OGG_USER;


Configure Extract Process with DDL Replication

The following extract ESRC01 was configured previously. Adding “DDL INCLUDE MAPPED” enables extracting the DDL which ran in the database. Here the “MAPPED .. TABLE” are all tables specified in [schema_name].*.

On restart of the ESRC01 process all DDL on the speicfied tables will be picked up and placed in the trail file for applying to the destination database.

EXTRACT ESRC01
USERID OGG_USER PASS_WORD OGG_USER
EXTTRAIL ./dirdat/st
TRANLOGOPTIONS EXCLUDEUSER OGG_USER
DDL INCLUDE MAPPED
TABLE APPOLTP01.*;

Don’t forget to add DDL INCLUDE MAPPED in the Pump and Replicat processes.

DDL replication setup on source completed!

Advanced Replication Series

Configure GoldenGate DDL Replication
GoldenGate Integrated Extract 12c
Configure GoldenGate Data Transformation
Upgrade Classic Extract to Integrated Capture
Oracle GoldenGate 11gr2 Upgrade

Comments

  1. oraclegigs@gmail.com'NR says

    great set of articles!!

    had a question – at a client site when installing OGG, they had a question about using the oraInventory for OGG…they wanted to use a separate inventory for OGG because they were not very happy with 2 different users using the same inventory – gguser and their oracle user..any thoughts ??…is it ok if we use separate inventory location for OGG?

  2. says

    Thanks!

    Well if you are using 12c and the OS users are different then it is okay to separate out the inventory locations. If they are the same users then I would try to use the same Inventory location.

  3. hayathk@hotmail.com'hayath mohammad khan says

    Dear Amin,

    I want to know, that can golden gate can directly read data from offline archive file.
    What items needs to be required even if dumy database needs to be created.

    What contents of Redo it will show,
    How the contents are different from V$LOGMNR_CONTENTS

    • Natik Ameen says

      AOA Hayath – Yes if Extract process is down for some time or if the process lags for any reason, it is automatically able to continue the Extract process from the Archived Logs.

      I hope that helps.

  4. saravanan.kcse@gmail.com'DBA says

    Hi Natik,

    Is Heterogenous replication support DDL?
    I’m working on replicating tables from MySQL to PostgreSQL using Oracle GG(Version 11.2.1.0.2 – GG for Postgres, Version 11.2.1.0.1 – GG for MySQL).
    DML is working perfectly when using sourcedef.

    Thanks,

    • Natik Ameen says

      Hi Saravan,

      Sorry to be the bearer of bad news. It’s not supported as the syntax for the DDL is different for the different databases.

      – Natik Ameen

  5. jamsher.khan2@yahoo.com'Jamsher says

    Dear Mr. Natik,

    I was trying DDL replication with OGG 12.1.2.0.0, But it was not replicating DDL with below parameter
    DDL INCLUDE MAPPED

    Extract was abended with below error.

    INFO OGG-00733 Marker table ggs.GGS_MARKER not found.
    ERROR OGG-00529 DDL Replication is enabled but table ggs.GGS_DDL_HIST is not found. Please check DDL installation in the database.

    But after i executed below two scripts
    SQL> @marker_setup
    SQL> @ddl_setup

    Integrated Extract start replicating DDL command.

    Thanks & Best Regards
    Jamsher

    • Natik Ameen says

      Hi Jamsher. Yes that is correct. Running the “marker_setup.sql” and the “ddl_setup.sql” scripts is part of the prerequisites before extracting DDL using the “DDL INCLUDE MAPPED” in the extract process.

      • amardeep1.kumar@gmail.com'Amardeep Kumar says

        Dear Ameeen,

        Do we need to run below 2 scripts in 12 C oracle database also for ddl mapping ?

        SQL> @marker_setup
        SQL> @ddl_setup

        Thanks.
        Amar

  6. tran.vhit@gmail.com'tranvhit says

    Hi,

    Thank so much for your share. But, now i config DDL for Og12c on database 11gR2.0.0.1 but still ” @ddl_setup.sql” , i have message:
    “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:ogg

    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.

    declare
    *
    ERROR at line 1:
    ORA-20783:
    ORA-20783:
    Oracle GoldenGate DDL Replication setup:
    *** Please move OGG to its own tablespace
    ORA-06512: at line 34

    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    Please help me that.

    • Natik Ameen says

      Hi Tranvhit,

      Please create a new tablespace and assign the tablespace to the GoldenGate user. That should solve the error message.

      Let me know how that works.

      – Natik

  7. kpanthi@outlook.com'KP says

    Hi – Thank you for sharing this with all.
    Just raising a question about DDL replication,
    I believe DDL statements are replicated as is in the target by GG.
    For example,
    Alter table ….. add (column default sysdate);
    Lets say there was a lag in target when this gets fired (10min).
    Column value in target will be 10 min off. Not sure if GG has any internal mechanism to sync it out of the DDL replication execution.
    Thank you
    KP

  8. saurabht904@gmail.com'Sauarabh Tandon says

    Hi ,

    I have done the setup for goldenGate . And my DML changes are reflecting and I did the setup for DDL synchronization after that but I am seeing no changes in DDL.

    May you tell me where I have to check for this.

    Thanks IN Advance

  9. anarayana_rao@yahoo.com'Narayana Rao says

    Hi

    Do we need to have “GGS_DDL_TRIGGER_BEFORE ” in 11.2.0.4 to capture DDL on a 11.2.0.4 downstream DB, which is receiving the ARCHIVES FROM 11.2.0.3 DB.

    as per the oracle note : ” If Extract will run in integrated mode against a version 11.2.0.4 or later Oracle database, the DDL trigger is not required. By default, DDL capture is handled transparently through the database logmining server. ”

    Thanks
    Rao

    • Natik Ameen says

      That’s correct. If Integrated extract is used the trigger is no longer required to capture the DDL as it can be captured natively.

  10. gunnykc@gmail.com'Gunny says

    Hi Ameen,

    Did you try replicating new user or tablespace creation? If yes – can you let me know what to put in EXTRACT and REPLICAT file?

    Thanks!
    Gunny

    • Natik Ameen says

      Hi Gunny, Thanks for stopping by. I am sorry I haven’t tried replicating a new user or TBS yet. I’ll let you know when I have a chance to test this out.

Leave a Reply

Your email address will not be published.