对于表级别的数据恢复，ORACLE提供了多种恢复方法:flashback query,logmnr等。本文通过示例演示使用bbed的copy命令恢复用户误删除或者损坏的表数据，当然我们也可以使用该方法来恢复其他数据。 实验过程： SQL select tablespace_name,file_name from dba_da

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