ORA-01194 ORA-01547 ORA-01110 DATAFILE NEEDS MORE RECOVERY TO BE CONSISTENT错误解析

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.


 

 

假设所有的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    邮箱:[email protected]

 

场景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;

 

 

 

 


Posted

in

by

Tags:

Comments

3 responses to “ORA-01194 ORA-01547 ORA-01110 DATAFILE NEEDS MORE RECOVERY TO BE CONSISTENT错误解析”

  1. Ask_Maclean_liu_Oracle Avatar

    GoalThe goal of this article is to assist DBAs who encounter the ORA-01110, and to point them in the right direction.Several notes have been referenced depending on the subsequent errors.If the DBA is unable to resolve the issue after reading the appropriate note, a script to collect diagnostic information has been provided below. Theoutput of this script should be uploaded to the service request.FixDefinitionError: ORA-01110Text: datafile : ——————————————————————————-Cause : This message reports the filename involved with other messages.Action: See the associated messages for a description of the problem.The ORA-01110 displays the physical datafile in which Oracle is having a problem accessing.The ORA-01110 is accompanied by one or more messages. These messages may be Oracle specific messages or be related to the operating system.The first aim is to identify all error messages encounted prior to addressing the issue.Below is a list of the common errors that may follow the ORA-01110.ORA-01157 “cannot identify datafile – file not found”ORA-01578 “ORACLE data block corrupted (file # %s, block # %s)”ORA-00376 “file cannot be read at this time”ORA-01194 “file needs more recovery to be consistent”ORA-01547 “warning: RECOVER succeeded but OPEN RESETLOGS would get error”Addressing an ORA-01157 (cannot identify datafile – file not found)- Does the datafile in question exist at the os level ?If yes ensure that it is valid. Check the permissions & state of the file. Run an RMAN DBV or RMAN validate on the file/s this may provideadditional diagnostics.If not locate the file or restore from backup.Addressing an ORA-01578 (ORACLE data block corrupted (file # %s, block # %s))- Identify the object flagged as corurpted:SELECT tablespace_name, segment_type, owner, segment_nameFROM dba_extentsWHERE file_id = &AFNand &BL between block_id AND block_id + blocks – 1;- Determine if the corruption is wide spreadOptions to scan DB ( using DBV or RMAN)% dbv userid= file= blocksize=RMAN> backup validate check logical database; ( this will scan the entire Database)RMAN> backup validate check logical tablespace ; ( this will scan the entire tablespace listed)RMAN> backup validate check logical datafile X; (Where X is the datafile in question)When either RMAN command is complete review:SQL> select * from v$database_block_corruption ( This will list corrupted blocks found within DB)- The following note provided avenues into resolving the corruption:Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g/11g (Doc ID 28814.1)Addressing an ORA-00376 (file cannot be read at this time)- If the file is physically present then ensure that the permissions and size of file is as expected.- Check to see if the file is online – look at v$recover_file. If the file is offline you will need to recover and online it assuming you are running inarchivelog mode.- To assist Oracle supporting you please upload the results of the script belowAddressing an ORA-01194 (file needs more recovery to be consistent) & ORA-01547 (warning: RECOVER succeededbut OPEN RESETLOGS would get error)- The error indicates that the recovery may still have files which are in an inconsistent state. This may be due to datafiles being restored atdifferent times or we have not applied enough achivelogs following a backup.- At a minimum, all archivelogs that were generated during an online backup must be applied during recovery.To assist Oracle support diagnose the issue please provide the following:Please provide the output in the service request that you may raise. (upload recovery_info.txt)

  2. Ask_Maclean_liu_Oracle Avatar

    SymptomsExperienced the following errors after the database recovery:ORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: ‘c:ORACLEORADATASYSTEM01.DBF’CauseBased from the “results01.txt”, the V$DATAFILE_HEADER.CHECKPOINT_CHANGE# is within the range of the current online redolog found in V$LOG.SQL> select dbid, name,2 to_char(created, ‘DD-MON-YYYY HH24:MI:SS’) created,3 open_mode, log_mode,4 to_char(checkpoint_change#, ‘999999999999999’) as checkpoint_change#,5 controlfile_type,6 to_char(controlfile_change#, ‘999999999999999’) as controlfile_change#,7 to_char(controlfile_time, ‘DD-MON-YYYY HH24:MI:SS’) controlfile_time,8 to_char(resetlogs_change#, ‘999999999999999’) as resetlogs_change#,9 to_char(resetlogs_time, ‘DD-MON-YYYY HH24:MI:SS’) resetlogs_time10 from v$database;DBID NAME CREATED OPEN_MODE LOG_MODE———- ———- ——————– ———- ————CHECKPOINT_CHANG CONTROL CONTROLFILE_CHAN CONTROLFILE_TIME RESETLOGS_CHANGE—————- ——- —————- ——————– —————-RESETLOGS_TIME——————–3224494246 MAX5PRD 15-SEP-2006 10:48:21 MOUNTED ARCHIVELOG3224783100697 BACKUP 3224783102926 03-MAR-2007 08:00:04 322380846298215-SEP-2006 10:48:551 row selected.SQL> select * from v$log where status=’CURRENT’;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS———- ———- ———- ———- ———- — —————-FIRST_CHANGE# FIRST_TIME————- ——————–4 1 17423 4194304 2 NO CURRENT3.2248E+12 03-MAR-2007 06:18:131 row selected.SQL> select status, checkpoint_change#,2 to_char(checkpoint_time, ‘DD-MON-YYYY HH24:MI:SS’) as checkpoint_time,3 count(*)4 from v$datafile_header5 group by status, checkpoint_change#, checkpoint_time6 order by status, checkpoint_change#, checkpoint_time;STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*)——- —————— ——————– ———-ONLINE 3.2248E+12 03-MAR-2007 06:18:13 211 row selected.Also, a datafile is fuzzy probably after putting tablespace in backup mode and forgot to issue “alter tablespace end backup” afterwards.SQL> select * from v$backup where status = ‘ACTIVE’;issue:SQL> recover database until cancel;Otherwise, if V$DATABASE.CONTROLFILE_TYPE is BACKUP, then issue:SQL> recover database using backup controlfile until cancel;2/ If you’re prompted for log sequence# 17423 found in the current online redolog, then you may apply either of the following online redolog members:D:ORACLEORADATAMAX5PRDREDO04_A.LOGC:ORACLEORADATAMAX5PRDREDO04_B.LOG3/ Afterwards, type CANCEL to cancel the database recovery.4/ Finally, you may open the database. If the V$DATABASE.CONTROLFILE_TYPE is CURRENT, then issue:SQL> alter database open;Otherwise, if V$DATABASE.CONTROLFILE_TYPE is BACKUP, then issue:SQL> alter database open resetlogs;

  3. maclean Avatar
    maclean

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

Leave a Reply

Your email address will not be published. Required fields are marked *