Solution for OGG-00516 Fatal error executing DDL replication in Oracle Goldengate
In this blog, we will perform a solution to resolve OGG-00516 Fatal error executing DDL replication.
This error occurs when the Oracle Goldengate REPLICATE process is unable to perform the same DDL on Target DB which was executed on Source DB. In the Target database, you may not create all objects to resolve dependency problems.
OGG Setup – Source (Linux Server)
GGSCI (test-machine01) 72> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EJ173 00:00:00 00:00:05 EXTRACT RUNNING PJ169 00:00:00 00:00:05
OGG Setup – Target (Window Server)
GGSCI (test-machine02) 85> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT ABENDED RJ173 00:00:00 00:47:40
Step 1. REPLICAT Process Abended in Target: As you will notice Replicat RJ173 Status is ABENDED, Check the replicate process rpt file to know the cause of ABEND. Use command show evn to get the rpt file location.
GGSCI (test-machine02) 100> show env Parameter settings: SET SUBDIRS ON SET DEBUG OFF Current directory: F:\ggate\ucasii Using subdirectories for all process files Editor: notepad Reports (.rpt) F:\ggate\ucasii\dirrpt Parameters (.prm) F:\ggate\ucasii\dirprm Replicat Checkpoints (.cpr) F:\ggate\ucasii\dirchk Extract Checkpoints (.cpe) F:\ggate\ucasii\dirchk Process Status (.pcs) F:\ggate\ucasii\dirpcs SQL Scripts (.sql) F:\ggate\ucasii\dirsql Database Definitions (.def) F:\ggate\ucasii\dirdef GGSCI (test-machine02) 101> F:\ggate\ucasii\dirrpt>type RJ1735.rpt 2021-06-03 11:16:53 ERROR OGG-00516 Fatal error executing DDL replication: error [Error code , ORA-04080: trigger 'INSERT_INVOICE' does not exist SQL ALTER TRIGGER UCASII.INSERT_INVOICE DISABLE /* GOLDENGATE_DDL_REPLICATION */], due to explicit ABEND error handling and filter [include all (default)].
Step 2. Temporary Solution: Add parameter: DDLERROR DEFAULT, DISCARD in REPLICAT RJ173 prm file and restart replicat process. And once Replicat is in sync revert back to DDLERROR DEFAULT, ABEND.
Step 3. Permanent Solution: Add parameter: DDLERROR 4080 DISCARD in REPLICAT RJ173 prm file. This parameter will bypass ORA-04080 trigger does not exist (Please note in GoldenGate you need to use 4080 not 04080).
F:\ggate\ucasii\dirprm>type RJ173.prm REPLICAT RJ173 SETENV (NLS_LANG = AMERICAN_AMERICA.AL32UTF8) USERID GGS, PASSWORD ggs DISCARDFILE F:\ggate\ucasii\discard\RJ173.dsc, MEGABYTES 1000, APPEND ALLOWDUPTARGETMAP DBOPTIONS DEFERREFCONST --DBOPTIONS SUPPRESSTRIGGERS DDL INCLUDE MAPPED --DDLERROR DEFAULT, DISCARD --REPERROR DEFAULT, DISCARD DDLERROR 4080 DISCARD DDLERROR DEFAULT, ABEND REPERROR DEFAULT, ABEND
Step 4. Start Replicat Process in Target: Start Replicat process using start <replicat name> command.
GGSCI (test-machine02) 86> start RJ173 Sending START request to MANAGER ... REPLICAT RJ173 starting GGSCI (test-machine02) 90> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RJ173 00:00:00 00:00:04
This document is only for learning purposes and always validate in the LAB environment first before applying in the LIVE environment.
Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.
- Oracle Critical Database Patch ID for July 2023 along with enabled Download Link
- Oracle Critical Database Patch ID for April 2023 along with enabled Download Link
- Oracle Critical Database Patch ID for January 2023 along with enabled Download Link
- How to Find Current SCN in Oracle
- Steps to Apply Combo Patch (Oct 2022) on Clusterware in Two Node RAC in Oracle
We are sorry that this post was not useful for you!
Let us improve this post!
Tell us how we can improve this post?
Hello and welcome to DBsGuru,
I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.
Thanks for the visits!
Share Learn Grow!