Assuming All Datafiles was either successfully restored,
and/or recovery was done with existing database datafiles.
Then open the database failed with errors like:
Error :
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS
would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/u01/app/oracle/oradata/V1123/system01.dbf'
Scenario 1:
Current Controlfile is
available
This shows the Information whats needed for recovery,
usually you will not see the errors as above at open database, but nevertheless
giving this example.
1) Ensure Instance is Mounted and ALL Datafiles ONLINE
If the CURRENT
CONTROLFILE is used then you can run recover database, and it will
apply all
archivelogs, and the online 'current' redolog if available, and finally you can
open database
Example:
SQL> select name,
controlfile_type from v$database ;
NAME CONTROL
--------- -------
V1123 CURRENT
SQL> recover automatic database ;
..
Media recovery complete
SQL> alter database open
Scenario 2:
Backup Controlfile is used
for recovery
Please see steps below showing you an example getting the
errors like ORA-1194, ORA-1547, ORA-1110,
and how to verify status of Controlfile, Datafiles,
Logfiles, and finally recover and open database.
SQL> select name, controlfile_type from v$database ;
NAME CONTROL
--------- -------
V1123 BACKUP -- controlfile_type is
"Backup" Controlfile
SQL> select status,
resetlogs_change#,
resetlogs_time,
checkpoint_change#,
to_char(checkpoint_time,
'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
from v$datafile_header
group by status,
resetlogs_change#, resetlogs_time, checkpoint_change#, checkpoint_time
order by status,
checkpoint_change#, checkpoint_time ;
STATUS
RESETLOGS_CHANGE# RESETLOGS_TIME
CHECKPOINT_CHANGE#
CHECKPOINT_TIME COUNT(*)
------- ----------------- --------------------
------------------ -------------------- ----------
ONLINE
995548 15-FEB-2012:17:17:20
2446300 13-FEB-2013 15:09:44
1 -- Datafile(s) are at different
checkpoint_change# (scn), so not consistent
ONLINE
995548 15-FEB-2012:17:17:20
2472049 13-FEB-2013 16:02:22
6
SQL> -- Check for datafile status, and fuzziness
SQL> select STATUS, ERROR, FUZZY, count(*)
from v$datafile_header group by STATUS, ERROR, FUZZY ;
STATUS ERROR
FUZ COUNT(*)
-------
----------------------------------------------------------------- ---
----------
ONLINE
YES 7
SQL> -- Check for MIN, and MAX SCN in Datafiles
SQL> select
min(CHECKPOINT_CHANGE#), max(CHECKPOINT_CHANGE#) from v$datafile_header ;
MIN(CHECKPOINT_CHANGE#) MAX(CHECKPOINT_CHANGE#)
----------------------- -----------------------
2446300 2472049
SQL>
SQL> select substr(L.GROUP#,1,6) GROUP#
,substr(L.THREAD#,1,7) THREAD#
,substr(L.SEQUENCE#,1,10) SEQUENCE#
,substr(L.MEMBERS,1,7) MEMBERS
,substr(L.ARCHIVED,1,8) ARCHIVED
,substr(L.STATUS,1,10) STATUS
,substr(L.FIRST_CHANGE#,1,16) FIRST_CHANGE#
,substr(LF.member,1,60) REDO_LOGFILE
from GV$LOG L, GV$LOGFILE LF
where
L.GROUP# = LF.GROUP# ;
GROUP# THREAD# SEQUENCE#
MEMBERS ARC STATUS
FIRST_CHANGE# REDO_LOGFILE
------ ------- ---------- ------- --- ----------
---------------- ------------------------------------------------------------
1 1 454 1
NO CURRENT 2471963
/u01/app/oracle/oradata/V1123/redo01.log <-- This is CURRENT log containing most
recent redo, and is available
3 1 453 1
YES INACTIVE 2471714
/u01/app/oracle/oradata/V1123/redo03.log
2 1 452 1
YES INACTIVE 2451698
/u01/app/oracle/oradata/V1123/redo02.log
SQL>
-- Use MIN(CHECKPOINT_CHANGE#) 2446300 as found before, then
use it with this query to find the
-- first SEQ# 'number' and archivelog file needed for
recover to start with.
-- All SEQ# up to the online Current Redolog SEQ# must be
available without any gap for successful recovery
-- MIN(CHECKPOINT_CHANGE#) 2446300
SQL> select thread#, sequence#,
substr(name,1,80) from v$Archived_log
where 2446300 between first_change# and
next_change#;
THREAD# SEQUENCE# SUBSTR(NAME,1,80)
---------- ---------- --------------------------------------------------------------------------------
1 449
/u01/app/oracle/oradata/V1123/arch1/arch_1_449_775329440.arc
1 449
/u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_449_8kq7oc6y_.arc
1 450 /u01/app/oracle/oradata/V1123/arch1/arch_1_450_775329440.arc
1
450
/u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_450_8kqbn929_.arc
SQL> select * from v$recover_file ; -- Checking for Datafile(s) which needs
recovery
FILE# ONLINE ONLINE_ ERROR
CHANGE# TIME
---------- ------- -------
----------------------------------------------------------------- ----------
--------------------
6 ONLINE ONLINE
2446300
13-FEB-2013:15:09:44
SQL>
If you use a "BACKUP CONTROLFILE", or previously used a CANCEL based recover
command
then we need to recover, and finally 'manual' apply the
online current redolog.
Example:
SQL> select name,
controlfile_type from v$database ;
NAME CONTROL
--------- -------
V1123 BACKUP
SQL>
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL
CANCEL ;
ORA-00279: change 2446300 generated at 02/13/2013 15:09:44
needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_450_8kqbn929_.arc
ORA-00280: change 2446300 for thread 1 is in sequence #450
Specify log: {<RET>=suggested | filename | AUTO |
CANCEL}
auto
ORA-00279: change 2451694 generated at 02/13/2013 16:00:25
needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_451_8kqbnbmh_.arc
ORA-00280: change 2451694 for thread 1 is in sequence #451
ORA-00278: log file
'/u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_450_8kqbn929_.arc' no
longer needed for this recovery
...
< all required
logs applied >
...
ORA-00279: change 2471963 generated at 02/13/2013 16:02:19
needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_454_%u_.arc
ORA-00280: change 2471963 for thread 1 is in sequence #454
ORA-00278: log file
'/u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_453_8kqbqvrk_.arc' no
longer needed for this recovery <--
All Redo, up to and including SEQ# 453 is applied
ORA-00308: cannot open archived log
'/u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_454_%u_.arc' <<<-- "SEQ# 454"
requested, which is in ONLINE REDOLOG as seen before
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER
succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more
recovery to be consistent
ORA-01110: data file 1:
'/u01/app/oracle/oradata/V1123/system01.dbf'
SQL> select * from
v$recover_file ;
FILE# ONLINE ONLINE_ ERROR
CHANGE# TIME
---------- ------- -------
----------------------------------------------------------------- ----------
--------------------
6 ONLINE ONLINE
2471963 13-FEB-2013:16:02:19
SQL>
SQL> alter database open resetlogs ;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/u01/app/oracle/oradata/V1123/system01.dbf'
SQL>
The following query will show you the SCN to which we must
at least recover to, to get all datafiles consistent.
SQL> select min(FHSCN) "LOW FILEHDR SCN"
, max(FHSCN) "MAX FILEHDR
SCN"
, max(FHAFS) "Min PITR
ABSSCN"
from X$KCVFH ;
LOW FILEHDR SCN MAX
FILEHDR SCN Min PITR ABSSCN
---------------- ---------------- ----------------
2446300
2472049 0
-- Example output explained:
--
-- "LOW FILEHDR SCN" - this is the SCN at which recovery process
starts
-- "MAX FILEHDR SCN" - this is the SCN we must recover to to get
all datafiles consistent
--
-- IF "Min PITR ABSSCN" != 0 AND
> "MAX FILEHDR SCN"
-- THEN "Min PITR ABSSCN" is the SCN we must recover to to get all
datafiles consistent
ABSSCN = Absolute SCN
Comment:
In the above output/sample we see, redo (archivelogs) was applied and datafile 6 was rolled forward
but still needs more recovery and redo to be applied.
Database Recovery did not complete, because the online
'current' redolog is not applied automatically with a 'BACKUP' controlfile.
As we use a Backup Controlfile we must 'manually' apply the
online 'current' redolog '/u01/app/oracle/oradata/V1123/redo01.log' having SEQ#
454 ( SCN 2472049) !
Additional Note:
As this is recover with a Backup Controlfile, or controlfile
create from Tracefile (sql> alter
database backup to trace;) ,
query of v$log/v$logfile will possibly not give the correct
information, about which logfile contains which sequence number (seq#).
# Options to find the Online log to be used
a: Check the
Alert.log file for the last sequences used with 'Online Redolog files'
b: If Alert.log is
lost you may simply try all online redolog files, if the wrong logfile is
chosen nothing will be applied,
but you will see
in in the output message which sequence is in that online redolog file.
Then simply try
the next online redolog file until you get 'media recovery complete' message.
c: You may also
dump the file log file headers for Online redolog file(s)
Example:
--------
sql> alter
system dump logfile '/u01/app/oracle/oradata/V1123/redo01.log' scn min 1 scn
max 1 ;
-- This will
write a tracefile with the header dump to your 'trace' (11g) [ or udump
(<=10g) ] directory
-- Check the
tracefile for similar entry like...
~~~
..
descrip:"Thread 0001, Seq# 0000000454 ...
..
Low scn: 0x.....
Next scn:
0x.....
..
~~~
SQL> RECOVER DATABASE USING
BACKUP CONTROLFILE UNTIL CANCEL ;
.
ORA-00279: change 2471963 generated at 02/13/2013 16:02:19
needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_454_%u_.arc
ORA-00280: change 2471963 for thread 1 is in sequence #454
Specify log: {<RET>=suggested
| filename | AUTO | CANCEL}
'/u01/app/oracle/oradata/V1123/redo01.log' <-- specify the online
redologfile having SEQ# 454 to be manually applied
Log applied.
Media recovery complete.
SQL> alter database open
resetlogs ;
Database altered.
SQL>
Note:
If after applying all archive logs and online redo logs the
database does not open
please provide the following script output to Oracle support
to assist with the recovery.
( Please upload spooled file: recovery_info.txt )
SQL> set pagesize 20000
set linesize 180
set pause off
set serveroutput
on
set feedback on
set echo on
set numformat
999999999999999
Spool
recovery_info.txt
select
substr(name, 1, 50), status from v$datafile;
select
substr(name,1,40), recover, fuzzy, checkpoint_change# from v$datafile_header;
select
GROUP#,substr(member,1,60) from v$logfile;
select * from
v$recover_file;
select distinct
status from v$backup;
select hxfil
FILENUMBER, fhsta STATUS, fhscn SCN, FHAFS ABSSCN , fhrba_Seq SEQUENCE from
x$kcvfh;
select distinct
(fuzzy) from v$datafile_header;
spool off
SQL> exit;
+++++++++++1528788.1+++++++++++
