Oracle8 Backup and Recovery Guide Release 8.0 A58396-01 |
Library | Product | Contents | Index

# 9
Recovery Manager Scenarios

This chapter provides scenarios that use Recovery Manager, and includes the following topics:

- Backing Up in NOARCHIVELOG Mode
- Backing Up Databases and Tablespaces
- Backing Up in a Parallel Server Environment
- Copying Datafiles
- Incremental Backups
- Handling Errors
- Using O/S Utilities To Make Copies
- Keeping Backups
- Restoring and Recovering
- Database Point-In-Time Recovery
- Querying the Recovery Catalog
- Using the Report Command for Complex Recovery Catalog Queries

## Backing Up in NOARCHIVELOG Mode

In this scenario the database is operating in NOARCHIVELOG mode, and the administrator has chosen to shut it down cleanly and back it up.

It is possible to skip tablespaces, but any skipped tablespace that has not been offline or read-only since its last backup will be lost if the database has to be restored from a backup.

You must use Server Manager/LineMode to shutdown the database.

         SVRMGR> shutdown;
         SVRMGR> startup mount

Then start Recovery Manager and enter the following:

         run {
         # backup the database to disk
         allocate channel dev1 type disk;
         backup (database format '/oracle/backups/bp_%s_%p'); }

The filename of the backup piece is generated using the format string. When the backup is written to disk, it is important to make sure that the destination (file system or raw device) has enough free space.

## Backing Up Databases and Tablespaces

In the following examples, the database is running in ARCHIVELOG mode.

### Backing Up a Database

To back up a database to tape you must first allocate a tape device. Query V$BACKUP_DEVICE to see what devices are available to you.

The following example shows how to back up the database (except tablespaces that are offline):

         run {
         allocate channel dev1 type 'sbt_tape';
         backup skip offline (database format '%d_%u'); }

A read-only tablespace needs to be backed up only once or twice after it has been made read-only. You can use the skip read only option to skip read-only datafiles. If you use the skip offline option, then the backup will not attempt to access offline datafiles. Use this option if the offline datafiles are not available.

### Backing Up a Tablespace

The following example shows how to back up individual tablespaces. It is important to back up tablespaces that contain important data frequently (including system data and any tablespace that contains rollback segments). Tablespaces containing only temporary segments need not be backed up. Because this example backs up to disk, the format string determines the name of the backup file.

         run {
         allocate channel dev1 type disk;
         backup
         (tablespace system,tbs_1,tbs_2,tbs_3,tbs_4,tbs_5
         format '/oracle/backups/bp_%s_%p');
         }

### Backing Up Individual Datafiles

You can also use Recovery Manager to back up individual datafiles as follows:

         run {
         allocate channel dev1 type 'sbt_tape';
         backup
         (datafile '?/dbs/t_dbs1.f'
         format '%d_%u');
         }

### Backing Up the Control File

The current control file is automatically backed up when the first datafile of the system tablespace is backed up. The current control file can also be explicitly included in a backup or backed up individually.

         run {
         allocate channel dev1 type 'sbt_tape';
         backup
         (tablespace tbs_5 include current controlfile
         format '%d_%u');
         }

### Backing Up Archived Logs

You can also back up archived logs to tape. The range of archived logs can be specified by time or log sequence. Note that specifying an archive log range does not guarantee that all redo in the range is backed up. For example, the last archived log may end before the end of the range, or an archived log in the range may be missing. Recovery Manager simply backs up the logs it finds and does not issue a warning. Note that online logs cannot be backed up; they must be archived first.

Hint: Set the NLS_LANG and NLS_DATE_FORMAT environment variables before invoking Recovery Manager.

         NLS_LANG=american
         NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'

         run {
         allocate channel dev1 type 'sbt_tape';
         backup
         (archivelog  from time  'Nov 13 1996 20:57:13'
                      until time 'Nov 13 1996 21:06:05'
         all
         format '%d_%u');
         }

Here we back up all archived logs from sequence# 288 to sequence# 301 and delete the archived logs after the backup is complete. If the backup fails the logs are not deleted.

         run {
         allocate channel dev1 type 'sbt_tape';
         backup
         (archivelog low logseq 288 high logseq 301 thread 1
         all delete input
         format '%d_%u');
         }

The following commands back up all archived logs generated during the last 24 hours. We archive the current log first to ensure that all redo generated up to the present gets backed up.

         run {
         allocate channel dev1 type 'sbt_tape';
         sql "alter system archive log current";
         backup
         (archivelog  from time 'SYSDATE-1' all
         format '%d_%u') ;
         }

See Also: For more information about your environment variables, see your operating system-specific documentation.

## Backing Up in a Parallel Server Environment

The following script distributes datafile and archivelog backups across two instances in a parallel server environment:

         run {

         allocate channel node_1 type disk connect 'internal/kernel@node_1';
         allocate channel node_2 type disk connect 'internal/kernel@node_2';
            backup
               filesperset 1
               format 'df_%s_%p'
               (tablespace system, rbs, data1, data2
                  channel node_1)
               (tablespace temp, reccat, data3, data4
                  channel node_2);
            backup
               filesperset 20
               format 'al_%s_%p'
               (archivelog
                  until time 'SYSDATE'
                  thread 1
                  delete input
                     channel node_1);
                (archivelog
                     until time 'SYSDATE'
                     thread 2
                     delete input
                     channel node_2);
         release channel node_1;
         release channel node_2;
         }

## Copying Datafiles

Here we use Recovery Manager to make copies of datafiles to disk. A datafile copy is an image copy of the datafile.

         run {
         allocate channel dev1 type disk;
         copy datafile '?/dbs/tbs_01.f/dbs/tbs_01.f' to '?/copy/temp3.f';
         }

## Incremental Backups

An incremental backup contains only blocks that have been changed since the previous backup. The first incremental backup must be a level 0 backup that contains all used blocks.

         run {
         allocate channel dev1 type 'sbt_tape';
         backup incremental level 0
         (database
         format '%d_%u');
         }

The next incremental backup at level 1 or higher will contain all blocks changed since the previous level 0 or level 1 backup.

         run {
         allocate channel dev1 type 'sbt_tape';
         backup incremental level 1
         (database
         format '&d_%u');
         }

If a new datafile or tablespace is added to the database then make a level 0 backup before the incremental backup. Otherwise the incremental backup of the tablespace or the database will fail because Recovery Manager doesn't find a parent backup for the new datafiles.

         run {
         allocate channel dev1 type 'sbt_tape';
         backup incremental level 0
         (tablespace new_tbs
         format '%d_%u');
         }

You can perform incremental backups in NOARCHIVELOG mode.

## Handling Errors

By default a checksum is calculated for every block read from a datafile and stored in the backup. If you use the nochecksum option then checksums are not calculated. However, if the block already contains a checksum, the checksum is validated and stored in the backup. If the validation fails, the block is marked corrupt in the backup.

The set maxcorrupt clause determines how many corrupt blocks backup tolerates. If any datafile has more corrupt blocks than specified by maxcorrupt, the backup will terminate.

By default, backup will terminate if it cannot access a datafile. If the skip inaccessible option is specified, then backup will skip inaccessible datafiles and continue to back up other files. If skip offline is used, then backup will not attempt to access offline files.

         run {
         allocate channel dev1 type 'sbt_tape';
         set maxcorrupt 5;
         backup (database format 'bp_%s_%p'); }

## Using O/S Utilities To Make Copies

Datafile copies you make using O/S utilities can be catalogued in the recovery catalog. Note that only copies made to disk can be catalogued. Because the format of backup pieces is proprietary, O/S utilities cannot write backups that Recovery Manager can read.

The datafile copies must be made using Oracle7 techniques. If the database is open and the datafile is online, you must first issue ALTER TABLESPACE BEGIN BACKUP. The resultant backup can be cataloged:

         catalog datafilecopy '?/dbs/tbs_33.f';

## Keeping Backups

How long backups and copies must be kept depends on factors such as:

- How frequently backups are taken
- How long the past point-in-time recovery is needed

For example, if all datafiles are backed up daily, no point-in-time recovery is needed and only one backup per datafile is sufficient, then previous backups can be deleted as soon as the new one completes.

         # list all files####
         # delete a specific datafilecopy
         change datafilecopy '?/dbs/tbs_35.f' delete;

         # delete archivelogs older than 31 days
         change archivelog until time 'SYSDATE-31' delete';

There is currently no way to delete all the pieces of a multi-piece backup set with a single command. You must delete all the backup pieces individually.The deletion of any piece of a backup set automatically removes that backup set from consideration for restore and recovery.

You must allocate a channel before deleting a backup piece. The specified backup piece must have been created on the same type of device. Note that the allocate channel for delete command is not issued inside of a run command.

         # delete a backup piece
         allocate channel for delete type 'sbt_tape';
         change backuppiece 'testdb_87fa39e0' delete;
         release channel;

## Restoring and Recovering

When and how to restore and/or recover information depends on the state of the database and the location of its datafiles.

The first step toward restoring/recovery is to determine the status of the database by executing the following query:

         select status, parallel from v$instance;

### Restore and Recover When the Database Is Open

If the status is open, then the database is open, but it is possible that some tablespaces and their datafiles need to be restored or recovered.

To recover or restore, execute the following query:

         select file#, status, error, recover, tablespace_name, name
            from v$datafile_header;

If the error column is not null, then either the datafile could not be accessed or its header could not be validated. Unless the error is caused by a temporary hardware or operating system problem, the datafile must be restored or switched to a copy of that datafile:

         run {
         # recover tablespace tbs_1 while the database is open
         allocate channel dev1 type 'sbt_tape';
         sql "alter tablespace tbs_1 offline immediate" ;
         restore tablespace tbs_1 ;
         recover tablespace tbs_1 ;
         sql "alter tablespace tbs_1 online" ;
         release channel dev1 ;
         }

If a datafile cannot be accessed due to a disk failure, it is likely that it must be restored to a new location or switched to an existing datafile copy. The following restore example allocates one disk channel and one 'sbt_tape' channel to allow restore to use datafile copies on disk and backups on 'sbt_tape'. This example allocates one disk device and one tape device to allow Recovery Manager to restore both from disk and tape.

         run {
            allocate channel dev1 type disk;
            allocate channel dev2 type 'sbt_tape';
               sql "alter tablespace tbs_1 offline immediate" ;
               set newname for datafile 'disk7/oracle/tbs11.f'
               to 'disk9/oracle/tbs11.f' ;
               restore (tablespace tbs_1) ;
               switch datafile all ;
               recover tablespace tbs_1 ;
               sql "alter tablespace tbs_1 online" ;
            release channel dev1;
            release channel dev2;
         }

Because V$DATAFILE_HEADER only reads the header block of each datafile it does not detect all problems that require the datafile to be restored. For example, if the datafile contains unreadable data blocks, but its header block is intact, no error is reported.

If the file can be accessed (error is null), but the recover column returns "yes," then the file should be recovered. The Recovery Manager recover command first applies any suitable incremental backups and then applies archived logs and/or online logs. The incremental backups and archived logs are restored as needed.

         run {
         # recover tablespace tbs_1 while the database is open
            allocate channel dev1 type disk ;
               sql "alter tablespace tbs_1 offline" ;
               recover tablespace tbs_1 ;
               sql "alter tablespace tbs_1 online" ;
            release channel dev1 ;
         }

### Restore

If the database status is "mounted," check the control file type:

         select LOG_MODE, CONTROLFILE_TYPE, OPEN_RESETLOGS from v$database;

If the database status is "current," try to open the database:

         alter database open;

If the open succeeds, then use the procedure above to find out if any tablespaces and datafiles need recovery.

If the open fails, then you need to restore and/or recover the database first. If the control file type is "backup," the database is probably being restored and recovered.

## Database Point-In-Time Recovery

The database must be closed to perform database point-in-time recovery. You can use Server Manager to close the database:

         SVRMGR> shutdown abort;
         SVRMGR> startup nomount;

In this scenario, we are recovering the database up until 3 p.m. Assume that the disk containing tablespace TBS_1 datafiles crashed and that there is no backup of tablespace TEMP1 because it only contains temporary segments, no user data. A new location is specified for TBS_1 datafiles. Note that, by default Recovery Manager will restore the files to their most recent location, not to where they were located at 3 p.m.

Hint: Set the NLS_LANG and NLS_DATE_FORMAT environment variables before invoking Recovery Manager.

         NLS_LANG=american
         NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'

         run {
         # recover database until 3pm after restoring tbs_1 to a new location
         allocate channel dev1 type disk;
         allocate channel dev2 type 'sbt_tape';
         set until time 'Nov 15 1996 15:00:00'
         set newname for datafile '/vobs/oracle/dbs/tbs_11.f'
         to '?/dbs/temp1.f' ;
         set newname for datafile '?/dbs/tbs_12.f'
         to '?/dbs/temp2.f' ;
         restore controlfile to '/vobs/oracle/dbs/cf1.f' ;
         replicate controlfile from '/vobs/oracle/dbs/cf1.f';
         sql "alter database mount" ;
         restore database skip tablespace temp1;
         switch datafile all;
         recover database skip tablespace temp1;
         sql "alter database open resetlogs";
         sql "drop tablespace temp1";
         sql "create tablespace temp1 datafile '/vobs/oracle/dbs/temp1.f' size 10M";
         release channel dev1;
         release channel dev2;
         }

Now assume that log sequence 1234 was lost due to a disk crash and the database needs to be recovered using available archivelogs. We know that tablespace READONLY1 has not changed since logseq 1234, so we don't restore it.

         run {
         # recover database until log sequence 1234
         allocate channel dev1 type disk;
         allocate channel dev2 type 'sbt_tape';
         set until logseq 1234 thread 1;
         restore controlfile to '/vobs/oracle/dbs/cf1.f' ;
         replicate controlfile from '/vobs/oracle/dbs/cf1.f';
         sql "alter database mount" ;
         restore database skip tablespace temp1, readonly1;
         recover database skip tablespace temp1;
         sql "alter database open resetlogs";
         sql "drop tablespace temp1";
         sql "create tablespace temp1 datafile '/vobs/oracle/dbs/temp1.f' size 10M";
         release channel dev1;
         release channel dev2; }

## Querying the Recovery Catalog

You can use the list command to query the contents of the recovery catalog, or the target database control file if no recovery catalog is used.

         # list all backups of files in tablespace tbs_1 that were made since
         # November first.
         list until time 'Nov 1 1996 00:00:00' backupset of tablespace tbs_1;
         # list all backups on device type 'sbt_tape'
         list device type 'sbt_tape' backupset of database;
         # list all copies of a datafile, qualified by tag and directory.
         list tag foo like '/somedir/%' copy of datafile 21;
         # list all database incarnations registered in the recovery catalog
         list incarnation of database;

You can also use the list command to determine which copies and backups can be deleted. For example, if a full backup of the database was created on November 2, and it will not be necessary to recover the database to an earlier point-in-time, then the backup sets listed in the following report can be deleted:

         list until time 'Nov 1 1996 00:00:00' backupset of database;

## Using the Report Command for Complex Recovery Catalog Queries

You can use the report command to issue more complex queries against the recovery catalog. The report command also works with the target database control file if no recovery catalog is used.

         # report on all datafiles which need a new backup because they contain
         # unlogged changes that were made after the last full or incremental backup.
         report unrecoverable database;
         # report on all datafiles which need a new backup because 3 or more incremental
         # backups have been taken since the last full backup.
         report need backup incremental 3 database;

         # report on all datafiles in tablespace tbs_1 which need a new backup because
         # the last full or incremental backup was taken more than 5 days ago.
        report need backup days 5 database;

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index