##########################
## 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
=====================================================================================================================
