This chapter describes how to manage an RMAN recovery catalog, which holds RMAN repository data for one or more databases in a separate database schema, in addition to using the control files of the databases.
This chapter contains these topics:
See Also:
Oracle Database Backup and Recovery Basics to learn how to manage the RMAN repository as stored in the control file, without a recovery catalogCreating a recovery catalog is a three-step process: you must configure the database that will contain the recovery catalog, create the recovery catalog owner, and then create the recovery catalog itself.
When 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.
Note:
Do not use the target database to be backed up as the database for the recovery catalog. The recovery catalog must be protected in the event of the loss of the target database.Also, decide whether to operate the catalog database in ARCHIVELOG
mode, which is recommended.
You must allocate space to be used by the catalog schema. The size of the recovery catalog schema depends upon the number of databases monitored by the catalog. The schema also grows as the number of archived redo log files and backups for each database increases. Finally, if you use RMAN stored scripts stored in the catalog, some space must be allocated for those scripts.
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 realistic estimate.
If you plan to register multiple databases in your recovery catalog, remember to add up the space required for each one based on the previous calculation to arrive at a total size for the default tablespace of the recovery catalog schema.
If you are creating your recovery catalog in an already-existing database, add enough room to hold the default tablespace to the recovery catalog schema. If you are creating a new database to hold your recovery catalog, then, in addition to the space for the recovery catalog schema itself, you must allow space for other files in the recovery catalog database:
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 10-1, "Typical Recovery Catalog Space Requirements for 1 Year" describes typical space requirements.
Table 10-1 Typical Recovery Catalog Space Requirements for 1 Year
Type of Space | Space Requirement |
---|---|
|
90 MB |
Temp tablespace |
5 MB |
Rollback or undo tablespace |
5 MB |
Recovery catalog tablespace |
15 MB for each database registered in the recovery catalog |
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 both your recovery catalog and your target database suffer hard disk failure, your recovery process is much more difficult. If possible, take other measures as well to eliminate common points of failure between your recovery catalog database and the databases you are backing up.
After 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
in the recovery catalog database catdb
stores the recovery catalog. Note that to use an RMAN reserved word as a tablespace name, you must enclose it in quotes and put it in uppercase. (Refer to Oracle Database Backup and Recovery Reference for a list of RMAN reserved words.)
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 all privileges required to maintain and query the recovery catalog.
SQL> GRANT RECOVERY_CATALOG_OWNER TO rman;
After creating the catalog owner, create the catalog tables 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:
% rman RMAN> CONNECT CATALOG rman/cat@catdb
Run the CREATE
CATALOG
command to create the catalog. The creation of the catalog can take several minutes. If the catalog tablespace is this user's default tablespace, then you can run this command:
CREATE CATALOG;
You can specify the tablespace name for the catalog in the CREATE
CATALOG
command. For example:
CREATE CATALOG TABLESPACE cat_ts;
Note:
If the tablespace name you wish to use for the recovery catalog is an RMAN reserved word, then it must be uppercase and enclosed in quotes. For example:CREATE CATALOG TABLESPACE 'CATALOG';
You can check the results by using SQL*Plus to query the recovery catalog to see which tables were created:
SQL> SELECT TABLE_NAME FROM USER_TABLES;
See Also:
Oracle Database SQL Reference for the SQL syntax for theGRANT
and CREATE
USER
statements, and Oracle Database Backup and Recovery Reference for CREATE
CATALOG
command syntaxThis section describes how to maintain target database records in the recovery catalog. It contains the following sections:
The first step in using a recovery catalog with a target database is registering the database in the recovery catalog. Use the following procedure:
After making sure the recovery catalog database is open, connect RMAN 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:
RMAN> STARTUP MOUNT;
Register the target database in the connected recovery catalog:
RMAN> REGISTER DATABASE;
RMAN creates rows in the catalog tables to contain information about the target database, then copies all pertinent data about the target database from the control file into the catalog, synchronizing the catalog with the control file.
Verify that the registration was successful by running REPORT
SCHEMA
:
RMAN> REPORT SCHEMA; Report of database schema File Size(MB) Tablespace RB segs Datafile Name ---- ---------- ---------------- ------- ------------------- 1 307200 SYSTEM NO /oracle/oradata/trgt/system01.dbf 2 20480 UNDOTBS YES /oracle/oradata/trgt/undotbs01.dbf 3 10240 CWMLITE NO /oracle/oradata/trgt/cwmlite01.dbf 4 10240 DRSYS NO /oracle/oradata/trgt/drsys01.dbf 5 10240 EXAMPLE NO /oracle/oradata/trgt/example01.dbf 6 10240 INDX NO /oracle/oradata/trgt/indx01.dbf 7 10240 TOOLS NO /oracle/oradata/trgt/tools01.dbf 8 10240 USERS NO /oracle/oradata/trgt/users01.dbf
If you have datafile copies, backup pieces or archive logs on disk, you can catalog them in the recovery catalog using the CATALOG command. When using a recovery catalog, cataloging older backups that have aged out of the control file lets RMAN use the older backups during restore operations. For example:
RMAN> CATALOG DATAFILECOPY '/disk1/old_datafiles/01_01_2003/users01.dbf'; RMAN> CATALOG ARCHIVELOG '/disk1/arch_logs/archive1_731.dbf', '/disk1/arch_logs/archive1_732.dbf'; RMAN> CATALOG BACKUPPIECE '/disk1/backups/backup_820.bkp';
You can also catalog multiple backup files in a directory at once, using the CATALOG START WITH
command, as shown in this example:
RMAN> CATALOG START WITH '/disk1/backups/';
RMAN lists the files to be added to the RMAN repository and prompts for confirmation before adding the backups.
Note:
Be careful when creating your prefix forCATALOG START WITH
. RMAN scans all paths for all files on disk which begin with the specified prefix. The prefix is not just a directory name. Using the wrong prefix can cause the cataloging of the wrong set of files. For example, a group of directories /disk1/backups
, /disk1/backups-year2003
, /disk1/backupsets
, and /disk1/backupsets/test
and so on, all contain backup files. The command
RMAN> CATALOG START WITH '/disk1/backups';
catalogs all files in all of these directories, because /disk1/backups
is a prefix for the paths for all of these directories. In order to catalog only backups in the /disk1/backups
directory, the correct command would be:
RMAN> CATALOG START WITH '/disk1/backups/';
In general, only Oracle8 and higher files can be cataloged. Datafile copies from Oracle7 databases can also be cataloged, if they do not require the application of Oracle7 redo before they can be opened. Datafile copies made in the following circumstances satisfy this requirement:
Datafile copies made when the database was shut down consistently. The database must not have been opened again before migration to a higher version of Oracle.
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 to a higher version of Oracle.
See Also:
Oracle Database Backup and Recovery Reference for REGISTER
syntax
Oracle Database Upgrade Guide for issues relating to database migration
You can register multiple target databases in a single recovery catalog, if they do not have duplicate DBIDs. RMAN uses the DBID to distinguish one database from another.
In general, if you use the DUPLICATE RMAN command or CREATE DATABASE SQL statement, the database created is assigned a unique DBID. If you create a database by some other means, such as a user-managed copy, then the new database may have the same DBID as the one from which it was copied. You will not be able to register both databases in the same recovery catalog until you change the DBID of the copied database using the DBNEWID
utility.
Note that you can also register a single target databases in multiple recovery catalogs.
See Also:
Oracle Database Backup and Recovery Reference for DUPLICATE
syntax
Oracle Database Utilities to learn how to use the DBNEWID
utility to change the DBID
Oracle Database Upgrade Guide for issues relating to database migration
The UNREGISTER DATABASE
command is used to unregister a database from the recover catalog.
Note:
When a database is unregistered from the recovery catalog, all RMAN repository records in the recovery catalog are lost. The database can be registered again, but the recovery catalog records for that database are then based on the contents of the control file at the time of re-registration. Records older than theCONTROLFILE_RECORD_KEEP_TIME
setting in the target database control file are lost. Stored scripts, which are not stored in the control file, are also lost.To unregister a database:
Start RMAN and 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
Make a note of the DBID as displayed by RMAN at startup. If there is more than one database registered in the recovery catalog, you will need the DBID to uniquely identify the database.
It is not necessary to connect to the target database, but if you do not, then you must specify the name of the target database in the UNREGISTER
command. If more than one database has the same name in the recovery catalog, then you must create a RUN block around the command and use SET DBID to set the DBID for the database.
As a precaution, it may be useful to list all of the backups recorded in the recovery catalog using LIST BACKUP SUMMARY
and LIST COPY SUMMARY
. This way, you can re-catalog backups not known to the control file if you later decide to re-register the database.
If your intention is to actually delete all backups of the database completely, rather than just removing the database from the recovery catalog and relying on the control file to store the RMAN repository for this database, then run DELETE
statements to delete all existing backups. For example:
DELETE BACKUP DEVICE TYPE sbt; DELETE BACKUP DEVICE TYPE DISK; DELETE COPY;
RMAN will list the backups that it intends to delete and prompt for confirmation before deleting them.
Run the UNREGISTER
DATABASE
command. For example:
UNREGISTER DATABASE;
RMAN displays the database name and DBID, and prompts you for a confirmation:
database name is "RDBMS" and DBID is 931696259 Do you really want to unregister the database (enter YES or NO)? yes
When the process is complete, RMAN outputs the following message:
database unregistered from the recovery catalog
When you run either the RMAN command or the SQL statement ALTER
DATABASE
OPEN
RESETLOGS
, you create a new incarnation of the database. You can access a record of the new incarnation in the V$DATABASE_INCARNATION
view of the target database.
If you run the RMAN command or the SQL statement ALTER
DATABASE
OPEN
RESETLOGS
, then a new database incarnation record is automatically created in the recovery catalog. The database also implicitly and automatically issues a RESET
DATABASE
command, which specifies that this new incarnation of the database is the current incarnation. All subsequent backups and log archiving done by the target database is associated with the new database incarnation.
You may need to change the current incarnation of the database in the recovery catalog for some recovery tasks. The steps for changing the incarnation are described in the following procedure.
To reset the recovery catalog to an older incarnation:
Determine the incarnation key of the desired database incarnation. Obtain the incarnation key value by issuing a LIST
command:
LIST INCARNATION OF DATABASE trgt; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- ------- ------ ------- ---------- ---------- 1 2 TRGT 1224038686 PARENT 1 02-JUL-02 1 582 TRGT 1224038686 CURRENT 59727 10-JUL-02
The incarnation key is listed in the "Inc Key" column.
Reset the database to the old incarnation. For example, enter:
RESET DATABASE TO INCARNATION 2;
If the control file of the previous incarnation is available and mounted, then skip to step 6 of this procedure. Otherwise, shut down the database and start it without mounting. For example:
SHUTDOWN IMMEDIATE STARTUP 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; # only if current control file is not available }
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;
See Also:
Oracle Database Backup and Recovery Reference forRESET
DATABASE
syntax, Oracle Database Backup and Recovery Reference for LIST
syntaxIn Oracle9i and later, RMAN always removes catalog records on deleting files, and never updates them to status DELETED
. However, in releases prior to Oracle9i, RMAN updated recovery catalog records to DELETED
status after deleting the physical files rather than removing the records. Thus, records with status DELETED
can appear in the recovery catalog when you upgrade a recovery catalog created prior to Oracle9i to the current release. For this special case, you can run the prgrmanc.sql
script, which is stored in an operating system specific location ($ORACLE_HOME/rdbms/admin
on UNIX).
To remove all backup records with status DELETED:
Start a SQL*Plus session and connect to the recovery catalog. This example connects to the database rcat
as user rman
:
% sqlplus rman/cat@catdb
Run the script prgrmanc.sql
script:
SQL> @?/rdbms/admin/prgrmanc.sql
The script removes all records with status DELETED
from the recovery catalog.
When RMAN performs a resynchronization, it compares the recovery catalog to either the current control file of the target database or a backup control file and updates the recovery catalog with information that is missing or changed. When resynchronizing, RMAN does the following:
Creates a snapshot control file.
Compares the recovery catalog to the snapshot control file.
Updates the recovery catalog with missing or changed information.
RMAN performs resynchronizations automatically as needed when you execute certain commands, including BACKUP
. You can also manually perform a full resynchronization using the RESYNC CATALOG
command.
Table 10-2, "Records Updated During a Resynchronization" describes the types of records that RMAN resynchronizes.
Table 10-2 Records Updated During a Resynchronization
Records | Description |
---|---|
Log history |
Created when an online redo log switch occurs. |
Archived redo logs |
Associated with archived logs that were created by archiving an online log, copying an existing archived redo log, or restoring an archived redo log backup set. RMAN tracks this information so that it knows which archived logs it should expect to find. |
Backup history |
Associated with backup sets, backup pieces, proxy copies, and file copies. The |
Incarnation history |
Associated with database incarnations. |
Physical schema |
Associated with datafiles and tablespaces. If the target database is open, then undo segment information is also updated. Physical schema information in the recovery catalog is updated only when the target has the current control file mounted. If the target database has mounted a backup control file, a freshly created control file, or a control file that is less current than a control file that was seen previously, then physical schema information in the recovery catalog is not updated. Physical schema information is also not updated when you use the |
Resynchronizations can be full or partial. In a partial resynchronization, RMAN reads the current control file to update changed information about new backups, new archived logs, and so forth. However, RMAN does not resynchronize metadata about the database physical schema: datafiles, tablespaces, redo threads, rollback segments (only if the database is open), and online redo logs. In a full resynchronization, RMAN updates all changed records, including those for the database schema.
Note:
Although RMAN performs partial resynchronizations when using a backup control file, it does not perform full resynchronizations. A backup control file may not have correct information about the database physical schema, so a full resynchronization could update the recovery catalog with incorrect information.Note:
You can use Oracle Enterprise Manager to perform catalog resynchronizations.See Also:
Oracle Database Backup and Recovery Reference for more information about theRESYNC
commandRMAN automatically performs full or partial resynchronizations in most situations in which they are needed. Most RMAN commands such as BACKUP
, DELETE
, and so forth perform a full or partial resynchronization (depending on whether the schema metadata has changed) automatically when the target database control file is mounted and the recovery catalog database is available. Thus, you should not need to manually run RESYNC
CATALOG
very often.
The following sections describe situations in which a manual resynchronization is required.
If the recovery catalog is unavailable when you issue RMAN commands that cause a partial resynchronization, then open the catalog database later and resynchronize it manually with the RESYNC
CATALOG
command.
For example, the target database may be in New York while the recovery catalog database is in Japan. You may not want to make daily backups of the target database in CATALOG
mode, to avoid depending on the availability of a geographically distant database. In such a case you could connect to the catalog as often as feasible (for example, once each week) and run the RESYNC
CATALOG
command.
Assume that you do the following:
Run the database in ARCHIVELOG
mode
Back up the database infrequently (for example, hundreds of archive logs are archived between database backups)
Generate a high number of log switches every day (for example, 1000 switches between catalog resynchronizations)
In this case, you may want to manually resynchronize the recovery catalog regularly because the recovery catalog is not updated automatically when a redo log switch occurs or when a redo log is archived. The database stores information about log switches and archived redo logs only in the control file. You must periodically resynchronize in order to propagate this information into the recovery catalog.
How frequently you need to resynchronize the recovery catalog depends on the rate at which the database archives redo logs. The cost of the operation is proportional to the number of records in the control file that have been inserted or changed since the previous resynchronization. If no records have been inserted or changed, then the cost of resynchronization is very low; if many records have been inserted or changed, then the resynchronization is more time-consuming.
Resynchronize the recovery catalog after making any change to the physical structure of the target database. As with redo log archive operations, the recovery catalog is not updated automatically after physical schema changes, such as adding or dropping tablespaces, adding datafiles to a tablespace, or adding or dropping rollback segments.
Use RESYNC
CATALOG
to force a full resynchronization of the recovery catalog.
Connect RMAN to the target and recovery catalog databases, and then mount or open the target database if it is not already mounted or open:
STARTUP MOUNT;
Run the RESYNC
CATALOG
command at the RMAN prompt:
RESYNC CATALOG;
If you maintain a recovery catalog, then use the RMAN RESYNC
CATALOG
command often enough to ensure that control file records are propagated to the recovery catalog before they are reused.
Make sure that CONTROL_FILE_RECORD_KEEP_TIME
is longer than the interval between backups or resynchronizations. Otherwise, control file records could be reused before they are propagated to the recovery catalog. An extra week is a safe margin in most circumstances.
Caution:
Never set CONTROL_FILE_RECORD_KEEP_TIME
to 0
. If you do, then backup records may be overwritten in the control file before RMAN is able to add them to the catalog.
See Also:
Oracle Database Backup and Recovery Basics to learn how to monitor the overwriting of control file recordsYour goal is to ensure that the metadata in the recovery catalog is current. Because the recovery catalog obtains its metadata from the target control file, the currency of the data in the catalog depends on the currency of the data in the control file. You need to make sure that the backup metadata in the control file is recorded in the catalog before it is overwritten with new records.
The CONTROL_FILE_RECORD_KEEP_TIME
initialization parameter determines the minimum number of days that records are retained in the control file before they are candidates for being overwritten. Hence, you must ensure that you resynchronize the recovery catalog with the control file records before these records are erased. As described in "Resynchronizing the Recovery Catalog and CONTROL_FILE_RECORD_KEEP_TIME", you should perform either of the following actions at intervals less than the CONTROL_FILE_RECORD_KEEP_TIME
setting:
Make a backup, thereby performing an implicit resynchronization of the recovery catalog
Manually resynchronize the recovery catalog with the RESYNC
CATALOG
command
So, to ensure the currency of the information in the recovery catalog, the frequency of resynchronizations should be related to the value for the CONTROL_FILE_RECORD_KEEP_TIME
initialization parameter.
One problem can arise if the control file becomes too large. The size of the target database control file grows depending on the number of:
Backups that you perform
Archived redo logs that the database generates
Days that this information is stored in the control file
As explained in Oracle Database Backup and Recovery Basics, if the control file grows so large that it can no longer expand because it has reached either the maximum number of blocks or the maximum number of records, then the database may overwrite the oldest records even if their age is less than the CONTROL_FILE_RECORD_KEEP_TIME
setting. In this case, the database writes a message to the alert log. If you discover that this situation occurs frequently, then reducing the value of CONTROL_FILE_RECORD_KEEP_TIME
and increase the frequency of resynchronizations.
Note:
The maximum size of the control file is port-specific. Typically, the maximum size is 20,000 Oracle blocks. Refer to your platform-specific Oracle documentation for more information.See Also:
Oracle Database Reference for more information about the CONTROL_FILE_RECORD_KEEP_TIME
parameter
Oracle Database Administrator's Guide for more detailed information on other aspects of control file management
Stored scripts offer an alternative to command files for managing frequently used sequences of RMAN commands.
The chief advantage of a stored script over a command file is that a stored script is always available to any RMAN client that can connect to the target database and recovery catalog, whereas command files are only available if the RMAN client has access to the file system on which they are stored.
Stored scripts can be global or local. A local stored script is associated with the target database to which RMAN is connected when the script is created, and can only be executed when you are connected to that target database. A global stored script can be run against any database registered in the recovery catalog, if the RMAN client is connected to the recovery catalog and a target database.
Note that to work with stored scripts, even global ones, you must be connected to both a recovery catalog and a target instance.
Make sure RMAN is connected to the right target database and the recovery catalog. Then run the CREATE
SCRIPT
command, as shown in this example:
CREATE SCRIPT full_backup { BACKUP DATABASE PLUS ARCHIVELOG; DELETE OBSOLETE; }
Examine the output. If no errors are displayed, then the script was successfully created and stored in the recovery catalog.
For a global script, the syntax is similar:
CREATE GLOBAL SCRIPT global_full_backup { BACKUP DATABASE PLUS ARCHIVELOG; DELETE OBSOLETE; }
You can also provide a COMMENT
with descriptive information:
CREATE GLOBAL SCRIPT global_full_backup COMMENT 'use only with ARCHIVELOG mode databases' { BACKUP DATABASE PLUS ARCHIVELOG; DELETE OBSOLETE; }
Finally, you can create a local or global script, reading its contents from a text file:
CREATE SCRIPT full_backup FROM FILE 'my_script_file.txt';
The file must begin with a {
character, contain a series of commands valid within a RUN block, and end with a }
character. Otherwise, a syntax error is signalled, just as if the commands were entered at the keyboard.
To run a stored script, connect to the target database and recovery catalog, and use EXECUTE
SCRIPT
. EXECUTE SCRIPT
requires a RUN
block, as shown:
RUN { EXECUTE SCRIPT full_backup; }
This command invokes a local script if one is with the name specified. If no local script is found, but there is a global script with the name specified, RMAN will execute the global script. You can also use EXECUTE GLOBAL SCRIPT
to control which script is invoked if a local and a global script have the same name. Assuming there is no local script called global_full_backup
, the following two commands have the same effect:
RUN { EXECUTE GLOBAL SCRIPT global_full_backup; } RUN { EXECUTE SCRIPT global_full_backup; }
Executing a global script only affects the connected target database; to run a global script across multiple databases, you must connect the RMAN client to each one separately and execute the script.
Your script will use the automatic channels configured at the time you execute the script. Use ALLOCATE
CHANNEL
commands in the script if you need to override the configured channels. Note that, because of the RUN block, if an RMAN command in the script fails, subsequent RMAN commands in the script will not execute.
The PRINT
SCRIPT
command displays a stored script or writes it out to a file. With RMAN connected to the target database and recovery catalog, use the PRINT SCRIPT command as shown here:
PRINT SCRIPT full_backup;
To send the contents of a script to a file, use this form of the command:
PRINT SCRIPT full_backup TO FILE 'my_script_file.txt';
For global scripts, the analogous syntax would be:
PRINT GLOBAL SCRIPT global_full_backup;
and
PRINT GLOBAL SCRIPT global_full_backup TO FILE 'my_script_file.txt';
Use the LIST SCRIPT NAMES
command to display the names of scripts defined in the recovery catalog. This command displays the names of all stored scripts, both global and local, that can be executed for the currently connected target database:
LIST SCRIPT NAMES;
If RMAN is not connected to a target database when the LIST
SCRIPT
NAMES
command is run, then RMAN will respond with an error.
To view only global script names, use this form of the command:
LIST GLOBAL SCRIPT NAMES;
To view the names of all scripts stored in the current recovery catalog, including global scripts and local scripts for all target databases registered in the recovery catalog, use this form of the command:
LIST ALL SCRIPT NAMES;
The output will indicate for each script listed which target database the script is defined for (or whether a script is global).
Note:
LIST GLOBAL SCRIPT NAMES
and LIST ALL SCRIPT NAMES
are the only commands that work when RMAN is connected to a recovery catalog without connecting to a target instance.See Also:
Oracle Database Backup and Recovery Reference forLIST
SCRIPT NAMES
command syntax and output format.To update stored scripts, connect to the target database and recovery catalog and use the REPLACE
SCRIPT
command. If the script does not already exist, then RMAN creates it.
This command updates stored script full_backup
with new contents:
REPLACE SCRIPT full_backup { BACKUP DATABASE PLUS ARCHIVELOG; }
Global scripts can be updated using the REPLACE GLOBAL SCRIPT
command when connected to a recovery catalog, as follows:
REPLACE GLOBAL SCRIPT global_full_backup COMMENT 'A script for full backup to be used with any database' { BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG; }
As with CREATE SCRIPT
, you can update a local or global stored script from a text file, with this form of the command:
REPLACE GLOBAL SCRIPT global_full_backup FROM FILE 'my_script_file.txt';
To delete a stored script from the recovery catalog, connect to the catalog and a target database, and use the DELETE
SCRIPT
command:
DELETE SCRIPT 'full_backup';
To delete a global stored script, use DELETE GLOBAL SCRIPT
:
DELETE GLOBAL SCRIPT 'global_full_backup';
If you use DELETE SCRIPT
without GLOBAL
, and there is no stored script for the target database with the specified name, RMAN will look for a global stored script by the specified name and delete the global script if it exists. So, if you were to enter the command
DELETE SCRIPT 'global_full_backup';
RMAN would look for a script 'global_full_backup
' defined for the connected target database, and if it did not find one, it would search the global scripts for a script called 'global_full_backup
' and delete that script.
To run the RMAN client and start a stored script in the recovery catalog on startup, use the SCRIPT
argument when starting the RMAN client.
% rman TARGET SYS/oracle@trgt CATALOG rman/cat@catdb SCRIPT 'full_backup';
You must connect to a recovery catalog (which contains the stored script) and target database (to which the script will apply) when starting the RMAN client.
See Also:
Oracle Database Backup and Recovery Reference for full RMAN client command line syntaxThere are some issues to be aware of about how RMAN resolves script names, especially when a local and global script share the same name.
RMAN permits but generally does not require that you use quotes around the name of a stored script. However, if the name begins with a digit or if the name is an RMAN reserved word, you will have to put quotes around the name to use it as a stored script name. Consider avoiding stored script names that begin with characters other than A-Z or that are the same as RMAN reserved words.
See Also:
Oracle Database Backup and Recovery ReferenceWhen starting the RMAN client with a SCRIPT argument on the command line, if local and global scripts are defined with the same name, then RMAN will always execute the local script.
For the EXECUTE SCRIPT
, DELETE SCRIPT
and PRINT SCRIPT
commands, if the script name passed as an argument is not the name of a script defined for the connected target instance, RMAN will look for a global script by the same name to execute, delete or print. For example, if the a stored script global_full_backup
is in the recovery catalog as a global script, but no local stored script global_full_backup
is defined for the target database, the following command will delete the global script:
DELETE SCRIPT global_full_backup;
Consider using some naming convention to avoid mistakes due to confusion between global stored scripts and local stored scripts.
See Also:
Oracle Database Backup and Recovery Reference for the list of RMAN reserved words.Include the recovery catalog database in your backup and recovery strategy. If you do not back up the recovery catalog and a disk failure occurs that destroys the recovery catalog database, then you may lose the metadata in the catalog. Without the recovery catalog contents, recovery of your other databases is likely to be more difficult.
Here are some general guidelines you should follow when developing a strategy for backing up the recovery catalog.
The recovery catalog database is a database like any other, and is also a key part of your backup and recovery strategy. Protect the recovery catalog as you would protect any other part of your database, by backing it up. The backup strategy for your recovery catalog database should be part of your overall backup and recovery strategy.
Back up the recovery catalog with the same frequency that you back up the target database. For example, if you make a weekly whole database backup of the target database, then back up the recovery catalog immediately after all target database backups, in order to protect the record of the whole database backup. This backup can help you in a disaster recovery scenario. Even if you have to restore the recovery catalog database using a control file autobackup, you can then use the full record of backups in your restored recovery catalog database to restore the target database without using a control file autobackup for the target database.
When backing up the recovery catalog database, you can use RMAN to make the backups. As illustrated in Figure 10-1, you should start RMAN with the NOCATALOG
option so that the repository for the recovery catalog is the control file in the catalog database.
Follow these guidelines when developing an RMAN backup strategy for the recovery catalog database:
Run the recovery catalog database in ARCHIVELOG
mode so that you can do point-in-time recovery if needed.
Set the retention policy to a REDUNDANCY
value greater than 1
.
Back up the database onto two separate media (for example, disk and tape).
Run BACKUP
DATABASE
PLUS
ARCHIVELOG
at regular intervals, to a media manager if available, or just to disk.
Do not use another recovery catalog as the repository for the backups.
Configure the control file autobackup feature to ON
.
With this strategy, the control file autobackup feature ensures that the recovery catalog database can always be recovered, so long as the control file autobackup is available.
Figure 10-1 Using the Control File as the Repository for Backups of the Recovery Catalog
See Also:
"Performing Disaster Recovery" for more information for recovery with a control file autobackupNever store a recovery catalog containing the RMAN repository for a database in the same database as the target database or on the same disks as the target database. For example, do not store the catalog for database prod1
in prod1
. A recovery catalog for prod1
is only effective if it is separated from the data that it is designed to protect.
If prod1
suffers a total media failure, and if the recovery catalog for prod1
is also stored in prod1
, then if you lose the database you also lose the recovery catalog. At that point the only option is to restore an autobackup of the control file for prod1
and use it to restore and recover the database without the benefit of any information stored in the recovery catalog.
Separating the target and catalog databases is especially important when you back up a recovery catalog database. The following example shows what you should not do. For example, consider the following:
Target database prod1
and catalog database catdb
are on different hosts.
catdb
contains the recovery catalog repository for target database prod1
.
You decide to use a recovery catalog to back up catdb
, but are not sure where to create it. If you create the catalog containing the repository for catdb
in database catdb
, then if you lose catdb
due to a media failure, you will have difficulty restoring catdb
and will leave prod1
without a recovery catalog to use in a restore scenario.
Logical backups of the RMAN recovery catalog created with one of Oracle's export utilities can be a useful supplement for physical backups. In the event of damage to a recovery catalog database, you can quickly reimport the exported recovery catalog data into another database and rebuild the catalog this way.
Restoring and recovering the recovery catalog is much like restoring and recovering any other database, if you backed it up with RMAN.
You can restore the control file and SPFILE for the recovery catalog database from an autobackup, then restore and perform complete recovery on the rest of the database. The processes required are all described in Oracle Database Backup and Recovery Basics You can also use another recovery catalog to record metadata about backups of this recovery catalog database, if you are in a situation where you are using multiple recovery catalogs.
If the recovery catalog database is lost or damaged, and recovery of the recovery catalog database through the normal Oracle recovery procedures is not possible, then you must re-create the catalog. Examples of this worst-case scenario include:
A recovery catalog database that has never been backed up
A recovery catalog database that has been backed up, but cannot be recovered because the datafile backups or archived logs are not available
You have these options for partially re-creating the contents of the missing recovery catalog:
Use the RESYNC
CATALOG
command to update the recovery catalog with any RMAN repository information from the control file of the target database or a control file copy. Note that any metadata from control file records that aged out of the control file is lost.
Issue CATALOG START WITH...
commands to recatalog any available backups.
To minimize the likelihood of this worst-case scenario, your backup strategy should at least include backing up the recovery catalog using RMAN as described in "Backing Up the Recovery Catalog".
See Also:
Oracle Database Backup and Recovery Reference for information about the CATALOG
command
Oracle Database Backup and Recovery Reference for information about the CROSSCHECK
command
"Managing the Control File When You Use a Recovery Catalog" to learn about how records age out of the control file
To move the recovery catalog from one database to another, export the catalog from the old database, and import it into the new one. You can only import the catalog into a supported version of the Oracle database server. In general, you can import the catalog into a database of the same release or later.
Exports can also serve as logical backups of the RMAN recovery catalog. If the recovery catalog database is damaged, you can quickly reimport the exported recovery catalog data into another database and rebuild the catalog.
This section contains the following topics:
You should only import the recover catalog into a schema that does not already contain a recovery catalog schema. In other words, the user who will own the imported recovery catalog schema should not already own a recovery catalog schema. For example, if user rman
owns the recovery catalog on database catdb
, and you want to export the recovery catalog on catdb
and import it into database catdb2
, then rman
should not already own a recovery catalog on catdb2
. You should either create a new recovery catalog owner on catdb2
, or drop the current user rman
on catdb2
and then re-create the user. You cannot merge a recovery catalog into an existing recovery catalog.
The basic steps for exporting a recovery catalog from a primary database and importing the catalog into a secondary database are as follows:
Use one of the Oracle Export utilities to export the catalog data from the primary database. See "Exporting the Recovery Catalog" for an example.
Create a user on the secondary database as described in "Creating the Recovery Catalog Owner", and grant the user necessary privileges.
Use the import utility corresponding to the export utility in step 1 to import the catalog data into the schema created in the previous step. See "Importing the Recovery Catalog" for an example.
You should not run the CREATE
CATALOG
command either before or after the Import of the catalog into the secondary database. By importing the catalog data into the new schema, you effectively create the catalog in the secondary database.
Note:
You cannot import data exported from two different recovery catalogs to merge them into one catalog. It is not currently possible to merge two or more recovery catalog schemas into one.This example uses the Original Export utility described in Oracle Database Utilities to create a logical export of the recovery catalog. Refer to Oracle Database Utilities for concepts and procedures relating to the Data Pump Export utility.
The following procedure creates a logical export of the recovery catalog.
Execute the Oracle export utility at the operating system command line, making sure to do the following:
Connect as the owner of the recovery catalog
Specify the OWNER
option
Specify an output file
For example, if the owner of the catalog in database catdb
is rman
, you can issue the following at the UNIX command line to export the catalog to file cat.dmp
:
% exp rman/cat@catdb FILE=cat.dmp OWNER=rman
Examine the output to make sure you were successful:
Export terminated successfully without warnings.
This example uses the Original Import utility described in Oracle Database Utilities to create a logical export of the recovery catalog. Refer to Oracle Database Utilities for concepts and procedures relating to the Data Pump Import utility.
To make a logical import of the recovery catalog from the command line:
Create a new user in another database. For the recommended SQL syntax for creating a new user in a recovery catalog database, see "Creating the Recovery Catalog Owner". Be sure to grant the new user the necessary privileges.
Import the catalog data from the export file. Execute the import at the command line, making sure to do the following:
Connect as the new owner of the recovery catalog.
Specify the old owner with the FROMUSER
parameter.
Specify the new owner with the TOUSER
parameter.
Specify the import file.
For example, assume the following:
The old owner of the catalog in database prod1
is rman
.
The user in the new recovery catalog database catdb2
is rman2
.
The file containing the export of the catalog is cat.dmp
.
The command is then as follows:
% imp USERID=rman2/cat2@catdb2 FILE=cat.dmp FROMUSER=rman TOUSER=rman2
Use the imported catalog data for restore and recovery of your target database.
You may have a production system in which you want to maintain high availability for the catalog database. For example, you may have 100 target databases registered in the recovery catalog. In case the primary catalog database goes down, you can create redundancy by storing a secondary recovery catalog in a separate database, as illustrated in Figure 10-2. You must register the target database in the secondary catalog.
In this availability scenario, the main catalog is synchronized as normal during regular backups, while the secondary catalog is synchronized periodically with the RESYNC
CATALOG
command. If the primary catalog database goes down or requires routine maintenance, then you can resynchronize the secondary catalog and use it as the new primary catalog during the interim.
Figure 10-2 Registering One Target Database in Two Recovery Catalogs
See Also:
"Creating a Recovery Catalog"The LIST
, REPORT
, and SHOW
commands provide the easiest means of accessing the data in the control file and the recovery catalog. Nevertheless, you can sometimes also obtain useful information from the recovery catalog views, which are views in the catalog schema prefixed with RC_
.
See Also:
Oracle Database Backup and Recovery Reference for reference information about the recovery catalog viewsRMAN obtains backup and recovery metadata from the target database control file and stores it in the tables of the recovery catalog. The recovery catalog views are derived from these tables. Note that the recovery catalog views are not normalized or optimized for user queries.
In general, the recovery catalog views are not as user-friendly as the RMAN reporting commands. For example, when you start RMAN and connect to a target database, you obtain the information for this target database only when you issue LIST
, REPORT
, and SHOW
commands. If you have 10 different target databases registered in the same recovery catalog, then any query of the recovery catalog views show the information for all incarnations of all databases registered in the catalog. You often have to perform complex selects and joins among the views to extract usable information about a specific database and incarnation.
Most of the catalog views have a corresponding dynamic performance view (or V$ view) in the database server. For example, RC_BACKUP_PIECE
corresponds to V$BACKUP_PIECE
. The primary difference between the recovery catalog view and corresponding server view is that each catalog view contains information about all the databases registered in the catalog, whereas the database server view contains information only about itself. The RC_ views and corresponding V$ views use different primary keys to uniquely identify rows.
Most of the catalog views contain the columns DB_KEY
and DBINC_KEY
. Each target database can be uniquely identified by either the primary key, which is the DB_KEY
column value, or the DBID
, which is the 32-bit unique database identifier. Each incarnation of a target database is uniquely identified by the DBINC_KEY
primary key. When querying data about a specific incarnation of a target database, you should use these columns to specify the database. Then, you can perform joins with most of the other catalog views to obtain the desired information.
An important difference between catalog and V$
views is that a different system of unique identifiers is used for backup and recovery objects. For example, many V$
views such as V$ARCHIVED_LOG
use the RECID
and STAMP
columns to form a concatenated primary key. The corresponding catalog view uses a derived value as its primary keys and stores this value in a single column. For example, the primary key in RC_ARCHIVED_LOG
is the AL_KEY
column. The AL_KEY
column value is the primary key that RMAN displays in the LIST
command output.
The DB_KEY
value, which is the primary key for a target database, is used only in the recovery catalog. The easiest way is to obtain the DB_KEY
is to use the DBID of the target database, which is displayed whenever you connect RMAN to the target database. The DBID, which is a unique system-defined number given to every Oracle database, is used to distinguish among databases registered in the RMAN recovery catalog.
Assume that you want to obtain information about one of the target databases registered in the recovery catalog. You can easily determine the DBID from this database either by looking at the output displayed when RMAN connects to the database, querying V$RMAN_OUTPUT
, or querying a V$DATABASE
view as in the following:
SELECT DBID FROM V$DATABASE; DBID --------- 598368217
You can then obtain the DB_KEY
for a target database by running the following query, where dbid_of_target
is the DBID that you previously obtained:
SELECT DB_KEY
FROM RC_DATABASE
WHERE DBID = dbid_of_target;
To obtain information about the current incarnation of a target database, specify the target database DB_KEY
value and perform a join with RC_DATABASE_INCARNATION
by using a WHERE
condition to specify that the CURRENT_INCARNATION
column value is set to YES
. For example, to obtain information about backup sets in the current incarnation of a target database with the DB_KEY
value of 1
, you can execute the following script:
SELECT BS_KEY, BACKUP_TYPE, COMPLETION_TIME FROM RC_DATABASE_INCARNATION i, RC_BACKUP_SET b WHERE i.DB_KEY = 1 AND i.DB_KEY = b.DB_KEY AND i.CURRENT_INCARNATION = 'YES';
You should use the DB_NAME
column to specify a database only if you do not have more than one database registered in the recovery catalog with the same DB_NAME
. RMAN permits you to register more than one database with the same database name, but requires that the DBID values be different. For example, you can have ten databases with the DB_NAME
value of prod1
, each with a different DBID. Because the DBID is the unique identifier for every database in the metadata, use this value to obtain the DB_KEY
and then use DB_KEY
to uniquely identify the database.
The view RC_BACKUP_FILES
can be queried for information about all backups of any database registered in the recovery catalog. However, before querying RC_BACKUP_FILES
you must call DBMS_RCVMAN.SETDATABASE
, specifying the DBID of one of the databases registered in the recovery catalog, as shown in the following example:
SQL> CALL DBMS_RCVMAN.SETDATABASE(null,null,null,2283997583,null);
The fourth parameter must be the DBID of a database registered in the recovery catalog. The other parameters must all be NULL.
See also:
RC_BACKUP_FILES
viewOracle Database Backup and Recovery Basics for methods of determining the DBID of a database
The schema version of the recovery catalog is stored in the recovery catalog itself. The information is important in case you maintain multiple databases of different versions in your production system, and need to determine whether the catalog schema version is usable with a specific target database version.
See Also:
Oracle Database Backup and Recovery Reference for the complete set of compatibility rules governing the RMAN environmentTo determine the schema version of the recovery catalog:
Start SQL*Plus and connect to the recovery catalog database as the catalog owner. For example:
% sqlplus rman/cat@catdb
Query the RCVER
table to obtain the schema version, as in the following example (sample output included):
SELECT * FROM rcver; VERSION ------------ 09.02.00
If the table displays multiple rows, then the highest version in the RCVER
table is the current catalog schema version. The table stores only the major version numbers and not the patch numbers. For example, assume that the rcver
table displays the following rows:
VERSION ------------ 08.01.07 09.00.01 09.02.00
These rows indicate that the catalog was created with a release 8.1.7 executable, then upgraded to release 9.0.1, and finally upgraded to release 9.2.0. The current version of the catalog schema is 9.2.0.
If you use a version of the recovery catalog that is older than that required by the RMAN client, then you must upgrade it. For example, you must upgrade the catalog if you use a release 8.1 version of the RMAN client with a release 8.0 version of the recovery catalog.
You receive an error when issuing UPGRADE
CATALOG
if the recovery catalog is already at a version greater than that required by the RMAN client. RMAN permits the UPGRADE
CATALOG
command to be run if the recovery catalog is current and does not require upgrading, however, so that you can re-create packages at any time if necessary. Check the message log for error messages generated during the upgrade.
To upgrade the recovery catalog:
To install the new recovery catalog schema, the recovery catalog user must have TYPE privilege:
sqlplus> connect sys/oracle@catdb as sysdba; sqlplus> grant TYPE to rman;
Use RMAN to connect to the target and recovery catalog databases. For example, enter:
% rman TARGET / CATALOG rman/cat@catdb connected to recovery catalog database PL/SQL package rcat.DBMS_RCVCAT version 08.00.04 in RCVCAT database is too old
Issue the UPGRADE
CATALOG
command:
UPGRADE CATALOG; recovery catalog owner is rman enter UPGRADE CATALOG command again to confirm catalog upgrade
Enter the UPDATE
CATALOG
command again to confirm:
UPGRADE CATALOG; recovery catalog upgraded to version 09.02.00 DBMS_RCVMAN package upgraded to version 09.02.00 DBMS_RCVCAT package upgraded to version 09.02.00
See Also:
Oracle Database Backup and Recovery Reference for UPGRADE
CATALOG
command syntax
If you do not want to maintain a recovery catalog, then you can drop the recovery catalog schema from the tablespace. The DROP
CATALOG
command deletes all information from the recovery catalog. Hence, if you have no backups of the recovery catalog schema, then backups of all target databases managed by this catalog may become unusable. (The control file of the target database will still retain a record of recent backups.)
The DROP
CATALOG
command is not appropriate for unregistering a single database from a recovery catalog that has multiple target databases registered. Dropping the catalog deletes the recovery catalog record of backups for all target databases registered in the catalog.
To drop the recovery catalog schema:
Use RMAN to connect to the target and recovery catalog databases.
% rman TARGET / CATALOG rman/cat@catdb
Issue the DROP
CATALOG
command twice to confirm:
DROP CATALOG; recovery catalog owner is rman enter DROP CATALOG command again to confirm catalog removal DROP CATALOG;
Note:
Even after you drop the recovery catalog, the control file still contains records about the backups. To purge RMAN repository records from the control file, re-create the control file.See Also:
Oracle Database Backup and Recovery Reference forDROP
CATALOG
command syntax, and "Unregistering a Target Database from the Recovery Catalog" to learn how to unregister a database from the catalog