2 Getting Started with RMAN

Backing Up a Database in ARCHIVELOG Mode

If a database runs in ARCHIVELOG mode, then you can back up the database while it is open. The backup is called an inconsistent backup because redo is required during recovery to bring the database to a consistent state. If you have the archived redo logs needed to recover the backup, open database backups are as effective for data protection as consistent backups.

To back up the database and archived redo logs while the database is open:

1. Start RMAN and connect to a target database.

2. Run the BACKUP DATABASE command.

   For example, enter the following command at the RMAN prompt to back up the database and all archived redo log files to the default backup device:

   RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
   

Backing Up a Database in NOARCHIVELOG Mode

If a database runs in NOARCHIVELOG mode, then the only valid database backup is a consistent backup. For the backup to be consistent, the database must be mounted after a consistent shutdown. No recovery is required after restoring the backup.

To make a consistent database backup:

1. Start RMAN and connect to a target database.

2. Shut down the database consistently and then mount it.

   For example, enter the following commands to guarantee that the database is in a consistent state for a backup:

   RMAN> SHUTDOWN IMMEDIATE;
   RMAN> STARTUP FORCE DBA;
   RMAN> SHUTDOWN IMMEDIATE;
   RMAN> STARTUP MOUNT;
   

3. Run the BACKUP DATABASE command.

   For example, enter the following command at the RMAN prompt to back up the database to the default backup device:

   RMAN> BACKUP DATABASE;
   

   The following variation of the command creates image copy backups of all datafiles in the database:

   RMAN> BACKUP AS COPY DATABASE;
   

4. Open the database and resume normal operations.

   The following command opens the database:

   RMAN> ALTER DATABASE OPEN;
   

Typical Backup Options

The BACKUP command includes a host of options, parameters, and clauses that control backup output. Table 2-1 lists some typical backup options.

BACKUP 
  FORMAT 'AL_%d/%t/%s/%p' 
  ARCHIVELOG LIKE '%arc_dest%';

BACKUP
  TAG 'weekly_full_db_bkup' 
  DATABASE MAXSETSIZE 10M;

Making Incremental Backups

If you specify BACKUP INCREMENTAL, then RMAN creates an incremental backup of a database. Incremental backups capture block-level changes to a database made after a previous incremental backup. Incremental backups are generally smaller and faster to make than full database backups. Recovery with incremental backups is faster than using redo logs alone.

The starting point for an incremental backup strategy is a level 0 incremental backup, which backs up all blocks in the database. An incremental backup at level 0 is identical in content to a full backup, however, unlike a full backup the level 0 backup is considered a part of the incremental backup strategy.

A level 1 incremental backup contains only blocks changed after a previous incremental backup. If no level 0 backup exists in either the current or parent database incarnation when you run a level 1 backup, then RMAN makes a level 0 backup automatically.

A level 1 backup can be a cumulative incremental backup, which includes all blocks changed since the most recent level 0 backup, or a differential incremental backup, which includes only blocks changed since the most recent incremental backup. Incremental backups are differential by default.

When restoring incremental backups, RMAN uses the level 0 backup as the starting point, then updates changed blocks based on level 1 backups where possible to avoid reapplying changes from redo one at a time. Recovering with incremental backups requires no additional effort on your part. If incremental backups are available, then RMAN uses them during recovery.

To make incremental backups of the database:

1. Start RMAN and connect to a target database.

2. Run the BACKUP INCREMENTAL command.

   The following example creates a level 0 incremental backup to serve as a base for an incremental backup strategy:

   BACKUP INCREMENTAL LEVEL 0 DATABASE;
   

   The following example creates a level 1 cumulative incremental backup:

   BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
   

   The following example creates a level 1 differential incremental backup:

   BACKUP INCREMENTAL LEVEL 1 DATABASE;
   

BACKUP 
  INCREMENTAL LEVEL 1
  FOR RECOVER OF COPY 
  WITH TAG 'incr_update'
  DATABASE;

BACKUP 
  INCREMENTAL LEVEL 1
  FOR RECOVER OF COPY
  DATAFILECOPY FORMAT
  '/disk2/df1.cpy'
  DATABASE;

Validating Database Files and Backups

You can use the VALIDATE command to confirm that all database files exist, are in their correct location, and are free of physical corruption. The CHECK LOGICAL option also checks for logical block corruption.

To validate database files:

1. Start RMAN and connect to a target database.

2. Run the BACKUP VALIDATE ... command for the desired files.

   For example, enter the following commands to validate all database files and archived redo log files for physical and logical corruption:

   BACKUP VALIDATE CHECK LOGICAL
     DATABASE ARCHIVELOG ALL;
   

   You can also use the VALIDATE command to check individual data blocks, as shown in the following example:

   VALIDATE DATAFILE 4 BLOCK 10 TO 13;
   

   You can also validate backup sets, as shown in the following example:

   VALIDATE BACKUPSET 3;
   

   You specify backup sets by primary key, which is shown in the output of the LIST BACKUP command.

Scripting RMAN Operations

RMAN supports the use of command files to manage recurring tasks such as weekly backups. A command file is a client-side text file containing RMAN commands, exactly as you enter them at the RMAN prompt. You can use any file extension. The RUN command provides a degree of flow-of-control in your scripts.

To create and run a command file:

1. Use a text editor to create a command file.

   For example, create a command file with the following contents:

   # my_command_file.txt
   CONNECT TARGET /
   BACKUP DATABASE PLUS ARCHIVELOG;
   LIST BACKUP;
   EXIT;
   

2. Start RMAN and then execute the contents of a command file by running the @ command at the RMAN prompt:

   % rman
   RMAN> @/my_dir/my_command_file.txt  # runs specified command file
   

   You can also launch RMAN with a command file to run, as shown here:

   % rman @/my_dir/my_command_file.txt
   

Listing Backups

Run the LIST BACKUP and LIST COPY commands to display information about backups and datafile copies listed in the repository. For backups, you can control the format of LIST output with the options in Table 2-3 and Table 2-4.

For both backups and copies you have additional options shown in Table 2-4.

To list backups and copies:

1. Start RMAN and connect to a target database.

2. Run the LIST command at the RMAN prompt.

   You can display specific objects, as in the following examples:

   LIST BACKUP OF DATABASE;
   LIST COPY OF DATAFILE 1, 2;
   LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 10;
   LIST BACKUPSET OF DATAFILE 1;
   

Reporting on Database Files and Backups

The REPORT command performs more complex analysis than theLIST. Some of the main options are shown in tTable 2-5.

To generate reports of database files and backups:

1. Start RMAN and connect to a target database.

2. Run the REPORT command at the RMAN prompt.

   The following example reports backups that are obsolete according to the currently configured backup retention policy:

   REPORT OBSOLETE;
   

   The following example reports the datafiles and tempfiles in the database:

   REPORT SCHEMA;
   

Cross-checking Backups

The CROSSCHECK command synchronizes the logical records of RMAN backups and copies with the files on storage media. If a backup is on disk, then CROSSCHECK determines whether the header of the file is valid. If a backup is on tape, then RMAN queries the RMAN repository for the names and locations of the backup pieces. It is a good idea to crosscheck backups and copies before deleting them.

To crosscheck all backups and copies on disk:

1. Start RMAN and connect to a target database.

2. Run the CROSSCHECK command, as shown in the following example:

   CROSSCHECK BACKUP;
   CROSSCHECK COPY;
   

Deleting Obsolete Backups

The DELETE command removes RMAN backups and copies from disk and tape, updates the status of the files to DELETED in the control file repository, and removes the records from the recovery catalog (if you use a catalog). If you run RMAN interactively, and if you do not specify the NOPROMPT option, then DELETE displays a list of files and prompts for confirmation before deleting any file in the list.

The DELETE OBSOLETE command is particular useful because RMAN deletes backups and datafile copies recorded in the RMAN repository that are obsolete, that is, no longer needed. You can use options on the DELETE command to specify what is obsolete or use the configured backup retention policy.

To delete obsolete backups and copies:

1. Start RMAN and connect to a target database.

2. Run the DELETE OBSOLETE command, as shown in the following example:

   DELETE OBSOLETE;
   

Listing Failures and Determining Repair Options

A failure is a persistent data corruption detected by the Health Monitor. Examples include physical and logical data block corruptions and missing datafiles. Each failure has a failure priority and failure status. The priority can be CRITICAL, HIGH, or LOW. The status can be OPEN or CLOSED.

You can run the LIST FAILURE command to show all known failures. If failures exist, then run the ADVISE FAILURE command in the same session to determine manual and automated repair options. The following example illustrates these two commands (sample output included).

RMAN> LIST FAILURE;
 
List of Database Failures
=========================
 
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
142        HIGH     OPEN      23-APR-07     One or more non-system datafiles are missing
101        HIGH     OPEN      23-APR-07     Datafile 1: '/disk1/oradata/prod/system01.dbf' 
                                            contains one or more corrupt blocks

RMAN> ADVISE FAILURE;
 
List of Database Failures
=========================
 
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
142        HIGH     OPEN      23-APR-07     One or more non-system datafiles are missing
101        HIGH     OPEN      23-APR-07     Datafile 1: '/disk1/oradata/prod/system01.dbf' 
                                            contains one or more corrupt blocks
 
analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete
 
Mandatory Manual Actions
========================
no manual actions available
 
Optional Manual Actions
=======================
1. If file /disk1/oradata/prod/users01.dbf was unintentionally renamed or moved, restore it
 
Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 28; Perform block media recovery of 
       block 56416 in file 1
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /disk1/oracle/log/diag/rdbms/prod/prod/hm/reco_660500184.hm

The ADVISE FAILURE output shows both manual and automated repair options. First try to fix the problem manually. If you cannot fix the problem manually, then review the automated repair section.

An automated repair option describes a server-managed repair for one or more failures. Repairs are consolidated when possible so that a single repair can fix multiple failures. The repair option indicates which repair is performed and whether data is lost by performing the repair operation.

In Example 2-1, the output indicates the filename of a repair script containing RMAN commands. If you do not want to use Data Recovery Advisor to repair the failure automatically, then you can use the script as the basis of your own recovery strategy.

Repairing Failures

After running LIST FAILURE and ADVISE FAILURE in an RMAN session, you can run REPAIR FAILURE to execute a repair option. If you execute REPAIR FAILURE with no other command options, then RMAN uses the first repair option of the most recent ADVISE FAILURE command in the current session. Alternatively, specify the repair option number obtained from the most recent ADVISE FAILURE command. Example 2-2 illustrates how to repair the failures identified in Example 2-1.

RMAN> REPAIR FAILURE;

By default, REPAIR FAILURE prompts for confirmation before it begins executing. After executing a repair, Data Recovery Advisor reevaluates all existing failures on the possibility that they may also have been fixed. Data Recovery Advisor always verifies that failures are still relevant and automatically closes fixed failures. If a repair fails to complete because of an error, then the error triggers a new assessment and re-evaluation of existing failures and repairs.

Preparing to Restore and Recover Database Files

If you need to recover the database because a media failure damages database files, then you should first ensure that you have the necessary backups. You can use the RESTORE ... PREVIEW command to report, but not restore, the backups that RMAN could use to restore to the specified time. RMAN queries the metadata and does not actually read the backup files. The database can be open when you run this command.

To preview a database restore and recovery:

1. Start RMAN and connect to the target database.

2. Optionally, list the current tablespaces and datafiles, as shown in the following command:

   RMAN> REPORT SCHEMA;
   

3. Run the RESTORE DATABASE command with the PREVIEW option.

   The following command specifies SUMMARY so that the backup metadata is not displayed in verbose mode (sample output included):

   RMAN> RESTORE DATABASE PREVIEW SUMMARY;
    
   Starting restore at 21-MAY-07
   allocated channel: ORA_DISK_1
   channel ORA_DISK_1: SID=80 device type=DISK
    
   List of Backups
   ===============
   Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
   ------- -- -- - ----------- --------------- ------- ------- ---------- ---
   11      B  F  A DISK        18-MAY-07       1       2       NO         TAG20070518T181114
   13      B  F  A DISK        18-MAY-07       1       2       NO         TAG20070518T181114
   using channel ORA_DISK_1
    
   List of Archived Log Copies for database with db_unique_name PROD
   =====================================================================
    
   Key     Thrd Seq     S Low Time
   ------- ---- ------- - ---------
   47      1    18      A 18-MAY-07
           Name: /disk1/oracle/dbs/db1r_60ffa882_1_18_0622902157.arc
    
   Media recovery start SCN is 586534
   Recovery must be done beyond SCN 587194 to clear datafile fuzziness
   validation succeeded for backup piece
   Finished restore at 21-MAY-07
   

Recovering the Whole Database

Use the RESTORE DATABASE and RECOVER DATABASE commands to recover the whole database. You must have previously made backups of all needed files. This scenario assumes that you can restore all datafiles to their original locations. If the original locations are inaccessible, then use the SET NEWNAME command as described in "Restoring Datafiles to a Nondefault Location".

To recover the whole database:

1. Prepare for recovery as explained in "Preparing to Restore and Recover Database Files".

2. Place the database in a mounted state.

   The following example terminates the database instance (if it is started) and mounts the database:

   RMAN> STARTUP FORCE MOUNT;
   

3. Restore the database.

   The following example uses the preconfigured disk channel to restore the database:

   RMAN> RESTORE DATABASE;
   

4. Recover the database, as shown in the following example:

   RMAN> RECOVER DATABASE;
   

5. Open the database, as shown in the following example:

   RMAN> ALTER DATABASE OPEN;
   

Recovering Tablespaces

Use the RESTORE TABLESPACE and RECOVER TABLESPACE commands on individual tablespaces when the database is open. In this case, must take the tablespace that needs recovery offline, restore and then recover the tablespace, and bring the recovered tablespace online.

If you cannot restore a datafile to a new location, then use the RMAN SET NEWNAME command within a RUN command to specify the new filename. Afterward, use a SWITCH DATAFILE ALL command, which is equivalent to using the SQL statement ALTER DATABASE RENAME FILE, to update the control file to reflect the new names for all datafiles for which a SET NEWNAME has been issued in the RUN command.

Unlike in user-managed media recovery, you should not place an online tablespace in backup mode. Unlike user-managed tools, RMAN does not require extra logging or backup mode because it knows the format of data blocks.

To recover an individual tablespace when the database is open:

1. Prepare for recovery as explained in "Preparing to Restore and Recover Database Files".

2. Take the tablespace to be recovered offline:

   The following example takes the users tablespace offline:

   RMAN> SQL 'ALTER TABLESPACE users OFFLINE';
   

3. Restore and recover the tablespace.

   The following RUN command, which you execute at the RMAN prompt, sets a new name for the datafile in the users tablespace:

   RUN
   {
     SET NEWNAME FOR DATAFILE '/disk1/oradata/prod/users01.dbf' 
       TO '/disk2/users01.dbf';
     RESTORE TABLESPACE users;
     SWITCH DATAFILE ALL;   # update control file with new filenames
     RECOVER TABLESPACE users;
   }
   

4. Bring the tablespace online, as shown in the following example:

   RMAN> SQL 'ALTER TABLESPACE users ONLINE';
   

You can also use RESTORE DATAFILE and RECOVER DATAFILE for recovery at the datafile level.

Recovering Individual Data Blocks

RMAN can recover individual corrupted datafile blocks. When RMAN performs a complete scan of a file for a backup, any corrupted blocks are listed in V$DATABASE_BLOCK_CORRUPTION. Corruption is usually reported in alert logs, trace files, or results of SQL queries.

To recover data blocks:

1. Obtain the block numbers of the corrupted blocks if you do not already have this information.

   The easiest way to locate trace files and the alert log is to connect SQL*Plus to the target database and execute the following query:

   SQL> SELECT NAME, VALUE 
     2  FROM V$DIAG_INFO;
   

2. Start RMAN and connect to the target database.

3. Run the RECOVER command to repair the blocks.

   The following RMAN command recovers all corrupted blocks:

   RMAN> RECOVER CORRUPTION LIST;
   

   You can also recover individual blocks, as shown in the following example:

   RMAN> RECOVER DATAFILE 1 BLOCK 233, 235 DATAFILE 2 BLOCK 100 TO 200;