Oracle10g Flashback的相关实践

Oracle10g Flashback 实践2008-11-21 16:52一、概述环境：RHEL4.5，oracle10.2.0.1Flashback 是ORACLE 自9i 就开始提供的一项特性，在9i 中利用oracle 查询多版本一致的特点，实现从回滚段中读取表一定时间内操作过的数据，可用来进行数据比对，或者修正意外提交造成的错误数据，该项特性也被称为Flashback Query。在10g 中Flashback又得到了相当大的增强，利用Recycle Bin(回收站)和Flash Recovery Area(闪回区)的特性实现快速恢复删除表(Flashback Table)或做数据库时间点恢复(Flashback Database)的功能。要使用flashback 的特性，必须启用自动撤销管理表空间，不仅是flashback query,也包括flashback table 和flashback database，而对于后两项还会有些其它的附加条件，比如flashback table 需要启用了recycle bin(回收站)，flashback database 还要求必须启用了flashback area(闪回区)。SQL show parameter undoNAME TYPE VALUE- - -undo_management string AUTOundo_retention integer 10800undo_tablespace string UNDOTBS1在一种情况下，undo 表空间能够确保undo 中的数据在undo_retention 指定时间过期前一定有效，就是为undo 表空间指定Retention Guarantee，指定之后，oracle 对于undo 表空间中未过期的undo 数据不会覆盖，例如：SQL Alter tablespace undotbs1 retention guarantee;如果想禁止undo 表空间retention guarantee，如例：SQL Alter tablespace undotbs1 retention noguarantee;二、Flashback Query(闪回查询)Flashback Query 是利用多版本读一致性的特性从UNDO 表空间读取操作前的记录数据！1、As of timestamp的示例SQL create table tab_test as select rownum id from dba_users;SQL delete from tab_test where id commit;假设当前距离删除数据已经有3分钟左右的话：SQL select count(*) from tab_test as of timestamp sysdate-3/1440 where id insert into tab_test select * from tab_test as of timestamp sysdate-3/1440 where id commit;提示：as of timestamp|scn 的语法是自9iR2 后才开始提供支持，如果是9iR1 版本，需要使用DBMS_FLASHBACK包来应用flashback query 的特性。2、As of scn的示例如果需要对多个相互有主外键约束的表进行恢复时，如果使用as of timestamp 的方式，可能会由于时间点不统一的缘故造成数据选择或插入失败，通过scn 方式则能够确保记录的约束一致性。获取当前scn 的方式非常多，比如：SQL select current_scn from v$database;SQL select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER- 191460SQL delete tab_test where id5;SQL commit;SQL select * from tab_test as of scn 191460;SQL insert into tab_test select * from tab_test as of scn 191460 where id not in (select id from tab_test);SQL commit;事实上，Oracle 在内部都是使用scn，即使你指定的是as of timestamp，oracle 也会将其转换成scn，系统时间标记与scn 之间存在一张表，即SYS 下的SMON_SCN_TIME。SQL select scn,to_char(time_dp,yyyy-mm-dd hh24:mi:ss) time_dp from sys.smon_scn_time;3、Versions between版本查询功能更加强大，通过versions between 能够查看指定时间段内undo 表空间中记录的不同版本（注意只包括被提交的记录）。版本查询过程中提供了多个伪列如下：VERSIONS_STARTSCN/VERSIONS_STARTTIME 记录操作时的scn 或时间，如果为空，表示该行记录是在查询范围外创建的。VERSIONS_ENDSCN/VERSIONS_ENDTIME 记录失效时的scn 或时间，如果为空，说明记录当前时间在当前表内存在，或者已经被删除了，可以配合着VERSIONS_OPERATION 列来看，如果VERSIONS_OPERATION 列值为D，说明该列已被删除，如果该列为空，则说明记录在这段时间无操作。VERSIONS_XID 该操作的事务IDVERSIONS_OPERATION 对该行执行的操作：I 表示insert，D 表示delete，U 表示update。提示：对于索引键的update 操作，版本查询可能会将其识别成两个操作DELETE 和INSERT。SQL update tab_test set id=id+100 where id5;SQL commit;SQL delete tab_test where id commit;SQL insert into tab_test values (888);SQL commit;SQL select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER- 191611SQL select id,VERSIONS_XID,versions_startscn,to_char(VERSIONS_STARTTIME,yyyy-mm-dd hh24:mi:ss) VERSIONS_STARTTIME,versions_endscn,to_char(VERSIONS_ENDTIME,yyyy-mm-dd hh24:mi:ss) VERSIONS_ENDTIME,versions_operation from tab_test versions between scn 191460 and 191611 order by 3;4、Transaction query事务查询SQL select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER- 192329SQL delete tab_test where id rollback;SQL delete tab_test where id=5;SQL commit;SQL select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER- 192377SQL select xid,commit_scn,commit_timestamp,operation,undo_sql from flashback_transaction_query q where q.xid in( select versions_xid from tab_test versions between scn 192329 and 192329);注：由于事务ID 是保存在版本查询中，因此我们需要通过版本查询来关联出flashback 的事务信息，这也是前面操作前要通过dbms_flashback 包取scn 的原因。5、DDL的影响修改并提交过数据之后，对表做过DDL 操作，包括：drop/modify 列, move 表, drop 分区(如果有的话), truncate table/partition，这些操作会另undo 表空间中的撤销数据失效，对于执行过这些操作的表应用flashback query 会触发ORA-01466 错误。另外一些表结构修改语句虽然并不会影响到undo 表空间中的撤销记录，但有可能因表结构修改导致undo 中重做记录无法应用的情况，比如对于增加了约束，而flashback query 查询出的undo 记录已经不符合新建的约束条件，这个时候直接恢复显然不可能成功，你要么暂时disable 约束，要么通过适当逻辑，对要恢复的数据进行处理之后，再执行恢复。另外，flashback query 对v$tables,x$tables 等动态性能视图无效，不过对于dba_*,all_*,user_*等数据字典是有效的。同时该特性也完全支持访问远端数据库，比如：select * from tbldblink as of scn 360; 的形式。三、Flashback Table(闪回表)1、从RECYCLEBIN中恢复要恢复recyclebin 中的表，注意语句如下：Flashback table objName to before drop，这个obj_name 即可以是表名，也可以是recyclebin 中的对象表(支持同时操作多个表，表名之间以逗号分隔即可，要记住单个flashback table 是在同一个事务中，因此这些表的恢复操作要么都成功要么都失败)，由于该项功能是恢复被删除表，因此官方对其还有另外一称谓：flashback drop。系统参数recyclebin默认是on，即启用了回收站功能，可以在session或system级别动态更改。每一个用户都会有一个自己的recycle bin。SQL conn /as sysdbaSQL show parameter recyclebinNAME TYPE VALUE- - -recyclebin string onSQL grant flashback any table to u_test;SQL conn u_test/iamwangncSQL create table tab_test as select * from v$instance;SQL select count(*) from tab_test;COUNT(*)- 1SQL drop table tab_test;SQL select * from tab_test;select * from tab_test *ERROR at line 1:ORA-00942: table or view does not existSQL select object_name,original_name,operation,type,createtime,droptime,dropscn from recyclebin;OBJECT_NAME ORIGINAL_NAME OPERATION TYPE CREATETIME DROPTIME DROPSCN- - - - - - -BIN$XC9ucNX/thHgQAB/AQBpYw=$0 TAB_TEST DROP TABLE 2008-11-21:15:59:47 2008-11-21:16:05:21 246999SQL flashback table TAB_TEST to before drop;SQL select object_name,original_name from recyclebin;no rows selectedSQL select count(*) from tab_test;COUNT(*)- 1Flashback table 语句同时提示了一个rename to newTBname的子句，如果要恢复的表在当前schema 中已经存在同名的表，建议你在恢复时通过rename to 子句为待恢复的表指定一个新的表名，不然数据库会报ORA-38312 错误，如：SQL flashback table tab_test to before drop rename to tab_test_bak;当删除时指定purge 参数(等同于我们在windows 中按着shift 删除)则无法闪回了，如：SQL drop table tab_test purge;基于recycle bin 的表恢复，flashback drop 不能恢复参照完整性，这很容易理解，毕竟在该表删除之后，其被参照表是否有修改它已经无法控制了，因此如果该表有主外键约束的话，恢复之后，该约束是disable状态，需要dba 手工处理。基于recycle bin 的表恢复，所操作的表必须是存在于本地管理表空间中。Flashback drop 不能恢复字典管理表空间中被删除的表，也不能恢复系统表。基于recycle bin 的表恢复，被恢复的表的关联对象，比如其索引啦，约束的名称不会自动恢复成删除前的名称，而是系统自动生成的名称，如果你对表的索引约束有相关命名规范，那在恢复表之后，需要dba 手工将索引约束等改名。当删除表时，信赖于该表的物化视图也会同时删除，但是由于物化视图并不会被放入recycle bin，因此当你执行flashback table to before drop 时，也不能恢复依赖其的物化视图，需要dba 手工介入重新创建。相对于被删除的表而言，当数据文件空间不足时，oracle 会首先清理被删除表的索引，因此假如你执行flashback table to before drop 而发现缺少索引可能是正常的，说明你显然错过了最佳的恢复时机。如何删除回收站里的对象：A.Purge 指定表，例如：SQL select object_name,original_name from recyclebin;OBJECT_NAME ORIGINAL_NAME- -BIN$JhA057bpRxKICIe/vNahyQ=$0 TAB_TESTSQL purge table tab_test;或SQL purge table BIN$JhA057bpRxKICIe/vNahyQ=$0;B.Purge 指定表空间，例如：SQL purge tablespace ts_test;C.清空recyclebin，例如：SQL purge recyclebin;除上述的三种之外，还有一种方式就是删除recycle bin 中对象所属用户或所在表空间。2、从UNDO中恢复某些时候，我们要处理的表并不是被意外删除，而是被反复修改过多次，希望能回复到之前的某个时间点，借助flashback query 是可以，但flashback query 只是查询出记录，如果想做恢复还需要写出相应的insert 或update，也许还需要相当多的where 条件做判断，因此，我们需要更高效更严谨更简便的方式：flashback table tbname to scn/timestamp。基于undo 的表恢复，被恢复的表必须启用row movement，不然会报ORA-08189 错误。SQL select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER- 247826SQL delete from tab_test;SQL commit;SQL flashback table tab_test to scn 247826;ERROR at line 1:ORA-08189: cannot flashback the table because row movement is not enabledSQL ALTER TABLE tab_test ENABLE ROW MOVEMENT;SQL flashback table tab_test to scn 247826;SQL 基于undo 的表恢复，flashback table 实际上做的也是dml 操作(会在被操作的表上加dml 锁)，因此还需要注意triggers 对其的影响，默认情况下，flashback table to scn/timestamp 在执行时会自动disable 掉与其操作表相差的triggers，如果你希望在此期间trigger 能够继续发挥做用，可以在flashback table 后附加ENABLE TRIGGERS 子句。基于undo 的表恢复，索引会自动维护，但统计信息并不会恢复到指定的时间点。四、Flashback Database(闪回数据库)如果说flashback query 可用来恢复指定时间点的记录，flashback table 可用来恢复被删除或指定时间点的表，而flashback database，则直接提供一个数据库级别的恢复，可将数据库恢复到指定的时间点，相对于前两者，其粒度是最大的。1、必备条件A. 数据库启用了flashback database。B. 数据库必须打开了flash recovery area，如果数据库是RAC，flash recovery area 必须位于共享存储中。C. 数据库必须处于archivelog 模式。2、操作示例Flashback database 即可以在rman 中执行，也可以在sql*plus 中执行，其命令格式都是一样的，在这个示例中，我们所有操作都是在sql*plus 中执行。A. 检查是否启动了flash recovery area:SQL show parameter db_recovery_fileNAME TYPE VALUE- - -db_recovery_file_dest string /orahome/flash_recovery_areadb_recovery_file_dest_size big integer 2GB. 检查是否启用了归档SQL archive log list;C. 检查是否启用了flashback database 和force_loggingSQL select flashback_on,force_logging from v$database;FLASHBACK_ON FOR- -NO NO如果未启用则先启用：SQL alter system set db_flashback_retention_target=1440 scope=both; (单位是分钟，1440表示一天)SQL shutdown immediate;SQL startup mount exclusive;SQL alter database flashback on;SQL alter database force logging;SQL select flashback_on,force_logging from v$database;FLASHBACK_ON FOR- -YES YESSQL alter database ope