Skip Headers

Oracle® Database Backup and Recovery Advanced User's Guide
10g Release 1 (10.1)
Part Number B10734-01
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Feedback
Go to previous page
Previous		 Go to next page
Next
 View PDF

17
Performing User-Managed Database Flashback and Recovery

This chapter describes how to restore and recover a database. It includes the following topics:

User-Managed Backup and Flashback Features of Oracle

Oracle's flashback features, which let you undo damage to your database after logical data corruption, include the following:

All of these operations are available within SQL*Plus, and none of them require the use of Recovery Manager. More details about using the flashback features of Oracle in data recovery situations are provided in  "Oracle Flashback Technology: Overview".

Performing Flashback Database with SQL*Plus

The SQL*Plus FLASHBACK DATABASE command performs the same function as the RMAN FLASHBACK DATABASE command: it returns the database to a prior state.

Note that using Flashback Database requires that you create a flash recovery area for your database and enable the collection of flashback logs. See "Oracle Flashback Database: Alternative to Point-In-Time Recovery" for more details about how the Flashback Database feature works, requirements for using Flashback Database , and how to enable collection of flashback logs required for Flashback Database. The requirements and preparations are the same whether you use RMAN or user-managed backup and recovery.

To perform the FLASHBACK DATABASE operation:

  1. Query the target database to determine the range of possible flashback SCNs. The following SQL*Plus queries show you the the latest and earliest SCN in the flashback window: 
    SQL> SELECT CURRENT_SCN FROM V$DATABASE;

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME 
     FROM V$FLASHBACK_DATABASE_LOG;
  2. Use other flashback features if necessary, to identify the SCN or time of the unwanted changes to your database.
  3. Start SQL*Plus with administrator privileges, and run the FLASHBACK DATABASE statement to return the database to a prior TIMESTAMP or SCN. For example: 
    FLASHBACK DATABASE TO SCN 46963;
FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24);
FLASHBACK DATABASE TO TIMESTAMP timestamp'2002-11-05 14:00:00';
FLASHBACK DATABASE 
  TO TIMESTAMP to_timestamp('2002-11-11 16:00:00', 'YYYY-MM-DD HH24:MI:SS');

    Open the database read-only to examine the results of the Flashback Database operation. When the operation completes, you can open the database read-only and perform some queries to make sure you have recovered the data you need. If you find that you need to perform Flashback Database again to a different target time, then use RECOVER DATABASE to return the database back to the present time, and then try another FLASHBACK DATABASE statement.

    If you are satisfied with the results of Flashback Database, then you can re-open your database with the RESETLOGS option. If appropriate, you can also use an Oracle export utililty like Data Pump Export to save lost data, use RECOVER DATABASE to return the database to the present, and re-import the lost object.

About User-Managed Restore Operations

To restore a file is to replace it with a backup file. Typically, you restore a file when a media failure or user error has damaged or deleted the original file. The following files are candidates for restore operations:

In each case, the loss of a primary file and the restore of a backup has the following implications for media recovery.

If you lose . . . Then . . .

One or more datafiles

You must restore them from a backup and perform media recovery. Recovery is required whenever the checkpoint SCN in the datafile header does not match the checkpoint SCN for the datafile that is recorded in the control file.

All copies of the current control file

You must restore a backup control file and then open the database with the RESETLOGS option.

If you do not have a backup, then you can attempt to re-create the control file. If possible, use the script included in the ALTER DATABASE BACKUP CONTROLFILE TO TRACE output. Additional work may be required to match the control file structure with the current database structure.

One copy of a multiplexed control file

Copy one of the intact multiplexed control files into the location of the damaged or missing control file and open the database. If you cannot copy the control file to its original location, then edit the initialization parameter file to reflect a new location or remove the damaged control file. Then, open the database.

One or more archived logs required for media recovery

You must restore backups of these archived logs for recovery to proceed. You can restore either to the default or nondefault location. If you do not have backups, then you must performing incomplete recovery up to an SCN before the first missing redo log and open RESETLOGS.

The server parameter file

If you have a backup of the server parameter file, then restore it. Alternatively, if you have a backup of the client-side initialization parameter file, then you can restore a backup of this file, start the instance, and then re-create the server parameter file.

Note:

Restore and recovery of Oracle-managed files is no different from restore and recovery of user-named files.

Determining Which Datafiles Require Recovery

You can use the dynamic performance view V$RECOVER_FILE to determine which files to restore in preparation for media recovery. This view lists all files that need to be recovered and explains why they need to be recovered.

The following query displays the file ID numbers of datafiles that require media recovery as well as the reason for recovery (if known) and the SCN and time when recovery needs to begin:

SELECT * FROM V$RECOVER_FILE;

FILE#      ONLINE  ERROR              CHANGE#    TIME     
---------- ------- ------------------ ---------- ---------
        14 ONLINE                              0          
        15 ONLINE  FILE NOT FOUND              0          
        21 OFFLINE OFFLINE NORMAL              0
Note:

The view is not useful if the control file currently in use is a restored backup or a new control file created after the media failure occurred. A restored or re-created control file does not contain the information the database needs to populate V$RECOVER_FILE accurately.

Query V$DATAFILE and V$TABLESPACE to obtain filenames and tablespace names for datafiles requiring recovery. For example, enter:

SELECT d.NAME, t.NAME AS tablespace_name
FROM V$DATAFILE d, V$TABLESPACE t 
WHERE t.TS# = d.TS# 
AND d.FILE# IN (14,15,21);  # use values obtained from V$RECOVER_FILE query

You can combine these queries in the following SQL*Plus script (sample output show in the following example):

COL df# FORMAT 999
COL df_name FORMAT a20
COL tbsp_name FORMAT a10
COL status FORMAT a7
COL error FORMAT a10
SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name, 
       d.STATUS, r.ERROR, r.CHANGE#, r.TIME
FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
WHERE t.TS# = d.TS#
AND d.FILE# = r.FILE#
/

Restoring Datafiles and Archived Redo Logs

This section contains the following topics:

Restoring Datafiles with Operating System Utilities

If a media failure permanently damages one or more datafiles of a database, then you must restore backups of these datafiles before you can recover the damaged files. If you cannot restore a damaged datafile to its original location (for example, you must replace a disk, so you restore the files to an alternate disk), then you must indicate the new locations of these files to the control file.

If you are restoring a database file on a raw disk or partition, then the procedure is basically the same as when restoring to a file on a file system. However, be aware of the naming conventions for files on raw devices (which differ depending on the operating system), and use an operating system utility that supports raw devices.

See Also:

"Making User-Managed Backups to Raw Devices" for an overview of considerations when backing up and restoring files on raw devices

To restore backup datafiles to their default location:

  1. Determine which datafiles to recover by using the techniques described in "Determining Which Datafiles Require Recovery".
  2. If the database is open, then take the tablespaces containing the inaccessible datafiles offline. For example, enter: 
    ALTER TABLESPACE users OFFLINE IMMEDIATE;
  3. Copy backups of the damaged datafiles to their default location using operating system commands. For example, to restore users01.dbf you might issue: 
    % cp /disk2/backup/users01.dbf $ORACLE_HOME/oradata/trgt/users01.dbf
  4. Recover the affected tablespace. For example, enter: 
    RECOVER TABLESPACE users
  5. Bring the recovered tablespace online. For example, enter: 
    ALTER TABLESPACE users ONLINE;

Restoring Archived Redo Logs with Operating System Utilities

All archived redo logs generated between the time a restored backup was created and the target recovery time are required for the pending recovery. The archived logs will eventually need to be on disk so that they are available to the database.

To restore necessary archived redo logs:

  1. To determine which archived redo log files are needed, query V$ARCHIVED_LOG and V$RECOVERY_LOG. If a datafile requires recovery, but no backup of the datafile exists, then you need all redo generated starting from the time when the datafile was added to the database.

    View Description

    V$ARCHIVED_LOG

    Lists filenames for all the archived logs.

    V$RECOVERY_LOG

    Lists only the archived redo logs that the database needs to perform media recovery. It also includes the probable names of the files, using LOG_ARCHIVE_FORMAT.

    Note: This view is only populated when recovery is required for a datafile. Hence, this view is not useful in the case of a planned recovery such as a user error.

  2. If space is available, then restore the required archived redo log files to the location specified by LOG_ARCHIVE_DEST_1. The database locates the correct log automatically when required during media recovery. For example, enter: 
    % cp /disk2/arch/* $ORACLE_HOME/oradata/trgt/arch
  3. If sufficient space is not available at the location indicated by the archiving destination initialization parameter, restore some or all of the required archived redo log files to an alternate location. Specify the location before or during media recovery using the LOGSOURCE parameter of the SET statement in SQL*Plus or the RECOVER ... FROM parameter of the ALTER DATABASE statement in SQL. For example, enter: 
    SET LOGSOURCE /tmp   # set location using SET statement
  DATABASE RECOVER FROM '/tmp';  # set location in RECOVER statement
  4. After an archived log is applied, and after making sure that a copy of each archived log group still exists in offline storage, delete the restored copy of the archived redo log file to free disk space. For example: 
    % rm /tmp/*.dbf
    See Also:

    Oracle Database Reference for more information about the data dictionary views, and "About User-Managed Media Recovery" for an overview of log application during media recovery

Restoring Control Files

This section contains the following topics:

Losing a Member of a Multiplexed Control File

Use the following procedures to recover a database if a permanent media failure has damaged one or more control files of a database and at least one control file has not been damaged by the media failure.

Copying a Multiplexed Control File to a Default Location

If the disk and file system containing the lost control file are intact, then you can simply copy one of the intact control files to the location of the missing control file. In this case, you do not have to alter the CONTROL_FILES initialization parameter setting.

To replace a damaged control file by copying a multiplexed control file:

  1. If the instance is still running, then shut it down: 
    SHUTDOWN ABORT
  2. Correct the hardware problem that caused the media failure. If you cannot repair the hardware problem quickly, then proceed with database recovery by restoring damaged control files to an alternative storage device, as described in "Copying a Multiplexed Control File to a Nondefault Location".
  3. Use an intact multiplexed copy of the database's current control file to copy over the damaged control files. For example, to replace bad_cf.f with good_cf.f, you might enter: 
    % cp /oracle/good_cf.f /oracle/dbs/bad_cf.f
  4. Start a new instance and mount and open the database. For example, enter: 
    STARTUP

Copying a Multiplexed Control File to a Nondefault Location

Assuming that the disk and file system containing the lost control file are not intact, then you cannot copy one of the good control files to the location of the missing control file. In this case, you must alter the CONTROL_FILES initialization parameter to indicate a new location for the missing control file.

To restore a control file to a nondefault location:

  1. If the instance is still running, then shut it down: 
    SHUTDOWN ABORT
  2. If you cannot correct the hardware problem that caused the media failure, then copy the intact control file to alternative locations. For example, to copy a good version of control01.dbf to a new disk location you might issue: 
    % cp $ORACLE_HOME/oradata/trgt/control01.dbf /new_disk/control01.dbf
  3. Edit the parameter file of the database so that the CONTROL_FILES parameter reflects the current locations of all control files and excludes all control files that were not restored. Assume the initialization parameter file contains: 
    CONTROL_FILES='/oracle/oradata/trgt/control01.dbf','/bad_disk/control02.dbf'

    Then, you can edit it as follows:

    CONTROL_FILES='/oracle/oradata/trgt/control01.dbf','/new_disk/control02.dbf'
  4. Start a new instance and mount and open the database. For example: 
    STARTUP

Losing All Current Control Files When a Backup Is Available

Use the following procedures to restore a backup control file if a permanent media failure has damaged all control files of a database and you have a backup of the control file. When a control file is inaccessible, you can start the instance, but not mount the database. If you attempt to mount the database when the control file is unavailable, then you receive this error message:

ORA-00205: error in identifying controlfile, check alert log for more info

You cannot mount and open the database until the control file is accessible again. If you restore a backup control file, then you must open RESETLOGS.

As indicated in Table 17-1, the procedure for restoring the control file depends on whether the online redo logs are available.

Table 17-1 Scenarios When Control Files Are Lost
Status of Online Logs Status of Datafiles Response

Available

Current

If the online logs contain redo necessary for recovery, then restore a backup control file and apply the logs during recovery. You must specify the filename of the online logs containing the changes in order to open the database. After recovery, open RESETLOGS.

Unavailable

Current

If the online logs contain redo necessary for recovery, then re-create the control file. Because the online redo logs are inaccessible, open RESETLOGS (when the online logs are accessible it is not necessary to OPEN RESETLOGS after recovery with a created control file).

Available

Backup

Restore a backup control file, perform complete recovery, and then open RESETLOGS.

Unavailable

Backup

Restore a backup control file, perform incomplete recovery, and then open RESETLOGS.

Restoring a Backup Control File to the Default Location

If possible, restore the control file to its original location. In this way, you avoid having to specify new control file locations in the initialization parameter file.

To restore a backup control file to its default location:

  1. If the instance is still running, shut it down: 
    SHUTDOWN ABORT
  2. Correct the hardware problem that caused the media failure.
  3. Restore the backup control file to all locations specified in the CONTROL_FILES parameter. For example, if ORACLE_HOME/oradata/trgt/control01.dbf and ORACLE_HOME/oradata/trgt/control02.dbf are the control file locations listed in the server parameter file, then use an operating system utility to restore the backup control file to these locations: 
    % cp /backup/control01.dbf ORACLE_HOME/oradata/trgt/control01.dbf
% cp /backup/control02.dbf ORACLE_HOME/oradata/trgt/control02.dbf
  4. Start a new instance and mount the database. For example, enter: 
    STARTUP MOUNT
  5. Begin recovery by executing the RECOVER command with the USING BACKUP CONTROLFILE clause. Specify UNTIL CANCEL if you are performing incomplete recovery. For example, enter: 
    RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
  6. Apply the prompted archived logs. If you then receive another message saying that the required archived log is missing, it probably means that a necessary redo record is located in the online redo logs. This situation can occur when unarchived changes were located in the online logs when the instance crashed.

    For example, assume that you see the following:

    ORA-00279: change 55636 generated at 11/08/2002 16:59:47 needed for thread 1
ORA-00289: suggestion : /oracle/work/arc_dest/arcr_1_111.arc
ORA-00280: change 55636 for thread 1 is in sequence #111
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    You can specify the name of an online redo log and press Enter (you may have to try this a few times until you find the correct log):

    ORACLE_HOME/oradata/redo01.dbf
Log applied.
Media recovery complete.

    If the online logs are inaccessible, then you can cancel recovery without applying them. If all datafiles are current, and if redo in the online logs is required for recovery, then you cannot open the database without applying the online logs. If the online logs are inaccessible, then you must re-create the control file (refer to "Losing All Current and Backup Control Files").

  7. Open the database with the RESETLOGS option after finishing recovery: 
    ALTER DATABASE OPEN RESETLOGS;

Restoring a Backup Control File to a Nondefault Location

If you cannot restore the control file to its original place because the media damage is too severe, then you must specify new control file locations in the server parameter file. A valid control file must be available in all locations specified by the CONTROL_FILES initialization parameter. If not, then the database prevents you from the mounting the database.

To restore a control file to a nondefault location:

Follow the steps in "Restoring a Backup Control File to the Default Location", except after step 2 add the following step:

Edit all locations specified in the CONTROL_FILES initialization parameter to reflect the new control file locations. For example, if the control file locations listed in the server parameter file are as follows, and both locations are inaccessible:

CONTROL_FILES='/oracle/oradata/trgt/control01.dbf',
              '/oracle/oradata/trgt/control01.dbf'

Then, you can edit the initialization parameter file as follows:

CONTROL_FILES='/good_disk/control01.dbf','/good_disk/control02.dbf'

Losing All Current and Backup Control Files

If all control files have been lost in a permanent media failure, but all online redo log members remain intact, then you can recover the database after creating a new control file. The advantage of this tactic is that you are not required to open the database with the RESETLOGS option.

Depending on the existence and currency of a control file backup, you have the options listed in Table 17-2 for generating the text of the CREATE CONTROLFILE statement. Note that changes to the database are recorded in the alert_SID.log, so check this log when deciding which option to choose.

Table 17-2 Options for Creating the Control File (Page 1 of 2)
If you . . . Then . . .

Executed ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS after you made the last structural change to the database, and if you have saved the SQL command trace output

Use the CREATE CONTROLFILE statement from the trace output as-is.

Performed your most recent execution of ALTER DATABASE BACKUP CONTROLFILE TO TRACE before you made a structural change to the database

Edit the output of ALTER DATABASE BACKUP CONTROLFILE TO TRACE to reflect the change. For example, if you recently added a datafile to the database, then add this datafile to the DATAFILE clause of the CREATE CONTROLFILE statement.

Backed up the control file with the ALTER DATABASE BACKUP CONTROLFILE TO filename statement (not the TO TRACE option)

Use the control file copy to obtain SQL output. Create a temporary database instance, mount the backup control file, and then run ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS. If the control file copy predated a recent structural change, then edit the trace to reflect the change.

Do not have a control file backup in either TO TRACE format or TO filename format

Execute the CREATE CONTROLFILE statement manually (refer to Oracle Database SQL Reference).

Note:

If your character set is not the default US7ASCII, then you must specify the character set as an argument to the CREATE CONTROLFILE statement. The database character set is written to the alert log at startup. The character set information is also recorded in the BACKUP CONTROLFILE TO TRACE output.

To create a new control file:

  1. Start the database in NOMOUNT mode. For example, enter: 
    STARTUP NOMOUNT
  2. Create the control file with the CREATE CONTROLFILE statement, specifying the NORESETLOGS option (refer to Table 17-2 for options). The following example assumes that the character set is the default US7ASCII: 
    CREATE CONTROLFILE REUSE DATABASE SALES NORESETLOGS ARCHIVELOG
     MAXLOGFILES 32
     MAXLOGMEMBERS 2
     MAXDATAFILES 32
     MAXINSTANCES 16
     MAXLOGHISTORY 1600
LOGFILE
     GROUP 1 (
       '/diska/prod/sales/db/log1t1.dbf',
       '/diskb/prod/sales/db/log1t2.dbf'
     )  SIZE 100K
     GROUP 2 (
       '/diska/prod/sales/db/log2t1.dbf',
       '/diskb/prod/sales/db/log2t2.dbf'
     )  SIZE 100K,
DATAFILE
     '/diska/prod/sales/db/database1.dbf',
     '/diskb/prod/sales/db/filea.dbf';

After creating the control file, the instance mounts the database.

Go to previous page
Previous		 Go to next page
Next
 Oracle
Copyright © 2003 Oracle Corporation
All Rights Reserved.
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Feedback