ORA-01194: 文件 1 需要更多的恢复来保持一致性 解析

Posted by PDSERVICE on May 30, 2016 In

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

?

[oracle@mlab2 ~]

$ oerr ora 1194 01194, 00000, “file %s needs more recovery to be consistent” // *Cause: An incomplete recovery session was started, but an insufficient // number of logs were applied to make the file consistent. The // reported file was not closed cleanly when it was last opened by // the database. It must be recovered to a time when it was not being // updated. The most likely cause of this error is forgetting to // restore the file from a backup before doing incomplete recovery. // *Action: Either apply more logs until the file is consistent or restore // the file from an older backup and repeat recovery.

[oracle@mlab2 ~]

$ oerr ora 1547 01547, 00000, “warning: RECOVER succeeded but OPEN RESETLOGS would get error below” // *Cause: Media recovery with one of the incomplete recovery options ended // without error. However, if the ALTER DATABASE OPEN RESETLOGS command // were attempted now, it would fail with the specified error. // The most likely cause of this error is forgetting to restore one or // more datafiles from a sufficiently old backup before executing the // incomplete recovery. // *Action: Rerun the incomplete media recovery using different datafile // backups, a different control file, or different stop criteria.

[oracle@mlab2 ~]

$ oerr ora 1110 01110, 00000, “data file %s: ‘%s'” // *Cause: Reporting file name for details of another error. The reported // name can be of the old file if a data file move operation is // in progress. // *Action: See associated error message.

ORA-01194: 文件1需要更多的恢复来保持一致性

ORA-01194: file  1 needs more recovery to be consistent

假设所有的oracle数据文件均成功restore并recovery，但打开数据库时仍报错，那么

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: ‘/system01.dbf’

场景1： 当前的控制文件可用

保证实例正常mount且所有的数据文件ONLINE，那么执行：

select name, controlfile_type from v$database ;

NAME CONTROL
——— ——-
ORCL?CURRENT

SQL> recover automatic database ;
..
Media recovery complete
SQL> alter database open

场景2 此次恢复中使用的是备份的控制文件

select name, controlfile_type from v$database ;NAME CONTROL--------- -------ORCL 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_header8 group by status, resetlogs_change#, resetlogs_time, checkpoint_change#, checkpoint_time9 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 consistentONLINE 995548 15-FEB-2012:17:17:20 2472049 13-FEB-2013 16:02:22 6SQL>SQL>SQL> -- Check for datafile status, and fuzzinessSQL> select STATUS, ERROR, FUZZY, count(*) from v$datafile_header group by STATUS, ERROR, FUZZY ;STATUS ERROR FUZ COUNT(*)------- ----------------------------------------------------------------- --- ----------ONLINE YES 7SQL>SQL>SQL> -- Check for MIN, and MAX SCN in DatafilesSQL> select min(CHECKPOINT_CHANGE#), max(CHECKPOINT_CHANGE#) from v$datafile_header ;MIN(CHECKPOINT_CHANGE#) MAX(CHECKPOINT_CHANGE#)----------------------- -----------------------2446300 2472049SQL>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) MEMBERS5 ,substr(L.ARCHIVED,1,8) ARCHIVED6 ,substr(L.STATUS,1,10) STATUS7 ,substr(L.FIRST_CHANGE#,1,16) FIRST_CHANGE#8 ,substr(LF.member,1,60) REDO_LOGFILE9 from GV$LOG L, GV$LOGFILE LF10 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/ORCL/redo01.log <-- This is CURRENT log containing mostrecent redo, and is available3 1 453 1 YES INACTIVE 2471714 /u01/app/oracle/oradata/ORCL/redo03.log2 1 452 1 YES INACTIVE 2451698 /u01/app/oracle/oradata/ORCL/redo02.logSQL>-- 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#) 2446300SQL> select thread#, sequence#, substr(name,1,80) from v$Archived_logwhere 2446300 between first_change# and next_change#;THREAD# SEQUENCE# SUBSTR(NAME,1,80)---------- ---------- --------------------------------------------------------------------------------1 449 /u01/app/oracle/oradata/ORCL/arch1/arch_1_449_775329440.arc1 449 /u01/app/oracle/fra/ORCL/archivelog/2013_02_13/o1_mf_1_449_8kq7oc6y_.arc1 450 /u01/app/oracle/oradata/ORCL/arch1/arch_1_450_775329440.arc1 450 /u01/app/oracle/fra/ORCL/archivelog/2013_02_13/o1_mf_1_450_8kqbn929_.arcSQL>SQL> select * from v$recover_file ; -- Checking for Datafile(s) which needs recoveryFILE# ONLINE ONLINE_ ERROR CHANGE# TIME---------- ------- ------- ----------------------------------------------------------------- ---------- --------------------6 ONLINE ONLINE 2446300 13-FEB-2013:15:09:44   SQL> select name, controlfile_type from v$database ;NAME CONTROL--------- -------ORCL BACKUPSQL> #451ORA-00278: log file '/u01/app/oracle/fra/ORCL/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 1ORA-00289: suggestion : /u01/app/oracle/fra/ORCL/archivelog/2013_02_13/o1_mf_1_454_%u_.arcORA-00280: change 2471963 for thread 1 is in sequence #454ORA-00278: log file '/u01/app/oracle/fra/ORCL/archivelog/2013_02_13/o1_mf_1_453_8kqbqvrk_.arc' no longer needed for this recovery <-- AllRedo, up to and including SEQ# 453 is appliedORA-00308: cannot open archived log '/u01/app/oracle/fra/ORCL/archivelog/2013_02_13/o1_mf_1_454_%u_.arc' <<<-- "SEQ# 454" requested,which is in ONLINE REDOLOG as seen beforeORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'SQL>SQL> select * from v$recover_file ;FILE# ONLINE ONLINE_ ERROR CHANGE# TIME---------- ------- ------- ----------------------------------------------------------------- ---------- --------------------6 ONLINE ONLINE 2471963 13-FEB-2013:16:02:19SQL>SQL> alter database open resetlogs ;alter database open resetlogs*ERROR at line 1:ORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'SQL>  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 consistentABSSCN = Absolute SCN SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;.ORA-00279: change 2471963 generated at 02/13/2013 16:02:19 needed for thread 1ORA-00289: suggestion : /u01/app/oracle/fra/ORCL/archivelog/2013_02_13/o1_mf_1_454_%u_.arcORA-00280: change 2471963 for thread 1 is in sequence #454Specify log: {<RET>=suggested | filename | AUTO | CANCEL}'/u01/app/oracle/oradata/ORCL/redo01.log' <-- specify the online redologfile having SEQ# 454 to be manually appliedLog 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 openplease provide the following script output to Oracle support to assist with the recovery.( Please upload spooled file: recovery_info.txt )SQL> set pagesize 20000set linesize 180set pause offset serveroutput onset feedback onset echo onset numformat 999999999999999Spool recovery_info.txtselect 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 offexit;