The Oracle10g Recovery Manager (RMAN) is a tool that uses features of the Oracle10g server to automate the process of creating database backups and restoring the database from backup. It provides the following features:

· Hot backup. (The database must be in “ARCHIVELOG” mode, but BEGIN/END BACKUP is not used.)

· Block-level incremental backups.

· Automatic tracking of backup copies for data files, control files and log archives.

· Automatic selection of backups to be restored, when a restoration is necessary.

· Automatic application of log archives.

Note that RMAN is only a command interface to the database - the actual backup is performed by a dedicated server process on the Oracle10g database.

Recovery Manager can be operated in catalog or no catalog mode. It can also be used in command-line mode or from within Oracle Enterprise Manager (OEM). In catalog mode Recovery Manager keeps track of backups in a recovery catalog. In no catalog mode Recovery Manager keeps track of the required information in the database control file(s) of the database being backed up.

Recovery Manager is capable of using a media management library from various third-party software packages to perform tape I/O. It is the responsibility of that software to keep track of what is on the tapes in the tape library, and to arrange to have the appropriate tapes mounted at the correct times.

In this initial configuration <some company> will not be linking Recovery Manager with a media management library. Backups will be performed by Recovery Manager to disk and offline storage of files will be handled outside of Recovery Manager. It is the administrator’s responsibility to keep track of files and their location.

Recovery Manager does not backup tnsnames, sqlnet, listener or password files, scripts, programs, or anything else stored in the Linux file systems. It only handles database control files, data files, and the log archives, and optionally, the init.ora file may be backed up by Recovery Manager.

Since we are using a recovery catalog and scripts we can embed host commands in them to cover the appropriate omitted files. Other files to consider backing up this way might include database creation scripts and any critical administration scripts such as those for index reorganization.

Recovery Manager does not backup the online redo logs. When the database server is not using a given online log, the server archives it, so backing up such a log file would be redundant with backing up archive logs. On the other hand, when the server is actively using a log, the contents are constantly changing, so any backup copy would quickly be out of date. Most recovery procedures assume the existence of intact online redo logs. When these logs are not available, the DBA must employ a “point-in-time” recovery technique to recover up to the end of the last available log, implying that some committed transactions will be lost.

New Recovery Manager Features in Oracle10g

This version of Recovery Manager resolves several performance and functionality issues with earlier releases and also adds new capabilities. The features introduced with Oracle10g include the following:

· The ability to use all archived redo logs, before and after an “alter database resetlogs” operation, to perform a recovery.

· Recovery previews. Viewing the contents of a backup prior to restoring.

· Faster incremental backups. In earlier releases Recovery Manager detected changed blocks by scanning all possible blocks. In 10g we will implement a change log file to track changed blocks-greatly increasing performance.

· Merged backups. Incremental backups can be merged with prior full backups. The merge takes place as part of the Recovery Manager “backup” command by making a copy of the prior backup and merging that with the new incremental backup.

· Compressed backups. Recovery Manager can compress the backup files as they are written to disk-greatly reducing storage space for online backups.

Recovery Catalog

The recovery catalog may not reside in a database being backed up by Recovery Manager.

<some company> will have a single Oracle10g instance whose only application is the RMAN Recovery Catalog. This database will reside on one of the smaller Linux servers (TBD); it may share the host with other applications, but will not share a database with any other application.

While the contents of the catalog are critically important in automated restoration, it is not required that the recovery catalog database be available 100% of the time. Recovery Manager can perform backups without the catalog, although it cannot use any stored script to do so. Furthermore, the RMAN catalog database will be less than 100 megabytes. Therefore, the catalog database will be backed up via cold backups, at a time of day when no other backups are running. Such backups will be created with a third-party command line interface, possibly automated via a shell script or batch file.

The recovery catalog could optionally be backed up by using a second recovery catalog that resides in a separate instance; but this is an additional complication which we will avoid for the initial installation.

Cold backup was selected for the following reasons:

· It is the simplest strategy, since it does not depend on the redo logs or log archives.

· The catalog databases are small enough that cold backup is practical.

· Since this is not a user application, frequent down time is tolerable. By having the catalog in an isolated database, the catalog databases can be stopped and started with no impact on any other system.

· Although it is possible to user RMAN to backup the catalog, this would require a second catalog in another database instance. A catalog cannot be used to restore itself.

Note – to support the RMAN “resynchronize” operation, the Oracle10g target database must have a value for CONTROL_FILE_RECORD_KEEP_TIME (an initialization parameter set in the init.ora) high enough to cover at least one and preferably two backup cycles of the recovery catalog. The default value is 7. Assuming the recovery catalog is backed up 7 days a week then it is recommended that the value not be lowered below 3.

Backup Cycles

This section applies only to Oracle10g databases.

For purposes of standardization, all databases at <some company> will have backup jobs in the categories listed below.

Within a given database, the tablespace will be grouped into sets based on insert/update/delete characteristics, as follows:

· Mostly queries

· Heavy insert/delete or insert/truncate

· Heavy update

· Heavy insert/update/delete

The following text has been struck through since <some company> is not linking through the media management libraries of a third party tape device; however, it is included here for completeness with regard to the rotations proposed.

Sets of tablespaces will also be distinguished by the choice of device on which the backups are stored. The backups will be stored on disk for those tablespaces with requirements for very quick recovery and where adequate disk space is available; otherwise the backups will be stored on tape.

Each set of tablespaces will be backed up by a separate set of backup jobs; no single backup job will handle tablespaces with different insert/update/delete characteristics. This way, the jobs for one set of tablespaces can be modified or scheduled independently of the jobs for other types of tablespaces. For each set of tablespaces, the DBA must choose the frequency of the backup jobs and the destination device. If the database is Oracle10g, the DBA must also choose the incremental rotation.

Below is an example backup rotation for a hypothetical Oracle10g database:

Tablespace Set	Characteristics	Backup Rotation	RMAN Scripts	Backup Device
System, RBS	Insert/update/delete	Daily level 0	Backup_system_level_0	Tape
Tools	Mostly queries	2 level 7 day	Backup_tools_level_0 Backup_tools_level_1 Backup_tools_level_2	Tape
Data, Indices	Insert/truncate	2 level 7 day	Backup_data_level_0 Backup_data_level_1 Backup_data_level_2	Tape
Log Archives		Daily	Backup_log_archives	Tape

These rotations should be kept simple. For some databases, it may be adequate to include the entire database in a single backup job at fixed intervals, thus requiring only one job for the database and one for the log archives. However, this write-up does not assume that one size fits all.

Note: This matrix will require modification as all incremental levels other than 0 have been depreciated with Oracle10g.

Complete Backup

Each set of tablespaces will have one job that performs a complete backup of the tablespaces. The frequency of this job will depend on the size of the tablespaces and the rate at which it is updated. For most sets of tablespaces, the complete backup will be performed once per week. These jobs will be scheduled throughout the week, so that we are not trying to backup everything on the weekend.

Each complete backup defined the beginning of a backup cycle. To avoid having one complete backup as a single point of failure, a rotation of at least two cycles will be retained. The oldest cycle will not be discarded until a new complete backup has been created.

For Oracle10g databases, the complete backup job will be implemented as a level 0 incremental backup.

Log Archives

All databases will have a backup job that creates disk copies of all log archives that are still on the disk; the job may also remove the archives from the original disk location(s). The frequency at which the job runs depends on the rate at which the database produces log archives; for most databases, this job will run daily.

Since a log archive can be a single point of failure during a recovery, ideally there should be copies of every log archive on two separate tapes. There is no straightforward way to do this with most media management libraries, and certainly no easy way without. A logical plan to accomplish this would be to establish a tape duplication job which will run separately from the third-party backups. This must be done separately by the DBA and is not handled with the initial installation of the Recovery Manager procedures.

Incremental Tablespace Backup

Incremental backups are logically similar to log archives, in that they provide block-level changes needed to update a file from one point in time to another. However, there are two cases where incremental backups are superior to log archives:

· A tablespace that is a small part of a database that has many updates. To recover such a tablespace with log archives, one must roll forward through the transactions for the whole database just to find those that apply to the specific tablespace. However, an incremental backup of the tablespace would contain updates that apply only to that tablespace.

· A tablespace that is frequently updated but rarely inserted, such that the updates tend to hit the same blocks repeatedly. The log archives will contain all such updates, while an incremental backup would contain only the last version of each affected block.

If neither of these two situations applies, then there is little point in using incremental backups, as they would be almost equivalent to log archives.

Incremental backup jobs will be constructed in such a way as to allow a recovery using at most two increments beyond the level 0 backup. At present, only three variations on incremental backups are planned:

Note: This section subject to change as incremental backups other then level 0 have been depreciated in the Oracle10g database software.

· No incremental backups. This is suitable for tablespaces that are primarily insert-only.

· Single-level. This consists of a cumulative level 1 incremental backup performed any number of times between level 0 backups. This is suitable for tablespaces that receive very few inserts, such that cumulative incremental backups will not grow significantly. It is also useful in those cases where the level 0 backups are more frequent than once per week.

· Two-level 7-day rotation. This consists of the incremental backups listed below. Using this rotation, a recovery to any day of the week requires restoration from at most three backups, and the incremental backups contain at most 3 days of updated blocks.

(Chart not included – as depreciated levels being analyzed for impact)

Control Files

Every database will have one RMAN procedure to back up the control file. This procedure will be run every day, between the tablespace backups and the log archive backups. This procedure will also issue the command “alter system archive log current” to ensure that the redo log that was in use at the time of the backup will itself be backed up with the log archives.

Non-Routine Backups

All Oracle10g databases will have the following RMAN scripts to be run manually whenever the DBA considers it necessary.

· Backup_db_full – Perform a full backup (not a level 0 incremental) of the whole database.

· Backup_db_level_0 – Run all of the level 0 backup scripts.

When RMAN is linked with a media management library there are a number of environment variables that must be set prior to starting RMAN. These are not fully documented here since <some company> is not linking to these libraries.

The rman.profile script is semi-covered in this document.

Note – The Backup_db_level_0 script may be replaced with Backup_db_log_archives based on depreciated commands in 10g.

Scripts and Command Files for Routine Backups

The backup rotation framework will be defined at two levels, described in more detail below:

· Media Management Library (3^rd party tape software) scripts.

~~Note – These will not be created or documented with initial installation.~~

· Each database will have a set of RMAN command scripts to specify exactly which backup tasks are needed on a given day.

It is getting a bit cumbersome to not be integrating with a tape backup system as there are various settings that would customarily take place outside of RMAN. The rman.profile script would normally be used to set environment variables for tape components, and it is here that the standard output and standard error would be redirected to a log file.

Note – The rman.profile will not be complete for this installation.

Routine backups are normally scheduled from the tape scheduler, via the following (in this example) NetBackup classes of type “obackup”:

NetBackup Class	Shell Scripts
oracle10_rman_day1	/usr/openv/netbackup/rman/day1.backup
oracle10_rman_day2	/usr/openv/netbackup/rman/day2.backup
oracle10_rman_day3	/usr/openv/netbackup/rman/day3.backup
oracle10_rman_day4	/usr/openv/netbackup/rman/day4.backup
oracle10_rman_day5	/usr/openv/netbackup/rman/day5.backup
oracle10_rman_day6	/usr/openv/netbackup/rman/day6.backup
oracle10_rman_day7	/usr/openv/netbackup/rman/day7.backup

Note – Any backup which must be run more than once per day will be handled as a special case.

Every host that has Oracle10 databases will have a set of seven shell scripts that will be called to run RMAN, as listed in the table above. Each RMAN script will have a series of commands, one for each Oracle10 database on that host, of the form shown below:

su – oracle –c /…/dba/oracle/application/backup/day1.application.sh

where the directory path is to a DBA directory and the file is a database-specific backup driver script.

After the script sets environment variables the following actions will take place:

cd `dirname $0` - go to backup directory

rman target / as sysdba rcvcat $RMAN_CONNECT <<-EOF

run { execute script backup_system_level_0; }

EOF

Note – connect string to be modified, as required.

RMAN has no error-recovery mechanism. If the backup encounters any “ORA-” errors, RMAN will report the errors, release all resources, and terminate without finishing any remaining backups. Since it is desirable to finish as much of the backup as possible regardless of failures, the shell script will be constructed so as to continue with subsequent backups whenever one part of a daily backup fails for any reason. To accomplish this, the shell script will call RMAN one or more times, and the input to RMAN on each invocation will be a command to execute one pre-defined script. Thus, if any RMAN backup script fails, the shell script will proceed to the next backup script.

One situation (in Oracle8i) where RMAN will routinely encounter server errors is when it runs an incremental backup on a set of files and there have been no changes in those files since the previous backup. The Oracle server reports this as ORA-19648, “incremental-start SCN equals checkpoint SCN”.

Oracle Technical Support confirmed that indeed RMAN cannot run an incremental backup when the resulting incremental would be empty, though they acknowledge that such a limitation makes it difficult to automate incremental backups. The error recovery strategy described earlier allows the remaining backups in a daily rotation to proceed regardless of whether a given incremental backup is possible or not.

Note – This issue should be corrected with 10g.

Restoration and Recovery

By definition, a recovery operation is a response to an unplanned event; therefore, it is difficult to automate the task fully for all possible unplanned events. However, it is possible to construct a set of recovery scripts that cover some typical failure scenarios, and these scripts can be used as the basis for ad hoc recovery in other cases.

In the following sections, the bullets listed under “applicable situations” are only hints as to when a particular restoration/recovery script might be useful. There is no guarantee that running the specified script will actually fix the problem; the DBA must still use some intelligence. Therefore, the target audience for this write-up is DBAs who are familiar with RMAN and the basics of the restoration and recovery process.

Note – Oracle defines “restoration” as the process of copying a backup of a datafile onto disk where the server can use it, and “recovery” as the process of rolling forward through log files to bring a restored datafile up-to-date.

RMAN

Preparation and Execution

Though the specifics of a particular recovery may vary, the basic outline is as follows:

1. Log in as Oracle on the host for the database being recovered.

2. Go to the “backup” directory.

3. Set environment variables ~~(Run rman.profile).~~

4. If necessary use SQL-Plus to perform any prerequisites.

5. Run the RMAN restoration and recovery. This may involve running a pre-defined recovery script described in the next section, customizing a pre-defined script, or a completely ah hoc manual procedure.

6. Perform any necessary follow-up actions via SQL-Plus.

Pre-Defined Scripts for Recovery

All Oracle10g databases will have the following restoration/recovery scripts pre-defined. The generic command to run a pre-defined script is as follows:

Run {execute script script-name:}

recover_db

Purpose: To roll forward through logs without restoring any datafiles from backup.

Applicable Situations:

§ The server refuses to start, saying that the database needs media recovery.

Prerequisites:

§ The database must be mounted but not opened.

Tasks performed by RMAN:

§ Chooses logs to be applied.

§ Locates the logs on disk ~~(or restores them from tape)~~

§ Applies the logs to any database files that need them.

Follow-up:

§ Open the database.

restore_recover_tablespace

Purpose: To restore all of the datafiles for a single tablespace (other than system, rollback, or temp) and roll forward through the log files. Note – because tablespace names are hardwired in the script, a different script must be used for each tablespace; these may be created in advance or when needed. If you do not find one for a tablespace that needs recovery, copy the script from another tablespace (other than system, rollback, or temp), and change the tablespace name.

Applicable Situations:

§ The server says that a datafile in the tablespace needs media recovery.

§ A file from the tablespace has been corrupted, removed, or otherwise lost.

Prerequisites:

§ Suspend all activities that access the tablespace in question.

Tasks performed by RMAN:

§ ~~Restores all files in the tablespace from tape.~~

§ Chooses logs to be applied.

§ Locates the logs on disk ~~(or restores them from tape)~~

§ Applies the logs to the tablespace files.

§ Brings the tablespace online.

Follow-up:

§ Resume suspended activities.

restore_recover_rbs

Purpose: To restore all of the datafiles for the rollback tablespace and roll forward through the log files.

Applicable Situations:

§ The server says that a datafile in the rollback tablespace needs media recovery.

§ A file from the rollback tablespace has been corrupted, removed, or otherwise lost.

Prerequisites:

§ Suspend all activities that access the database.

§ Start the instance in the MOUNT mode. You will probably not be able to open the database since the rollback segments will not be available.

Tasks performed by RMAN:

§ ~~Restore all files in the tablespace from tape.~~

§ Chooses logs to be applied.

§ Locates the logs on disk, ~~(or restores them from tape).~~

§ Applies the logs to the tablespace files.

Follow-up:

§ If any datafiles in the rollback tablespace are still offline, then bring them online.

§ Open the database.

restore_recover_system

Purpose: To restore all of the datafiles for the system tablespace and roll forward through the log files.

Applicable Situations:

§ The server says that a datafile in the system tablespace needs media recovery.

§ A file from the system tablespace has been corrupted, removed, or otherwise lost.

Prerequisites:

§ Suspend all activities that access the database.

§ Start the instance in the MOUNT mode.

Tasks performed by RMAN:

§ ~~Restores all files in the tablespace from tape.~~

§ Choose logs to be applied.

§ Locates the logs on disk, ~~(or restores them from tape).~~

§ Applies the logs to all database files that need them, whether in the SYSTEM tablespace or not. (Such is required for SYSTEM tablespace recovery.)

§ Opens the database.

Follow-up:

§ If the TEMP tablespace was also affected by the failure, then drop it and create a new one. See special notes on TEMP tablespace, below.

restore_recover_db

Purpose: To restore all database files and roll forward through log archives.

Applicable Situations:

§ Disasters where all or most database files have been lost but online control files and redo logs are still available.

§ A database has been moved to another machine, where copies of online control and redo logs can be obtained from a brief cold shutdown.

Prerequisites:

§ Suspend all activities that access the database.

§ Start the instance in the MOUNT mode.

Tasks performed by RMAN:

§ ~~Restores all files in the tablespace from tape.~~

§ Chooses logs to be applied.

§ Locates the logs on disk, ~~(or restores them from tape).~~

§ Applies the logs to the database files.

Follow-up:

§ Open the database.

§ Replace the TEMP tablespace. See special notes on TEMP tablespace, below.

restore_recover_db_cf

Purpose: To restore all database files and roll forward through log archives.

Applicable Situations:

§ Disasters where the entire database has been lost.

§ A database has been moved to another machine, and the control files and online redo logs are not available.

Prerequisites:

§ Suspend all activities that access the database.

§ Start the instance in the NOMOUNT mode.

Tasks performed by RMAN:

§ ~~Restores all files in the tablespace from tape.~~

§ Mounts the database.

§ Restores all database files from disk AFTER they are restored by the DBA from tape to disk location.

§ Chooses logs to be applied.

§ Locates the logs on disk, ~~(or restores them from tape).~~

§ Applies the logs to the database files.

WARNING:

If the online redo logs are missing, then it is guaranteed that this script will not be successful. Instead, the DBA must make a customized version of “restore_recover_db_cf”, that runs the “recover database” command with the “UNTIL LOGSEQ” operand, to make the recovery stop at the last available log sequence. See the RMAN manual for syntax details. IMPORTANT: The log sequence number that you use here is the first log that is NOT available; RMAN will not apply this log sequence. This implies three things: (1) all files must be recovered from backups that are older than the last available log sequence; (2) committed transactions in the missing log files will be lost; and (3) there is no pre-defined script to do this operation because the DBA must determine the upper bound on log sequence at the time of the recovery.

Follow-up:

§ Open the database with the “RESETLOGS” option.

§ Replace the TEMP tablespace. Se special notes on TEMP tablespace, below.

§ Issue the RMAN command “reset database”. This is required after a RESETLOGS, to update the RMAN catalog.

§ Create a new backup.

Special Notes on Temporary Tablespaces

A temporary tablespace contains such things as sort segments, which exist only while a transaction is running. These are discarded when transactions are committed or rolled back. They are not used during a recovery, and will always be discarded when a recovery completes. Therefore, there is no point in backing up or recovering a temporary tablespace.

However, the Oracle server will still insist that the datafiles that comprise the temporary tablespace be synchronized with the rest of the database. When such a file is lost or corrupted, the recovery process involves dropping the tablespace and creating a new one to replace it.

Prerequisite: The database must be mounted or opened.

1. Take the missing or corrupted files off line via the command “alter database datafile ‘file’ offline drop;”

2. Open the database, if it is not already opened.

3. Drop the tablespace: “drop tablespace temp including contents;”

4. Create a new temporary tablespace.

Oracle10g “Issues” to Incorporate into this Document

Both Oracle Managed Files (OMF) and Automated Storage Management (ASM) are new in Oracle9i. Each of these components potentially uses new, separate naming conventions for objects (files, tablespaces, etc.) that are not necessarily recognized by “default” Recovery Manager syntax, or more specifically, pointed to by the scripts I previously planned to write.

The database installation at <some company> includes the creation of tablespaces that potentially violate the naming conventions of ASM files. We must make sure that we backup all files, as required.

The use of Flash Recovery Areas must also be addressed.

Persistent Parameters

RMAN can be configured in Oracle10g by using persistent parameters. Refer to Oracle documentation for a full description. The following parameters are of particular interest for <some company>’s backups:

· Retention Policy – instructs RMAN of backups okay to be deleted. A retention policy of 2 would allow backups older than the latest and the one prior to the latest to be deleted.

· Default Device Type – this can be “disk” or “sbt” (system backup to tape). <some company> will be using “disk” for the initial implementation and the DBA will be manually backing up files to tape.

· Controlfile Autobackup – when set to “on” RMAN will backup of the controlfile and server parameter file (pfile, init.ora) each time a backup is performed.

· Controlfile Autobackup Format – tells RMAN where to store the file. The “%F” in the file name instructs RMAN to append the database identifier (DBID) and backup timestamp to the backup filename.

· Parallelism – tells RMAN how many server processes to use while performing backups. Oracle8i performance was best when set to 3. This value should be confirmed for 10g.

· Device Type Format – tells RMAN the location and name of the backup files. The “%U” tells Oracle to append a unique identifier to the backup file name.

To view parameters in RMAN use the “show all;” command. The values can be changed with the command that is displayed in the “show all” output.

Appendix A. Scripts.

To run a manual backup, you must set environment variables before starting RMAN. The rman.profile script will set at least the following environment variables:

· ORACLE_HOME	The full path of the Oracle home directory for the database instance
· ORACLE_SID	The SID identifying the instance being backup up
· PATH	The shell execution path, which must contain at least $ORACLE_HOME/bin
· LD_LIBRARY_PATH	The path names of directories containing shared libraries used by the Oracle server, which must include at least $ORACLE_HOME/lib and the path of the media management libraries (when implemented)
· RMAN_CONNECT	The user name, password, and two-task connect string which identifies the RMAN catalog

Additional variables will be added to this script when the media management libraries for a tape device are implemented.

The rman.profile script will also redirect standard output and standard error to a log file.

Complete Backup

Example only…

The backup script is stored in a file named "rman_backup.rcv";

# comment lines are ignored by RMAN
backup database plus archivelog;
restore database validate;
delete noprompt obsolete;
host 'cp <path>\pwd<SID>.ora <path>\backup\.';
host 'cp <path>\tnsnames.ora <path>\backup\.';
host 'cp <path>\listener.ora <path>\backup\.';
exit;

SQL commands can also be executed from RMAN, for example:

sql 'alter system archive log current';
sql "create pfile=''<path>\backup\init<SID>.ora'' from spfile";

The SQL keyword tells RMAN that the command is SQL. The command is enclosed in quotes. Two single quotes are substituted for commands that need to have quotes within their syntax.

The above script is executed from a shell file with the following syntax:

REM contents of rman_backup.sh
rman target / cmdfile rman_backup.rcv log rman_backup.log

Scheduling these scripts would customarily be done in the tape software. Since OEM will be used at <some company> the scheduling of scripts will be completed in OEM.