差点丢失数据的一次RMAN恢复

事件经过
we preform a successful backup and tried to restore on another server,seq 35333 is the first archive log when we perform backup,and 35334 is the last archivelog. after the backup successful,I runed “alter system archivelog current” from plsql and shutdown the datbase,so it comes an archive log 35335.we successful run ” restore database” with no error,then it occres the errors below when run “recover database”

Oracle Error:
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/oracle/oradata/dbc/data1/system01.dbf'

RMAN-00571: ===============================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
RMAN-00571: =================================================
RMAN-03002: failure of recover command at 04/02/2009 06:18:16
RMAN-06053: unable to perform media recovery because of missing log
RMAN-03002: failure of recover command at 04/02/2009 06:18:16
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 35332 lowscn 6103122088730 found to restore
RMAN-06025: no backup of log thread 1 seq 35331 lowscn 6103119560491 found to restore
.....many more old logs required

尝试restore归档回来

restore archivelog from logseq 35279 until logseq 35332;
it says mismatch logs
list backup find the archivelogs backup below avaible:
-rw-r----- 1 oracle oinstall 2465792 Apr 1 21:15 vckbdj6g_1_1_22508.arc
-rw-r----- 1 oracle oinstall 26793472 Apr 1 22:51 vskbdoqh_1_1_22524.arc

resoter archivelog all and failed.
use the comand and succefull restore the archivelog

DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
sys.dbms_backup_restore.restoreSetArchivedLog;
sys.dbms_backup_restore.restoreArchivedLogRange;
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/nasbak_dbc/rman/dev_dbc.dev-dbc/vskbdoqh_1_1_22524.arc',params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/

开始恢复归档

recover database using backup controlfile ;
it required 35334,type auto
it required 35335,copy my manul back archivelog 35335 to the directory and type auto
it required 35556, type cancel;
open resetlogs
reported:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/oracle/oradata/dbc/data1/system01.dbf'

we thought something wrong with the controlfile so we backup controlfile to trace and created the new controlfile,

open reset logs ,and the same error occured again.
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/u01/oracle/oradata/dbc/data1/system01.dbf’
归档都应用了，为什么就是打不开，重建控制文件也不行。
we tried on a new mechine and try to restore also occure the error.
at last ,we use
_allow_terminal_recovery_corruption _allow_resetlogs_corruption on the pfile and succefully open the database.
事后跟踪原因，先将原备份在另一台机器恢复回来。
1、看看所有文件的check_time

SQL> select file#,substr(name,1,50), substr(recover,1,1) R, substr(fuzzy,1,1) F,
2 to_char(checkpoint_time,'dd/mm/yyyy:hh24:mi:ss') ckpt_time,
3 checkpoint_change#, resetlogs_change#,
4 to_char(resetlogs_time,'dd/mm/yyyy HH24:MI:SS') tm from v$datafile_header;
71 /u01/oracle/oradata/dbc/data1/test__1.dbf N 30/03/2009:21:28:44 6102273276922 21739661701
01/12/2005 11:35:45 

72 /u01/oracle/oradata/dbc/data1/tbs_03.dbf  Y  09/08/2006:16:26:47 42315919273 21739661701
01/12/2005 11:35:45 

73 /u01/oracle/oradata/dbc/data1/tbs__2.dbf N 30/03/2009:21:28:44 6102273276922 21739661701
01/12/2005 11:35:45 

76 /u01/oracle/oradata/dbc/data2/tbs__12.dbf N 30/03/2009:21:28:44 6102273276922 21739661701

2、看看status

select file#,substr(name, 1, 50), status,enabled,to_char(creation_time,'dd/mm/yyyy:hh24:mi:ss') from v$datafile;
/u01/oracle/oradata/dbc/data2/tbs__11.dbf ONLINE READ WRITE 11/05/2006:09:45:51
71 /u01/oracle/oradata/dbc/data1/test_1.dbf ONLINE READ WRITE 22/07/2006:11:02:56
72 /u01/oracle/oradata/dbc/data1/tbs_03.dbf RECOVER READ WRITE 09/08/2006:16:26:46
73 /u01/oracle/oradata/dbc/data1/tbs__2.dbf ONLINE READ WRITE 11/09/2006:17:50:43
76 /u01/oracle/oradata/dbc/data2/tbs__12.dbf ONLINE READ WRITE 08/12/2006:09:36:08

数据文件 72是recover状态并且SCN值不对
You need to choose from any of the following options :

1. You need to recover this datafile till current time and bring it online and then database can be opened successfully
2. Take this datafile offline drop, open up the database and then drop the tablespace which contains this datafile. You can choose to export the data from other datafiles of the tablespace.

SQL> alter database datafile 72 offline drop;
Database altered.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/oracle/oradata/dbc/data1/system01.dbf'

SQL> recover database using backup controlfile until cancel;
如果只是用recover database using backup controlfile，还是会出现同样的错打不开的。
ORA-00279: change 6102273276922 generated at 03/30/2009 21:28:44 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/dbc/data4/arch/1_35297.arc
ORA-00280: change 6102273276922 for thread 1 is in sequence #35297

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
这里是因为我用10.2.0.4的数据库去打开9.2.0.6的DB。SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@dev_dbc oracle]$ dbasql
-bash: dbasql: command not found
[oracle@dev_dbc oracle]$ sqlplus ” / as sysdba”
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 10 13:30:25 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup upgrade pfile=’/home/oracle/init.ora’;
ORACLE instance started.
Total System Global Area 2785017856 bytes
Fixed Size 1265888 bytes
Variable Size 310644512 bytes
Database Buffers 2457600000 bytes
Redo Buffers 15507456 bytes
Database mounted.
Database opened.

总结：事实上操作时我们怀疑过备份集有问题，还拿更老的备份集来做恢复（事后也是拿这份做的恢复测试），结果还是报system表空间数据文件的错。有时候报的错不是主要的，数据库报错时处于什么样的状态更值得关注。此备份的datafile 72，在备份之前应该就是offline的，所以恢复时无法应用到最新的SCN。

--EOF--