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 👍!