异机恢复后ORA-01152(SCN不一致)错误解决

mahan

1. 现象：

异机数据文件恢复成功后，执行最后一步打开数据库时报错。例如：

SQL > alter database open resetlogs;

报错：

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

ORA-01110: data file 1: '/u02/oracle/oradata/mingya/system01.dbf'



2. 原因：

造成这种报错的原因是因为controlfile里所记录的scn与datafile里的scn不一致，导致数据库启动时失败。



3. 解决

3.1 确定需要恢复的achivelog

将数据库启动到mount状态；

$ rman target / catalog rman/rman@catalog    ；连接到RMAN

RMAN> recover database;

运行命令后，RMAN将会报错，在最后列出需要的archivelog；例如：

RMAN-06025: no backup of log thread 1 seq 3784 lowscn 82847939 found to restore

RMAN-06025: no backup of log thread 1 seq 3783 lowscn 82845664 found to restore

其中的3783、3784是我们需要恢复的archivelog。



3.2 从磁带库备份中恢复所需archivelog

RMAN> run {

2>set archivelog destination to '/u03/oracle/archivelog';    该路径根据归档日志实际路径设置

3> allocate channel ch00 type 'SBT_TAPE';

4> send 'NB_ORA_SERV=BAKSERV,NB_ORA_CLIENT=HOSTA';

5> restore archivelog sequence between 3783 and 3784;

6> release channel ch00;

7> }

恢复成功后，在/u03/oracle/archivelog目录下将看到3783、3784两个文件。



3.3 应用archivelog

RMAN> run{

2> allocate channel ch00 type disk;

3> set until sequence 3785 thread 1;           3785是需要恢复的最大号3784＋1

4> recover database;

5> release channel ch00;

6> }



3.4 打开数据库

RMAN> alter database open resetlogs;

至此，数据库可以正常打开。为使其他客户端能连接本数据库，最好重新创建监听listener.ora，其他客户端重新配置连接文件tnsnames.ora。



4. 其他问题－ORA-19625错误解决

在测试机上恢复操作成功后，正常运行的生产主机用NBU进行备份时，可能出现以下错误：

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

RMAN-03002: failure of backup command at 09/02/2009 02:21:47

ORA-19625: error identifying file /u03/oracle /archivelog/2009_08_31/1_3783_634497921.dbf

==== ended in error on Wed Sep 2 02:21:48 CST 2009 ====

这时需要进行以下操作修正：

登录正常运行的主机：

$ rman target / catalog rman/rman@catalog

RMAN>  crosscheck archivelog all;



二、

在一次恢复过程中,restore过后，在open时遇到报错：
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/../../raw12'
造成这种报错的大部分原因是因为controlfile里所记录的scn与datafile里的scn不一致。比如从备份里restore出的controlfile上的scn < datafile上所记录的scn，因此理论上二种思路：
1、以old controlfile为准的，datafile上的scn是新的，这样存在着数据的不一致，要继续恢复下去，将datafile上的scn也要restore到与controlfile一致的情况，但这样会丢失datafile上的数据。
2、就是以datafile上的scn为基准，将controlfile恢复到与datafile scn一致。
于是，在restore过后如果遭遇到ORA-01152之类的问题，可以这样来操作(实际上就是上面第二种思路的实现)：
RMAN>RECOVER DATABASE;　---找出同步controlfile scn和datafile scn所需的archivelog，如果归档目录缺少所列出log就从备份里(比如说是在磁带里)取出来并放回归档目录，比如说所列的archivelog是1_215.dbf - 1_230.dbf。
然后进行时间点的数据恢复(大于之前所需archivelog中的最后一个archivelog即可)
RMAN>run{
allocate channel d1 type disk;
allocate channel d2 type disk;
set until sequence 231 thread 1; --注意这里指定为1_231，大于之前的1_230
recover database;
release channel d1;
release channel d2;
}
这样就恢复出一致性的数据，然后用open resetlogs打开数据即可，但记得resetlogs后应该全备一次当前数据库。当然，如果不愿意用resetlogs后，重建controlfile后，用noresetlogs也是ok的。
此方法同样适合于RAC数据库的恢复测试时遇到的ORA-01152错误。
-----------------------------------
遭遇ORA-01152: file 1 was not restored from a sufficiently old backup






三、

问题描述

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

问题分析

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

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

B)以datafile的SCN为准，将controlfile恢复到与其一致，此时就会报ORA-01152的错误，然后我们通过recover database，进而利用归档日志来完成controlfile和datafile的SCN一致，归档找不到在去利用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: {=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: {=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: {=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问题解决。



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