使用bbed恢複表資料，bbed恢複表

使用bbed恢複表資料，bbed恢複表

對於表層級的資料恢複，ORACLE提供了多種恢複方法:flashback query,logmnr等。本文通過樣本示範使用bbed的copy命令恢複使用者誤刪除或者損壞的表資料，當然我們也可以使用該方法來恢複其他資料。

實驗過程：

SQL> select tablespace_name,file_name from dba_data_files;TABLESPACE_NAME FILE_NAME--------------- --------------------------------------------------USERS/home/app/oraten/oradata/oraten/users01.dbfSYSAUX/home/app/oraten/oradata/oraten/sysaux01.dbfUNDOTBS1/home/app/oraten/oradata/oraten/undotbs01.dbfSYSTEM/home/app/oraten/oradata/oraten/system01.dbfTBS1/home/app/oraten/oradata/oraten/tbs101.dbfSQL> conn scott/tigerConnected.SQL> create table tcopy tablespace tbs1 as select object_id,object_name from user_objects;Table created.SQL> select * from tcop;  select * from tcop              *ERROR at line 1:ORA-00942: table or view does not existSQL> select * from tcopy; OBJECT_ID OBJECT_NAME---------- --------------------------------------------------------------------------------------------------------------------------------     51809 INVALID_ROWS     52080 TCOPY     51574 PK_DEPT     51573 DEPT     51575 EMP     51576 PK_EMP     51577 BONUS     51578 SALGRADE8 rows selected.SQL> conn / as sysdbaConnected.SQL> alter system checkpoint;System altered.SQL> alter system flush buffer_cache;System altered.SQL> host cp /home/app/oraten/oradata/oraten/tbs101.dbf /home/app/oraten/oradata/oraten/tbs101.copy.dbfSQL> conn scott/tigerConnected.SQL> delete from tcopy;8 rows deleted.SQL> commit;Commit complete.SQL> select * from tcopy;no rows selected

使用者誤將表資料刪除，下面通過bbed來進行恢複.

首先看看需要修複的資料區塊

SQL> desc dba_segments Name       Null?Type ----------------------------------------------------- -------- ------------------------------------ OWNERVARCHAR2(30) SEGMENT_NAMEVARCHAR2(81) PARTITION_NAME VARCHAR2(30) SEGMENT_TYPEVARCHAR2(18) TABLESPACE_NAMEVARCHAR2(30) HEADER_FILENUMBER HEADER_BLOCKNUMBER BYTESNUMBER BLOCKS NUMBER EXTENTSNUMBER INITIAL_EXTENT NUMBER NEXT_EXTENTNUMBER MIN_EXTENTSNUMBER MAX_EXTENTSNUMBER PCT_INCREASENUMBER FREELISTSNUMBER FREELIST_GROUPSNUMBER RELATIVE_FNONUMBER BUFFER_POOLVARCHAR2(7)SQL> select segment_name,header_file,header_block,blocks from dba_segments where segment_name='TCOPY';SEGMENT_NAME  HEADER_FILE--------------------------------------------------------------------------------- -----------HEADER_BLOCK BLOCKS------------ ----------TCOPY    5 531      8

使用bbed的copy命令來恢複

SQL> desc dba_segments Name       Null?Type ----------------------------------------------------- -------- ------------------------------------ OWNERVARCHAR2(30) SEGMENT_NAMEVARCHAR2(81) PARTITION_NAME VARCHAR2(30) SEGMENT_TYPEVARCHAR2(18) TABLESPACE_NAMEVARCHAR2(30) HEADER_FILENUMBER HEADER_BLOCKNUMBER BYTESNUMBER BLOCKS NUMBER EXTENTSNUMBER INITIAL_EXTENT NUMBER NEXT_EXTENTNUMBER MIN_EXTENTSNUMBER MAX_EXTENTSNUMBER PCT_INCREASENUMBER FREELISTSNUMBER FREELIST_GROUPSNUMBER RELATIVE_FNONUMBER BUFFER_POOLVARCHAR2(7)SQL> select segment_name,header_file,header_block,blocks from dba_segments where segment_name='TCOPY';SEGMENT_NAME  HEADER_FILE--------------------------------------------------------------------------------- -----------HEADER_BLOCK BLOCKS------------ ----------TCOPY    5 531      8

查看修複結果

SQL> conn / as sysdbaConnected.SQL> alter system flush buffer_cache;System altered.SQL> conn scott/tigerConnected.SQL> select * from tcopy; OBJECT_ID----------OBJECT_NAME----------------------------------------------------------------------------------------------------     51809INVALID_ROWS     52080TCOPY     51574PK_DEPT OBJECT_ID----------OBJECT_NAME----------------------------------------------------------------------------------------------------     51573DEPT     51575EMP     51576PK_EMP OBJECT_ID----------OBJECT_NAME----------------------------------------------------------------------------------------------------     51577BONUS     51578SALGRADE8 rows selected.

bbed工具是幹什的？聽說oracle資料庫有個bbed工具很厲害的，介紹

“BBED(Oracle Block Brower and EDitor Tool)，用來直接查看和修改資料檔案資料的一個工具，是Oracle一款內部工具，可以直接修改Oracle資料檔案塊的內容，簡單來說就是一個針對 Oracle的二進位編輯工具。該工具不受Oracle支援，所以預設是沒有產生可執行檔的，在使用前需要重新編譯。”

在10g中編譯該工具顯得較簡單:

[maclean@rh2 ~]$ cd $ORACLE_HOME/rdbms/lib

[maclean@rh2 lib]$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
make: `/s01/10gdb/rdbms/lib/bbed' is up to date.

[maclean@rh2 lib]$ rm bbed

[maclean@rh2 lib]$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed

Linking BBED utility (bbed)
rm -f /s01/10gdb/rdbms/lib/bbed
gcc -o /s01/10gdb/rdbms/lib/bbed -L/s01/10gdb/rdbms/lib/ -L/s01/10gdb/lib/ -L/s01/10gdb/lib/stubs/ /s01/10gdb/lib/s0main.o /s01/10gdb/rdbms/lib/ssbbded.o /s01/10gdb/rdbms/lib/sbbdpt.o `cat /s01/10gdb/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 /s01/10gdb/rdbms/lib/defopt.o -ldbtools10 -lclntsh `cat /s01/10gdb/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lnro10 `cat /s01/10gdb/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lclient10 -lnnetd10 -lvsn10 -lcommon10 -lgeneric10 -lmm -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 `cat &#......餘下全文>>
 
怎利用bbed查看資料檔案的file id

BBED> info all File# Name Size(blks) ----- ---- ---------- 1 /u01/app/oracle/oradata/repo/system01.dbf 93440 2 /u01/app/oracle/oradata/repo/sysaux01.dbf 98560 3 /u01/app/oracle/oradata/repo/undotbs01.dbf 12160 4 /u01/app/oracle/oradata/repo/users01.dbf 2080 5 /u01/app/oracle/oradata/repo/example01.dbf 44240 6 /u01/app/oracle/oradata/repo/bbedtb01.dbf 1280 7 /u01/app/oracle/oradata/repo/bbedtb02.dbf 1280BBED> set dba 5,1 DBA 0x01400001 (20971521 5,1)BBED> p kcvfhrfnub4 kcvfhrfn @368 0x00000005
0x00000005

使用查看fileheader的方式。