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:
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.
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:
[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.
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.
RMAN_CATALOGO=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostrepo.domain.local)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb_rman.domain.local)
)
)
