对于表级别的数据恢复，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.
声明：本网页内容旨在传播知识，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
Copyright © 2019-2024 51dongshi.com 版权所有
违法及侵权请联系：TEL:177 7030 7066 E-MAIL:11247931@qq.com 本站由北京市万商天勤律师事务所王兴未律师提供法律服务
