Oracle 闪回技术

sweli

　　一、配置闪回数据库
　　1、数据库处于归档日志模式
　　SQL> select log_mode from v$database;
　　LOG_MODE
　　------------
　　ARCHIVELOG
　　2、创建闪回恢复区

　　SQL>>
　　System>
　　SQL>>
　　System>　　3、设置闪回保留目标时间

　　SQL>>
　　System>　　--DB_FLASHBACK_RETENTION_TARGET参数控制保留时间，单位是分钟，默认值是1天。闪回日志空间以循环的方式重用，更新的数据将覆盖旧的数据。(本例为保留4小时)
　　4、关闭数据库并启动到MOUNT状态
　　SQL> shutdown immediate;
　　Database closed.
　　Database dismounted.
　　ORACLE instance shut down.
　　SQL> startup mount;
　　ORACLE instance started.
　　Total System Global Area 3373858816 bytes

　　Fixed>
　　Variable>　　Database Buffers         1509949440 bytes
　　Redo Buffers              16232448 bytes
　　Database mounted.
　　5、启用闪回日志记录并打开数据库

　　SQL>>
　　Database>
　　SQL>>
　　Database>　　6、查看是否启用闪回日志记录
　　SQL> select flashback_on from v$database;
　　FLASHBACK_ON
　　------------------
　　YES
　　二、使用sqlplus闪回数据库
　　1、创建一个临时表
　　SQL> create table t as select * from dba_objects;
　　Table created.
　　2、查询当前系统时间表
　　SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
　　TO_CHAR(SYSDATE,'YY
　　-------------------
　　2016-03-15 11:34:21
　　3、对t表做一些DML操作
　　SQL> delete from t;
　　72464 rows deleted.
　　SQL> commit;
　　Commit complete.
　　SQL> insert into t select * from dba_objects whererownum commit;
　　Commit complete.
　　SQL> select count(*) from t;
　　COUNT(*)
　　----------
　　100
　　4、把数据库闪回到步骤2查询出的时间
　　SQL> shutdown abort
　　ORACLE instance shut down.
　　SQL> startup mount;
　　ORACLE instance started.
　　Total System Global Area 3373858816 bytes

　　Fixed>
　　Variable>　　Database Buffers         1509949440 bytes
　　Redo Buffers              16232448 bytes
　　Database mounted.
　　SQL> flashback database to timestampto_timestamp('2016-03-15 11:34:21','yyyy-mm-dd hh24:mi:ss');--可以接受时间戳或系统变更号(SCN)参数，不接受日期或日志世界的序列号
　　Flashback complete.
　　5、以只读方式打开数据库查询是否闪回成功 --注意使用read only方式打开数据库，验证闪回情况。

　　SQL>>
　　Database>　　SQL> select count(*) from t;
　　COUNT(*)
　　----------
　　72464
　　6、闪回成功后，以resetlogs方式打开数据库
　　SQL> shutdown abort
　　ORACLE instance shut down.
　　SQL> startup mount;
　　ORACLE instance started.
　　Total System Global Area 3373858816 bytes

　　Fixed>
　　Variable>　　Database Buffers         1509949440 bytes
　　Redo Buffers              16232448 bytes
　　Database mounted.

　　SQL>>
　　Database>　　SQL> select count(*) from t;
　　COUNT(*)
　　----------
　　72464
　　数据库闪回成功
　　注：如果flashback闪回的数据库时间太早，可以使用RECOVERDATABASE UNTILE TIME times恢复到后边的时间点。
　　--也可以使用RMAN进行闪回
　　RMAN>flashback database to time = to_date('2016-03-15 11:34:21','yyyy-mm-ddhh24:mi:ss');
　　RMAN>flashback database to scn = 2728665;
　　RMAN>flash backup to sequence = 2123 thread = 1;
　　三、限制生成的闪回数据量
　　1、关闭表空间闪回属性
　　ALTER TABLESPACE tablespace_name FLASHBACK OFF;
　　2、开启表空间闪回属性
　　ALTER TABLESPACE tablespace_name FLASHBACK ON;--只能在mount状态下执行
　　3、查看表空间的闪回属性
　　select name,flashback_on from v$tablespace;
　　四、设置sqlplus提示符
　　SQL> set sqlprompt "_user'@'_connect_identifier>"
　　SYS@ocp>
　　--为了对所有的sqlplus会话自动设置sqlprompt。将上面的命令放置在ORACLE_HOME/sqlplus/admin目录中的glogin.sql文件中
　　五、闪回删除
　　1、创建一个练习用户

　　SYS@ocp>create user dropper>　　User created.
　　SYS@ocp>grant create session,resource to dropper;
　　Grant succeeded.
　　SYS@ocp>connect dropper/dropper;
　　Connected.
　　2、创建一个带有索引和约束的表，并插入一行
　　DROPPER@ocp>create table names(name varchar2(10));
　　Table created.
　　DROPPER@ocp>create index name_idx on names(name);
　　Index created.
　　DROPPER@ocp>alter table names add constraint name_u unique(name);

　　Table>　　DROPPER@ocp>insert into names values('John');
　　1 row created.
　　DROPPER@ocp>commit;
　　Commit complete.
　　3、确认模式的内容
　　DROPPER@ocp>select object_name,object_type from user_objects;
　　OBJECT_NAME  OBJECT_TYPE
　　-------------------------------------
　　NAME_IDX      INDEX
　　NAMES         TABLE
　　DROPPER@ocp>select constraint_name,constraint_type,table_namefrom user_constraints;
　　CONSTRAINT_NAME               C TABLE_NAME
　　------------------------------ - ------------------------------
　　NAME_U                        U NAMES
　　4、删除该表
　　DROPPER@ocp>drop table names;
　　Table dropped.
　　5、查询回收站查看原始名称到回收站名称的映射
　　DROPPER@ocp>select object_name,original_name,type fromuser_recyclebin;
　　OBJECT_NAME                   ORIGINAL_NAME        TYPE
　　------------------------------ ------------------  -------------------------
　　BIN$q+6VgWdBRGOykqOJfCyZNg==$0 NAME_IDX            INDEX
　　BIN$u1TeIIlLS3isIPDvpSTblQ==$0 NAMES               TABLE
　　--注：视图并没有显示约束
　　6、可以使用回收站的对象名进行查询，但不可做DML语句
　　DROPPER@ocp>select * from"BIN$u1TeIIlLS3isIPDvpSTblQ==$0";
　　NAME
　　----------
　　John
　　DROPPER@ocp>insert into"BIN$u1TeIIlLS3isIPDvpSTblQ==$0" values('Root');
　　insert into "BIN$u1TeIIlLS3isIPDvpSTblQ==$0"values('Root')
　　*
　　ERROR at line 1:
　　ORA-38301: can not perform DDL/DML over objects in RecycleBin
　　7、使用FLASHBACK drop恢复表
　　DROPPER@ocp>flashback table names to before drop;
　　Flashback complete.
　　8、查询模式中的内容
　　DROPPER@ocp>select * from names;
　　NAME
　　----------
　　John
　　DROPPER@ocp>select object_name,original_name,type fromuser_recyclebin;
　　no rows selected
　　DROPPER@ocp>select object_name,object_type from user_objects;
　　OBJECT_NAME                     OBJECT_TYPE
　　-------------------------------------------------
　　BIN$q+6VgWdBRGOykqOJfCyZNg==$0   INDEX
　　NAMES                           TABLE
　　DROPPER@ocp>select constraint_name,constraint_type,table_namefrom user_constraints;
　　CONSTRAINT_NAME               C TABLE_NAME
　　------------------------------ - ------------------------------
　　BIN$cLAR1tu9Toi3u5qKdAbvIw==$0 U NAMES
　　9、将索引和约束重命名回原先的名称
　　DROPPER@ocp>alter index"BIN$q+6VgWdBRGOykqOJfCyZNg==$0" rename to name_idx;

　　Index>　　DROPPER@ocp>alter table names rename constraint"BIN$cLAR1tu9Toi3u5qKdAbvIw==$0" to name_u;

　　Table>　　DROPPER@ocp>select object_name,object_type from user_objects;
　　OBJECT_NAME    OBJECT_TYPE
　　----------------------------------
　　NAME_IDX         INDEX
　　NAMES            TABLE
　　DROPPER@ocp>select constraint_name,constraint_type,table_namefrom user_constraints;
　　CONSTRAINT_NAME               C TABLE_NAME
　　------------------------------ - ------------------------------
　　NAME_U                        U NAMES
　　10、使用SYS用户删除DROPPER模式
　　SYS@ocp>drop user dropper cascade;
　　User dropped.
　　11、查询DBA_RECYCLEBIN视图来证实确实删除了用户DROPPER拥有的所有对象。
　　SYS@ocp>select count(*) from dba_recyclebin whereowner='DROPPER';
　　COUNT(*)
　　----------
　　0
　　注：如果回收站中两两个表的原始名称相同，默认情况下，Flashback Drop命令总是恢复最新版本的表，但如果不是想要的版本，可以指定希望恢复的版本的回收站名称，而不是原先的名称。
　　SQL>flashback table "BIN$q+6VgWdBRGOykqOJfCyZNg==$0"to before drop;
　　六、管理回收站
　　SQL>show recyclebin;
　　user_recyclebin
　　dba_recyclebin
　　注：FlashbackDrop不适用于存储在SYSTEM表空间中的表，直接将它们删掉并清除了
　　永久清除删掉的对象
　　DROP TABLE table_name PURGE;--删除表并不将它转移到回收站
　　PURGE TABLE table_name;--从回收站中清除表。如果存在多个具有相同原始名称的对象，清除时间最久的对象。也可以通过指定回收站名称来避免这种混淆。
　　PURGE INDEX index_name;--从回收站中消除索引。同样可以指定原始名称或回收站名称。
　　PURGE TABLESPACE tablespace_name;--从表空间中清除所有删除的对象。
　　PURGE TABLESPACE tablespace_name USERuser_name;--从表空间中清除属于一个用户的所有删除的对象。
　　PURGE USER_RECYCLEBIN;--清除用户删除文件的所有对象。
　　PURGE DBA_RECYCLEBIN;--清除所有删除的对象，需要dba权限。
　　七、闪回查询
　　--所有形式的闪回查询依赖撤销数据来重构它在过去某个时间点的数据。
　　1、基本的闪回查询
　　1.1创建测试表并插入测试数据
　　USER1@mydb>create table regions (region_id number,region_namevarchar2(20));
　　Table created.
　　USER1@mydb>insert into regions values(1,'Europe');
　　1 row created.
　　USER1@mydb>insert into regions values(2,'Americas');
　　1 row created.
　　USER1@mydb>insert into regions values(3,'Asia');
　　1 row created.
　　USER1@mydb>insert into regions values(4,'Middle East');
　　1 row created.
　　USER1@mydb>commit;
　　Commit complete.
　　1.2查询系统当前时间，删除部分数据并确认
　　USER1@mydb>select sysdate from dual;
　　SYSDATE
　　-------------------
　　2016-03-15 19:08:16
　　USER1@mydb>delete from regions where region_name like 'A%';
　　2 rows deleted.
　　USER1@mydb>commit;
　　Commit complete.
　　USER1@mydb>select * from regions;
　　REGION_ID REGION_NAME
　　---------- --------------------
　　1 Europe
　　4 Middle East
　　1.3进行闪回查询
　　USER1@mydb>select * from regions as of timestampto_timestamp('2016-03-15 19:08:16','yyyy-mm-dd hh24:mi:ss');
　　REGION_ID REGION_NAME
　　---------- --------------------
　　1 Europe
　　2 Americas
　　3 Asia
　　4 Middle East
　　1.4查询闪回时间点到现在的差异
　　USER1@mydb>select * from regions as of timestampto_timestamp('2016-03-15 19:08:16','yyyy-mm-dd hh24:mi:ss') minus select * fromregions;
　　REGION_ID REGION_NAME
　　---------- --------------------
　　2 Americas
　　3 Asia
　　可以使用DBMS_FLASHBACK程序包将整个会话回退到过去某个时间，对其他会话没有影响。处于闪回模式中不支持DMS语句。
　　USER1@mydb>executedbms_flashback.enable_at_time(to_timestamp('2016-03-15 19:08:16','yyyy-mm-ddhh24:mi:ss'));
　　PL/SQL procedure successfully completed.
　　USER1@mydb>select * from regions;
　　REGION_ID REGION_NAME
　　---------- --------------------
　　1 Europe
　　2 Americas
　　3 Asia
　　4 Middle East
　　USER1@mydb>execute dbms_flashback.disable;
　　PL/SQL procedure successfully completed.
　　select * from table_name as oftimestamp(systimestamp-interval '30' minute);
　　select * from table_name as oftimestamp(systimestamp-interval '1' hour);
　　2、闪回表查询  --启用表闪回的第一步是在表上支持行移动。
　　2.1创建测试表插入测试数据
　　HR@mydb>create table dept (dept_id number,dept_namevarchar2(20));
　　Table created.
　　HR@mydb>alter table dept add constraint pk_dept primary key(dept_id);

　　Table>　　HR@mydb>create table emp (emp_id number,namevarchar2(20),dept_id number);
　　Table created.
　　HR@mydb>alter table emp add constraint fk_emp foreign key(dept_id) references dept(dept_id);

　　Table>　　HR@mydb>insert into dept values(1,'SUPPORT');
　　1 row created.
　　HR@mydb>select * from dept;
　　DEPT_ID DEPT_NAME
　　---------- --------------------
　　1 SUPPORT
　　HR@mydb>commit;
　　Commit complete.
　　HR@mydb>insert into emp values(101,'John',1);
　　1 row created.
　　HR@mydb>commit;
　　Commit complete.
　　HR@mydb>select * from emp;
　　EMP_ID NAME                   DEPT_ID
　　---------- -------------------- ----------
　　101 John                         1
　　2.2、查看当前系统时间
　　HR@mydb>select sysdate from dual;
　　SYSDATE
　　-------------------
　　2016-03-15 21:37:36
　　2.3、删除测试表数据
　　HR@mydb>delete from emp where emp_id=101;
　　1 row deleted.
　　HR@mydb>delete from dept where dept_id=1;
　　1 row deleted.
　　HR@mydb>commit;
　　Commit complete.
　　2.4、开始闪回表
　　flashback table table_name totimestamp to_timestamp(systimestamp-interval '30' minute);
　　HR@mydb>flashback table emp to timestampto_timestamp('2016-03-15 21:37:36','yyyy-mm-dd hh24:mi:ss');
　　flashback table emp to timestamp to_timestamp('2016-03-1521:37:36','yyyy-mm-dd hh24:mi:ss')
　　*
　　ERROR at line 1:
　　ORA-08189: cannot flashback the table because row movement is notenabled
　　--提示需要打开表的row movement
　　HR@mydb>alter table emp enable row movement;

　　Table>　　HR@mydb>alter table dept enable row movement;

　　Table>　　HR@mydb>flashback table emp to timestampto_timestamp('2016-03-15 21:37:36','yyyy-mm-dd hh24:mi:ss');
　　flashback table emp to timestamp to_timestamp('2016-03-1521:37:36','yyyy-mm-dd hh24:mi:ss')
　　*
　　ERROR at line 1:
　　ORA-02091: transaction rolled back
　　ORA-02291: integrity constraint (HR.FK_EMP) violated - parent keynot found
　　--提示有外键约束
　　--两个表同时闪回避免约束问题
　　HR@mydb>flashback table emp,dept to timestampto_timestamp('2016-03-15 21:37:36','yyyy-mm-dd hh24:mi:ss');
　　Flashback complete.
　　2.5、检查闪回结果
　　HR@mydb>select * from dept;
　　DEPT_ID DEPT_NAME
　　---------- --------------------
　　1 SUPPORT
　　HR@mydb>select * from emp;
　　EMP_ID NAME                   DEPT_ID
　　---------- -------------------- ----------
　　101 John                         1
　　--语法的变型允许闪回到一个系统变更号并在操作期间激活DML触发器。
　　flashback table emp,dept to scn 6539425 enable triggers;
　　3、闪回版本查询
　　--使用VERSIONS BETWEEN关键字
　　--根据scn的返回版本
　　select emp_id,versions_xid,versions_startscn,versions_endscn,versions_operation from empversions between scn minvalue and maxvalue where emp_id=101;
　　--根据时间戳的返回版本
　　select emp_id,versions_xid,versions_starttime,versions_endtime,versions_operation from empversions between timestamp (systimestamp - 25/1440) and systimestamp whereemp_id=101;
　　4、闪回事务

　　--使用闪回事务需要开启库的最小附加日志>　　4.1创建测试表并插入数据
　　USER1@mydb>create table countries(name varchar2(10));
　　Table created.
　　USER1@mydb>alter table countries enable row movement;

　　Table>　　USER1@mydb>insert into countries values('Zambia');
　　1 row created.
　　USER1@mydb>insert into countries values('Zimbabwe');
　　1 row created.
　　USER1@mydb>insert into countries values('Zamibia');
　　1 row created.
　　USER1@mydb>commit;
　　Commit complete.
　　USER1@mydb>select * from countries;
　　NAME
　　----------
　　Zambia
　　Zimbabwe
　　Zamibia
　　4.2对测试表进行更新
　　USER1@mydb>update countries set name='Namibia';
　　3 rows updated.
　　USER1@mydb>commit;
　　Commit complete.
　　USER1@mydb>select * from countries;
　　NAME
　　----------
　　Namibia
　　Namibia
　　Namibia
　　4.3查询行的所有版本，确定错误的事务ID
　　USER1@mydb>select name,versions_xid,versions_operation fromcountries versions between scn minvalue and maxvalue ;
　　NAME       VERSIONS_XID     V
　　---------- ---------------- -
　　Namibia    080007000F040000 U
　　Namibia    080007000F040000 U
　　Namibia    080007000F040000 U
　　Zamibia    040009003C030000 I
　　Zimbabwe   040009003C030000 I
　　Zambia     040009003C030000 I
　　6 rows selected.
　　4.4查询FLASHBACK_TARNSACTION_QUERY视图看到该事务影响的行，并给出如何取消影响的SQL语句。
　　--XID列是RAW类型的，VERSIONS_XID伪列是十六进制的，需要使用类型强制转换函数。
　　SYS@mydb>select operation,undo_sql fromflashback_transaction_query where xid=hextoraw('080007000F040000');
　　OPERATIO UNDO_SQL
　　--------------------------------------------------------------------------------------------------------------------------------
　　UPDATE   update "USER1"."COUNTRIES" set"NAME" = 'Zamibia' where ROWID = 'AAASjKAAEAAAACVAAC';
　　UPDATE   update "USER1"."COUNTRIES" set"NAME" = 'Zimbabwe' where ROWID = 'AAASjKAAEAAAACVAAB';
　　UPDATE   update "USER1"."COUNTRIES" set"NAME" = 'Zambia' where ROWID = 'AAASjKAAEAAAACVAAA';
　　--还可以使用DBMS_FLASHBACK包进行闪回事务
　　executesys.dbms_flashback.transaction_backout(numtxns=>2,xids=>sys.xid_array('080007000F040000'),options=>dbms_flashback.cascade);
　　5、闪回数据归档  --保证将表闪回到过去任何时间。
　　可以在预先存在的表空间中创建归档，但在新表空间中更加明智。
　　create flashback archive default hrarch tablespace fbda1 quota 10gretention 5 year;
　　--default关键字表示除非另行说明将用作所有表的归档。
　　alter flashback archive hrarch set default;
　　--quota显示归档在表空间中占用的空间。可以在原有的表空间或另一个表空间中添加更多的空间。
　　alter flashback archive hrarch add tablespace fbda2 quota 10g;
　　--可以调整保留时间
　　alter flashback archive hrarch modify retention 7 year;
　　--数据超过了指定的保留期限，FBDA进程自动从归档中删除。在过期之前也可以手动进行删除
　　alter flashback archive hrarch purge before timestampto_timestamp('01-01-2009','dd-mm-yyyy');
　　--只有FLASHBACK ARCHIVE ADMINISTER系统权限能够创建、修改或删除归档以及控制归档的保留和清除。
　　grant flashback archive administer to fbdaadmin;
　　--必须授予用户归档的FLASHBACK ARCHIVE权限以便能够对表进行归档。
　　grant flashback archive on hrarch to hr;
　　--启用表的归档保护
　　alter table hr.employees flashback archive hrarch;
　　--删除表的归档保护
　　alter table hr.employees no flashback archive;
　　--删除闪回数据归档
　　drop flashback archive hrarch;
　　参考《OCP_OCA认证考试指南全册__ORACLE_DATABASE_11G》