Oracle Study之案例--数据恢复神器Flashback（2）

ゝ一世哀愁。 2022-08-07 10:39 167阅读 0赞

**一、Flashback Table:**

对于DML的误操作，可以通过Undo block对表进行回退（两种模式：基于时间和基于SCN)

**案例分析：**

**1、基于SCN（可以通过logminer找到DML操作的时间点和SCN)**

模拟测试环境：
    SQL> select current_scn from v$database;
    CURRENT_SCN
    -----------
         1264179
    07:16:18 SQL> select * from test;                                                                                                        
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
          7566 JONES      MANAGER         7839 02-APR-81       2975                    20
          7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
          7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
          7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
          7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
          7839 KING       PRESIDENT            17-NOV-81       5000                    10
          7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
          7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
          7900 JAMES      CLERK           7698 03-DEC-81        950                    30
          7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
          7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
    16 rows selected.
    
    07:16:23 SQL> delete from test;                                                                                                          
    16 rows deleted.
    07:16:50 SQL> commit;                                                                                                                   
    Commit complete.
    
    07:16:52 SQL> select * from test;                                                                                                        
    no rows selected
    07:16:57 SQL> insert into test select * from emp where rownum=1;                                                                         
    1 row created.
    07:17:17 SQL> commit;                                                                                                                    
    Commit complete.
    07:17:19 SQL> select * from test;                                                                                                        
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
    
    通过flashback table回退：      
    07:17:21 SQL> flashback table test to scn 1264179;                                                                                       
    flashback table test to scn 1264179
                    *
    ERROR at line 1:
    ORA-08189: cannot flashback the table because row movement is not enabled
    
    回退table必须支持row movement：
    07:17:41 SQL> alter table test enable row  movement;                                                                                     
    Table altered.
    
    07:18:01 SQL> flashback table test to scn 1264179;                                                                                       
    Flashback complete.
    07:18:05 SQL> select * from test;                                                                                                        
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
          7566 JONES      MANAGER         7839 02-APR-81       2975                    20
          7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
          7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
          7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
          7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
          7839 KING       PRESIDENT            17-NOV-81       5000                    10
          7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
          7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
          7900 JAMES      CLERK           7698 03-DEC-81        950                    30
          7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
          7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
    16 rows selected.
    ---回退成功 ！

2**、基于timestamp（可以通过logminer找到DML操作的时间点和SCN)**

05:43:31 SQL> delete from scott.emp1;
    14 rows deleted.
    
    05:44:25 SQL> flashback table scott.emp1 to timestamp to_timestamp('2011-03-18 04:50:00','yyyy-mm-dd hh24:mi:ss');
    Flashback complete.
    
    05:44:32 SQL> select * from scott.emp1;
         EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
          7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
          7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
          7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
          7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
          7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
          7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
          7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
          7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
          7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
          7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
          7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
          7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
          7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10
    14 rows selected.

**1. 基于undo 的表恢复，需要注意DDL 操作的影响**

第三个就是修改并提交过数据之后，对表做过DDL 操作，包括：

drop/modify 列, move 表, drop 分区(如果有的话), truncate table/partition，这些操作会另undo 表空间中的撤销数据失效，对于执行过这些操作的表应用flashback query 会触发ORA-01466 错误。另外一些表结构修改语句虽然并不会影响到undo 表空间中的撤销记录，但有可能因表结构修改导致undo 中重做记录无法应用的情况，比如对于增加了约束，而flashback query 查询出的undo 记录已经不符合新建的约束条件，这个时候直接恢复显然不可能成功，你要么暂时disable 约束，要么通过适当逻辑，对要恢复的数据进行处理之后，再执行恢复。

另外，flashback query 对v$tables,x$tables 等动态性能视图无效，不过对于dba\_\*,all\_\*,user\_\*等数据字典是有效的。同时该特性也完全支持访问远端数据库，比如select \* from tbl@dblink as of scn 360;的形式。

**2. 基于undo 的表恢复，flashback table 实际上做的也是dml 操作(会在被操作的表上加dml 锁)，因此还需要注意triggers 对其的影响**

默认情况下，flashback table to scn/timestamp 在执行时会自动disable 掉与其操作表相差的triggers，如果你希望在此期间trigger 能够继续发挥做用，可以在flashback table 后附加

**ENABLE TRIGGERS** 子句。

**二、Flashback Drop**

**[![wKiom1S2J6LTWazuAAMgwbErcYQ756.jpg][]][wKiom1S2J6LTWazuAAMgwbErcYQ756.jpg]**

在实际开发和维护中，我们有时候会遇到把数据表drop掉的情况。过去这种情况，我们只能通过之前保留的[**备份**][Link 1]，进行不完全的备份。这样的工作量很大也很麻烦。从Oracle10g起，引入了回收站的机制，将drop掉的数据表保存在回收站中。当发现误删除的时候，可以通过回收站回收数据表。

回收站机制类似于我们在[**Windows**][Link 1]上的回收站。在windows中，当我们选择删除一个文件时，本质上并没有将文件从硬盘上删除，只是将文件以一种形式改名，这样就能从回收站中看到。

Oracle的回收站也是采用同样的原理。下面我们做一个简单的[**实验**][Link 1]。

首先，确定系统参数。在Oracle10g中,有一个参数**recyclebin**，控制数据表回收站机制的启动和关闭。

用sys帐号登录，确定recyclebin参数：

SQL> show parameter recyclebin;

NAME                                TYPE       VALUE

\------------------------------------ ----------- ------

recyclebin                          string     on                //当取值为on的时候，表示开启回收站功能；

**案例分析：**

1）06:52:29 SQL> select * from tab;                                                                                                         
    TNAME                          TABTYPE  CLUSTERID
    ------------------------------ ------- ----------
    DEPT                           TABLE
    EMP                            TABLE
    BONUS                          TABLE
    SALGRADE                       TABLE
    TEST                           TABLE
    T01                            TABLE
    T02                            TABLE
    7 rows selected.
    06:52:31 SQL> drop table t01;                                                                                                            
    Table dropped.
    
    查看回收站：
    06:52:38 SQL> show recycle;                                                                                                              
    ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
    ---------------- ------------------------------ ------------ -------------------
    T01              BIN$qrJLbL74ZgvgQKjA8Agb/A==$0 TABLE        2011-08-17:06:52:38
    
    --------除了system 表空间，其余表空间都有一个类似windows 回收站，在drop table，实际上把table 改名后放入recyclebin。
    
    06:52:44 SQL> flashback table t01 to before drop;                                                                                        
    Flashback complete.
    
    06:54:05 SQL> show recycle;                                                                                                              
    06:54:07 SQL>  select * from tab;                                                                                                        
    TNAME                          TABTYPE  CLUSTERID
    ------------------------------ ------- ----------
    DEPT                           TABLE
    EMP                            TABLE
    BONUS                          TABLE
    SALGRADE                       TABLE
    TEST                           TABLE
    T01                            TABLE
    T02                            TABLE
    7 rows selected.
    
    06:54:11 SQL> drop table t02 purge;    //purge  会彻底的删除table                                                                                                    
    Table dropped.
    06:54:40 SQL> show recycle;  
    -----------清空recyclebin
    
    06:54:43 SQL> drop table t01;                                                                                                            
    Table dropped.
    06:55:49 SQL> show recycle;                                                                                                              
    ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
    ---------------- ------------------------------ ------------ -------------------
    T01              BIN$qrJLbL75ZgvgQKjA8Agb/A==$0 TABLE        2011-08-17:06:55:49
    06:55:51 SQL> purge recyclebin;                                                                                                          
    Recyclebin purged.
    
    06:55:57 SQL> show recycle;                                                                                                              
    06:55:59 SQL> 
    --------------如何恢复同一个schema 下同名的table
    
    06:56:32 SQL> drop table test;                                                                                                           
    Table dropped.
    06:56:42 SQL> create table test as  select * from emp;                                                                                   
    Table created.
    06:56:46 SQL> select * from tab;                                                                                                         
    TNAME                          TABTYPE  CLUSTERID
    ------------------------------ ------- ----------
    DEPT                           TABLE
    EMP                            TABLE
    BONUS                          TABLE
    SALGRADE                       TABLE
    BIN$qrJLbL76ZgvgQKjA8Agb/A==$0 TABLE
    TEST                           TABLE
    6 rows selected.
    06:56:50 SQL> show recycle;                                                                                                              
    ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
    ---------------- ------------------------------ ------------ -------------------
    TEST             BIN$qrJLbL76ZgvgQKjA8Agb/A==$0 TABLE        2011-08-17:06:56:36
    06:56:58 SQL> flashback table test to before drop;                                                                                       
    flashback table test to before drop
    *
    ERROR at line 1:
    ORA-38312: original name is used by an existing object
    06:57:09 SQL> flashback table test to before drop rename to test_old;                                                                    
    Flashback complete.
    06:57:32 SQL> select * from tab;                                                                                                         
    TNAME                          TABTYPE  CLUSTERID
    ------------------------------ ------- ----------
    DEPT                           TABLE
    EMP                            TABLE
    BONUS                          TABLE
    SALGRADE                       TABLE
    TEST_OLD                       TABLE
    TEST                           TABLE
    6 rows selected.
    
    flashback Drop不支持sys用户：
    ----system 表空间不存在recyclebin ，表直接被删除
    06:57:36 SQL> conn /as sysdba                                                                                                            
    Connected.
    06:58:33 SQL> 
    06:58:33 SQL> create table test as select * from user_tables;                                                                            
    Table created.
    06:58:42 SQL> drop table test;                                                                                                          
    Table dropped.
    06:58:46 SQL> show recycle; 
    
    闪回表回收站——3个视图
    使用方面，闪回特性还要关注两个回收站视图。user_recyclebin、all_recyclebin、dba_recyclebin。

所谓的闪回drop，就是一种对象假删除[**技术**][Link 2]。当系统参数recyclebin被设置为on的时候，Oracle是开启闪回drop功能的。当对数据表使用drop的时候，Oracle并不是将对象直接删除，而是采用了对象改名。将删除的数据表进行改名（逻辑上），改为BIN$开头的一个编码。这个编码占据了原有数据表的所有资源（包括对象信息和存储信息），但是不能算成为原对象的等价体。也就是说，如果我们直接操作这个修改名，系统会报错，因为Oracle不认为这个对象是一个数据表。数据表T的数据字典信息被删除，而改名的对象信息没有加入其中。

本文出自 “[天涯客的blog][blog]” 博客，请务必保留此出处[http://tiany.blog.51cto.com/513694/1603975][http_tiany.blog.51cto.com_513694_1603975]

[wKiom1S2J6LTWazuAAMgwbErcYQ756.jpg]: /images/20220805/09aa561ccf494882b8d7fc9201a40b15.png
[Link 1]: http://space.itpub.net/?uid-17203031-action-viewspace-itemid-684631
[Link 2]: http://space.itpub.net/?uid-17203031-action-viewspace-itemid-686496
[blog]: http://tiany.blog.51cto.com
[http_tiany.blog.51cto.com_513694_1603975]: http://tiany.blog.51cto.com/513694/1603975