ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于Oracle中通过UNDO进行表数据恢复的测试

关于Oracle中通过UNDO进行表数据恢复的测试

原创 Linux操作系统 作者:ThinkAW 时间:2010-01-01 01:01:16 0 删除 编辑

以往实现--9i

语法:
select * from as of scn/timestamp xxx;
        
举例:
        
        SQL> spool e:\flashback.sp
        SQL> drop table t1 purge;

        表已删除。

        SQL> create table t1
          2  (n number);

        表已创建。

        SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

        会话已更改。

        SQL> select sysdate from dual;

        SYSDATE
        -------------------
        2009-12-23 22:00:09

        SQL> insert into t1
          2  values(1);

        已创建 1 行。

        SQL> insert into t1
          2  values(2);

        已创建 1 行。

        SQL> commit;

        提交完成。

        SQL> select sysdate from dual;

        SYSDATE
        -------------------
        2009-12-23 22:00:55

        SQL> insert into t1
          2  values(3);

        已创建 1 行。

        SQL> commit;

        提交完成。

        SQL> select sysdate from dual;

        SYSDATE
        -------------------
        2009-12-23 22:01:49
        
        --可以通过Create table t1_xxx as select *……来创建表存储t1的在某一时间点上的数据存储情况
        SQL> select * from t1 as of timestamp to_timestamp('2009-12-23 22:00:55','yyyy-mm-dd hh24:mi:ss');

                 N
        ----------
                 1
                 2

        SQL> select * from t1 as of timestamp to_timestamp('2009-12-23 22:01:49','yyyy-mm-dd hh24:mi:ss');

                 N
        ----------
                 1
                 2
                 3
       
目前实现--10g之后
语法:
        flashback table to scn/timestamp
举例:
        --在这里,启用表的Row Movement是必须的
        SQL> alter table t1 enable row movement;

        表已更改。

        SQL> flashback table t1 to timestamp to_timestamp('2009-12-23 22:00:55','yyyy-mm-dd hh24:mi:ss');

        闪回完成。

        SQL> select * from t1;

                 N
        ----------
                 1
                 2
        
        --flashback table的方式相比上面9i中的实现,仅仅是在操作上便捷了一些,可以这样理解
        --delete from t1;
        --insert into t1 select * from t1 as of timestamp to_timestamp('2009-12-23 22:00:55','yyyy-mm-dd hh24:mi:ss');

语法:
        flashback table to before drop [rename to ]
        
举例:
        --对于drop掉的表,如果不指定purge选项,则在空间未回收的情况下,还可以通过Flashback table的方式进行恢复
        
        SQL> drop table t1;

        表已删除。

        --drop掉的table被放在一个叫做回收站的地方,可以通过如下语句查看
        --在下面的显示中,有多个t1,flashback恢复的是最近被drop的一个,即2009-12-23:22:19:44
        --所以,如果要恢复回收站中的多个t1,则可以使用flashback中的rename选项方便的完成
        SQL> show recycle;
        ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
        ---------------- ------------------------------ ------------ -------------------
        T1               BIN$e2cDmQ5FVSngQAB/AQB2zg==$0 TABLE        2009-12-23:22:19:44
        T1               BIN$dMY5paupAkrgQAB/AQB1CQ==$0 TABLE        2009-09-30:14:05:22
        T1               BIN$dEkTU4SWwIjgQAB/AQBScQ==$0 TABLE        2009-09-24:08:40:17
        T2               BIN$dMY5pauqAkrgQAB/AQB1CQ==$0 TABLE        2009-09-30:14:05:23
        T2               BIN$dMY5paunAkrgQAB/AQB1CQ==$0 TABLE        2009-09-30:13:58:57
        T3               BIN$dMY5paurAkrgQAB/AQB1CQ==$0 TABLE        2009-09-30:14:05:24
        T3               BIN$dMY5pauoAkrgQAB/AQB1CQ==$0 TABLE        2009-09-30:13:59:03
        T_OBJECTS        BIN$dSZFN3ikQI/gQAB/AQBzUQ==$0 TABLE        2009-10-05:08:34:02
        XFXTEST          BIN$dMYPcthI/WvgQAB/AQBzAA==$0 TABLE        2009-09-30:13:47:03

        SQL> flashback table t1 to before drop;

        闪回完成。

        SQL> select *from t1;

                 N
        ----------
                 1
                 2

        --对于drop时采用purge选项的情况,则不能恢复
        SQL> drop table t4 purge;

        表已删除。

        SQL> flashback table t4 to before drop;
        flashback table t4 to before drop
        *
        第 1 行出现错误:
        ORA-38305: 对象不在回收站中
        
更为细粒度的恢复--10g
        以上的两种对于表数据的恢复中,都很难处理一种情况,比如在A时刻写入数据,B时刻删除数据,然后在C时刻又写入数据,
        那么如果我想做到在保留C时刻的数据的前提下,还能恢复A时刻的数据就比较困难,尤其是可能B和C往复出现。针对这种
        情况,10g中支持对于单个事务的回复处理。
举例:
        
        SQL> create table t5
          2  ( n number);

        表已创建。

        SQL> insert into t5
          2  values(1);

        已创建 1 行。

        SQL> insert into t5
          2  values(2);

        已创建 1 行。

        SQL> insert into t5
          2  values(3);

        已创建 1 行。

        SQL> commit;

        提交完成。

        SQL> delete from t5;

        已删除3行。

        SQL> commit;

        提交完成。

        SQL> insert into t5
          2  values(4);

        已创建 1 行。

        SQL> commit;

        提交完成。

        SQL> col starttime FOR a20
        SQL> col endtime FOR a20
        SQL> col versions_xid FOR a20
        SQL> col VERSIONS_STARTSCN FOR 9999999999999999
        SQL> col VERSIONS_EnDSCn FOR 9999999999999999
        SQL> col VERSIONS_OPERATION FOR a20
        SQL> SELECT TO_CHAR(VERSIONS_STARTTIME, 'yyyy-mm-dd hh24:mi:ss') STARTTIME,
          2         TO_CHAR(VERSIONS_ENDTIME, 'yyyy-mm-dd hh24:mi:ss') ENDTIME,
          3         VERSIONS_XID,
          4         VERSIONS_STARTSCN,
          5         VERSIONS_ENDSCN,
          6         VERSIONS_OPERATION,
          7         n
          8    FROM T5 VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
          9   ORDER BY VERSIONS_STARTTIME;
        
        STARTTIME            ENDTIME              VERSIONS_XID         VERSIONS_STARTSCN   VERSIONS_ENDSCN VERSIONS_OPERATION            N
        -------------------- -------------------- -------------------- ----------------- ----------------- -------------------- ----------
        2009-12-24 08:03:03  2009-12-24 08:03:09  02001200002C0500         2498109837086     2498109837091 I                     3
        2009-12-24 08:03:03  2009-12-24 08:03:09  02001200002C0500         2498109837086     2498109837091 I                     1
        2009-12-24 08:03:03  2009-12-24 08:03:09  02001200002C0500         2498109837086     2498109837091 I                     2
        2009-12-24 08:03:09                       07001000EC7E0800         2498109837091                   D                     2
        2009-12-24 08:03:09                       07001000EC7E0800         2498109837091                   D                     3
        2009-12-24 08:03:09                       07001000EC7E0800         2498109837091                   D                     1
        2009-12-24 08:03:33                       02000E00A52B0500         2498109837100                   I                     4

        已选择7行。
        
        --注:这里比较奇怪的是,明明XID对应为07001000EC7E0800的事务是三个D(DELETE),可是在UNDO_SQL中,只能查询到一个
        SQL> col undo_sql for a100
        SQL> SELECT undo_sql FROM Flashback_Transaction_Query
          2  WHERE XID = hextoraw('07001000EC7E0800');

        UNDO_SQL
        ----------------------------------------------------------------------------------------------------
        insert into "THINKAW"."T5"("N") values ('1');

        SQL> select * from t5;

                 N
        ----------
                 4

        SQL> insert into t5
          2  values(1);

        已创建 1 行。

        SQL> commit;

        提交完成。

        SQL> delete from t5;

        已删除2行。

        SQL> commit;

        提交完成。
        
        SQL> SELECT TO_CHAR(VERSIONS_STARTTIME, 'yyyy-mm-dd hh24:mi:ss') STARTTIME,
          2         TO_CHAR(VERSIONS_ENDTIME, 'yyyy-mm-dd hh24:mi:ss') ENDTIME,
          3         VERSIONS_XID,
          4         VERSIONS_STARTSCN,
          5         VERSIONS_ENDSCN,
          6         VERSIONS_OPERATION,
          7         n
          8    FROM T5 VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
          9   ORDER BY VERSIONS_STARTTIME;
        
        STARTTIME            ENDTIME              VERSIONS_XID         VERSIONS_STARTSCN   VERSIONS_ENDSCN VERSIONS_OPERATION            N
        -------------------- -------------------- -------------------- ----------------- ----------------- -------------------- ----------
        2009-12-24 08:03:03  2009-12-24 08:03:09  02001200002C0500         2498109837086     2498109837091 I                     2
        2009-12-24 08:03:03  2009-12-24 08:03:09  02001200002C0500         2498109837086     2498109837091 I                     3
        2009-12-24 08:03:03  2009-12-24 08:03:09  02001200002C0500         2498109837086     2498109837091 I                     1
        2009-12-24 08:03:09                       07001000EC7E0800         2498109837091                   D                     3
        2009-12-24 08:03:09                       07001000EC7E0800         2498109837091                   D                     1
        2009-12-24 08:03:09                       07001000EC7E0800         2498109837091                   D                     2
        2009-12-24 08:03:33  2009-12-24 08:08:06  02000E00A52B0500         2498109837100     2498109837288 I                     4
        2009-12-24 08:07:57  2009-12-24 08:08:06  010021002E420600         2498109837283     2498109837288 I                     1
        2009-12-24 08:08:06                       06001500076F0500         2498109837288                   D                     1
        2009-12-24 08:08:06                       06001500076F0500         2498109837288                   D                     4

        已选择10行。

        --注:而在这里对应XID为06001500076F0500的事务,则可以查询到完整的UNDO_SQL,同上面第一次查询不符
        SQL> SELECT undo_sql FROM Flashback_Transaction_Query
          2  WHERE XID = hextoraw('06001500076F0500');

        UNDO_SQL
        ----------------------------------------------------------------------------------------------------
        insert into "THINKAW"."T5"("N") values ('1');
        insert into "THINKAW"."T5"("N") values ('4');


        SQL> SELECT undo_sql FROM Flashback_Transaction_Query
          2  WHERE XID = hextoraw('07001000EC7E0800');

        UNDO_SQL
        ----------------------------------------------------------------------------------------------------
        insert into "THINKAW"."T5"("N") values ('1');


        SQL> insert into t5
          2  values(2);

        已创建 1 行。

        SQL> insert into t5
          2  values(3);

        已创建 1 行。

        SQL> commit;

        提交完成。

        SQL> select *From t5;

                 N
        ----------
                 2
                 3

        SQL> insert into "THINKAW"."T5"("N") values ('1');

        已创建 1 行。

        SQL> insert into "THINKAW"."T5"("N") values ('4');

        已创建 1 行。

        SQL> commit;

        提交完成。

        SQL> select *from t5;

                 N
        ----------
                 2
                 3
                 1
                 4

        SQL> delete from t5;

        已删除4行。

        SQL> commit;

        提交完成。

        SQL> SELECT TO_CHAR(VERSIONS_STARTTIME, 'yyyy-mm-dd hh24:mi:ss') STARTTIME,
          2         TO_CHAR(VERSIONS_ENDTIME, 'yyyy-mm-dd hh24:mi:ss') ENDTIME,
          3         VERSIONS_XID,
          4         VERSIONS_STARTSCN,
          5         VERSIONS_ENDSCN,
          6         VERSIONS_OPERATION,
          7         n
          8    FROM T5 VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
          9   ORDER BY VERSIONS_STARTTIME;

        STARTTIME            ENDTIME              VERSIONS_XID         VERSIONS_STARTSCN   VERSIONS_ENDSCN VERSIONS_OPERATION            N
        -------------------- -------------------- -------------------- ----------------- ----------------- -------------------- ----------
        2009-12-24 08:03:03  2009-12-24 08:03:09  02001200002C0500         2498109837086     2498109837091 I                     2
        2009-12-24 08:03:03  2009-12-24 08:03:09  02001200002C0500         2498109837086     2498109837091 I                     3
        2009-12-24 08:03:03  2009-12-24 08:03:09  02001200002C0500         2498109837086     2498109837091 I                     1
        2009-12-24 08:03:09                       07001000EC7E0800         2498109837091                   D                     1
        2009-12-24 08:03:09                       07001000EC7E0800         2498109837091                   D                     2
        2009-12-24 08:03:09                       07001000EC7E0800         2498109837091                   D                     3
        2009-12-24 08:03:33  2009-12-24 08:08:06  02000E00A52B0500         2498109837100     2498109837288 I                     4
        2009-12-24 08:07:57  2009-12-24 08:08:06  010021002E420600         2498109837283     2498109837288 I                     1
        2009-12-24 08:08:06                       06001500076F0500         2498109837288                   D                     1
        2009-12-24 08:08:06                       06001500076F0500         2498109837288                   D                     4
        2009-12-24 08:09:51  2009-12-24 08:10:51  0A002B0070DA0700         2498109837380     2498109837428 I                     3

        STARTTIME            ENDTIME              VERSIONS_XID         VERSIONS_STARTSCN   VERSIONS_ENDSCN VERSIONS_OPERATION            N
        -------------------- -------------------- -------------------- ----------------- ----------------- -------------------- ----------
        2009-12-24 08:09:51  2009-12-24 08:10:51  0A002B0070DA0700         2498109837380     2498109837428 I                     2
        2009-12-24 08:10:12  2009-12-24 08:10:51  08000100D8640700         2498109837387     2498109837428 I                     1
        2009-12-24 08:10:12  2009-12-24 08:10:51  08000100D8640700         2498109837387     2498109837428 I                     4
        2009-12-24 08:10:51                       050026004CA00600         2498109837428                   D                     2
        2009-12-24 08:10:51                       050026004CA00600         2498109837428                   D                     3
        2009-12-24 08:10:51                       050026004CA00600         2498109837428                   D                     1
        2009-12-24 08:10:51                       050026004CA00600         2498109837428                   D                     4

        已选择18行。

        SQL> SELECT undo_sql FROM Flashback_Transaction_Query
          2  WHERE XID = hextoraw('050026004CA00600');

        UNDO_SQL
        ----------------------------------------------------------------------------------------------------
        insert into "THINKAW"."T5"("N") values ('4');
        insert into "THINKAW"."T5"("N") values ('1');
        insert into "THINKAW"."T5"("N") values ('3');
        insert into "THINKAW"."T5"("N") values ('2');
        
            
        --可以满足单事务语句提取,然后执行,即某一事务的回退操作。
        --在实际使用中,可以通过
        select xxx
          from versions between timestamp xxx and xxx;
        来查询某表在某一段时间内的事务操作历史,以便找到进行回退。

        --注:使用Version查询,可以知道在表上执行DML操作的具体时间和SCN,通过这些信息也能很好的进行
        --Flashback或者as of形式的回退操作,否则就只能靠业务操作人员的记忆和不断的尝试恢复时间点了。
        
        --结论:
        从上面的实验可以看出,对于表T5而言,第一次Delete多行,只能从UNDO_SQL中查询到一行数据,但是对于后面操作的
        DELETE则可以完整的查询,不知道这是不是Oracle的bug,大家可以测一下。

结尾
    请大家在使用中理解一下查询出的Versions信息中的字段含义,比如Versions_starttime(此数据行从什么时间开始生效)、
    Versions_startscn(此数据行从那个SCN号开始生效)。
    
    另外,鉴于回滚段中数据保存的时间有限,如果发现有误删数据的情况,请尽快恢复,对于大表和极为重要的数据建议使用
    Create table as select * from xxx as of xxx 的方式进行恢复,从我目前的理解,此语句应该是不写回滚段的(免得进行
    不必要的回滚段覆盖),至于Flashback table xxx to是否使用回滚段,尚不清楚。

    本文中涉及到的内容一些来自于书本、实验和自己的理解,如果有误或者疑问,还望大家指出。
    

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14790134/viewspace-624066/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2008-07-08

  • 博文量
    7
  • 访问量
    20463