A-A+
BBED修复需介质恢复的文件
参考文档：disassembling the oracle data block,writter by graham thornton 2005
注：该文档是在9i下进行的，在11g中数据块格式都发生了变化，主要参数名未变，但偏移量发生变化
操作系统信息
[oracle@rhl6 lib]$ uname -a Linux rhl6.0x64 2.6.32-71.el6.x86_64 #1 SMP Wed Sep 1 01:33:01 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
数据库版本
SQL> set line 400
SQL> select * from gv$version;
INST_ID BANNER
---------- --------------------------------------------------------------------------------
1 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
1 PL/SQL Release 11.2.0.3.0 - Production
1 CORE 11.2.0.3.0 Production
1 TNS for Linux: Version 11.2.0.3.0 - Production
1 NLSRTL Version 11.2.0.3.0 - Production
测试数据
SQL> conn yallonking/yallonking Connected. SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss'; Session altered. SQL> select rowid,t.* from yallonking t; ROWID ID NAME MY_DATE ------------------ ---------- -------------------- ------------------- AAARciAAFAAAACHAAA 1 yallonking 2013/07/22 09:44:53 AAARciAAFAAAACHAAB 2 oraking 2013/07/22 09:45:07 AAARciAAFAAAACHAAC 3 xiaolong 2013/07/22 09:50:09
人为破坏数据库一致性
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/ora11gr2/system01.dbf /u01/app/oracle/oradata/ora11gr2/sysaux01.dbf /u01/app/oracle/oradata/ora11gr2/undotbs01.dbf /u01/app/oracle/oradata/ora11gr2/users01.dbf /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf SQL> !cp /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf /tmp/yallonking01.dbf SQL> insert into yallonking values(4,'other',sysdate); 1 row created. SQL> commit; Commit complete. SQL> conn /as sysdba Connected. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
人为用旧的数据文件替换原来一致性的数据文件5
[oracle@rhl6 ~]$ mv /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf /tmp/yallonking01.dbf_curr [oracle@rhl6 ~]$ mv /tmp/yallonking01.dbf /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf
启动数据库会报需要介质恢复
SQL> startup ORACLE instance started. Total System Global Area 939495424 bytes Fixed Size 2233960 bytes Variable Size 545261976 bytes Database Buffers 385875968 bytes Redo Buffers 6123520 bytes Database mounted. ORA-01113: file 5 needs media recovery ORA-01110: data file 5: '/u01/app/oracle/oradata/ora11gr2/yallonking01.dbf'
数据库是非归档的
SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 66 Current log sequence 68
注意：猜测刚刚进行的操作还没有被redo循环写覆盖，所以应该在非归档下可以完全恢复。（这里也体现了redo对于完全恢复的重要性）
查看数据库一致性
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
761363
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 761363
2 761363
3 761363
4 761363
5 761363
SQL> select file#,change# from v$recover_file;
FILE# CHANGE#
---------- ----------
5 760842
尝试恢复介质恢复
SQL> recover datafile 5; Media recovery complete. SQL> alter database open; Database altered.
查看后台日志
Mon Jul 22 10:59:14 2013 db_recovery_file_dest_size of 4977 MB is 0.00% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Mon Jul 22 10:59:38 2013 ALTER DATABASE RECOVER datafile 5 Media Recovery Start Serial Media Recovery started WARNING! Recovering data file 5 from a fuzzy backup. It might be an online backup taken without entering the begin backup command. Recovery of Online Redo Log: Thread 1 Group 2 Seq 68 Reading mem 0 Mem# 0: /u01/app/oracle/oradata/ora11gr2/redo02.log Media Recovery Complete (ora11gr2) Completed: ALTER DATABASE RECOVER datafile 5 Mon Jul 22 10:59:52 2013 alter database open Mon Jul 22 10:59:52 2013 Thread 1 opened at log sequence 68 Current log# 2 seq# 68 mem# 0: /u01/app/oracle/oradata/ora11gr2/redo02.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Mon Jul 22 10:59:52 2013 SMON: enabling cache recovery [2312] Successfully onlined Undo Tablespace 2. Undo initialization finished serial:0 start:2140524 end:2140564 diff:40 (0 seconds) Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is ZHS16GBK No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Mon Jul 22 10:59:53 2013 QMNC started with pid=20, OS id=2378 Completed: alter database open Mon Jul 22 10:59:53 2013 Starting background process CJQ0 Mon Jul 22 10:59:54 2013 CJQ0 started with pid=22, OS id=2392
验证数据
SQL> select * from yallonking;
ID NAME MY_DATE
---------- -------------------- -------------------
1 yallonking 2013/07/22 09:44:53
2 oraking 2013/07/22 09:45:07
3 xiaolong 2013/07/22 09:50:09
4 other 2013/07/22 10:41:36
==============================================================================================
下边我们继续试验，这次是将数据文件更加陈旧，也就是在redo中完全没有了其相关的可用来恢复的记录
我们首先通过用老的数据文件替换新文件的方法构造一个需要介质恢复的场景
SQL> alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss';
Session altered.
SQL> select rowid,t.* from yallonking t;
ROWID ID NAME MY_DATE
------------------ ---------- -------------------- -------------------
AAARciAAFAAAACHAAA 1 yallonking 2013/07/22 09:44:53
AAARciAAFAAAACHAAB 2 oraking 2013/07/22 09:45:07
AAARciAAFAAAACHAAC 3 xiaolong 2013/07/22 09:50:09
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !cp /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf /tmp/yallonking01.dbf_bak
SQL> startup
ORACLE instance started.
Total System Global Area 939495424 bytes
Fixed Size 2233960 bytes
Variable Size 545261976 bytes
Database Buffers 385875968 bytes
Redo Buffers 6123520 bytes
Database mounted.
Database opened.
SQL> insert into yallonking.yallonking values(4,'other',sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from yallonking.yallonking;
ID NAME MY_DATE
---------- -------------------- ---------
1 yallonking 22-JUL-13
2 oraking 22-JUL-13
3 xiaolong 22-JUL-13
4 other 23-JUL-13
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> cp /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf /tmp/yallonking01.dbf_curr
SP2-0734: unknown command beginning "cp /u01/ap..." - rest of line ignored.
SQL> !cp /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf /tmp/yallonking01.dbf_curr
SQL> !cp /tmp/yallonking01.dbf_bak /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 939495424 bytes
Fixed Size 2233960 bytes
Variable Size 545261976 bytes
Database Buffers 385875968 bytes
Redo Buffers 6123520 bytes
Database mounted.
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/ora11gr2/yallonking01.dbf'
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
766347
SQL> select file#,checkpoint_change#,status from v$datafile;
FILE# CHECKPOINT_CHANGE# STATUS
---------- ------------------ -------
1 766347 SYSTEM
2 766347 ONLINE
3 766347 ONLINE
4 766347 ONLINE
5 766347 ONLINE
SQL> set line 400
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
5 ONLINE ONLINE 765884 23-JUL-13
SQL> recover datafile 5;
ORA-00279: change 765884 generated at 07/23/2013 12:46:20 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORA11GR2/archivelog/2013_07_23/o1_mf_1_68_%u_
.arc
ORA-00280: change 765884 for thread 1 is in sequence #68
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/ORA11GR2/archivelog/2013_07_23/o1_mf_1_68_%u
_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
此处发现，所有文件均是online状态，而且问题文件确实太陈旧了，而且数据库没有开启归档，没有可用于恢复的redo entry
我们首先看控制文件中关于该文件的相关记录
SQL> alter session set tracefile_identifier='wyl'; Session altered. SQL> alter session set events 'immediate trace name controlf level 3'; Session altered.
查看trace部分如下：
*************************************************************************** DATA FILE RECORDS *************************************************************************** (size = 520, compat size = 520, section max = 100, section in-use = 5, last-recid= 27, old-recno = 0, last-recno = 0) (extent = 1, blkno = 11, numrecs = 100) DATA FILE #1: name #4: /u01/app/oracle/oradata/ora11gr2/system01.dbf creation size=89600 block size=8192 status=0xe head=4 tail=4 dup=1 tablespace 0, index=1 krfil=1 prev_file=0 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00 Checkpoint cnt:85 scn: 0x0000.000bb18b 07/23/2013 12:55:01 Stop scn: 0x0000.000bb18b 07/23/2013 12:55:01 Creation Checkpointed at scn: 0x0000.00000029 12/25/2012 10:30:40 thread:1 rba:(0x1.3.10) DATA FILE #5: name #9: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf creation size=256 block size=8192 status=0xe head=9 tail=9 dup=1 tablespace 5, index=6 krfil=5 prev_file=0 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00 Checkpoint cnt:17 scn: 0x0000.000bb18b 07/23/2013 12:55:01 Stop scn: 0x0000.000bb18b 07/23/2013 12:55:01 Creation Checkpointed at scn: 0x0000.000b9775 07/22/2013 09:42:59 thread:1 rba:(0x44.5a9c.10)
下边我们尝试用BBED进行恢复
我们在trace中发现，控制文件中记录的5号和1号文件的scn均一致
首先需要注意以下参数
kscnbas @484 <<文件最后一次改变的scn
查找方法如下：
我们查看1号文件的信息
BBED> set dba 1,1
DBA 0x00400001 (4194305 1,1)
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x000bb18b
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x30f7aba5
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000048
ub4 kcrbabno @504 0x00000128
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> set dba 5,1
DBA 0x01400001 (20971521 5,1)
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x000bafbc //需要修改成和1号文件及上边trace中datafile 5中的scn
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x30f7a99c
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000044
ub4 kcrbabno @504 0x0000b559
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
下边我们修改5号文件的scn(kscnbas)
BBED> set dba 5,1
DBA 0x01400001 (20971521 5,1)
BBED> m /x 8bb1 dba 5,1 offset 484 //由于在linux下的字节序不同，此处的值和dump出来的需要两两逆序
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5)
Block: 1 Offsets: 484 to 995 Dba:0x01400001
------------------------------------------------------------------------
8bb10b00 00000000 9ca9f730 01000000 44000000 59b50000 10008997 02000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
0d000d00 0d000100 00000000 00000000 00000000 02004001 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply;
Check value for File 5, Block 1:
current = 0x3094, required = 0x3094
下边尝试继续恢复5号数据文件
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
Session altered.
SQL> select * from yallonking.yallonking;
ID NAME MY_DATE
---------- -------------------- -------------------
1 yallonking 2013/07/22 09:44:53
2 oraking 2013/07/22 09:45:07
3 xiaolong 2013/07/22 09:50:09
注：发现确实和我们之前猜测的结果一致，第4条数据因为跳过了redo entry而不存在。
http://www.traveldba.com/archives/276 请看这个。你的理解有的有错误
文档已重新更新…多谢指正…