Oracle 查看需要recover的datafile v$recover_file 需要哪些归档日志

To easily and quickly find out if the online redo log files can be used to recover a database.

Scope

This document is addressed to DBAs that want to quickly find the best recovery solution in case of a database crash.

Details

Many databases today are run without archive logging enabled, this 
reduces the available options to quickly recover a database. Basically
2 options are available: 

   a) restore from a backup
   b) recover the database using the online redo logs.

Option a) is straight forward and will not be covered here. Only important
thing to mention is that option a) WILL cause loss of data if there has
been updates/inserts to the database since the backup was taken.

Let us instead take a look at option b):

In case of a database crash or a database that will not startup
due to ORA-1110, ORA-1113 or both, we first need to identify which 
files need to be recovered.

1) First we mount the database then issue the following query:

	select * from v$recover_file; 	

This will give us a list of the files that need media recovery. It
will also give us CHANGE#, i.e. the SCN where the media recovery
must start. To get the name of the file use the FILE# column and
query against v$datafile like this:

	select name from v$datafile
	where file# = <file# from v$recover_file>

or like this from 9i:

	select substr(d.name,1,30) fname
	,      r.online_status
	,      r.error
	,      r.change#
	,      r.time
	from v$datafile d, v$recover_file r
	where d.file# = r.file#

2) Next we do:

	archive log list

This will give us the current log sequence. We need the current
log sequence -1. This will give us the last redo log file that
was in use.

3) As the last step we do:
	select recid
	,      stamp
	,      sequence#
	,      first_change#
	,      next_change#
	from v$log_history
	where sequence# = <current log sequence -1)

This will show us the NEXT_CHANGE#, i.e. the highest SCN, in the redo 
log file. It will also give us the FIRST_CHANGE# SCN in 
this redo log file. We need these 2 SCN numbers to find out if we 
can use the redo log file for recovery against the file(s) found 
in 1). If the CHANGE# from 1) is between the FIRST_CHANGE# and the 
NEXT_CHANGE# then we can use the redo log file for recovery.

If the CHANGE# from 1) is lower than FIRST_CHANGE# we need to 
investigate an earlier online redo log file. 

When we have identified which redo log files to use for the recovery we
perform the recovery using the redo log file(s) in the following way:
	- mount the database
	- recover database 

The recover process will now display something similar to the following:

ORA-00279: change 12599 generated at 08/18/98 13:25:48 needed for thread 1
ORA-00289: suggestion : /<archivelog_disk_path>/arch_129.arc
ORA-00280: change 12599 for thread 1 is in sequence #129
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

As the database is not in ARCHIVELOG mode we will not have the 129.arc
file. Instead the name of the redo log file must be entered on the
command line. The filename must include the full path. After the redo
log file has been applied the recover process will return: Log applied.
At this stage 2 things can happened: 

	1) Media Recovery Completed
	2) or additional redo log files must be applied

If 2) then just specify the next redo log file on the command line and
continue to do so until Media Recovery Completed is displayed. This must
be done with all the redo log files also the CURRENT redo log. To find the
CURRENT redo log file one can issue the following query:
   
        select f.member,to_char(v.first_change#)
        ,      v.sequence#  
        from   v$log v
        ,      v$logfile f 
        where  v.group# = f.group# 
        and    v.status='CURRENT';

When the CURRENT redo log has been applied the database can be opened with: 
alter database open;

It is necessary to apply all relevant redo logs otherwise we will not be
able to perform the complete recovery which is the only option we have
when the database is in NOARCHIVELOG mode.

If we do not find any online redo log files which covers the CHANGE# 
from 1) we cannot do a recover of the database or datafile(s). This 
means we are left with only 2 options of bringing the database back online:

1) Restore from a valid backup taken before the crash. Doing so and 
   running the database in NOARCHIVELOG MODE will cause a loss of data. 
   This is unavoidable.

2) Force the database open. This will override Oracle's internal 
   datafile synchronization and consistency check. The result is an 
   inconsistent database. The database MUST now be exported and rebuild 
   as an inconsistent database is unreliable, and hence not supported.
   This last option should only be used in cooperation with Oracle Support