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