【Oracle】闪回技术
1.闪回技术描述
2.数据库的准备:
--undo表空间要设置成AUTO，设置合适的保存时间、undo表空间： SYS@ENMOEDU> show parameter undo NAME TYPE VALUE ------------------------ ----------- ----- undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1
3.闪回查询数据
（1）闪回查询
①基于timestamp
SCOTT@LGR> create table t1 as select * from dept; Table created. SCOTT@LGR> select * from t1; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 18:25:50 SCOTT@LGR> set time on 18:26:00 SCOTT@LGR> delete t1 where deptno=10; 1 row deleted. 18:26:21 SCOTT@LGR> commit; Commit complete. 18:26:58 SCOTT@LGR> select * from t1; DEPTNO DNAME LOC ---------- -------------- ------------- 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 18:31:26 SCOTT@LGR> select * from t1 as of timestamp to_timestamp('2016-12-15 18:24:00','yyyy-mm-dd hh24:mi:ss'); DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
②基于SCN
SYS@LGR> grant execute on dbms_flashback to scott; Grant succeeded. SYS@LGR> conn scott/tiger Connected. SCOTT@LGR> create table t2 as select * from dept; Table created. SCOTT@LGR> select * from t2; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SCOTT@LGR> select dbms_flashback.get_system_change_number as scn from dual; SCN ---------- 539268 SCOTT@LGR> delete t2; 4 rows deleted. SCOTT@LGR> commit; Commit complete. SCOTT@LGR> select * from t2; no rows selected SCOTT@LGR> select * from t2 as of scn 539268; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON ---也可以通过以下方式获取SCN SCOTT@LGR> conn / as sysdba Connected. SYS@LGR> select current_scn from v$database; CURRENT_SCN ----------- 539373
③基于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的权限授权给scott SYS@LGR> grant execute on dbms_flashback to scott; Grant succeeded. ---创建测试表 SCOTT@LGR> create table t3 as select * from dept; Table created. SCOTT@LGR> select * from t3; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON ---获取当前的SCN SCOTT@LGR> select dbms_flashback.get_system_change_number as scn from dual; SCN ---------- 540384 ---对t3表进行操作 SCOTT@LGR> update t3 set dname=null,loc=null; 4 rows updated. SCOTT@LGR> commit; Commit complete. SCOTT@LGR> select * from t3; DEPTNO DNAME LOC ---------- -------------- ------------- 10 20 30 40 ---执行dbms_flashback包 SCOTT@LGR> exec dbms_flashback.enable_at_system_change_number(540384); PL/SQL procedure successfully completed. ---再次查看测试表 SCOTT@LGR> select * from t3; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON ---注意的是，在闪回模式中不能进行DML操作 SCOTT@LGR> insert into t3 values (1,'a','a'); insert into t3 values (1,'a','a') * ERROR at line 1: ORA-08182: operation not supported while in Flashback mode ---结束操作，需要执行如下过程 SCOTT@LGR> exec dbms_flashback.disable; PL/SQL procedure successfully completed. ---查询测试表，恢复原貌，且可以进行DML操作 SCOTT@LGR> select * from t3; DEPTNO DNAME LOC ---------- -------------- ------------- 10 20 30 40 SCOTT@LGR> delete t3 where deptno=20; 1 row deleted. SCOTT@LGR> select * from t3; DEPTNO DNAME LOC ---------- -------------- ------------- 10 30 40 注：sys下不允许使用dbms_flashback包 SYS@LGR> 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
（2）闪回版本查询
语法： select column_name[,column_name,...] from table_name versions between [SCN | TIMESTAMP] [expr | MINVALUE] and [expr | MAXVALUE]; ---连续三次对emp表进行修改 SCOTT@ENMOEDU> select ename,job,sal from emp where empno=7369; ENAME JOB SAL ---------- --------- ---------- SMITH CLERK 800 SCOTT@ENMOEDU> update emp set sal=sal+200 where empno=7369; 1 row updated. SCOTT@ENMOEDU> commit; Commit complete. SCOTT@ENMOEDU> update emp set sal=sal+200 where empno=7369; 1 row updated. SCOTT@ENMOEDU> commit; Commit complete. SCOTT@ENMOEDU> update emp set sal=sal+200 where empno=7369; 1 row updated. SCOTT@ENMOEDU> commit; Commit complete. ---查看修改后的sal SCOTT@ENMOEDU> select ename,job,sal from emp where empno=7369; ENAME JOB SAL ---------- --------- ---------- SMITH CLERK 1400 ---闪回版本查询，可以清晰的看到每次更新的数据 SCOTT@ENMOEDU> select to_char(versions_starttime,'yyyy-mm-dd hh24:mi:ss') as starttime,versions_xid,ename,job,sal from emp versions between timestamp sysdate-10/1440 and sysdate where empno=7369; STARTTIME VERSIONS_XID ENAME JOB SAL ------------------- ---------------- ---------- --------- ---------- 2016-12-16 09:57:13 02001400B3030000 SMITH CLERK 1400 2016-12-16 09:57:09 05002000B3030000 SMITH CLERK 1200 2016-12-16 09:56:54 03001900B3030000 SMITH CLERK 1000 SMITH CLERK 800
—附：设计到的伪列
—闪回版本注意事项
VERSIONS 子句不能用于：
—外部表
—临时表
—固定表
—视图
VERSIONS不能跨DDL命令。
段收缩操作被过滤掉。
（3）闪回事务处理查询
①11g闪回事务处理处理查询必须要启用最小补充日志
---启用最小补充日志 SYS@ENMOEDU> alter database add supplemental log data; Database altered. ---查询最小补充日志是否开启 SYS@ENMOEDU> select supplemental_log_data_min from v$database; SUPPLEME -------- YES
②建立测试表，并对其进行DML操作
SYS@ENMOEDU> create table t (id number(2),name varchar(10)) tablespace users; Table created. SYS@ENMOEDU> insert into t values (1,'zhangsan'); 1 row created. SYS@ENMOEDU> update t set name='zs' where id=1; 1 row updated. SYS@ENMOEDU> commit; Commit complete.
③执行闪回版本查询，得到事务ID
SYS@ENMOEDU> select versions_xid,versions_startscn,id,name from t versions between timestamp minvalue and maxvalue; VERSIONS_XID VERSIONS_STARTSCN ID NAME ---------------- ----------------- ---------- ---------- 07001600CB020000 1035729 1 zs
④通过事务ID得到相关SQL
事务‘07001600CB020000’做了两个操作：插入数据、更新数据，闪回事务会逆向的返回这个事务的操作，通过这组SQL可修改回事务修改前的样子。
SYS@ENMOEDU> select undo_sql from flashback_transaction_query where xid='07001600CB020000'; UNDO_SQL -------------------------------------------------------------- update "SYS"."T" set "NAME" = 'zhangsan' where ROWID = 'AAAVslAAEAAAAJMAAA'; delete from "SYS"."T" where ROWID = 'AAAVslAAEAAAAJMAAA';
⑤关闭最小补充日志
SYS@ENMOEDU> alter database drop supplemental log data; Database altered.
4.闪回表
语法：
FLASHBACK TABLE[ schema.] table [, [ schema. ] table ]... TO {{{ SCN |TIMESTAMP }expr | RESTORE POINT restore_point} [{ ENABLE | DIABLE} TRIGGERS ]| BEFORE DROP [ RENAME TO table]};
注：FLASHBACK TABLE 是DDL命令
①创建测试表 fb_table, 在表 fb_table 上创建触发器（trg_fb_table ），fb_table 的 的 x 字段创建一个索引（idx_fb_table ），此时，与 fb_table 相关的对象就有两个，一个是触发器，一个是索引，这三个对象的状态都是有效的。
SYS@ENMOEDU> conn scott/tiger Connected. SCOTT@ENMOEDU> create table fb_table(x number(2),d date); Table created. SCOTT@ENMOEDU> create or replace trigger trg_fb_table 2 before insert on fb_table for each row 3 begin 4 if :new.d is null then 5 :new.d :=to_date('20160606','yyyymmdd'); 6 end if; 7 end; 8 / Trigger created. SCOTT@ENMOEDU> create unique index idx_fb_table on fb_table(x); Index created. SCOTT@ENMOEDU> COL OBJECT_NAME FOR A15 SCOTT@ENMOEDU> / OBJECT_NAME STATUS --------------- ------- IDX_FB_TABLE VALID FB_TABLE VALID TRG_FB_TABLE VALID
②插入测试数据，验证触发器
SCOTT@ENMOEDU> insert into fb_table(x) values(1); 1 row created. SCOTT@ENMOEDU> insert into fb_table(x,d) values (2,sysdate); 1 row created. SCOTT@ENMOEDU> commit; Commit complete. SCOTT@ENMOEDU> select * from fb_table; X D ---------- --------- 1 06-JUN-16 2 16-DEC-16
③返回当前时间
SCOTT@ENMOEDU> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. SCOTT@ENMOEDU> select sysdate from dual; SYSDATE ------------------- 2016-12-16 11:33:29
④删除表中的一条记录
SCOTT@ENMOEDU> delete fb_table where x=2; 1 row deleted. SCOTT@ENMOEDU> commit; Commit complete. SCOTT@ENMOEDU> select * from fb_table; X D ---------- ------------------- 1 2016-06-06 00:00:00
⑤尝试用闪回表的方式恢复数据
--第一次闪回报错很明显是因为没有启动行移动，启动后，成功闪回表 SCOTT@ENMOEDU> flashback table fb_table to timestamp to_timestamp('2016-12-16 11:33:29','yyyy-mm-dd hh24:mi:ss'); flashback table fb_table to timestamp to_timestamp('2016-12-16 11:33:29','yyyy-mm-dd hh24:mi:ss') * ERROR at line 1: ORA-08189: cannot flashback the table because row movement is not enabled SCOTT@ENMOEDU> select table_name,row_movement from user_tables where table_name='FB_TABLE'; TABLE_NAME ROW_MOVE ------------------------------ -------- FB_TABLE DISABLED SCOTT@ENMOEDU> alter table fb_table enable row movement; Table altered. SCOTT@ENMOEDU> select table_name,row_movement from user_tables where table_name='FB_TABLE'; TABLE_NAME ROW_MOVE ------------------------------ -------- FB_TABLE ENABLED SCOTT@ENMOEDU> flashback table fb_table to timestamp to_timestamp('2016-12-16 11:33:29','yyyy-mm-dd hh24:mi:ss'); Flashback complete. SCOTT@ENMOEDU> select * from fb_table; X D ---------- ------------------- 1 2016-06-06 00:00:00 2 2016-12-16 11:33:19
⑥查看与测试表相关对象
--所有对象的状态均为有效的，测试的时候我们创建触发器和索引意义不大了，不过要说明的是，11g在闪回表上做了升级，10g闪回表后，触发器为失效的状态。 SCOTT@ENMOEDU> select object_name,status from user_objects where object_name like '%FB_TABLE'; OBJECT_NAME STATUS --------------- ------- TRG_FB_TABLE VALID FB_TABLE VALID IDX_FB_TABLE VALID
⑦也可以基于时间间隔、scn闪回一张或者多张表。
---基于时间间隔闪回 SCOTT@ENMOEDU> flashback table fb_table to timestamp sysdate-7/1440; Flashback complete. SCOTT@ENMOEDU> flashback table fb_table to timestamp sysdate-interval'8'minute; Flashback complete. ---基于scn闪回 SCOTT@ENMOEDU> flashback table fb_table to scn 1041216; Flashback complete.
5.闪回归档
语法： CREATE FLASHBACK ARCHIVE [DEFAULT] flashback_archive TABLESPACE tablespace_name [QUOTA integer {K| M| G| T| P| E}] RETENTION integer {YEAR | MONTH | DAY};
① 创建测试表空间用户，分配角色
SYS@ENMOEDU> create tablespace ts_users datafile'/u01/app/oracle/oradata/ENMOEDU/ts_users.dbf' size 10M; Tablespace created. SYS@ENMOEDU> create user lgr identified by oracle default tablespace ts_users; User created. SYS@ENMOEDU> grant connect,resource to lgr; Grant succeeded.
② 创建flashback archive
SYS@ENMOEDU> create flashback archive fbad_ts_users tablespace ts_users retention 1 day; Flashback archive created.
③创建表t1，启用闪回归档数据（这个动作需要有“flashback archive”权限）
SYS@ENMOEDU> conn lgr/oracle Connected. LGR@ENMOEDU> create table t1(x int) flashback archive fbad_ts_users; create table t1(x int) flashback archive fbad_ts_users * ERROR at line 1: ORA-55620: No privilege to use Flashback Archive LGR@ENMOEDU> conn / as sysdba Connected. SYS@ENMOEDU> grant flashback archive on fbad_ts_users to lgr; Grant succeeded. SYS@ENMOEDU> create table t1(x int) flashback archive fbad_ts_users; create table t1(x int) flashback archive fbad_ts_users * ERROR at line 1: ORA-00955: name is already used by an existing object SYS@ENMOEDU> conn lgr/oracle Connected. LGR@ENMOEDU> create table t1(x int) flashback archive fbad_ts_users; Table created.
④对已存在的表启动闪回归档数据，如下
LGR@ENMOEDU> create table t2(x int); Table created. LGR@ENMOEDU> alter table t2 flashback archive fbad_ts_users; Table altered.
⑤查看已启用闪回归档数据的表（其中：E ARCHIVE_TABLE_NAME 为记录闪回数据的表，我们无法查看它）
TABLE_NAME OWNER_NAME FLASHBACK_ARCHI ARCHIVE_TABLE_NAME STATUS ---------- ----------- --------------- -------------------- ------- T1 LGR FBAD_TS_USERS SYS_FBA_HIST_88884 ENABLED T2 LGR FBAD_TS_USERS SYS_FBA_HIST_88885 ENABLED
⑥禁用闪回归档数据
使用“no flashback archive”子句可以disable flashback archive(注：需要有权限） LGR@ENMOEDU> alter table t2 no flashback archive; alter table t2 no flashback archive * ERROR at line 1: ORA-55620: No privilege to use Flashback Archive LGR@ENMOEDU> conn / as sysdba Connected. SYS@ENMOEDU> alter table lgr.t2 no flashback archive; Table altered. TABLE_NAME OWNER_NAME FLASHBACK_ARCHI ARCHIVE_TABLE_NAME STATUS ---------- ----------- --------------- -------------------- ------- T1 LGR FBAD_TS_USERS SYS_FBA_HIST_88884 ENABLED
⑦删除数据
---查看t1表数据 SYS@ENMOEDU> select * from lgr.t1; X ---------- 1 2 3 4 5 6 7 8 9 10 10 rows selected. ---查看当前的scn SYS@ENMOEDU> select dbms_flashback.get_system_change_number as scn from dual; SCN ---------- 1046908 ---删除t1表的数据 SYS@ENMOEDU> delete lgr.t1 where x<10; 9 rows deleted. ---查看删除后的数据 SYS@ENMOEDU> select * from lgr.t1; X ---------- 10 SYS@ENMOEDU> commit; Commit complete. SYS@ENMOEDU> select * from lgr.t1 as of scn 1046908; X ---------- 1 2 3 4 5 6 7 8 9 10 10 rows selected.
