ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01152: file 1 was not restored from a sufficiently old backup

Linux操作系统 作者:zhongshanyang 时间:2016-03-19 16:20:01 0 删除 编辑

ORA-01152: file 1 was not restored from a sufficiently old backup

问题描述

         我们在用rman恢复数据库时当执行到将数据库open阶段遇到ORA-01152错误。

问题分析

         由于controlfile里记录的SCNdatafile里的不一致导致。比如通过restore出来的controlfileSCNdatafile记录的要小。解决方法有两条:

A)      old controlfileSCN为准,将datafile恢复到与其一致,这样就会导致数据的不一致。

B)      datafileSCN为准,将controlfile恢复到与其一致,此时就会报ORA-01152的错误,然后我们通过recover database,进而利用归档日志来完成controlfiledatafileSCN一致,归档找不到在去利用redolog来恢复。

C)      通过更改内置参数跳过一致性的检查启动数据库。

问题重现与解决 

a)       备份控制文件,记录DBID

连接RMAN时记录DBID

[oracle@dev206 ~]$ rman target /

 

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Mar 5 13:53:26 2013

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

connected to target database: ORCL (DBID=1324770912)

 

RMAN> backup current controlfile;

 

Starting backup at 05-MAR-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=534 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

channel ORA_DISK_1: starting piece 1 at 05-MAR-13

channel ORA_DISK_1: finished piece 1 at 05-MAR-13

piece handle=/oracle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_05/o1_mf_ncnnf_TAG20130305T142357_8mc3vxo7_.bkp tag=TAG20130305T142357 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 05-MAR-13

将控制文件备份到

/oracle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_05/o1_mf_ncnnf_TAG20130305T142357_8mc3vxo7_.bkp路径下。

b)       模拟插入几条数据

原表没有记录:

SQL>select * from test;

no rows selected

插入新的数据

SQL>insert into test values(1,’a’);

SQL>commit;

在表中插入一条数据

c)       一致性停库

SQL>shutdown immediate;

d)       启动数据库到nomount状态,进入RAMN,并设置DBID

SQL>startup nomount

SQL>rman target /

RMAN>set dbid=1324770912

e)       恢复控制文件

RMAN> restore controlfile from '/oracle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_05/o1_mf_ncnnf_TAG20130305T142357_8mc3vxo7_.bkp';

 

Starting restore at 05-MAR-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=540 devtype=DISK

 

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output filename=/oracle/u01/app/oracle/oradata/orcl/control01.ctl

output filename=/oracle/u01/app/oracle/oradata/orcl/control02.ctl

output filename=/oracle/u01/app/oracle/oradata/orcl/control03.ctl

Finished restore at 05-MAR-13

控制文件已恢复到/oracle/u01/app/oracle/oradata/orcl/路径下。

f)        启动数据库到mount状态

RMAN>alter database mount;

g)       启动数据库到open状态

RMAN> alter database open;

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 03/05/2013 14:34:16

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

RMAN> alter database open resetlogs;

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 03/05/2013 14:34:37

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/oracle/u01/app/oracle/oradata/orcl/system01.dbf'

 

这时已经报错ORA-01152,根据提示就是说使用了一个旧的控制文件。

 

通过B的方法恢复,应用归档。

h)       恢复数据库

这时有两种方法完成

                       i.              SQLPLUS中手动利用归档恢复数据库

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

 

 

SQL> recover database using backup controlfile;

ORA-00279: change 5634731 generated at 03/05/2013 14:23:36 needed for thread 1

ORA-00289: suggestion :

/oracle/u01/app/oracle/oradata/orcl/archive/1_315_796990179.dbf

ORA-00280: change 5634731 for thread 1 is in sequence #315

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Auto

ORA-00308: cannot open archived log

'/oracle/u01/app/oracle/oradata/orcl/archive/1_315_796990179.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

 

 

ORA-00308: cannot open archived log

'/oracle/u01/app/oracle/oradata/orcl/archive/1_315_796990179.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

此时找不到1_315_796990179.dbf这个归档文件,其实是在redolog中,手动指定redo路径

 

SQL> recover database using backup controlfile;

ORA-00279: change 5634731 generated at 03/05/2013 14:23:36 needed for thread 1

ORA-00289: suggestion :

/oracle/u01/app/oracle/oradata/orcl/archive/1_315_796990179.dbf

ORA-00280: change 5634731 for thread 1 is in sequence #315

 

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/oracle/u01/app/oracle/oradata/orcl/redo01.log

Log applied.

Media recovery complete.

此时恢复完成,应用完所有归档,并重新应用了redolog,启动数据库到open状态

SQL>alter database open resetlogs;

Database altered.

                     ii.              利用RMAN自动利用归档恢复数据库

RMAN> recover database;

 

Starting recover at 05-MAR-13

Starting implicit crosscheck backup at 05-MAR-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=540 devtype=DISK

Finished implicit crosscheck backup at 05-MAR-13

 

Starting implicit crosscheck copy at 05-MAR-13

using channel ORA_DISK_1

Crosschecked 2 objects

Finished implicit crosscheck copy at 05-MAR-13

 

searching for all files in the recovery area

cataloging files...

cataloging done

 

List of Cataloged Files

=======================

File Name: /oracle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_05/o1_mf_ncnnf_TAG20130305T144504_8mc53kcr_.bkp

 

using channel ORA_DISK_1

 

starting media recovery

 

archive log thread 1 sequence 1 is already on disk as file /oracle/u01/app/oracle/oradata/orcl/redo01.log

archive log filename=/oracle/u01/app/oracle/oradata/orcl/redo01.log thread=1 sequence=1

media recovery complete, elapsed time: 00:00:01

Finished recover at 05-MAR-13

此时恢复完成,启动数据库到open状态

RMAN> alter database open resetlogs;

 

database opened

 

通过C的方法,修改参数

i)         在报ORA-01152错误后

RMAN> alter database open resetlogs;

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 03/05/2013 14:56:41

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/oracle/u01/app/oracle/oradata/orcl/system01.dbf'

j)         重启数据库

SQL> startup force;

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

 

Total System Global Area 1224736768 bytes

Fixed Size                  2020384 bytes

Variable Size             352324576 bytes

Database Buffers          855638016 bytes

Redo Buffers               14753792 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

k)       更改参数

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

 

System altered.

l)         恢复数据库

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 5638424 generated at 03/05/2013 14:48:17 needed for thread 1

ORA-00289: suggestion :

/oracle/u01/app/oracle/oradata/orcl/archive/1_1_809275697.dbf

ORA-00280: change 5638424 for thread 1 is in sequence #1

 

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

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: '/oracle/u01/app/oracle/oradata/orcl/system01.dbf'

 

 

ORA-01112: media recovery not started

 

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

 

    SQL> startup force;

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

 

Total System Global Area 1224736768 bytes

Fixed Size                  2020384 bytes

Variable Size             352324576 bytes

Database Buffers          855638016 bytes

Redo Buffers               14753792 bytes

Database mounted.

    Database opened.

 

 

m)     将内置参数改回来

SQL> alter system set "_allow_resetlogs_corruption"=false scope=spfile;

 

System altered.

 

n)       数据库启动到open状态后查询数据

查询表test,看数据是否存在

数据是存在的,此时ORA-01152问题解决。

 

注:在数据库恢复完成后要重新备份数据库。

         

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

请登录后发表评论 登录
全部评论

注册时间:2015-01-18

  • 博文量
    132
  • 访问量
    428148