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.
DDLgggoldengatereplication GOLDENGATE