Skip to content

RMAN Catalog Setup

November 4, 2013

Index

RMAN Catalog setup and Its Benefits.
Typically, you store the catalog in a dedicated database. A recovery catalog provides the following benefits:
  1. A recovery catalog creates redundancy for the RMAN repository stored in the control file of each target database. The recovery catalog serves as a secondary metadata repository. If the target control file and all backups are lost, then the RMAN metadata still exists in the recovery catalog.
  2. A recovery catalog centralizes metadata for all your target databases. Storing the metadata in a single place makes reporting and administration tasks easier to perform.
  3. A recovery catalog can store metadata history much longer than the control file. This capability is useful if you have to do a recovery that goes further back in time than the history in the control file.
  4. You can store metadata about multiple incarnations of a single target database in the catalog. Hence, you can restore backups from any incarnation.
  5. When restoring and recovering to a time when the database files that exist in the database are different from the files recorded in the mounted control file, the recovery catalog specifies which files that are needed. Without a catalog, you must first restore a control file backup that lists the correct set of database files.
  6. If the control file is lost and must be restored from backup, and if persistent configurations have been made to automate the tape channel allocation, these configurations are still available when the database is not mounted.
  7. you can store RMAN scripts in a recovery catalog. The chief advantage of a stored script is that it is available to any RMAN client that can connect to the target database and recovery catalog.

RMAN Repository Server     : mgrmansrv  Repository DB name : RMAN11G
DB Server Name                 : mgd041srv   DB to backup               : D041

Setup up tnsnames.ora file with RMAN DB  at All DB HOME’s.
=========================================================================
RMAN11G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = mgrmansrv)(PORT = 1521))
)
(CONNECT_DATA =
(SID = RMAN11G)
)
)

OR

RMAN11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mgrmansrv)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RMAN11G)
)
)

Setting Up  RMAN Repository
========================================
1) echo $ORACLE_SID
RMAN11G

sqlplus / as sysdba

create tablespace rman_data datafile ‘/oradata/RMAN11G/rman_data01.dbf’ size 1000m autoextend on next 50m maxsize 10000m;

CREATE USER rman IDENTIFIED BY rman#11g QUOTA UNLIMITED ON rman_data ;

grant connect, resource to rman;

grant recovery_catalog_owner to rman;

2) Now that the RMAN user account exists in the repository database, we can start RMAN,
connect to the catalog, and initialize the repository with the create catalog command:

rman catalog rman/rman#11g@rman11g

create catalog;

3) Register the Database with RMAN Catalog DB

echo $ORACLE_SID
d041

rman target / catalog rman/rman#11g@rman11g

register database;
show all;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
report schema;
report schema at time=’30-aug-07′;

mgd041srv:/opt/oracle: echo $ORACLE_HOME
d041

mgd041srv:/opt/oracle: rman target / catalog rman/rman#11g@rman11g

Recovery Manager: Release 11.2.0.1.0 – Production on Mon Jan 24 05:28:56 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: D041 (DBID=3435832677)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> show all;

starting full resync of recovery catalog
full resync complete
RMAN configuration parameters for database with db_unique_name D041 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
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 ‘/u01/app/oracle/product/11.2.0/dbs/snapcf_D041.f’; # default

As a rule of thumb, you should set CONTROL_FILE_RECORD_KEEP_TIME to several days,beyond your actual recovery window to ensure that backup records are retained in the controlfile.
The default is 7 days for TARGET DB.

Target (D041):
===============

rman target / catalog rman/rman#11g@rman11g

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF  14 DAYS;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;

new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> exit

col name for a40
col VALUE for a60
set line 200
SQL> select * from v$rman_configuration;

     CONF# NAME                                     VALUE
---------- ---------------------------------------- ------------------------------------------------------------
         1 CONTROLFILE AUTOBACKUP                   ON
         2 RETENTION POLICY                         TO RECOVERY WINDOW OF 14 DAYS
         3 DEVICE TYPE                              DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1

Catalog (RMAN11G):
====================
SQL> connect rman/rman#11g@rman11g
Connected.

SQL> select db_key, db_unique_name, name, value from rman.rc_rman_configuration;

    DB_KEY DB_UNIQUE_NAME  NAME                                     VALUE
---------- --------------- ---------------------------------------- ------------------------------------------------------------
         2 D041            CONTROLFILE AUTOBACKUP                   ON
         2                 RETENTION POLICY                         TO RECOVERY WINDOW OF 14 DAYS
         2 D041            DEVICE TYPE                              DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1

If we were using RMAN to back up another database, this view would contain other values for
DB_KEY and DB_UNIQUE_NAME for other target databases with non-default RMAN parameters.

SQL> select name from v$database;

NAME
———
RMAN11G

SQL> select * from v$rman_configuration;

no rows selected

As above we are not using RMAN to back up the RMAN11G database, the views V$RMAN_* are empty.


############################################################################################################
Anytime, If you are upgrading the Target DB then upgrade the catalog as well as below:
############################################################################################################
Upgrade catalog:
======================

mgd041srv:/opt/oracle: echo $ORACLE_SID
d041

mgd041srv:/opt/oracle: rman target / catalog rman/rman#11g@rman11g

Recovery Manager: Release 11.2.0.2.0 – Production on Wed May 18 05:57:17 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: D041 (DBID=2242370417)
connected to recovery catalog database
PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.01 in RCVCAT database is not current
PL/SQL package RMAN.DBMS_RCVMAN version 11.02.00.01 in RCVCAT database is not current

RMAN> upgrade catalog

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN>
RMAN> upgrade catalog

recovery catalog upgraded to version 11.02.00.02
DBMS_RCVMAN package upgraded to version 11.02.00.02
DBMS_RCVCAT package upgraded to version 11.02.00.02

RMAN>

What Tapes were used for the backup?

To see what tapes were used for a particular backup, run this query against the RMAN catalog database:

SELECT media, start_time, completion_time FROM rc_backup_piece
 WHERE db_id = (SELECT db_id FROM rc_database WHERE UPPER(name) = 'ORCL')
   AND completion_time BETWEEN TO_DATE('27-DEC-2007 12:38:36','DD-MON-YYYY HH24:MI:SS')
                           AND TO_DATE('28-DEC-2007 01:39:27','DD-MON-YYYY HH24:MI:SS')
/

Unregistering a Target Database from the Recovery Catalog

RMAN can unregister a database as well as register it. Make sure this procedure is what you intend, because if you make a mistake, then must reregister the database. In this case, you lose any metadata that is older than the CONTROLFILE_RECORD_KEEP_TIME setting in the target database control file.

To unregister a database:

  1. Start RMAN and connect to the target database. Note down the DBID value that is displayed when you use RMAN to connect to the target database. For example, enter:
    % rman TARGET / CATALOG rman/cat@catdb 
    
    connected to target database: D041(DBID=1237603294)
    connected to recovery catalog database
    
  2. List the copies and backup sets recorded in the repository (refer to Listing RMAN Backups, Copies, and Database Incarnations). For example, enter:
    LIST BACKUP SUMMARY;
    
    List of Backups
    ===============
    Key     TY LV S Device Type Completion Time #Pieces #Copies Tag
    ------- -- -- - ----------- --------------- ------- ------- ---
    19      B  A  A DISK        08-FEB-02       1       1       TAG20020208T155239
    20      B  F  A DISK        08-FEB-02       1       1       TAG20020208T155242
    21      B  A  A DISK        08-FEB-02       1       1       TAG20020208T155331
    22      B  A  A DISK        08-FEB-02       1       1       TAG20020208T155604
    
  3. Run DELETE statements to delete all existing physical backups (refer to “Deleting Backups and Copies”). For example:
    DELETE BACKUP DEVICE TYPE sbt;
    DELETE BACKUP DEVICE TYPE DISK;
    

    RMAN will list the backups that it intends to delete and prompt for confirmation before deleting them.

  4. Use SQL*Plus to connect to the recovery catalog database as the catalog owner, then execute the following query in the recovery catalog to find the correct row of the DB table, setting DB_ID equal to the value you obtained from step 1. For example, enter:
    % sqlplus rman/cat@catdb
    SQL> SELECT DB_KEY, DB_ID FROM DB WHERE DB_ID = 1237603294;
    

    This query should return exactly one row.

    DB_KEY     DB_ID      
    ---------- ----------
             1 1237603294 
    1 row selected.
    
  5. While still connected to the recovery catalog, enter the following, where DB_KEY and DB_ID are the corresponding columns from the row you got from the query in step 4:
    SQL> EXECUTE dbms_rcvcat.unregisterdatabase(db_key, db_id)
    

    For example, enter:

    SQL> EXECUTE dbms_rcvcat.unregisterdatabase(1, 1237603294)

Without RMAN Catalaog DB:

Monitoring the Overwriting of Control File Records

When you do not use a recovery catalog, the control file is the sole source of information about RMAN backups and copies. As you make backups and copies, Oracle adds new records to the control file. These records are circularly reused, which means that Oracle overwrites older records.

The following initialization parameter determines the minimum age in days of a record before it can be overwritten:

CONTROL_FILE_RECORD_KEEP_TIME = integer

For example, if the parameter value is 14, then any record aged 14 days and older is a candidate for reuse. Information in an overwritten record is lost.

What happens when Oracle needs to add new records to the control file, but the oldest record is less than the value specified in CONTROL_FILE_RECORD_KEEP_TIME? The following steps occur:

  1. Oracle attempts to expand the size of the control file, which it can only do if the underlying operating system file can be expanded.
  2. If it cannot expand the control file, then Oracle overwrites the oldest record–regardless of whether its age is less than the CONTROL_FILE_RECORD_KEEP_TIME value–and logs this action in the alert.log.

Hence, if you are not using a recovery catalog, then set the CONTROL_FILE_RECORD_KEEP_TIME value to slightly longer than the oldest file that you need to keep. For example, if you back up the database once a week, then you need to keep every backup at least a week. Set CONTROL_FILE_RECORD_KEEP_TIME to a value such as 10 or 14.

Managing the Overwriting of Control File Records: Scenario

Assume the following scenario:

  • You do not use a recovery catalog.
  • CONTROL_FILE_RECORD_KEEP_TIME is set to 14.
  • All records currently in the control file are between 1 and 13 days old.
  • The control file is at the maximum size permitted by the operating system.

You make a backup of the database. Because Oracle cannot expand the control file beyond the operating system file size limit, it begins overwriting records in the control file, starting with those records aged 13 days. For each record that it overwrites, it records an entry in the alert.log that looks something like the following:

krcpwnc: following controlfile record written over:  
RECID #72 Recno 72 Record timestamp  
07/28/00 22:15:21  
Thread=1 Seq#=3460  
Backup set key: stamp=372031415, count=17  
Low scn: 0x0000.3af33f36  
07/27/00 21:00:08  
Next scn: 0x0000.3af3871b  
07/27/00 23:23:54  
Resetlogs scn and time  
scn: 0x0000.00000001  
08/05/99 10:46:44  
Block count=102400 Blocksize=512


To guard against this type of scenario, use a recovery catalog. If you cannot use a catalog, then do the following if possible:

  • Store the control file in a file system rather than raw disk so that it can expand as needed.
  • Monitor the alert.log to make sure that Oracle is not overwriting control file records.
Advertisements

From → Rman

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: