Use bbed to restore table data and bbed to restore the table

Use bbed to restore table data and bbed to restore the table

ORACLE provides a variety of restoration methods for table-level data recovery: flashback query and logmnr. This example demonstrates how to use the copy command of bbed to restore the table data that you accidentally deleted or damaged. Of course, we can also use this method to restore other data.

Experiment process:

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

The user deletes the table data by mistake and uses bbed to restore the table.

First, check the data blocks to be repaired.

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

Use the copy command of bbed to restore

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

View repair results

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.

What is the bbed tool? I heard that the oracle database has a very powerful bbed tool.

"BBED (Oracle Block Brower and EDitor Tool) is a Tool used to directly view and modify data file data. It is an internal Oracle Tool that can directly modify the content of Oracle data file blocks, it is simply a binary editing tool for Oracle. This tool is not supported by Oracle, so it does not generate executable files by default, and needs to be re-compiled before use ."

Compiling the tool in 10 Gb is relatively simple:

[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-signature-ln10-lnnz10-lnl10-lnro10' cat/s01/10gdb /ldflags '-signature-lncrypt10-signature-ln10-signature-lnl10-lclient10-lnnetd10-lvsn10-lcommon10-lgeneric10-lmm-signature-lnls10-lcore10-signature lnls10- lcore10-lsnls10-lnls10-lxml10-lcore10-lunls10-lsnls10-lnls10-lcore10-lnls10 'cat &#...... remaining full text>

How to Use bbed to view the file id of the data file

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 1280 BBED> set dba 5, 1 DBA 0x01400001 (20971521, 1) BBED> p kcvfhrfnub4 kcvfhrfn @ 368 0x00000005
Zero x 00000005

View the fileheader.