Add simple DDLERROR exception in Replicat to ingore certain ORA errors

Short braindump about adding a DDLERROR error exception in your Golden Gate REPLICAT configuration.

In this scenario the goal is to prevent my replicat from abending over an ‘ORA-01432: public synonym to be dropped does not exist ‘ error.

Check statuses for all OGG processes

GGSCI () 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    ABENDED     REPL1       00:00:06      96:57:06

%^(&* My REPL1 Replicat has been down for more than four days… Luckily it is a test site.

Look up the error
First thing is finding the error that caused the REPLICAT to abend in the report file of the REPL1 Replicat;

GGSCI () 4> view report REPL1 
.
.
2013-08-28 17:48:28  ERROR   OGG-00519  Fatal error executing DDL replication: error [Error code [1432], ORA-01432: public synonym to be dropped does not exist SQL drop public synonym <synonym_name>  /* GOLDENGATE_DDL_REPLICATION */], no error handler present.
.

OK, the error is obvious. A public synonym did exist on the Extract database but NOT in the Replicat database. So, Golden Gate has captured the ‘drop public synonym’ statement from the Extract database logs and it expects to successfully execute this statement on the Replicat database.
This failed because, apparently, this public synonym is not present in the Replicat database. The result the Replicat process abended.

I will make a note of this and document that in our (datapump) initial load (of a couple of schemas) of our Replicat database we should make sure to include all possible public synonyms. Or in any case the public synonyms that may be passed through by our Extract.

For now I want to go forward and instead of creating all the missing public synonym manually in the Replicat database I will configure the REPL1 Replicat to ignore this particular ORA error ‘drop public synonym’ – I want this for all failed public synonym errors.

Add DDLERROR parameter in the replicat parameter file

See below the parameter DDLERROR where I choose to entirely IGNORE all ‘ORA-01432’ errors. No retries (RETRYOP), no filtering on object names (OBJNAME).

-- remi visser, 2-9-2013 18:43:50
-- TEMPORARY ! ignore all 'ORA-01432: public synonym to be dropped does' errors
ddlerror 1432 ignore

Start up REPLICAT and confirm that the ORA error is indeed ingnored

Now start up the REPL1 Replicat again;

GGSCI () 1> start REPL1

Sending START request to MANAGER ...
REPLICAT REPL1 starting

As you can see below the Replicat REPL1 is in business again (the ‘Lag at Chkpt’ is nicely decreasing).

GGSCI () 25> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REPL1       96:59:04      00:00:29


GGSCI () 26> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REPL1       90:12:08      00:00:01


GGSCI () 27> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REPL1       89:21:18      00:00:00


GGSCI () 28> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REPL1       89:20:00      00:00:00


GGSCI () 29> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REPL1       88:37:09      00:00:04


GGSCI () 30> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REPL1       78:23:10      00:00:00


GGSCI () 31> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REPL1       00:00:00      00:00:00

Also in the REPL1 Replicat report file it is reported that;

  • a) the DDL error is encoutered (the drop public synonym SQL captured from the Extract database was executed in the Replicat databaseand returned an ORA error),
    but / and:
  • b) the DDL error is ignored as per the configuration (OGG-00492 DDL error ignored).
.
2013-09-01 18:48:44  INFO    OGG-00492  DDL error ignored: error code [1432], filter [include all (default)], error text [Error code [1432], ORA-01432: public synonym to be dropped does not exist SQL drop public synonym <synonym_name>  /* GOLDENGATE_DDL_REPLICATION */].
.
.

See the Golden Gate Windows and UNIX Reference Guide for a full explanation of the varous DDLERROR options.

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>