A-A+

BBED修复需介质恢复的文件

2013年07月22日 Backup&Recovery 评论 2 条 阅读 7,796 次

参考文档: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而不存在。

2 条留言  访客:0 条  博主:0 条

  1. travel.liu

    http://www.traveldba.com/archives/276 请看这个。你的理解有的有错误

    • Yallon King

      文档已重新更新…多谢指正…

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: