Media Recovery – Oracle

Executive Overview:

The media failure is the biggest threat for enterprise data management. A media failure is a physical problem that occurs when a computer unsuccessfully attempts to read from or write to a file necessary to operate the database. The technique you use to recover from media failure of a database file depends heavily on the type of media failure that occurred. The strategy you use to recover from a corrupted data file is different from the strategy for recovering from the loss of the control file. And also the important point needs to be remembered that the database is operating in archive log mode or no archive log mode. If the DB is operating in ARCHIVELOG mode, there are options for recovery depending on the type of failure. Broadly there are two types of media recovery:

· Complete media recovery

· Incomplete media recovery

Complete Media Recovery:

A complete media recovery will recover all lost changes to the database. You need to recover the entire database or a single data file which becomes corrupted. The dynamic view V$RECOVER_FILE determines what files requires recovery. The complete media recovery can be accomplished only if all online and archived redo log files are available. Complete media recovery can be performed on offline data files while the database is open and need to be opened with the RESETLOGS option if a backup control file or new control file was created for the recovery.

Types of complete media recovery are:

1. Closed Database Recovery

2. Open Database Recovery with Offline Tablespace

3. Open Database Recovery with Offline Datafile

1) Closed Database Recovery

If your system suffers a media failure and the database stays down, you need to perform a close database recovery.

Follow these steps to execute a closed database recovery:

a. Make sure the database is shutdown.

b. Correct the media problem if possible.

c. Restore the most recent backup of only those data files that were damaged by the media failure.

(There is no need to restore any undamaged data files or any online redo log files. If the hardware problem has been repaired, and damaged data files can be restored to their original locations, do so. If the hardware problem still exists, restore the data files to another location. This location will need to be recorded in the control file later in this procedure)

d. Start SQL*Plus and connect to Oracle as SYS.

e. Start the instance and mount it, but do not open the database.

f. If you restored lost files to alternate locations, the new location of these files must be recorded in the control file.

Follow these three steps to relocate the data file(s):

ü Make sure the tablespace that contain the data files are offline.

ü Make sure that the new, fully specified file names are different from the old file names.

ü Use the SQL command ALTER TABLESPACE with the RENAME DATAFILE option to change the file names within the database.

g. Query the V$DATAFILE view and make sure all data files you want to recover are online. If a data file is offline, issue the ALTER DATABASE command with the DATAFILE ONLINE option.

For example: ALTER DATABASE DATAFILE ‘userdata01.dbf’ ONLINE;

h. To start closed database recovery, use the RECOVER command.

Oracle will now start the roll forward by applying the archived redo log files and the online redo log file. If AUTORECOVERY is set to ON, the applying of the log files is automatic. If it is not set to ON, you will be prompted for each log file.

When recovery is complete, open the database with the ALTER DATABASE OPEN; command.

2) Open Database Recovery with Offline Tablespace

If your system suffers a media failure but the database stays up, you can perform an open database recovery and recover only the tablespaces that are damaged. Users can continue to access the online tablespaces and data files that were not damaged by the failure. Oracle automatically takes damaged data files offline.

Follow these steps to execute an open database recovery with offline tablespaces:

a. The database should be started and open.

b. Take all tablespaces containing damaged data files offline using the command: ALTER TABLESPACE tablespace_name OFFLINE

You can query the V$DATAFILE view to see which data files are offline.

c. Correct the problem that caused the media failure.

(If the problem cannot be corrected in a reasonable amount of time, your other option is to restore the damaged files to another location.)

Follow these three steps to relocate the data file(s):

ü Make sure the tablespace that contains the data files is offline.

ü Make sure that the new, fully specified file names are different from the old file names.

ü Use the ALTER DATABASE command with the RENAME FILE option to change the file names within the database.

(If the media problem can be corrected, restore the most recent backup files of only the data files damaged by the media failure. Remember that the database is open. Do NOT try to restore undamaged data files, log files, or control files.)

d. Use the RECOVER TABLESPACE command to start offline tablespace recovery of all damaged data files in one or more offline tablespaces.

Oracle will now start the roll forward by applying the archived redo log files and the online redo log file. If AUTORECOVERY is set to ON, the applying of the log files is automatic. If it is not set to ON, you will be prompted for each log file.

e. The damaged tablespaces of the open database are now recovered up to the point of failure. You can bring the offline tablespaces online using ALTER TABLESPACE command with the ONLINE option.

3) Open Database Recovery with Offline Datafile

If there is a media failure but the database stays up, you can perform an open database recovery with only the data files that are damaged. Users can continue to access the online tablespaces and data files that were not damaged by the failure. Oracle automatically takes damaged data files offline.

Follow these steps to execute an open database recovery with offline data files.

a. The database should be started and open.

b. Take all tablespaces containing damaged data files offline using the ALTER DATABASE DATAFILE ‘stuff01.dbf’ OFFLINE;

You can query the V$DATAFILE view to see which data files are offline.

c. Correct the problem that caused the media failure.

(If the problem cannot be corrected in a reasonable amount of time, your other option is to restore the damaged files to another location.)

Follow these three steps to relocate the datafile(s):

ü Make sure the tablespace that contains the data files is offline.

ü Make sure that the new, fully specified file names are different from the old file names.

ü Use the ALTER DATABASE command with the RENAME FILE option to change the file names within the database.

(If the media problem can be corrected, restore the most recent backup files of only the data files damaged by the media failure. Remember, the database is open. Do NOT try to restore undamaged data files, log files, or control files.)

d. Use the RECOVER DATAFILE command to start offline datafile recovery of all damaged data files in one or more offline data files tablespaces.

Oracle will now start the roll forward by applying the archived redo log files and the online redo log file. If AUTORECOVERY is set to ON, the applying of the log files is automatic. If it is not set to ON, you will be prompted for each log file.

e. The damaged data files of the open database are now recovered up to the point of failure. You can bring the offline data files tablespaces online using the ALTER DATABASE DATAFILE TABLESPACE command with the ONLINE option.

Incomplete Media Recovery:

In some situations complete media recovery may not be possible or may not be desired. This can happen because all the files needed for a complete recovery are not available (for example, all online redo log files are lost and you were not duplexing your log groups). You might want to perform an incomplete recovery if a user drops a table and you want to recover to the point before the table was dropped.

Incomplete media recovery is also called point-in-time recovery. Point in time recovery that is not continued to a complete recovery must be terminated by the OPEN RESETLOGS option. The database must be closed during incomplete recovery operations.

There are three types of incomplete media recovery:

1. Cancel-Based Recovery

2. Time-Based Recovery

3. Change-Based Recovery

1) Cancel-Based Recovery

Cancel-Based recovery allows canceling recovery at a desired point. This situation is most likely occur if archive log files or redo log files needed for recovery are lost or damaged and cannot be restored. In this situation, you would apply all logs until you reached the missing files and then cancel the recovery.

Follow these steps to execute a cancel-based recovery:

ü If the database is still open, shut down the database using the SHUTDOWN command with the ABORT option.

ü Make a full backup of the database, including all datafiles, a control file, and the parameter files in case an error is made during the recovery.

ü Correct the problem that caused the media failure.

(If the problem cannot be corrected, the datafiles must be restored to an alternate location. If this is the case, the ALTER TABLESPACE RENAME DATAFILE command must be used to change the location of the datafile in the control file.)

ü If the current control files do not match the physical structure of the database at the time you want to recover to, restore a backup of the control file that matches the database’s physical file structure at the point in time you want to recover to. Replace all current control files of the database with the one you want to use for recovery. If you do not have a backup copy of the control file, you can create a new one.

ü Restore backups of all datafiles. Make sure the backups were taken before the point in time you are going to recover to. Any datafiles added after the point in time you are recovering to should not be restored. They will not be used in the recovery and will have to be recreated after recovery is complete. Any data in the datafiles created after the point of recovery will be lost.

Note: Make sure read-only tablespace are offline before you start recovery so recovery does not try to update the datafile headers.

a. Start SQL*Plus and connect to Oracle as SYS.

b. Start the instance and mount the database using the STARTUP command with the MOUNT option.

c. If you restored files to an alternative location, change the location now in the control file by using the ALTER TABLESPACE RENAME DATAFILE command.

d. Use the RECOVER DATABASE UNTIL CANCEL command to begin cancel-based recovery. If a backup of the control file is being used, make sure to specify the USING BACKUP parameter.

Oracle will now start the roll forward by applying the archived redo logfiles and the online redo logfile. Oracle will prompt you for each logfile. If you used a backup control file, you must enter the names of the online redo logfiles.

e. Continue applying redo logfiles until the most recent, undamaged logfile has been applied.

Enter “CANCEL” instead of the logfile name to cancel the recovery. Oracle will respond with a recovery successful message.

f. Use the ALTER DATABASE OPEN command with the RESETLOGS or NORESETLOGS option.

You should use the RESETLOGS option if you used a backup of the control file in recovery, or the recovery was incomplete. Use the NORESETLOGS option if the recovery was complete. If you are using a standby database and must reset the logs, the standby database will have to be re-created.

You can check the ALERT file to see if your incomplete recovery was actually a complete recovery.

If the recovery was a complete recovery, the message in the ALERT file is as follows:

RESETLOGS after complete recovery through change scn

If the recovery was incomplete, the following message is recorded:

RESETLOGS after incomplete recovery UNTIL CHANGE scn

g. After opening the database using the RESETLOGS option, perform a normal shutdown and a full database backup. If you do not do this, any changes made after the recovery and before the next full backup are unrecoverable. If you did not reset the logs, the database is still recoverable.

2) Time based Recovery

To recover the database to the time specified by the date. The date must be a character literal in the format ‘YYYY-MM-DD:HH24:MI:SS’.

ü If the database is still open, shut down the database using the SHUTDOWN command with the ABORT option.

ü Back up the database as a precaution and correct any media failures.

ü Restore backup control files (if necessary) and backup data files and bring them online.

Question: You do not have a backup of a data file	Answer: Create an empty replacement file, which can be recovered.

ü Perform media recovery on the restored backup using the RECOVER DATABASE statement with the UNTIL TIME option.

Note: Files in read-only tablespaces should be offline if you are using a control file backup. Otherwise, the recovery will try to update the headers of the read-only files.

a. Start SQL*Plus and connect to Oracle as SYS.

b. Start the instance and mount the database using the STARTUP command with the MOUNT option.

c. If one or more damaged data files were restored to alternative locations, indicate the new locations of these files to the control file of the associated database. For example, enter:

ALTER DATABASE RENAME FILE ‘/oracle/dbs/df2.f’ TO ‘/oracle/newloc/df2.f’;

d. Obtain the names of all data files requiring recovery by:

ü Checking the list of data files that normally accompanies the control file being used.

ü Querying the V$DATAFILE view.

e. Make sure that all data files of the database are online. All data files of the database must be online unless an offline tablespace was taken offline normally. For example:

ALTER DATABASE DATAFILE ‘users1’ ONLINE;

f. Issue the RECOVER DATABASE UNTIL TIME statement to begin time-based recovery. The time is always specified using the following format, delimited by single quotation marks: ‘YYYY-MM-DD:HH24:MI:SS’.

The following statement recovers the database up to a specified time using a control file backup:

RECOVER DATABASE UNTIL TIME ‘2007-12-31:12:47:30’ USING BACKUP CONTROLFILE;

g. Apply the necessary redo log files to reconstruct the restored data files. Unless the application of files is automated, Oracle supplies the name it expects to find from LOG_ARCHIVE_DEST and requests you to stop or proceed with applying the log file. If the control file is a backup, you must supply names of online logs.

h. Apply redo log files until the last required redo log file has been applied to the restored data files. Oracle automatically terminates the recovery when it reaches the correct time, and returns a message indicating whether recovery is successful.

3) Changed Based Recovery

This recovers the database to a transaction consistent state immediately prior to the system change number (SCN) specified by integer.

This describes how to perform recovery to a specified SCN in these stages:

ü If the database is still open, shut down the database using the SHUTDOWN command with the ABORT option.

ü Back up the database as a precaution and correct any media failures.

ü Restore backup data files.

a. Start SQL*Plus and connect to Oracle as SYS.

b. Start the instance and mount the database using the STARTUP command with the MOUNT option.

c. If one or more damaged data files were restored to alternative locations, indicate the new locations of these files to the control file of the associated database.

Note: To determine the SCN needed for recovery, you can query the V$LOG_HISTORY view.

For example: Select * from v$log_history where rownum < 3;

THREAD# SEQUENCE# TIME LOW_CHANGE# HIGH_CHANGE# ARCHIVE_NAME

1 47213 06/26/98 12:47:55 116098950 116098954 /u01/arch/log_47213.arc

1 47212 06/26/98 12:47:33 116098947 116098949 /u01/arch/log_47212.arc

The LOW_CHANGE# column represents the SCN at the beginning of the log file and the HIGH_CHANGE# represents the SCN at the end of the log file.

d. Begin change-based recovery, specifying the SCN for recovery termination. The SCN is specified as a decimal number without quotation marks. For example, to recover until SCN 116098954 issue:

RECOVER DATABASE UNTIL CHANGE 116098954;

e. Oracle begins the roll forward phase of media recovery by applying the necessary redo log files (archived and online) to reconstruct the restored data files. Unless the application of files is automated, Oracle supplies the name it expects to find from LOG_ARCHIVE_DEST and requests you to stop or proceed with applying the log file. If the control file is a backup file, you must supply names of online logs.

f. Oracle apply the redo log files until the last required redo log file has been applied to the restored data files. Oracle automatically terminates the recovery when it reaches the correct system change number, and returns a message indicating whether recovery is successful.

CONCLUSION

The practices described above are a checklist to ensure that media recovery is being organized in a right direction. Correct media recovery leads to maximum availability and minimize recovery time. This equates to more uptime and higher availability in the case of an unplanned or planned outage and helps enterprises meet the SLAs associated with recovery time objectives.

Reference: http://www.stanford.edu