Oracle 之利用BBED修改数据块SCN----没有备份数据文件的数据恢复

测试环境 OS：redhat6.6 oracle:12.1.0.2  BBED(OracleBlockBrowerandEDitor Tool)，用来直接查看和修改数据文件数据的一个工具，是Oracle一款内部工具，可以直接修改Oracle数据文件块的内容，在一些极端恢复场景下比较有用。该工具不受Oracle支持，所以默认是没有生成可执行文件的，在使用前需要重新连接。 1.安装BBED [oracle@12cdock software]$ cp bbedus.msb $ORACLE_HOME/rdbms/mesg [oracle@12cdock software]$ cp sbbdpt.o $ORACLE_HOME/rdbms/lib [oracle@12cdock software]$ cp ssbbded.o $ORACLE_HOME/rdbms/lib [oracle@12cdock software]$ cd $ORACLE_HOME/rdbms/lib/ [oracle@12cdock lib]$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed Linking BBED utility (bbed) rm -f /u01/app/oracle/product/12.1.0.2/db_1/rdbms/lib/bbed /u01/app/oracle/product/12.1.0.2/db_1/bin/orald -o /u01/app/oracle/product/12.1.0.2/db_1/rdbms/lib/bbed -m64 -z noexecstack -Wl, --disable-new-dtags -L/u01/app/oracle/product/12.1.0.2/db_1/rdbms/lib/ -L/u01/app/oracle/product/12.1.0.2/db_1/lib/ -L/u01/app/oracle/product/12.1.0.2/db_1/lib/stubs/   /u01/app/oracle/product/12.1.0.2/db_1/lib/s0main.o /u01/app/oracle/product/12.1.0.2/db_1/rdbms/lib/ssbbded.o /u01/app/oracle/product/12.1.0.2/db_1/rdbms/lib/sbbdpt.o  `cat /u01/app/oracle/product/12.1.0.2/db_1/lib/ldflags`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -ldbtools12 -lclntsh -lclntshcore   `cat /u01/app/oracle/product/12.1.0.2/db_1/lib/ldflags`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12  `cat /u01/app/oracle/product/12.1.0.2/db_1/lib/ldflags`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnnz12 -lzt12 -lztkg12 -lztkg12 -lclient12 -lnnetd12  -lvsn12 -lcommon12 -lgeneric12 -lmm -lsnls12 -lnls12  -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12  `cat /u01/app/oracle/product/12.1.0.2/db_1/lib/ldflags`   -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12  `cat /u01/app/oracle/product/12.1.0.2/db_1/lib/ldflags`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lclient12 -lnnetd12  -lvsn12 -lcommon12 -lgeneric12   -lsnls12 -lnls12  -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lclient12 -lnnetd12  -lvsn12 -lcommon12 -lgeneric12 -lsnls12 -lnls12  -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12    `cat /u01/app/oracle/product/12.1.0.2/db_1/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/12.1.0.2/db_1/lib -lm     `cat /u01/app/oracle/product/12.1.0.2/db_1/lib/sysliblist` -ldl -lm   -L/u01/app/oracle/product/12.1.0.2/db_1/lib [oracle@12cdock lib]$ cp bbed /u01/app/oracle/product/12.1.0.2/db_1/bin/ BBED是Oracle 内部使用的命令，所以Oracle 不提供技术支持。 为了安全BBED设置了口令保护，默认密码为blockedit。 [oracle@12cdock lib]$ bbed Password:  BBED: Release 2.0.0.0.0 - Limited Production on Wed Feb 22 09:47:07 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> show  FILE#          0 BLOCK#         1 OFFSET         0 DBA            0x00000000 (0 0,1) FILENAME       BIFILE         bifile.bbd LISTFILE       BLOCKSIZE      8192 MODE           Browse EDIT           Unrecoverable IBASE          Dec OBASE          Dec WIDTH          80 COUNT          512 LOGFILE        log.bbd SPOOL          No 将现有数据文件在bbed中指定，没有的话先在sqlplus中创建 BBED> set filename '/u01/app/oracle/oradata/ORCL/datafile/test.dbf' FILENAME       /u01/app/oracle/oradata/ORCL/datafile/test.dbf BBED> show all; FILE#          0 BLOCK#         1 OFFSET         0 DBA            0x00000000 (0 0,1) FILENAME       /u01/app/oracle/oradata/ORCL/datafile/test.dbf BIFILE         bifile.bbd LISTFILE       BLOCKSIZE      8192 MODE           Browse EDIT           Unrecoverable IBASE          Dec OBASE          Dec WIDTH          80 COUNT          512 LOGFILE        log.bbd SPOOL          No 2.使用参数文件连接BBED a）查询出当前的数据文件并保存在文本文件中 SQL>spool psdb_file.txt 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> spool off 注意：保存在文件里的文件号要与我们数据库查询出来的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使用参数文件登陆 [oracle@12cdock ~]$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 [oracle@12cdock ~]$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表空间文件 注意：这里最好先做好备份 ocrl:/data/oradata/ocrl/datafile@oracle1>cp system01.dbf system01.dbf.bak 切换日志模拟生产交易，更新SCN SQL> alter system switch logfile;//多切几次 System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. SQL>  把旧的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 出现问题，数据库直接宕掉 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. 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相同，达到起库的目的。 登陆bbed 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> 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 count 26 COUNT          26 BBED> d offset 484 dba 3,1  File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_d0xj0lhx_.dbf (3)  Block: 1                Offsets:  484 to  509           Dba:0x00c00001 ------------------------------------------------------------------------  6dcf1d00 00000000 41a5d337 0100a792 2a000000 02000000 1000   <32 bytes per line> BBED> d offset 484 dba 4,1  File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_d0xj4qc1_.dbf (4)  Block: 1                Offsets:  484 to  509           Dba:0x01000001 ------------------------------------------------------------------------  6dcf1d00 00000000 41a5d337 0100a792 2a000000 02000000 1000     查看system文件的数据文件头   BBED> d offset 484 dba 1,1  File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_d0xj2b4p_.dbf (1)  Block: 1                Offsets:  484 to  509           Dba:0x00400001 ------------------------------------------------------------------------  e8c81d00 00000000 f5a2d337 01000000 15000000 02000000 1000   <32 bytes per line> 修改system文件头 BBED> modify /x 6dcf dba 1,1 offset 484 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y  File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_d0xj2b4p_.dbf (1)  Block: 1                Offsets:  484 to  509           Dba:0x00400001 ------------------------------------------------------------------------  6dcf1d00 00000000 f5a2d337 01000000 15000000 02000000 1000   <32 bytes per line> BBED> sum dba 1,1 apply Check value for File 1, Block 1: current = 0x2438, required = 0x2438 BBED> d offset dba 1,1 BBED-00207: invalid offset specifier (dba) BBED> d offset 484 dba 1,1  File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_d0xj2b4p_.dbf (1)  Block: 1                Offsets:  484 to  509           Dba:0x00400001 ------------------------------------------------------------------------  6dcf1d00 00000000 f5a2d337 01000000 15000000 02000000 1000   <32 bytes per line> 打开数据库 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.