ORA-01917 "RECOVERY_CATALOG_USER" error while upgrading RMAN catalog

This post is also available in: Português

These days, I needed to register a new database that was running at 12c on my RMAN catalog. The procedure that I tried was the same as I had done before hundreds of times with DBs in all versions. When I connected, RMAN complained that the version of the catalog was outdated, as expected:

[oracle@brocbddtrj001 ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Sep 2 15:36:06 2014

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

connected to target database: BROCDB (DBID=2702110135)

RMAN> connect catalog 'rman/xxx@rmanbkp';

connected to recovery catalog database
PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.04 in RCVCAT database is too old

RMAN> register database;

PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.04 in RCVCAT database is too old
PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.04 in RCVCAT database is too old
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of register command at 09/03/2014 17:15:15
RMAN-06429: RCVCAT database is not compatible with this version of RMAN

RMAN>

I then decided to upgrade the version of the catalog, but I got the error "RMAN-07539: insufficient privileges to create or upgrade the catalog schema":

RMAN> upgrade catalog;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-07539: insufficient privileges to create or upgrade the catalog schema

RMAN>

To solve the problem, I ended up screwing things when I temporarily gave the DBA grant (do not do it!) to the catalog owner (RMAN schema in my case) to see whether the upgrade would work. I got an even worse error: "RMAN-06004: ORACLE error from recovery catalog database: ORA-01917: user or role 'RECOVERY_CATALOG_USER' does not exist":

RMAN> upgrade catalog;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-01917: user or role 'RECOVERY_CATALOG_USER' does not exist

RMAN>

The damage was done. The catalog upgrade process stopped in the middle. Despite the backup connections in the catalog continue running without problems, the rman aborted in the middle of the upgrade process, leaving it possibly inconsistent for future updates.

To resolve this problem, my first step was to restore the existing backup of the catalog schema from the previous day (done via expdp). Then I found out that this failure is a known bug and to avoid the initial privileges error (RMAN-07539), I should have run on my catalog DB the script "$ORACLE_HOME/rdbms/admin/dbmsrmansys.sql":

[oracle@brocbddtrj001 ~]$ cat $ORACLE_HOME/rdbms/admin/dbmsrmansys.sql
Rem
Rem Copyright (c) 2013, 2014, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem BEGIN SQL_FILE_METADATA
Rem SQL_SOURCE_FILE: rdbms/admin/dbmsrmansys.sql
Rem SQL_SHIPPED_FILE: rdbms/admin/dbmsrmansys.sql
Rem SQL_PHASE: DBMSRMANSYS
Rem SQL_STARTUP_MODE: NORMAL
Rem SQL_IGNORABLE_ERRORS: NONE
Rem SQL_CALLING_FILE: NONE
Rem END SQL_FILE_METADATA
Rem
Rem MODIFIED (MM/DD/YY)
Rem surman 02/11/14 - 13922626: Update SQL metadata
Rem vbegun 10/29/13 - Created
Rem

@@?/rdbms/admin/sqlsessstart.sql

-- Do not drop this role recovery_catalog_owner.
-- Drop this role will revoke this role from all rman users.
-- If this role exists, ORA-1921 is expected.
declare
role_exists exception;
pragma exception_init(role_exists, -1921);
begin
execute immediate 'create role recovery_catalog_owner';
exception
when role_exists then
null;
end;
/
declare
role_exists exception;
pragma exception_init(role_exists, -1921);
begin
execute immediate 'create role recovery_catalog_user';
exception
when role_exists then
null;
end;
/

grant create session,alter session,create synonym,create view,
create database link,create table,create cluster,create sequence,
create trigger,create procedure, create type to recovery_catalog_owner;

-- Following are added for VPD support
grant execute on dbms_rls to recovery_catalog_owner;
grant create any trigger to recovery_catalog_owner;
grant create any synonym to recovery_catalog_owner;
grant create public synonym to recovery_catalog_owner;
grant drop any synonym to recovery_catalog_owner;
grant administer database trigger to recovery_catalog_owner;
grant recovery_catalog_user to recovery_catalog_owner with admin option;

@?/rdbms/admin/sqlsessend.sql
[oracle@brocbddtrj001 ~]$

After returning the catalog to the backup image, now back square one, I ran the script:

[oracle@brocbddtrj001 ~]$ sqlplus sys@rmanbkp as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 3 17:20:51 2014

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> @?/rdbms/admin/dbmsrmansys.sql
alter session set "_ORACLE_SCRIPT" = true
*
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

alter session set "_ORACLE_SCRIPT" = false
*
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@brocbddtrj001 ~]$

After the execution, the above script repaired the privileges failure that I initially received. Finaly all I needed was to restart the upgrade process from scratch:

RMAN> connect catalog 'rman/xxx@rmanbkp'

connected to recovery catalog database
PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.04 in RCVCAT database is too old

RMAN> upgrade catalog;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;

recovery catalog upgraded to version 12.01.00.02
DBMS_RCVMAN package upgraded to version 12.01.00.02
DBMS_RCVCAT package upgraded to version 12.01.00.02.

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

Okay, now everything is working as I expected.

Information about this bug can be found at MOS: Oracle Support Document 1915561.1 (Upgrade Recovery Catalog fails with RMAN-07539: insufficient privileges).

+9