Release 2 (8.1.6)
Part Number A76993-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Backup and Recovery Guide Release 2 (8.1.6) Part Number A76993-01 |
|
This chapter describes how to recover a database, and includes the following topics:
You can often use the table V$RECOVER_FILE to determine which files to recover. 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/time when recovery needs to begin:
SQL> SELECT * FROM v$recover_file; FILE# ONLINE ERROR CHANGE# TIME ---------- ------- ------------------ ---------- --------- 14 ONLINE 0 15 ONLINE FILE NOT FOUND 0 21 OFFLINE OFFLINE NORMAL 0
Query V$DATAFILE and V$TABLESPACE to obtain filenames and tablespace names for datafiles requiring recovery. For example, enter:
SQL> SELECT d.name, t.name 2 FROM v$datafile d, v$tablespace t 3 WHERE t.ts# = d.ts# 4 AND d.file# in (14,15,21); # use values obtained from V$RECOVER_FILE query NAME TABLESPACE_NAME ---------------------------------- ---------------- /oracle/dbs/tbs_14.f TBS_1 /oracle/dbs/tbs_15.f TBS_2 /oracle/dbs/tbs_21.f TBS_3
You can combine these queries in the following SQL*Plus script (sample output shown below):
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# / SQL> @script DF# DF_NAME TBSP_NAME STATUS ERROR CHANGE# TIME ---- -------------------- ---------- ------- ---------- ----------- ---------- 12 /oracle/dbs/tbs_41.f TBS_4 OFFLINE OFFLINE 0 NORMAL 13 /oracle/dbs/tbs_42.f TBS_4 OFFLINE OFFLINE 0 NORMAL 20 /oracle/dbs/tbs_43.f TBS_4 OFFLINE OFFLINE 0 NORMAL
Besides determining which files to recover, you must also know which files you should not recover. The following have special implications for media recovery:
You can create tables and indexes using the CREATE TABLE AS SELECT statement. You can also specify that Oracle create them as unrecoverable. When you create a table or index as unrecoverable, Oracle does not generate redo log records for the operation. Thus, you cannot recover objects created unrecoverable, even if you are running in ARCHIVELOG mode.
Be aware that when you perform media recovery, and some tables or indexes are created as recoverable while others are unrecoverable, the unrecoverable objects are marked logically corrupt by the RECOVER operation. Any attempt to access the unrecoverable objects returns an ORA-01578 error message. Drop the unrecoverable objects and re-create them if needed.
Because it is possible to create a table unrecoverable and then create a recoverable index on that table, the index is not marked as logically corrupt after you perform media recovery. The table was unrecoverable (and thus marked as corrupt after recovery), however, so the index points to corrupt blocks. The index must be dropped, and the table and index must be re-created if necessary.
|
See Also:
Oracle8i Standby Database Concepts and Administration for information about the impact of unrecoverable operations on a standby database. |
Media recovery with the USING BACKUP CONTROLFILE option checks for read-only files. You cannot recover a read-only file. To avoid this error, take datafiles from read-only tablespaces offline before doing recovery with a backup control file.
Use the correct version of the control file for the recovery. If the tablespace will be read-only when the recovery is complete, then the control file must be from a time when the tablespace was read-only. Similarly, if the tablespace will be read-write at the end of recovery, it should be read-write in the control file.
If the appropriate control file is unavailable, execute a CREATE CONTROLFILE statement as described in "Losing All Copies of the Current Control File". If you need to re-create a control file for a database with read-only tablespaces, issue the following to obtain the procedure that you need to follow:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
The procedure is similar to the procedure for offline normal tablespaces, except that you need to bring the tablespace online after the database is open.
|
See Also:
"Backing Up the Control File to a Trace File" to learn about taking trace backups of the control file. |
If you determine that media recovery is necessary, restore the files necessary to perform it. Learn how to execute the following tasks:
If a media failure permanently damages one or more datafiles of a database, you must restore backups of the damaged 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 of the associated database.
tbs_14.f on UNIX you might issue:
% cp /disk2/backup/tbs_14.bak /disk1/oracle/dbs/tbs_14.f
If a datafile is damaged and no backup of the file is available, you can still recover the datafile if:
disk1:users1 has been damaged, and no backup is available. The following statement re-creates the original datafile (same size) on disk2:
ALTER DATABASE CREATE DATAFILE 'disk1:users1' AS 'disk2:users1';
This statement creates an empty file that matches the lost file. Oracle looks at information in the control file and the data dictionary to obtain size information. The old datafile is renamed as the new datafile.
RECOVER DATAFILE 'disk2:users1'
All archived redo log files required for the pending media recovery eventually need to be on disk so that they are readily available to Oracle.
For example, enter:
% cp /disk2/arc_backup/*.arc /disk1/oracle/dbs/arc_dest
SET LOGSOURCE /disk2/temp # set location using SET statement ALTER DATABASE RECOVER FROM "/disk2/temp" DATABASE; # set in RECOVER statement
% rm *.arc
Before beginning recovery, familiarize yourself with the following topics:
Oracle uses these basic media recovery SQL*Plus statements, which differ only in the way the system determines the set of files to be recovered:
Each statement uses the same criteria to determine whether files are recoverable. Oracle prevents two recovery sessions from recovering the same file and prevents media recovery of a file that is in use.
You can also use the SQL statement ALTER DATABASE RECOVER, although Oracle strongly recommends you use the SQL*Plus RECOVER statement instead so that Oracle will prompt you for the names of the archived redo logs.
|
See Also:
SQL*Plus User's Guide and Reference for more information about SQL*Plus RECOVER statements, and Oracle8i SQL Reference for more information about the ALTER DATABASE RECOVER statement. |
RECOVER DATABASE performs media recovery on all online datafiles that require redo to be applied. For example, issue the following at the SQL prompt to recover the whole database:
RECOVER DATABASE
If you shut down all instances cleanly, and did not restore any backups, issuing RECOVER DATABASE returns an error indicating that no recovery is required. It also fails if any instances have the database open, since they have the datafile locks. To perform media recovery on an entire database, the database must be mounted EXCLUSIVE and closed.
RECOVER TABLESPACE performs media recovery on all datafiles in the tablespaces listed. For example, enter the following at the SQL prompt to recover tablespace TBS_1:
RECOVER TABLESPACE tbs_1
The tablespaces must be offline to perform the recovery. Oracle indicates an error if none of the files require recovery.
RECOVER DATAFILE lists the datafiles to be recovered. For example, enter the following at the SQL prompt to recover datafile /oracle/dbs/tbs_22.f:
RECOVER DATAFILE '/oracle/dbs/tbs_22.f'
The database can be open or closed, provided that you can acquire the media recovery locks. If the database is open in any instance, then datafile recovery can only recover offline files.
During complete or incomplete media recovery, Oracle applies redo log files to the datafiles during the roll forward phase of media recovery. Because rollback data is recorded in the redo log, rolling forward regenerates the corresponding rollback segments. Rolling forward proceeds through as many redo log files as necessary to bring the database forward in time.
As a log file is needed, Oracle suggests the name of the file. For example, if you are using SQL*Plus, it returns the following lines and prompts:
ORA-00279: Change #### generated at DD/MM/YY HH:MM:SS needed for thread# ORA-00289: Suggestion : logfile ORA-00280: Change #### for thread # is in sequence # Specify log: [<RET> for suggested | AUTO | FROM logsource | CANCEL ]
Similar messages are returned when you use an ALTER DATABASE ... RECOVER statement. However, no prompt is displayed.
Oracle suggests archived redo log filenames by concatenating the current values of the initialization parameters LOG_ARCHIVE_DEST_1 or LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT and using information from the control file. For example, the following are possible settings for archived logs:
LOG_ARCHIVE_DEST_1 = /oracle/arc_dest/arc LOG_ARCHIVE_FORMAT = r_%t_%s.arc SQL> SELECT name FROM v$archived_log; NAME ------------------------------- /oracle/arc_dest/arcr_1_467.arc /oracle/arc_dest/arcr_1_468.arc /oracle/arc_dest/arcr_1_469.arc /oracle/arc_dest/arcr_1_470.arc
Thus, if all the required archived log files are mounted at the LOG_ARCHIVE_DEST_1 or LOG_ARCHIVE_DEST destination, and the value for LOG_ARCHIVE_FORMAT is never altered, Oracle can suggest and apply log files to complete media recovery automatically.
LOG_ARCHIVE_DEST_1 = /oracle/new_location
% cp /oracle/arc_dest/* /oracle/new_location
STARTUP MOUNT
RECOVER DATABASE
In some cases, you may want to override the current setting for the destination parameter as a source for redo log files. For example, assume that a database is open and an offline tablespace must be recovered, but not enough space is available to mount the necessary redo log files at the location specified by the destination parameter.
% cp /disk1/oracle/arc_dest/* /disk2/temp
SET LOGSOURCE "/disk2/temp"
RECOVER TABLESPACE offline_tbsp
Consider overriding the current setting for the destination parameter when not enough space is available to mount all the required log files at any one location. In this case, you can set the log file source to an operating system variable (such as a logical or an environment variable) that acts as a search path to several locations.
When using SQL*Plus, use the following statement to automate the application of the default filenames of archived redo logs needed during recovery:
SET AUTORECOVERY ON
No interaction is required when you issue the RECOVER statement, provided that the necessary files are in the correct locations with the correct names.
The filenames used when you use SET AUTORECOVERY ON are derived from the values of the initialization parameters LOG_ARCHIVE_DEST or LOG_ARCHIVE_DEST_1 in conjunction with LOG_ARCHIVE_FORMAT. If you execute SET AUTORECOVERY OFF, which is the default option, then you must enter the filenames manually, or accept the suggested default filename.
% cp /oracle/work/BACKUP/tbs* /oracle/dbs
SQL> STARTUP MOUNT
SQL> SET AUTORECOVERY ON Autorecovery ON
SQL> RECOVER DATABASE
ORA-00279: change 53577 generated at 01/26/99 19:20:58 needed for thread 1 ORA-00289: suggestion : /oracle/work/arc_dest/arcr_1_802.arc ORA-00280: change 53577 for thread 1 is in sequence #802 Log applied. ORA-00279: change 53584 generated at 01/26/99 19:24:05 needed for thread 1 ORA-00289: suggestion : /oracle/work/arc_dest/arcr_1_803.arc ORA-00280: change 53584 for thread 1 is in sequence #803 ORA-00278: log file "/oracle/work/arc_dest/arcr_1_802.arc" no longer needed for this recovery Log applied. ORA-00279: change 53585 generated at 01/26/99 19:24:14 needed for thread 1 ORA-00289: suggestion : /oracle/work/arc_dest/arcr_1_804.arc ORA-00280: change 53585 for thread 1 is in sequence #804 ORA-00278: log file "/oracle/work/arc_dest/arcr_1_803.arc" no longer needed for this recovery Log applied. Media recovery complete.
If you use an OPS configuration, and you are performing incomplete recovery or using a backup control file, then Oracle can only compute the name of the first archived redo log file from the first thread. You may have to apply the first log file from the other threads. Once the first log file in a given thread has been supplied, Oracle can suggest the names of the subsequent logfiles in those threads.
When you perform media recovery using SQL statements, Oracle does not display a prompt for log files after media recovery is started. Instead, you must provide the correct log file using an ALTER DATABASE RECOVER LOGFILE statement. For example, if a message suggests log1.arc, apply the suggestion using the following statement:
ALTER DATABASE RECOVER LOGFILE 'log1.arc';
As a result, recovering a tablespace requires several statements, as indicated in the following example (DBA input is boldfaced; variable information is italicized.):
SQL> ALTER DATABASE RECOVER TABLESPACE users;
ORA-00279: Change #### generated at DD/MM/YY HH:MM:SS needed for thread #
ORA-00289: Suggestion : logfile1
ORA-00280: Change #### for thread # is in sequence #
SQL> ALTER DATABASE RECOVER LOGFILE 'logfile1';
ORA-00279: Change #### generated at DD/MM/YY HH:MM:SS needed for thread # <D%0>
ORA-00289: Suggestion : logfile2
ORA-00280: Change #### for thread # is in sequence #
SQL> ALTER DATABASE RECOVER LOGFILE 'logfile2';
. . .
Repeat until all logs are applied.)
Statement processed.
SQL> ALTER TABLESPACE users ONLINE;
Statement processed.
In this example, assume that the backup files have been restored, and that the user has administrator privileges. As in the method you used with SQL*Plus, automatic application of the redo logs can be started with the following statements, before and during recovery, respectively:
ALTER DATABASE RECOVER AUTOMATIC ...; ALTER DATABASE RECOVER AUTOMATIC LOGFILE suggested_log_filename;
An example of the first statement follows:
SQL>ALTER DATABASE RECOVER AUTOMATIC TABLESPACE users;Statement processed. SQL>ALTER TABLESPACE users ONLINE;Statement processed.
In this example, it is assumed that the backup files have been restored, and that the user has administrator privileges.
An example of the ALTER DATABASE RECOVER AUTOMATIC LOGFILE statement follows:
SQL> ALTER DATABASE RECOVER TABLESPACE users; ORA-00279: Change #### generated at DD/MM/YY HH:MM:SS needed for thread # ORA-00289: Suggestion : logfile1 ORA-00280: Change #### for thread # is in sequence # SQL> ALTER DATABASE RECOVER AUTOMATIC LOGFILE 'logfile1'; Statement processed. SQL> ALTER TABLESPACE users ONLINE; Statement processed.
In this example, assume that the backup files have been restored, and that the user has administrator privileges.
If you are using SQL*Plus's recovery options (not SQL statements), each time Oracle finishes applying a redo log file, the following message is returned:
Log applied.
Oracle then prompts for the next log in the sequence or, if the most recently applied log is the last required log, terminates recovery.
If the suggested file is incorrect or you provide an incorrect filename, Oracle returns an error message. For example, you may see something similar to the following:
ORA-00308: cannot open archived log "/oracle/work/arc_dest/arcr_1_811.arc" ORA-27037: unable to obtain file status SVR4 Error: 2: No such file or directory Additional information: 3
Recovery cannot continue until the required redo log file is applied. If Oracle returns an error message after supplying a redo log filename, the following scenarios are possible:
If you start a media recovery operation and must then interrupt it, for example, because a recovery operation must end for the night and resume the next morning, interrupt recovery at any time by taking either of the following actions:
After recovery is canceled, it must be completed before opening a database for normal operation. To resume recovery, restart it. Recovery resumes where it left off when it was canceled.
There are several reasons why, after starting recovery, you may want to restart. For example, if you want to restart with a different backup or want to use the same backup but need to change the end time to an earlier point in time than you initially specified, then the entire operation must recommence by restoring a backup. Failure to do so may result in "file inconsistent" error messages when attempting to open the database.
If a database is in NOARCHIVELOG mode and a media failure damages some or all of the datafiles, the only option for recovering the database is usually to restore the most recent whole database backup. If you are using Export to supplement regular backups, then you can instead restore the database by importing an exported backup of the database.
The disadvantage of NOARCHIVELOG mode is that to recover your database from the time of the most recent full backup up to the time of the media failure, you have to re-enter manually all of the changes executed in that interval. If your database was in ARCHIVELOG mode, however, the redo log covering this interval would have been available as archived log files or online log files. Using archived redo logs would have enabled you to use complete or incomplete recovery to reconstruct your database, thereby minimizing the amount of lost work.
If you have a database damaged by media failure and operating in NOARCHIVELOG mode, and you want to restore from your most recent consistent whole database backup (your only option at this point), follow the steps below.
SHUTDOWN ABORT
% cp /oracle/work/BACKUP/tbs* /oracle/dbs # restores datafiles % cp /oracle/work/BACKUP/cf.f /oracle/dbs # restores control file
ALTER DATABASE RECOVER DATABASE UNTIL CANCEL;
ALTER DATABASE OPEN RESETLOGS;
A RESETLOGS operation invalidates all redo in the online logs. Restoring from a whole database backup and then resetting the log discards all changes to the database made from the time the backup was taken to the time of the failure.
SHUTDOWN NORMAL
% cp /disk2/BACKUP/tbs* /disk3/oracle/dbs % cp /disk2/BACKUP/cf.f /disk3/oracle/dbs
CONTROL_FILES = "/disk3/oracle/dbs/cf.f"
initPROD1.ora:
STARTUP MOUNT pfile=initPROD1.ora
ALTER DATABASE RENAME FILE "/disk1/oracle/dbs/tbs1.f" TO "/disk3/oracle/dbs/tbs1.f";
ALTER DATABASE RENAME FILE "/disk1/oracle/dbs/log1.f" TO "/disk3/oracle/dbs/log1.f";
ALTER DATABASE RECOVER DATABASE UNTIL CANCEL;
ALTER DATABASE RECOVER CANCEL;
ALTER DATABASE OPEN RESETLOGS;
A RESETLOGS operation invalidates all redo in the online logs. Restoring from a whole database backup and then resetting the log discards all changes to the database made from the time the backup was taken to the time of the failure.
|
See Also:
Oracle8i Administrator's Guide for more information about renaming and relocating datafiles. |
To begin media recovery operations when your database is running in ARCHIVELOG mode, use one of the following options:
To start any type of media recovery, you must adhere to the following restrictions:
Use parallel block recovery to tune the roll forward phase of media recovery. In parallel block recovery, Oracle uses a "division of labor" approach to allocate different processes to different data blocks while rolling forward, thereby making the procedure more efficient. For example, if the redo log contains a substantial number of entries, spawned process 1 takes responsibility for one part of the log file, process 2 takes responsibility for another part, process 3 takes responsibility for a third part, etc.
Use the following SQL*Plus RECOVER statement to perform parallel media recovery:
RECOVER PARALLEL ... ;
The PARALLEL clause of the RECOVER statement has the following options:
For example, to specify that 5 recovery processes should operate during recovery, specify as follows:
RECOVER DEGREE 5 ... ;
In a different scenario, assume that you are recovering 10 datafiles. Issue the following statement to specify that 20 processes should perform recovery:
RECOVER DEGREE DEFAULT;
|
See Also:
|
When you perform complete recovery, you can either recover the whole database at once or recover individual tablespaces or datafiles. Because you do not have to open the database with the RESETLOGS option after complete recovery as you do after incomplete recovery, you have the option of recovering some datafiles at one time and the remaining datafiles later.
This section describes the steps necessary to complete media recovery operations, and includes the following topics:
Chapter 3, "Developing a Backup and Recovery Strategy" to familiarize yourself with fundamental recovery concepts and strategies
See Also:
This section describes steps to perform closed database recovery of either all damaged datafiles in one operation, or individual recovery of each damaged datafile in separate operations.
Perform the media recovery in these stages:
SHUTDOWN ABORT
STARTUP
For example, if /oracle/dbs/tbs_10.f is the damaged file, you may consult your records and determine that /oracle/backup/tbs_10.backup is the most recent backup. If you do not have a backup of a specific datafile, you may be able to create an empty replacement file that can be recovered.
/oracle/dbs/tbs_10.f to its default location might enter:
% cp /oracle/backup/tbs_10.backup /oracle/dbs/tbs_10.f
Follow these guidelines when determining where to restore datafile backups:
| If... | Then... |
|---|---|
|
The hardware problem is repaired and you can restore the datafiles to their default locations |
Restore the datafiles to their default locations and begin media recovery. |
|
The hardware problem persists and you cannot restore datafiles to their original locations |
Restore the datafiles to an alternative storage device. Indicate the new location of these files to the control file. Use the operation described in "Renaming and Relocating Datafiles" in the Oracle8i Administrator's Guide, as necessary. |
STARTUP MOUNT
SELECT name FROM v$datafile;
/oracle/dbs/tbs_10.f is online, enter the following:
ALTER DATABASE DATAFILE '/oracle/dbs/tbs_10.f' ONLINE;
If a specified datafile is already online, Oracle ignores the statement. If you prefer, create a script to bring all datafiles online at once as in the following:
SPOOL onlineall.sql SELECT 'ALTER DATABASE DATAFILE '''||name||''' ONLINE;' FROM v$datafile; SPOOL OFF @onlineall
RECOVER DATABASE # recovers whole database RECOVER TABLESPACE users # recovers specific tablespace RECOVER DATAFILE '/oracle/dbs/tbs_10'; # recovers specific datafile
Follow these guidelines when deciding which statement to execute:
ALTER DATABASE OPEN;
It is possible for a media failure to occur while the database remains open, leaving the undamaged datafiles online and available for use. Oracle automatically takes the damaged datafiles offline--but not the tablespaces that contain them--if DBWR fails to be able to write to them. Queries that cannot read damaged files receive errors, but Oracle does not take the files offline for this reason alone.
This procedure cannot be used to perform complete media recovery on the datafiles of the SYSTEM tablespace. If the media failure damages any datafiles of the SYSTEM tablespace, Oracle automatically shuts down the database.
Perform media recovery in these stages:
"Performing Closed Database Recovery" for procedures for proceeding with complete media recovery of SYSTEM tablespaces datafiles
See Also:
STARTUP
ALTER TABLESPACE tbs_1 OFFLINE TEMPORARY;
ALTER TABLESPACE tbs_1 OFFLINE TEMPORARY;
% sqlplus sys/oracle@prod1
RECOVER TABLESPACE tbs_1 # begins recovery on datafiles in tbs_1
|
Note: For maximum performance, use parallel recovery to recover the datafiles. See"Performing Media Recovery in Parallel". |
Oracle continues until all required archived redo log files have been applied to the restored datafiles. The online redo log files are then automatically applied to the restored datafiles to complete media recovery.
If no archived redo log files are required for complete media recovery, Oracle does not prompt for any. Instead, all necessary online redo log files are applied, and media recovery is complete.
ALTER TALBESPACE tbs_1 ONLINE;
This section describes the steps necessary to complete the different types of incomplete media recovery operations, and includes the following topics:
Note that if your database is affected by seasonal time changes (for example, daylight savings time), you may experience a problem if a time appears twice in the redo log and you want to recover to the second, or later time. To deal with time changes, perform cancel-based or change-based recovery to the point in time where the clock is set back, then continue with the time-based recovery to the exact time.
This section describes how to perform cancel-based media recovery in these stages:
SHUTDOWN ABORT
The restored control file should reflect the database's physical file structure, that is, contain the names of datafiles and online redo log files, at the point at which incomplete media recovery is intended to finish. Review the list of files that correspond to the current control file as well as each control file backup to determine the correct control file to use.
If necessary, replace all current control files of the database with the correct control file backup. Alternatively, create a new control file.
% sqlplus sys/change_on_install@prod1
STARTUP MOUNT
RECOVER DATABASE UNTIL CANCEL
If you are using a backup control file with this incomplete recovery, specify the USING BACKUP CONTROLFILE option in the RECOVER statement.
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
CANCEL
Oracle returns a message indicating whether recovery is successful. Note that if you cancel recovery before it is complete and then try to open the database, you will get an ORA-1113 error if more recovery is necessary for the file.
This section describes how to perform the time-based media recovery procedure in these stages:
Follow the same preparation procedure described in the section "Performing Cancel-Based Recovery".
| If... | Then... |
|---|---|
|
You do not have a backup of a datafile |
Create an empty replacement file, which can be recovered. |
|
A datafile was added after the intended time of recovery |
Do not restore a backup of this file, since it will no longer be used for the database after recovery completes. |
|
The hardware problem causing the failure has been solved and all datafiles can be restored to their default locations |
Restore the files and skip Step 5 of this procedure. |
|
A hardware problem persists |
Restore damaged datafiles to an alternative storage device. |
% sqlplus sys/change_on_install@prod1
STARTUP MOUNT
ALTER DATABASE RENAME FILE "/oracle/dbs/df2.f" TO "/oracle/newloc/df2.f";
user1 (a fully specified filename) is online, enter the following statement:
ALTER DATABASE DATAFILE 'users1' ONLINE;
If a backup of the control file is being used with this incomplete recovery (that is, a control file backup or re-created control file was restored), indicate this in the dialog box or command used to start recovery. If a specified datafile is already online, Oracle ignores the statement.
RECOVER DATABASE UNTIL TIME '1992-12-31:12:47:30' USING BACKUP CONTROLFILE
This section describes how to perform recovery to a specified SCN in these stages:
Follow the same preparation procedure described in the section "Performing Cancel-Based Recovery".
Follow the same restore procedure described in the section "Performing Time-Based Recovery".
RECOVER DATABASE UNTIL CHANGE 100;
Whenever you perform incomplete recovery or perform recovery using a backup control file, you must reset the online redo logs when you open the database. The new version of the reset database is called a new incarnation. All archived redo logs generated after the point of the RESETLOGS on the old incarnation are invalid in the new incarnation.
If you perform complete recovery, then you do not have to open the database with the RESETLOGS option: you simply open the database as normal. All previous backups and archived logs created during the lifetime of this incarnation of the database are still valid.
This section contains the following topics:
Whenever you open the database with the RESETLOGS option, all datafiles get a new RESETLOGS SCN and timestamp. Archived redo logs also have these two values in their header. Because Oracle will not apply an archived redo log to a datafile unless the RESETLOGS SCN and timestamps match, the RESETLOGS operations prevents you from corrupting your datafiles with old archived logs.
Figure 5-1 shows the case of a database that can only be recovered to SCN 2500 because an archived redo log is missing. At SCN 4000, the database crashes. You restore the SCN 1000 backup and prepare for complete recovery. Unfortunately, one of your archived redo logs is corrupted. The log before the missing log contains SCN 2500, so you recover to this point and open with the RESETLOGS option.
As the diagram illustrates, you generate new changes in the new incarnation of the database, eventually reaching SCN 4000. The changes between SCN 2500 and SCN 4000 for the new incarnation of the database are completely different from the changes between SCN 2500 and SCN 4000 for the old incarnation. Oracle does not allow you to apply logs from an old incarnation to the new incarnation. You cannot restore backups from before SCN 2500 in the old incarnation to the new incarnation.
The RESETLOGS option is required after incomplete media recovery or recovery using a backup control file. Resetting the redo log:
Use the following rules when deciding whether to specify RESETLOGS or NORESETLOGS:
To preserve the log sequence number when opening a database after media recovery, execute the following statement:
ALTER DATABASE OPEN NORESETLOGS;
To reset the log sequence number when opening a database after recovery, execute the following statement:
ALTER DATABASE OPEN RESETLOGS;
If you open with the RESETLOGS option, Oracle returns different messages depending on whether recovery was complete or incomplete. If the recovery was complete, the following message appears in the alert.log file:
RESETLOGS after complete recovery through change scn
If the recovery was incomplete, this message is reported in the ALERT file:
RESETLOGS after incomplete recovery UNTIL CHANGE scn
If you attempt to reset the log when you should not, or if you neglect to reset the log when you should, Oracle returns an error and does not open the database. Correct the error and try again.
This section describes actions that you should perform after opening the database in RESETLOGS mode:
Immediately shut down the database normally and make a full database backup. Otherwise, you cannot recover changes made after you reset the logs. Until you take a full backup, the only way to recover is to repeat the procedures you just finished, up to resetting the logs. You do not need to back up the database if you did not reset the log sequence.
In general, backups made before a RESETLOGS operation are not allowed in the new incarnation. There is, however, an exception to the rule: you can restore a pre-RESETLOGS backup only if Oracle does not need to access archived redo logs from before the RESETLOGS to perform recovery.
After opening the database using the RESETLOGS option, check the alert.log to see whether Oracle detected inconsistencies between the data dictionary and the control file, for example, a datafile that the data dictionary includes but does not list in the new control file.
If a datafile exists in the data dictionary but not in the new control file, Oracle creates a placeholder entry in the control file under MISSINGnnnn (where nnnn is the file number in decimal). MISSINGnnnn is flagged in the control file as being offline and requiring media recovery.
The actual datafile corresponding to MISSINGnnnn can be made accessible by renaming MISSINGnnnn so that it points to the datafile only if the datafile was read-only or offline normal. If, on the other hand, MISSINGnnnn corresponds to a datafile that was not read-only or offline normal, then the rename operation cannot be used to make the datafile accessible, because the datafile requires media recovery that is precluded by the results of RESETLOGS. In this case, you must drop the tablespace containing the datafile.
In contrast, if a datafile indicated in the control file is not present in the data dictionary, Oracle removes references to it from the new control file. In both cases, Oracle includes an explanatory message in the alert.log file to let you know what was found.
In pre-Oracle8 releases, DBAs typically backed up online logs when performing cold consistent backups to avoid opening the database with the RESETLOGS option (if they were planning to restore immediately).
A classic example of this technique was disk maintenance, which required the database to be backed up, deleted, the disks reconfigured, and the database restored. DBAs realized that by not restarting in RESETLOGS mode, they would not have to perform a whole database backup immediately after the database was restored. This backup was required since it was impossible to perform recovery using a backup taken before using RESETLOGS--especially if any errors occurred after resetting the logs.
It is possible to restore the following types of pre-RESETLOGS backups in a new incarnation:
You are prevented from restoring backups of read-write tablespaces that were not made immediately before the RESETLOGS. This restriction applies even if no changes were made to the datafiles in the read-write tablespace between the backup and the RESETLOGS. Because the checkpoint in the datafile header of a backup will be older than the checkpoint in the control file, Oracle has to search the archived logs to determine whether changes need to be applied--and the pre-RESETLOGS archived logs are not valid in the new incarnation.
The following scenario illustrates a situation when you can use a pre-RESETLOGS backup. Suppose you wish to perform hardware striping reconfiguration, which requires the database files to be backed up and deleted, the hardware reconfigured, and the database restored.
On Friday night you perform the following actions:
SHUTDOWN IMMEDIATE
% cp /oracle/dbs/* /oracle/backup
% cp /oracle/backup/* /oracle/dbs
STARTUP MOUNT
RECOVER DATABASE UNTIL CANCEL
ALTER DATABASE OPEN RESETLOGS;
On Saturday morning the scheduled batch jobs run, generating archived redo logs. If a hardware error occurs on Saturday night that requires you to restore the whole database, then you can restore the backup taken immediately before opening the database with the RESETLOGS option, and roll forward using the logs produced on Saturday.
On Saturday night you do the following:
SHUTDOWN ABORT
% cp /oracle/backup/* /oracle/dbs
SET AUTORECOVERY ON RECOVER DATABASE
STARTUP
In this scenario, if you had opened the database after the Friday night backup and before opening the database with RESETLOGS, or, did not have a control file from after opening the database, you would not be able to use the Friday night backup to roll forward. You must have a backup after opening the database with the RESETLOGS option in order to be able to recover.
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|