Erro ORA-01917 "RECOVERY_CATALOG_USER" na atualização do catálogo RMAN

This post is also available in: English

Estes dias, precisei registar um Banco de Dados novo que estava rodando na 12c no meu catálogo do RMAN. O procedimento que tentei foi o padrão como já tinha feito outras centenas de vezes com BDs em todas as versões. Quando conectei, o RMAN já reclamou que a versão do catálogo estava defasada, como esperado:

[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>

Resolvi então atualizar a versão do catálogo, mas recebi o erro "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>

Para resolver o problema, eu fiz a besteira de dar temporariamente o grant DBA (não faça isso!) para o usuário owner do catálogo (no meu caso, schema RMAN) para ver se o upgrade funcionaria. Acabei chegando em um erro ainda pior: "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>

O estrago estava feito: a atualização do catálogo parou no meio. Apesar das conexões de backup no catálogo continuarem funcionando, o rman abortou no meio do processo da atualização, o que o deixou possivelmente inconsistente para futuras atualizações.

Para resolver esse problema, meu primeiro passo foi restaurar o backup do schema do catálogo existente do dia anterior (feito via expdp). Em seguida, descobri que essa falha é um bug conhecido e para evitar o erro inicial de privilégios (RMAN-07539), eu deveria ter executado no meu BD de catálogo o 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 ~]$

Após retornar então o catálogo para a imagem do seu backup, de volta a estaca zero, executei então o 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 ~]$

Terminada a execução, o script acima consertou a falha de privilégios que recebi inicialmente. Agora bastou reiniciar o processo de upgrade do zero:

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>

Pronto, agora está tudo funcionando como eu esperava.

Informações sobre o Bug podem ser encontradas no Doc: Oracle Support Document 1915561.1 (Upgrade Recovery Catalog fails with RMAN-07539: insufficient privileges).

Gostou? Não deixe de comentar ou deixar um 👍!

Deixe um comentário

Seu e-mail não será publicado.