当数据误删除，想要回到删除前的状态，可以通过闪回查询获得闪回点的数据，前后比对，最后进行DML操作，恢复到自己想要的数据
闪回查询有三中查询手段；基于timestamp；基于scn和基于dbms_flashback包

1.基于timestamp
SCOTT@PROD1>create table t1 as select * from dept;

Table created.

SCOTT@PROD1>select * from t1;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SCOTT@PROD1>set time on;
15:00:08 SCOTT@PROD1>

15:01:19 SCOTT@PROD1>delete t1 where deptno=10;

1 row deleted.

15:01:32 SCOTT@PROD1>commit;

Commit complete.

15:01:46 SCOTT@PROD1>select * from t1;

DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

没有查询到删除前的数据,继续
15:04:18 SCOTT@PROD1>select * from t1 as of timestamp sysdate-3/1440;

DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

15:04:53 SCOTT@PROD1>select * from t1 as of timestamp sysdate-5/1440;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

2.基于scn

SCOTT@PROD1>create table t2 as select * from dept;

Table created.

SCOTT@PROD1>select * from t2;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SCOTT@PROD1>select dbms_flashback.get_system_change_number as scn from dual;
select dbms_flashback.get_system_change_number as scn from dual
*
ERROR at line 1:
ORA-00904: : invalid identifier

SYS@PROD1>select dbms_flashback.get_system_change_number as scn from dual;

SCN
----------
1177406

删除t2表
SCOTT@PROD1>delete from t2;

4 rows deleted.

SCOTT@PROD1>commit;

Commit complete.

SCOTT@PROD1>select * from t2;

no rows selected

SCOTT@PROD1>select * from t2 as of scn 1177406;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

(也可以通过select current_scn from v$database;获取当前 SCN)

3.基于 dbms_flashback 包
语法：
会话启用闪回指定时间：
DBMS_FLASHBACK.ENABLE_AT_TIME(query_time IN TIMESTAMP);
会话启用闪回指定SCN:
DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(query_scn IN NUMBER);
关闭闪回：
DBMS_FLASHBACK.DISABLE;

用户需要有执行 dbms_flashback 包的权限
SYS@PROD1>grant execute on dbms_flashback to scott;

Grant succeeded.

SCOTT@PROD1>create table t3 as select * from dept;

Table created.

SCOTT@PROD1>select * from t3;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

获取当前的scn号
SCOTT@PROD1>select dbms_flashback.get_system_change_number as scn from dual;

SCN
----------
1177951

SCOTT@PROD1>update t3 set dname=null,loc=null;

4 rows updated.

SCOTT@PROD1>commit;

Commit complete.

SCOTT@PROD1>select * from t3;

DEPTNO DNAME LOC
---------- -------------- -------------
10
20
30
40

SCOTT@PROD1>exec dbms_flashback.enable_at_system_change_number(1177951);

PL/SQL procedure successfully completed.

再次查看测试表
SCOTT@PROD1>select * from t3;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

在闪回模式中不能进行DML操作
SCOTT@PROD1>insert into t3 values(12,'a','a');
insert into t3 values(12,'a','a')
*
ERROR at line 1:
ORA-08182: operation not supported while in Flashback mode

结束dbms_flashback包的操作
SCOTT@PROD1>exec dbms_flashback.disable;

PL/SQL procedure successfully completed.

查询测试表，恢复原貌，且可以进行DML操作
SCOTT@PROD1>select * from t3;

DEPTNO DNAME LOC
---------- -------------- -------------
10
20
30
40

SCOTT@PROD1>delete from t3 where deptno=40;

1 row deleted.

SCOTT@PROD1>commit;

Commit complete.

SCOTT@PROD1>select * from t3;

DEPTNO DNAME LOC
---------- -------------- -------------
10
20
30

sys下不允许使用 dbms_flashback包
SYS@PROD1>exec dbms_flashback.enable_at_time(sysdate-5/1440);
BEGIN dbms_flashback.enable_at_time(sysdate-5/1440); END;

*
ERROR at line 1:
ORA-08185: Flashback not supported for user SYS
ORA-06512: at "SYS.DBMS_FLASHBACK", line 3
ORA-06512: at line 1

ITPub博客

【备份恢复】闪回技术之闪回查询

最新文章

ITPub博客

【备份恢复】 闪回技术之闪回查询

最新文章

【备份恢复】闪回技术之闪回查询