Recovery Manager (RMAN)

Topics

Recovery Manager Major Features
Recovery Manager contains a number of new features to aid in the backup, restore and recovery operations in large, complex Oracle environments. These include:

Backup file Compression
Recovery Manager only copies data blocks that contain data. A tablespace that is only half filled will be backed up into a file half the size of an OS image copy of the same tablespace.

Incremental Backups
This feature allows backups to be performed that contain only database blocks that have changed since the last full or incremental backup. This can greatly reduce the size of backups. It can also reduce the time required to do a restoration. A typical scenario would be to perform a weekly full backup and daily incrementals.

Corrupt Block Detection
When a backup is executed with Recovery Manager, the database blocks are read and corruption is automatically detected. By default, when a corrupt block error is detected by Recovery Manager, an error message will be displayed (or written to a log file if not running interactively), and the backup terminates. This error message will identify the file that the corrupt block is in. By setting the value of maxcorrupt to something greater than zero (the default) and re-executing the backup or copy, more information on the corrupt block will be put in the views V$BACKUP_CORRUPTION or V$COPY_CORRUPTION.

Fractured Block Detection
Another benefit of an Oracle server process being used when executing Recovery Manager is that an Oracle server process can detect when a data block is fractured. A data block is considered fractured when the control information in the header does not match the information stored in the footer. A fractured block can occur during an online backup because the Oracle data blocks are not always the same size as the operating system block size.

Oracle data blocks are referred to as logical blocks while operating system blocks are called physical blocks. In most environments, the logical block contains multiple physical blocks. Therefore, while the operating system is backing up a database, Oracle can be writing into multiple physical blocks at the same time the operating system is reading a subset of the physical blocks written to by Oracle. To resolve this problem, Oracle implemented the hot backup mode , which tells Oracle to copy the entire logical block to the redo log buffer when a data block is modified. When the database is not in hot backup mode, only the before and after image of the modified row is written to the redo buffer.

Since the server process can detect fractured blocks and will reread the block if a fracture is detected, the hot backup mode is not necessary using Recovery Manager. Although Oracle8 does support the following statements:

o         alter tablespace <tablespace name> begin backup

o         alter tablespace <tablespace name> end backup

they are not needed when implementing online backups through RMAN.

File Multiplexing
Another benefit of using Recovery Manager is that Oracle server processes can operate on one or more datafiles and/or tablespaces concurrently. The server process will read a set of data blocks from each datafile and intermingle the data blocks from separate data files into the backup set file (backup piece). This means that a backup set can consist of one or more files, and each file in the backup set can contain data blocks from multiple database datafiles.

For example, assume a backup set will contain data files 1 - 5 from the database. Assume during the backup operation, the filesperset option was set to 5. Therefore, the backup set will contain only one file. However, this backup set file will contain all the used data blocks from data files 1 - 5 in the database. Furthermore, the data blocks from each data file will not be segregated within the backup set file, but will be mixed with data blocks from the other four data files.

File multiplexing aids in the performance of the backup. A backup set can consist of datafiles from separate disks. This helps to distribute the read operations so that an I/O bottleneck does not occur while performing backups.

Automatic Parallelization
Backup and restore operations are automatically parallelized. This is supported with both disk and tape backup/restore operations. The parallelization can be implemented by using the allocate channel and filesperset Recovery Manager commands. Allocate channel is used to assign I/O devices, and the filesperset is used to define how many database files will be written to a single backup output file (called a backup set). The total backup time can be reduced by taking advantage of parallelism, when the necessary hardware is available.

Backup Usability and Restore Validation Reporting
Recovery Manager has several features that allow a DBA to look at both a running database and backups that have been performed on that database. This information can answer such questions as:

The Recovery Manager report and recover commands can be used to answer these questions. These commands can be executed interactively, or as scripts.
To find out if a tablespace has a datafile that can't be recovered (this could be due to an unrecoverable operation, such as running SQL*Loader in UNRECOVERABLE mode), enter:
report unrecoverable tablespace "users";

To determine if a database has not been backed up within the last 7 days, enter:
report need backup days 7 database;

Backups can become obsolete for various reasons including: a backup of a database file that no longer exists, or there is a more recent backup of an existing file. A report of obsolete backups can be used to determine what could be deleted from backup media to free up space. To find backups that are obsolete because there are at least 2 more recent backups, enter:
report obsolete redundancy 2;

Recovery Manager has the capability of doing a "dry run" restoration of a database. This can be valuable in determining that all files needed for the restoration are available from backup media. The following is an example of validating restoration for an entire database:
run {
allocate channel d1 type disk;
restore database validate;
}

This command checks for the database files needed for restoration, but not the archive log files that may be needed to recover.

Recovery Catalog for Centralized Operations
The recovery catalog is used to store information regarding backup and restore operations. The catalog is a schema that should reside in a database other than those being backed up (target databases). The recovery catalog can contain information about multiple target databases. The reports described in the previous section were generated from information stored in the recovery catalog. The recovery catalog is also used to store scripts of Recovery Manager commands.
 
 

RMAN Architecture
The production database that you are backing up is called the target. A separate database, called the recovery catalog, contains information about datafile and control-file copies, backup sets, archived redo logs, and other key elements needed for recovery.

RMAN performs two main functions:
 - It maintains the RMAN metadata in the control file or the recovery catalog.
 - It communicates with the Oracle database and the operating system in order to create, restore, and recover backup sets and image copies. (RMAN writes image copies to disk only, not to tape.)
RMAN creates several client connections, or channels, between the target database and the backup storage device. RMAN can create backup sets on disk or directly on tape. To use tape storage, RMAN requires a media manager

If you have more than one database to back up, you can create more than one recovery catalog and have each database serve as the other's recovery catalog. For example, assume there are two production databases, one called "prd1," and a second called "prd2." You can install the recovery catalog for "prd1" in the "prd2" database, and the recovery catalog for the "prd2" database in "prd1." This enables you to avoid the extra space requirements and memory overhead of maintaining a separate recovery catalog database. However, this solution is not practical if the recovery catalog databases for both reside in tablespaces residing on the same physical disk. Typical recovery catalog space requirements for 1 year are:
  

Tablespace Name

Space

SYSTEM

50 MB

TEMP

5 MB

ROLLBACK

5 MB

RECOVERY_CATALOG

10 MB

ONLINE LOGS

1 MB each one


 

RMAN Setup and Configuration
To get the most out of RMAN, you should always use a recovery catalog, which contains details of all backup-and-recovery-related operations. RMAN uses the catalog to identify the relationship between backups and database files, automatically deciding which recovery events will minimize the mean time to recover (MTTR). If you run RMAN without using a recovery catalog, it will gather the necessary information from the database's control file but won't support point-in-time recovery. And if you lose all copies of your control file you'll be out of luck. That's why unless you have only one computer running the Oracle software, you should always use a recovery catalog and store it on totally separate hardware from any of your production or target instances - preferably in a separate location. The catalog itself rarely occupies more than 40MB.

STEP 1 Modify your Net8 Connnections and Create the Recovery Catalog
Before invoking RMAN, set the NLS_DATE_FORMAT and NLS_LANG environment variables. Much of the RMAN LIST output is date/time related. It is often necessary to have this information displayed as accurately as possible when performing time-based recovery. Example NLS settings:

NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS

Your TNSNAMES.ORA file should have an entry for both your catalog and target databases. For our paper, we will use two databases, the catalog database (RMAN) and the target database (PROD). Connect to the Oracle instance by using your DBA account, create an account for RMAN, and grant it appropriate rights.

connect internal
create tablespace RMAN datafile '.......' size 20M autoextend on next 5M maxzize 300M;
create user RMAN identified by rman temporary tablespace temp default tablespace RMAN quota unlimited on RMAN;
grant RECOVERY_CATALOG_OWNER, connect, resource, dba to RMAN;
connect RMAN/RMAN@RMAN

Then, create the catalog.
%export ORACLE_SID=RMAN
rman catalog rman/rman
create catalog;

STEP 2 Register Your Database
Now you need to register your target database or databases with the catalog you've created. This populates the catalog with initial information regarding the configuration of your target database and includes a full synchronization of the catalog with the target database's control file. This sample RMAN session shows the process:

export ORACLE_SID=PROD
rman target / rcvcat rman/rman@RMAN

OR

rman target <qstring> [rcvcat <qstring> | cmdfile <qstring> |
msglog <qstring> | append | trace <qstring>]

Argument     Quoted String Description

TARGET= A connect string containing a userid and password for the database on which Recovery Manager is to operate.

RCVCAT= A connect string that contains a userid and password for the database that contains the recovery catalog (RMAN).

CMDFILE= The name of a file that contains the input commands for RMAN. If this argument is specified, RMAN operates in batch mode; otherwise, RMAN operates in interactive line mode.

MSGLOG= The name of a file where RMAN records commands and output Results. If not specified, RMAN outputs to the screen.

APPEND= This parameters causes the msglog file to be opened in append mode. If this parameter is not specified and a file with the same name as the msglog file already exists, it is overwritten.

TRACE=  A file name where RMAN will dump a trace information. (useful feature for RMAN jobs debugging)
 

Note: RMAN automatically requests a connection to the target database as SYSDBA. In order to connect to the target database as SYSDBA, you must either:

Be part of the operating system DBA group with respect to the target database. This means that you have the ability to CONNECT INTERNAL to the target database without a password.

-or -

Have a password file setup. This requires the use of the "orapwd" command and the initialization parameter "remote_login_passwordfile".

Then Register the Database:
register database

Once complete the DBID, DB_NAME, and structure are captured in the recovery catalog. To verify the registration run:
list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            CUR Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
38      39      DIEGO    4283267716       YES 19688901   10-MAY-01

Now we have the target database information stored in the recovery catalog. Notice the current column (CUR) as this becomes important later. When the database is opened with the RESETLOGS option, the catalog must also be reset. This is accomplished by running:
reset database;

This command lets RMAN know we have reset the TARGET database after an incomplete recovery. The next backup is the baseline for the new incarnation of the TARGET database and the current target would be listed separately in the list command.
report schema;    --> Shows information about datafiles in that DB
 

STEP 3 Back Up the Catalog
If you lose your catalog, you can partially regenerate it from the information in the control file, but you should avoid that predicament by backing it up. You can use RMAN to back up the catalog by creating a recovery-catalog schema in one of your other instances, using that as the catalog for your primary catalog instance. Providing that these two instances do not share any common resources, this role reversal is free from single points of failure.
 

STEP 4 Synchronize All Databases
It's important that your recovery catalog has an up-to-date view of your database. When you execute RMAN operations on the database, RMAN automatically synchronizes the catalog with the target. However, depending on the volatility of your database, you may need to resynchronize the catalog more frequently. Always include catalog resynchronization as part of any structural database change (the addition of files, tablespaces, and so on) and ensure that a resynchronization occurs at intervals less than the init.ora setting for the parameter CONTROL_FILE_RECORD_KEEP_TIME. To perform this action, issue the RESYNC CATALOG command from within RMAN.
 

STEP 5 Start RMAN
Now you are ready to produce your first backups. You can enter RMAN commands interactively, or you can hold them in RMAN-stored scripts or operating-system command files

export ORACLE_SID=PROD
rman target / rcvcat rman/rman@RMAN

or

rman
connect target internal/password@PROD
connect rcvcat rman/rman@RMAN
# then enter your RMAN commands here
 

STEP 6 Create Database Backups
The following code backs up your entire database, including the archive redo log files (furthermore,  it actually deletes the logs after backing them up) and a copy of the current control file :

run {
  # backup the complete database to disk
  allocate channel ch1 type disk;
  backup full database include current controlfile
  tag prod_full_backup
  format '/data/archdata/DBbackup/%d_t%t_s%s_p%p';
  sql "alter system archive log current";
  backup archivelog all delete input
  # or you can use backup archivelog from logseq=16 thread 1 delete input  for specific files
  format '/data/archdata/DBbackup/prodarch_t%t';
  release channel ch1;
 }

When you back up to disk, you need to tell RMAN where to put the backup files by using the format parameter. The format specifies the full path and filename of the backup. Here, %d is the database name, %t is the timestamp, %s is the backup set number and %p is the backup piece number. Archivelog backups cannot be tagged. Finally, we are backing up all the archivelogs starting from the log sequence number 16, since we do not have the older archivelogs that were produced before we set up RMAN. We are also deleting the archivelogs after the backup is successfully completed. If the backups fail, the archivelogs will not be deleted, but use this with caution, since archivelogs are paramount for full database recovery

So, in three lines of code, you can accomplish a full backup and dynamically identify the database files to be included. RMAN is very flexible, however, and can perform complex backup routines without your having to add much complexity to the code. The following script, for example, performs a full database backup to disk, in two parallel streams (two channels allocated, each of type 'disk'), automatically excluding any data files that support read-only tablespaces:

run {
allocate channel t1 type disk;
allocate channel t2 type disk;
backup
format '/backup/fullRMAN_%d_%s'
filesperset 10
skip readonly
database;
release channel t1;
release channel t2;
}

The filesperset value specifies the maximum number of datafiles that RMAN will write to a backup set. The combination of multiple output channels and files per set govern the operation's parallelism. RMAN backups that include the first data file in the SYSTEM tablespace automatically include a copy of the current control file. To back up the control file explicitly, add the INCLUDE CURRENT CONTROLFILE clause to your backup statement.

list backup;    --> Shows my backups
list copy;        --> Shows my copies
 
To save the script into the catalog you can use:
create script prod_full_backup {
 # backup the complete database to disk
  allocate channel ch1 type disk;
  backup full database include current controlfile
  tag prod_full_backup
  format '/data/archdata/DBbackup/%d_t%t_s%s_p%p';
  sql "alter system archive log current";
  backup archivelog all delete input
  # or you can use backup archivelog from logseq=16 thread 1 delete input  for specific files
  format '/data/archdata/DBbackup/prodarch_t%t';
  release channel ch1;
 }

replace script prod_full_backup {
  # backup the complete database to disk
  allocate channel ch1 type disk;
  backup full database include current controlfile
  tag prod_full_backup
  format '/data/archdata/DBbackup/%d_t%t_s%s_p%p';
  sql "alter system archive log current";
  backup archivelog all delete input
  # or you can use backup archivelog from logseq=16 thread 1 delete input  for specific files
  format '/data/archdata/DBbackup/prodarch_t%t';
  release channel ch1;
 }

To Drop it from RMAN just perform:
delete script  script_name;

To Execute it from RMAN just perform:
run {execute script prod_full_backup;}

To read the code from the Database you can run the following query:
select text from rc_stored_script_line where script_name='prod_full_backup';

Step 7 (optional) -  Then you can create a Unix Script
That will call the saved script in RMAN
We want to run it every Sunday night as a scheduled job. Let's run it as a cron job. Create a Korn shell script called "rman_backup.ksh". This shell script will run the RMAN script, prod_full_backup.rcv.

#!/bin/ksh
# Shell script to run RMAN script prod_full_backup.rcv
# Logging messages to prod_full_backup.log
# Set environment variables
export ORACLE_SID=PPPPROD
export ORACLE_HOME=/usr/local/OraHome1
# Connect to RMAN and run the backup script (all in one line)
rman target / rcvcat rman/rman@rman cmdfile /home/oracle/script/rman_prod_full_backup.rcv msglog /home/oracle/script/rman_full_backup.log
exit

Change permissions on the shell script appropriately.
chmod 755 rman_backup.ksh

Now, create the RMAN script file, prod_full_backup.rcv. This just runs the previously stored catalog script, prod_full_backup. Here are the contents of prod_full_backup.rcv

run {execute script prod_full_backup;}

Finally, schedule the shell script using the crontab.
[prod-server:oracle] $ crontab -e
00 21 * * 0 /home/oracle/script/rman_backup.ksh

We have scheduled our script to run every Sunday night at 9:00 PM.

Backing up in no archivelog mode
Target database is MOUNTED (but not open). If the database is open and not in archivelog mode, RMAN will generate an error when you attempt to perform a datafile backup

1. Example of how to back up a complete database
In one session perform:
shutdown immediate;
startup force dba;
shutdown immediate
startup mount; 

In another session perform:
run {
# backup the complete database to disk
allocate channel dev1 type disk;
# or backup database ?
backup
full
tag full_db_sunday_night
format '/oracle/backups/full_%d_%s'
(database);
release channel dev1;
 }

To view this backup in the catalog, use the following command:
list backup;
 

2. Example of how to back up a tablespace
run {
allocate channel dev1 type disk;
backup
tag tbs_users_read_only
format '/oracle/backups/tbs_users_t%t_s%s'
(tablespace users);
release channel dev1;
}

      Line#
         6: Specifying only the USERS tablespace for backup

To view this tablespace backup in the catalog, use the following command:
list backupset of tablespace users;

If for example the USERS tablespace is going to be put READ ONLY after being backed up, subsequent full database backups would not need to backup this tablespace. To cater for this, specify the 'skip readonly' option in subsequent backups.
 

3. Example of how to backup individual datafiles
run {
allocate channel dev1 type 'SBT_TAPE';
backup
format '%d_%u'
(datafile '/oracle/dbs/sysbigdb.dbf');
release channel dev1;
}
        Line#
          2: Allocates a tape drive using the media manager layer (MML)
          Note that no tag was specified and is therefore null.

To view this tablespace backup in the catalog, use the following command:
list backupset of datafile 1;
 

4. Copying datafiles

run {
allocate channel dev1 type 'SBT_TAPE';
copy datafile '/oracle/dbs/temp.dbf' to '/oracle/backups/temp.dbf';
release channel dev1;
}

To view this file copy in the catalog, use the following command:

list copy of datafile '/oracle/dbs/temp.dbf';

Copying a datafile is different to backing up a datafile. A datafile copy is an image copy of the file. A backup of the file creates a backupset.
 

5. Backing up the controlfile
run {
allocate channel dev1 type 'SBT_TAPE';
backup
format 'cf_t%t_s%s_p%p'
tag cf_monday_night
(current controlfile);
release channel dev1;
}

Note that a database backup will automatically back up the controlfile.
 

Full DB Backup
# Resync the catalogue
resync catalog;
# Backup the database
run {
 allocate channel 'dev0' type 'SBT_TAPE';
 allocate channel 'dev1' type 'SBT_TAPE';
 backup
  full
  tag Full_Oracle_Backup
  filesperset 5
  format '%d_%t_%s_%p.dbf'
  database
  ;
 backup
  filesperset 5
  format '%d_%t_%s_%p.cf'
  current controlfile
  ;
 sql 'alter system archive log current';
 backup
  filesperset 50
  archivelog all format '%d_%t_%s_%p.arch';

 release channel dev0;
 release channel dev1;
}
 

Backing up in archivelog mode
 The commands are identical to those in the previous sections, except that the target database is in archivelog mode.

1.Backing up archived logs
The following script backs up all archive logs:

run {
allocate channel dev1 type disk;
backup
format '/oracle/backups/log_t%t_s%s_p%p'
(archivelog all);
release channel dev1;
}
 

The following script backs up archive logs from sequence# 90 to 100:
run {
allocate channel dev1 type disk;
backup
format '/oracle/backups/log_t%t_s%s_p%p'
(archivelog from logseq=90 until logseq=100 thread 1);
release channel dev1;
}

The following script backs up all archive logs generated in the past 24 hours. Furthermore it actually deletes the logs after backing them up. If the backup fails, logs will NOT be deleted:
run {
allocate channel dev1 type disk;
backup
format '/oracle/backups/log_t%t_s%s_p%p'
(archivelog from time 'sysdate-1' all delete input);
release channel dev1;
}

To view the archive logs in the catalog, use the following command:
list backupset of archivelog all;
 

2. Backing up the online logs
Online logs CANNOT be backed up using RMAN; they must be archived first. To do this, you can issue SQL commands from RMAN e.g.
run {
allocate channel dev1 type disk;
sql "alter system archive log current";
backup
format '/oracle/backups/log_t%t_s%s_p%p'
(archivelog from time 'sysdate-1' all delete input);
release channel dev1;
}

The above script might be run after performing a full 'database open' backup. It would ensure that all redo to recover the database to a consistent state would be backed up.
 
 

Incremental backups
If your database is particularly large, you may want to consider making incremental backups. Unlike incremental exports, which operate at a table level, RMAN incremental backups back up only changed blocks. This approach optimizes not only backup times but recovery operations as well, because RMAN intelligently decides what combination of full, incremental, and archive-log backups will produce the optimal recovery path.
RMAN uses the concept of multilevel incremental backups, with levels 0, 1, or 2. Level 0 is equivalent to a full backup. An incremental backup at a level greater than 0 copies only those blocks that have changed since a previous incremental backup at the same or lower level. For example, a level-1 incremental backup backs up any blocks that have changed since the most recent backup at level 0 or 1. A level-2 backup backs up only blocks that have changed since the last backup at any level. (See the Oracle8 Backup and Recovery Guide for more details.)
To execute an incremental backup, include the incremental keyword and a level number in the backup statement:
# Resync the catalogue
resync catalog;
# Backup the database
run {
allocate channel t1 type sbt_tape;
backup
incremental level 0
 format 'full_inc1_%u'
database;
release channel t1;
}

Another Example:

# Resync the catalogue
resync catalog;
# Backup the database
run {
 allocate channel 'dev0' type 'SBT_TAPE';
 allocate channel 'dev1' type 'SBT_TAPE';
 backup
  incremental level 2
  cumulative
  tag Full_Oracle_Backup
  filesperset 5
  format '%d_%t_%s_%p.dbf'
  database
  ;
 backup
  cumulative
  filesperset 5
  format '%d_%t_%s_%p.cf'
  current controlfile
  ;
 sql 'alter system archive log current';
 backup
  filesperset 10
  archivelog all format '%d_%t_%s_%p.arch';

 release channel dev0;
 release channel dev1;
}

A level N incremental backup backs up blocks that have changed since the most recent incremental backup at level N or less.
 

1. Level 0 - the basis of the incremental backup strategy
run {
allocate channel dev1 type disk;
backup
incremental level 0
filesperset 4
format '/oracle/backups/sunday_level0_%t'
(database);
release channel dev1;
}
      Line#
         4: Level 0 backup - backups of level > 0 can be applied to this
         5: Specifies maximum files in the backupset

A list of the database backupsets will show the above backup. The 'type' column is marked 'Incremental'; the 'LV' column shows '0'.

2. Example backup strategy using incremental backups
A typical incremental backup cycle would be as follows:
        o. Sun night - level 0 backup performed
        o. Mon night - level 2 backup performed
        o. Tue night - level 2 backup performed
        o. Wed night - level 2 backup performed
        o. Thu night - level 1 backup performed
        o. Fri night - level 2 backup performed
        o. Sat night - level 2 backup performed

If the database suffered a failure on Sat morning and this resulted in a restore operation, RMAN could recover to the point of failure by restoring the backups from Sunday, Thursday, and Friday. This is because Thursdays level 1 backup contains all changes since Sunday, and Friday's level 2 backup contains all changes since Thursday. Whether the database could be completely recovered would depend on whether archive logging is enabled.
 
Another example:
Create a suitable backup strategy

A proper plan must be made to protect your production database against potential failures based on the service requirements and the database size.  It may not be acceptable to lose any data in a production environment if a disk failure damages some of the files that constitute a database.  Therefore, the database may have to be operated in ARCHIVELOG mode, ideally with a multiplexed online redo log.  The following plan is an example.

Backup    Sunday    Monday     Tuesday    Wednesday    Thursday    Friday    Saturday
Level 0   8:00 PM                       
Level 1                                   2:00 AM                  2:00 AM    
Level 2                        2:00 AM                 2:00 AM               2:00 AM

Level 0 backup: Backs up the whole database.  This backup is part of the incremental strategy.  It means that it can have incremental backups of levels greater than 0 applied to it. 

Level 1 backup: This backup will only back up the blocks which have been modified since the last level 0 backup was performed.  Otherwise it is exactly the same backup as the level 0 above

Level 2 backup: The backup will only back up the blocks which have been modified since the last level 0 or 1 backup was performed.  Otherwise it is exactly the same backup as the level 0 or 1 backups above. 
 
The following scripts describe how to do the three levels backups.

# Incremental level 0 (whole) database backup
# The control file is automatically included each time file 1 of the
# system tablespace is backed up.
replace script backup_db_level_0_disk {
  execute script alloc_all_disks;
  set maxcorrupt for datafile 1 to 0;
  backup
    incremental level 0
    tag backup_db_level_0
    # The skip inaccessible clause ensures the backup will continue
    # if any of the datafiles are inaccessible.
    skip inaccessible
    filesperset 9
    format '/bkup/SID/%d_0_t%t_s%s_p%p'
    (database);
  execute script rel_all_disks;
  sql 'alter system archive log current'; 
  execute script backup_al_all_disk;
}


# Incremental level 1 database backup
replace script backup_db_level_1_disk {
execute script alloc_all_disks;
  set maxcorrupt for datafile 1 to 0;
  backup
    incremental level 1
    # You can use the 'skip readonly' and 'skip offline' clauses
    # to omit backing up these files if you have already had
    # valid backups of them.
    skip offline
    skip readonly
    skip inaccessible
    tag backup_db_level_1
    filesperset 9
    format '/bkup/SID/%d_1_t%t_s%s_p%p'
    (database);
  execute script rel_all_disks;
  sql 'alter system archive log current'; 
  execute script backup_al_all_disk;
}


# Incremental level 2 database backup
replace script backup_db_level_2_disk {
  execute script alloc_all_disks;
  set maxcorrupt for datafile 1 to 0;
  backup
    incremental level 2
    skip offline
    skip readonly
    skip inaccessible
    tag backup_db_level_2
    filesperset 9
    format '/bkup/SID/%d_2_t%t_s%s_p%p'
    (database);
  execute script rel_all_disks;
  sql 'alter system archive log current'; 
  execute script backup_al_all_disk;
}

Cumulative incremental backups
 A cumulative incremental backup backs up all blocks that have changed since the the most recent incremental backup at level N-1 or less (contrast with non-cumulative incremental backups that backup blocks that have changed since the the most recent incremental backup at level N or less). This means that more work is done in performing the backup (duplication of backup effort), but time may be saved when restoring (potentially fewer backupsets to restore).
run {
allocate channel dev01 type disk;
backup incremental level 1 cumulative database;
release channel dev01;
}
 

Scripting in RMAN
You can integrate RMAN into operating-system command scripts, either as a call to RMAN with a command file or with in-line RMAN scripts.
It is very easy to create and replace stored scripts with RMAN. E.g.

create script alloc_disk {
# Allocates one disk
allocate channel dev1 type disk;
setlimit channel dev1 kbytes 2097150 maxopenfiles 32 readrate 200;
}

replace script rel_disk {
# releases disk
release channel dev1;
}

replace script backup_db_full {
# Performs a complete backup
execute script alloc_disk;
 backup
 .....<backup commands here>
execute script rel_disk;
}

The first 2 scripts allocate and deallocate channels respectively. The alloc_disk script additionally specifies the maximum size of backup pieces created on this channel (kbytes), the maximum number of input files that a backup will have open (maxopenfiles), and the maximum number of buffers per second which will be read from each of the input datafiles.
The 3rd script calls the previously stored scripts either side of performing a backup.
Example of executing a stored script:

run { execute script backup_db_full; }

    It is possible to create a job command list in a flat file and call that script from the O/S command line as an RMAN option. E.g. to call scripts stored in a file called 'weekly_cold_backup':
    % rman <other RMAN options> cmdfile weekly_cold_backup

This UNIX script, for example, shows how to work around one of RMAN's current shortcomings - its inability to receive variables.

This script shows you how to pass variables from a UNIX Korn-shell script to RMAN.

#!/bin/ksh
# Back up single tablespace to disk or tape
# Process command-line args
TargetConnStr=$1
RcvcatConnStr=$2
TapeOrDisk=$3
tablespace=$4
# need to do some parameter validation here!
${ORACLE_HOME}/bin/rman << EOF
connect target ${TargetConnStr}
connect rcvcat ${RcvcatConnstr}
run {
execute script alloc_${TapeOrDisk}_channel;
backup tablespace ${tablespace}
format '${tablespace}_%u';
execute script rel_${TapeOrDisk}_channel;
}
exit
EOF
if [ $? -ne 0 ]
then
 echo "RMAN failed to backup ${tablespace}"
else
 echo "${tablespace} backed up OK"
fi
 

You can store RMAN commands in scripts and hold them in the recovery catalog itself. The following command creates a script for backing up the ACCT tablespace:

replace script backup_acct {
allocate channel c1 type disk;
backup tablespace acct
format 'z:\backups\acct%u';
release channel c1;
}
 

To execute this script, simply issue the call to run the backup_acct procedure, as shown in the RMAN run command:
run { execute script backup_acct;}

You may want to create a set of standard scripts for routine tasks. For example, you may have a set of scripts that allocate channels (allocate_1_disk, allocate_3_ tapes, and so on). You can call these scripts from within other scripts.
Alternatively, you can write the RMAN commands to operating-system command files and invoke them either from within RMAN, using the @filename notation, or on the RMAN command line, using the cmdfile= argument.
 

Managing Archive Logs
Backing up and keeping track of archive log files has always been challenging for DBAs. How do you know whether ARCH has finished writing to a log? How soon should you delete the archive logs after backups? Do you restore the archive logs all at once or one at a time when you need to recover - and where do you restore them? RMAN provides several features to assist you.
You back up archived redo logs in much the same way you do datafiles. Use the RMAN backup command and specify criteria indicating the files to be backed up. Because RMAN works with the contents of the control files rather than the operating-system directory, it knows which log files are still being written by ARCH. The following script backs up all complete archived redo logs in the log_arch_dest directory. (Note that you can use RMAN and SQL statements in a single piece of code.)

replace script archlog_all {
allocate channel t1 type sbt_tape;
sql "alter system archive log current";
backup archivelog all format 'arch_%u';
release channel t1;
}
 

You can be more selective when deciding which logs to back up, and you can delete log files once they've been successfully backed up. Selection criteria can include creation date/time, sequence number, or thread number. The following example backs up and then deletes all logs generated more than 48 hours ago:

replace script archlog_old {
allocate channel t1 type sbt_tape;
backup archivelog 
# all
until time 'sysdate-2'
all format 'arch_%u'
delete input;
release channel t1;
}
 
 

Recovering from Failure (with Examples)
Backing up the database is the easy part. The real challenge begins when the "ORA-01113 file needs recovery" message flashes on your console. Fortunately, using RMAN for recovery is as simple as backing up in the first place. Just as it takes only a few commands to back up the database, it takes just a few to restore it:

run {
allocate channel t1 type 'sbt_tape';
restore database;
recover database;
release channel t1;
}

When you invoke this script, RMAN identifies the most appropriate backup from which to restore, requests the required tape volumes, and restores the data files. If an incremental-backup strategy is in place, then it will restore a combination of levels 0 and above. RMAN then determines which archive logs are required for rolling forward to the current point in time, requests that they are restored from tape, and recovers the database.
In theory, this may be the only script you'll ever need for performing full-database recoveries. In practice, however, you will probably want a little more flexibility and control.
 
 

More Recovery Examples

1. Database open, datafile deleted
Datafile has been deleted from a running database. There are two methods of open database recovery: restore the datafile and recover either the datafile, or the tablespace. The next two examples show both methods:

(a) Datafile recovery
run {
allocate channel dev1 type disk;
sql "alter tablespace users offline immediate";
restore datafile 4;
recover datafile 4;
sql "alter tablespace users online";
release channel dev1;
}

(b) Tablespace recovery
run {
allocate channel dev1 type disk;
sql "alter tablespace users offline immediate";
restore tablespace users;
recover tablespace users;
sql "alter tablespace users online";
release channel dev1;
}

      Note that if it is the system tablespace datafiles to be restored, the database must be closed. It is not possible to offline the system tablespace.

2. Complete restore (lost online redo) and rollforward - database closed
run {
allocate channel dev1 type disk;
set until logseq=105 thread=1;
restore controlfile to '/oracle/dbs/ctrltargdb.ctl';
replicate controlfile from '/oracle/dbs/ctrltargdb.ctl';
restore database;
sql "alter database mount";
recover database;
sql "alter database open resetlogs";
release channel dev1;
}

Notes:
- The 'set until' command dictates at which log sequence recovery will stop. It is critical that this command is issued BEFORE datafiles are restored, otherwise RMAN will attempt to restore the most recent set of datafiles, which could be ahead of the specified log
- The 'replicate controlfile' copies the restored controlfile to the controlfiles referenced in init.ora
- Because the database is opened with resetlogs, it is necessary to register the new incarnation of the database with the RESET DATABASE command. As with v7, it is important to take a full backup of the database immediately after a resetlogs
 

3. Restore of a subset of datafiles, complete recovery
run {
allocate channel dev1 type disk;
sql "alter database mount";
restore datafile 2;
restore datafile 3;
restore archivelog all;
recover database;
sql "alter database open";
release channel dev1;
}
 

4.Performs a full-database recovery to a previous point in time:
run {
allocate channel t1 type 'sbt_tape';
allocate channel t2 type 'sbt_tape';
set until time '28-MAY-1999 12:32';
restore database;
recover database;
sql "alter database open resetlogs";
release channel t1;
release channel t2;
}
reset database;

Note: the date format in the SET UNTIL clause must match the date format of the NLS_DATE_FORMAT environment setting. If you have opened the database with RESETLOGS, you must register this fact in the recovery catalog, using the RESET DATABASE; command.
  

5. In this example the database is offline to restore the controlfile, we still have the online logs so complete recovery is possible:
% set ORACLE_SID=PROD
svrmgrl> connect internal
svrmgrl> shutdown abort;
svrmgrl> startup nomount;

rman target / rcvcat rman/rman@RMANDB

run { 
allocate channel t1 type 'sbt_tape';
allocate channel d1 type disk;
restore controlfile to 'd:\target\ctl1targ.ora';
replicate controlfile from 'd:\target\ctl1targ.ora';
restore database;
sql "alter database mount";
recover database;
sql "alter database open";
}


6.In this example we'll restore the system tablespace
% set ORACLE_SID=PROD
svrmgrl> connect internal
svrmgrl> shutdown immediate;
svrmgrl> startup mount;

rman target / rcvcat rman/rman@RMANDB

run { 
allocate channel d1 type disk;
restore tablespace SYSTEM;
recover database;
sql "alter database open";
}


7. Restoring the database to a new server
1. You must have the Oracle binaries installed on the node to restore to and have updated the sqlnet information on that node so Recovery Manager can connect to the new instance started at nomount. We will perform time based recovery.

Note:  If you are restoring from a disk backupset.  You must copy the backupsets that will be used for restore to the new node and they must reside in the same image path used on the original node.  Rule of thumb for disk backups.  Keep the path simple so it is easy to duplicate the path if needed on a new node.

% set ORACLE_SID=PROD
svrmgrl> connect internal
svrmgrl> shutdown abort;
svrmgrl> startup nomount;

rman target / rcvcat rman/rman@RMANDB

run { 
set until time 'Jul 01 1999 00:05:00';
allocate channel d1 type disk;
set newname for datafile '/u04/oracle/prod/sys1prod.dbf' to '/u02/oracle/prod/sys1prod.dbf';
set newname for datafile '/u04/oracle/prod/usr1prod.dbf' to '/u02/oracle/prod/usr1prod.dbf';
set newname for datafile '/u04/oracle/prod/tmp1prod.dbf' to '/u02/oracle/prod/tmp1prod.dbf';
set newname for datafile '/u04/oracle/prod/rbs1prod.dbf' to '/u02/oracle/prod/rbs1prod.dbf';
set newname for datafile '/u04/oracle/prod/indx1prod.dbf' to '/u02/oracle/prod/indx1prod.dbf';
restore controlfile to '/u02/oracle/prod/ctl1prod.ora';
replicate controlfile from '/u02/oracle/prod/ctl1prod.ora';
restore database;
sql "alter database mount";
switch datafile all;
recover database;
sql "alter database open resetlogs";
release channel d1;
}

Note: Switch in this command updates the restore controlfile with the new datafile path name just like "alter database datafile rename.."
Since we are restoring to a new server we will not have the use of the online redo logs so complete recovery is not possible and the database is open with the resetlogs option.

 

Resource Utilization
RMAN has several options that allow you to control its use of system resources. Predominantly, these govern performance and space utilization - for example, you can increase backup and recovery throughput by increasing the level of parallelism. Backup speed is governed by a combination of the number of channels allocated and the files per set (filesperset) parameter. Recovery throughput is related to the number of channels and the number of distinct backup sets that RMAN must read in order to perform the recovery.

Wherever possible, RMAN attempts to capitalize on the streaming capabilities of tape drives by writing several data files simultaneously to a single backup set. This is all well and good but, as with other aspects of the Oracle product set, RMAN can potentially take more than its fair share of the resources on offer. Other users (potentially end users of an application) may suffer as a result. Because when you're using RMAN, you don't generate redo information (as you would without RMAN) during hot backups, the desire to finish the backup ASAP is not quite so strong. RMAN allows you to restrict, or "throttle," its usage of resources by setting the readrate parameter on a per-channel basis, using the setlimit statement - for example:
run {
allocate channel t1 type 'sbt_tape';
setlimit channel t1 readrate 30;
backup......

The readrate value is the maximum number of buffers per second. The buffer size is the result of multiplying the init.ora settings DB_FILE_DIRECT_IO_COUNT and DB_BLOCK_SIZE. Setting the readrate value inevitably involves a level of trial and error and is application- and hardware-specific.
In addition, setting the LARGE_POOL_SIZE init.ora parameter can help optimize I/O buffering during RMAN backup operations.
When backing up very large databases, you run the risk that RMAN will create a single backup set that is too large for the underlying disk or tape subsystem to recognize as a single file. In such cases, you need to split the backup set into several backup pieces. The kbytes parameter of the setlimit statement specifies the threshold for splitting the backup set. For example, the following statement limits the size of any backup pieces produced through channel t1 to a size of 1GB:
run {
allocate channel t1 type 'sbt_tape';
setlimit channel t1 kbytes 1048576;
backup...
 
 

Report & list commands
Lists
      The list command queries the recovery catalog to produce a formatted listing of contents. E.g.
      list backup;  # lists backup sets, backup pieces, and proxy copies

To restrict by object, use list copy or list backup with the of listObjList condition. For example, enter:
list backup of database;                 # lists backups of all files in database
list copy of datafile '/oracle/dbs/tbs_1.f';     # lists copy of specified datafile
list backup of tablespace SYSTEM;     # lists all backups of SYSTEM tablespace
list copy of archivelog all;              # lists all archived redo logs and copies of logs
list backup of controlfile;                # lists all control file backups

You can also restrict your search by specifying a combination of tag, device type, filename pattern, or time options. For example, enter:
list backup tag 'weekly_full_db_backup';    # by tag
list copy of datafile '/oracle/dbs/tbs_1.f' type 'sbt_tape';   # by type
list backup like '/oracle/backup/tbs_4%';  # by filename pattern
list backup of archivelog until time 'SYSDATE-30'; # by time
list copy of datafile 2 completed between '10-DEC-1999' and '17-DEC-1999'; i
 

Report objects that needs backup:
1. If necessary, issue crosscheck commands to update the status of backups and change ... crosscheck commands to update the status of image copies (if you want to specify image copies by primary key, issue a list command to obtain the keys). Following is a possible crosscheck session:
# must allocate maintenance channel for crosscheck
allocate channel for maintenance type disk;
crosscheck backup;  # crosschecks all backups
change datafile copy 100,101,102,103,104,105,106,107 crosscheck; # specified by key
change archivelog copy 50,51,52,53,54 crosscheck;  # specified by key
release channel;

2. Use the need backup option to identify which datafiles need a new backup, restricting the report by a threshold number of days or incremental backups. RMAN considers any backups older than the days parameter value as needing a new backup because backups require days worth of archived redo logs for recovery.
For example, enter:
report need backup days = 7 database;  # needs at least 7 days of logs to recover
report need backup days = 30 tablespace system;
report need backup days = 14 datafile '/oracle/dbs/tbs_5.f';

You can also specify the incremental parameter. If complete recovery of a datafile requires more than the specified number of incremental backups, then RMAN considers it in need of a new backup. For example, enter:
report need backup incremental = 1 database;
report need backup incremental = 3 tablespace system;
report need backup incremental = 5 datafile '/oracle/dbs/tbs_5.f';
 

To report on backups that are obsolete:
1. Perform crosscheck if neccesary (see previous step 1).
2. Use the obsolete option to identify which backups are obsolete because they are no longer needed for recovery. The redundancy parameter specifies the minimum level of redundancy considered necessary for a backup or copy to be obsolete. If you do not specify the parameter, redundancy defaults to 1.
A datafile copy is obsolete if at least integer more recent backups of this file exist; a datafile backup set is obsolete if at least integer more recent backups or image copies of each file contained in the backup set exist. For example, enter:
# lists backups or copies that have at least 2 more recent backups or copies
report obsolete redundancy = 2;

Use the untilClause to use make the redundancy check for backups sets or copies that are more recent, but not later than the specified time, SCN, or log sequence number:
# obsolete if there are at least 2 copies/backups that are no more than 2 weeks old
report obsolete redundancy = 2 until time 'SYSDATE-14';
report obsolete until scn 1000;
report obsolete redundancy = 3 until logseq = 121 thread = 1;

3. Use the orphan option to list which backups and copies are unusable because they belong to a incarnation that is not a direct predecessor of the current incarnation:
report obsolete orphan;

4. Examine the report and delete those backups that are obsolete.
report obsolete;
 

To report on backups that are unrecoverable
report unrecoverable database; # examines all datafiles
 

List and Report Scenarios
Following are some examples of list and report generation:

  • Makings Lists of Backups and Copies

  • Using Lists to Determine Obsolete Backups and Copies
  • Reporting Datafiles Needing Backups
  • Reporting Unrecoverable Datafiles
  • Reporting Obsolete Backups and Copies
  • Manually Deleting Obsolete Backups and Copies
  • Deleting Obsolete Backups and Copies Using a UNIX Shell Script
  • Generating Historical Reports of Database Schema
  • Listing Database Incarnations
  • Reporting Deleted Backups and Copies

 
Makings Lists of Backups and Copies
Use the list command to query the contents of the recovery catalog or the target database control file if no recovery catalog is used. You can use several different parameters to qualify your lists.

The following example lists all backups of datafiles in tablespace TBS_1 that were made after November 1, 1999:
list backup of tablespace tbs_1 completed before 'Nov 1 1999 00:00:00';

The following example lists all backup sets or proxy copies on media management devices:
list backup of database device type 'sbt_tape';

The following example lists all copies of datafile 2 using the tag weekly_df2__copy that are in the copy sub-directory:
list copy of datafile 2 tag weekly_df2_copy like '/copy/%';

Using Lists to Determine Obsolete Backups and Copies
Use the list command to determine which copies and backups can be deleted. For example, if you created a full backup of the database on November 2, and you know you will not need to recover the database to an earlier date, then the backups and image copies listed in the following report can be deleted:
list backup of database completed before 'Nov 1 1999 00:00:00';
list copy completed before 'Nov 1 1999 00:00:00';

Reporting Datafiles Needing Backups
The following command reports all datafiles in the database that require the application of three or more incremental backups to be recovered to their current state:
report need backup incremental 3 database;

The following command reports all datafiles from tablespace SYSTEM that have not had a full or incremental backup in five or more days:
report need backup days 5 tablespace system;

The following command reports which of datafiles 1 - 5 need backups because they do not have two or more backups or copies stored on tape:
report need backup redundancy 2 datafile 1,2,3,4,5 device type 'sbt_tape';

Reporting Unrecoverable Datafiles
The following example reports on all datafiles on tape that need a new backup because they contain unlogged changes that were made after the last full or incremental backup.
report unrecoverable database device type 'sbt_tape';

Reporting Obsolete Backups and Copies
The following command reports all backups and copies on disk that are obsolete because three more recent backups or copies are already available:
report obsolete redundancy 3 device type disk;

The following command reports all backups on tape that are obsolete because at least two backups already exist that were made no more than one week ago:
report obsolete redundancy 2 until time 'SYSDATE-7' device type 'sbt_tape';

The following command reports which datafiles are obsolete because they belong to a database incarnation that is not a direct predecessor of the current incarnation:
report obsolete orphan;

Manually Deleting Obsolete Backups and Copies
In this scenario, assume that you want to delete the following:

  • Datafile copies for which there are at least two more recent copies.

  • Datafile backups for which there are at least two more recent backups or image copies of each datafile contained in the backup set.

1. Generate a report with redundancy set to 2:
report obsolete redundancy 2;
Report of obsolete backups and copies
Type                 Recid  Stamp     Filename
-------------------- ------ --------- --------------------------
Datafile Copy        23     345392880 /oracle/dbs/tbs_01.copy
Datafile Copy        22     345392456 /oracle/dbs/tbs_01_copy.f
Backup Set           31     345552065
Backup Piece         31     345552061 /oracle/dbs/0va9hd5o_1_1
Backup Set           23     345399397
Backup Piece         23     345399391 /oracle/dbs/0ma9co2p_1_1
Backup Set           20     345397468
Backup Piece         20     345397464 /oracle/dbs/0ka9cm6l_1_1

2. Issue change ... delete commands for the copies and backups to delete them and remove their repository records. Use the filenames or issue a list command to obtain the primary keys:
allocate channel for delete type disk;
change backuppiece '/oracle/dbs/0va9hd5o_1_1', '/oracle/dbs/0ma9co2p_1_1',
   '/oracle/dbs/0ka9cm6l_1_1' delete;
change datafilecopy '/oracle/dbs/tbs_01.copy', '/oracle/dbs/tbs_01_copy.f'
   delete;
release channel;

Deleting Obsolete Backups and Copies Using a UNIX Shell Script
Oracle provides the $ORACLE_HOME/rdbms/demo/rman1.sh UNIX script to automate deletion of obsolete backups and copies. The script uses sed and grep commands to process the output of the RMAN report obsolete command and constructs an RMAN command file containing the necessary change ... delete commands. This script does not require the use of a recovery catalog.
You can edit the report obsolete commands in the script as desired: the script uses the default value for report obsolete for both disk and tape devices. The output of the commands are stored in deleted.log.
1. Change into the $ORACLE_HOME/rdbms/demo directory and run the following shell script:
% rman1.sh

2. If desired, check deleted.log to see the command output.
 

Generating Historical Reports of Database Schema
The following commands reports the database schema in the present, a week ago, two weeks ago, and a month ago:
report schema;
report schema at time 'SYSDATE-7';
report schema at time "TO_DATE('12/20/98','MM/DD/YY')";

Listing Database Incarnations
Every time that you perform a RESETLOGS operation on a database, you create a new incarnation. This example lists all database incarnation of PROD1 registered in the recovery catalog:
list incarnation of database prod1;
 
 

Duplicate Database using RMAN

   1. Run the script below on the database in which you plan to duplicate to generate the new paths for the data files. This example assumes you have enough disk space on one mount point. However, you can easily change some of the mount points in VI to point another location or modify the script to suit your specific needs.
select 'set auxname for datafile ' || file# || ' to ' || '''/ora<XXX>/oradata/<SID>' ||
substr(name, instr(name, '/', -1)) || ''';'
from v$datafile
/
 
   2. Create the RMAN script to perform the duplicate. The number of log groups must match the source database. You may increase or decrease the number of log members as needed. You must also insert your auxname commands before the rman run block. If you are not doing a point in time dupilcate you can remove the optional line "set until time ...".
-------------------------------------------------------------------------------------------------------------------------
connect target sys/<password>@<sid>
connect auxiliary /
connect catalog rman/<password>@<sid>

<Insert data file rename commands from step 1>

run {
-- Optional Line -- set until time '2001-12-18:08:30:00';
allocate auxiliary channel a0 type <DISK/SBT_TAPE>;
allocate auxiliary channel a1 type <DISK/SBT_TAPE>;
allocate auxiliary channel a2 type <DISK/SBT_TAPE>;
allocate auxiliary channel a3 type <DISK/SBT_TAPE>;
allocate auxiliary channel a4 type <DISK/SBT_TAPE>;
allocate auxiliary channel a5 type <DISK/SBT_TAPE>;
allocate auxiliary channel a6 type <DISK/SBT_TAPE>;
allocate auxiliary channel a7 type <DISK/SBT_TAPE>;
allocate auxiliary channel a8 type <DISK/SBT_TAPE>;
allocate auxiliary channel a9 type <DISK/SBT_TAPE>;
allocate auxiliary channel a10 type <DISK/SBT_TAPE>;
allocate auxiliary channel a11 type <DISK/SBT_TAPE>;
allocate auxiliary channel a12 type <DISK/SBT_TAPE>;
allocate auxiliary channel a13 type <DISK/SBT_TAPE>;
allocate auxiliary channel a14 type <DISK/SBT_TAPE>;
allocate auxiliary channel a15 type <DISK/SBT_TAPE>;
duplicate target database to <NEW_SID>
logfile
group 1 ('/ora<XXX>/oradata/<SID>/redo01_01.rdo') size 200M reuse,
group 2 ('/ora<XXX>/oradata/<SID>/redo02_01.rdo') size 200M reuse,
group 3 ('/ora<XXX>/oradata/<SID>/redo03_01.rdo') size 200M reuse,
group 4 ('/ora<XXX>/oradata/<SID>/redo04_01.rdo') size 200M reuse;
}
 
   3. Log into the server where you will be duplicating the database.

   4. If you are using RMAN duplicate to refresh a TEST or Development database first. Shutdown the instance if it already running.

   5. You will need to recreate/create the password file on for the target database.
You do not have to delete the data files of the existing database if one exists (applicable to only a refresh). It would be a good idea to do so if you are not sure that the source and the target you are duplicating too has the same name and number of data files. If not you will have unused data files on the duplicate database, wasting server resources.
 
   6. STARTUP NOMOUNT the Target database.
Note: You must have an adequately sized SORT_AREA_SIZE for RMAN to do its quering against the auxiliary database since it will not have any temp space available in NOMOUNT.
 
   7. For the recovery part of the duplicate you will need the archive logs of the source database available. The archive logs can be copied over from the production machine to the target machine as long as the archive logs are placed in the exact path as on the production machine. Otherwise, you must do an archive log backup via Rman to either disk or tape. Remember that if you use a combination of Disk and Tape backups to allocate channels for both tape and disk in your script.

   8. If you are doing a duplicate from a point in time you must set the NLS environmental variables. If not you can skip this step.
C- Shell
setenv NLS_LANG american
setenv NLS_DATE_FORMAT YYYY-MM-DD:HH24:MI:SS

Korn Shell
export NLS_LANG= American
export NLS_DATE_FORMAT= YYYY-MM-DD:HH24:MI:SS
 
   9. Run the completed duplicate script
$ rman @runscript
Note: If for any reason the duplicate fails due to missing archive log file or any other reason, it is still possible to finish the recovery manually, however the DBID will not be reset. Also, If you are duplicating a Multimaster replicated production database be sure to set job_queue_processes=0 to avoid pushing replication transactions to your production databases.

  10. Make sure your global name is set correctly fro your target database because RMAN does not set this for you. It is also important to note that if you change the domain you must rebuild any database links.
SQL> SELECT * from global_name;
SQL> ALTER database rename global_name to <new name>;
 
  11. Remeber to add any temporary files for any tablespaces that are using them.
  12. Change any passwords for the new database.

 

Top 10 Tips for Using RMAN

  1. Always use a recovery catalog. Place your catalog on totally separate hardware from your target database. If you can, house it in a separate geographical location. Make regular backups of your catalog. Resynchronize the catalog at least as frequently as the control file recycles backup info (governed by the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter). Always resynchronize the catalog when you have changed a database's physical layout.

  2. Before starting RMAN, set the NLS_LANG and NLS_DATE_FORMAT environment variables to the required format. These are used for performing point-in-time recoveries and also for formatting output from the LIST and REPORT commands.
  3. When starting RMAN, remember to issue the TARGET and RCVCAT connect strings from within RMAN rather than on the command line in order to reduce the chances of "process sniffers" being able to identify key user-name/password pairings.
  4. If your operating system or tape-management system has limits on maximum file sizes make sure you reference these in your RMAN backup scripts, using the kbytes parameter of setlimit; otherwise, you risk filling a backup set.
  5. Let RMAN work for you. There is no point letting RMAN back up your database and not your archived redo logs. Recovery operations are a whole lot easier if RMAN knows what has gone on previously.
  6. Choose your filesperset and parallelism settings carefully, to minimize the time for backing up and recovering your database.
  7. Don't let RMAN "eat your resources." If necessary, use readrate to throttle the I/O requests and relegate RMAN in your processing-priority list.
  8. Remember, RMAN doesn't back up everything. You still need facilities for securing initialization, configuration, password, and other operating-system-specific files that are part of your Oracle-database installation.
  9. Keep at least a couple of days' worth of archived logs on disk. You can still back them up to tape and let RMAN delete only those more than 48 hours old.
  10. Keep it simple. RMAN does 90 percent of the job for you. Do not overcomplicate the remaining 10 percent with elaborate, platform-specific scripts. And, as always, remember to test your recovery scenarios in a safe environment first.

 
Script reports RMAN backup statistics direct from the recovery catalog.
set pages 200

col datafileMb format 9,999,999 heading "Datafile Size Mb"
col backedupMb format 9,999,999 heading "Backuped Size Mb"

break on report
compute sum of datafileMb on report
compute sum of backedupMb on report

show user

SELECT db_name,MIN(completion_time)
FROM rc_backup_datafile
GROUP BY db_name;

REM top running backups

select rownum as rank
       ,name
       , DECODE(backup_type,'D','Full','I','Incremental'
           ,'L','Archive Log',backup_type) backup_type
       ,round(max_secs/60) mins
FROM ( select name , backup_type , max(elapsed_seconds)  max_secs
     from rc_backup_set bs, rc_database d
     where bs.db_key = d.db_key
     group by name, backup_type
     order by max(elapsed_seconds) desc
     )
WHERE rownum <= 10

SELECT db_name
   , DECODE(status,'A','Available'
                   ,'D','Deleted'
                   ,'O','Unusable'
                   ,status)        status
   , COUNT(*)
from rc_backup_datafile
GROUP BY db_name,status

SELECT db_name
   , DECODE(backup_type, 'D','Full','Incremental') backup_type
   , SUM(datafile_blocks*block_size) /1024/1024 datafileMb
   , SUM(blocks*block_size) /1024/1024 backedupMb
FROM rc_backup_datafile
GROUP BY db_name
   , DECODE(backup_type, 'D','Full','Incremental');
 
rem -----------------------------------------------------------------------
rem Filename:   rmanlist.sql
rem Purpose:    List backups registered in RMAN catalog database
rem Author:     Diego Pafumi
rem -----------------------------------------------------------------------
connect rman/rman
col media   format a8
col tag     format a12 trunc
col minutes format 990

select d.name, p.tag, p.media,
       s.incremental_level "LEVEL",
       to_char(s.start_time, 'DD-MON-YY HH24:MI') start_time,
       s.elapsed_seconds/60 "MINUTES"
from  RC_DATABASE d, RC_BACKUP_PIECE p, RC_BACKUP_SET s
where d.name            = 'WH'
  and s.start_time      > '04-MAY-02'
  and s.completion_time < '06-MAY-02'
  and p.backup_type     = 'D' -- D=Database, L=Log
  and d.db_key = p.db_key
  and s.db_key = p.db_key
  and p.bs_key = s.bs_key
/


rem -----------------------------------------------------------------------
rem Filename:   rmanstat.sql
rem Purpose:    List completed RMAN backups for the last 24-hours
rem             (use info from Dictionary - Control File Views)
rem Date:    12-Feb-2002
rem Author:     Diego Pafumi
rem -----------------------------------------------------------------------

tti "RMAN Backups for last 24-hours"

col type     format a4
col handle   format a35 trunc
col file#    format 9999
col duration format a9

select decode(BACKUP_TYPE, 'L', 'ARCH', 'D', 'DB', 'I', 'INC',
              'Unknown type='||BACKUP_TYPE) TYPE,
       to_char(a.start_time, 'DDMON HH24:MI') start_time,
       to_char(a.elapsed_seconds/60, '99.9')||' Min' DURATION,
       substr(handle, -35) handle,
       nvl(d.file#, l.sequence#) file#, nvl(d.blocks, l.blocks) blocks
from   SYS.V_$BACKUP_SET a, SYS.V_$BACKUP_PIECE b,
       SYS.V_$BACKUP_DATAFILE d, SYS.V_$BACKUP_REDOLOG l
where  a.start_time between sysdate-1 and sysdate
  and  a.SET_STAMP = b.SET_STAMP
  and  a.SET_STAMP = d.SET_STAMP(+)
  and  a.SET_STAMP = l.SET_STAMP(+)
order  by start_time, file#
/



rem -----------------------------------------------------------------------
rem Filename:   rman_run.sql
rem Purpose:    Monitor RMAN status, while a backup is running
rem Date:    12-Nov-2001
rem Author:     Diego Pafumi
rem -----------------------------------------------------------------------

prompt RMAN Backup Status:

SELECT to_char(start_time,'DD-MON-YY HH24:MI') "BACKUP STARTED",
       sofar, totalwork,
       elapsed_seconds/60 "ELAPSE (Min)",
       round(sofar/totalwork*100,2) "Complete%"
FROM   sys.v_$session_longops
WHERE  compnam = 'dbms_backup_restore'
/


USE UNIX SCRIPTS TO CLEAN UP OLD TRACE AND LOG FILES
#!/bin/ksh
# Cleanup archive logs more than 7 days old
find /u01/app/oracle/admin/mysid/arch/arch_mysid*.arc -ctime +7 -exec rm {} ;


The following script loops through each database, visiting the bdump,
udump, and audit directories, and removes all files that are more than two
weeks old.
#!/bin/ksh
for ORACLE_SID in `cat /etc/oratab|egrep ':N|:Y'|grep -v \*|cut -f1-d':'`
do
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -d":" -f2`
DBA=`echo $ORACLE_HOME | sed -e 's:/product/.*::g'`/admin
find $DBA/$ORACLE_SID/bdump -name \*.trc -mtime +14 -exec rm {} \;
$DBA/$ORACLE_SID/udump -name \*.trc -mtime +14 -exec rm {} \;
find $ORACLE_HOME/rdbms/audit -name \*.aud -mtime +14 -exec rm {} \;
done

Unregister a DB from RMAN
First we start up RMAN with a connection to the catalog and the target, making a note of the DBID in the banner:
C:\>rman catalog=rman/rman@rman target=sys/password@prod
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
connected to target database: W2K1 (DBID=1487421514)
connected to recovery catalog database

Next we list and delete any backupsets recorded in the repository:

RMAN> LIST BACKUP SUMMARY;
RMAN> DELETE BACKUP DEVICE TYPE SBT;
RMAN> DELETE BACKUP DEVICE TYPE DISK;
Next we connect to the RMAN catalog owner using SQL*Plus and issue the following statement:

SQL> CONNECT rman/rman@RMAN
SELECT db_key, db_id
  FROM   db
  WHERE  db_id = 1487421514;

    DB_KEY      DB_ID
---------- ----------
         1 1487421514

The resulting key and id can then be used to unregister the database:

SQL> EXECUTE dbms_rcvcat.unregisterdatabase(1, 1487421514);

Here is a way to delete expired backup sets and archived logs in Oracle 8i. You still have to execute the commands below manually, since Oracle does not provide functionality to execute them from a stored scripts.

Scripts were created and tested on Oracle 8.1.6 and 8.1.7 Enterprise Edition database instances. I execute them once a month, to remove the expired backup references from my RMAN catalog.


-------------- DB BACKUP -----------------------------
# bkup_db_crosscheck.rcv
#
# Crosscheck db logs backup and keep one week old in the repoitory
#

connect rcvcat rman/rman@rman;
connect target ' / as sysdba';

allocate channel for maintenance type disk;
crosscheck backup of database completed between 'sysdate - 30' and 'sysdate - 7';
delete expired backup;
release channel;
exit;


------------ ARCHIVED LOGS BACKUP ----------------
# bkup_arch_logs_crosscheck.rcv
#
# Crosscheck archived logs backup and keep one week old in the repoitory
#

connect rcvcat rman/rman@rman01;
connect target ' / as sysdba';

allocate channel for maintenance type disk;
crosscheck backup of archivelog completed between 'sysdate - 30' and 'sysdate - 7';
delete expired backup;
release channel;
exit;