• 欢迎访问搞代码网站,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站!
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏搞代码吧

9i新特性之Flashback Query的应用—–_sqlserver

sqlserver 搞代码 3年前 (2018-06-16) 117次浏览 已收录 0个评论

用DBMS_FLASHBACK包

 

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

http://www.gaodaima.com/35496.html9i新特性之Flashback Query的应用—–_sqlserver

 

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(*)

———-

0

然后执行下面的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(*)

———-

14

到此成功结束,检查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;

 

已删除 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select dbms_flashback.get_system_change_number from dual;

 

GET_SYSTEM_CHANGE_NUMBER

————————

                 8058379

 

SQL> select * from lyb as of scn 8058302

  2  ;

 

        ID

———-

         1

SQL> select * from lyb as of scn 8058379

2         ;

 

未选定行

SQL>

 

 

所以说,基于scn的恢复才是能够做到精确!

当然,我们很明显遇到的问题是,如果真正的误操作,我那里会记录scn啊?这里就设计到另外的一个oracle很好用的工具,logminer,下次介绍!

 

注:SYS 用户不允许执行DBMS_FLASHBACK 包,将会产生ORA-08185 错误,

Flashback not supported for user SYS

 

参考:

otn.oracle.com

asktom.oracle.com
Seraphim(张乐奕)的《利用Flashback Query 恢复误操作的数据》

欢迎大家阅读《9i新特性之Flashback Query的应用—–_sqlserver,跪求各位点评,若觉得好的话请收藏本文,by 搞代码


喜欢 (0)
[搞代码]
分享 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址