10 Using RMAN to Back Up and Restore Files

10.1.1 Using Disk as Cache for Tape Backup

The following instructions assume the flash recovery area is configured (as described in Section 5.2.3) and other RMAN persistent configurations are set. Perform the following steps:

1. On the primary database, issue the following RMAN commands to make a current backup of the control file and SPFILE, and back up files in the flash recovery area created by the primary instance to tape:

   BACKUP DEVICE TYPE DISK CURRENT CONTROLFILE;
   BACKUP RECOVERY AREA;
   

   Issue these commands (or use them in a script) every day or once a week, depending on how much application of redo data can be tolerated in the event of the loss of all current control files (see Section 10.2.4).

2. On the standby database, issue the following RMAN commands every day to roll forward a level 0 copy of the database:

   RECOVER COPY OF DATABASE WITH TAG 'OSS';
   BACKUP DEVICE TYPE DISK INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'OSS' DATABASE;
   BACKUP DEVICE TYPE DISK ARCHIVELOG ALL NOT BACKED UP 2 TIMES;
   BACKUP RECOVERY AREA;
   

   These commands apply the level 1 incremental backup taken a day before, create a new level 1 incremental backup, back up archived redo log files to the flash recovery area, and back up files created by the standby instance from the flash recovery area to tape.

10.1.2 Performing Backups Directly to Tape

If all backups are written directly to tape, configure the default device type to SBT using the RMAN CONFIGURE DEFAULT DEVICE TYPE TO SBT command.

On the primary database, use the following RMAN commands to back up the current control file and copy auto backups created by the primary instance to tape:

BACKUP AS BACKUPSET CURRENT CONTROLFILE;
BACKUP RECOVERY AREA;

Issue these commands every day or once a week, depending on how much application of redo data can be tolerated in the event of loss of all current control files (refer to Section 10.2.4).

Assuming that a complete database backup is taken every Sunday, the following commands can be issued on the standby database to take a level 0 database backup:

BACKUP AS BACKUPSET INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG NOT BACKED UP 2 TIMES;

On the other days of the backup cycle, run the following commands to create a level 1 incremental backup of the database and all archived redo log files that have not already been backed up 2 times:

BACKUP AS BACKUPSET INCREMENTAL LEVEL 1 DATABASE PLUS ARCHIVELOG NOT BACKED UP 2 TIMES;

10.2.1 Recovery from Loss of Datafiles on the Primary Database

Issue the following RMAN commands to restore and recover datafiles. You must be connected to both the primary and recovery catalog databases.

RESTORE DATAFILE n,m...;
RECOVER DATAFILE n,m...;

Issue the following RMAN commands to restore and recover tablespaces. You must be connected to both the primary and recovery catalog databases.

RESTORE TABLESPACE tbs_name1, tbs_name2, ...
RECOVER TABLESPACE tbs_name1, tbs_name2, ...

10.2.2 Recovery from Loss of Datafiles on the Standby Database

To recover the standby database after the loss of one or more datafiles, you must restore the lost files to the standby database from the backup using the RMAN RESTORE DATAFILE command. If all the archived redo log files required for recovery of damaged files are accessible on disk by the standby database, restart Redo Apply.

If the archived redo log files required for recovery are not accessible on disk, use RMAN to recover the restored datafiles to an SCN/log sequence greater than the last log applied to the standby database, and then restart Redo Apply to continue the application of redo data, as follows:

1. Stop Redo Apply.

2. Determine the value of the UNTIL_SCN column, by issuing the following query:

   SQL> SELECT MAX(NEXT_CHANGE#)+1 UNTIL_SCN FROM V$LOG_HISTORY LH, V$DATABASE DB WHERE LH.RESETLOGS_CHANGE#=DB.RESETLOGS_CHANGE# AND LH.RESETLOGS_TIME = DB.RESETLOGS_TIME;
   UNTIL_SCN
   ------- ----------------
   967786
   

3. Issue the following RMAN commands to restore and recover datafiles on the standby database. You must be connected to both the standby and recovery catalog databases (use the TARGET keyword to connect to standby instance):

   RESTORE DATAFILE <n,m,...>;
   RECOVER DATABASE UNTIL SCN 967786;
   

   To restore a tablespace, use the RMAN 'RESTORE TABLESPACE tbs_name1, tbs_name2, ...' command.

4. Restart Redo Apply.

10.2.3 Recovery from the Loss of a Standby Control File

Oracle software allows multiplexing of the standby control file. To ensure the standby control file is multiplexed, check the CONTROL_FILES initialization parameter, as follows:

SQL> SHOW PARAMETER CONTROL_FILES
NAME                                 TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
control_files                        string      <cfilepath1>,<cfilepath2>

If one of the multiplexed standby control files is lost or is not accessible, Oracle software stops the instance and writes the following messages to the alert log:

ORA-00210: cannot open the specified controlfile
ORA-00202: controlfile: '/ade/banand_hosted6/oracle/dbs/scf3_2.f'
ORA-27041: unable to open file

You can copy an intact copy of the control file over the lost copy, then restart the standby instance using the following SQL statements:

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

If all standby control files are lost, then you must create a new control file from the primary database, copy it to all multiplexed locations on the standby database, and restart the standby instance and Redo Apply. The created control file loses all information about archived redo log files generated before its creation. Because RMAN looks into the control file for the list of archived redo log files to back up, all the archived redo log files generated since the last backup must be manually cataloged.

10.2.4 Recovery from the Loss of the Primary Control File

Oracle software allows multiplexing of the control file on the primary database. If one of the control files cannot be updated on the primary database, the primary database instance is shut down automatically. As described in Section 10.2.3, you can copy an intact copy of the control file and restart the instance without having to perform restore or recovery operations.

If you lose all of your control files, you can choose among the following procedures, depending on the amount of downtime that is acceptable.

Create a new control file If all control file copies are lost, you can create a new control file using the NORESETLOGS option and open the database after doing media recovery. An existing standby database instance can generate the script to create a new control file by using the following statement:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS;

Note that if the database filenames are different in the primary and standby databases, then you must edit the generated script to correct the filenames.

This statement can be used periodically to generate a control file creation script. If you are going to use control file creation as part of your recovery plan, then you should use this statement after any physical structure change, such as adding or dropping a datafile, tablespace, or redo log member.

The created control file loses all information about the archived redo log files generated before control file creation time. If archived redo log file backups are being performed on the primary database, all the archived redo log files generated since the last archived redo log file backup must be manually cataloged.

Recover using a backup control file If you are unable to create a control file using the previous procedure, then you can use a backup control file, perform complete recovery, and open the database with the RESETLOGS option.

To restore the control file and recover the database, issue the following RMAN commands after connecting to the primary instance (in NOMOUNT state) and catalog database:

RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;

Beginning with Oracle Release 10.1.0, all the backups taken before a RESETLOGS operation can be used for recovery. Hence, it is not necessary to back up the database before making it available for production.

10.2.5 Recovery from the Loss of an Online Redo Log File

Oracle recommends multiplexing the online redo log files. The loss of all members of an online redo log group causes Oracle software to terminate the instance. If only some members of a log file group cannot be written, they will not be used until they become accessible. The views V$LOGFILE and V$LOG contain more information about the current status of log file members in the primary database instance.

When Oracle software is unable to write to one of the online redo log file members, the following alert messages are returned:

ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/ade/banand_hosted6/oracle/dbs/t1_log1.f'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

If the access problem is temporary due to a hardware issue, correct the problem and processing will continue automatically. If the loss is permanent, a new member can be added and the old one dropped from the group.

To add a new member to a redo log group, issue the following statement:

SQL> ALTER DATABASE ADD LOGFILE MEMBER 'log_file_name' REUSE TO GROUP n

You can issue this statement even when the database is open, without affecting database availability.

If all members of an inactive group that has been archived are lost, the group can be dropped and re-created.

In all other cases (loss of all online log members for the current ACTIVE group, or an inactive group which has not yet been archived), you must fail over to the standby database. Refer to Chapter 7 for the failover procedure.

10.2.6 Incomplete Recovery of the Database

Incomplete recovery of the primary database is normally done in cases such as when the database is logically corrupted (by some user or an application) or when a tablespace or datafile was accidentally dropped from database.

Depending on the current database checkpoint SCN on the standby database instances, you can use one of the following procedures to perform incomplete recovery of the database. All the procedures are in order of preference, starting with the one that is the least time consuming.

Using Flashback Database Using Flashback Database is the recommended procedure when the Flashback Database feature is enabled on the primary database, none of the database files are lost, and the point-in-time recovery is greater than the oldest flashback SCN or the oldest flashback time. See Section 12.5 for the procedure to use Flashback Database to do point-in-time recovery.

Using the standby database instance This is the recommended procedure when the standby database is behind the desired incomplete recovery time, and Flashback Database is not enabled on the primary or standby databases:

1. Recover the standby database to the desired point in time.

   RECOVER DATABASE UNTIL TIME 'time';
   

   Alternatively, incomplete recovery time can be specified using the SCN or log sequence number:

   RECOVER DATABASE UNTIL SCN incomplete recovery SCN'
   RECOVER DATABASE UNTIL LOGSEQ incomplete recovery log sequence number THREAD thread number
   

2. Open the standby database in read-only mode to verify the state of database.

   If the state is not what is desired, use the LogMiner utility to look at the archived redo log files to find the right target time or SCN for incomplete recovery. Alternatively, you can start by recovering the standby database to a point that you know is before the target time, and then open the database in read-only mode to examine the state of the data. Repeat this process until the state of the database is verified to be correct. Note that if you recover the database too far (that is, past the SCN where the error occurred) you cannot return it to an earlier SCN.

3. Activate the standby database using the SQL ALTER DATABASE ACTIVATE STANDBY DATABASE statement. This converts the standby database to a primary database, creates a new resetlogs branch, and opens the database. See Section 8.4 to learn how the standby database reacts to the new reset logs branch.

Using the primary database instance If all of the standby database instances have already been recovered past the desired point in time and Flashback Database is enabled on the primary or standby database, then this is your only option.

Use the following procedure to perform incomplete recovery on the primary database:

1. Use LogMiner or another means to identify the time or SCN at which all the data in the database is known to be good.

2. Using the time or SCN, issue the following RMAN commands to do incomplete database recovery and open the database with the RESETLOGS option (after connecting to catalog database and primary instance that is in MOUNT state):

   RUN 
   {
   SET UNTIL TIME 'time';
   RESTORE DATABASE;
   RECOVER DATABASE;
   }
   ALTER DATABASE OPEN RESETLOGS;
   

After this process, all standby database instances must be reestablished in the Data Guard configuration.

10.3.1 Standby Databases Too Geographically Distant to Share Backups

In this case, the backups taken on a standby system are not easily accessible by the primary system or other standby systems. Perform a complete backup of the database on all systems to perform recovery operations. The flash recovery area can reside locally on the primary and standby systems (for example, the flash recovery area is not the same for the primary and standby databases).

In this scenario, you can still use the general strategies described in Section 10.2, with the following exceptions:

• Backup files created by RMAN must be tagged with the local system name, and with RESTORE operations that tag must be used to restrict RMAN from selecting backups taken on the same host. In other words, the BACKUP command must use the TAG node name option when creating backups; the RESTORE command must use the FROM TAG node name option; and the RECOVER command must use FROM TAG node name ARCHIVELOG TAG node name option.

• Disaster recovery of the standby site:

  1. Start the standby instance in the NOMOUNT state using the same parameter files with which the standby was operating earlier.

  2. Create a standby control file on the primary instance using the SQL ALTER DATABASE CREATE STANDBY CONTROLFILE AS filename statement, and use the created control file to mount the standby instance.

  3. Issue the following RMAN commands to restore and recover the database files:

     RESTORE DATABASE FROM TAG 'node name'
     RECOVER DATABASE FROM TAG 'node name' ARCHIVELOG TAG 'node name'
     

  4. Restart Redo Apply.

The standby instance will fetch the remaining archived redo log files as described in Section 5.8.

10.3.2 Standby Database Does Not Contain Datafiles, Used as a FAL Server

Use the same procedure described in Section 10.1, with the exception that the RMAN commands that back up database files cannot be run against the FAL server. The FAL server can be used as a backup source for all archived redo log files, thus off-loading backups of archived redo log files to the FAL server.

10.3.3 Standby Database File Names Are Different than Primary Database

If the database filenames are not the same on the primary and standby databases, the RESTORE and RECOVER commands you use will be slightly different. To obtain the actual datafile names on the standby database, query the V$DATAFILE view and specify the SET NEWNAME option for all the datafiles in the database:

RUN 
{
SET NEWNAME FOR DATAFILE 1 TO 'existing file location for file#1 from V$DATAFILE';
SET NEWNAME FOR DATAFILE 2 TO 'existing file location for file#2 from V$DATAFILE';
…
…
 SET NEWNAME FOR DATAFILE n TO 'existing file location for file#n from V$DATAFILE';
 RESTORE {DATAFILE <n,m,…> | TABLESPACE tbs_name_1, 2, …| DATABASE;
SWITCH DATAFILE ALL; 
RECOVER DATABASE {NOREDO};
}

Similarly, the RMAN DUPLICATE command should also use the SET NEWNAME option to specify new filenames during standby database creation.

10.3.4 Deletion Policy for Archived Redo Log Files In Flash Recovery Areas

By default, archived redo log files in a flash recovery area that were backed up to a tertiary device or made obsolete (as defined by the RMAN retention policy) are eligible for deletion. The archived redo log files that are backed up or obsolete can eventually be deleted automatically to make space if the disk space in the flash recovery area becomes full. However, you can change this default deletion policy using the following RMAN command:

CONFIGURE ARCHIVELOG DELETION POLICY TO [CLEAR | NONE | APPLIED ON STANDBY];

This section describes the command qualifiers and provides examples for setting up a deletion policy. See Oracle Database Backup and Recovery Advanced User's Guide for more information about how Oracle software manages disk space in the flash recovery area.

Using the APPLIED ON STANDBY Clause

Use the APPLIED ON STANDBY clause so that archived redo log files that have been applied on all mandatory standby destinations will be deleted. The actions taken when you specify this clause are described in the following table:

See Appendix E for more information about cascaded destinations.

Using the CLEAR Clause

Use the CLEAR clause to disable the deletion policy that was previously set up with the RMAN CONFIGURE ARCHIVELOG DELETION POLICY command. The Oracle database will resume the default deletion policy behavior, which is to delete archived redo log files that are backed up or obsolete to make space if disk space in the flash recovery area becomes full.

Using the NONE Clause

Use the NONE clause so that archived redo logs in flash recovery area that were backed up or obsolete as per the RMAN retention policy are eligible for deletion. This is the default configuration. Archived redo log files that are backed up or obsolete are deleted to make space if the disk space in the flash recovery area becomes full.

Examples of the CONFIGURE ARCHIVELOG DELETION POLICY Command

When backups of archived redo log files are taken on the standby database:

1. Issue the following command on the primary database:

   CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
   

2. Issue the following command on the standby database:

   CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
   

When backups of archived redo log files are taken on the primary database:

1. Issue the following command on the standby database:

   CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
   

2. Issue the following command on the primary database:

   CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
   

SELECT NAME, VALUE FROM V$RMAN_CONFIGURATION WHERE
NAME LIKE '%ARCHIVELOG DELETION POLICY%';

NAME                              VALUE
-----------------------------     --------------
ARCHIVELOG DELETION POLICY        TO APPLIED ON STANDBY

RMAN> SHOW ARCHIVELOG DELETION POLICY
RMAN configuration parameters are:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;