In this below tutorial, we will enable DDL replication for a table, which is already part of goldengate replication and currently only DML transactions are getting replicated.
TABLE_NAME = APPLIANCE.CLASSTAB1
To enable DDL replication, We need to run few SQL scripts(provided under $GG_HOME) on the source database. and update the extract parameter file.
1. Run role_setup.sql where extract is running [ SOURCE ]
conn / as sysdba
cd $GG_HOME 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_USER 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 where is the user assigned to the GoldenGate processes. SQL>
2. Grant the role to GATE_USER.[SOURCE]
SQL> GRANT GGS_GGSUSER_ROLE TO GATE_USER; Grant succeeded.
3.Run the marker script:[SOURCE]
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_USER 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_USER MARKER TABLE ------------------------------- OK MARKER SEQUENCE ------------------------------- OK Script complete.
4. Run ddl_setup.sql script [ SOURCE ]
@ddl_setup.sql
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_USER 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_USER 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 GATE_USER 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 ------------------------------------------------------------------------------------------------------------------------ /oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/log/ggs_ddl_trace.log Analyzing installation status... VERSION OF DDL REPLICATION ------------------------------------------------------------------------------------------------------------------------ OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401 STATUS OF DDL REPLICATION ------------------------------------------------------------------------------------------------------------------------ SUCCESSFUL installation of DDL Replication software components Script complete.
5. Update the extract parameter file to include DDL
We need to add the command DDL INCLUDE MAPPED OBJNAME <SCHEMA_NAME>.<TABLE_NAME>
It will look as below
ggsci> edit params ext1 EXTRACT EXT1 SETENV (ORACLE_SID="SRCDB") SETENV (ORACLE_HOME = "/oracle/app/oracle/product/12.1.0/dbhome_1") SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8") userid ggate, PASSWORD ggate123 FETCHOPTIONS FETCHPKUPDATECOLS, USESNAPSHOT, USELATESTVERSION DBOPTIONS ALLOWUNUSEDCOLUMN DISCARDFILE /ggate/app/oracle/product/12.2.1.0.3/TST/EXT1.dsc, APPEND, MEGABYTES 100 EXTTRAIL /ggate/app/oracle/product/12.2.1.0.3/TST/E5 DDL INCLUDE MAPPED OBJNAME APPLIANCE.CLASSTAB1 TABLE APPLIANCE.CLASSTAB1;
No changes required to extract pump and replicat parameter files.
6. Restart the extract [ SOURCE]
gggsci> stop ext1 ggsci> start ext1 GGSCI> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXT1 00:00:00 00:00:03 EXTRACT RUNNING EXT1P 00:00:00 00:00:01
Now DDL replication is enabled. Do some DDL changes on source.
-- DDL change on source
SQL> alter table appliance.classtab1 add ( SCHOOL varchar2(20));
Table altered.
SQL> desc appliance.classtab1
Name Null? Type
----------------------------------------- -------- ---------------------
SR NUMBER
CLASS VARCHAR2(20)
SCHOOL VARCHAR2(20)
-- Check the status of extract:
GGSCI () 18> stats EXT1
Sending STATS request to EXTRACT EXT1 ...
Start of Statistics at 2017-07-25 10:50:03.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 1.00
Mapped operations 1.00 ---- >>>>>>>>>>>>>>>>>> DDL CHANGE CAPTURES BY EXTRACT.
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
Output to /GGATE_USER/TRAILFILE/S2:
-- Check the table in target db( replicat side)
SQL> desc appliance.classtab1
Name Null? Type
----------------------------------------- -------- ----------------------------
SR NUMBER
CLASS VARCHAR2(20)
SCHOOL VARCHAR2(20)
The column has been added in target database also.
