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

最新推荐文章于 2023-05-29 16:37:37 发布

cksmp68828

最新推荐文章于 2023-05-29 16:37:37 发布

阅读量153

点赞数

文章标签：数据库

以往实现--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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14790134/viewspace-624066/

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
关于Oracle中通过UNDO进行表数据恢复的测试

以往实现--9i语法: select * from as of scn/timestamp xxx; 举例: SQL> spool e:\flashback.sp...
复制链接

扫一扫

评论

被折叠的条评论为什么被折叠?

到【灌水乐园】发言

查看更多评论

添加红包

成就一亿技术人!

hope_wisdom

发出的红包

实付元

使用余额支付

点击重新获取

扫码支付

钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。