ORA-01194 file needs more recovery to be consistent

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;

Read more

How to migrate Raspberry Pi 5 OS from micro SD to NVME m.2 SSD

首先我買了Raspberry Pi CM5後來買了Raspberry Pi CM5 I/O board來當個人電腦使用,系統是安裝在256GB SD卡上運行的很好。用久了在開啟較肥的程式像Web Browser或LiberOffice會有慢半拍的反應,而有了升級NVME m.2 SSD念頭。 因為Raspberry Pi 5支援的最快PCIe gen3 x 4就不去考慮快的Gen4 or Gen5 m.2 SSD。找了ADATA出的 LEGEND 710入門級的產品,會利用HMB(Host Memory Buffer)來加速I/O速度,因為是Raspberry Pi OS kernel會認不得而無法正常使用 事先在SD卡的/boot/firmware/cmdline.txt 加入 kernel command line參數如下,然後重開機m.

By Phillips Hsieh

How to document Home Lab and Network

運維機房和跨域的網路,會遇到各式需求與問題,用對工具才能分析問題,個人覺得最重要的是使用能處理問題的工具。 推薦目前想學和正在使用的平台與軟體,協助將公司/家用機房文件化 佈告欄任務管理 Focalboard 白板可管理任務指派 網路架構文件編寫 netbox 精細管理網路設備與連接線路 IP 資源管理 phpipam 專注網路IP分配 邏輯塊文件編寫 draw.io 視覺化概念圖 機房設備管理 ITDB 管理設備生命週期與使用者

By Phillips Hsieh

如何在Raspberry Pi4上安裝Proxmox for ARM64

第一步 準備好Raspberry Pi 4 / CM4 4GB RAM,這裡要留意CM4如果是買有內建eMMC storage會限制不能使用SD卡開機而限制本地空間容量,如果沒有NAS外接空間或使用USB開機的話,建議買CM4 Lite插上大容量SD卡 第二步 去Armbian官網下載最小化Debian bookworm image https://www.armbian.com/rpi4b/ Armbian 25.2.2 Bookworm Minimal / IOT 然後寫入SD/USB開機碟,寫入方法參考官方文件 https://github.com/raspberrypi/usbboot/blob/master/Readme.md Note: 官方提供的預先設定系統方法,可以在Armbian初次啟動自動化完成系統設定。連結在此 https://docs.armbian.com/User-Guide_Autoconfig/

By Phillips Hsieh

世界越快心越慢

在晚飯後的休息時間,我特別享受在客廳瀏灠youtube上各樣各式創作者的影音作品。很大不同於傳統媒體,節目多是針對大多數族群喜好挑選的,在youtube上我會依心情看無腦的動畫、一些旅拍記錄、新聞時事談論。 尤其在看了大量的Youtube的分享後,我真的感受到會限制我的是我的無知,特別是那些我想都沒想過的實際應用,在學習後大大幫助到我的生活和工作層面。 休息在家時,我喜歡想一些沒做過的菜,動手去設計生活和工作上的解決方案,自己是真的很難閒著沒事做。 如創作文章,陪養新的習慣都能感覺到成長的喜悅,是不同於吃喝玩樂的快樂的。 創作不去限制固定的形式,文字是創作、影像聲音也是創作,記錄生活也是創作,我想留下的就是創造—》實現—》回憶,這樣子的循環過程,在留下的足跡面看到自己一路上的成長、失敗、絕望、重新再來。 雖然大部份的時候去做這些創作也不明白有什麼特別的意義,但不去做也不會留下什麼,所以呀不如反事都去試試看,也許能有不一樣的水花也許有意想不到的結果,投資自己永遠不會是失敗的決定,不是嗎?先問問自己再開始計畫下一步,未來沒人說得準。 像最近看youtube仍大一群人在為DOS開

By Phillips Hsieh