Oracle9i 中新增的闪回查询(Flashback Query)功能对于误删除或者误更新并且已经commit
了的情况,提供了简便快捷的恢复方法,而在Oracle 提供闪回查询之前,碰到这种情况只
能通过备份来进行基于时间点的恢复,无疑这比闪回查询要麻烦而且费时。
什么是Flashback Query
利用Oracle 多版本读一致的特性,在需要的时候通过undo 来提供所需的前镜像中的数据。
利用这个功能,可以看到历史数据(呵呵,就像时光倒流。月光宝盒?),甚至用历史数据
来修复误操作引起的错误。可以通过指定时间或者SCN 来检索需要的数据。
前提条件
数据库必须处于Automatic Undo Management 状态。
最大可以闪回查询的时间段由UNDO_RETENTION 初始化参数(单位为秒)指定
可以通过ALTER SYSTEM SET UNDO_RETENTION = ;来修改参数值
如何使用Flashback Query
通过SQL
使用SELECT 语句的AS OF 来进行闪回查询,语法如下:
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 10800
SQL> show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
使用AS OF 关键字来对表,视图,或者物化视图进行Flashback Query,如果指定了SCN,
那么expr 部分必须是一个数字,如果指定了TIMESTAMP,那么expr 必须是一个timestamp
类型的值。查询结果将返回在指定的SCN 或者时间点上的数据。
下面我们使用scott 方案来作一个实验。
[zhangleyi@linux9 bin]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 9 23:44:07 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect scott/tiger
Connected.
SQL> select sal from emp where empno=7369;
SAL
----------
800
SQL> update emp set sal=4000 where empno=7369;
1 row updated.
SQL> commit;
Commit complete.
SQL> select sal from emp where empno=7369;
SAL
----------
4000
备注:TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)指查询距当前时间一天以
前的时间点的数据,如果我们要查询一小时以前的,那么需要将DAY 替换成HOUR 即可,
查询10 分钟以前的将'1' DAY 替换'10' MINUTE。
以上演示了对于误更新的字段进行恢复的方法,但是如果想在update 的子查询部分使用AS
OF 那么该查询只能返回一条记录,否则将会报错。如下:
SQL> select empno,sal from emp;
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
SQL> select sal from emp
2 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
3 where empno=7369;
SAL
----------
800
SQL> update emp set sal=
2 (select sal from emp
3 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)
4 where empno=7369)
5 where empno=7369;
1 row updated.
SQL> select sal from emp where empno=7369;
SAL
----------
800
SQL> commit;
7839 5000
7844 1500
7876 1100
EMPNO SAL
---------- ----------
7900 950
7902 3000
7934 1300
14 rows selected.
SQL> update emp set sal=4000;
14 rows updated.
SQL> commit;
Commit complete.
SQL> select empno,sal from emp;
EMPNO SAL
---------- ----------
7369 4000
7499 4000
7521 4000
7566 4000
7654 4000
7698 4000
7782 4000
7788 4000
7839 4000
7844 4000
7876 4000
EMPNO SAL
---------- ----------
7900 4000
7902 4000
7934 4000
14 rows selected.
SQL> select empno,sal from emp
2 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
EMPNO SAL
---------- ----------
7900 950
7902 3000
7934 1300
14 rows selected.
SQL> update emp a set sal =
2 (select sal from emp b
3 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)
4 where a.empno=b.empno);
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)
*
ERROR at line 3:
ORA-00907: missing right parenthesis
其实上面的语法是没有问题的,但是可能是因为闪回查询的特殊性导致上面的SQL 报错,
而这种错误update 了大量数据时候的恢复才真正是闪回查询的方便所在,对于这种情况我
们可以有两种处理方法,一种是使用DBMS_FLASHBACK 包,将在后面介绍,另外一种方
法仍然是直接使用SQL,但是添加一个临时表作为中转,如下:
SQL> create table empsal_temp as
2 select empno,sal from emp
3 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
Table created.
SQL> update emp a set sal =
2 (select sal from empsal_temp b
3 where a.empno=b.empno);
14 rows updated.
SQL> select empno,sal from emp;
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
EMPNO SAL
---------- ----------
7900 950
7902 3000
7934 1300
14 rows selected.
SQL> commit;
Commit complete.
SQL> drop table empsal_temp;
Table dropped.
这样我们就完成了错误数据的恢复。COOL!! RIGHT? :D
介绍DBMS_FLASHBACK 包
DBMS_FLASHBACK 包提供了以下几个函数:
ENABLE_AT_TIME:设置当前SESSION 的闪回查询时间
ENABLE_AT_SYSTEM_CHANGE_NUMBER:设置当前SESSION 的闪回查询SCN
GET_SYSTEM_CHANGE_NUMBER:取得当前数据库的SCN
比如:select dbms_flashback.get_system_change_number from dual;
DISABLE:关闭当前SESSION 的闪回查询
当将一个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。
以上例子中的所有恢复都是基于时间点的,下面介绍基于SCN 的闪回查询。
既然已经有基于时间点的恢复,为什么还需要基于SCN 呢,我们先来看一个例子。
[zhangleyi@linux9 oralinux]$ sqlplus scott/tiger
SQL*Plus: Release 9.2.0.1.0 - Production on Sat Oct 11 02:26:20 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> set time on;
02:26:50 SQL> insert into dept values(60,'FLASH','BEIJING');
1 row created.
02:27:53 SQL> commit;
Commit complete.
02:27:57 SQL> delete from dept where deptno=60;
1 row deleted.
02:28:19 SQL> commit;
Commit complete.
02:28:21 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '1' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
02:29:49 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '2' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
02:31:11 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '3' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
02:31:19 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '4' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
02:31:30 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '5' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
02:31:39 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '6' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
可以发现我们闪回查询了1 分钟到6 分钟之前的所有前镜像都没有找到新插入的那条
DEPTNO=60 的记录,虽然TIMESTAMP 可以精确到毫秒,但是很显然我们很难准确地
定位到毫秒级的时间点。至于为什么如此,biti 在论坛上的某个帖子中有所探讨,这里暂时
先不追究产生这种结果的原因。在这种情况下,使用基于SCN 的闪回查询是解决问题的最
好办法。
以往的一些测试例子,都是在insert 数据之后立刻使用DBMS_FLASHBACK 包中的
GET_SYSTEM_CHANGE_NUMBER 函数来返回当时的SCN,然后再利用AS OF SCN 来进
行闪回查询,但是实际应用中这是不可能的,因为在误操作之前不会运行这个函数。所以我
们要使用LOGMINER 来对redolog 进行分析,得到当时错误地update 或者delete 数据时的
SCN。
对于LOGMINER 的安装和使用方法本文不进行详细的叙述,请自行查阅文档。
下面是结合LOGMINER 进行闪回查询的例子,为了描述简便,假设从删除数据到目前
ONLINE REDO LOG 没有进行LOG SWITCH,也就是我们只需要分析当前ACTIVE 的
ONLINE REDO LOG 就可以了。
SQL> connect / as sysdba
Connected.
SQL> select b.MEMBER,a.STATUS from v$log a,v$logfile b where a.GROUP#=b.GROUP#;
MEMBER STATUS
-------------------------------------------- ----------------
/oracle/oradata/oralinux/redo01.log INACTIVE
/oracle/oradata/oralinux/redo02.log INACTIVE
/oracle/oradata/oralinux/redo03.log CURRENT
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName =>
'/oracle/oradata/oralinux/redo03.log',Options => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(DictFileName =>
'/oracle/admin/oralinux/orcldict.ora');
PL/SQL procedure successfully completed.
SQL> select scn,sql_redo from (select * from v$logmnr_contents where
sql_redo like 'delete%' order by scn desc) where rownum<2;
SCN SQL_REDO
---------- -------------------------------------------------------------------
543523 delete from "SCOTT"."DEPT" where "DEPTNO" = '60' and "D
现在我们已经找到了删除那条记录时候的SCN 是543523。
SQL> select * from scott.dept as of scn 543523;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 FLASH BEIJING
搞定!我们利用SCN 进行闪回查询找到了删除前的那条数据,此时利用上文描述过的SQL
方法或者DBMS_FLASHBACK 方法都可以进行数据恢复了。
几个注意点:
1. Flashback Query 对于DDL 操作(比如DROP)无效,只能适用于DML 的误操作
(UPDATE,DELETE)
2. SYS 用户不允许执行DBMS_FLASHBACK 包,将会产生ORA-08185 错误,
Flashback not supported for user SYS
3. 可能需要给其它用户授权才能允许其它用户执行DBMS_FLASHBACK 包,需要执
行:GRANT EXECUTE ON DBMS_FLASHBACK TO SCOTT;
4. 闪回查询的功能不止是适用于数据恢复,同样适用于DSS 和OLAP,比如需要查询
在前一个小时内生成的新订单,那么就可以利用AS OF 来取得两个时间点的查询结
果的差集。
5. 如果结合使用LOGON TRIGGERS,那么可以实现不更改代码就支持各个时间点的
报表查询功能。
6. Flashback Query 的查询速度依赖于需要执行多少UNDO,也就是想查询多长时间以
前的数据库快照,回溯的时间越久可能执行的速度就越慢。
7. Flashback Query 不会真正的UNDO 任何数据,仅仅是一个查询的机制而已。
8. Flashback Query 不会告诉你到底数据发生了哪些变化,这是LOGMINER 的功能。
9. 如果需要恢复的表中有巨大的数据量,那么闪回查询会是一个极为昂贵的操作,此
时可能作基于时间点的恢复反而更有效。
了的情况,提供了简便快捷的恢复方法,而在Oracle 提供闪回查询之前,碰到这种情况只
能通过备份来进行基于时间点的恢复,无疑这比闪回查询要麻烦而且费时。
什么是Flashback Query
利用Oracle 多版本读一致的特性,在需要的时候通过undo 来提供所需的前镜像中的数据。
利用这个功能,可以看到历史数据(呵呵,就像时光倒流。月光宝盒?),甚至用历史数据
来修复误操作引起的错误。可以通过指定时间或者SCN 来检索需要的数据。
前提条件
数据库必须处于Automatic Undo Management 状态。
最大可以闪回查询的时间段由UNDO_RETENTION 初始化参数(单位为秒)指定
可以通过ALTER SYSTEM SET UNDO_RETENTION = ;来修改参数值
如何使用Flashback Query
通过SQL
使用SELECT 语句的AS OF 来进行闪回查询,语法如下:
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 10800
SQL> show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
使用AS OF 关键字来对表,视图,或者物化视图进行Flashback Query,如果指定了SCN,
那么expr 部分必须是一个数字,如果指定了TIMESTAMP,那么expr 必须是一个timestamp
类型的值。查询结果将返回在指定的SCN 或者时间点上的数据。
下面我们使用scott 方案来作一个实验。
[zhangleyi@linux9 bin]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 9 23:44:07 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect scott/tiger
Connected.
SQL> select sal from emp where empno=7369;
SAL
----------
800
SQL> update emp set sal=4000 where empno=7369;
1 row updated.
SQL> commit;
Commit complete.
SQL> select sal from emp where empno=7369;
SAL
----------
4000
备注:TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)指查询距当前时间一天以
前的时间点的数据,如果我们要查询一小时以前的,那么需要将DAY 替换成HOUR 即可,
查询10 分钟以前的将'1' DAY 替换'10' MINUTE。
以上演示了对于误更新的字段进行恢复的方法,但是如果想在update 的子查询部分使用AS
OF 那么该查询只能返回一条记录,否则将会报错。如下:
SQL> select empno,sal from emp;
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
SQL> select sal from emp
2 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
3 where empno=7369;
SAL
----------
800
SQL> update emp set sal=
2 (select sal from emp
3 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)
4 where empno=7369)
5 where empno=7369;
1 row updated.
SQL> select sal from emp where empno=7369;
SAL
----------
800
SQL> commit;
7839 5000
7844 1500
7876 1100
EMPNO SAL
---------- ----------
7900 950
7902 3000
7934 1300
14 rows selected.
SQL> update emp set sal=4000;
14 rows updated.
SQL> commit;
Commit complete.
SQL> select empno,sal from emp;
EMPNO SAL
---------- ----------
7369 4000
7499 4000
7521 4000
7566 4000
7654 4000
7698 4000
7782 4000
7788 4000
7839 4000
7844 4000
7876 4000
EMPNO SAL
---------- ----------
7900 4000
7902 4000
7934 4000
14 rows selected.
SQL> select empno,sal from emp
2 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
EMPNO SAL
---------- ----------
7900 950
7902 3000
7934 1300
14 rows selected.
SQL> update emp a set sal =
2 (select sal from emp b
3 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)
4 where a.empno=b.empno);
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)
*
ERROR at line 3:
ORA-00907: missing right parenthesis
其实上面的语法是没有问题的,但是可能是因为闪回查询的特殊性导致上面的SQL 报错,
而这种错误update 了大量数据时候的恢复才真正是闪回查询的方便所在,对于这种情况我
们可以有两种处理方法,一种是使用DBMS_FLASHBACK 包,将在后面介绍,另外一种方
法仍然是直接使用SQL,但是添加一个临时表作为中转,如下:
SQL> create table empsal_temp as
2 select empno,sal from emp
3 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
Table created.
SQL> update emp a set sal =
2 (select sal from empsal_temp b
3 where a.empno=b.empno);
14 rows updated.
SQL> select empno,sal from emp;
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
EMPNO SAL
---------- ----------
7900 950
7902 3000
7934 1300
14 rows selected.
SQL> commit;
Commit complete.
SQL> drop table empsal_temp;
Table dropped.
这样我们就完成了错误数据的恢复。COOL!! RIGHT? :D
介绍DBMS_FLASHBACK 包
DBMS_FLASHBACK 包提供了以下几个函数:
ENABLE_AT_TIME:设置当前SESSION 的闪回查询时间
ENABLE_AT_SYSTEM_CHANGE_NUMBER:设置当前SESSION 的闪回查询SCN
GET_SYSTEM_CHANGE_NUMBER:取得当前数据库的SCN
比如:select dbms_flashback.get_system_change_number from dual;
DISABLE:关闭当前SESSION 的闪回查询
当将一个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。
以上例子中的所有恢复都是基于时间点的,下面介绍基于SCN 的闪回查询。
既然已经有基于时间点的恢复,为什么还需要基于SCN 呢,我们先来看一个例子。
[zhangleyi@linux9 oralinux]$ sqlplus scott/tiger
SQL*Plus: Release 9.2.0.1.0 - Production on Sat Oct 11 02:26:20 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> set time on;
02:26:50 SQL> insert into dept values(60,'FLASH','BEIJING');
1 row created.
02:27:53 SQL> commit;
Commit complete.
02:27:57 SQL> delete from dept where deptno=60;
1 row deleted.
02:28:19 SQL> commit;
Commit complete.
02:28:21 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '1' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
02:29:49 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '2' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
02:31:11 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '3' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
02:31:19 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '4' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
02:31:30 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '5' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
02:31:39 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '6' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
可以发现我们闪回查询了1 分钟到6 分钟之前的所有前镜像都没有找到新插入的那条
DEPTNO=60 的记录,虽然TIMESTAMP 可以精确到毫秒,但是很显然我们很难准确地
定位到毫秒级的时间点。至于为什么如此,biti 在论坛上的某个帖子中有所探讨,这里暂时
先不追究产生这种结果的原因。在这种情况下,使用基于SCN 的闪回查询是解决问题的最
好办法。
以往的一些测试例子,都是在insert 数据之后立刻使用DBMS_FLASHBACK 包中的
GET_SYSTEM_CHANGE_NUMBER 函数来返回当时的SCN,然后再利用AS OF SCN 来进
行闪回查询,但是实际应用中这是不可能的,因为在误操作之前不会运行这个函数。所以我
们要使用LOGMINER 来对redolog 进行分析,得到当时错误地update 或者delete 数据时的
SCN。
对于LOGMINER 的安装和使用方法本文不进行详细的叙述,请自行查阅文档。
下面是结合LOGMINER 进行闪回查询的例子,为了描述简便,假设从删除数据到目前
ONLINE REDO LOG 没有进行LOG SWITCH,也就是我们只需要分析当前ACTIVE 的
ONLINE REDO LOG 就可以了。
SQL> connect / as sysdba
Connected.
SQL> select b.MEMBER,a.STATUS from v$log a,v$logfile b where a.GROUP#=b.GROUP#;
MEMBER STATUS
-------------------------------------------- ----------------
/oracle/oradata/oralinux/redo01.log INACTIVE
/oracle/oradata/oralinux/redo02.log INACTIVE
/oracle/oradata/oralinux/redo03.log CURRENT
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName =>
'/oracle/oradata/oralinux/redo03.log',Options => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(DictFileName =>
'/oracle/admin/oralinux/orcldict.ora');
PL/SQL procedure successfully completed.
SQL> select scn,sql_redo from (select * from v$logmnr_contents where
sql_redo like 'delete%' order by scn desc) where rownum<2;
SCN SQL_REDO
---------- -------------------------------------------------------------------
543523 delete from "SCOTT"."DEPT" where "DEPTNO" = '60' and "D
现在我们已经找到了删除那条记录时候的SCN 是543523。
SQL> select * from scott.dept as of scn 543523;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 FLASH BEIJING
搞定!我们利用SCN 进行闪回查询找到了删除前的那条数据,此时利用上文描述过的SQL
方法或者DBMS_FLASHBACK 方法都可以进行数据恢复了。
几个注意点:
1. Flashback Query 对于DDL 操作(比如DROP)无效,只能适用于DML 的误操作
(UPDATE,DELETE)
2. SYS 用户不允许执行DBMS_FLASHBACK 包,将会产生ORA-08185 错误,
Flashback not supported for user SYS
3. 可能需要给其它用户授权才能允许其它用户执行DBMS_FLASHBACK 包,需要执
行:GRANT EXECUTE ON DBMS_FLASHBACK TO SCOTT;
4. 闪回查询的功能不止是适用于数据恢复,同样适用于DSS 和OLAP,比如需要查询
在前一个小时内生成的新订单,那么就可以利用AS OF 来取得两个时间点的查询结
果的差集。
5. 如果结合使用LOGON TRIGGERS,那么可以实现不更改代码就支持各个时间点的
报表查询功能。
6. Flashback Query 的查询速度依赖于需要执行多少UNDO,也就是想查询多长时间以
前的数据库快照,回溯的时间越久可能执行的速度就越慢。
7. Flashback Query 不会真正的UNDO 任何数据,仅仅是一个查询的机制而已。
8. Flashback Query 不会告诉你到底数据发生了哪些变化,这是LOGMINER 的功能。
9. 如果需要恢复的表中有巨大的数据量,那么闪回查询会是一个极为昂贵的操作,此
时可能作基于时间点的恢复反而更有效。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/756652/viewspace-242127/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/756652/viewspace-242127/