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 18.104.22.168.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 22.214.171.124.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)