群友问题之ORA-01152: file 1 was not restored from a sufficiently old backup

群友问题

    请教个问题，今天在做恢复测试的时候，在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的对应备份获取

个人简介：

8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。

   

   服务过的客户：

          中国电信

          中国移动

          中国联通

          中国电通

          国家电网

          四川达州商业银行

          湖南老百姓大药房

          山西省公安厅

          中国邮政

          北京302医院     

          河北廊坊新奥集团公司

  
 项目经验:

           中国电信3G项目AAA系统数据库部署及优化

           中国联通CRM数据库性能优化

           中国移动10086电商平台数据库部署及优化

           湖南老百姓大药房ERR数据库sql优化项目

           四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化

           四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化

           北京高铁信号监控系统RAC数据库部署及优化

           河南宇通客车数据库性能优化

           中国电信电商平台核心采购模块表模型设计及优化

           中国邮政储蓄系统数据库性能优化及sql优化

           北京302医院数据库迁移实施

           河北廊坊新奥data guard部署及优化

           山西公安厅身份证审计数据库系统故障评估

         

 联系方式：

          手机：18201115468

          qq   :   305076427

          qq微博: wisdomone1

          新浪微博：wisdomone9

          qq群：275813900    

          itpub博客名称：wisdomone1     http://blog.itpub.net/9240380/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1816123/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-1816123/