Shulou(Shulou.com)06/01 Report--

OGG failed, ORA-01400, cannot insert null into "TEST". "TEST"

The destination side replication process uses the configuration HANDLECOLLISIONS. When this configuration is updated on the destination side, if the data cannot be updated, the UPDATE will be converted to insert.

However, because the source side does not configure additional logs for all columns, columns that are not updated will not be included in the insert, resulting in an error

The following steps have been taken, and here is a temporary solution:

1. Modify the column to null: alter table test modify col1 null

two。 Start the replication process

3. Check data: select count (*) from test where col1 is null (found to have eight entries)

4. Process and manually synchronize these eight pieces of data

5. Change the field to not null: alter table test modify col1 not null

Final solution: additional logs for all columns that need to be added to the table: alter table test add supplemental log data (ALL) columns

The DDL operation is dangerous in the production environment, so the operation is postponed.

MOS document ID: Replicat Abends With Errror ORA-01400: Cannot Insert NULL Into Table for Not Null Columns When Specifying INSERTALLRECORDS (document ID 1994869.1)

REPLICAT Reports ORA-01400: Cannot Insert NULL When Using HANDLECOLLISIONS (document ID 1576900.1)

REPLICAT tried to UPDATE an existing row, but none was found, so due to parameter HANDLECOLLISIONS, the failed UPDATE was converted into an INSERT.

That INSERT then failed because the Trail file did not have all the columns, because the record was for an UPDATE for 5 out of 15 columns, where the table on the Oracle database had the NOT NULL constraint set for all columns.

The cause of the problem has been determined from the following points

The REPLICAT report shows the message "... inserting a row into USER1.TABLE1 due to missing target row for a key update operation."

Where the key words are "missing target row for a key update".

This means there was no row to UPDATE, for the change record found in the Trail file.The REPLICAT had the HANDLECOLLISIONS parameter set.The GoldenGate Reference Guide shows that when HANDLECOLLISIONS is used, failed UPDATE's will be converted into INSERT's

"If the row with the old key is not found in the target, the change record in the trail is converted to an insert" REPLICAT report finally shows that after the failed UPDATE was reported, the INSERT also failed.

The INSERT failed because the Trail file record only had 5 out of 15 columns, where the table is defined with NOT NULL on all the columns.

This then caused the Oracle database error: ORA-01400: cannot insert NULL...

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.