CATALOG

The Catalog chapter covers the following topics:

1-set-catalog

2-create-catalog

3-register-database

4-verify-registration

5-manual-backup-registration

6-create-scripts

7-export-catalog

8-query-catalog

9-get-catalog-version

10-upgrade-catalog

11-unregister-database

12-drop-catalog

1-set-catalog                                                          

This script create the rman user that will be the owner of the rman catalog, on the catalog database.

The catalog database is usually a small database it contains and maintains the metadata of all rman backups performed using the catalog.

#!/usr/bin/tcsh

source ./set-environment

sqlplus $dbauser/$dbapwd@$rmandb as sysdba<<eof

@sqlenv

set echo on

CREATE USER rman IDENTIFIED BY cat

TEMPORARY TABLESPACE temp

DEFAULT TABLESPACE users

QUOTA UNLIMITED ON users;

GRANT RECOVERY_CATALOG_OWNER TO rman;

exit;

eof

exit

2-create-catalog

Using the rman user created on step 1, this script connects to the catalog database using the rman command line interface, and creates the rman catalog.

The rman catalog is a set of database objects that will be used to store the rman backups metadata.

#!/usr/bin/tcsh

source ./set-environment

rman CATALOG $rmanuser/$rmanpwd@$rmandb <<eof

CREATE CATALOG;

eof

exit

3-register-database

Once the rman catalog is set, the databases to be backed up need to be registered with it.

In this case we execute rman connecting to the database to be registered, the TARGET and to the CATALOG database.

#!/usr/bin/tcsh

source ./set-environment

echo executing command : REGISTER DATABASE

echo

rman TARGET $dbauser/$dbapwd@$datadb CATALOG $rmanuser/$rmanpwd@$rmandb <<eof

REGISTER DATABASE;

eof

exit

4-verify-registration

Once a databases is registered with rman, we can get information about it from the catalog.

In this case we execute the report schema command to get information.

#!/usr/bin/tcsh

source ./set-environment

echo Executing Command : REPORT SCHEMA

echo

rman TARGET $dbauser/$dbapwd@$datadb CATALOG $rmanuser/$rmanpwd@$rmandb <<eof

REPORT SCHEMA;

eof

exit

5-manual-backup-registration

Manual backups, made using Rman without a recovery catalog or user scripts, can be cataloged and made available to the rman catalog.

In this example a controlfile backup is made using rman in NOCATALOG mode, afterwards the backup is catalogued.

#!/usr/bin/tcsh

source ./set-environment

echo Executing command : backup current controlfile

echo

rman TARGET $dbauser/$dbapwd@$datadb NOCATALOG<<eof

backup current controlfile;

list backup;

exit;

eof

echo

echo Type file name to catalog

set fname = $<

echo

echo Executing Command : CATALOG backuppiece '<filename>'

echo

rman TARGET $dbauser/$dbapwd@$datadb CATALOG $rmanuser/$rmanpwd@$rmandb <<eof

CATALOG backuppiece '$fname';

eof

exit

6-create-scripts

Rman tasks can be run using scripts, that can be stored on the rman catalog.

Global scripts will be available to all databases, non-global scripts will be available only to the target database.

This example shows how to create a global script.

#!/usr/bin/tcsh

source ./set-environment

echo Executing Command : CREATE GLOBAL SCRIPT

echo

rman TARGET $dbauser/$dbapwd@$datadb CATALOG $rmanuser/$rmanpwd@$rmandb <<eof

CREATE GLOBAL SCRIPT global_full_backup COMMENT 'use only with ARCHIVELOG mode databases'

{ BACKUP DATABASE PLUS ARCHIVELOG;

DELETE NOPROMPT OBSOLETE;

}

LIST GLOBAL SCRIPT NAMES;

eof

exit

7-export-catalog

The database holding the rman catalog should be protected against information loss, the minimum level of protection can be implemented by executing a full export of the catalog schema.

In case of catalog loss an import of the schema on a new or existing database will make the catalog available again.

#!/usr/bin/tcsh

source ./set-environment

echo Executing Command : exp $rmanuser/$rmanpwd@$rmandb file=rman-catalog-export.dmp

echo

exp $rmanuser/$rmanpwd@$rmandb file=rman-catalog-export.dmp owner=rman feedback=100

exit

8-query-catalog

In some specific situations it may be useful to query directly the recovery catalog. to do so we need to know the Database ID (dbid) and the DB Key of the database we want to query data for on the recovery catalog.

#!/usr/bin/tcsh

source ./set-environment

clear

echo

echo Querying Catalog Views for the Target DB_KEY or DBID Values

echo DB_KEY is used only in the recovery catalog.

echo You can determine the DBID by looking at the output displayed when RMAN          

        connects to the database

echo or by querying a V\$DATABASE view as in the following:

echo

echo select dbid from v\$database

echo

set v_db=v\$database

sqlplus -s $dbauser/$dbapwd@$datadb as sysdba<<eof

@sqlenv

SELECT DBID FROM $v_db;

exit

eof

echo

echo You can then obtain the DB_KEY on the Catalog Database based on the DBID

echo Enter the DBID

set v_dbid = $<

echo

echo select db_key from rc_database where dbid=$v_dbid;

sqlplus -s $rmanuser/$rmanpwd@$rmandb <<eof

@sqlenv

SELECT DB_KEY FROM RC_DATABASE WHERE DBID = $v_dbid;

eof

echo

echo Having the DB_KEY we can execute other queries on the catalog database

echo

echo Enter the DB_KEY

set v_dbkey = $<

echo

echo To obtain the backups per incarnation of a database you can execute the

        following script:

echo

echo SELECT DBINC_KEY,BS_KEY, BACKUP_TYPE, COMPLETION_TIME

echo FROM RC_DATABASE_INCARNATION i, RC_BACKUP_SET b

echo WHERE i.DB_KEY = $v_dbkey

echo AND i.DB_KEY = b.DB_KEY ;

sqlplus -s $rmanuser/$rmanpwd@$rmandb <<eof

@sqlenv

SELECT DBINC_KEY,BS_KEY, BACKUP_TYPE, COMPLETION_TIME

FROM RC_DATABASE_INCARNATION i, RC_BACKUP_SET b

WHERE i.DB_KEY = $v_dbkey

AND i.DB_KEY = b.DB_KEY ;

eof

echo

echo For querying RC_BACKUP_FILES you must call DBMS_RCVMAN.SETDATABASE ,

        with the DBID of a database

echo registered in the catalog, the fourth parameter must be DBID.

echo The other parameters must all be NULL.

echo

echo "CALL DBMS_RCVMAN.SETDATABASE(null,null,null,$v_dbid,null);"

echo

sqlplus -s $rmanuser/$rmanpwd@$rmandb <<eof

@sqlenv

CALL DBMS_RCVMAN.SETDATABASE(null,null,null,$v_dbid,null);

select BACKUP_TYPE,STATUS,TAG,BYTES,COMPLETION_TIME

from RC_BACKUP_FILES;

eof

9-get-catalog-version

This example show how to get the catalog version on the rman catalog database

#!/usr/bin/tcsh

source ./set-environment

clear

echo

echo "SELECT * FROM rcver;"

echo

sqlplus -s $rmanuser/$rmanpwd@$rmandb <<eof

@sqlenv

SELECT * FROM rcver;

eof

echo

exit

10-upgrade-catalog

This example show how to upgrade the catalog to the last version, the upgrade command needs to be entered twice (to confirm catalog upgrade) to get it executed.

#!/usr/bin/tcsh

source ./set-environment

rman TARGET $dbauser/$dbapwd@$datadb CATALOG $rmanuser/$rmanpwd@$rmandb <<eof

UPGRADE CATALOG;

UPGRADE CATALOG;

Eof

11-unregister-database

If a database is not longer required on the rman catalog it can be unregistered.

This command needs to be executed with the 'noprompt' option if run from within a script, otherwise it assumes NO as response to the confirmation request.

Note that rman metadata is always stored also on the controlfile, if a database is unregistered from the catalog, it can be afterwards registered again and all backups contained in the controlfile will be cataloged

#!/usr/bin/tcsh

source ./set-environment

rman TARGET $dbauser/$dbapwd@$datadb CATALOG $rmanuser/$rmanpwd@$rmandb <<eof

UNREGISTER DATABASE;

Eof

12-drop-catalog

An rman catalog can be removed with the drop catalog command. If executed from a script the command needs to be entered twice to get it run.

#!/usr/bin/tcsh

source ./set-environment

echo Executing Command : DROP CATALOG

echo

rman CATALOG $rmanuser/$rmanpwd@$rmandb <<eof

DROP CATALOG;

Eof