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