Upgrade Recovery Catalog to 12cR2 fails and leaves catalog an unusable state

After upgrading our AWR Warehouse instance to 12.2 our standard RMAN scripts were failing because the Recovery Catalog required upgrading. Attempting the upgrade failed, and left the Recovery Catalog unable. Fortunately we were able to restore the catalog from weekly export.

I was able to reproduce the problem by importing into a new schema, below are the results. Note I enable tracing to dig into exactly what was failing:

[oracle@vmhkoemawrbpd1 ~]$ rman catalog TST_RCAT/TST_RCAT debug trace=/tmp/upgrade.trc

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Mar 21 17:16:31 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

RMAN-06008: connected to recovery catalog database
RMAN-06186: PL/SQL package TST_RCAT.DBMS_RCVCAT version 12.01.00.02. in RCVCAT database is too old

RMAN> UPGRADE CATALOG

RMAN-06435: recovery catalog owner is TST_RCAT
RMAN-06442: enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> UPGRADE CATALOG

RMAN-06444: error creating init_grsp_pdb_key
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-01422: exact fetch returns more than requested number of rows

The following extract from the trace file identifies the failing section of code:

DBGSQL:         RCVCAT> declare
DBGSQL:            cursor grsp_c is
DBGSQL:               select distinct grsp.rowid rid, dbinc.db_key db_key
DBGSQL:                 from grsp, dbinc
DBGSQL:                where grsp.pdb_key is null
DBGSQL:                  and grsp.dbinc_key = dbinc.dbinc_key
DBGSQL:                order by dbinc.db_key;
DBGSQL:            prev_db_key   number := 0;
DBGSQL:            local_pdb_key number;
DBGSQL:         begin
DBGSQL:            for r in grsp_c loop
DBGSQL:              if (r.db_key  prev_db_key) then
DBGSQL:                 prev_db_key := r.db_key;
DBGSQL:                 select pdb_key into local_pdb_key
DBGSQL:                   from pdb
DBGSQL:                  where pdb.con_id in (1, 0);
DBGSQL:              end if;
DBGSQL:
DBGSQL:              update grsp set pdb_key = local_pdb_key where rowid = r.rid;
DBGSQL:            end loop;
DBGSQL:         end;
DBGSQL:
DBGSQL:            sqlcode = 1422
DBGSQL:         error: ORA-01422: exact fetch returns more than requested number of rows (krmkosqlerr)

Table PDB seems to hold information about Pluggable DataBases (a feature of multitenant).  In our catalog we had 30 records with con_id equals to 0 or 1.  Obviously the upgrade code was not expecting this.

Attempts to use the partially upgraded catalog now fail due to the mismatch of components within the catalog.

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Mar 21 17:32:53 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to recovery catalog database
PL/SQL package TST_RCAT.DBMS_RCVCAT version 12.02.00.01 in RCVCAT database is too new
RMAN must be upgraded to version 12.02.00.01 to work with this package
RMAN> backup database;

Starting backup at 17:35:44 21/03/2017
PL/SQL package TST_RCAT.DBMS_RCVCAT version 12.02.00.01 in RCVCAT database is too new
RMAN must be upgraded to version 12.02.00.01 to work with this package
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 03/21/2017 17:35:44
RMAN-03014: implicit resync of recovery catalog failed
RMAN-06429: RCVCAT database is not compatible with this version of RMAN

Oracle support have published a document about this issue:

UPGRADE CATALOG command from 12.1 to 12.2 Fails With RMAN-6004 and ORA-1422 (Doc ID 2252894.1)