对于表级别的数据恢复，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 ----------------------------------------------------- -------- ------------------------------------ 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 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.