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’

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com

场景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_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/ORCL/redo01.log <-- This is CURRENT log containing most
recent redo, and is available
3 1 453 1 YES INACTIVE 2471714 /u01/app/oracle/oradata/ORCL/redo03.log
2 1 452 1 YES INACTIVE 2451698 /u01/app/oracle/oradata/ORCL/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/ORCL/arch1/arch_1_449_775329440.arc
1 449 /u01/app/oracle/fra/ORCL/archivelog/2013_02_13/o1_mf_1_449_8kq7oc6y_.arc
1 450 /u01/app/oracle/oradata/ORCL/arch1/arch_1_450_775329440.arc
1 450 /u01/app/oracle/fra/ORCL/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> select name, controlfile_type from v$database ;
NAME CONTROL
--------- -------
ORCL BACKUP
SQL>

 

#451
ORA-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 1
ORA-00289: suggestion : /u01/app/oracle/fra/ORCL/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/ORCL/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/ORCL/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/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: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/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 consistent
ABSSCN = 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 1
ORA-00289: suggestion : /u01/app/oracle/fra/ORCL/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/ORCL/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;

You are here

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