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

青旅半醒 2022-07-12 10:40 329阅读 0赞

测试环境  
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