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 [4080], 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.

Solution for OGG-00516 Fatal error executing DDL replication in Oracle Goldengate

Recent articles