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

germany006發表於2016-05-24
REST

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: {<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/28371090/viewspace-2105978/，如需轉載，請註明出處，否則將追究法律責任。

相關文章