All about Performing User-Managed Database Recovery


All about Performing User-Managed Database Recovery
 
Issuing SET AUTORECOVERY ON before issuing the RECOVER command. If you recovery with SET AUTORECOVERY OFF, which is the default, then you must enter filenames manually or accept the suggested filename by pressing Enter.
 
STARTUP MOUNT
SET AUTORECOVERY ON
RECOVER DATABASE
ALTER DATABASE OPEN;
Note:
After issuing the SQL*Plus RECOVER 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.
 
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 example, 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).
 
Automatic Recovery with the AUTOMATIC Option of the RECOVER Command
===================================================================
STARTUP MOUNT
RECOVER AUTOMATIC DATABASE
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.
 
 
Recovery When Archived Logs Are in a Nondefault Location
########################################################
 
Overriding the Archived Log Destination
=======================================
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"
 
Parallel Media Recovery
=======================
By default, Oracle uses parallel media recovery to improve performance of the roll forward phase of media recovery
To override the default behavior of performing parallel recovery, use the RECOVER with the NOPARALLEL option, or RECOVER PARALLEL 0. The RECOVERY_PARALLELISM initialization parameter controls instance or crash recovery only. Media recovery is not affected by the value used for RECOVERY_PARALLELISM.
 
Performing Closed Database Recovery
===================================
If you choose not to automate the application of archived redo logs, then you must accept or reject each prompted log. If you automate recovery, then the database applies the logs automatically. Recovery continues until all required archived and online redo logs have been applied to the restored datafiles. The database notifies you when media recovery is complete:
 
Media recovery complete.
If no archived redo logs are required for complete media recovery, then the database applies all necessary online redo log files and terminates recovery.
 
ALTER TABLESPACE users OFFLINE TEMPORARY;
 
RECOVER AUTOMATIC FROM '/tmp' TABLESPACE users, tools;
 
RECOVER AUTOMATIC TABLESPACE users, tools;
 
ALTER TABLESPACE users ONLINE;
 
 
Performing Incomplete Database Recovery
#######################################
Performing Cancel-Based Incomplete Recovery
===========================================
RECOVER DATABASE UNTIL CANCEL;
 
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. If the control file is a backup, then you must supply the names of the online redo logs if you want to apply the changes in these logs.
 
ALTER DATABASE OPEN RESETLOGS;
 
Performing Time-Based or Change-Based Incomplete Recovery
=========================================================
RECOVER DATABASE UNTIL CHANGE 10034;
 
RECOVER DATABASE UNTIL TIME '2000-12-31:12:47:30';
 
 
About User-Managed Media Recovery Problems
##########################################
When media recovery encounters a problem, the alert log may indicate that recovery can continue if it is allowed to corrupt the data block causing the problem. The alert log contains information about the block: its block type, block address, the tablespace it belongs to, and so forth. For blocks containing user data, the alert log may also report the data object number.
 
In this case, the database can proceed with recovery if it is allowed to mark the problem block as corrupt. Nevertheless, this response is not always advisable. For example, if the block is an important block in the SYSTEM tablespace, marking the block as corrupt can eventually prevent you from opening the recovered database. Another consideration is whether the recovery problem is isolated. If this problem is followed immediately by many other problems in the redo stream, then you may want to open the database with the RESETLOGS option.
 
ALTER DATABASE OPEN READ ONLY;
 
SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE DATA_OBJECT_ID = 8031;
 
Allowing Recovery to Corrupt Blocks
===================================
RECOVER DATABASE ALLOW 5 CORRUPTION
 
Performing Trial Recovery
=========================
By default, if a trial recovery encounters a stuck recovery or similar problem, then it always marks the data block as corrupt in memory when this action can allow recovery to proceed. The database writes errors generated during trial recovery to alert files. These errors are clearly marked as test run errors.
 
RECOVER DATABASE TEST
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL TEST
RECOVER TABLESPACE users TEST
RECOVER DATABASE UNTIL CANCEL TEST
 
By default, trial recovery always attempts to corrupt blocks in memory if this action allows trial recovery to proceed. Trial recovery by default can corrupt an unlimited number of data blocks. You can specify the ALLOW n CORRUPTION clause on the RECOVER ... TEST statement to limit the nu