If media
failure occurs while the database is open, the database continues to function.
When an attempt is made to write to one of these data files, the data file is
taken offline automatically. A query against one of these data files does not
cause it to go offline, but it does result in an error being returned to the
user that issued the query.
As with the
closed database recovery, you first need to query for the files and archive
logs that need to be recovered. Then, take all tablespaces that contain damaged
data files offline. Use a command such as the following to do this:
SQL> ALTER TABLESPACE
survey OFFLINE TEMPORARY;
Using the
TEMPORARY option causes Oracle to perform a checkpoint on any online data files
belonging to the tablespace. Checkpointed data files do not require recovery
when they are brought back online, because they are up-to-date for the latest
SCN of any transactions that would have affected them. This is the more
desirable option, although the data files must be available at the time this
command is run. It is possible that the problem was temporary, and you are able
to bring the tablespaces online with no errors.
Inspect the
media to determine the cause of the problem. You can use the DBVERIFY utility
for this. If the files are permanently damaged, then proceed to restore and
recover. After the restore and recovery steps are complete, bring all the
tablespaces online again.
Performing
User-Managed Incomplete Recovery:
An
incomplete recovery is one that does not bring the database back to the most
recent SCN that was transacted. For some reason, you need to recover that
database only up to a point in the past, not to the present. The processing
that occurs when performing incomplete recovery differs from the processing for
complete recovery, basically, in the amount of redo that is applied.
Choosing
an Incomplete Recovery Method
As you plan
your incomplete recovery, decide which method you want to use for specifying
when to stop applying redo data. You stop the recovery process by specifying
one of the following:
•
A
time: The time in
the logs, at which recovery should stop. This can be automated so that the
process does not prompt you for each file name.
•
An
SCN: The system
change number at which recovery should stop. This can be automated so that the
process does not prompt you for each file name.
•
CANCEL: That the CANCEL keyword will be
input when recovery prompts for the next redo log file name. This cannot
be automated so that the process does not prompt you for each file name. It
must prompt you, giving you the opportunity, at any time, to cancel the
recovery.
Performing
User-Managed Incomplete Recovery
The
following command is used to perform incomplete recovery:
RECOVER
[AUTOMATIC] DATABASE option
Following
are the meanings of the options:
•
AUTOMATIC: Automatically applies archived and
redo log files
•
option: UNTIL TIME 'YYYY-MM-DD:HH24:MI:SS'
UNTIL CANCEL
UNTIL
CHANGE <integer>
USING
BACKUP CONTROLFILE
Recover a
database until time:
RECOVER
DATABASE UNTIL TIME '2011-12-12:11:14:03';
Recover a
database until cancel:
SQL>
RECOVER DATABASE UNTIL CANCEL;
Cancel-Based
Incomplete Recovery
Cancel-based
incomplete recovery is very much like closed database complete recovery. The
difference is how you execute the RECOVER command; specify the UNTIL CANCEL
clause. This clause causes the recovery process to prompt you with the
suggested name for each redo log file to be applied. So, as the recovery
proceeds, you are prompted with an archived or online redo log file name, and,
for each one, you can either accept it or change it. When you reach the point
where you want the recovery to stop, enter CANCEL instead of accepting the file
name. This stops the recovery.
After this
is done, you have to open the database with the RESETLOGS option. The database
is in another instantiation now, so the redo log sequence numbers need to be
reset.
After
opening the database, check the alert log for messages. This is how you find
out if the recovery was successful. To apply redo log files automatically
during recovery, you can use the SQL*Plus SET AUTORECOVERY ON command, enter
AUTO at the recovery prompt, or use the RECOVER AUTOMATIC command.
Time-
and Change-Based Incomplete Recovery
Both time-
and change-based incomplete recovery are like the cancel-based recovery, except
that different criteria are used to specify when to stop the recovery.
Time-based recovery uses a time specified on the command line of the RECOVER
command, to know when to stop. Change-based recovery uses an SCN, specified on
the command line.
As with all
incomplete recoveries, the database must then be opened using the RESETLOGS
option.
Performing
User-Managed Incomplete Recovery: Steps
1. If the database is open, shut it down by
using the NORMAL, IMMEDIATE, or TRANSACTIONAL option.
2. Restore all data files from backup (the
most recent, if possible). You may also need to restore archived logs. If there
is enough space available, restore to the LOG_ARCHIVE_DEST location or use the
ALTER SYSTEM ARCHIVE LOG START TO <LOCATION> command or the SET
LOGSOURCE <LOCATION> command to change the location.
3.
Mount
the database.
4. Recover the database by using the RECOVER
DATABASE command.
5. To synchronize data files with control
files and redo logs, open the database by using the RESETLOGS option.
SQL>
SHUTDOWN IMMEDIATE
$
cp /BACKUP/* /u01/db01/ORADATA
SQL>
STARTUP MOUNT
SQL>
RECOVER DATABASE UNTIL TIME '2011-11-12:11:34:00';
SQL>
ALTER DATABASE OPEN RESETLOGS;
