In this article, I will show how to enable DDL replication for a table or schema which is the part of goldengate replication.
To enable the DDL, first need to execute below mentioned scripts on source database.
1. Scripts Execute.
- marker_setup
- ddl_setup
- role_setup.sql
- ddl_enable.sql
2. Update Extract and PUMP parameter to include DDL (DDL INCLUDE ALL)
3. Validation
4. Enable DDL at Table Level
Execute marker_setup script:
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:GGUSR Marker setup table script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GGUSR MARKER TABLE ------------------------------- OK MARKER SEQUENCE ------------------------------- OK Script complete.
Execute ddl_setup script:
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:GGUSR 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. WARNING: Tablespace TBS_GG does not have AUTOEXTEND enabled. Using GGUSR as a Oracle GoldenGate schema name. Working, please wait ... DDL replication setup script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GGUSR 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 ----------------------------------- NONE LOCATION OF DDL TRACE FILE ------------------------------------------------------------------------------------------------------------------------ /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/log/ggs_ddl_trace.log Analyzing installation status... VERSION OF DDL REPLICATION ------------------------------------------------------------------------------------------------------------------------ OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054 STATUS OF DDL REPLICATION ------------------------------------------------------------------------------------------------------------------------ SUCCESSFUL installation of DDL Replication software components Script complete.
Execute role_setup.sql:
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:GGUSR 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 <loggedUser> is the user assigned to the GoldenGate processes.
SQL> GRANT GGS_GGSUSER_ROLE TO GGUSR; Grant succeeded.
Execute ddl_enable.sql script:
SQL> @ddl_enable.sql Trigger altered.
Update Extract and PUMP parameter to include DDL (DDL INCLUDE ALL):
GGSCI (orclprd.localdomain) 2> edit param EEMP EXTRACT EEMP SETENV (ORACLE_SID=ORCLPRD) USERIDALIAS GGUSR_ORCLPRD WARNLONGTRANS 4H CHECKINTERVAL 5M SKIPEMPTYTRANS BR BROFF CACHEMGR CACHESIZE 4GB, CACHEDIRECTORY /u01/goldengate/gg_dircache 5GB EXTTRAIL /u01/goldengate/db19/dirdat/em #Enable DDL DDL INCLUDE ALL TABLE HR.*;
GGSCI (orclprd.localdomain) 3> edit param PEMP EXTRACT PEMP SETENV (ORACLE_SID=ORCLPRD) PASSTHRU --CACHEMGR CACHESIZE 2GB, CACHEDIRECTORY /u01/goldengate/gg_dircache 4GB EOFDELAYCSECS 1 FLUSHCSECS 1 RMTHOST 192.168.56.20, MGRPORT 7809 RMTTRAIL /u01/gg_target/hr GETUPDATEBEFORES GETUPDATEAFTERS REPORTCOUNT EVERY 10 MINUTES, RATE DDL INCLUDE ALL TABLE HR.*;
Validation:
On Source:
GGSCI (orclprd.localdomain) 10> exit [oracle@orclprd db19]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 23 06:07:48 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> Create table HR.TEST (id number); Table created. SQL> insert into hr.test values(10); 1 row created. SQL> commit; Commit complete.
On Target:
[oracle@orcl19c ogg19]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 23 06:33:32 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select count(*) from hr.test;
COUNT(*)
----------
0
SQL> select count(*) from hr.test;
COUNT(*)
----------
1
Enable DDL at Table Level: (DDL INCLUDE MAPPED OBJNAME)
GGSCI (orclprd.localdomain) 1> view param EEMP EXTRACT EEMP SETENV (ORACLE_SID=ORCLPRD) USERIDALIAS GGUSR_ORCLPRD WARNLONGTRANS 4H CHECKINTERVAL 5M SKIPEMPTYTRANS BR BROFF CACHEMGR CACHESIZE 4GB, CACHEDIRECTORY /u01/goldengate/gg_dircache 5GB EXTTRAIL /u01/goldengate/db19/dirdat/em DDL INCLUDE MAPPED OBJNAME HR.TEST TABLE HR.EMPLOYEES; TABLE HR.DEPARTMENTS; TABLE HR.TEST;
GGSCI (orclprd.localdomain) 2> view param PEMP EXTRACT PEMP SETENV (ORACLE_SID=ORCLPRD) PASSTHRU --CACHEMGR CACHESIZE 2GB, CACHEDIRECTORY /u01/goldengate/gg_dircache 4GB EOFDELAYCSECS 1 FLUSHCSECS 1 RMTHOST 192.168.56.20, MGRPORT 7809 RMTTRAIL /u01/gg_target/hr GETUPDATEBEFORES GETUPDATEAFTERS REPORTCOUNT EVERY 10 MINUTES, RATE DDL INCLUDE MAPPED OBJNAME HR.TEST TABLE HR.EMPLOYEES; TABLE HR.DEPARTMENTS; TABLE HR.TEST;
GGSCI (orclprd.localdomain) 2> stats EEMP
Sending STATS request to EXTRACT EEMP ...
Start of Statistics at 2023-11-23 06:49:43.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 1.00
Mapped operations 1.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
Output to /u01/goldengate/db19/dirdat/em:
Extracting from GGUSR.GGS_MARKER to GGUSR.GGS_MARKER:
*** Total statistics since 2023-11-23 06:49:25 ***
No database operations have been performed.
*** Daily statistics since 2023-11-23 06:49:25 ***
No database operations have been performed.
*** Hourly statistics since 2023-11-23 06:49:25 ***
No database operations have been performed.
*** Latest statistics since 2023-11-23 06:49:25 ***
No database operations have been performed.
End of Statistics.
SQL> select name from v$database;
NAME
---------
ORCL
SQL> select * from hr.test;
ID NAME PHONE
---------- -------------------- ------------------------------
10
