使用bbed恢复表数据

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