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