Symptoms

When replicating or restoring Oracle database backup created using datapump, you receive foreign key constraint errors. The following appears in the atlassian-confluence.log:

ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (WIKI_357.FKA898D4778DD41734) - parent keys not found

Cause

This is usually caused by taking a database backup using datapump utility while the database is active, also known as a "hot dump".

Workaround

The specific entries causing the error to be thrown will be included in the logs.

The datapump file can be cleaned up using the following queries:

select <COLUMN_NAME> from <CHILD_TABLE> a where not exists (select <COLUMN_NAME> from
<PARENT_TABLE> where primary_key = a.<KEY_VALUE>);

This will show you the row (or rows) that exist in the child table but don't exist in the parent table. Once you have either (1)added rows to the parent table to match the child rows, or (2) removed the child rows that don't have parents, run the import again, preferably in a development environment.

Resolution

1. If possible, create the database dump while the database is not active.
2. There are also some features in Oracle datapump that will allow you to avoid this kind of trouble when importing/exporting. Two specifically are FLASHBACK_TIME & FLASHBACK_SCN, which may be used to alleviate these problems in the future.

3. If it's not possible to complete step 1, use the query in the Workaround section to clean up database foreign key constraint errors.