RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
RECOVERY CATALOG RMAN
utility mainly used for Backup and Recovery operations for an Oracle Database.
RMAN
stores metadata (RMAN Repository) of its operations (backup/restore/recovery) typically in
the control file of the target database and optionally in Recovery Catalog Schema of the separate Oracle database, if Catalog is configured. A Recovery Catalog is a Schema that tracks backups and stores scripts in a separate database, which is useful to RMAN for Backup and Recovery situations. RMAN REPOSITORY The RMAN repository is the collection of metadata I.e. (the record of RMAN metadata about backup and
recovery
operations)
on
the
target
database
that
RMAN
uses
for
backup,
recovery,
and
maintenance. RMAN stores this information in the control file. The backup information can be retrieve either using LIST, REPORT and SHOW commands from RMAN prompt or using SQL queries from views of the catalog Schema. CATALOG SCHEMA & DATABASE
A separate database contains special schema i.e. (Recovery Catalog Schema) that the schema contains multiple objects to store backup, restore and recovery info of the target database. WHY RECOVERY CATALOG ? RMAN
can be configured in two modes.
They are CATALOG MODE & NO CATALOG MODE. It’s recommended to configure RMAN in Catalog Mode. Single Recovery Catalog can support multiple target databases. In no catalog mode, RMAN keeps the backup info in the reusable section of target database of the control file. If the target database control files are lost recovery can become tedious job for DBA’s. To avoid this situation Oracle has introduced Recovery Catalog. As we know a Recovery Catalog is a database Schema used by RMAN to store RMAN metadata about one or more Oracle databases. It creates redundancy for the RMAN repository stored in the control file of each target database. Here Recovery catalog servers as a secondary metadata repository. If target control files are lost, then the RMAN metadata still exists in the Recovery Catalog. More over the Recovery Catalog can store metadata history much longer (many years of backup history) than the control file. This is useful when we perform recovery that goes back in time than the history in the target database control file. A Recovery Catalog is required when you use RMAN in Data Guard environment. Only the Primary database is explicitly registered to the RMAN Catalog; and Physical Standby databases are registered automatically if they are connected as target while connected to the Recovery Catalog. Here RMAN uses db_unique_name parameter to identify one database from another. Some RMAN features function only when we use Recovery catalog. It’s possible to store RMAN Scripts in a Recovery Catalog. The advantage is Stored Scripts are, available to any RMAN Client that can connect to the target database and Recovery Catalog.
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
STORED SCRIPT A sequence of RMAN commands stored in the Recovery Catalog. We can use a Stored Scripts as an alternative to a command file for managing frequently used sequence of RMAN commands. The command create script is a RMAN command which is used to create Stored Scripts in Recovery Catalog. Stored Scripts can be Global or Local. A Local Script is created for the current target database only. The Global Scripts can be shared by all databases registered in the Recovery Catalog. CONTENTS OF THE RECOVERY CATALOG As we know the Recovery Catalog contains metadata about RMAN operations for registered target database. When RAMN is connected to a Recovery Catalog, RMAN obtains its metadata exclusively from the Catalog. The Catalog contains RMAN operations and following type of metadata including:
Stored Scripts RMAN configuration settings. Database Structure (tablespaces and data files) Backup sets and Backup pieces of data files and Archived Redo Log files. Image copies of data files and archived Redo Logs
DATABASE REGISTRATION The process of enrolling of a database in Recovery Catalog is called Registration. You can register more than one target database in the Recovery Catalog. Ex: you can register database prod1, prod2, prod3 in a Catalog database. RMAN identifies databases by unique database identifier (DBID). RECOVERY WITHOUT A RECOVERY CATALOG If no Recovery Catalog is used then it’s highly recommended to set CONTROL_FILE_RECORD_KEEP_TIME parameter value more than database backup Retention Period. The retention of the backup information in target database control file, depends on the parameter control_file_record_keep_time.
SYS> show parameter keep_time; NAME
TYPE
------------------------------------ ---------control_file_record_keep_time
integer
VALUE -----7
SYS> alter system set control_file_record_keep_time=40 scope=spfile; System altered. SYS> show parameter keep_time; NAME
TYPE
VALUE
------------------------------------ ----------- ------control_file_record_keep_time
integer
40
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
Even we specify bigger values for the parameter retention may NOT be guaranteed because control file grows up to Operating System beyond that is overwrites the existing backup information. So it is highly recommend to configure Recovery Catalog for Critical Production databases.
CREATING A RECOVERY CATALOG
SYS cannot be owner of the Catalog Do not use Target database for Recovery Catalog. Archive Log Mode is recommended for Catalog database. Ensure Catalog and Target database do NOT reside on same disk. Assign Separate Tablespace for Recovery Catalog Schema.
ON TARGET DATABASE
Enable Archive log (If it is not enabled) Configure the listener.ora Create the password file Verify RMAN backup location Directory Structure
CATALOG DATABASE
Configure the tnsnames.ora for target database. Create the tablespace to store rman catalog objects. Create a user and grant appropriate privilege called (recovery_catalog_owner) Assign RMAN tablespace to rmanuser as a default Connect to RMAN prompt and execute create catalog to create RMAN metadata tables. Connect to Target database through Catalog database and register the target database. Configure RMAN backup policies and take full backup **
SYSTEM ENVIRONMENT
DATABASE VERSION
: 11.2.0.1.0
Target Database Name
: prod
Catalog Database Name : catdb
on RHEL 4.8 on RHEL 4.8
192.168.241.131
SERVER1
CATALOG DATABASE SERVER
192.168.241.132
SERVER2
TARGET DATABASE SERVER
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
RECOVERY CATALOG CREATION
SYS> select name, log_mode, open_mode from v$database NAME
LOG_MODE
OPEN_MODE
--------- ------------ -------------------CATDB
ARCHIVELOG
READ WRITE
Create a dedicated tablespace to maintain Recovery Catalog in the catdb database.
SYS> create tablespace catalogtbs datafile '/u01/app/oracle/oradata/catdb/catalogtbs01.dbf' size 100m autoextend on maxsize unlimited; Tablespace created.
Creating a dedicated user which will be the owner of the Recovery Catalog. SYS> create user rmanuser identified by rmanuser default tablespace catalogtbs quota unlimited on catalogtbs; User created.
Grant the RECOVERY_CATALOG_OWNER role to the schema owner. This role provides with required privileges to the user to maintain Recovery Catalog.
SYS> grant recovery_catalog_owner to rmanuser; Grant succeeded.
The role recovery_catalog_owner has some privileges to query and maintain the Recovery Catalog. SYS> select privilege from dba_sys_privs where grantee ='RECOVERY_CATALOG_OWNER'; PRIVILEGE ---------------------------------------CREATE SYNONYM CREATE CLUSTER ALTER SESSION CREATE DATABASE LINK CREATE PROCEDURE CREATE SEQUENCE CREATE TABLE CREATE SESSION CREATE TYPE CREATE VIEW CREATE TRIGGER 11 rows selected.
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
SYS> grant connect, resource to rmanuser; Grant succeeded. SYS> conn rmanuser/rmanuser; Connected. RMANUSER> select * from tab; no rows selected
The actual process of the Recovery Catalog is performed in RMAN. The command CREATE CATALOG creates the catalog in the default table space of the user. $ rman catalog rmanuser/rmanuser Recovery Manager: Release 11.2.0.1.0 - Production on Mon Mar 14 00:09:05 2016 Copyright (c) 1982, 2009, Oracle and/or its affiliates.
All rights reserved.
connected to recovery catalog database RMAN> create catalog; recovery catalog created
Now connect as rmanuser and check all objects are created or not
RMANUSER> select count(*) from tab; COUNT(*) ---------142
CONFIGURE TNSNAMES.ORA ON SERVER2 192.168.117.132
$ cd $ORACLE_HOME/network/admin $ vi tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools.
PRODDB
=
(DESCRIPTION = (ADDRESS = (PROTOCOL=TCP)(HOST=192.168.117.131)(PORT=1521)) (CONNECT_DATA = (SERVER=DEDICATED) (SERVICE_NAME=prod) ) )
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
CONFIGURE LISTENER.ORA ON SERVER1 192.168.117.131
$ cd $ORACLE_HOME/network/admin $ vi listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME=prod) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (GLOBAL_DBNAME=prod) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL=TCP)(HOST=192.168.241.131)(PORT=1521)) ) )
START THE LISTENER & CHECK LISTENER STATUS [oracle@SERVER1 admin]$ lsnrctl start [oracle@SERVER1 admin]$ lsnrctl status ON CATALOG SERVER 192.168.117.132
$ tnsping PRODDB .. ... Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.117.131)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod))) OK (110 msec) CONFIGURE PASSWORD FILE ON THE TARGET SERVER $ cd $ORACLE_HOME/dbs $ export ORACLE_SID=prod [oracle@SERVER1 dbs]$ orapwd file=orapwprod password=dba entries=3 force=y [oracle@SERVER1 dbs]$ ls orapw* orapwprod
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
ON CATALOG SERVER 192.168.117.132
$ rman catalog rmanuser/rmanuser target sys/prod@PRODDB Recovery Manager: Release 11.2.0.1.0 - Production on Mon Mar 14 00:53:01 2016 Copyright (c) 1982, 2009, Oracle and/or its affiliates.
All rights reserved.
connected to target database: PROD (DBID=318150854) connected to recovery catalog database RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete
Verify that the registration was successful by running REPORT SCHEMA RMAN> report schema; Report of database schema for database with db_unique_name PROD List of Permanent Datafiles =========================== File Size(MB) Tablespace
RB segs Datafile Name
---- -------- -------------------- ------- -----------------------1
680
SYSTEM
YES
/u01/app/oracle/oradata/prod/system01.dbf
2
480
SYSAUX
NO
/u01/app/oracle/oradata/prod/sysaux01.dbf
3
50
UNDOTBS1
YES
/u01/app/oracle/oradata/prod/undotbs01.dbf
4
5
USERS
NO
/u01/app/oracle/oradata/prod/users01.dbf
5
100
EXAMPLE
NO
/u01/app/oracle/oradata/prod/example01.dbf
List of Temporary Files ======================= File Size(MB) Tablespace
Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- -------------------1
20
TEMP
32767
You can use the following command
/u01/app/oracle/oradata/prod/temp01.dbf
to verify registered databases on the recovery catalog
RMAN> list db_unique_name all; List of Databases DB Key
DB Name
DB ID
Database Role
Db_unique_name
------- ------- ----------------- ---------------
------------------
83
PROD
PROD
318150854
PRIMARY
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
VERIFY REGISTER DATABASE DETAIL THROUGH SQL
We can another option to verify rc_database view in rmanuser schema $ rlsqlplus rmanuser/rmanuser SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 14 01:19:52 2016 Copyright (c) 1982, 2009, Oracle.
All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options RMANUSER> select * from rc_database; DB_KEY
DBINC_KEY
DBID
NAME
RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- --------83
84
318150854
PROD
754488
12-MAR-16
CONFIGURE RMAN BACKUP POLICIES
$ rman catalog rmanuser/rmanuser target sys/prod@PRODDB Recovery Manager: Release 11.2.0.1.0 - Production on Mon Mar 14 05:37:17 2016 Copyright (c) 1982, 2009, Oracle and/or its affiliates.
All rights reserved.
connected to target database: PROD (DBID=318583963) connected to recovery catalog database RMAN> spool log to rman.log RMAN> show all; RMAN> spool log off; RAMN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; new RMAN configuration parameters: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete RMAN> configure default device type to disk; new RMAN configuration parameters: CONFIGURE DEFAULT DEVICE TYPE TO DISK; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
RECONFIGURE RMAN PARAMETERS
$ vi rman.log RMAN configuration parameters for database with db_unique_name PROD are: CONFIGURE RETENTION POLICY TO REDUNDANCY 2; # default CONFIGURE BACKUP OPTIMIZATION ON; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/rmanbkp/%F.ctl'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET; # default CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNNECT SYS/PROD@PRODDB; CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE; #default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u03/rmanbkp/snapcf_prod.f'; # default
Save and quit wq! EXECUTE THE COMMAND FILE
$ rman catalog rmanuser/rmanuser target sys/prod@PRODDB cmdfile=rman.log .. ... new RMAN configuration parameters: CONFIGURE RETENTION POLICY TO REDUNDANCY 2; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete .. ... [Trimmed] new RMAN configuration parameters: CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u03/rmanbkp/snapcf_prod.f'; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
RMAN> report need backup; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 2 Report of files with less than 2 redundant backups File #bkps Name ---- ----- ----------------------------------------------------1
0
/u01/app/oracle/oradata/prod/system01.dbf
2
0
/u01/app/oracle/oradata/prod/sysaux01.dbf
3
0
/u01/app/oracle/oradata/prod/undotbs01.dbf
4
0
/u01/app/oracle/oradata/prod/users01.dbf
5
0
/u01/app/oracle/oradata/prod/example01.dbf
TAKE FULL DATABASE BACKUP
RMAN> backup database plus archivelog TAG=FULLBKP; RMAN> list backup; RMAN> list backup summary;
SCENARIO FOR LOSS OF ALL DATAFILES
SYS> select name, dbid, open_mode from v$database; NAME
DBID
OPEN_MODE
--------- --------- -------------PROD
318631334
READ WRITE
SYS> select name from v$datafile; NAME ---------------------------------------------/u01/app/oracle/oradata/prod/system01.dbf /u01/app/oracle/oradata/prod/sysaux01.dbf /u01/app/oracle/oradata/prod/undotbs01.dbf /u01/app/oracle/oradata/prod/users01.dbf /u01/app/oracle/oradata/prod/example01.dbf # MOVING ALL DATAFILES TO ANOTHER LOCATION $ cd /u01/app/oracle/oradata/prod $ mv *.dbf /u03/bkp # YOU WOULD ENCOUNTER FOLLOWING ORA- ERRORS (ORA-01110) ORA-01110: data file 1: '/u01/app/oracle/oradata/prod/system01.dbf' SYS> shut abort; ORACLE instance shut down.
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
# CONNECTING RMAN PROMPT $ rman target / ... connected to target database (not started) # STARTUP THE DATABASE INSTANCE RMAN> startup nomount; Oracle instance started Total System Global Area Fixed Size
1272213504 bytes 1336260 bytes
Variable Size
754977852 bytes
Database Buffers
503316480 bytes
Redo Buffers
12582912 bytes
# MOUNT THE DATABASE RMAN> alter database mount; database mounted released channel: ORA_DISK_1 # RESTORE DATABASE RMAN> restore database preview; RMAN> restore database; # RECOVER DATABASE RMAN> recover database preview; RAMN> recover database; # OPEN THE DATABASE SYS> alter database open; database opened.
LOSS OF FULL DATABASE
# CHECK ALL FILES LOCATION AT DATABASE LEVEL SYS> select name, log_mode, open_mode from v$database; ... SYS> select name from v$datafile; ... SYS> select name from v$controlfile; ... SYS> select member from v$logfile; ...
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
# MOVE ALL PHYSICAL FILES TO ANOTHER LOCATION $ cd /u0/app/oracle/oradata/prod $ mv *.* /u03/bkp/ $ cd /u0/app/oracle/flash_recovery_area/prod $ mv *.ctl /u03/bkp SQL> conn scott/tiger ERROR: ORA-00604: error occurred at recursive SQL level 2 ORA-01116: error in opening database file 1 ORA-01110: data file 1: '/u01/app/oracle/oradata/prod/system01.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3 $ rlrman target / .. ... connected to target database (not started) RMAN> startup nomount; Oracle instance started Total System Global Area Fixed Size
1272213504 bytes 1336260 bytes
Variable Size
754977852 bytes
Database Buffers
503316480 bytes
Redo Buffers
12582912 bytes
# RESTORE THE CONTROL FILE RMAN> restore controlfile; ... RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP RMAN> restore controlfile from autobackup; ... RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece # ON CATALOG SERVER (192.168.117.132) # CHECK CONTROL FILE BACKUP PIECE NAME AND ITS LOCATION $ rlrman catalog rmanuser/rmanuser target sys/prod@PRODDB .. ... connected to target database: PROD (not mounted) connected to recovery catalog database
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
RAMN> list backup; .. ... BS Key
Type LV Size
Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------1138
Full
9.64M
BP Key: 1140
DISK
00:00:02
Status: AVAILABLE
18-MAR-16
Compressed: NO
Tag: TAG20160318T173828
Piece Name: /u03/rmanbkp/c-318631334-20160318-04.ctl SPFILE Included: Modification time: 18-MAR-16 SPFILE db_unique_name: PROD Control File Included: Ckp SCN: 826247
Ckp time: 18-MAR-16
RMAN> restore controlfile from '/u03/rmanbkp/c-318631334-20160318-04.ctl'; Starting restore at 19-MAR-16 using channel ORA_DISK_1 channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 output file name=/u01/app/oracle/oradata/prod/control01.ctl output file name=/u01/app/oracle/flash_recovery_area/prod/control02.ctl Finished restore at 19-MAR-16 # MOUNT THE DATABASE RMAN> alter database mount; database mounted released channel: ORA_DISK_1 # RESTORE THE DATABASE RMAN> restore database preview; RMAN> restore database; # RECOVER THE DATABASE RMAN> recover database preview; RMAN> recover database; RMAN> alter database open; ... ORA-01589: must use RESETLOGS or NORESETLOGS option for database open RMAN> alter database open resetlogs; database opened RMAN> list incarnation of database; ...
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
STORED SCRIPTS The command create script is used to create stored scripts in Recovery Catalaog. Stored scripts can be two types
Global Stored Scripts
Local Stored Scripts
Global Stored Scripts can be executed for all databases which are registered in the recovery catalog. Local Stored Scripts is associated with target database to which RMAN is connected when the script is created; and can be executed when you are connected to the target database.
# CONNECT RMAN TARGET DATABASE WITH RECOVERY CATALOG $ rman catalog rmanuser/rmanuser target sys/prod@PRODDB .. ... connected to target database: PROD (DBID=318631334) connected to recovery catalog database # CREATING A LOCAL SCRIPT RMAN> create script bkp_report { SHOW ALL; REPORT NEED BACKUP; REPORT OBSOLETE; } # CREATING A GLOBAL SCRIPT RMAN> create global script full_bkp_global { backup database plus archivelog; delete obsolete; } # EXECUTE THE SCRIPT RMAN> run {execute script full_bkp;} # LISTING STORED SCRIPTS RMAN> list script names; RMAN> list all script names; RMAN> list global script names; # DISPLAYING A STORED SCRIPTS RMAN> print script script_name; RMAN> print script bkp_report;
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
# UPDATE THE STORED SCRIPT RMAN> replace script bkp_report_global { backup database plus archivelog; } # DELETING SCRIPTS RMAN> delete script script_name; RMAN> delete script bkp_report; RMAN> delete global script script_name; RMAN> delete global script bkp_report_global;
REGISTER THE NEW DATABASE IN RECOVERY CATALOG
# ON THE CATALOG DATABASE SERVER $ rman catalog rmanuser/rmanuser target sys/devdb@DEVDB .. ... connected to target database: DEVDB (DBID=811474030) connected to recovery catalog database RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete RMAN> list incarnation of database; List of Database Incarnations DB Key
Inc Key DB Name
DB ID
STATUS
Reset SCN
Reset Time
------- ------- -------- ---------------- --- ---------- ---------1742
1755
DEVDB
811474030
PARENT
1
1742
1743
DEVDB
811474030
CURRENT 754488
13-AUG-09 17-MAR-16
UNREGISTER THE DATABASE IN RECOVERY CATALOG
Remark: To unregister a database from the recovery catalog, use UNREGISTER DATABASE;
RMAN> unregister database; database name is "DEVDB" and DBID is 811474030 Do you really want to unregister the database (enter YES or NO)? YES database unregistered from the recovery catalog
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
VIRTUAL PRIVATE CATALOG RMAN Recovery Catalog consolidate backup metadata of multiple target databases. The Recovery Catalog owner can manage and maintain all metadata of these target databases in a repository. Several RMAN metadata of all registered databases are stored in the same repository. In large database environments more than one DBA is managing the same repository. Any user has access privilege to a Recovery catalog, that user could view data from all the databases registered in the recovery catalog. All users of an RMAN Recovery Catalog have full privileges to insert, update, and delete any metadata in the Recovery Catalog. There was no way to provide partial access to the Recovery Catalog before 11g. To address this problem, Oracle has introduced VPC. VPC stands for Virtual Private Catalog. The VPC has been introduced in Oracle 11g and is a subset of the base Recovery Catalog. This methods allows Recovery Catalog owner to create Virtual Catalogs for each database. Using this feature we can grant restricted access on RMAN Catalog to some users, so that they can only access a limited number of databases that are registered with RAMN Catalog. Now there is a logical boundary between each catalog for different databases; because administrators of the different database can virtually connect their own Catalog but they will not able to see metadata of other databases thus enhancing security by restricting access to others metadata. Each restricted user has full read/write access to his own Virtual Private Catalog.
The entire Recovery Catalog can be a Base Recovery catalog. A VPC is a set of synonyms and views that refers to a Base Recovery Catalog. It’s possible to create one or more Virtual Private Catalog; the mechanism for Virtual Private Catalog exist in the Recovery Catalog Schema itself. The VPC owner can create a local stored script, but only has read-only access to a global stored script.
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
STEPS TO CREATE VPC
Create RMAN Base Catalog.
Create Virtual Private Catalog owner in Recovery Catalog Database.
Grant desired Privileges to VPC owner
Register the new Target database into the Recovery Catalog.
Create the Virtual Private Catalog. NETWORK CONNECTIVITY
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DB SERVER1 192.168.117.130
DB SERVER2 192.168.1171.131
DATABASE - ORCL
DATABASE - DEVDB
VPC_USER – vpc_orcl
VPC_USER - vpc_test
-
192.168.117.131 DATBASE - PROD
CATDB -------------DB SERVER3 192.168.117.132 ------------RECOVRY CATALOG DATABASE - CATDB -----
SYSTEM INFORMATION
BASE RECOVEY CATALOG DB
CATDB (11.2.0.1)
BASE RECOVEY CATALOG OWBER
rmanuser
VPC_USERS
vpc_dev, vpc_orcl
USER APPLICATON DATABASES
DEVDB, ORCL, PROD (11.2.0.1)
In this scenario, there are 3 databases namely devdb, orcl and prod. Already prod database has been registered with Recovery Catalog owned by the user RMANUSER.
# CREATE A TABLESPACE FOR VIRTUAL PRIVATE CATALOG SYS> crete tablespace vpctbs datafile '/u01/app/oracle/oradata/catdb/vpctbs01.dbf' size 100m auto extend on maxsize unlimited; Tablespace created. # CREATE DEDICATED DATABASE USERS FOR VIRTUAL PRIVATE CATALOG SYS> create user vpc_dev identified by vpcdev default tablespace vpctbs temporary tablespace temp quota unlimited on vpctbs; User created.
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
SYS> create user vpc_orcl identified by vpcorcl default tablespace vpctbs temporary tablespace temp quota unlimited on vpctbs; User created. SYS> select username, default_tablespace from dba_users where username like 'VPC%'; USERNAME
DEFAULT_TABLESPACE
------------------------------ -----------------------------VPC_ORCL
VPCTBS
VPC_DEV
VPCTBS
# GRANTING RECOVERY_CATALOG_OWNER PRIVILEGE TO VPC USERS SYS> grant connect, resource, recovery_catalog_owner to vpc_dev; Grant succeeded. SYS> grant connect, resource, recovery_catalog_owner to vpc_orcl; Grant succeeded.
CONFIGURE NETWORK FILES Need to add an entry for devdb for databases in listener.ora file on DEBSERVER1 Need to add an entry for orcl for databases in listener.ora file on DEBSERVER2 Need to add an entry for devdb and orcl databases in tnsnames.ora file on DBSERVER3 $ cat tnsnames.ora DBSERVER3 -(192.168.117.132) # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. DEVDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.117.130)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = devdb) ) ) ORCLDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.117.131)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
$ cat listener.ora DBSERVER1 - (192.168.117.130) # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.117.130)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle SID_LIST_LISTENER = (SID_LIST (SID_DESC
= =
(SID_NAME = orcl) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) ) )
$ cat listener.ora DBSERVER2 - (192.168.117.131) # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.117.131)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle SID_LIST_LISTENER = (SID_LIST (SID_DESC
= =
(SID_NAME = devdb) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) ) (SID_DESC = (SID_NAME = prod) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) ) )
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
REGISTER THE DATABASE TO RECOVERY CATALOG Now register the devdb database to the Recovery Catalog using Recovery Catalog owner (rmanuser) on DBSERVER3 – (192.168.117.132)
$ rman catalog rmanuser/rmanuser target sys/devdb@DEVDB .. ... connected to target database: DEVDB (DBID=811474030) connected to recovery catalog database RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete
Now we need to register the orcl database to the Recovery Catalog using Recovery Catalog owner (rmanuser) on DBSERVER3 – (192.168.117.132)
$ rman catalog rmanuser/rmanuser target sys/orcl@ORCLDB .. ... connected to target database: ORCL (DBID= 1434957320) connected to recovery catalog database RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete
Now we need to grant privileges to (vpc_orcl and vpc_dev) users to access RMAN metadata stored on CATDB from rmanuser.
$ rman CATALOG rmanuser/rmanuser Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 22 18:23:26 2016 Copyright (c) 1982, 2009, Oracle and/or its affiliates.
All rights reserved.
connected to recovery catalog database RMAN> grant catalog for database devdb to VPC_DEV; Grant succeeded. RMAN> grant catalog for database orcl to VPC_ORCL; Grant succeeded. RMAN> exit
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
CREATE VIRTUAL CATALOG FOR DEVDB DATABASE
$ rman catalog vpc_dev/vpcdev .. ... connected to recovery catalog database RMAN> create virtual catalog; found eligible base catalog owned by RMANUSER created virtual catalog against base catalog owned by RMANUSER
CREATE VIRTUAL CATALOG FOR ORCL DATABASE
$ rman catalog vpc_orcl/vpcorcl .. ... connected to recovery catalog database RMAN> create virtual catalog; found eligible base catalog owned by RMANUSER created virtual catalog against base catalog owned by RMANUSER
Connect to catalog owner vpc_orcl and list registered databases:
$ rman catalog vpc_orcl/vpcorcl .. ... connected to recovery catalog database RMAN> list incarnation; List of Database Incarnations DB Key
Inc Key DB Name
DB ID
STATUS
Reset SCN
Reset Time
------- ------- -------- ---------------- --- ---------- ---------2104
2119
ORCL
1434957320
PARENT
1
2104
2105
ORCL
1434957320
CURRENT 754488
13-AUG-09 22-MAR-16
# CHECK TOTAL OBJECTS FROM VPC_ORCL SCHEMA SYS> conn vpc_orcl/vpcorcl Connected. VPC_ORCL> select count(*) from tab; COUNT(*) ---------101
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
Connect to catalog owner vpc_orcl and list registered databases:
$ rman catalog vpc_dev/vpcdev .. ... connected to recovery catalog database RMAN> list incarnation; List of Database Incarnations DB Key
Inc Key DB Name
DB ID
STATUS
Reset SCN
Reset Time
------- ------- -------- ---------------- --- ---------- ---------1806
1819
DEVDB
811474030
PARENT
1
13-AUG-09
1806
1807
DEVDB
811474030
CURRENT 754488
17-MAR-16
SYS> conn vpc_dev/vpcdev Connected. VPC_DEV> select count(*) from tab; COUNT(*) ---------101
CONNECT RECOVERY CATALOG OWNER If we connect as the original RMAN catalog owner (rmanuser) we can see all the registered databases
$ rman catalog rmanuser/rmanuser .. ... connected to recovery catalog database RMAN> list db_unique_name all; List of Databases DB Key
DB Name
DB ID
Database Role
Db_unique_name
------- ------- ----------------- ---------------
------------------
462
PROD
318631334
PRIMARY
PROD
1806
DEVDB
811474030
PRIMARY
DEVDB
2062
ORCL
1434957320
PRIMARY
ORCL
SYS> select * from rmanuser.rc_database; DB_KEY
DBINC_KEY
DBID
NAME
RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ----------2062
2063
1434957320
ORCL
754488
22-MAR-16
462
1160
318631334
PROD
849515
19-MAR-16
1806
1807
811474030
DEVDB
754488
17-MAR-16
Oracle DBA Technology explored by Gunasekaran , Thiyagu
RECOVERY CATALOG & VIRTUAL PRIVATE CATALOG | ORACLE 11g
DROP THE VIRTUAL PRIVATE CATALOG $ rman catalog vpc_dev/vpcdev .. ... connected to recovery catalog database RMAN> drop catalog; recovery catalog owner is VPC_DEV enter DROP CATALOG command again to confirm catalog removal RMAN> drop catalog; recovery catalog dropped
Oracle DBA Technology explored by Gunasekaran , Thiyagu