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.

Mapping problem with compressed update record (target format)...

One of the group abanded and ggserror.log says:
ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, RRHP03.prm:  Error mapping from schema_name.table_name to schema_name.table_name

Discard file says:

Key column column_name (8) is missing from update on table table_name.column_name
and the list continues for all the columns in the table and:
Missing 46 key columns in update for table schema_name.table_name.
Current time: 2014-05-11 12:32:11
Discarded record from action ABEND on error 0


Aborting transaction on dirdat/rc beginning at seqno 63 rba 194046251
                         error at seqno 63 rba 194046975
Problem replicating schema_name.table_name to schema_name.table_name
Mapping problem with compressed update record (target format)...

Well, this table has UNIQUE INDEX defined at the source but somehow that index is missing at the target.
So, created that index at the target and also added this to parameter file to make sure that index column is being used instead of looking for the complete set of columns for the uniqueness.

map schema_name.table_name, target schema_name.table_name, keycols (unique_index_columns) ;

More details for different scenarios:

Replicat Abending With Mapping Error and discard file shows Missing Key Columns (Doc ID 1276538.1)

ORA-39014: One or more workers have prematurely exited.

This time issues with IMPDP.
Export was taken with parallelism of 4 and does Import too but still receiving this error.

ORA-39014: One or more workers have prematurely exited.

ORA-39029: worker 4 with process name "DW01" prematurely terminated

ORA-31671: Worker process DW01 had an unhandled exception.

ORA-00600: internal error code, arguments: [qerxtGetRefOffset_911], [], [], [], [], [], [], [], [], [], [], []

ORA-06512: at "SYS.KUPW$WORKER", line 1838

ORA-06512: at line 2

Reducing the parallelism to 2 and trying now again. Let's see I can get pass with this error.

Update:
Playing with Parallelism did not help in this case.
Digging alert log and the traces, found that the issue is with NESTED TABLES. 

OGG-01172 Discard file (dirrpt/RRHP02a2.dsc) exceeded max bytes (1000000)

One after another...

There is one table that I split into 3 different replicats due to its size and number of transactions that are happening on this table.
These groups abended due to 1403 issue which is due to data missing (well, there was no need to backload data at target and at source there were few dml's on those records).
So, took care of that by adding REPERROR (1403, TRANSDISCARD) to the param file.

Once that's taken care, it started abending again with Discard File exceeded max bytes.

Well, didn't define the max size for discard file and by default it's limiting to 1MB and there were ton's of dml's so within no time it will fill it up (well, need to record all transactions those got discarded).

So, changed the param file to set the limit as 1GB instead:

DiscardFile dirrpt/RRHP02a2.dsc, Append, megabytes 1000

UDE-00008: operation generated ORACLE error 31626

This one is interesting and do not panic by just seeing these messages in your EXPDP but check the log file and see if it says job successfully completed or not...

UDE-00008: operation generated ORACLE error 31626
ORA-31626: job does not exist
ORA-39086: cannot retrieve job information
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2772
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3886
ORA-06512: at line 1

I was running expdp with nohup in background by appending nohup info into out file and was checking the out file and noticed these errors and felt that the export job really had an issue.

Opened the log file which is from the parameters settings and the log file says its completed successfully!!!

Oracle Doc ID:549781.1 states:
This issue has been discussed in Bug 5969934 EXPDP CLIENT GETS UDE-00008 ORA-31626 WHILE THE SERVER SIDE EXPORT IS OK