Friday, November 28, 2014

OGG-00869, ORA-04098: trigger 'SIOD.EMP' is invalid and failed re-validation (status = 4098).


##########################
## Error
##########################

2014-11-21 02:13:33  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, RTab.prm:  OCI Error
ORA-04098: trigger 'SIOD.EMP' is invalid and failed re-validation (status = 4098). INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "SIOD"."TAB_1" ("ID",

### Full Error

2014-11-21 02:13:27  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, RTab.prm:  REPLICAT RTab started.
2014-11-21 02:13:32  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, RTab.prm:  No unique key is defined for table 'TAB_1'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
2014-11-21 02:13:33  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, RTab.prm:  OCI Error ORA-04098: trigger 'SIOD.EMP' is invalid and failed re-validation (status = 4098).
2014-11-21 02:13:33  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, RTab.prm:  Error mapping from SIOD.TAB_1 to SIOD.TAB_1.
2014-11-21 02:13:33  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, RTab.prm:  PROCESS ABENDING.


##########################
#  Error Occurred
##########################

Oracle Golden Gate Replicat process ABENDED with the above error.

##########################
## Command Executed
##########################

start replicat RTab

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check ggserr.log file
#########################################

2014-11-21 02:13:27  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, RTab.prm:  REPLICAT RTab started.
2014-11-21 02:13:32  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, RTab.prm:  No unique key is defined for table 'TAB_1'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
2014-11-21 02:13:33  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, RTab.prm:  OCI Error ORA-04098: trigger 'SIOD.EMP' is invalid and failed re-validation (status = 4098).
2014-11-21 02:13:33  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, RTab.prm:  Repositioning to rba 154752882 in seqno 7060.
2014-11-21 02:13:33  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, RTab.prm:  Error mapping from SIOD.TAB_1 to SIOD.TAB_1.
2014-11-21 02:13:33  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, RTab.prm:  PROCESS ABENDING.

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

Above error indicates that a trigger is in INVALID status due to which REPLICAT is failing to start.

=====================================================================================================================

#########################################
# 3) Check Trigger Status
#########################################

SET LINES 200
COL OBJECT_NAME FOR A30
COL OBJECT_TYPE FOR A25
COL Owner FOR A25

Select owner,object_name,object_type,status,created,LAST_DDL_TIME from dba_objects where object_name like 'EMP';

OWNER                     OBJECT_NAME                    OBJECT_TYPE               STATUS  CREATED   LAST_DDL_
------------------------- ------------------------------ ------------------------- ------- --------- ---------
SIOD         EMP           TRIGGER                   INVALID 29-OCT-14 21-NOV-14

set lines 200
col triggering_event for a35
col table_owner for a25

Select owner,trigger_name,triggering_Event,table_owner,table_name,status from dba_triggers where trigger_name='EMP';

OWNER                          TRIGGER_NAME                   TRIGGERING_EVENT                    TABLE_OWNER               TABLE_NAME                     STATUS
------------------------------ ------------------------------ ----------------------------------- ------------------------- ------------------------------ --------
SIOD             EMP           INSERT OR UPDATE                    SIOD       TAB_1     ENABLED

=====================================================================================================================
From above we can see that TRIGGER is in ENABLED at the database level but it is in INVALID status.
=====================================================================================================================

#########################################
# Solution Available
#########################################

a) Fix the errors in trigger compilation.
b) We can use DBOPTIONS SUPPRESSTRIGGER to prevent Trigger from its execution.
c) Disable the trigger if not needed.

=====================================================================================================================

##########################
## Solution ( a )
##########################

a) Fix the errors in trigger compilation.

Alter trigger SIOD.EMP compile;

SQL> Alter trigger SIOD.EMP compile;
Warning: Trigger altered with compilation errors.

SQL> sho err
Errors for TRIGGER SIOD.EMP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/2      PLS-00049: bad bind variable 'NEW.START_DATE'

=====================================================================================================================
Compilation of Trigger is not working as there is some definition error in the trigger.
=====================================================================================================================

##########################
## Solution ( b )
##########################

b) We can use DBOPTIONS SUPPRESSTRIGGER to prevent Trigger from its execution.

GGSCI (host01.example.com) 8> view param RTab

replicat RTab
USERID gold@oralin, PASSWORD ****
ASSUMETARGETDEFS
GROUPTRANSOPS 2000
EOFDELAYCSECS 20

-- To suppress the trigger over the target table.
DBOPTIONS SUPPRESSTRIGGERS
DBOPTIONS DEFERREFCONST

#### Modified the Replicat Parameter file and included "DBOPTIONS SUPPRESSTRIGGERS" keyword and restarted the Replicat which again failed with the below error.

#### oggerr.log

2014-11-21 06:45:12  INFO    OGG-00995  Oracle GoldenGate Delivery for Oracle, RTab.prm:  REPLICAT RTAB starting.
2014-11-21 06:45:12  INFO    OGG-03035  Oracle GoldenGate Delivery for Oracle, RTab.prm:  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
2014-11-21 06:45:12  INFO    OGG-03501  Oracle GoldenGate Delivery for Oracle, RTab.prm:  WARNING: NLS_LANG environment variable is invalid or not set. Using operating system character set value of AL32UTF8.
2014-11-21 06:45:12  ERROR   OGG-01746  Oracle GoldenGate Delivery for Oracle, RTab.prm:  Support for parameter SUPPRESSTRIGGERS is not available in the RDBMS version you are using.
2014-11-21 06:45:12  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, RTab.prm:  PROCESS ABENDING.

=====================================================================================================================
Tried placing the suppresstriggers options below userid also which didn't work out.
=====================================================================================================================

##########################
## Solution ( c )
##########################

c) Disable the trigger if not needed.

Alter trigger SIOD.EMP disable;

SQL> Alter trigger SIOD.EMP disable;

Trigger altered.

Select owner,trigger_name,triggering_Event,table_owner,table_name,status from dba_triggers where trigger_name='EMP';

OWNER                          TRIGGER_NAME                   TRIGGERING_EVENT                    TABLE_OWNER               TABLE_NAME                     STATUS
------------------------------ ------------------------------ ----------------------------------- ------------------------- ------------------------------ --------
SIOD             EMP         INSERT OR UPDATE                    SIOD       TAB_1     DISABLED

=====================================================================================================================
As the other options are not working we disabled the trigger as it is not needed and restarted the replicat which came up without any issues...
=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================