This chapter describes how to restore and recover a database, and use the flashback features of Oracle, when using a user-managed backup and recovery strategy, that is, a a strategy that does not depend upon using Recovery Manager.
This chapter includes the following topics:
Oracle's flashback features, which let you undo damage to your database after logical data corruption, include the following:
Oracle Flashback Database, which returns your entire database to a previous state without requiring you to restore files from backup;
Oracle Flashback Table, which returns one or more tables to their contents at a previous time;
Oracle Flashback Drop, which undoes the effects of the DROP TABLE
operation;
Oracle Flashback Query, which is used to query the contents of the database at a past time;
Oracle Flashback Version Query, which lets you view past states of data;
Oracle Flashback Transaction Query, which is used to review transactions affecting a table over time.
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 Database Backup and Recovery Basics.
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 Database Backup and Recovery Basics 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 in SQL*Plus:
Query the target database to determine the range of possible flashback SCNs. The following SQL*Plus queries show you 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;
Use other flashback features if necessary, to identify the SCN or time of the unwanted changes to your database.
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 utility like Data Pump Export to save lost data, use RECOVER DATABASE
to return the database to the present, and re-import the lost object.
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:
Datafiles and control files
Archived redo logs
Server parameter file
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 |
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.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.
If you are planning to perform complete recovery rather than point-in-time recovery, you can recover only those datafiles which require recovery, rather than the whole database. (Note that for point-in-time recovery, you must restore and recover all datafiles, unless you perform tablespace point-in-time recovery as described inChapter 20, "Performing User-Managed TSPITR". You can also use Flashback Database as described in "User-Managed Flashback Features of Oracle", but this affects all datafiles and returns the entire database to a past time.)
You can query V$RECOVER_FILE
to list datafiles requiring recovery by datafile number with their status and error information.
SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME FROM V$RECOVER_FILE;
Note:
You cannot useV$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 datafiles. 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 using the datafile number and the V$DATAFILE
and V$TABLESPACE
views, to get the datafile 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.
See Also:
Oracle Database Reference for information about the V$ viewsThis section contains the following topics:
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 devicesTo restore backup datafiles to their default location:
Determine which datafiles to recover by using the techniques described in "Determining Which Datafiles Require Recovery".
If the database is open, then take the tablespaces containing the inaccessible datafiles offline. For example, enter:
ALTER TABLESPACE users OFFLINE IMMEDIATE;
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
Recover the affected tablespace. For example, enter:
RECOVER TABLESPACE users
Bring the recovered tablespace online. For example, enter:
ALTER TABLESPACE users ONLINE;
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:
To determine which archived redo log files are needed, query V$ARCHIVED_LOG
and V$RECOVERY_LOG
. V$ARCHIVED_LOG
lists filenames for all 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:
V$RECOVERY_LOG
is only populated when media recovery is required for a datafile. Hence, this view is not useful in the case of a planned recovery, such as recovery from a user error.
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.
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
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
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 recoveryThis section contains the following topics:
Restore Control File from Backup After Loss of All Current Control Files
Create New Control File After Losing All Current and Backup Control Files
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.
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:
If the instance is still running, then shut it down:
SHUTDOWN ABORT
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".
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
Start a new instance and mount and open the database. For example, enter:
STARTUP
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:
If the instance is still running, then shut it down:
SHUTDOWN ABORT
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
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'
Start a new instance and mount and open the database. For example:
STARTUP
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 control file, 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 the following table, the procedure for restoring the control file depends on whether the online redo logs are available.
Table 18-1 Scenarios When Control Files Are Lost
Status of Online Logs | Status of Datafiles | Restore Procedure |
---|---|---|
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 |
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 |
Available |
Backup |
Restore a backup control file, perform complete recovery, and then open |
Unavailable |
Backup |
Restore a backup control file, perform incomplete recovery, and then open |
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:
If the instance is still running, shut it down:
SHUTDOWN ABORT
Correct the hardware problem that caused the media failure.
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
Start a new instance and mount the database. For example, enter:
STARTUP MOUNT
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
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, using the procedure described in "Create New Control File After Losing All Current and Backup Control Files".
Open the database with the RESETLOGS
option after finishing recovery:
ALTER DATABASE OPEN RESETLOGS;
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'
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 the following table 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 18-2 Options for Creating the Control File
If you . . . | Then . . . |
---|---|
Executed |
Use the |
Performed your most recent execution of |
Edit the output of |
Backed up the control file with the |
Use the control file copy to obtain SQL output. Create a temporary database instance, mount the backup control file, and then run |
Do not have a control file backup in either |
Execute the |
Note:
If your character set is not the default US7ASCII, then you must specify the character set as an argument to theCREATE
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.Start the database in NOMOUNT
mode. For example, enter:
STARTUP NOMOUNT
Create the control file with the CREATE
CONTROLFILE
statement, specifying the NORESETLOGS
option (refer to Table 18-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.
Recover the database as normal (without specifying the USING
BACKUP
CONTROLFILE
clause):
RECOVER DATABASE
Open the database after recovery completes (RESETLOGS
option not required):
ALTER DATABASE OPEN;
Immediately back up the control file. The following SQL statement backs up a database's control file to /backup/control01.dbf
:
ALTER DATABASE BACKUP CONTROLFILE TO '/backup/control01.dbf' REUSE;
To perform recovery, Oracle recommends that you use the RECOVER
SQL statement in SQL*Plus. You can also use the SQL statement ALTER
DATABASE
RECOVER
, but the RECOVER statement is simpler in most cases.
To start any type of media recovery, you must adhere to the following restrictions:
You must have administrator privileges.
All recovery sessions must be compatible.
One session cannot start complete media recovery while another performs incomplete media recovery.
You cannot start media recovery if you are connected to the database through a shared server process.
Oracle recommends that you use the SQL*Plus RECOVER
command rather than the ALTER
DATABASE
RECOVER
statement to perform media recovery. In almost all cases, the SQL*Plus method is easier.
When using SQL*Plus to perform media recovery, the easiest strategy is to perform automatic recovery. Automatic recovery initiates recovery without manually prompting SQL*Plus to apply each individual archived log.
When using SQL*Plus, you have two options for automating the application of the default filenames of archived redo logs needed during recovery:
Issuing SET
AUTORECOVERY
ON
before issuing the RECOVER
command
Specifying the AUTOMATIC
keyword as an option of the RECOVER
command
In either case, no interaction is required when you issue the RECOVER
command if the necessary files are in the correct locations with the correct names. The filenames used when you use automatic recovery are derived from the concatenated values of LOG_ARCHIVE_FORMAT
with LOG_ARCHIVE_DEST_
n
, where n
is the highest value among all enabled, local destinations.
For example, assume the following initialization parameter settings are in effect in the database instance:
LOG_ARCHIVE_DEST_1 = "LOCATION=/arc_dest/loc1/" LOG_ARCHIVE_DEST_2 = "LOCATION=/arc_dest/loc2/" LOG_ARCHIVE_DEST_STATE_1 = DEFER LOG_ARCHIVE_DEST_STATE_2 = ENABLE LOG_ARCHIVE_FORMAT = arch_%t_%s_%r.arc
In this case, SQL*Plus automatically suggests the filename /arc_dest/loc2/arch_%t_%s_%r.arc
(where %t
is the thread, %s
is the sequence and %r
is the resetlogs ID).
If you run SET
AUTORECOVERY
OFF
, which is the default option, then you must enter the filenames manually, or accept the suggested default filename by pressing the Enter key.
Run the SET
AUTORECOVERY
ON
command to enable on automatic recovery.
To automate the recovery using SET AUTORECOVERY:
Restore a backup of the offline datafiles. This example restores an inconsistent backup of all datafiles with an operating system utility:
% cp /backup/datafiles/*.dbf $ORACLE_HOME/oradata/trgt/
Ensure the database is mounted. For example, if the database is shut down, run:
STARTUP MOUNT
Enable automatic recovery. For example, in SQL*Plus run:
SET AUTORECOVERY ON
Recover the desired datafiles. This example recovers the whole database:
RECOVER DATABASE
The database automatically suggests and applies the necessary archived logs.
Open the database. For example:
ALTER DATABASE OPEN;
Note:
After issuing the SQL*PlusRECOVER
command, you can view all files that have been considered for recovery in the V$RECOVERY_FILE_STATUS
view. You can access status information for each file in the V$RECOVERY_STATUS
view. These views are not accessible after you terminate the recovery session.Besides using SET
AUTORECOVERY
to turn on automatic recovery, you can also simply specify the AUTOMATIC
keyword in the RECOVER
command.
To automate the recovery with the RECOVER AUTOMATIC command:
Restore a backup of the offline datafiles. This example restores a backup of all datafiles:
% cp /backup/datafiles/*.dbf $ORACLE_HOME/oradata/trgt/
Ensure the database is mounted. For example, if the database is shut down, run:
STARTUP MOUNT
Recover the desired datafiles by specifying the AUTOMATIC
keyword. This example performs automatic recovery on the whole database:
RECOVER AUTOMATIC DATABASE
The database automatically suggests and applies the necessary archived logs.
Open the database. For example:
ALTER DATABASE OPEN;
If you use an Oracle Real Application Clusters configuration, and if you are performing incomplete recovery or using a backup control file, then the database can only compute the name of the first archived redo log file from the first redo thread. You may have to manually apply the first log file from the other redo threads. After the first log file in a given thread has been supplied, the database can suggest the names of the subsequent logs in this thread.
See Also:
Your platform-specific Oracle documentation for examples of log file applicationRecovering when the archived logs are in their default location is the simplest case. As a log is needed, the database suggests the filename. If you are running nonautomatic media recovery with SQL*Plus, then the output is displayed in this format:
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 ]
For example, SQL*Plus displays output similar to the following:
ORA-00279: change 53577 generated at 11/26/02 19:20:58 needed for thread 1 ORA-00289: suggestion : /oracle/oradata/trgt/arch/arcr_1_802.arc ORA-00280: change 53577 for thread 1 is in sequence #802 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.
The database constructs suggested archived log filenames by concatenating the current values of the initialization parameters LOG_ARCHIVE_DEST_
n
(where n
is the highest value among all enabled, local destinations) and LOG_ARCHIVE_FORMAT
and using log history data from the control file. The following are possible settings:
LOG_ARCHIVE_DEST_1 = 'LOCATION = /oracle/oradata/trgt/arch/' LOG_ARCHIVE_FORMAT = arcr_%t_%s.arc SELECT NAME FROM V$ARCHIVED_LOG; NAME ---------------------------------------- /oracle/oradata/trgt/arch/arcr_1_467.arc /oracle/oradata/trgt/arch/arcr_1_468.arc /oracle/oradata/trgt/arch/arcr_1_469.arc
Thus, if all the required archived log files are mounted at the LOG_ARCHIVE_DEST_1
destination, and if the value for LOG_ARCHIVE_FORMAT
is never altered, then the database can suggest and apply log files to complete media recovery automatically.
Performing media recovery when archived logs are not in their default location adds an extra step. You have the following mutually exclusive options:
Edit the LOG_ARCHIVE_DEST_
n
parameter that specifies the location of the archived redo logs, then recover as usual.
Use the SET
statement in SQL*Plus to specify the nondefault log location before recovery, or the LOGFILE
parameter of the RECOVER
command
You can edit the initialization parameter file or issue ALTER
SYSTEM
statements to change the default location of the archived redo logs.
To change the default archived log location before recovery:
Use an operating system utility to restore the archived logs to a nondefault location. For example, enter:
% cp /backup/arch/* /tmp/
Change the value for the archive log parameter to the nondefault location. You can issue ALTER
SYSTEM
statements while the instance is started, or edit the initialization parameter file and then start the database instance. For example, while the instance is shut down edit the parameter file as follows:
LOG_ARCHIVE_DEST_1 = 'LOCATION=/tmp/' LOG_ARCHIVE_FORMAT = arcr_%t_%s.arc
Using SQL*Plus, start a new instance by specifying the edited initialization parameter file, and then mount the database. For example, enter:
STARTUP MOUNT
Begin media recovery as usual. For example, enter:
RECOVER DATABASE
In some cases, you may want to override the current setting for the archiving destination parameter as a source for redo log files.
To recover archived logs in a nondefault location with SET LOGSOURCE:
Using an operating system utility, copy the archived redo logs to an alternative location. For example, enter:
% cp $ORACLE_HOME/oradata/trgt/arch/* /tmp
Specify the alternative location within SQL*Plus for the recovery operation. Use the LOGSOURCE
parameter of the SET
statement or the RECOVER
...
FROM
clause of the ALTER
DATABASE
statement. For example, start SQL*Plus and run:
SET LOGSOURCE "/tmp"
Recover the offline tablespace. For example, to recover the offline tablespace users
do the following:
RECOVER AUTOMATIC TABLESPACE users
Alternatively, you can avoid running SET
LOGSOURCE
and simply run:
RECOVER AUTOMATIC TABLESPACE users FROM "/tmp"
Note:
Overriding the redo log source does not affect the archive redo log destination for online redo logs groups being archived.If you are using SQL*Plus's recovery options (not SQL statements), then each time the database successfully applies a redo log file, the following message is returned:
Log applied.
You are then prompted 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, then the database returns an error message. For example, you may see something like:
ORA-00308: cannot open archived log "/oracle/oradata/trgt/arch/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 is applied. If the database returns an error message after supplying a log filename, then the following responses are possible.
Error | Possible Cause | Solution |
---|---|---|
ORA-27037 : unable to obtain file status |
Entered wrong filename.
Log is missing. |
Reenter correct filename.
Restore backup archived redo log. |
ORA-27047: unable to read the header block of file |
The log may have been partially written or become corrupted. | If you can locate an uncorrupted or complete log copy, then apply the intact copy and continue recovery.
If no copy of the log exists and you know the time of the last valid redo entry, then you use incomplete recovery. Restore backups and restart recovery. |
If you start media recovery and must then interrupt it, for example, because a recovery operation must end for the night and resume the next morning, then take either of the following actions:
Enter the word CANCEL
when prompted for a redo log file.
Use your operating system's interrupt signal if you must terminate when recovering an individual datafile, or when automated recovery is in progress.
After recovery is canceled, you can resume it later with the RECOVER
command. Recovery resumes where it left off when it was canceled.
When you perform complete recovery, you recover the backups to the current SCN. 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:
Performing Datafile Recovery in an Open Database
See Also:
Oracle Database Backup and Recovery Basics for basic information about media recovery concepts, which apply in both user-managed and RMAN-based backup and recovery.See Also:
Oracle Database Backup and Recovery Basics to familiarize yourself with fundamental recovery concepts and strategiesThis section describes steps to perform complete recovery while the database is not open. You can recover either all damaged datafiles in one operation, or perform individual recovery of each damaged datafile in separate operations.
Perform the media recovery in the following stages:
Prepare for closed database recovery as described in "Preparing for Closed Database Recovery".
Restore the necessary files as described in "Restoring Backups of the Damaged or Missing Files".
Recover the restored datafiles as described in "Recovering the Database".
In this stage, you shut down the instance and inspect the media device that is causing the problem.
To prepare for closed database recovery:
If the database is open, then shut it down. For example:
SHUTDOWN IMMEDIATE
If you are recovering from a media error, then correct it if possible. 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), then no media recovery is required: simply start the database and resume normal operations. If you cannot repair the problem, then proceed to the next stage.
In this stage, you restore all necessary backups.
To restore the necessary files:
Determine which datafiles to recover by using the techniques described in "Determining Which Datafiles Require Recovery".
If the files are permanently damaged, then identify the most recent backups for the damaged files. Restore only the datafiles damaged by the media failure: do not restore any undamaged datafiles or any online redo log files.
For example, if ORACLE_HOME/
oradata/trgt/users01.dbf
is the only damaged file, then you may determine 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 datafile, then you may be able to create an empty replacement file that can be recovered.
Use an operating system utility to restore the files to their default location or to a new location. Restore the necessary files as described in "Restoring Datafiles and Archived Redo Logs". For example, a UNIX user restoring users01.dbf
to its default location might enter:
% cp /backup/users01_10_24_02.dbf $ORACLE_HOME/oradata/trgt/users01.dbf
Use the following 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 in the control file with ALTER DATABASE RENAME FILE . Use the operation described in "Renaming and Relocating Datafiles" in the Oracle Database Administrator's Guide, as necessary. |
In the final stage, you recover the datafiles that you have restored.
To recover the restored datafiles:
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
Obtain the datafile names and statuses of all datafiles by checking the list of datafiles that normally accompanies the current control file or querying the V$DATAFILE
view. For example, enter:
SELECT NAME,STATUS FROM V$DATAFILE;
Ensure that all datafiles of the database are online. All datafiles of the database requiring recovery must be online unless an offline tablespace was taken offline normally or is part of a read-only tablespace. For example, to guarantee that a datafile named /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, then the database 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 SQL> @onlineall
Issue the statement to recover the database, tablespace, or datafile. For example, enter one of the following RECOVER
command:
RECOVER DATABASE # recovers whole database RECOVER TABLESPACE users # recovers specific tablespace RECOVER DATAFILE '?/oradata/trgt/users01.dbf'; # recovers specific datafile
Follow these guidelines when deciding which statement to execute:
If you want to . . . | Then . . . |
---|---|
Recover all damaged files in one step | Execute RECOVER DATABASE |
Recover an individual tablespace | Execute RECOVER TABLESPACE |
Recover an individual damaged datafile | Execute RECOVER DATAFILE |
Prevent parallelization of recovery on multiple-CPU systems | Refer to "Controlling Parallel Media Recovery" |
If you choose not to automate the application of archived 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 datafiles.
The database notifies you when media recovery is complete:
Media recovery complete.
If no archived redo log files are required for complete media recovery, then the database applies all necessary online redo log files and terminates recovery.
After recovery terminates, open the database for use:
ALTER DATABASE OPEN;
See Also:
"About User-Managed Media Recovery" for more information about applying redo log filesIt is possible for a media failure to occur while the database remains open, leaving the undamaged datafiles online and available for use. Damaged datafiles—but not the tablespaces that contain them—are automatically taken offline if the database writer is unable to write to them. Queries that cannot read damaged files return errors, but the datafiles are not taken offline because of the failed queries. For example, you may run a query and see output such as:
ERROR at line 1: ORA-01116: error in opening database file 3 ORA-01110: data file 11: '/oracle/oradata/trgt/cwmlite02.dbf' ORA-27041: unable to open file SVR4 Error: 2: No such file or directory Additional information: 3
The procedure in this section cannot be used to perform complete media recovery on the datafiles of the SYSTEM
tablespace while the database is open. If the media failure damages datafiles of the SYSTEM
tablespace, then the database automatically shuts down.
Perform media recovery in these stages:
Prepare the database for recovery by making sure it is open and taking the tablespaces requiring recovery offline, as described in "Preparing for Open Database Recovery".
Restore the necessary files in the affected tablespaces as described in "Restoring Backups of the Inaccessible Datafiles".
Recover the affected tablespaces as described in "Recovering Offline Tablespaces in an Open Database".
In this stage, you take affected tablespaces offline and inspect the media device that is causing the problem.
To prepare for datafile recovery when the database is open:
If the database is open when you discover that recovery is required, take all tablespaces containing damaged datafiles offline. For example, if tablespace users
and tools
contain damaged datafiles, enter:
ALTER TABLESPACE users OFFLINE TEMPORARY; ALTER TABLESPACE tools OFFLINE TEMPORARY;
Correct the hardware problem that caused the media failure. If the hardware problem cannot be repaired quickly, proceed with database recovery by restoring damaged files to an alternative storage device.
In this stage, you restore all necessary backups in the offline tablespaces.
To restore datafiles in an open database:
If files are permanently damaged, then restore the most recent backup files of only the datafiles damaged by the media failure. Do not restore undamaged datafiles, online redo logs, or control files. If the hardware problem is fixed and the datafiles can be restored to their original locations, then do so. Otherwise, restore the datafiles to an alternative storage device.
Note:
In some circumstances, if you do not have a backup of a specific datafile, you can useALTER
DATABASE
CREATE
DATAFILE
to create an empty replacement file that is recoverable.If you restored one or more damaged datafiles to alternative locations, update the control file of the database to reflect the new datafile names. For example, to change the filename of the datafile in tablespace users
you might enter:
ALTER DATABASE RENAME FILE '?/oradata/trgt/users01.dbf' TO '/disk2/users01.dbf';
In the final stage, you recover the datafiles in the offline tablespaces.
To recover offline tablespaces in an open database:
Connect to the database with administrator privileges, and start offline tablespace recovery of all damaged datafiles in one or more offline tablespaces using one step. For example, recover users
and tools
:
RECOVER TABLESPACE users, tools;
The database begins the roll forward phase of media recovery by applying the necessary redo logs (archived and online) to reconstruct the restored datafiles. Unless the applying of files is automated with RECOVER
AUTOMATIC
or SET
AUTORECOVERY
ON
, the database prompts for each required redo log file.
Recovery continues until all required archived logs have been applied to the datafiles. The online redo logs are then automatically applied to the restored datafiles to complete media recovery. If no archived redo logs are required for complete media recovery, then the database does not prompt for any. Instead, all necessary online redo logs are applied, and media recovery is complete.
When the damaged tablespaces are recovered up to the moment that media failure occurred, bring the offline tablespaces online. For example, to bring tablespaces users
and tools
online, issue the following statements:
ALTER TABLESPACE users ONLINE; ALTER TABLESPACE tools ONLINE;
See Also:
Oracle Database Administrator's Guide for more information about creating datafilesIn this phase, you examine the source of the media problem.
To prepare for incomplete recovery:
If you are uncertain about performing incomplete recovery, then back up the whole database—all datafiles, a control file, and the parameter files—as a precautionary measure in case an error occurs during the recovery procedure.
If the database is still open and incomplete media recovery is necessary, then terminate the instance:
SHUTDOWN ABORT
If a media failure occurred, correct the hardware problem that caused the failure. If the hardware problem cannot be repaired quickly, then proceed with database recovery by restoring damaged files to an alternative storage device.
In this phase, you restore a whole database backup.
To restore the files necessary for cancel-based recovery and bring them online:
If the current control files do not match the physical structure of the database at the intended time of recovery, then restore a backup control file as described in "Restore Control File from Backup After Loss of All Current Control Files". The restored control file should reflect the database's physical file structure at the point at which incomplete media recovery should finish. To determine which control file backup to use:
Review the list of files that corresponds to the current control file and 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 to replace the missing one.
Note:
If you are unable to restore a control file backup to one of theCONTROL_FILES
locations, then edit the initialization parameter file so that this CONTROL_FILES
location is removed.Restore backups of all datafiles of the database. All backups used to replace existing datafiles must have been taken before the intended time of recovery. For example, if you intend to recover to January 2 at 2:00 p.m., then restore all datafiles with backups completed before this time. Follow these guidelines:
If . . . | Then . . . |
---|---|
You do not have a backup of a datafile | Create an empty replacement file that can be recovered as described in "Restoring Backups of the Damaged or Missing Files". |
A datafile was added after the intended time of recovery | Do not restore a backup of this file because 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 as described in "Restoring Datafiles and Archived Redo Logs" and skip step 4 of this procedure. |
A hardware problem persists | Restore damaged datafiles to an alternative storage device. |
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.Start SQL*Plus and connect to the database with administrator privilege, then start a new instance and mount the database:
STARTUP MOUNT
If one or more damaged datafiles were restored to alternative locations in step 2, then indicate the new locations of these files to the control file of the associated database. For example, enter:
ALTER DATABASE RENAME FILE '?/oradata/trgt/users01.dbf' TO '/disk2/users01.dbf';
Obtain the datafile names and statuses of all datafiles by checking the list of datafiles that normally accompanies the current control file or querying the V$DATAFILE
view. For example, enter:
SELECT NAME,STATUS FROM V$DATAFILE;
Ensure that all datafiles of the database are online. All datafiles of the database requiring recovery must be online unless a tablespace was taken offline with the NORMAL
option or is a read-only tablespace. For example, to guarantee that a datafile named ?/oradata/trgt/users01.dbf
is online, enter the following:
ALTER DATABASE DATAFILE '?/oradata/trgt/users01.dbf' ONLINE;
If a specified datafile is already online, the statement has no effect. 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 SQL> @onlineall
In cancel-based recovery, recovery proceeds by prompting you with the suggested filenames of archived redo log files. Recovery stops when you specify CANCEL
instead of a filename or when all redo has been applied to the datafiles.
Cancel-based recovery is better than change-based or time-based recovery if you want to control which archived log terminates recovery. For example, you may know that you have lost all logs past sequence 1234, so you want to cancel recovery after log 1233 is applied.
You should perform cancel-based media recovery in these stages:
Prepare for recovery by backing up the database and correct any media failures as described in "Preparing for Incomplete Recovery".
Restore backup datafiles as described in "Restoring Datafiles Before Performing Incomplete Recovery". If you have a current control file, then do not restore a backup control file.
Perform media recovery on the restored database backup as described in the following procedure.
To perform cancel-based recovery:
Start SQL*Plus and connect to the database with administrator privileges, then start a new instance and mount the database:
STARTUP MOUNT
Begin cancel-based recovery by issuing the following command:
RECOVER DATABASE UNTIL CANCEL
If you are using a backup control file with this incomplete recovery, then specify the USING
BACKUP
CONTROLFILE
option in the RECOVER
command.
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
Note:
If you fail to specify theUNTIL
clause on the RECOVER
command, then the database assumes a complete recovery and will not open until all redo is applied.The database applies the necessary redo log files to reconstruct the restored datafiles. The database supplies the name it expects to find from LOG_ARCHIVE_DEST_1
and requests you to stop or proceed with applying the log file. Note that if the control file is a backup, then you must supply the names of the online logs if you want to apply the changes in these logs.
Note:
If you use a Real Application Clusters (RAC) configuration, and you are performing incomplete recovery or using a backup control file, then the database can only compute the name of the first archived redo log file from the first thread. The first redo log file from the other threads must be supplied by the user. After the first log file in a given thread has been supplied, the database can suggest the names of the subsequent log files in this thread.Continue applying redo log files until the last log has been applied to the restored datafiles, then cancel recovery by executing the following command:
CANCEL
The database indicates whether recovery is successful. If you cancel before all the datafiles have been recovered to a consistent SCN and then try to open the database, you will get an ORA-1113
error if more recovery is necessary. As explained in "Determining Which Datafiles Require Recovery", you can query V$RECOVER_FILE
to determine whether more recovery is needed, or if a backup of a datafile was not restored prior to starting incomplete recovery.
Open the database with the RESETLOGS
option. You must always reset the logs after incomplete recovery or recovery with a backup control file. For example:
ALTER DATABASE OPEN RESETLOGS;
This section describes how to perform the time-based media recovery procedure in the following stages:
Prepare for recovery by backing up the database and correct any media failures as described in "Preparing for Incomplete Recovery".
Restore backup datafiles as described in "Restoring Datafiles Before Performing Incomplete Recovery". If you have a current control file, then do not restore a backup control file.
Perform media recovery with the following procedure.
To perform change-based or time-based recovery:
Issue the RECOVER
DATABASE
UNTIL
statement to begin recovery. If recovering to an SCN, specify as a decimal number without quotation marks. For example, to recover through SCN 10034 issue:
RECOVER DATABASE UNTIL CHANGE 10034;
If recovering to a time, 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:
RECOVER DATABASE UNTIL TIME '2000-12-31:12:47:30'
Apply the necessary redo log files to recover the restored datafiles. The database automatically terminates the recovery when it reaches the correct time, and returns a message indicating whether recovery is successful.
Note:
Unless recovery is automated, the database supplies the name fromLOG_ARCHIVE_DEST_1
and asks you to stop or proceed with after each log. If the control file is a backup, then after the archived logs are applied you must supply the names of the online logs.Open the database in RESETLOGS
mode. You must always reset the online logs after incomplete recovery or recovery with a backup control file. For example:
ALTER DATABASE OPEN RESETLOGS;
Whenever you perform incomplete recovery or recovery with a backup control file, you must reset the online logs when you open the database. The new version of the reset database is called a new incarnation.
This section contains the following topics:
The RESETLOGS
option is always required after incomplete media recovery or recovery using a backup control file. Resetting the redo log does the following:
Archives the current online redo logs (if they are accessible) and then erases the contents of the online redo logs and resets the log sequence number to 1. For example, if the current online redo logs are sequence 1000 and 1001 when you open RESETLOGS
, then the database archives logs 1000 and 1001 and then resets the online logs to sequence 1 and 2.
Creates the online redo log files if they do not currently exist.
Reinitializes the control file metadata about online redo logs and redo threads.
Updates all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS
SCN
and time stamp.
Because the database will not apply an archived log to a datafile unless the RESETLOGS
SCN
and time stamps match, the RESETLOGS
prevents you from corrupting datafiles with archived logs that are not from direct parent incarnations of the current incarnation.
In prior releases, it was recommended that you back up the database immediately after the RESETLOGS
. Because you can now easily recover a pre-RESETLOGS
backup like any other backup, making a new database backup is optional. In order to perform recovery through resetlogs you must have all archived logs generated since the last backup and at least one control file (current, backup, or created).
Figure 18-1 shows the case of a database that can only be recovered to log sequence 2500 because an archived redo log is missing. When the online redo log is at sequence 4000, the database crashes. You restore the sequence 1000 backup and prepare for complete recovery. Unfortunately, one of your archived logs is corrupted. The log before the missing log contains sequence 2500, so you recover to this log sequence and open RESETLOGS
. As part of the RESETLOGS
, the database archives the current online logs (sequence 4000 and 4001) and resets the log sequence to 1.
You generate changes in the new incarnation of the database, eventually reaching log sequence 4000. The changes between sequence 2500 and sequence 4000 for the new incarnation of the database are different from the changes between sequence 2500 and sequence 4000 for the old incarnation. You cannot apply logs generated after 2500 in the old incarnation to the new incarnation, but you can apply the logs generated before sequence 2500 in the old incarnation to the new incarnation. The logs from after sequence 2500 are said to be orphaned in the new incarnation because they are unusable for recovery in that incarnation.
Figure 18-1 Creating a New Database Incarnation
To preserve the log sequence number when opening a database after media recovery, execute either of the following statements:
ALTER DATABASE OPEN NORESETLOGS; ALTER DATABASE OPEN;
To reset the log sequence number when opening a database after recovery and thereby create a new incarnation of the database, execute the following statement:
ALTER DATABASE OPEN RESETLOGS;
If you open with the RESETLOGS
option, the database returns different messages depending on whether recovery was complete or incomplete. If the recovery was complete, then the following message appears in the alert_
SID
.log
file:
RESETLOGS after complete recovery through change scn
If the recovery was incomplete, then this message is reported in the alert_
SID
.log
file, where scn
refers to the end point of incomplete recovery:
RESETLOGS after incomplete recovery UNTIL CHANGE scn
If you attempt to OPEN
RESETLOGS
when you should not, or if you neglect to reset the log when you should, then the database returns an error and does not open the database. Correct the problem and try again.
See Also:
"About User-Managed Media Recovery Problems" for descriptions of situations that can causeALTER
DATABASE
OPEN
RESETLOGS
to failAfter opening the database with the RESETLOGS
option, check the alert_
SID
.log
to see whether the database detected inconsistencies between the data dictionary and the control file, for example, a datafile that the data dictionary includes but which is not listed in the new control file. The following table describes two possible scenarios.
Control File | Data Dictionary | Result |
---|---|---|
Datafile is listed | Datafile is not listed | References to the unlisted datafile are removed from the control file. A message in the alert log indicates what was found. |
Datafile is not listed | Datafile is listed | The database creates a placeholder entry in the control file under MISSING nnnnn (where nnnnn is the file number in decimal). MISSING nnnnn is flagged in the control file as offline and requiring media recovery. You can make the datafile corresponding to MISSING nnnnn accessible by using ALTER DATABASE RENAME FILE for MISSING nnnnn so that it points to the datafile. If you do not have a backup of this datafile, then drop the tablespace. |
If a media failure damages datafiles in a NOARCHIVELOG
database, then the only option for recovery is usually to restore a consistent whole database backup. If you are using logical backups created by an Oracle export utility to supplement regular physical backups, then you can also attempt to restore the database by importing an exported backup of the database into a re-created database or a database restored from an old backup.
In this scenario, the media failure is repaired so that you are able to restore all database files to their original location.
To restore the most recent whole database backup to the default location:
If the database is open, then shut down the database. For example, enter:
SHUTDOWN IMMEDIATE
If possible, correct the media problem so that the backup database files can be restored to their original locations.
Restore the most recent whole database backup with operating system commands as described in "Restoring Datafiles and Archived Redo Logs". Restore all of the datafiles and control files of the whole database backup, not just the damaged files. The following example restores a whole database backup to its default location:
% cp /backup/*.dbf $ORACLE_HOME/oradata/trgt/
Because online redo logs are not backed up, you cannot restore them with the datafiles and control files. In order to allow the database to reset the online redo logs, you must first mimic incomplete recovery:
RECOVER DATABASE UNTIL CANCEL CANCEL
Open the database in RESETLOGS
mode:
ALTER DATABASE OPEN RESETLOGS;
In this scenario, you restore the database files to an alternative location because the original location is damaged by a media failure.
To restore the most recent whole database backup to a new location:
If the database is open, then shut it down. For example, enter:
SHUTDOWN IMMEDIATE
Restore all of the datafiles and control files of the whole database backup, not just the damaged files. If the hardware problem has not been corrected and some or all of the database files must be restored to alternative locations, then restore the whole database backup to a new location. For example, enter:
% cp /backup/*.dbf /new_disk/oradata/trgt/
If necessary, edit the restored parameter file to indicate the new location of the control files. For example:
CONTROL_FILES = "/new_disk/oradata/trgt/control01.dbf"
Start an instance using the restored and edited parameter file and mount, but do not open, the database. For example:
STARTUP MOUNT
If the restored datafile filenames will be different (as will be the case when you restore to a different file system or directory, on the same node or a different node), then update the control file to reflect the new datafile locations. For example, to rename datafile 1
you might enter:
ALTER DATABASE RENAME FILE '?/oradata/trgt/system01.dbf' TO '/new_disk/oradata/system01.dbf';
If the online redo logs were located on a damaged disk, and the hardware problem is not corrected, then specify a new location for each affected online log. For example, enter:
ALTER DATABASE RENAME FILE '?/oradata/trgt/redo01.log' TO '/new_disk/oradata/redo_01.log'; ALTER DATABASE RENAME FILE '?/oradata/trgt/redo02.log' TO '/new_disk/oradata/redo_02.log';
Because online redo logs are not backed up, you cannot restore them with the datafiles and control files. In order to allow the database to reset the online redo logs, you must first mimic incomplete recovery:
RECOVER DATABASE UNTIL CANCEL; CANCEL;
Open the database in RESETLOGS
mode. This command clears the online redo logs and resets the log sequence to 1:
ALTER DATABASE OPEN RESETLOGS;
Note that restoring a NOARCHIVELOG
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:
Oracle Database Administrator's Guide for more information about renaming and relocating datafiles, and Oracle Database SQL Reference to learn aboutALTER
DATABASE
RENAME
FILE
By default, Oracle uses parallel media recovery to improve performance of the roll forward phase of media recovery. In parallel media recovery, the database uses a "division of labor" approach to allocate different processes to different data blocks while rolling forward, thereby making the procedure more efficient. The number of processes used is derived from the CPU_COUNT
initialization parameter, which by default is equal to the number of CPUs on the system. For example, if parallel recovery is performed on a system where CPU_COUNT
is 4, and only one datafile is recovered, then four spawned processes read blocks from the datafile and apply redo.
Typically, recovery is I/O-bound on reads from and writes to data blocks. Parallelism at the block level may only help recovery performance if it increases total I/Os, for example, by bypassing operating system restrictions on asynchronous I/Os. Systems with efficient asynchronous I/O see little benefit from parallel media recovery.
To override the default behavior of performing parallel recovery, use the RECOVER
with the NOPARALLEL
option, or RECOVER
PARALLEL
0
.
Note:
TheRECOVERY_PARALLELISM
initialization parameter controls instance or crash recovery only. Media recovery is not affected by the value used for RECOVERY_PARALLELISM
.See Also:
Oracle Database Performance Tuning Guide for more information on parallel recovery
SQL*Plus User's Guide and Reference for more information about the SQL*Plus RECOVER
...
PARALLEL
and NOPARALLEL
statements