• 使用bbed恢复表数据


    对于表级别的数据恢复,ORACLE提供了多种恢复方法:flashback query,logmnr等。

    本文通过演示样例演示使用bbedcopy命令恢复用户误删除或者损坏的表数据,当然我们也能够使用该方法来恢复其它数据。

    实验过程:

    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

    使用bbedcopy命令来恢复

    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.





  • 相关阅读:
    滑雪(dp好题)
    田忌赛马
    反质数(Antiprimes)
    LCA-倍增法(在线)
    二模 (2) day2
    Spring Integration
    [转载] Spring MVC
    收藏夹
    Linux profile File
    git merge 与 rebase 的区别
  • 原文地址:https://www.cnblogs.com/blfbuaa/p/6925927.html
Copyright © 2020-2023  润新知