群友問題

    請教個問題，今天在做恢復測試的時候，在recover的時候報錯
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
 
ORA-01152: file 1 was not restored from a sufficiently old backup
 
ORA-01110: data file 1: '/u01/app/oracle/oradata/EPPS/system01.dbf'
請問下怎麼解決

測試

1,資料庫版本
SQL> select * from v$version where rownum=1;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


2，10046跟蹤RECOVER DATAFILE,目的，檢視恢復資料檔案到底在作什麼操作
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> recover datafile 6;
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 6 - file is in use or recovery
ORA-01110: data file 6: '/oracle/oradata/guowang/t_err1.dbf'




SQL> alter database datafile 6 offline;


Database altered.


SQL> recover datafile 6;
Media recovery complete.
SQL> alter database datafile 6 online;


Database altered.


SQL> oradebug event 10046 trace name context  off
Statement processed.
SQL> oradebug tracefile_name
/oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_27393.trc


3，檢視TRC檔案
--擷取有關係的部分內容，可見恢復資料檔案要從控制檔案進行讀取資訊，可見備份及資料檔案的資訊是存在在控制檔案中
PARSING IN CURSOR #1 len=36 dep=0 uid=0 oct=35 lid=0 tim=1445934177384898 hv=604348291 ad='de5735f8' sqlid='ccba0cwk0b7w3'
ALTER DATABASE RECOVER  datafile 6  
END OF STMT
PARSE #1:c=1999,e=58980,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1445934177384860
WAIT #1: nam='Disk file operations I/O' ela= 122 FileOperation=2 fileno=0 filetype=1 obj#=-1 tim=1445934177465446
WAIT #1: nam='Disk file operations I/O' ela= 214 FileOperation=2 fileno=1 filetype=1 obj#=-1 tim=1445934177510958
WAIT #1: nam='control file sequential read' ela= 45 file#=0 block#=1 blocks=1 obj#=-1 tim=1445934177511250
WAIT #1: nam='control file sequential read' ela= 14 file#=0 block#=16 blocks=1 obj#=-1 tim=1445934177537669
WAIT #1: nam='control file sequential read' ela= 6 file#=0 block#=18 blocks=1 obj#=-1 tim=1445934177537693
WAIT #1: nam='control file sequential read' ela= 5 file#=0 block#=23 blocks=1 obj#=-1 tim=1445934177537713
WAIT #1: nam='control file sequential read' ela= 7 file#=0 block#=1 blocks=1 obj#=-1 tim=1445934177537913
WAIT #1: nam='control file sequential read' ela= 9 file#=1 block#=1 blocks=1 obj#=-1 tim=1445934177537938
WAIT #1: nam='control file sequential read' ela= 6 file#=0 block#=16 blocks=1 obj#=-1 tim=1445934177537955
WAIT #1: nam='control file sequential read' ela= 5 file#=0 block#=18 blocks=1 obj#=-1 tim=1445934177537970
WAIT #1: nam='control file sequential read' ela= 6 file#=0 block#=281 blocks=1 obj#=-1 tim=1445934177537994
WAIT #1: nam='control file sequential read' ela= 11 file#=0 block#=1 blocks=1 obj#=-1 tim=1445934177557949
WAIT #1: nam='control file sequential read' ela= 8 file#=1 block#=1 blocks=1 obj#=-1 tim=1445934177558008
WAIT #1: nam='control file sequential read' ela= 6 file#=0 block#=16 blocks=1 obj#=-1 tim=1445934177558029
WAIT #1: nam='control file sequential read' ela= 5 file#=0 block#=18 blocks=1 obj#=-1 tim=1445934177558045
WAIT #1: nam='control file sequential read' ela= 227 file#=0 block#=281 blocks=1 obj#=-1 tim=1445934177559175


*** 2015-10-27 04:22:57.617
Started Serial Media Recovery


*** 2015-10-27 04:22:57.922
DDE rules only execution for: ORA 1110
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 6 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 6 csec) -----


*** 2015-10-27 04:22:58.038
Completed Media Recovery


4，現在DUMP下控制檔案
SQL> select name,status from v$controlfile;


NAME                                               STATUS
-------------------------------------------------- ------------------------------
/oracle/oradata/guowang/control01.ctl
/oracle/oradata/guowang/control02.ctl




5，關於控制檔案如何DUMP以及其中內容，請參考我之前的文章：


oracle controlfile控制檔案 dump內容小記 
http://blog.itpub.net/9240380/viewspace-757471/


6，再看群友的問題
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
 
ORA-01152: file 1 was not restored from a sufficiently old backup
 
ORA-01110: data file 1: '/u01/app/oracle/oradata/EPPS/system01.dbf'


檢視上述的報錯
[oracle@seconary ~]$ oerr ora 1152
01152, 00000, "file %s was not restored from a sufficiently old backup "
// *Cause:  An incomplete recovery session was started, but an insufficient
//         number of logs were applied to make the database consistent. This
//         file is still in the future of the last log applied. 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 database is consistent or
//         restore the database file from an older backup and repeat recovery.


也就是說在進行非一致性恢復時，要恢復的檔案一致性資訊要在將來要的日誌中，什麼意思呢？
也就是說要恢復這個檔案的一致性或備份資訊從控制檔案找不到或者說這個控制檔案版本過於舊了，對應的檔案SCN與其它檔案SCN不一致，
所以這就有2個原因，
1，確實沒有這個檔案的RMAN備份
2，控制檔案沒有記錄這個RMAN備份，為何控制檔案沒有記錄這個RMAN備份的資訊，就是說控制檔案不是最新的控制檔案

結論

我們從上面依次分析下，只有第2種原因最可能，所以建議群友使用最新的控制檔案即可
而這個最新的控制檔案，可以從RMAN配置的控制檔案自動備份獲知或者RMAN的對應備份獲取

個人簡介：