All about Performing User-Managed Database Recovery

Automatic Recovery with SET AUTORECOVERY
========================================
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 number of data blocks trial recovery can corrupt in memory.

A trial recovery command is usable in any scenario in which a normal recovery command is usable. Nevertheless, you should only need to run trial recovery when recovery runs into problems.

Recovering Through an Added Datafile with a Backup Control File
===============================================================
You may see the following error when applying the CREATE TABLESPACE redo data:

ORA-00283: recovery session canceled due to errors 
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 11: '/disk1/oradata/trgt/test02.dbf'
ORA-01110: data file 10: '/disk1/oradata/trgt/test01.dbf'

SELECT FILE#,NAME 
FROM V$DATAFILE;

ALTER DATABASE RENAME FILE '/db/UNNAMED00001' TO
                           '/disk1/oradata/trgt/test01.dbf';
ALTER DATABASE RENAME FILE '/db/UNNAMED00002' TO
                           '/disk1/oradata/trgt/test02.dbf';

RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL

Re-Creating a Control File
==========================
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. You are not required to open the database with the RESETLOGS option after the recovery.

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE SALES NORESETLOGS ARCHIVELOG ...

RECOVER DATABASE; (without specifying the USING BACKUP CONTROLFILE clause)

Open the database after recovery completes (RESETLOGS option not required):
ALTER DATABASE OPEN;

ALTER DATABASE BACKUP CONTROLFILE TO '/backup/control01.dbf' REUSE;

Recovering Through a RESETLOGS with a Created Control File
==========================================================
ALTER DATABASE REGISTER LOGFILE '/disk1/oradata/trgt/arch/arcr_1_1_12343533.arc';
RECOVER AUTOMATIC DATABASE;

Recovery of Read-Only Files with a Re-Created Control File
==========================================================
If a current or backup control file is unavailable for recovery, then you can execute a CREATE CONTROLFILE statement. Read-only files should not be listed in the CREATE CONTROLFILE statement so that recovery can skip these files. No recovery is required for read-only datafiles unless you restored backups of these files from a time when the datafiles were read/write.

After you create a new control file and attempt to mount and open the database, the database performs a data dictionary check against the files listed in the control file. For each file that is not listed in the CREATE CONTROLFILE statement but is present in the data dictionary, an entry is created for them in the control file. These files are named as MISSINGnnnnn, where nnnnn is a five digit number starting with 0.

After the database is open, rename the read-only files to their correct filenames by executing the ALTER DATABASE RENAME FILE statement for all the files whose name is prefixed with MISSING.

To re-create a datafile for recovery
====================================
ALTER DATABASE CREATE DATAFILE '/disk1/oradata/trgt/users01.dbf' AS '/disk2/users01.dbf';

Recovering After Losing a Member of a Multiplexed Online Redo Log Group
=======================================================================
SELECT GROUP#, STATUS, MEMBER 
FROM V$LOGFILE
WHERE STATUS='INVALID';

ALTER DATABASE DROP LOGFILE MEMBER '/disk1/oradata/trgt/redo02.log';

ALTER DATABASE ADD LOGFILE MEMBER '/disk1/oradata/trgt/redo02b.log' TO GROUP 2;

Losing an Inactive Online Redo Log Group
========================================
You can clear an inactive redo log group when the database is open or closed. The procedure depends on whether the damaged group has been archived.
To clear an inactive, online redo log group that has been archived
------------------------------------------------------------------
STARTUP MOUNT

ALTER DATABASE CLEAR LOGFILE GROUP 2;

To clear an inactive, online redo log group that has not been archived
----------------------------------------------------------------------
SQL> STARTUP MOUNT

SQL> ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 2;

If there is an offline datafile that requires the cleared log to bring it online, then the keywords UNRECOVERABLE DATAFILE are required. The datafile and its entire tablespace have to be dropped because the redo necessary to bring it online is being cleared, and there is no copy of it. For example, enter:
SQL> ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 2 UNRECOVERABLE DATAFILE;

Immediately back up all datafiles in the database with an operating system utility
% cp /disk1/oracle/dbs/*.dbf /disk2/backup

Back up the database's control file with the ALTER DATABASE statement. For example, enter:
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/dbs/cf_backup.f';

To recover from loss of an active online log group
--------------------------------------------------
If the database is still running and the lost active redo log is not the current log, then issue the ALTER SYSTEM CHECKPOINT statement. If successful, then the active redo log becomes inactive, and you can follow the procedure in "Losing an Inactive Online Redo Log Group". If unsuccessful, or if your database has halted, then perform procedures in this section.
The current log is the one LGWR is currently writing to. If a LGWR I/O fails, then LGWR terminates and the instance crashes. In this case, you must restore a backup, perform incomplete recovery, and open the database with the RESETLOGS option.

Begin incomplete media recovery, recovering up through the log before the damaged log.

ALTER DATABASE RENAME FILE "/disk1/oradata/trgt/redo01.log" TO "/tmp/redo01.log";

ALTER DATABASE OPEN RESETLOGS;

Dropping a Database with SQL*Plus
=================================
SQL> startup restrict force mount;

SQL> drop database;   # deletes all database files, both ASM and non-ASM

% rm /backup/* /disk1/arch/*

本文出自：http://blog.csdn.net/jlhnxly/article/details/9229071