Oracle 之利用BBED修改数据块SCN----极端环境下的数据恢复（一）

测试环境
OS：RedHat5.9
Oracle:12.1.0.2
 BBED(OracleBlockBrowerandEDitor Tool)，用来直接查看和修改数据文件数据的一个工具，是Oracle一款内部工具，可以直接修改Oracle数据文件块的内容，在一些极端恢复场景下比较有用。该工具不受Oracle支持，所以默认是没有生成可执行文件的，在使用前需要重新连接。
1.安装BBED
这个之前写过详细步骤，请参考：
http://blog.csdn.net/shiyu1157758655/article/details/56279479
2.使用参数文件连接BBED
a）查询出当前的数据文件并保存在文本文件中
SQL> select file#||' '||name||' '||bytes from v$datafile ;

FILE#||''||NAME||''||BYTES
--------------------------------------------------------------------------------
1 /data/oradata/ocrl/datafile/system01.dbf 912261120
2 /data/oradata/ocrl/datafile/sysaux01.dbf 1247805440
3 /data/oradata/ocrl/datafile/undotbs01.dbf 765460480
4 /data/oradata/ocrl/datafile/users01.dbf 28835840
5 /data/oradata/ocrl/datafile/test.dbf 10485760
6 /data/oradata/ocrl/datafile/tstest.dbf 10485760

6 rows selected.

SQL>
注意：保存在文件里的文件号要与我们数据库查询出来的FILE#相同
ocrl:/home/oracle@oracle1>cat psdb_file.txt
1 /data/oradata/ocrl/datafile/system01.dbf 912261120
2 /data/oradata/ocrl/datafile/sysaux01.dbf 1247805440
3 /data/oradata/ocrl/datafile/undotbs01.dbf 765460480
4 /data/oradata/ocrl/datafile/users01.dbf 28835840
5 /data/oradata/ocrl/datafile/test.dbf 10485760
6 /data/oradata/ocrl/datafile/tstest.dbf 10485760

b）BBED使用参数文件登陆
ocrl:/home/oracle@oracle1>cat psdb_file.txt
1 /data/oradata/ocrl/datafile/system01.dbf 912261120
2 /data/oradata/ocrl/datafile/sysaux01.dbf 1247805440
3 /data/oradata/ocrl/datafile/undotbs01.dbf 765460480
4 /data/oradata/ocrl/datafile/users01.dbf 28835840
5 /data/oradata/ocrl/datafile/test.dbf 10485760
6 /data/oradata/ocrl/datafile/tstest.dbf 10485760

ocrl:/home/oracle@oracle1>cat bbed_parameter.txt
blocksize=8192
listfile=/home/oracle/psdb_file.txt
mode=edit
ocrl:/home/oracle@oracle1>bbed parfile=/home/oracle/bbed_parameter.txt
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Tue Feb 21 15:17:57 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /data/oradata/ocrl/datafile/system01.dbf                        111360
     2  /data/oradata/ocrl/datafile/sysaux01.dbf                        152320
     3  /data/oradata/ocrl/datafile/undotbs01.dbf                        93440
     4  /data/oradata/ocrl/datafile/users01.dbf                           3520
     5  /data/oradata/ocrl/datafile/test.dbf                              1280
     6  /data/oradata/ocrl/datafile/tstest.dbf                            1280

BBED>

3.破坏system表空间文件
注意：这里最好先做好备份

a）切换日志模拟生产交易，更新SCN

ocrl:/data/oradata/ocrl/datafile@oracle1>cp system01.dbf system01.dbf.bak

SQL> alter system switch logfile;//多切几次

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>
b）用旧的system文件直接复制替换掉新的
ocrl:/data/oradata/ocrl/datafile@oracle1>cp system01.dbf.bak system01.dbf
再次切换日志模拟生产交易
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /
/
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 9115
Session ID: 20 Serial number: 33669
出现问题，数据库直接宕掉

4.利用BBED进行恢复

a）登陆打开数据库

ocrl:/home/oracle@oracle1>sqlplus /  as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 21 14:53:47 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  754974720 bytes
Fixed Size            2928968 bytes
Variable Size          524291768 bytes
Database Buffers      222298112 bytes
Redo Buffers            5455872 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/data/oradata/ocrl/datafile/system01.dbf'


SQL> col name for a50
SQL> select name,checkpoint_change# from v$datafile;

NAME                           CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/data/oradata/ocrl/datafile/system01.dbf             12717804
/data/oradata/ocrl/datafile/sysaux01.dbf             12717804
/data/oradata/ocrl/datafile/undotbs01.dbf             12717804
/data/oradata/ocrl/datafile/users01.dbf              12717804
/data/oradata/ocrl/datafile/test.dbf                 12717804
/data/oradata/ocrl/datafile/tstest.dbf                 12717804

6 rows selected.
b）scn 转换成16进制
SQL> select to_char(12717804,'xxxxxxxx') from dual;

TO_CHAR(1
---------
   c20eec

SQL> select name,checkpoint_change# from v$datafile_header;

NAME                           CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/data/oradata/ocrl/datafile/system01.dbf             12717402
/data/oradata/ocrl/datafile/sysaux01.dbf             12717804
/data/oradata/ocrl/datafile/undotbs01.dbf             12717804
/data/oradata/ocrl/datafile/users01.dbf              12717804
/data/oradata/ocrl/datafile/test.dbf                 12717804
/data/oradata/ocrl/datafile/tstest.dbf                 12717804

6 rows selected.
可以看到数据库system01.dbf的scn点为12717402明显是用的备份的数据文件。oracle在open数据库时要对控制文件，数据文件头的scn进行检查，一致才能打开，所以这里我们通过ddeb来修改数据文件头让它和其他的数据文件的scn相同，达到起库的目的。
c）BBED修复system头文件
ocrl:/home/oracle@oracle1>bbed parfile=bbed_parameter.txt
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Tue Feb 21 14:58:25 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************
用BBED查询bbed01数据文件结构信息
BBED> set dba 1,1
    DBA                0x00400001 (4194305 1,1)
Bbed查看kcvfh信息
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
   struct kcvcpscn, 8 bytes                 @484     
      ub4 kscnbas                           @484      0x00c20d5a
      ub2 kscnwrp                           @488      0x0000
   ub4 kcvcptim                             @492      0x37d28348
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500     
      struct kcvcprba, 12 bytes             @500     
         ub4 kcrbaseq                       @500      0x000000bb
         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> set filename '/data/oradata/ocrl/datafile/system01.dbf'
    FILENAME           /data/oradata/ocrl/datafile/system01.dbf

BBED查看kcvfh信息

BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
   struct kcvcpscn, 8 bytes                 @484     
      ub4 kscnbas                           @484      0x00c20d5a
      ub2 kscnwrp                           @488      0x0000
   ub4 kcvcptim                             @492      0x37d28348
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500     
      struct kcvcprba, 12 bytes             @500     
         ub4 kcrbaseq                       @500      0x000000bb
         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 dba 4,1 offset 484
 File: /data/oradata/ocrl/datafile/users01.dbf (4)
 Block: 1       Offsets:  484 to  995  Dba:0x01000001
-------------------------------------------------------
  ec0ec200 00000000 8f83d237 010091c3 l ??......?...
 c3000000 02000000 10000000 02000000 l ?..............
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 0d000d00 0d000100 00000000 00000000 l ................
 00000000 02000001 77399100 00000000 l ........w9......
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................

 <16 bytes per line>
查看system文件头
BBED> d /v dba 1,1 offset 484
 File: /data/oradata/ocrl/datafile/system01.dbf (1)
 Block: 1       Offsets:  484 to  995  Dba:0x00400001
-------------------------------------------------------
  5a0dc200 00000000 4883d237 010091c3 l Z.?....H.?...
 bb000000 02000000 10000000 02000000 l ?..............
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 0d000d00 0d000100 00000000 00000000 l ................
 00000000 02004000 6c3d9200 00000000 l ......@.l=......
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 0215e11b l ..............?
 fb1ef7c6 d7131af0 7f44f1f4 59000000 l ?髌?.?D耵Y...
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 0013b9e3 l ..............广
 13aac34f aebfb3af e5f7d22b 03000600 l .?O?朝鬻?....
 f3b89100 00000000 00000000 00000000 l 蟾..............
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................

 <16 bytes per line>
修改system文件头
BBED> modify /x ec0ec200 dba 1,1 offset 484
BBED-00209: invalid number (ec0ec200)

BBED>  modify /x ec0e dba 1,1 offset 484 //填写前面4位
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /data/oradata/ocrl/datafile/system01.dbf (1)
 Block: 1                Offsets:  484 to  995           Dba:0x00400001
------------------------------------------------------------------------
 ec0ec200 00000000 4883d237 010091c3 bb000000 02000000 10000000 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 02004000 6c3d9200 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 0215e11b fb1ef7c6 d7131af0 7f44f1f4 59000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0013b9e3
 13aac34f aebfb3af e5f7d22b 03000600 f3b89100 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 dba 1,1 apply
Check value for File 1, Block 1:
current = 0xcd35, required = 0xcd35
查看修改后的system头文件
BBED> d /v dba 1,1 offset 484
 File: /data/oradata/ocrl/datafile/system01.dbf (1)
 Block: 1       Offsets:  484 to  995  Dba:0x00400001
-------------------------------------------------------
 ec0ec200 00000000 4883d237 010091c3 l ??....H.?...
 bb000000 02000000 10000000 02000000 l ?..............
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 0d000d00 0d000100 00000000 00000000 l ................
 00000000 02004000 6c3d9200 00000000 l ......@.l=......
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 0215e11b l ..............?
 fb1ef7c6 d7131af0 7f44f1f4 59000000 l ?髌?.?D耵Y...
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 0013b9e3 l ..............广
 13aac34f aebfb3af e5f7d22b 03000600 l .?O?朝鬻?....
 f3b89100 00000000 00000000 00000000 l 蟾..............
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................

 <16 bytes per line>

d）打开数据库
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/data/oradata/ocrl/datafile/system01.dbf'


SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

至此测试恢复完成；

以上如有不正确的地方，还请指正。欢迎加入Oracle QQ讨论群：425614362