第 29 章 使用闪回查询 作为其读取一致性模型的一部分,Oracle 可以显示已经提交给数据库的数据 您可以查询事务提交前已存在的数据 如果不小心提交了一个错误的 update 或 delete 操作, 那么可以使用闪回查询 (flashback query) 功能查看提交前存在的数据 可以使用闪回查询的结果还原数据 要支持闪回查询, 数据库必须使用系统管理的撤消功能来自动管理回滚段 ; 用户可以询问 DBA 以确定环境中是否启用了此功能 DBA 必须创建一个撤消表空间, 启用自动撤消管理 (Automatic Undo Management), 并创建一个撤消保留时间窗 闪回查询可以对远程数据库执行 Oracle 将试图在撤消表空间中维护足够的撤消信息, 以便在保留时间段内支持闪回查询
456 第 Ⅲ 部分高级主题 保留时间设置和撤消表空间中的可用空间的大小将极大地影响成功执行闪回查询的能力 Oracle 使用撤消功能回滚事务处理, 并支持闪回查询 Oracle 使用重做信息 ( 在联机重做日志文件中捕获的信息 ) 在数据库恢复过程中应用事务 在局部恢复操作中, 闪回查询是非常重要的工具 一般来说, 由于闪回查询取决于应用 程序开发人员控制之外的系统元素 ( 例如, 在某个时间段中的事务的数量以及撤消表空间的大 小 ), 因此, 不应该依赖于闪回查询设计应用程序的某一部分 而应该把闪回查询作为在测试 支持和数据恢复这些关键时期的一个选项 例如, 可以使用闪回查询及时地在过去的不同时刻创建表的副本, 以重构改变的数据 为了使用闪回查询的某些功能, 必须拥有对 DBMS_FLASHBACK 程序包的 EXECUTE 权限 大多数用户并不需要对该程序包拥有权限 29.1 基于时间的闪回示例 BOOK_ORDER 表有 6 条记录, 如以下的清单所示 : column Title format a30 select * from BOOK_ORDER; TITLE PUBLISHER CATEGORYNAME ---- ---- -- SHOELESS JOE MARINER ADULTFIC GOSPEL PICADOR ADULTFIC SOMETHING SO STRONG PANDORAS ADULTNF GALILEO'S DAUGHTER PENGUIN ADULTNF LONGITUDE PENGUIN ADULTNF ONCE REMOVED SANCTUARY PUB ADULTNF 6 rows selected. 一批货物到达后, 旧的 BOOK_ORDER 记录被删除, 且删除操作被提交 遗憾的是, 由于并非所有的图书都在这批货中, 因此使用 delete 操作是不合适的 : delete from BOOK_ORDER; 既然手中只有收到的书, 那么如何从 BOOK_ORDER 表重构那些未收到书的记录呢? 可以使用 Data Pump Import( 参阅第 24 章 ) 执行数据库恢复, 从而还原表 ; 或者也可以执行物理数据库恢复及时地将数据库复原至 delete 操作前的某个状态 但是, 若使用闪回查询, 则不必执行这些恢复操作 首先, 查询数据库中的旧数据 可以使用 select 命令的 as of timestamp 和 as of scn 子句
第 29 章使用闪回查询 457 来指定 Oracle 闪回多长时间以前的数据 select from BOOK_ORDER; 0 select from BOOK_ORDER as of timestamp (SysDate 5/1440); 6 在执行闪回查询时, 只改变数据的状态 使用了当前的系统时间 ( 查询 SysDate 值可以看到 ) 和当前的数据字典 如果表的结构发生变化, 查询就会失败 29.2 保存数据 如 BOOK_ORDER 示例所示, 闪回查询易于实现, 只要数据库的撤消管理适当地配置且撤消信息可用即可 但是, 怎样用闪回数据工作呢? 最简单的方法是将数据保存在单独的表中 因为每天有 1 440 分钟, 所以 SysDate 5/1440 语句将把数据库置于 5 分钟之前的状态 请 如果提交后至少经过了 5 分钟, 那么查询将不会返回任何行 create table BOOK_ORDER_OLD as select * from BOOK_ORDER as of timestamp (SysDate 5/1440); Table created. select from BOOK_ORDER_OLD; 6 可以这样验证数据的正确性 : column Title format a30 select * from BOOK_ORDER_OLD; TITLE PUBLISHER CATEGORYNAME ------ ------- -- SHOELESS JOE MARINER ADULTFIC GOSPEL PICADOR ADULTFIC SOMETHING SO STRONG PANDORAS ADULTNF GALILEO'S DAUGHTER PENGUIN ADULTNF LONGITUDE PENGUIN ADULTNF ONCE REMOVED SANCTUARY PUB ADULTNF
458 第 Ⅲ 部分高级主题 6 rows selected. 这样就可以使用这些数据来还原丢失的数据 执行选择性的更新 仅插入错误删除的行或者满足其他操作的需要 新表 BOOK_ORDER_OLD 没有索引, 也没有参照完整性约束 如果需要将其连接至其他表, 那么可能需要创建多个表的闪回副本, 以维护数据的参照完整性 同时, 请注意每一个查询都是在不同的时间点执行的 于是 as of timestamp 子句使用的相关时间将会导致混乱或不一致的结果 可以直接使用旧的数据 但是, 用户依赖对其事务可用的旧数据 从一般的 更安全的角度来说, 应当创建一个表并且将旧数据暂时存储进去 29.3 基于 SCN 的闪回示例 执行基于时间的闪回操作时, 实际在执行基于 SCN 的闪回操作 ; 用户正在依靠 Oracle 查找接近于所指定时间的 SCN 如果知道准确的 SCN, 则可以精确地执行闪回操作 为启动基于 SCN 的闪回, 必须首先知道事务的 SCN 为了得到最近的更改号, 可以使用 commit 命令, 然后使用 select 命令的 as of scn 子句 可以在执行事务前, 执行 DBMS_ FLASHBACK 程序包的 GET_SYSTEM_CHANGE_NUMBER 函数来查找当前的 SCN 在执行以下示例之前, 必须拥有 DBMS_FLASHBACK 程序包的 EXECUTE 权限 以下示例说明了该过程是针对 BOOK_ORDER_OLD 表的事务的一部分, 该表在本章的第一部分创建并填充 首先, 将当前的 SCN 分配给变量 SCN_FLASH, 并通过 SQL*Plus 的 print 命令显示 : variable SCN_FLASH number; execute :SCN_FLASH :=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER; PL/SQL procedure successfully completed. print SCN_FLASH SCN_FLASH 529732 接下来, 使用 delete 命令并提交结果 : delete from BOOK_ORDER_OLD; 6 rows deleted.
第 29 章使用闪回查询 459 现在, 可以查询闪回数据 虽然已知 SCN 的值, 但如果您是在同一个会话中, 那么可以 继续使用 SCN_FLASH 变量的值 : select from BOOK_ORDER_OLD; 0 select from BOOK_ORDER_OLD as of scn (:scn_flash); 6 现在, 通过 as of scn 子句的访问, 可以使用 BOOK_ORDER_OLD 中的闪回数据用旧值填充表 : insert into BOOK_ORDER_OLD select * from BOOK_ORDER_OLD as of scn (:scn_flash); 6 rows created. 更改表结构的 DDL 操作使表原来撤消的数据无效, 并且从执行 DDL 以后闪回功能在时间上受到限制 与空间有关的变化 ( 如改变 pctfree) 不会使旧的撤消数据无效 有关 flashback table 命令和 flashback database 命令的详细信息, 请参阅第 30 章 29.4 闪回查询失败的后果 如果在撤消表空间中没有足够的空间来维持闪回查询所需的所有数据, 查询就会失败 即使 DBA 创建了一个巨大的撤消表空间, 一系列大型事务可能还会使用全部可用的空间 每个失败的查询的一部分信息会写入数据库的警告日志中 从用户试图恢复旧数据的角度来说, 应当尽可能正确地和及时地恢复数据 一般来说, 可能需要执行多个闪回查询来确定返回多长时间之前成功查询的数据, 然后保存可以访问的最早的数据和最接近出错时刻的数据 一旦最早的数据从撤消表空间中删除, 就再也不能使用闪回查询重新得到了 如果不可能通过闪回取得很早之前的数据, 就需要执行某种数据库恢复操作 闪回整个数据库或者通过传统的 DBA 方法恢复特定的表和表空间 如果总是出现问题, 则应当增加撤消保留时间, 增加分配给撤消表空间的空间, 并检查应用程序的使用情况以确定为什么
460 第 Ⅲ 部分高级主题 有问题的事务总是不断发生 29.5 什么 SCN 与每一行关联 可以查看数据库中与每一行关联的最接近的 SCN 下面使用恢复到 BOOK_ORDER_OLD 表的数据来重新填充 BOOK_ORDER 表 : delete from BOOK_ORDER; 0 rows deleted. insert into BOOK_ORDER select * from BOOK_ORDER_OLD; 6 rows created. 现在, 使用 ORA_ROWSCN 来查看与每一行关联的 SCN: select Title, ORA_ROWSCN from BOOK_ORDER; TITLE ORA_ROWSCN SHOELESS JOE 553531 GOSPEL 553531 SOMETHING SO STRONG 553531 GALILEO'S DAUGHTER 553531 LONGITUDE 553531 ONCE REMOVED 553531 所有的行都是同一事务的一部分, 都发生在 SCN 553531 上 ( 数据库中的 SCN 与本示例中的 SCN 不同 ) 现在等待数据库中其他事务的发生, 然后插入一条新的记录 : insert into BOOK_ORDER values ('INNUMERACY','VINTAGE BOOKS','ADULTNF'); 1 row created. 现在, 新的更改已经提交, 可以查看与之关联的 SCN 了 : select Title, ORA_ROWSCN from BOOK_ORDER; TITLE ORA_ROWSCN ------- SHOELESS JOE 553531 GOSPEL 553531 SOMETHING SO STRONG 553531
第 29 章使用闪回查询 461 GALILEO'S DAUGHTER 553531 LONGITUDE 553531 ONCE REMOVED 553531 INNUMERACY 553853 ORA_ROWSCN 伪列的值不是绝对准确的, 因为 Oracle 通过行所在的块提交的事务处理来跟踪 SCN 块中的新事务处理可能更新块中所有行的 ORA_ROWSCN 值 SCN 映射到什么时间? 可以使用 SCN_TO_TIMESTAMP 函数来显示变化发生的日期 : select SCN_TO_TIMESTAMP(555853) from DUAL; SCN_TO_TIMESTAMP(555853) -------- 20-FEB-04 03.11.28.000000000 PM 可以合并这两个查询来查看每行最新的事务时间 : select Title, SCN_TO_TIMESTAMP(ORA_ROWSCN) from BOOK_ORDER; 29.6 闪回版本查询 可以显示在指定的时间间隔内存在的行的不同版本 如本章前面的示例所示, 因为版本变化依赖于 SCN, 所以只有提交的更改才会显示 闪回版本查询要求 DBA 已经把 UNDO_RETENTION 初始化参数设置为一个非零值 如果 UNDO_RETENTION 值很小, 那么可能遇到 ORA-30052 错误 为了准备这些示例, 首先要删除 BOOK_ORDER 表中的旧行 : delete from BOOK_ORDER; 接下来, 重新填充 BOOK_ORDER 表 : select SysTimeStamp from DUAL; insert into BOOK_ORDER select * from BOOK_ORDER_OLD; select SysTimeStamp from DUAL; 然后, 等待几分钟, 再更新所有的行 : select SysTimeStamp from DUAL;
462 第 Ⅲ 部分高级主题 update BOOK_ORDER set CategoryName = 'ADULTF'; select SysTimeStamp from DUAL; 要执行闪回版本查询, 可以使用 select 命令的 versions between 子句 可以指定时间戳或者 SCN 在此示例中, 时间戳子句的格式基于 Oracle 的标准格式 ( 对于当前值应该是 select SysTimeStamp from DUAL): select * from BOOK_ORDER versions between timestamp to_timestamp('20 FEB-04 16.00.20','DD-MON-YY HH24.MI.SS') and to_timestamp('20 FEB-04 16.06.20','DD-MON-YY HH24.MI.SS') ; 执行该查询时,Oracle 将为每行的每个版本返回一行, 该版本出现在 versions between 子句中指定的起点和终点之间 对那些返回的行, 可以查询其他伪列, 如表 29-1 所示 表 29-1 返回的行可以查询的其他伪列 伪列说明 VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID 数据第一次使值反射时的 SCN 如果为 NULL, 则行在查询的时间下限之前创建数据第一次使值反射时的时间戳 如果为 NULL, 则行在查询的时间下限之前创建行版本过期时的 SCN 如果为 NULL, 则行是当前版本, 或已经被删除行版本过期时的时间戳 如果为 NULL, 则行是当前版本, 或已经被删除创建行版本的事务标识符 VERSIONS_OPERATION 执行事务的操作 (I 表示插入,U 表示更新,D 表示删除 ) 以下示例显示了对闪回版本查询的使用 当前行的 Versions_StartSCN 值为 NULL; 旧行已经被更新 时间戳将因平台的不同而不同 select Title, Versions_StartSCN, Versions_Operation from BOOK_ORDER versions between timestamp TO_TIMESTAMP('20 FEB-04 16.00.20','DD-MON-YY HH24.MI.SS') and TO_TIMESTAMP('20 FEB-04 16.06.20','DD-MON-YY HH24.MI.SS') ; TITLE VERSIONS_STARTSCN V -------- ------- - ONCE REMOVED LONGITUDE GALILEO'S DAUGHTER SOMETHING SO STRONG GOSPEL SHOELESS JOE SHOELESS JOE
第 29 章使用闪回查询 463 GOSPEL SOMETHING SO STRONG GALILEO'S DAUGHTER LONGITUDE ONCE REMOVED versions between 子句可以用在 DML 命令和 DDL 命令的子查询中 可以使用 FLASHBACK_TRANSACTION_QUERY 数据字典视图来跟踪由特殊事务产生的变化 对于一个给定的事务,FLASHBACK_TRANSACTION_ QUERY 可以显示执行事务的用户的名称 所执行的操作 应用事务的表 开始和结束的 SCN 和时间戳以及用来撤消事务的 SQL 29.7 闪回计划 对于 DBA 来说, 闪回查询可以提供快速执行部分恢复操作的方法 如果数据能通过闪回查询重构并且数据量不大的话, 那么可以将多个闪回查询的结果保存在不同的表中 然后, 可以通过前几章给出的 SQL 选项 ( 相关的子查询 exists not exists minus, 等等 ) 来比较表中的数据 如果不能避免使用恢复操作, 那么应当确定基于时间的恢复操作所使用的时间段 在第 30 章将看到,Oracle 支持更多的选项 flashback database 命令和 flashback table 命令 对于应用程序开发人员和应用程序管理员来说, 闪回查询提供了重构数据的一个重要的工具 闪回查询对于测试和支持操作尤其重要 不要把闪回查询作为产品应用程序设计的一部分, 而应该将闪回查询作为一种备选方案, 以支持在数据受影响之前不能解决的情况