本文主要介绍非归档模式下offline数据文件的恢复,测试过程如下:
SQL> select * from v$version where rownum<3;
BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production
SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/prod_arch Oldest online log sequence 17 Current log sequence 19
---控件文件中的SCN SQL> select file#,status,checkpoint_change# from v$datafile order by 1;
FILE# STATUS CHECKPOINT_CHANGE# ---------- ------- ------------------ 1 SYSTEM 554606 2 ONLINE 554606 3 ONLINE 554606 4 ONLINE 554606 5 ONLINE 554606
--数据文件头中的SCN
SQL> select file#,status,checkpoint_change# from v$datafile_header order by 1;
FILE# STATUS CHECKPOINT_CHANGE# ---------- ------- ------------------ 1 ONLINE 554606 2 ONLINE 554606 3 ONLINE 554606 4 ONLINE 554606 5 ONLINE 554606
--日志文件的情况
SQL> select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 19 52428800 1 NO CURRENT 543593 29-OCT-13 2 1 17 52428800 1 YES INACTIVE 500547 29-OCT-13 3 1 18 52428800 1 YES INACTIVE 521183 29-OCT-13
--offline数据文件5,在为进行日志切换的i情况下直接rcover然后在online就可以恢复。
SQL> alter database datafile 5 offline drop;
Database altered.
SQL> alter database datafile 5 online; alter database datafile 5 online * ERROR at line 1: ORA-01113: file 5 needs media recovery if it was restored from backup, or END BACKUP if it was not ORA-01110: data file 5: '/u01/app/oracle/oradata/ptod/zxy01.dbf'
SQL> recover datafile 5; Media recovery complete. SQL> alter database datafile 5 online;Database altered.
二、在日志被覆盖的情况下:
SQL> alter system switch logfile;
System altered.
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE# ---------- ------------------ 1 554973 2 554973 3 554973 4 554973 5 554973
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 22 52428800 1 NO INACTIVE 554969 30-OCT-13 2 1 23 52428800 1 NO INACTIVE 554971 30-OCT-13 3 1 24 52428800 1 NO CURRENT 554973 30-OCT-13
SQL> alter database datafile 5 offline drop;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 28 52428800 1 NO CURRENT 555067 30-OCT-13 2 1 26 52428800 1 NO INACTIVE 555052 30-OCT-13 3 1 27 52428800 1 NO INACTIVE 555055 30-OCT-13
SQL> alter database datafile 5 online; alter database datafile 5 online * ERROR at line 1: ORA-01113: file 5 needs media recovery if it was restored from backup, or END BACKUP if it was not ORA-01110: data file 5: '/u01/app/oracle/oradata/ptod/zxy01.dbf'
SQL> recover datafile 5; ORA-00279: change 554973 generated at 10/30/2013 23:28:26 needed for thread 1 ORA-00289: suggestion : /u01/prod_arch/1_24_829999096.dbf ORA-00280: change 554973 for thread 1 is in sequence #24
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled.
SQL> alter database datafile 5 online; alter database datafile 5 online * ERROR at line 1: ORA-01113: file 5 needs media recovery if it was restored from backup, or END BACKUP if it was not ORA-01110: data file 5: '/u01/app/oracle/oradata/ptod/zxy01.dbf'
---下面用bbed来检查点信息:
BBED> info File# Name Size(blks) ----- ---- ---------- 1 /u01/app/oracle/oradata/ptod/system01.dbf 0 2 /u01/app/oracle/oradata/ptod/undotbs03.dbf 0 3 /u01/app/oracle/oradata/ptod/sysaux01.dbf 0 4 /u01/app/oracle/oradata/ptod/users01.dbf 0 5 /u01/app/oracle/oradata/ptod/zxy01.dbf 0
BBED> set file 5 block 1 FILE# 5 BLOCK# 1
BBED> p kcvfhckp struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x000877dd---数据文件头的SCN低4字节值 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x317c161a ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000018---数据文件当前写的redolog sequence。 ub4 kcrbabno @504 0x00000002---数据文件当前写的redolog的block号 ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00
因此我只需要把红色部分的信息改成与正常的数据文件的值一样,骗过oracle的检测。
BBED> set file 4 blcok 1 FILE# 4 BBED-00202: invalid parameter (blcok)
BBED> set file 4 block 1 FILE# 4 BLOCK# 1BBED> p kcvfhckp struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x0008783b ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x317c1711 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x0000001c ub4 kcrbabno @504 0x00000002 ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00
BBED> d /v offset 484 count 16 File: /u01/app/oracle/oradata/ptod/users01.dbf (4) Block: 1 Offsets: 484 to 499 Dba:0x01000001 ------------------------------------------------------- 3b780800 0000af76 11177c31 01008e00 l ;x....|1....
<16 bytes per line>
BBED> set file 5 block 1 FILE# 5 BLOCK# 1
BBED> modify /x 3b7808 offset 484 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /u01/app/oracle/oradata/ptod/zxy01.dbf (5) Block: 1 Offsets: 484 to 499 Dba:0x01400001 ------------------------------------------------------------------------ 3b780800 0000af76 1a167c31 01008e00
<32 bytes per line>
BBED> d /v offset 484 count 16 File: /u01/app/oracle/oradata/ptod/zxy01.dbf (5) Block: 1 Offsets: 484 to 499 Dba:0x01400001 ------------------------------------------------------- 3b780800 0000af76 1a167c31 01008e00 l ;x....|1....
<16 bytes per line>
BBED> modify /x 18 offset 500 File: /u01/app/oracle/oradata/ptod/zxy01.dbf (5) Block: 1 Offsets: 500 to 515 Dba:0x01400001 ------------------------------------------------------------------------ 18000000 02000000 10000000 02000000
<32 bytes per line>
BBED> sum apply Check value for File 5, Block 1: current = 0x9989, required = 0x9989
BBED> exit
----在查看SCN的情况,试着recover
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE# ---------- ------------------ 1 555687 2 555687 3 555687 4 555687 5 554973
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE# ---------- ------------------ 1 555687 2 555687 3 555687 4 555687 5 555067
SQL> alter database datafile 5 online; alter database datafile 5 online * ERROR at line 1: ORA-01113: file 5 needs media recovery if it was restored from backup, or END BACKUP if it was not ORA-01110: data file 5: '/u01/app/oracle/oradata/ptod/zxy01.dbf'
SQL> recover datafile 5; Media recovery complete. SQL> alter database datafile 5 online;Database altered.
成功online数据文件5!