Open DB failed-DATAFILE NEEDS MORE RECOVERY TO BE CONSISTENT_1528788.1

rongshiyuan發表於2014-01-06
AI


In this Document

Purpose
Scope
Details
  Scenario 1: Current Controlfile is available
  Scenario 2: Backup Controlfile is used for recovery
References

Applies to:

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'"

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'

 

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,
  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   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: {=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  

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: {=suggested | filename | AUTO | CANCEL}
'/u01/app/oracle/oradata/V1123/redo01.log'                  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;
    

 

 

 

 

 

 

References

NOTE:1354256.1 - How to quickly check that Database is consistent after incomplete recovery (Point in Time Recovery) before OPEN RESETLOGS
NOTE:434013.1 - HOW TO TROUBLESHOOT AND RESOLVE an ORA-1110

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-1067605/，如需轉載，請註明出處，否則將追究法律責任。

相關文章