RMAN Catalog Upgrade fails – ORA-02296 – error creating modify_ts_pdbinc_key_not_null

This issue got raised to my via a customer I know for quite a while – all credits go to Andy Kielhorn for digging down into that issue and solving it.

Failed RMAN Catalog Upgrade from 11.2.0.4 to 12.1.0.2

The RMAN catalog upgrade:

SQL> @?/rdbms/admin/dbmsrmansys.sql

$ rman CATALOG rman/xxx@rman01

RMAN> UPGRADE CATALOG; 

RMAN> UPGRADE CATALOG;

failed with the following sequence of error messages:

error creating modify_ts_pdbinc_key_not_null
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-02296: cannot enable (RMAN.) - null values found

error creating modify_tsatt_pdbinc_key_not_null
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-02296: cannot enable (RMAN.) - null values found

error creating modify_df_pdbinc_key_not_null
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-02296: cannot enable (RMAN.) - null values found

error creating modify_tf_pdb_key_not_null
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-02296: cannot enable (RMAN.) - null values found

error creating modify_bs_pdb_key_not_null
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-02296: cannot enable (RMAN.) - null values found

Andy found also these bugs in MOS – but no helpful information included:

• Bug 20861957
  ORA-2296 DURING UPGRADE CATALOG TO 12.1,0.1 IN AN
  11.2 DATABASE
• Bug 19677999
  CATALOG SCHEMA UPGRADE TO 12.1.0.2 FAILED

The  Solution

There seems to be a potential inconsistency in the RMAN catalog when the PDB/CDB mechanisms get introduced. This does not necessarily happen – but it can happen.

The workaround is described in:

• Bug 19677999 : CATALOG SCHEMA UPGRADE TO 12.1.0.2 FAILED

==> Connect to catalog schema and clear the table having null details

delete bdf
where not exists (select 1 from dbinc where dbinc.dbinc_key = bdf.dbinc_key);
delete bcf where not exists (select 1 from dbinc where dbinc.dbinc_key = bcf.dbinc_key);
commit;

==> After clearing the offending rows , upgrade catalog worked

But please use this workaround only under Oracle Support’s supervision. I did document it here to ease your verification.

Andy fixed it with:

    update [rmancat_owner].dbinc set PARENT_DBINC_KEY=NULL
    where (DBINC_KEY) IN
       (SELECT DBINC_KEY  from [rmancat_owner].ts
        where pdbinc_key is null);
    commit;

but please open an SR and point Oracle Support to the bug and the potential workarounds in case you hit the issue.

–Mike