丢失归档文件情况下的数据文件的恢复(二)
【转载声明】本文为转载文章，原作者「李华荣」，版权归原作者所有。 原文链接：https://mp.weixin.qq.com/s/iYdBKZHdSB9iTEaritMeqA
公众号：杨建荣的学习笔记 · 作者：李华荣 · 发布：2016-04-18 23:56:47 · 原文链接
1.1.1 模拟system数据文件丢失且删除归档
首先，rman备份1号system数据文件：
RMAN> backupdatafile 1;
handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2016_04_07/o1_mf_nnndf_TAG20160407T170035_cjd8fn42_.bkptag=TAG20160407T170035 comment=NONE
删除1号数据文件且删除归档，重启报错：
[oracle@rhel5:/home/oracle]# sqlplus / as sysdba
SYS@ora11g> drop table T_LHR_20160407_02 ;
drop table T_LHR_20160407_02
*
ERROR at line 1:
ORA-00942: table or view does notexist
SYS@ora11g> create tableT_LHR_20160407_02 as select * from all_objects;
Table created.
SYS@ora11g> update T_LHR_20160407_02 set object_id=100;
72458 rows updated.
SYS@ora11g> commit;
Commit complete.
====》 SYSTEM表空间不能被offline，普通文件可以，我们os级别删除即可
SYS@ora11g> select count(1) fromT_LHR_20160407_02;
COUNT(1)
———-
72458
SYS@ora11g> col name format a60
SYS@ora11g> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
——————
2217678
SYS@ora11g> alter system switch logfile;
/
/
System altered.
System altered.
System altered.
SYS@ora11g> col name format a60
SYS@ora11g> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
——————
2220979
SYS@ora11g> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a;
FILE# NAME REC CHECKPOINT_CHANGE# STATUS
———- ————————————————————— —————— ——-
1/u01/app/oracle/oradata/ora11g/system01.dbf NO 2220979ONLINE
2/u01/app/oracle/oradata/ora11g/sysaux01.dbf NO 2220979ONLINE
3/u01/app/oracle/oradata/ora11g/undotbs01.dbf NO 2220979ONLINE
4/u01/app/oracle/oradata/ora11g/users01.dbf NO 2220979ONLINE
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf NO 2220979ONLINE
6/u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf NO 2220979ONLINE
7/u01/app/oracle/oradata/ora11g/lxtbs01.dbf NO 2220979ONLINE
7 rows selected.
SYS@ora11g> select file#,online_status,change# from v$recover_file;
no rows selected
====》切换日志后我们可以看到scn号都保持一致，都是2220979 ，下边我们来删除1号文件
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/system01.dbf
SYS@ora11g> alter system switch logfile;
System altered.
SYS@ora11g> /
System altered.
SYS@ora11g> /
====》切换日志过程中有一定几率数据库down掉，若没有就shutdown abort掉，然后重启
SYS@ora11g> shutdown abort
ORACLE instance shut down.
SYS@ora11g> startup
Database mounted.
ORA-01157:cannot identify/lock data file 1 – see DBWR trace file
ORA-01110:data file 1: ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
SYS@ora11g> select file#,online_status,change# from v$recover_file;
FILE# ONLINE_ CHANGE#
———- ——- ———-
1 ONLINE 0
====》可以看到1号数据文件找不到，然后我们删除归档，确保不能通过recover来恢复
[oracle@rhel5:/home/oracle]# cd /u01/app/oracle/fast_recovery_area/ORA11G/archivelog
[oracle@rhel5:/u01/app/oracle/fast_recovery_area/ORA11G/archivelog]#ll
total 4
drwxr-x— 2 oracle asmadmin 4096Apr 7 17:09 2016_04_07
[oracle@rhel5:/u01/app/oracle/fast_recovery_area/ORA11G/archivelog]# rm -rf *
[oracle@rhel5:/u01/app/oracle/fast_recovery_area/ORA11G/archivelog]#cd
接下来，我们通过rman来还原1号数据文件，由于归档丢失，不能recover，若非system，undo的数据文件，我们可以offline 掉，然后就可以打开数据库了，但是system必须online才能打开数据库，所以目前数据库不能打开：
[oracle@rhel5:/home/oracle]# rman target /
RMAN> restoredatafile 1;
channel ORA_DISK_1: restorecomplete, elapsed time: 00:01:55
Finished restore at 2016-04-0717:12:36
RMAN> recoverdatafile 1;
Starting recover at 2016-04-0717:14:48
using channel ORA_DISK_1
starting media recovery
RMAN-00571:===========================================================
RMAN-00569: =============== ERRORMESSAGE STACK FOLLOWS ===============
RMAN-00571:===========================================================
RMAN-03002: failure of recovercommand at 04/07/2016 17:14:49
RMAN-06053: unable to perform mediarecovery because of missing log
RMAN-06025: no backup of archivedlog for thread 1 with sequence 11 and starting SCN of 2221005 found to restore
RMAN-06025: no backup of archivedlog for thread 1 with sequence 10 and starting SCN of 2221002 found to restore
RMAN-06025: no backup of archivedlog for thread 1 with sequence 9 and starting SCN of 2220999 found to restore
RMAN-06025: no backup of archivedlog for thread 1 with sequence 8 and starting SCN of 2220996 found to restore
RMAN-06025: no backup of archivedlog for thread 1 with sequence 7 and starting SCN of 2220979 found to restore
RMAN-06025: no backup of archivedlog for thread 1 with sequence 6 and starting SCN of 2220975 found to restore
RMAN-06025: no backup of archivedlog for thread 1 with sequence 5 and starting SCN of 2220972 found to restore
RMAN-06025: no backup of archivedlog for thread 1 with sequence 4 and starting SCN of 2217677 found to restore
RMAN> recoverdatafile 1 until sequence 3 thread 1;
Starting recover at 2016-04-0717:20:09
using channel ORA_DISK_1
RMAN-00571:===========================================================
RMAN-00569: =============== ERRORMESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recovercommand at 04/07/2016 17:20:09
RMAN-06556: datafile 1 must berestored from backup older than SCN 2197424
====》可以看到由于归档丢失，不能执行recover操作，rman的不完全恢复也不能完成，下边通过SQL的不完全恢复试试
SYS@ora11g> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs mediarecovery
ORA-01110: data file 1:’/u01/app/oracle/oradata/ora11g/system01.dbf’
SYS@ora11g> recover database using backupcontrolfile until cancel;
ORA-00279: change 2218426 generatedat 04/07/2016 17:00:36 needed for thread 1
ORA-00289: suggestion :/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc
ORA-00280: change 2218426 for thread1 is in sequence #4
Specify log: {<RET>=suggested| filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log’/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc’
ORA-27037: unable to obtain filestatus
Linux-x86_64 Error: 2: No such fileor directory
Additional information: 3
ORA-00308: cannot open archived log’/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc’
ORA-27037: unable to obtain filestatus
Linux-x86_64 Error: 2: No such fileor directory
Additional information: 3
ORA-01547: warning: RECOVERsucceeded but OPEN RESETLOGS would get error below
ORA-01194: file 2 needs morerecovery to be consistent
ORA-01110: data file 2:’/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
SYS@ora11g> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS orNORESETLOGS option for database open
SYS@ora11g> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 2 needs morerecovery to be consistent
ORA-01110: data file 2:’/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
SYS@ora11g> col name format a60
SYS@ora11g> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
——————
2221005
SYS@ora11g> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a;
FILE# NAME REC CHECKPOINT_CHANGE# STATUS
———————————————————————- ——————— ——-
1/u01/app/oracle/oradata/ora11g/system01.dbf 2218426ONLINE
2/u01/app/oracle/oradata/ora11g/sysaux01.dbf 2221005ONLINE
…
SYS@ora11g> select file#,online_status,change# from v$recover_file;
FILE# ONLINE_ CHANGE#
———- ——- ———-
1 ONLINE 2218426
可以看到1号文件和其他文件的Ckp SCN 不同，所以数据库不能打开，下边分别介绍基于隐含参数_allow_resetlogs_corruption 和 BBED修改文件头来恢复打开数据库。
1.1.1.1 方法一：隐含参数_allow_resetlogs_corruption
SYS@ora11g> alter database openresetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 2 needs morerecovery to be consistent
ORA-01110: data file 2:’/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
SYS@ora11g> select file#,online_status,change# from v$recover_file;
FILE# ONLINE_ CHANGE#
———- ——- ———-
1 ONLINE 2218426
SYS@ora11g> alter system set “_allow_resetlogs_corruption”=truescope=spfile;
System altered.
SYS@ora11g> recover database using backup controlfile until cancel;
ORA-00279: change 2218426 generatedat 04/07/2016 17:00:36 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc
ORA-00280: change 2218426 for thread1 is in sequence #4
Specify log: {<RET>=suggested| filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log’/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc’
ORA-27037: unable to obtain filestatus
Linux-x86_64 Error: 2: No such fileor directory
Additional information: 3
ORA-00308: cannot open archived log’/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc’
ORA-27037: unable to obtain filestatus
Linux-x86_64 Error: 2: No such fileor directory
Additional information: 3
ORA-01547: warning: RECOVERsucceeded but OPEN RESETLOGS would get error below
ORA-01194: file 2 needs morerecovery to be consistent
ORA-01110: data file 2:’/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
SYS@ora11g> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS orNORESETLOGS option for database open
SYS@ora11g> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 2 needs morerecovery to be consistent
ORA-01110: data file 2:’/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
====》不完全恢复不能打开数据库，只能采用隐含参数了
SYS@ora11g> startup force
ORACLE instance started.
Database mounted.
ORA-01589: must use RESETLOGS orNORESETLOGS option for database open
SYS@ora11g> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server sessionterminated by fatal error
ORA-00600: internal error code,arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], [], [], [],[], []
ORA-00600: internal error code,arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [],[], []
ORA-01092: ORACLE instanceterminated. Disconnection forced
ORA-00600: internal error code,arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [],[], []
Process ID: 15341
Session ID: 191 Serial number: 3
[oracle@rhel5:/home/oracle]# sqlplus / as sysdba
SYS@ora11g> startup mount
Database mounted.
SYS@ora11g> alter database open;
Database altered.
SYS@ora11g> col name format a60
SYS@ora11g> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
——————
2238438
SYS@ora11g> SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROMv$datafile a;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
———————————————————————- —————————— ——-
1/u01/app/oracle/oradata/ora11g/system01.dbf 2238438 SYSTEM
2/u01/app/oracle/oradata/ora11g/sysaux01.dbf 2238438 ONLINE
…
7 rows selected.
SYS@ora11g> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a;
FILE# NAME REC CHECKPOINT_CHANGE# STATUS
———————————————————————- ——————— ——-
1 /u01/app/oracle/oradata/ora11g/system01.dbf NO 2238438ONLINE
2/u01/app/oracle/oradata/ora11g/sysaux01.dbf NO 2238438ONLINE
…
7 rows selected.
====》ckp scn 一致，数据库打开，下边我们把_allow_resetlogs_corruption这个参数从spfile中删除，然后备份数据库，我们测试就把该参数删除即可
SYS@ora11g> alter system set “_allow_resetlogs_corruption”=falsescope=spfile;
System altered.
SYS@ora11g> alter system reset “_allow_resetlogs_corruption” scope=spfile sid=’*’;
System altered.
SYS@ora11g> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora11g> startup
ORACLE instance started.
Database mounted.
Database opened.
SYS@ora11g> show parameter _allow_resetlogs_corruption
====》隐含参数已从spfile中清除掉了，备份数据库即可