如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!
诗檀软件专业数据库修复团队
服务热线 ： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com
Oracle数据库- 企业版- 版本9.2.0.1及更高版本
本文档中的信息适用于任何平台。
***于2015年2月27日检测相关性***
目的
本文的目标是在打开数据库遇到以下错误时帮助数据库管理员：
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-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'
打开数据库前报错ORA-01547表示Oracle认为数据文件不一致。可以采用更多恢复使数据文件一致，从而解决该错误。
检查执行
1. 检查数据库文件的当前状态 :
set numwidth 30; set pagesize 50000; alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS'; select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy from v$datafile_header group by status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy;
目标是让上面的查询返回1行，并使得模糊列值为NO。
注：如果checkpoint_change＃在查询时返回0，这表明Oracle无法读取文件头。这可能是因为在控制文件中数据文件的位置及名称与在磁盘上的不同。
2. 检查恢复文件所需的归档日志序列号。 执行：
select min(fhrba_Seq), max(fhrba_Seq) from X$KCVFH;
该查询会显示数据文件所需要的最小和最大序列。为了使数据文件保持一致，必须运用上述序列号范围内的所有归档日志文件。可能是一个或多个归档日志文件。 注：该查询在RAC环境中无效。在RAC中，从所有线程中找到必要的序列，使用＃1中的查询返回最小的（也是最大的）checkpoint_change＃，并执行： select thread#, first_change#, next_change# from v$archived_log where <smallest/largest checkpoint_change#> between first_change# and next_change#; 一旦执行上述操作，有最小和最大的checkpoint_change＃，就得到了RAC数据库的所有线程应用所需要的序列范围。见文档243760.1- “RMAN：RAC备份与使用RMAN恢复”，有更多详细信息。
3. 一旦进行进一步恢复，确认数据文件与上述＃1中的查询一致，直到完成目标。 4. 一旦所有数据文件保持一致，模糊值为 NO，数据库可以打开，就不会再返回到ORA-01547。
方案1：当前控制文件用于恢复
如果当前控制文件正在使用中，所有联机 redo日志可用，那么就可以简单地进行数据库恢复。该操作会用到所有归档日志，以及当前联机redo日志。
1)确保实例安装，所有数据文件都为ONLINE（系统数据文件与系统的状态一致）
SQL> select name, controlfile_type from v$database ; NAME CONTROL --------- ------- V1123 CURRENT SQL> select status from v$instance; STATUS ------------ MOUNTED SQL> select distinct(status) from v$datafile; STATUS ------- ONLINE SYSTEM
2) 恢复并打开数据库:
SQL> recover automatic database ; .. Media recovery complete SQL> alter database open Database altered.
方案2：备份控制文件用于恢复
请参考下列步骤，展示了报错ORA-1194，ORA-1547，ORA-1110的例子
以及如何验证控制文件，数据文件，日志文件的状态，最终恢复并打开数据库。
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>
如果使用“备份控制文件”，或之前基于恢复命令使用撤销按钮，那么我们需要恢复，并最终应用当前联机redo日志。
例如:
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>
下面的查询会向你展示SCN，要使所有数据文件一致，我们至少要恢复到SCN。
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
评论: 在上面的输出/样品中，我们看到，redo（归档日志）被应用，数据文件6向前滚动，但仍需要更多恢复，仍需应用redo。
数据库恢复没有完成，因为没有与'备份'控制文件自动应用在线“当前”redo日志。 使用备份控制文件时，必须“手动”申请网上“当前”redo日志“/u01/app/oracle/oradata/V1123/redo01.log”序列＃454（SCN2472049）！
附加说明: 这是备份控制文件恢复，或控制文件从跟踪文件中新建（SQL> alter database backup to trace;），
查询 v$log/v$logfile 可能无法给出正确信息，对此日志文件包含的序列号（seq＃）。
# 找到可用联机日志的选项Options to find the Online log to be used a: 检查alert.log文件找到“联机redo日志文件”使用的最后的序列
b: 如果ALERT.LOG丢失，只需尝试所有联机redo日志文件，如果选择了错误的日志文件什么都不会被应用，但你会在输出信息中看到，哪一序列位于联机redo日志文件。然后，只需尝试下一个联机redo日志文件，直到得到“介质恢复完成”的消息。
c: 你也可以为联机redo日志文件（S）转储文件日志文件头
例如: -------- 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# 454to be manually applied Log applied. Media recovery complete. SQL> alter database open resetlogs ; Database altered. SQL>
注意：
如果应用所有归档日志和联机redo日志后，数据库没有打开，请提供以下脚本输出到Oracle support，以协助文件恢复。
（请上传脱机文件：recovery_info.txt）Note:
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;