OraclegFlashback实践

1、Oracle10gFlashback 实践2008-11-2116:52一、概述环境： RHEL4.5，oracle10.2.0.1Flashback 是 ORACLE 自 9i 就开始提供的一项特性，在 9i 中利用 oracle 查询 多版本一致的特点， 实现从回滚段中读取表一定时间内操作过的数据， 可用来进 行数据比对，或者修正意外提交造成的错误数据，该项特性也被称为 FlashbackQuery 。在 10g 中 Flashback 又得到了相当大的增强，利用 RecycleBin（ 回收站 ）和 FlashRecoveryArea（ 闪回区 ）的特性实现快速恢复删除表 （Flashb

2、ackTable） 或做数据库时间点恢复 （FlashbackDatabase） 的功能。 要使用 flashback 的特性，必须启用自动撤销管理表空间，不仅是 flashbackquery, 也包括 flashbacktable 和 flashbackdatabase ，而对于后两项 还会有些其它的附加条件，比如 flashbacktable 需要启用了 recyclebin（ 回收 站）,flashbackdatabase 还要求必须启用了 flashbackarea（闪回区）。 SQLshowparameterundoNAMETYPEVALUE undo_managementstrin

3、gAUTOundo_retentioninteger10800undo_tablespacestringUNDOTBS1在一种情况下， undo 表空间能够确保 undo 中的数据在 undo_retention 指定 时间过期前一定有效， 就是为 undo 表空间指定 RetentionGuarantee ，指定之 后， oracle 对于 undo 表空间中未过期的 undo 数据不会 覆盖，例如：SQLAltertablespaceundotbs1retentionguarantee;如果想禁止 undo 表空间 retentionguarantee ，如例：SQLAltertables

4、paceundotbs1retentionnoguarantee;二、FlashbackQuery( 闪回查询 )FlashbackQuery 是利用多版本读一致性的特性从 UNDO 表空间读取操作前的 记录数据！1 、Asoftimestamp的示例SQLcreatetabletab_testasselectrownumidfromdba_users;SQLdeletefromtab_testwhereidcommit;假设当前距离删除数据已经有 3 分钟左右的话：SQLselectcount(*)fromtab_testasoftimestampsysdate-3/1440whereidi

5、nsertintotab_testselect*fromtab_testasoftimestampsysdate-3/144 0whereidcommit;提示： asoftimestamp|scn 的语法是自 9iR2 后才开始提供支持，如果是 9iR1 版本，需要使用 DBMS_FLASHBACK 包来应用 flashbackquery 的特性。2 、Asofscn 的示例 如果需要对多个相互有主外键约束的表进行恢复时，如果使用 asoftimestamp的方式，可能会由于时间点不统一的缘故造成数据选择或插入失败，通过scn方式则能够确保记录的约束一致性。获取当前 scn 的方式非常多，比

6、如：SQLselectcurrent_scnfromv$database;SQLselectdbms_flashback.get_system_change_numberfromdual;GET_SYSTEM_CHANGE_NUMBER191460SQLdeletetab_testwhereid5;SQLcommit;SQLselect*fromtab_testasofscn191460;SQLinsertintotab_testselect*fromtab_testasofscn191460whereidnotin(s electidfromtab_test);SQLcommit;事实上，O

7、racle在内部都是使用sen，即使你指定的是asoftimestamp , oracle 也会将其转换成 scn ，系统时间标记与 scn 之间存在一张表，即 SYS 下的 SMON_SCN_TIME 。SQLselectscn,to_char(time_dp,yyyy-mm-ddhh24:mi:ss)time_dpfromsy s.smon_scn_time;3、Versionsbetween版本查询功能更加强大，通过 versionsbetween 能够查看指定时间段内 undo 表空间中 记录的不同版本(注意只包括被提交的记录)版本查询过程中提供了多个伪列如下：VERSIONS_STA

8、RTSCN/VERSIONS_STARTTIME 记录操作时的 scn 或时间， 如果为空，表示该行记录是在查询范围外创建的。VERSIONS_ENDSCN/VERSIONS_ENDTIME 记录失效时的 scn 或时间，如果 为空，说明记录当前时间在当前表内存在，或者已经被删除了，可以配合着 VERSIONS_OPERATION 列来看，如果 VERSIONS_OPERATION 列值为 D ， 说明该列已被删除，如果该列为空，则说明记录在这段时间无操作。VERSIONS_XID 该操作的事务 IDVERSIONS_OPERATION 对该行执行的操作： I 表示 insert ，D 表示

9、delete ，U 表示 update 。提示：对于索引键的 update 操作，版本查询可能会将其识别成 两个操作 DELETE 和 INSERT。SQLupdatetab_testsetid=id+100whereid5;SQLcommit;SQLdeletetab_testwhereidcommit;SQLinsertintotab_testvalues(888);SQLcommit;SQLselectdbms_flashback.get_system_change_numberfromdual;GET_SYSTEM_CHANGE_NUMBER191611SQLselectid,VERS

10、IONS_XID,versions_startscn,to_char(VERSIONS_START TIME,yyyy-mm-ddhh24:mi:ss)VERSIONS_STARTTIME,versions_endscn,t o_char(VERSIONS_ENDTIME,yyyy-mm-ddhh24:mi:ss)VERSIONS_ENDTI ME,versions_operationfromtab_testversionsbetweenscn191460and1916 11orderby3;4 、Transactionquery事务查询SQLselectdbms_flashback.get_

11、system_change_numberfromdual;GET_SYSTEM_CHANGE_NUMBER192329SQLdeletetab_testwhereidrollback;SQLdeletetab_testwhereid=5;SQLcommit;SQLselectdbms_flashback.get_system_change_numberfromdual;GET_SYSTEM_CHANGE_NUMBER192377SQLselectxid,commit_scn,commit_timestamp,operation,undo_sqlfromfl ashback_transactio

12、n_queryqwhereq.xidin(selectversions_xidfromtab_test versionsbetweenscn192329and192329);注：由于事务 ID 是保存在版本查询中，因此我们需要通过版本查询来关联出flashback 的事务信息，这也是前面操作前要通过 dbms_flashback 包取 scn 的 原因5 、DDL 的影响修改并提交过数据之后，对表做过 DDL 操作，包括： drop/modify列 ,move表,drop分区（如果有的话）,truncatetable/partition，这些操作会另undo 表空间中的撤销数据失效，对于执行过

13、这些操作的表应用 flashbackquery 会触发 ORA-01466 错误。另外一些表结构修改语句虽然并不会影响到 undo 表空间中 的撤销记录， 但有可能因表结构修改导致 undo 中重做记录无法应用的情况， 比 如对于增加了约束， 而 flashbackquery 查询出的 undo 记录已经不符合新建的 约束条件，这个时候直接恢复显然不可能成功，你要么暂时 disable 约束，要么 通过适当逻辑，对要恢复的数据进行处理之后，再执行恢复。另外， flashbackquery 对 v$tables,x$tables 等动态性能视图无效，不过对于 dba_*,all_*,user_*

14、 等数据字典是有效的。同时该特性也完全支持访问远端数据 库，比如：select*fromtbldblinkasofscn360; 的形式。三、FlashbackTable（闪回表）1 、从 RECYCLEBIN 中恢复要恢复 recyclebin 中的表，注意语句如下：FlashbacktableobjNametobeforedrop ，这个 obj_name 即可以是表名，也 可以是 recyclebin 中的对象表 （支持同时操作多个表， 表名之间以逗号分隔即可， 要记住单个 flashbacktable 是在同一个事务中，因此这些表的恢复操作要么都 成功要么都失败 ），由于该项功能是恢复

15、被删除表，因此官方对其还有另外一称谓：flashbackdrop 。系统参数recyclebin默认是on，即启用了回收站功能, 可以在 session 或 system 级别动态更改。每一个用户都会有一个自己的recyclebin 。SQLconn/assysdbaSQLshowparameterrecyclebinNAMETYPEVALUE recyclebinstringonSQLgrantflashbackanytabletou_test;SQLconnu_test/iamwangncSQLcreatetabletab_testasselect*fromv$instance;SQLse

16、lectcount(*)fromtab_test;COUNT(*)1SQLdroptabletab_test;SQLselect*fromtab_test;select*fromtab_test*ERRORatline1:ORA-00942:tableorviewdoesnotexistSQLselectobject_name,original_name,operation,type,createtime,droptime,dropscnfromrecyclebin;OBJECT_NAMEORIGINAL_NAMEOPERATIONTYPECREATETIMEDROPTIMEDROPSCNBI

17、N$XC9ucNX/thHgQAB/AQBpYw=$0TAB_TESTDROPTABLE2008-11-21:15:59:472008-11-21:16:05:21246999SQLflashbacktableTAB_TESTtobeforedrop;SQLselectobject_name,original_namefromrecyclebin;norowsselectedSQLselectcount(*)fromtab_test;COUNT(*)1Flashbacktable 语句同时提示了一个 renametonewTBname 的子句，如果要 恢复的表在当前 schema 中已经存在同

18、名的表， 建议你在恢复时通过 renameto 子句为待恢复的表指定一个新的表名，不然数据库会报 ORA-38312 错误，如： SQLflashbacktabletab_testtobeforedroprenametotab_test_bak;当删除时指定 purge 参数（等同于我们在 windows 中按着 shift 删除 ）则无法闪 回了，如：SQLdroptabletab_testpurge;基于 recyclebin 的表恢复， flashbackdrop 不能恢复参照完整性，这很容易理 解，毕竟在该表删除之后， 其被参照表是否有修改它已经无法控制了， 因此如果 该表有主外键约束

19、的话，恢复之后，该约束是 disable 状态，需要 dba 手工处 理。基于 recyclebin 的表恢复，所操作的表必须是存在于本地管理表空间中。 Flashbackdrop 不能恢复字典管理表空间中被删除的表，也不能恢复系统表。 基于 recyclebin 的表恢复，被恢复的表的关联对象，比如其索引啦，约束的名 称不会自动恢复成删除前的名称， 而是系统自动生成的名称， 如果你对表的索引 约束有相关命名规范，那在恢复表之后，需要dba 手工将索引约束等改名。当删除表时， 信赖于该表的物化视图也会同时删除， 但是由于物化视图并不会被 放入 recyclebin ，因此当你执行 flashb

20、acktabletobeforedrop 时，也不能恢复 依赖其的物化视图，需要 dba 手工介入重新创建。相对于被删除的表而言，当数据文件空间不足时， oracle 会首先清理被删除表 的索引，因此假如你执行 flashbacktabletobeforedrop 而发现缺少索引可能是 正常的，说明你显然错过了最佳的恢复时机。如何删除回收站里的对象：A. Purge 指定表，例如： SQLselectobject_name,original_namefromrecyclebin;OBJECT_NAMEORIGINAL_NAMEBIN$JhA057bpRxKICIe/vNahyQ=$0TAB_T

21、ESTSQLpurgetabletab_test;SQLpurgetableBIN$JhA057bpRxKICIe/vNahyQ=$0;B.Purge 指定表空间，例如：SQLpurgetablespacets_test;C.清空 recyclebin，例如：SQLpurgerecyclebin;除上述的三种之外，还有一种方式就是删除 recyclebin 中对象所属用户或所在 表空间。2 、从 UNDO 中恢复某些时候， 我们要处理的表并不是被意外删除， 而是被反复修改过多次， 希望能 回复到之前的某个时间点，借助 flashbackquery 是可以，但 flashbackquery 只是

22、查询出记录，如果想做恢复还需要写出相应的 insert 或 update ，也许还需 要相当多的 where 条件做判断，因此，我们需要更高效更严谨更简便的方式： flashbacktabletbnametoscn/timestamp 。基于 undo 的表恢复，被恢复的表必须启用 rowmovement ，不然会报 ORA-08189 错误。SQLselectdbms_flashback.get_system_change_numberfromdual; GET_SYSTEM_CHANGE_NUMBER247826SQLdeletefromtab_test;SQLcommit;SQLflas

23、hbacktabletab_testtoscn247826;ERRORatline1:ORA-08189:cannotflashbackthetablebecauserowmovementisnotenabledSQLALTERTABLEtab_testENABLEROWMOVEMENT; SQLflashbacktabletab_testtoscn247826;SQL基于 undo 的表恢复， flashbacktable 实际上做的也是 dml 操作（会在被操作的 表上加 dml 锁 ），因此还需要注意 triggers 对其的影响，默认情况下， flashbacktabletoscn/t

24、imestamp 在执行时会自动 disable 掉与其操作表相差 的 triggers ，如果你希望在此期间 trigger 能够继续发挥做用，可以在 flashbacktable 后附加 ENABLETRIGGERS 子句。基于 undo 的表恢复，索引会自动维护， 但统计信息并不会恢复到指定的时间点。四、 FlashbackDatabase（ 闪回数据库 ）如果说 flashbackquery 可用来恢复指定时间点的记录， flashbacktable 可用来 恢复被删除或指定时间点的表， 而 flashbackdatabase ，则直接提供一个数据库 级别的恢复，可将数据库恢复到指定的

25、时间点， 相对于前两者， 其粒度是最大的。1、必备条件A. 数据库启用了 flashbackdatabase 。B. 数据库必须打开了 flashrecoveryarea ，如果数据库是RAC, flashrecoveryarea 必须位于共享存储中。C. 数据库必须处于archivelog模式。2、操作示例Flashbackdatabase 即可以在 rman 中执行,也可以在 sql*plus 中执行,其命令格式都是一样的，在这个示例中，我们所有操作都是在 sql*plus 中执行A.检查是否启动了 flashrecoveryarea:SQLshowparameterdb_recovery

26、_fileNAMETYPEVALUE db_recovery_file_deststring/orahome/flash_recovery_areadb_recovery_file_dest_sizebiginteger2GB. 检查是否启用了归档SQLarchiveloglist;C. 检查是否启用了 flashbackdatabase和 force_loggingSQLselectflashback_on,force_loggingfromv$database;FLASHBACK_ONFORNONO 如果未启用则先启用：SQLaltersystemsetdb_flashback_retention_target=1440scope=both;（单位是分钟， 1440 表示一天 ）SQLshutdownimmediate;SQLstartupmountexclusive;SQLalterdatabaseflashbackon;SQLalterdatabaseforcelogging;SQLselectflashback_on,force_loggingfromv$database;FLASHBACK_ONFORYESYESSQLalterdatabaseopen;D. 查询