search cancel

Primary Keys missing after importing vanilla dataset

book

Article ID: 215280

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

When importing the vanilla dataset using impdp, the following errors are thrown due to statistics being available for some Primary Keys, but the Primary Keys itself do not exist:

NIKU.ODF_HIERARCHY_ATTRIBUTES_PK : sqlerrm = ORA-20000: Unable to set values for index ODF_HIERARCHY_ATTRIBUTES_PK: does not exist or insufficient privileges
NIKU.RDM_ROADMAP_ITEM_POS_PK : sqlerrm = ORA-20000: Unable to set values for index RDM_ROADMAP_ITEM_POS_PK: does not exist or insufficient privileges

Steps to Reproduce

  1. Create an schema for the Clarity database in Oracle 19c.
  2. Grant all the necessary rights and privileges.
  3. Import the vanilla dataset from Clarity ISO using impdp. e.g.:
    impdp system/niku DIRECTORY=import_directory transform=oid:n DUMPFILE=oracle_base.db SCHEMAS=niku LOGFILE=import.log

Expected Results: Import succeeds with no errors.

Actual Results: Errors are shown related to ODF_HIERARCHY_ATTRIBUTES_PK and RDM_ROADMAP_ITEM_POS_PK not existing. If statistics are not imported, the errors are not shown but the PKs are also not present in the schema after the import.

Cause

This issue has been reported to Engineering as DE60967.

Environment

Release : 15.9.1

Component : CA PPM INTEGRATIONS & INSTALLATIONS

Resolution

Indexes/constraints are being created out of order. By reordering the constraint creation (first), the errors go away. This happens only in 19c. This is a bug in Oracle (Oracle defect: 26380126).


Workaround:

  1. Run impdb with EXCLUDE=STATISTICS option.
    impdp system/niku DIRECTORY=import_directory transform=oid:n DUMPFILE=oracle_base.db SCHEMAS=niku LOGFILE=import.log EXCLUDE=STATISTICS
  2. Run Oracle Analyze Job.