使用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的方式。