9i新特性之Flashback Query的应用---针对DML误操作的恢复

最新推荐文章于 2018-02-18 14:33:01 发布

conggan2284

最新推荐文章于 2018-02-18 14:33:01 发布

阅读量116

点赞数

在9i之前，如果出现DML的误操作，只能通过备份来完成基于时间点的恢复，9i给提供了一个新的特性Flashback Query，我们可以应用此特性，可以很方便的实现恢复。但是要注意的是，Flashback Query 仅仅是一个查询的机制，不会真正的UNDO任何数据。

[@more@]

1. 什么是Flashback Query？

利用Oracle 多版本读一致的特性通过undo 来提供所需的前镜像中的数据。通过这个功能，可以看到历史数据，甚至用历史数据来修复误操作引起的错误。可以通过指定时间或者SCN 来检索需要的数据。｛Uses Oracle's multiversion read-consistency capabilities to restore data by applying undo as needed. You can view and repair historical data, and you can perform queries on the database as of a certain wall clock time or user-specified system commit number (SCN).｝

可以理解成，Oracle在之前的版本中就已经存在了Flashback Query了，之前我们把它称为多版本的读一致性。（多版本特性提供跨多个用户会话的一致性视图，Oracle不执行dirty read）

2. 准备工作

the best way：数据库处于Automatic Undo Management 状态（也有文章说必须，我查找了oracle文档应用了这个词Prerequisite，同时在AskTom上我也查找到相应的解释，指明最好是自动Undo管理，手动的Undo管理也是可以的，并且有例子）

最大可以闪回查询的时间段由UNDO_RETENTION 初始化参数（单位为秒）指定，参看下面执行命令

SQL> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 600

undo_suppress_errors boolean FALSE

undo_tablespace string UNDOTBS1

SQL>

这是一个可以动态的修改的参数，可以通过ALTER SYSTEM SET UNDO_RETENTION =;来修改参数值

比较显然的是，你设置了相对大的UNDO_RETENTION，就必须设置足够大的UNDO ROLLBACK SEGMENTS。

3. 如何使用Flashback Query？

我们可以通过两种方式来使用Flashback Query：

用SQL

使用SELECT 语句的AS OF 来进行闪回查询，语法如下：

as of scn ( timestamp) expr

通过关键词 AS OF 可以对表，视图，物化视图进行Flashback Query，可以制定SCN或者TIMESTAMP，其中TIMESTAMP是9i中出现的，可以有毫秒的时间单位，如

SQL> select systimestamp from dual;

SYSTIMESTAMP

---------------------------------------------------------------------------

29-12月-03 10.15.05.171000 下午 +08:00

下面，进行一个例子：

SQL> connect scott/tiger

已连接。

SQL> create table test (id number(1));

表已创建。

SQL> insert into test values (1);

已创建 1 行。

SQL> insert into test values (2);

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from test;

----------

SQL> delete from test where id=1;

已删除 1 行。

SQL> commit;

提交完成。

SQL> select * from test;

----------

SQL> select * from test as of timestamp (systimestamp -interval'10'second);

----------

SQL> insert into test (select * from test as of timestamp (systimestamp -interva

l'10'second) where id =1);

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from test;

----------

现在，利用了Flashback Query 我们恢复了test表中被误删除的记录。当然我们可以通过一些其他的方法，类似于建立一个中间表等，进行这种少量数据的DML误操作的恢复.

用DBMS_FLASHBACK包

DBMS_FLASHBACK 包提供了以下几个函数：

ENABLE_AT_TIME：设置当前SESSION 的闪回查询时间

ENABLE_AT_SYSTEM_CHANGE_NUMBER：设置当前SESSION的闪回查询SCN

GET_SYSTEM_CHANGE_NUMBER：取得当前数据库的SCN

DISABLE：关闭当前SESSION 的闪回查询

如：

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

8053651

当将一个SESSION 设置为闪回查询模式之后，后续的查询都会基于那个时间点或者SCN 的数据库状态，如果SESSION 结束，那么即使没有明确指定DISABLE，闪回查询也会自动失效。在SESSION 运行在闪回查询状态时，是不允许进行任何DML 和DDL 操作。如果要用DML操作来进行数据恢复就必须使用PL/SQL 游标（其实，这里也就是给我们提供了一个数据恢复的方法）。即使SESSION 运行在闪回查询模式，SYSDATE 函数也不会受到影响，仍然会返回当前正确的系统时间。

下面我们用一个例子说明如何使用DBMS_FLASHBACK 包来恢复数据。

假设由于误操作删除了SCOTT.EMP 表中的所有数据，现在我们要恢复。

SQL> delete from emp;

14 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from emp;

COUNT(*)

----------

然后执行下面的SQL 创建一个存储过程用于恢复数据

CREATE OR REPLACE PROCEDURE prc_recoveremp IS

CURSOR c_emp IS

SELECT * FROM scott.emp;

v_row c_emp%ROWTYPE;

BEGIN

DBMS_FLASHBACK.ENABLE_AT_TIME(SYSTIMESTAMP - INTERVAL '1' DAY);

OPEN c_emp;

DBMS_FLASHBACK.DISABLE;

LOOP

FETCH c_emp

INTO v_row;

EXIT WHEN c_emp%NOTFOUND;

INSERT INTO scott.emp

VALUES

(v_row.EMPNO,

v_row.ENAME,

v_row.JOB,

v_row.MGR,

v_row.HIREDATE,

v_row.SAL,

v_row.COMM,

v_row.DEPTNO);

END LOOP;

CLOSE c_emp;

COMMIT;

END prc_recoveremp;

SQL> execute prc_recoveremp;

PL/SQL procedure successfully completed.

SQL> select count(*) from emp;

COUNT(*)

----------

到此成功结束，检查EMP 表可以看到所有的数据已经全部都恢复了。

备注：在存储过程中我们创建了游标之后就将执行了DBMS_FLASHBACK.DISABLE，只

有这样我们才能在这个SESSION 中进行DML 操作。否则将产生ORA-08182 错误，In

Flashback mode, user cannot perform DML or DDL operations。

上面我们已经介绍了关于如何的应用Flashback Query来恢复DML的误操作，但都是基于时间点（timestamp）的，其实呢，尽管timestamp可以精确到毫秒，可是由于{oracle 每隔5分钟会将产生的 SCN 对应一个 TIME 做记录，也就是说通常只记录了SCN,但是每5分钟会记录 SCN and TIME }(这段话需要深入的考究)，当采用 timestamp 来做flashback 的时候就有可能产生偏差，5分钟的来由是在于表SYS.SMON_SCN_TIME，我们可以察看一下：

该表的记录一共是1440行，那来几行可以看看

THREAD TIME_MP TIME_DP SCN_WRP SCN_BAS

---------- ---------- ----------- ---------- ----------

1 1072772527 2003-12-30 0 8052536

1 1072772834 2003-12-30 0 8053330

1 1072773142 2003-12-30 0 8054053

1 1072773446 2003-12-30 0 8054845

可以看到，每行的timestamp差上5分钟左右，实际上，每5分钟，SMON删除最旧的数据并且插入当前的信息，这也就可以推算出为什么无论你的UNDO RETENTION 设置多大，Flashback Query 只能用5天(1440*5/24/60 )。所以基于SCN的Flashback Query是最准确的

举个例子看看：

SQL> select * from lyb;

未选定行

SQL> insert into lyb values (1);

已创建 1 行。

SQL> commit;

提交完成。

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

8058302

SQL> delete from lyb;