Extract Captures Null Value for Default Not Null Column Causing Replicat to Abend with ORA-1400 or ORA-1403

(Doc ID 1331178.1)

Last updated on JULY 27, 2017

Applies to:

Oracle GoldenGate - Version 10.4.0.103 and later
Information in this document applies to any platform.

Symptoms

Make a note saying the error 1403 error could happen due to other issues as well and refer Replicat is abending with cannot insert null into a not null column or Replicat is abending with ORA-1403 error
eg:
********************************************************
2011-05-31 22:42:22 WARNING OGG-01004 Aborted grouped transaction on 'STG_SAP.HRP1000', Database error 100 (retrieving bind info for query).
2011-05-31 22:42:22 WARNING OGG-01003 Repositioning to rba 21070779 in seqno 15.
2011-05-31 22:42:22 WARNING OGG-01154 SQL error 1403 mapping SAP..HRP1000 to STG_SAP.HRP1000.
2011-05-31 22:42:22 WARNING OGG-01003 Repositioning to rba 21070779 in seqno 15.
********************************************************


1. Check whether supplemental logging is enabled for the columns used as keycols by extract and replicat.

2. Check the problematic table and see if the column defined as default not null.  
   eg:  grade varchar2(10) NOT NULL DEFAULT 'M2'

3. Check the archive log which contains the problematic data on source using logminer and see if the record has default value logged for the problematic column.
   Please get rowid of this record using below action plan:

$ ./logdump
logdump> ghdr on
logdump> detail data
logdump> ggstokens on
logdump> ggstokens detail
logdump> open /ggtrail11g/dirdat/cs17/dd000176
logdump> pos 4972242
logdump> n

Once you have record detail, you'll get row id from information of ORAROWID displayed in output.
Using this rowid, please execute below steps to do logmining for archive log sequence number <number> and see actual sql:

SQL> exec sys.dbms_logmnr.add_logfile ( logfilename => '<full-path-name-of-archived-log-file-name>', options => sys.dbms_logmnr.new);
SQL> exec sys.dbms_logmnr.start_logmnr(options => sys.dbms_logmnr.dict_from_online_catalog);
SQL> select sql_undo, sql_redo from v$logmnr_contents where row_id='<ROW_ID>';




Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms