使用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.dbf
SYSAUX /home/app/oraten/oradata/oraten/sysaux01.dbf
UNDOTBS1 /home/app/oraten/oradata/oraten/undotbs01.dbf
SYSTEM /home/app/oraten/oradata/oraten/system01.dbf
TBS1 /home/app/oraten/oradata/oraten/tbs101.dbf
SQL> conn scott/tiger
Connected.
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 exist
SQL> 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 SALGRADE
8 rows selected.
SQL> conn / as sysdba
Connected.
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.dbf
SQL> conn scott/tiger
Connected.
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 ----------------------------------------------------- -------- ------------------------------------ OWNER VARCHAR2(30) SEGMENT_NAME VARCHAR2(81) PARTITION_NAME VARCHAR2(30) SEGMENT_TYPE VARCHAR2(18) TABLESPACE_NAME VARCHAR2(30) HEADER_FILE NUMBER HEADER_BLOCK NUMBER BYTES NUMBER BLOCKS NUMBER EXTENTS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER RELATIVE_FNO NUMBER BUFFER_POOL VARCHAR2(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 ----------------------------------------------------- -------- ------------------------------------ OWNER VARCHAR2(30) SEGMENT_NAME VARCHAR2(81) PARTITION_NAME VARCHAR2(30) SEGMENT_TYPE VARCHAR2(18) TABLESPACE_NAME VARCHAR2(30) HEADER_FILE NUMBER HEADER_BLOCK NUMBER BYTES NUMBER BLOCKS NUMBER EXTENTS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER RELATIVE_FNO NUMBER BUFFER_POOL VARCHAR2(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 sysdba
Connected.
SQL> alter system flush buffer_cache;
System altered.
SQL> conn scott/tiger
Connected.
SQL> select * from tcopy;
OBJECT_ID
----------
OBJECT_NAME
----------------------------------------------------------------------------------------------------
51809
INVALID_ROWS
52080
TCOPY
51574
PK_DEPT
OBJECT_ID
----------
OBJECT_NAME
----------------------------------------------------------------------------------------------------
51573
DEPT
51575
EMP
51576
PK_EMP
OBJECT_ID
----------
OBJECT_NAME
----------------------------------------------------------------------------------------------------
51577
BONUS
51578
SALGRADE
8 rows selected.
“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> 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的方式。