"ORA-02291: integrity constraint (RCUSER.RLH_F1) violated - parent key not found " while register database in recovery catalog

1.1. Error

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03008: error while performing automatic resync of recovery catalog

ORA-02291: integrity constraint (RCUSER.RLH_F1) violated - parent key not found

1.2. Cause

A foreign key value has no matching primary key value.

1.3. Action:

Delete the foreign key or add a matching primary key.

1.4. Steps to diagnose

1.4.1.     Check violate foreign key definition to find out which table and column are refered

SELECT CON.*,

       R.OWNER REF_OWNER,

       R.TABLE_NAME REF_TAB_NAME,

       R.COLUMN_NAME REF_COL_NAME

  FROM (SELECT C.OWNER,

               C.CONSTRAINT_NAME,

               C.CONSTRAINT_TYPE,

               C.TABLE_NAME,

               C.DELETE_RULE,

               C.R_CONSTRAINT_NAME,

               COL.COLUMN_NAME,

               COL.POSITION

          FROM USER_CONS_COLUMNS COL, USER_CONSTRAINTS C

         WHERE     C.OWNER = 'RCUSER'

               AND C.CONSTRAINT_NAME = 'RLH_F1'

               AND C.OWNER = COL.OWNER

               AND C.CONSTRAINT_NAME = COL.CONSTRAINT_NAME) CON,

       USER_CONS_COLUMNS R

 WHERE CON.R_CONSTRAINT_NAME = R.CONSTRAINT_NAME;

 

 

SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','RLH_F1') FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','DBINC_P') FROM DUAL;

From the excerpt ,RCUSER.RLH  foreign key RLH_F1 is refered on table RCUSER.DBINC (DBINC_KEY) with the delete option"cascade".

RCUSER.DBINC column DBINC_KEY has primary key .

1.4.2.     Check the record on RCUSER.DBINC table

DBINC_KEY exists on DBIC table for database ORA11G which is the cause of failure registration .

When "unregister database",record delete from rc_rlh but parent key still exists in dbinc.

1.4.3.     Check the record in both dbinc &rc_database

Even dbinc_key exists in both dbinc and rc_database .However when run "resync catalog" still failed .

1.4.4.     Solution

When unregister database from recovery catalog , need to use dbms_rcvcat.unregisterdatabase procedure instead of "unregister database "