关于Oracle中通过UNDO进行表数据恢复的测试 - XXXXXX的角落 - ITeye博客

`

thinkaw

浏览: 1615 次
性别:
来自: 秦皇岛

最近访客更多访客>>

博主相关

博客

微博

相册

收藏

留言

关于我

文章分类

社区版块

存档分类

2010-01 ( 1)
更多存档...

最新评论

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

博客分类：

ORACLE备份恢复

阅读更多

以往实现--9i

语法:

select * from <table_name> 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 <table_name> 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 <table_name> to before drop [rename to <table_name>]

举例:

--对于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 <table_name> 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是否使用回滚段，尚不清楚。

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

1
顶

0
踩

分享到：

2010-01-01 01:13
浏览 1609
评论(0)
分类:数据库
查看更多

评论

发表评论

您还没有登录,请您登录后再发表评论

相关推荐

oracle 9i UNDO表空间数据文件丢失恢复: ### Oracle 9i UNDO 表空间数据文件丢失恢复知识点详解 #### 一、概述在Oracle数据库管理中，UNDO表空间是非常重要的组成部分之一，主要用于存储事务处理期间的回滚信息。当UNDO表空间的数据文件丢失时，可能会...

oracle数据库热备恢复手册: 本文档提供了 Oracle 热备份恢复的手册，包括Undo 表空间恢复、非系统数据文件恢复、Oracle 热备份恢复过程、系统崩溃恢复、Undo 表空间和 Rollback 段、数据库初始化参数、数据库恢复命令等知识点。

Oracle11g备份恢复笔记文档: - **闪回技术概述**：一种快速恢复技术，利用undo数据或闪回日志来进行数据恢复，适用于人为逻辑错误的恢复。 - **11g支持的闪回方式**：Oracle 11g 支持多种闪回功能，包括但不限于闪回查询、闪回表、闪回事务查询...

Oracle Flashback在医院数据恢复中的应用.pdf: Oracle Flashback技术是Oracle数据库提供的一种高级数据恢复机制，它允许用户恢复到数据库的某个历史状态，无需回滚日志或还原备份。在医院数据恢复中，这一特性尤为重要，因为医院业务的连续性和数据完整性至关重要...

oracle恢复: 3. 执行恢复操作：根据策略，使用RMAN、手工复制文件、数据块修复等方式进行数据恢复。 4. 验证数据完整性：恢复完成后，需要验证数据的完整性和一致性。 5. 恢复数据库状态：如果需要，可以通过数据库恢复模式或...

恢复drop的表和记录: Oracle数据库的闪回技术是一种非常强大的数据恢复工具，它允许数据库管理员或具有相应权限的用户恢复到某一时间点的数据状态。这一功能基于Oracle数据库的Undo和Redo日志机制，以及系统全局区（SGA）中的闪回区域。...

ORACLE数据库及SQL语言考试题一含答案.pdf: 2、在使用视图进行查询时，是通过View 中的 SQL 查询原表的数据，不能提高查询性能。在通过物化视图进行查询时，直接读取物化视图的数据，提高查询性能。 7. Oracle 数据表连接的方式有哪几种：自连接、内连接、外...

Oracle DBA关键技能备份与恢复最佳实践: 计划中应包含对备份介质的管理和存储、备份频率、备份类型、以及恢复测试的周期性检查。使用测试环境进行故障恢复练习是检验备份有效性的关键步骤。三、备份恢复分类 1. 逻辑备份与恢复逻辑备份是针对数据库对象...

用Oracle闪回功能恢复偶然丢失的数据: Oracle的闪回功能是数据库恢复策略的一个重要组成部分，它允许用户恢复偶然丢失的数据，从而避免因误操作导致的重大损失。这一特性在Oracle 9i版本中首次引入，并随着时间的推移，不断发展和完善。 1. 闪回查询功能...

[Oracle] 解析在没有备份的情况下undo损坏怎么办: 在使用Oracle数据库过程中，若遇到Undo表空间损坏且没有备份的情况，可通过以下步骤尝试解决此问题： 1. **创建临时的pfile文件**：首先，需要创建一个临时的初始化参数文件（pfile），因为此时无法直接从服务器...

Oracle的UNDOTBS01.DBF文件太大的解决办法: 升级数据库版本前，请确保进行充分的测试，并备份现有数据库环境，以防止升级过程中可能出现的问题。 #### 解决方案二：禁用自动增长功能另一个常见的解决方案是禁用UNDOTBS01.DBF文件的自动增长功能。这可以通过...

Oracle 备份恢复闪回技术实战: Oracle闪回技术是一种先进的数据恢复机制，能够帮助数据库管理员迅速恢复因逻辑错误而导致的数据损失。它不同于传统的备份与恢复方法，无需依赖备份文件，而是利用数据库内部的特殊机制来实现数据的即时恢复。这种...

如何恢复只有完好数据文件的Oracle数据: 如果仅有数据文件是完好的，而控制文件和其他重要配置文件丢失或者损坏了，那么如何进行数据恢复呢？本文将详细介绍这种情况下恢复Oracle数据的具体步骤。 #### 二、准备工作在开始恢复过程之前，我们需要准备一些...

Oracle 11g 备份与恢复最佳手册: - **面向逻辑错误**: 利用undo数据或闪回日志进行快速恢复。 - **特点**: 不需要利用备份，支持多种闪回方式，适用于不同的逻辑错误场景。 #### 四、完全恢复与不完全恢复 - **完全恢复**: 使用完整备份或部分备份...

用Oracle闪回功能恢复偶然丢失的数据.rar: 同时，对于重要的数据操作，始终建议先进行测试，以熟悉恢复流程并避免潜在的风险。通过阅读《用Oracle闪回功能恢复偶然丢失的数据.doc》文档，您可以获得更详细的操作步骤和实例，帮助您更好地理解和应用Oracle闪...

如何在ORACLE9I下利用闪回功能恢复误删数据.pdf: Oracle 9i 是一款强大的关系型数据库管理系统，其在数据安全和恢复方面提供了诸多功能，其中闪回功能（Flashback）是一项重要的数据恢复工具。闪回功能允许用户恢复误删除或误修改的数据，大大增强了数据库的安全性...

到底能拿多少薪水 ORACLE工程师技能评估表: 为了更好地评估一名Oracle工程师的专业水平及其潜在的薪酬范围，本文将根据“到底能拿多少薪水 Oracle工程师技能评估表”中的内容进行深入解析，帮助读者理解不同技能层次的Oracle工程师应具备的知识点。 #### 二、...

Global site tag (gtag.js) - Google Analytics