Open Database failed - DATAFILE NEEDS MORE RECOVERY TO BE CONSISTENT ORA-1194 ORA-1547 ORA-1110 (Doc ID 1528788.1)
Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.
PURPOSE
The goal of this article is to assist DBAs who encounter the following errors at Database open.
ORA-1194 "file %s needs more recovery to be consistent"
ORA-1547 "warning: RECOVER succeeded but OPEN RESETLOGS would get error below"
ORA-1110 "data file %s: '%s'"
ORA-1194 "file %s needs more recovery to be consistent"
ORA-1547 "warning: RECOVER succeeded but OPEN RESETLOGS would get error below"
ORA-1110 "data file %s: '%s'"
SCOPE
This article is meant for database administrators and backup and recovery specialists tasked with the restore and recovery of a database
DETAILS
Assuming All Datafiles was either successfully restored, and/or recovery was done with existing database datafiles.
Then open the database failed with errors like:
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'
Then open the database failed with errors like:
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
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:
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,
2 resetlogs_change#,
3 resetlogs_time,
4 checkpoint_change#,
5 to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
6 count(*)
7 from v$datafile_header
8 group by status, resetlogs_change#, resetlogs_time, checkpoint_change#, checkpoint_time
9 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>
SQL>
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>
SQL>
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#
2 ,substr(L.THREAD#,1,7) THREAD#
3 ,substr(L.SEQUENCE#,1,10) SEQUENCE#
4 ,substr(L.MEMBERS,1,7) MEMBERS
5 ,substr(L.ARCHIVED,1,8) ARCHIVED
6 ,substr(L.STATUS,1,10) STATUS
7 ,substr(L.FIRST_CHANGE#,1,16) FIRST_CHANGE#
8 ,substr(LF.member,1,60) REDO_LOGFILE
9 from GV$LOG L, GV$LOGFILE LF
10 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>
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>
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
exit;
and how to verify status of Controlfile, Datafiles, Logfiles, and finally recover and open database.
NAME CONTROL
--------- -------
V1123 BACKUP -- controlfile_type is "Backup" Controlfile
2 resetlogs_change#,
3 resetlogs_time,
4 checkpoint_change#,
5 to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
6 count(*)
7 from v$datafile_header
8 group by status, resetlogs_change#, resetlogs_time, checkpoint_change#, checkpoint_time
9 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>
SQL>
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>
SQL>
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#
2 ,substr(L.THREAD#,1,7) THREAD#
3 ,substr(L.SEQUENCE#,1,10) SEQUENCE#
4 ,substr(L.MEMBERS,1,7) MEMBERS
5 ,substr(L.ARCHIVED,1,8) ARCHIVED
6 ,substr(L.STATUS,1,10) STATUS
7 ,substr(L.FIRST_CHANGE#,1,16) FIRST_CHANGE#
8 ,substr(LF.member,1,60) REDO_LOGFILE
9 from GV$LOG L, GV$LOGFILE LF
10 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>
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>
then we need to recover, and finally 'manual' apply the online current redolog.
Example:
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>
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>
, 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
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) !
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.....
..
~~~
.
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>
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
exit;
Niciun comentariu:
Trimiteți un comentariu