Creating Catalog Database Server using RMAN

Creating the Recovery Catalog Managing Target Database Records in the Recovery Catalog Resynchronizing the Recovery Catalog Managing RMAN Scripts Stored in the Recovery Catalog Managing the Control File When You Use a Recovery Catalog Backing Up and Recovering the Recovery Catalog Exporting the Recovery Catalog Increasing Availability of the Recovery Catalog Determining the Schema Version of the Recovery Catalog Upgrading the Recovery Catalog Dropping the Recovery Catalog Managing the RMAN Repository Without a Recovery Catalog Creating the Recovery CatalogThis section contains these topics:
Configuring the Recovery Catalog Database Creating the Recovery Catalog Owner Creating the Recovery Catalog
Configuring the Recovery Catalog DatabaseWhen you use a recovery catalog, RMAN requires that you maintain a recovery catalog schema. The recovery catalog is stored in the default tablespace of the schema. Note that SYS cannot be the owner of the recovery catalog.
Decide which database you will use to install the recovery catalog schema, and also how you will back up this database. You should not install the catalog in the target database: this tactic defeats the purpose of the catalog. Also, decide whether to operate the catalog database in ARCHIVELOG mode, which is recommended.
Planning the Size of the Recovery Catalog SchemaYou must allocate space to be used by the catalog schema. The size of the recovery catalog schema:
Depends on the number of databases monitored by the catalog Depends on the number and size of RMAN scripts stored in the catalog Grows as the numbers of archived logs and backups for each database grow For an example, assume that the trgt database has 100 files, and you back up the database once a day, producing 50 backup sets containing 1 backup piece each. If you assume that each row in the backup piece table uses the maximum amount of space, then one daily backup will consume less than 170 KB in the recovery catalog. So, if you back up once a day for a year, then the total storage in this period is about 62 MB. Assume approximately the same amount for archived logs. Hence, the worst case is about 120 MB for a year for metadata storage. For a more typical case in which only a portion of the backup piece row space is used, 15 MB for each year is a more realistic estimate.
Allocating Disk Space for the Recovery Catalog DatabaseAfter either creating a new catalog database or finding an existing database to host the catalog, allocate disk space for the following:
SYSTEM tablespace Temporary tablespaces Rollback segment tablespaces Online redo log files Most of the space used in the recovery catalog database is devoted to supporting tablespaces, for example, the SYSTEM, temporary, and rollback or undo tablespaces. Table 16-1 describes typical space requirements.
Table 16-1 Typical Recovery Catalog Space Requirements for 1 YearType of Space Space Requirement SYSTEM tablespace 90 MB Temp tablespace 5 MB Rollback or undo tablespace 5 MB Recovery catalog tablespace 15 MB Online redo logs 1 MB each (3 groups, each with 2 members)
----------------------------------------------------------------------------
Caution: Ensure that the recovery catalog and target databases do not reside on the same disk. If they are on the same disk and you lose one database, then you will probably lose the other.
--------------------------------------------------------------------------------
Creating the Recovery Catalog OwnerAfter choosing the recovery catalog database and creating necessary space, you are ready to create the owner of the recovery catalog and grant this user necessary privileges.
Assume the following background information for the instructions in the following sections:
User SYS with password oracle has SYSDBA privileges on the recovery catalog database catdb. A tablespace called tools on the recovery catalog database catdb stores the recovery catalog. Note that to use a reserved word as a tablespace name, you must enclose it in quotes and put it in uppercase font. A tablespace called temp exists in the recovery catalog database. The database is configured in the same way as all normal databases, for example, catalog.sql and catproc.sql have successfully run. To create the recovery catalog schema in the recovery catalog database:
Start SQL*Plus and then connect with administrator privileges to the database containing the recovery catalog.
For example, enter: CONNECT SYS/oracle@catdb AS SYSDBA
Create a user and schema for the recovery catalog.
For example, enter: CREATE USER rman IDENTIFIED BY cat TEMPORARY TABLESPACE temp DEFAULT TABLESPACE tools QUOTA UNLIMITED ON tools;
Grant the RECOVERY_CATALOG_OWNER role to the schema owner.
This role provides the user with privileges to maintain and query the recovery catalog.

SQL> GRANT RECOVERY_CATALOG_OWNER TO rman;

Grant other desired privileges to the RMAN user.
SQL> GRANT CONNECT, RESOURCE TO rman;
Creating the Recovery CatalogAfter creating the catalog owner, create the catalog itself with the RMAN CREATE CATALOG command. The command creates the catalog in the default tablespace of the catalog owner.
To create the recovery catalog:
Connect to the database that will contain the catalog as the catalog owner.
For example, enter the following from the operating system command line:
% rman CATALOG rman/cat@catdb
You can also connect from the RMAN prompt:
% rmanRMAN> CONNECT CATALOG rman/cat@catdb
Run the CREATE CATALOG command to create the catalog. If the catalog tablespace is this user's default tablespace, then you can run this command:
CREATE CATALOG;
Note that the creation of the catalog can take several minutes.
Optionally, start SQL*Plus and query the recovery catalog to see which tables were created: SQL> SELECT TABLE_NAME FROM USER_TABLES;
See Also: Oracle9i SQL Reference for the SQL syntax for the GRANT and CREATE USER statements, and Oracle9i Recovery Manager Reference for CREATE CATALOG command syntax
Managing Target Database Records in the Recovery CatalogThis section describes how to register, unregister, and reset target database records in the recovery catalog.
This section contains these topics:
Registering a Database in the Recovery Catalog Unregistering a Target Database from the Recovery Catalog Resetting a Database Incarnation in the Recovery Catalog Registering a Database in the Recovery CatalogBefore using RMAN with a recovery catalog, you must register the target database in the recovery catalog. RMAN obtains all information it needs to register the target database from the database itself.
As long as each target database has a distinct DBID, you can register more than one target database in the same recovery catalog. For example, you can register target databases prod1, prod2, and prod3 in catalog schema rman1. Note that you cannot register a specific database multiple times in the same recovery catalog: registration occurs only once. However, you can register a target database in multiple recovery catalog schemas. For example, you can register target database prod1 in catalog schema rman1 and in schema rman2.
Each database registered in a given catalog must have a unique database identifier (DBID), but not necessarily a unique database name. When you copy a database with user-managed methods, the copy database has the same DBID as the original database. You can use the DBNEWID utility to change the DBID and database name of manually copied databases. However, the recommended method is to copy a database with the DUPLICATE command, because RMAN gives it a new DBID.
--------------------------------------------------------------------------------
Note: If you use Oracle Enterprise Manager, then you can use also the Maintenance wizard to register the database.
--------------------------------------------------------------------------------
To register the target database:
Connect to the target database and recovery catalog database. For example, issue the following to connect to the catalog database catdb as user rman (who owns the catalog schema):
% rman TARGET / CATALOG rman/cat@catdb
If the target database is not mounted, then mount or open it. For example, issue:
STARTUP MOUNT;
The recovery catalog database must be open.
To use RMAN with a target database, you must first register the database. Run the following command: REGISTER DATABASE;
After you run REGISTER DATABASE, RMAN creates rows in the repository that contain information about the target database. Then, RMAN performs a full resynchronization with the catalog in which it transfers all pertinent data about the target database from the control file and saves it in the catalog.
Test that the registration was successful by running REPORT SCHEMA. This command shows the database structure as it is stored in the repository.
For example: RMAN> REPORT SCHEMA;
Report of database schemaFile
K-bytes Tablespace RB segs Datafile Name
---- ---------- -------------------- ------- -------------------
1 307200 SYSTEM *** /oracle/oradata/trgt/system01.dbf2 20480 UNDOTBS *** /oracle/oradata/trgt/undotbs01.dbf3 10240 CWMLITE *** /oracle/oradata/trgt/cwmlite01.dbf4 10240 DRSYS *** /oracle/oradata/trgt/drsys01.dbf5 10240 EXAMPLE *** /oracle/oradata/trgt/example01.dbf6 10240 INDX *** /oracle/oradata/trgt/indx01.dbf7 10240 TOOLS *** /oracle/oradata/trgt/tools01.dbf8 10240 USERS *** /oracle/oradata/trgt/users01.dbf
If there are any existing user-created copies of datafiles or archived logs on disk that were created under Oracle release 8.0 or higher, you can add them to the recovery catalog with the CATALOG command.
For example: CATALOG DATAFILECOPY '/tmp/users01.dbf';CATALOG ARCHIVELOG '/tmp/archive1_731.dbf', '/tmp/archive1_732.dbf';
In some cases, datafile copies made of an Oracle7 database with operating systems can be cataloged. To be usable, the copies must not require any Oracle7 redo to be recovered, that is, they must be either of the following:
Datafile copies made when the database was shut down consistently. The database must not have been opened again before being migration. Datafile copies made after a tablespace became offline normal or read-only. The tablespaces must not have been brought online or made read/write again before migration.
--------------------------------------------------------------------------------
Note: To determine whether log records have aged out of the control file, compare the number of logs on disk with the number of records in V$ARCHIVED_LOG.
--------------------------------------------------------------------------------
See Also: Oracle9i Recovery Manager Reference for REGISTER syntax Oracle Enterprise Manager Administrator's Guide to learn about RMAN restore and recovery Oracle9i Database Migration for issues relating to database migration
Troubleshooting DBID ProblemsOracle uses an internal, uniquely generated number called the DBID to distinguish one database from another. Oracle generates the DBID at database creation. RMAN uses the DBID to distinguish one database from another. A problem can occur when you create a database by non-RMAN techniques (for example, user-managed backup and restore) instead of with a CREATE DATABASE statement or DUPLICATE command. In such cases, RMAN detects the duplicate database identifiers and the REGISTER DATABASE command fails.
To be able to register a copied database with the same DBID as an already registered database, use the DBNEWID utility to change the DBID of the copy database. You can also use this utility to change the database name. However, the best solution is to avoid the problem altogether by using the DUPLICATE command, which generates a new database identifier automatically.
See Also: Oracle9i Recovery Manager Reference for CATALOG syntax Oracle9i Recovery Manager Reference for DUPLICATE syntax Oracle9i Database Utilities to learn how to use the DBNEWID utility to change the DBID Oracle9i Database Migration for issues relating to database migration
Unregistering a Target Database from the Recovery CatalogRMAN 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:
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: RDBMS (DBID=1237603294)connected to recovery catalog database
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 TAG20020208T15523920 B F A DISK 08-FEB-02 1 1 TAG20020208T15524221 B A A DISK 08-FEB-02 1 1 TAG20020208T15533122 B A A DISK 08-FEB-02 1 1 TAG20020208T155604
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.
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@catdbSQL> 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.
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)
Resetting a Database Incarnation in the Recovery CatalogWhen you run either the RMAN command or the SQL statement ALTER DATABASE OPEN RESETLOGS, you create a new incarnation of the database. You can see a record of the new incarnation in the V$DATABASE_INCARNATION view of the target database.
If you run the RMAN command (not the SQL statement) ALTER DATABASE OPEN RESETLOGS, then RMAN automatically creates a new database incarnation record in the recovery catalog. RMAN implicitly and automatically issues a RESET DATABASE command, which specifies that this new incarnation of the database is the current incarnation. RMAN associates all subsequent backups and log archiving done by the target database with the new database incarnation.
If you issue the SQL statement (not the RMAN command) ALTER DATABASE OPEN RESETLOGS, then RMAN does not automatically run a RESET DATABASE command. Hence, RMAN cannot access the recovery catalog because it cannot distinguish between a RESETLOGS command and an accidental restore of an old control file. To solve this problem, you must manually run the RESET DATABASE command in RMAN after executing the SQL statement ALTER DATABASE OPEN RESETLOGS. The RESET DATABASE command updates the repository to indicate that the target database has been opened with the RESETLOGS option.
In the rare situation in which you wish to restore backups of a prior incarnation of the database, use the RESET DATABASE TO INCARNATION key command to change the current incarnation to an older incarnation. For example, if you accidentally drop a table immediately after the most recent RESETLOGS, then you may want to recover the database to just before the time of the most recent RESETLOGS and then open it with the RESETLOGS option, thereby creating a new incarnation.
--------------------------------------------------------------------------------
Note: If you use Oracle Enterprise Manager, then you can use also the Maintenance wizard to reset the database incarnation.
--------------------------------------------------------------------------------
To reset the recovery catalog to an older incarnation:
Specify the primary key of the desired database incarnation. Obtain the incarnation key value by issuing a LIST command: LIST INCARNATION;
List of Database IncarnationsDB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time
------- ------- ------- ------ --- ---------- ----------
1 2 TRGT 1224038686 NO 1 02-JUL-011 582 TRGT 1224038686 YES 59727 10-JUL-01
Reset the database to the old incarnation. For example, enter: RESET DATABASE TO INCARNATION 2;
Shut down the database and start it without mounting. For example: SHUTDOWN IMMEDIATESTARTUP NOMOUNT
Restore a control file from the old incarnation. If you have a control file tagged, then specify the tag. Otherwise, you can run the SET UNTIL command, as in this example:
RUN { SET UNTIL 'SYSDATE-45'; RESTORE CONTROLFILE;}
Mount the restored control file:
ALTER DATABASE MOUNT;
Run RESTORE and RECOVER commands to restore and recover the database files from the prior incarnation, then open the database with the RESETLOGS option.
For example, enter: RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;