IMPDP - ORA-00001: unique constraint () violated

Forgot to blog about this error :)

As part of 10g to 11g migration, we were backloading Terabytes of data in parts using IMPDP while Golden Gating the real time data. So, somewhere in that process looks like the data which was importing was already got imported and IMPDP started failing with ORA-00001: unique constraint violated.

I went with DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS parameter to let the IMPORT go.
I Know, this option basically defeats the main feature of DIRECT PATH load and import will be slow but figured it would be rather more simpler than trying to find duplicates.

OGG-01168 - Encountered an update for target table which has no unique key defined. KEYCOLS can be used to define a key...

Was very happy that Golden Gate replication is going smooth until one of the group abended with the following error:

2014-05-21 20:23:15  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, RRHP99.prm:  No unique key is defined for table 'SAN_TEST'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
2014-05-21 20:23:15  ERROR   OGG-01168  Oracle GoldenGate Delivery for Oracle, RRHP99.prm:  Encountered an update for target table EQT6TIW.SAN_TEST, which has no unique key defined.  KEYCOLS can be used to define a key.  Use ALLOWNOOPUPDATES to process the update without applying it to the target database.  Use APPLYNOOPUPDATES to force the update to be applied using all columns in both the SET and WHERE clause.
2014-05-21 20:23:15  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, RRHP99.prm:  PROCESS ABENDING.

The reason for this failure is because there was an update operation that occurred at Source which actually didnt really update any data.

Used parameter ALLOWNOOPUPDATES to get the replicat keep going by ignoring such updates.

I know, there is always a fear as to what will happen to the real update? does this parameter ignores that too? No. It wont. Below is the test that I have performed which illustrates when we get error without that parameter and the real update that does replicates to the target with the parameter:

Source and Target have this table with same structure:

Source-SQL> desc san_test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NO                                                 NUMBER
 COL1                                               NUMBER(10)

Source-SQL> select * from san_test;

        NO       COL1
---------- ----------
         1          2
         2          2
         3          3
         4          4

At Target:
Target-SQL> select * from san_test;

        NO       COL1
---------- ----------
         1          2
         2          2
         3          3
         4          4

At Source:

Source-SQL> update san_test set no=2 where no=2;

1 row updated.

Source-SQL> commit;

Note the SET value. It is same as the existing.
Now, Replicat Abends with below error message:

2014-05-21 20:23:15  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, RRHP99.prm:  No unique key is defined for table 'SAN_TEST'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
2014-05-21 20:23:15  ERROR   OGG-01168  Oracle GoldenGate Delivery for Oracle, RRHP99.prm:  Encountered an update for target table EQT6TIW.SAN_TEST, which has no unique key defined.  KEYCOLS can be used to define a key.  Use ALLOWNOOPUPDATES to process the update without applying it to the target database.  Use APPLYNOOPUPDATES to force the update to be applied using all columns in both the SET and WHERE clause.
2014-05-21 20:23:15  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, RRHP99.prm:  PROCESS ABENDING.

Add the parameter ALLOWNOOPUPDATES to the prm file and start replicat again.

Now, lets check the update by actually changing the value to make sure it gets replicated.

At Source:
Source-SQL> update san_test set col1=1 where no = 1;

1 row updated.

SQL> commit;

Note the SET value for col1 is different.
At Target:
Target-SQL> select *from san_test;

        NO       COL1
---------- ----------
         1          1
         2          2
         3          3
         4          4

So setting that parameter is only to avoid an unnecessary update and not the real update.

Refer to Oracle Golden Gate Docs on this parameter to obtain more information.

WARNING OGG-00706 Failed to add supplemental log group on table schema_name.table_name due to ORA-00957: duplicate column name

Was setting up another group in golden gate and as a initial step was adding trandata to the tables in the schema and got this error:

GGSCI > add trandata schema.table_name, cols (column_name), nokey

WARNING OGG-00706  Failed to add supplemental log group on table schema.table_name due to ORA-00957: duplicate column name

Prior to this I did try adding trandata on the same table and it failed due to resource busy error but looks like it did add.

So, delete the log and then try again.

GGSCI > delete trandata schema.table_name
Logging of supplemental redo log data disabled for table schema.table_name.

Now add trandata again:

GGSCI  19> obey diroby/add_trandata_rhp05.oby

GGSCI  20> add trandata schema.table_name, cols (column_name), nokey


2014-05-11 17:30:39  WARNING OGG-00869  No unique key is defined for table 'TABLE_NAME'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table schema.table_name.

GGSCI  22> info trandata schema.table_name

Logging of supplemental redo log data is enabled for table schema.table_name.

Columns supplementally logged for table schema.table_name: column_name.

GGSCI  23> 

As you see above, This table didn't have any Primary Key (reason is because this table is another monster with way too many inserts (about 250 millions a month)) and thus defining trandata on a column using COLS and NOKEY.