联系：手机/微信(+86 17813235971) QQ(107644445)
标题：使用_allow_resetlogs_corruption打开无归档日志rman备份库
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
rman还原恢复操作
--还原数据库 RMAN> restore database; --恢复数据库 RMAN> recover database; Starting recover at 2012-03-08 21:20:45 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=65 device type=DISK starting media recovery RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 03/08/2012 21:20:47 RMAN-06053: unable to perform media recovery because of missing log RMAN-06025: no backup of archived log for thread 1 with sequence 2936 and starting SCN of 25991695 found to restore RMAN-06025: no backup of archived log for thread 1 with sequence 2935 and starting SCN of 25991652 found to restore RMAN-06025: no backup of archived log for thread 1 with sequence 2934 and starting SCN of 25991649 found to restore …………………… RMAN-06025: no backup of archived log for thread 1 with sequence 2902 and starting SCN of 25991156 found to restore 这里报日志缺少，实际上是备份的数据库文件后，没有备份归档日志，归档日志全部丢失
进行不完全恢复
SQL> recover database until cancel;
ORA-00279: change 25991194 generated at 03/08/2012 20:33:58 needed for thread 1
ORA-00289: suggestion : /opt/oracle/oradata/archivelog/chf/1_2902_752334071.dbf
ORA-00280: change 25991194 for thread 1 is in sequence #2902
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'
查看相关SCN
SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;
FILE# TO_CHAR(CHECK
---------- -------------
1 25992214
2 25992214
3 25992214
4 25992214
5 25992214
6 25992214
7 25992214
8 25992214
9 25992214
10 25992214
11 25992214
FILE# TO_CHAR(CHECK
---------- -------------
13 25992214
14 25992214
13 rows selected.
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
1 ONLINE 25991194
2 ONLINE 25991194
3 ONLINE 25991194
4 ONLINE 25991194
5 ONLINE 25991194
6 ONLINE 25991194
7 ONLINE 25991194
8 ONLINE 25991194
9 ONLINE 25991194
10 ONLINE 25991194
11 ONLINE 25991194
FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
13 ONLINE 25991194
14 ONLINE 25991194
13 rows selected.
SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile_header;
FILE# TO_CHAR(CHECK
---------- -------------
1 25991194
2 25991194
3 25991194
4 25991194
5 25991194
6 25991194
7 25991194
8 25991194
9 25991194
10 25991194
11 25991194
FILE# TO_CHAR(CHECK
---------- -------------
13 25991194
14 25991194
13 rows selected.
--发现数据文件scn和控制文件不一致，重建控制文件，然后查询相关scn
SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;
FILE# TO_CHAR(CHECK
---------- -------------
1 25991194
2 25991194
3 25991194
4 25991194
5 25991194
6 25991194
7 25991194
8 25991194
9 25991194
10 25991194
11 25991194
FILE# TO_CHAR(CHECK
---------- -------------
13 25991194
14 25991194
13 rows selected.
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
1 ONLINE 25991194
2 ONLINE 25991194
3 ONLINE 25991194
4 ONLINE 25991194
5 ONLINE 25991194
6 ONLINE 25991194
7 ONLINE 25991194
8 ONLINE 25991194
9 ONLINE 25991194
10 ONLINE 25991194
11 ONLINE 25991194
FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
13 ONLINE 25991194
14 ONLINE 25991194
13 rows selected.
SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile_header;
FILE# TO_CHAR(CHECK
---------- -------------
1 25991194
2 25991194
3 25991194
4 25991194
5 25991194
6 25991194
7 25991194
8 25991194
9 25991194
10 25991194
11 25991194
FILE# TO_CHAR(CHECK
---------- -------------
13 25991194
14 25991194
13 rows selected.
--此时所有scn均一致
尝试打开数据库
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 25991194 generated at 03/08/2012 20:33:58 needed for thread 1
ORA-00289: suggestion : /opt/oracle/oradata/archivelog/chf/1_2902_752334071.dbf
ORA-00280: change 25991194 for thread 1 is in sequence #2902
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'
使用隐含参数打开数据库
SQL> create pfile='/tmp/pfile' from spfile; File created. -------/tmp/pfile中加上---------- _allow_resetlogs_corruption= TRUE --------------------------------- SQL> startup mount pfile='/tmp/pfile' force ORACLE instance started. Total System Global Area 622149632 bytes Fixed Size 2230912 bytes Variable Size 419431808 bytes Database Buffers 192937984 bytes Redo Buffers 7548928 bytes Database mounted. SQL> alter database open resetlogs; Database altered. SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE
总结
这次的试验没有多少实际意义，但是可以说明几个问题：
1.所有的数据文件的scn都一致，甚至和控制文件的也一致，数据库不一定可以open成功
(怀疑是数据文件中的scn大于data header scn)
2.对于这样的问题，如果使用bbed修改所有数据文件header的scn不知道是否可以解决
3.如果rman只备份了数据文件而没有任何一个归档日志，数据库通过隐含参数还是可以open，抢救数据
补充一次无归档日志，scn不一致，有offline文件的恢复
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file; FILE# ONLINE_STATUS TO_CHAR(CHANGE#,'999999999 ---------- -------------- -------------------------- 1 ONLINE 869697 2 ONLINE 869732 3 ONLINE 869768 4 ONLINE 869768 5 OFFLINE 868415 SQL> select file#,to_char(checkpoint_change#,'999999999999'), 2 to_char(last_change#,'999999999999') from v$datafile; FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(LAST_CHANGE#,'9999 ---------- -------------------------- -------------------------- 1 870009 870009 2 870009 870009 3 870009 870009 4 870009 870009 5 868415 868810 SQL> recover database until cancel; ORA-00279: change 869697 generated at 03/15/2012 12:11:57 needed for thread 1 ORA-00289: suggestion : /u01/oracle/oradata/archivelog/ora11g/1_29_777766629.dbf ORA-00280: change 869697 for thread 1 is in sequence #29 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01152: file 2 was not restored from a sufficiently old backup ORA-01110: data file 2: '/u01/oracle/oradata/ora11g/sysaux01.dbf' ORA-01112: media recovery not started SQL> select file#,to_char(checkpoint_change#,'999999999999'), 2 to_char(RESETLOGS_CHANGE#,'999999999999') 3 from v$datafile_header; FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#, ---------- -------------------------- -------------------------- 1 869697 787897 2 869732 787897 3 869768 787897 4 869768 787897 5 868415 787897 SQL> create pfile='/tmp/pfile' from spfile; File created. 在pfile中增加 _allow_resetlogs_corruption=true _allow_error_simulation=TRUE(10g及其以上版本需要) SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup pfile='/tmp/pfile' mount; ORACLE instance started. Total System Global Area 368263168 bytes Fixed Size 1345016 bytes Variable Size 293603848 bytes Database Buffers 67108864 bytes Redo Buffers 6205440 bytes Database mounted. SQL> alter session set events '10015 trace name adjust_scn level 2'; Session altered. SQL> recover database until cancel; ORA-00279: change 869697 generated at 03/15/2012 12:11:57 needed for thread 1 ORA-00289: suggestion : /u01/oracle/oradata/archivelog/ora11g/1_29_777766629.dbf ORA-00280: change 869697 for thread 1 is in sequence #29 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01152: file 2 was not restored from a sufficiently old backup ORA-01110: data file 2: '/u01/oracle/oradata/ora11g/sysaux01.dbf' ORA-01112: media recovery not started SQL> alter database datafile 5 online; Database altered. SQL> alter database open resetlogs; Database altered. SQL> select file#,online_status,to_char(change#,'999999999999') 2 from v$recover_file; no rows selected