Recovery with Archiving  «Prev  Next»

Lesson 5 Performing closed database recovery
Objective Demonstrate the steps to perform a closed database recovery.

Performing closed Database Recovery in Oracle

Suppose one of your datafiles on disk #1 is corrupted. You go into your well-documented database records and find that this damaged datafile belongs to the system tablespace. In this situation, you decide that you must perform a closed database recovery.
The following simulation walks you through the steps to perform a closed database recovery. You may also preview all the steps.

Steps to Perform Closed Database Recovery

  1. If the database is open, shut it down using the command SHUTDOWN ABORT.
  2. Restore the damaged datafiles from the most recent backup files. Remember, do not restore any undamaged datafiles or online redo log files.
  3. Start the database in mount mode by issuing the STARTUP MOUNT command.
  4. To make sure all datafiles needing recovery are online during complete media recovery, issue the 
    ALTER DATABASE DATAFILE 'FILENAME' ONLINE;
    statement. If the specified datafile is already online, Oracle just ignores this statement.
  5. To start closed database recovery, issue the RECOVER DATABASE command for all damaged datafiles in one step, or the RECOVER DATAFILE command for an individual damaged datafile.
  6. Unless the application of redo log files is automated, Oracle will prompt you for each required redo log file.
  7. When recovery is finished, all datafiles are synchronized. Open the database by issuing the ALTER DATABASE OPEN; command.


After the recovery is finished, you must notify users to reenter any data that was not committed before the media failure.
Executing Closed Database Recovery

Perform a Closed Database Recovery

  1. If the database is open, shut it down using the SHUTDOWN ABORT. Type SHUTDOWN ABORT at the prompt and press Enter.
  2. To access the operating system, type HOST at the prompt and press Enter.
  3. To restore the damaged file, type COPY C:\DIGITAL\BACKUP\SYS1ORCL.ORA C:\ORANT\DATABASE\SYS1ORCL.ORA at the prompt and press Enter.
  4. Now you need to go back to Server Manager to start the database in mount mode. To access Server Manager, type EXIT at the prompt and press Enter.
  5. To startup the database in mount mode, type STARTUP MOUNT PFILE=D:\ORANT\DATABASE\INITORCL.ORA at the prompt and press Enter.
  6. To make sure that the datafile needing recovery is online during complete recovery, type ALTER DATABASE DATAFILE 
    C:\ORANT\DATABASE\SYS1ORCL.ORA' ONLINE;
    at the prompt and press Enter (If the specified datafile is already online, Oracle will ignore this statement).
  7. To start to recover the database, type 
    RECOVER DATAFILE C:\ORANT\DATABASE\SYS1ORCL.ORA;
    at the prompt and press Enter.
  8. To apply the suggested and subsequent archive logs to the restored file, type AUTO at the prompt and press Enter.
  9. Oracle automatically applies each logfile in sequence. Each logfile takes some time to apply, depending upon the volume of information it contains. This simulation lets you see the final result immediately. After media recovery is reported complete, you are ready to start the database. Type 
    ALTER DATABASE OPEN;

    at the prompt and press Enter.
  10. You have successfully recovered the datafile. This completes the Simulation. Click the Exit button to return to the lesson.
Read the following section to practice putting the steps in order for complete recovery of closed database.

Order Database Recovery Steps to recover a closed Database in Oracle

The correct order is as follows:
  1. Shut down the database.
  2. Restore the file from the backup.
  3. Start the database instance in mount mode.
  4. Make sure all the datafiles needing recovery are online.
  5. Recover the datafile using the recover command.
  6. Open the database.

Performing Closed Database Recovery

This section describes steps to perform complete recovery while the database is not open. You can recover either all damaged data files in one operation or perform individual recovery of each damaged data file in separate operations. To restore and recover damaged or missing data files:
  1. If the database is open, query V$RECOVER_FILE to determine which data files must be recovered and why they must be recovered. If you are planning to perform complete recovery rather than point-in-time recovery, then you can recover only those data files that require recovery, rather than the whole database. For point-in-time recovery, you must restore and recover all data files, unless you perform TSPITR "Performing RMAN Tablespace Point-in-Time Recovery (TSPITR)". You can also use Flashback Database, but this procedure affects all data files and returns the entire database to a past time. You can query the V$RECOVER_FILE view to list data files requiring recovery by data file number with their status and error information. 
    SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME
FROM V$RECOVER_FILE;

    Note: You cannot use V$RECOVER_FILE with a control file restored from backup or a control file that was re-created after the time of the media failure affecting the data files. A restored or re-created control file does not contain the information needed to update V$RECOVER_FILE accurately. You can also perform useful joins by using the data file number and the V$DATAFILE and V$TABLESPACE views to get the data file and tablespace names. Use the following SQL*Plus commands to format the output of the query: 
    COL DF# FORMAT 999
COL DF_NAME FORMAT A35
COL TBSP_NAME FORMAT A7
COL STATUS FORMAT A7
COL ERROR FORMAT A10
COL CHANGE# FORMAT 99999999
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#;

    The ERROR column identifies the problem for each file requiring recovery.
  2. Query the V$ARCHIVED_LOG and V$RECOVERY_LOG views to determine which archived redo log files are needed. V$ARCHIVED_LOG lists file names for all archived redo logs, whereas V$RECOVERY_LOG lists only the archived redo logs that the database needs to perform media recovery.
    The latter view also includes the probable names of the files based on the naming convention specified by using the LOG_ARCHIVE_FORMAT parameter.
    Note: V$RECOVERY_LOG is only populated when media recovery is required for a data file. Thus, this view is not useful for a planned recovery, such as recovery from a user error. If a data file requires recovery, but no backup of the data file exists, then you need all redo generated starting from the time when the data file was added to the database.
  3. If all archived logs are available in the default location, then skip to the Step 4. If some archived logs must be restored, and if sufficient 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, you might enter a command such as the following on Linux or UNIX: 
    % cp /disk2/arch/* $ORACLE_HOME/oradata/trgt/arch

    If sufficient space is not available, then restore some or all of the required archived redo log files to an alternative location.
  4. If the database is open, then shut it down. For example: 
    SHUTDOWN IMMEDIATE
  5. Inspect the media to determine the source of the problem. If the hardware problem that caused the media failure was temporary, and if the data was undamaged (for example, a disk or controller power failure occurred), then no media recovery is required: start the database and resume normal operations. If you cannot repair the problem, then proceed to the Step 6.
  6. If the files are permanently damaged, then identify the most recent backups for the damaged files. Restore only the data files damaged by the media failure: do not restore undamaged data files or any online redo log files. For example, if ORACLE_HOME/oradata/trgt/users01.dbf is the only damaged file, then you may find that /backup/users01_10_24_02.dbf is the most recent backup of this file. If you do not have a backup of a specific data file, then you may be able to create an empty replacement file that can be recovered.
  7. Use an operating system utility to restore the data files to their default location or to a new location. For example, a Linux or UNIX user restoring users01.dbf to its default location might enter: 
    % cp /backup/users01_10_24_06.dbf $ORACLE_HOME/oradata/trgt/users01.dbf

    Use the following guidelines when determining where to restore data file backups:
    1. If the hardware problem is repaired and you can restore the data files to their default locations, then restore the data files to their default locations and begin media recovery.
    2. If the hardware problem persists and you cannot restore data files to their original locations, then restore the data files to an alternative storage device. Indicate the new location of these files in the control file with the ALTER DATABASE RENAME FILE statement. See Oracle Database Administrator's Guide.
    3. If you are restoring a data file to a raw disk or partition, then the technique is basically the same as when you are restoring to a file on a file system. 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.
  8. Connect to the database with administrator privileges. Then start a new instance and mount, but do not open, the database. For example, enter: 
    STARTUP MOUNT
  9. If you restored one or more damaged data files to alternative locations, then update the control file of the database to reflect the new data file names. For example, to change the file name of the data file in tablespace users you might enter: 
    ALTER DATABASE RENAME FILE '?/oradata/trgt/users01.dbf' TO
'/disk2/users01.dbf';
  10. Obtain the data file names and statuses of all data files by checking the list of data files that normally accompanies the current control file or by querying the V$DATAFILE view. For example, enter: 
     SELECT NAME,STATUS FROM V$DATAFILE;
  11. Ensure that all data files requiring recovery are online. The only exceptions are data files in an offline tablespace that was taken offline normally or data files in a read-only tablespace. For example, to guarantee that a data file named /oracle/dbs/tbs_10.f is online, enter the following: 
    ALTER DATABASE DATAFILE '/oracle/dbs/tbs_10.f' ONLINE;
    If a specified data file is already online, then the database ignores the statement. If you prefer, create a script to bring all data files online at once, as in the following example: 
    SPOOL onlineall.sql
SELECT 'ALTER DATABASE DATAFILE '''||name||''' ONLINE;' FROM V$DATAFILE;
SPOOL OFF
SQL> @onlineall
  12. If you restored archived redo logs to an alternative location, then you can specify the location before media recovery with the LOGSOURCE parameter of the SET command in SQL*Plus. For example, if the logs are staged in /tmp, you can enter the following command: 
    SET LOGSOURCE /tmp
    Alternatively, you can skip Step 12 and use the FROM parameter on the RECOVER command as in Step 13. For example, if the logs are staged in /tmp, you can enter the following command: 
    RECOVER AUTOMATIC FROM '/tmp' DATABASE

    Note: Overriding the redo log source does not affect the archive redo log destination for online redo log groups being archived.
  13. Issue a statement to recover the database, tablespace, or data file. For example, enter one of the following RECOVER commands: 
    RECOVER AUTOMATIC DATABASE # whole database
RECOVER AUTOMATIC TABLESPACE users # specific tablespace
RECOVER AUTOMATIC DATAFILE '?/oradata/trgt/users01.dbf'; # specific data file

    If you choose not to automate the application of archived redo logs, then you must accept or reject each prompted log. If you automate recovery, then the database applies the logs automatically. Recovery continues until all required archived and online redo logs have been applied to the restored data files. The database notifies you when media recovery is complete: 
    Media recovery complete.

    If no archived redo logs are required for complete media recovery, then the database applies all necessary online redo log files and terminates recovery.
  14. After recovery terminates, open the database for use: 
    ALTER DATABASE OPEN;
  15. After archived logs are 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/*.arc