# Creación del catálogo de RMAN Como siempre comenzaremos con un poco de teoría. Imagina que RMAN (Recovery Manager) es como un súper asistente que se encarga de hacer copias de seguridad y restaurar tus bases de datos en Oracle. Ahora, para que este asistente haga bien su trabajo, necesita llevar un registro detallado de todas las copias de seguridad que ha hecho, dónde están guardadas, y toda la información necesaria para restaurarlas si algo sale mal. Aquí es donde entra el catálogo de RMAN. El catálogo es básicamente una base de datos aparte que guarda todos esos registros importantes. Es como una libreta o bitácora donde RMAN apunta todo lo que hace. ¿Por qué usar un catálogo de RMAN? * **Todo en un solo lugar**. Si tienes varias bases de datos, el catálogo de RMAN centraliza la información de todas ellas. * **Historial completo**. Mantiene un historial más largo y detallado que los archivos de control de cada base de datos. Esto te permite restaurar datos de mucho tiempo atrás, no solo los más recientes. * **Scripts reutilizables**. Puedes guardar scripts (instrucciones) dentro del catálogo para automatizar tareas de backup y restauración. Esto es súper útil si haces las mismas operaciones una y otra vez. Ahora vamos a un caso práctico de creación y configuración. En mi caso albergaré el catálogo en la CDB donde reside el repositorio de OEM. En primer paso crearemos la PDB, Tablespace por defecto y usuario: ```plaintext CREATE PLUGGABLE DATABASE pdb_rman ADMIN USER pdbadmin IDENTIFIED BY "******"; alter pluggable database pdb_rman open; alter pluggable database pdb_rman save state; alter session set container=PDB_RMAN; create tablespace tbs_rman; CREATE USER rman_catalog IDENTIFIED BY "*********" DEFAULT TABLESPACE tbs_rman QUOTA UNLIMITED ON tbs_rman; GRANT recovery_catalog_owner TO rman_catalog; ``` A continuación, ejecuta el script dbmsrmansys.sql para otorgar privilegios adicionales necesarios para la función RECOVERY\_CATALOG\_OWNER. ```plaintext SYS@cdboem> show con_name; CON_NAME ------------------------------ PDB_RMAN SQL> @$ORACLE_HOME/rdbms/admin/dbmsrmansys.sql SYS@cdboem> @$ORACLE_HOME/rdbms/admin/dbmsrmansys.sql SYS@cdboem> Rem SYS@cdboem> Rem Copyright (c) 2013, 2018, Oracle and/or its affiliates. SYS@cdboem> Rem All rights reserved. SYS@cdboem> Rem SYS@cdboem> Rem BEGIN SQL_FILE_METADATA SYS@cdboem> Rem SQL_SOURCE_FILE: rdbms/admin/dbmsrmansys.sql SYS@cdboem> Rem SQL_SHIPPED_FILE: rdbms/admin/dbmsrmansys.sql SYS@cdboem> Rem SQL_PHASE: DBMSRMANSYS SYS@cdboem> Rem SQL_STARTUP_MODE: NORMAL SYS@cdboem> Rem SQL_IGNORABLE_ERRORS: NONE SYS@cdboem> Rem SQL_CALLING_FILE: NONE SYS@cdboem> Rem END SQL_FILE_METADATA SYS@cdboem> Rem SYS@cdboem> Rem MODIFIED (MM/DD/YY) SYS@cdboem> Rem vbegun 01/10/18 - custom base catalog context SYS@cdboem> Rem vbegun 02/22/16 - bug 22757320 workaround SYS@cdboem> Rem ppatare 11/25/15 - bug 21497667 SYS@cdboem> Rem vbegun 03/16/15 - bug20713578 SYS@cdboem> Rem vbegun 01/30/15 - disabling vpd support out of the box SYS@cdboem> Rem surman 02/11/14 - 13922626: Update SQL metadata SYS@cdboem> Rem vbegun 10/29/13 - Created SYS@cdboem> Rem SYS@cdboem> SYS@cdboem> SET TERMOUT OFF SYS@cdboem> SYS@cdboem> -- Do not drop this role recovery_catalog_owner. SYS@cdboem> -- Drop this role will revoke this role from all rman users. SYS@cdboem> -- If this role exists, ORA-1921 is expected. SYS@cdboem> declare 2 role_exists exception; 3 pragma exception_init(role_exists, -1921); 4 begin 5 execute immediate 'create role recovery_catalog_owner'; 6 exception 7 when role_exists 8 then null; 9 end; 10 / PL/SQL procedure successfully completed. SYS@cdboem> declare 2 role_exists exception; 3 pragma exception_init(role_exists, -1921); 4 begin 5 execute immediate 'create role recovery_catalog_owner_vpd'; 6 exception 7 when role_exists 8 then null; 9 end; 10 / PL/SQL procedure successfully completed. SYS@cdboem> declare 2 role_exists exception; 3 pragma exception_init(role_exists, -1921); 4 begin 5 execute immediate 'create role recovery_catalog_user'; 6 exception 7 when role_exists 8 then null; 9 end; 10 / PL/SQL procedure successfully completed. SYS@cdboem> SYS@cdboem> grant create session,alter session,create synonym,create view, 2 create database link,create table,create cluster,create sequence, 3 create trigger,create procedure, create type to recovery_catalog_owner; Grant succeeded. SYS@cdboem> SYS@cdboem> -- Following are added for VPD support SYS@cdboem> grant execute on dbms_rls to recovery_catalog_owner_vpd; Grant succeeded. SYS@cdboem> grant create any synonym, drop any synonym, administer database trigger, 2 recovery_catalog_owner, create any context, drop any context 3 to recovery_catalog_owner_vpd; Grant succeeded. SYS@cdboem> grant recovery_catalog_user to recovery_catalog_owner_vpd with admin option; Grant succeeded. SYS@cdboem> SYS@cdboem> -- Bug 21497667 : Grant execute access on DBMS_LOCK package to SYS@cdboem> -- recovery_catalog_owner and other catalog users. This is required because SYS@cdboem> -- the dbms_lock was not accesible in the cdb-pdb environment, but it was SYS@cdboem> -- accesible from non cdb environment SYS@cdboem> grant execute on dbms_lock to recovery_catalog_owner, recovery_catalog_user, 2 recovery_catalog_owner_vpd; Grant succeeded. SYS@cdboem> SYS@cdboem> -- This detects all RMAN base catalogs deployed and does privileges adjustments SYS@cdboem> -- depending on the presence of the VPC users associated with that catalog and SYS@cdboem> -- its current VPD status SYS@cdboem> declare 2 procedure r ( 3 i_priv in varchar2 4 ) 5 is 6 begin 7 execute immediate 'revoke ' || i_priv 8 || ' from recovery_catalog_owner'; 9 exception 10 when others 11 then null; 12 end; 13 14 function has_vpc_users ( 15 i_catowner user_users.username%type 16 ) 17 return boolean 18 is 19 l_dummy varchar2(1); 20 l_has_filter_uid number; 21 l_catowner varchar2(130); 22 begin 23 l_catowner := dbms_assert.enquote_name(i_catowner); 24 begin 25 select 'x' 26 , ( 27 select count(*) 28 from dba_tab_columns c 29 where c.owner = t.owner 30 and c.table_name = t.table_name 31 and c.column_name in ('FILTER_UID') 32 ) 33 into l_dummy 34 , l_has_filter_uid 35 from dba_tables t 36 where table_name = 'VPC_USERS' 37 and owner = i_catowner 38 and 1 = ( 39 select count(*) 40 from dba_tab_columns c 41 where c.owner = t.owner 42 and c.table_name = t.table_name 43 and c.column_name in ('FILTER_USER') 44 ) 45 and 2 = ( 46 select count(*) 47 from dba_objects o 48 where o.owner = t.owner 49 and o.object_name = 'DBMS_RCVCAT' 50 and o.object_type in ('PACKAGE', 'PACKAGE BODY') 51 ) 52 and 2 = ( 53 select count(*) 54 from dba_objects o 55 where o.owner = t.owner 56 and o.object_name = 'DBMS_RCVMAN' 57 and o.object_type in ('PACKAGE', 'PACKAGE BODY') 58 ); 59 if (l_has_filter_uid > 0) 60 then 61 execute immediate 62 regexp_replace( 63 'select ''x'' from %o.vpc_users u, dba_users du where' 64 || ' u.filter_user = du.username and u.filter_uid = du.user_id ' 65 || ' and rownum = 1 having count(*) = 1' 66 , '%o' 67 , l_catowner 68 ) 69 into l_dummy; 70 else 71 execute immediate 72 regexp_replace( 73 'select ''x'' from %o.vpc_users u, dba_users du where' 74 || ' u.filter_user = du.username and rownum = 1 having count(*) = 1' 75 , '%o' 76 , l_catowner 77 ) 78 into l_dummy; 79 end if; 80 exception 81 when no_data_found 82 then return false; 83 end; 84 return l_dummy is not null; 85 end; 86 87 begin 88 for u in ( 89 select u.username catowner 90 , ( 91 select 'y' 92 from dba_tab_privs t 93 where t.grantee = r.granted_role 94 and t.table_name = 'DBMS_RLS' 95 and t.privilege = 'EXECUTE' 96 and t.owner = 'SYS' 97 and rownum = 1 98 ) has_rls 99 , ( 100 select 'y' 101 from dba_triggers 102 where owner = u.username 103 and trigger_name = 'VPC_CONTEXT_TRG' 104 ) has_trg 105 from dba_role_privs r 106 , dba_users u 107 where r.granted_role = 'RECOVERY_CATALOG_OWNER' 108 and r.grantee = u.username 109 ) 110 loop 111 if ((u.has_rls = 'y' and u.has_trg = 'y') or has_vpc_users(u.catowner)) 112 then 113 execute immediate 114 'grant recovery_catalog_owner_vpd to ' 115 || dbms_assert.enquote_name(u.catowner); 116 execute immediate 117 'revoke recovery_catalog_owner from ' 118 || dbms_assert.enquote_name(u.catowner); 119 execute immediate 120 'grant create any context to ' 121 || dbms_assert.enquote_name(u.catowner); 122 execute immediate 123 'grant drop any context to ' 124 || dbms_assert.enquote_name(u.catowner); 125 end if; 126 end loop; 127 128 r('drop any synonym'); 129 r('create any trigger'); 130 r('create any synonym'); 131 r('create public synonym'); 132 r('administer database trigger'); 133 r('recovery_catalog_user'); 134 r('execute on dbms_rls'); 135 end; 136 / PL/SQL procedure successfully completed. ``` Ahora nos conectamos a RMAN y ejecutamos los siguientes comandos para crear el catálogo de recuperación: ```plaintext [oracle@rac-lab-001 ~]$ rman catalog rman_catalog/"*******"@hostrepo.domain.local:1521/pdb_rman.domain.local Recovery Manager: Release 19.0.0.0.0 - Production on Mon May 13 10:52:18 2024 Version 19.22.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to recovery catalog database RMAN> CREATE CATALOG; recovery catalog created ``` Listo! Ahora podríamos registrar una base de datos de la siguiente manera, en la que conectaremos a ésta y al catálogo de RMAN. ```plaintext rman target / catalog rman_catalog/"*******"@hostrepo.domain.local:1521/pdb_rman.domain.local RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete ``` Recordar añadir las entradas de TNSNames correspondientes para los targets que tengas que acceder al catálogo. ```plaintext RMAN_CATALOGO= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hostrepo.domain.local)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb_rman.domain.local) ) ) ```