Oracle 之利用BBED修改資料塊SCN----沒有備份資料檔案的資料恢復

strivechao發表於2019-07-04

測試環境
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.

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25469263/viewspace-2649608/,如需轉載,請註明出處,否則將追究法律責任。

相關文章