10_11g新特性_事务管理.doc

Oracle Database 11g：面向 DBA 和开发人员的重要特性利用 LogMiner 和闪回数据存档进行事务管理学习如何通过收回具体的事务及其相关项来识别并回滚时间。LogMiner 是 Oracle Database 中一个经常受到忽视但功能却非常强大的工具。它可用于从重做日志文件中提取 DML 语句 引发事务的初始 SQL，甚至是能撤销事务的 SQL。（有关 LogMiner 的介绍及其工作方式，请参阅我在 Oracle Magazine 上发表的文章“挖掘线索”。）至今为止，这个功能强大的工具由于缺少较简单的界面而得不到普遍认可。然而，在 Oracle Database 11g 中，Oracle Enterprise Manager 具有一个使用 LogMiner 从重做日志提取事务的图形界面，这为使用这一工具检查和回滚事务带来了极大的便利。（注意：与以前的版本相同，如果愿意，您可以继续使用 DBMS_LOGMNR 程序包执行命令行驱动的日志挖掘。）下面我们来看一个示例。要启用日志挖掘，您只需针对数据库或至少是针对表启用的小型追加日志。闪回事务需要主键日志。要针对整个数据库启用它，请执行以下命令： SQL alter database add supplemental log data; Database altered. SQL alter database add supplemental log data (primary key) columns; Database altered.现在，请看以下由某个应用程序针对数据库执行的语句： SQL insert into res values (100002,sysdate,12,1); 1 row created. SQL commit; Commit complete.SQL update res set hotel_id = 13 where res_id = 100002;1 row updated. SQL commit; Commit complete.SQL delete res where res_id = 100002; 1 row deleted. SQL commit; Commit complete.仔细观察这些语句：每个语句后都有一个提交语句，这表明每个语句都是一个事务。现在让我们看一下如何使用 Oracle Database 11g Database Control 中的 LogMiner 检查事务。 在 Enterprise Manager 屏幕中，从 Database 主页进入 Availability 选项卡。单击 Manage 下的 View and Manage Transactions。LogMiner 主界面显示，如下图所示： 您可以输入具体的时间范围或 SCN 范围来搜索事务。在上图中，我已经在 Query Time Range 中输入了一个时间范围进行搜索。在 Query Filter 中，我只用了 SCOTT 的事务，因为它曾用来执行过所有的 DML。在 Advanced Query 部分中，您可以输入其他任何筛选器。填完所有的域后，单击 Continue。这将导致日志挖掘过程对重做日志（联机的和存档的，必要的话）进行搜索，寻找用户 SCOTT 发布的事务。此过程结束后，您将看到结果屏幕。结果屏幕的顶端部分与下图类似：结果表明，通过搜索找到两个由 SCOTT 发布的事务，这些事务影响了两条记录。屏幕的底端部分显示了这些事务的详细信息。这是屏幕的局部视图。您可以看到，事务显示为 1 ins（表示“1 条插入语句”）。最左边一列显示了事务标识符 (XID)，是唯一标识事务的编号。如果点击事务标识符，您可以看到相应事务的详细信息，如下图所示：如您所见，您可以使用 Database Control 搜索和识别事务。单击 Previous Transaction 和 Next Transaction 按钮可以滚动浏览通过搜索找到的所有事务。用例如何使用这一特性？有几种方法。最重要的用途可能就是查清“谁”做了“什么”。如果由于性能原因您没有启用审计，或者仅仅是没有保留审计记录，只需通过挖掘重做日志（联机的和存档的）在 LogMiner 界面中搜索相关线索即可。在搜索屏幕中，您可以在 Query Filter 下的 Advanced Query 域中输入附加筛选条件。假设，您要查找一个插入、删除或更新了 RES_ID = 100002 的记录的事务。可以使用 dbms_logmnr 程序包中的 column_present 函数在重做流中搜索具体的值，如下所示：这个函数将提取 SCOTT 模式下 RES 表的 RES_ID 列中包含 100002 的所有事务。您还可以使用该特性掘出针对该数据库执行的 DDL 命令。要实现此目的，选择 Query Filter 部分中的单选按钮 View DDL Only 即可。收回选定的事务检查一个事务时，您想怎样处理这个事务？一种想法是将其撤销，因为此事务的执行有误，这或许是您查找该事务的首要原因。这非常简单。如果该事务是一个插入，您只需将其删除即可；如果它是一个更新，那么撤销会将该行还原至前一个值。然而，请仔细观察本例中使用的事务。第一个事务插入了一行。第二个事务更新了刚插入的行，而第三个事务将那一行删除了。第一个事务（插入）就是您要收回的事务。但是，问题是，那一行已经被后续的事务删除了，那么在本例中撤销事务是什么呢？这就是 Oracle Database 11g 中相关事务视图特性的用途所在。单击 Flashback Transaction。经过一些搜索后，系统将显示类似下图的屏幕：该屏幕将显示相关事务以及更新和删除。现在，在收回事务时，您还可以收回其相关项。要实现这一目的，从下面的列表中选择 Cascade 单选按钮，然后单击 OK 即可。它会为您显示所需收回的不同事务。单击事务 ID 查看 Oracle 将执行哪些 SQL 语句来撤销这个事务。例如，要撤销插入，Oracle 必须执行删除，如上图所示。如果点击下一个事务（就在它的下面），您将看到收回下一个事务所需的详细操作：这样，您就了解了操作构思。单击 Submit，所有这些事务都将一次性全部回滚。这是撤销事务及其相关项最彻底的方法。命令行界面如果您无权访问 Enterprise Manager 怎么办？或者也许您想通过脚本来完成这一切，那该怎么办？程序包 DBMS_FLASHBACK（Oracle Database 10g 中也提供该程序包）新增了一个名为 TRANSACTION_BACKOUT 的过程。这个过程过载，因此您必须将值传递给命名参数，如下所示。declare trans_arr xid_array;begin trans_arr := xid_array(030003000D040000,F30003000D04010); dbms_flashback.transaction_backout ( numtxns = 1, xids = trans_arr, options = dbms_flashback.cascade );end;id_array 类型也是 Oracle Database 11g 的新增内容。它用来向该过程传递一系列事务标识符。LogMiner 的其他改进如果您一直使用 XMLType 作为数据类型，那么在 Oracle Database 11g 中您更有理由使用它，您会很高兴地看到 LogMiner 也可以挖掘 XML 数据。XML 数据在 SQL_REDO 和 SQL_UNDO 列中都有显示。启动 LogMiner 时，您可以设置选项 SKIP_CORRUPTION，这会跳过重做日志中的受损块。因此，即使部分数据损坏了，您也可以回收重做日志里的有效数据。以下是这一改进语法的用法：begin dbms_logmnr.start_logmnr( options = dbms_logmnr.skip_corruption ) ;end;闪回数据存档Oracle9i Database 第 2 版以闪回查询的形式引入了众所周知的时间机器。闪回查询允许您选择更改前的数据。例如，如果您将一个值从 100 改为 200 并将其提交，即使更改已经提交了，您仍然可以选择两分钟前的值。这种技术使用了撤销段中更改前的数据。在 Oracle Database 10g 中，这项功能通过引入闪回版本查询得到增强，您甚至可以将某一行的更改追溯到撤销段所能提供的最久远的更改状态。但是，这里存在一个小问题：数据库回收后，撤销数据被清除了，随之更改前的值也消失了。即使不回收数据库，数据也可能因时间太长而退出撤销段为新更改让出空间。由于 11g 之前的闪回操作依赖于撤销数据，而撤销数据的可用时间短暂，您无法真正长期使用这些数据或者将其用于审计之类较永久的记录。作为变通手段，我们通过编写触发器来长期记录数据库的更改。不过，不要感到失望。在 Oracle Database 11g 中，闪回数据存档结合了两者的优势：它既提供闪回查询的简易性与功能性，又不像撤销数据一样依赖临时存储。它在更加永久的位置（即闪回恢复区）记录更改。我们来看一个示例。（注意：您需要激活自动撤销管理，这样闪回数据存档才能发挥作用。）首先，创建一个闪回数据存档，如下所示：SQL create flashback archive near_term 2 tablespace far_near_term 3 retention 1 month 4 /Flashback archive created.目前先不用管术语“保留”的意义，我们稍后再讨论。（这是记录更改的位置。）存档在表空间 far_near_term 中创建好了。 假设您需要记录表 TRANS 的更改。那么，您只需启用该表的闪回数据存档状态，开始在存档中记录更改即可。SQL alter table trans flashback archive near_term;Table altered.这会将表置于闪回数据存档模式。现在，该表中的所有行更改都将受到永久跟踪。我们来看一个演示。 首先，选择该表的具体一行。SQL select txn_amt from trans where trans_id = 2; TXN_AMT- 19325.67SQL update trans set txn_amt = 2000 where trans_id = 2;1 row updated.SQL commit;Commit complete.现在，如果您选择了该行，这一列将始终显示 2000。要查找早先某一时间点的值，可以使用闪回查询，如下所示：elect txn_amtfrom transas of timestamp to_timestamp (07/18/2007 12:39:00,mm/dd/yyyy hh24:mi:ss)where trans_id = 2; TXN_AMT- 19325.67 现在，隔一段时间，当撤销数据从撤销段中清除后，再次查询这个闪回数据：select txn_amtfrom transas of timestamp to_timestamp (07/18/2007 12:39:00,mm/dd/yyyy hh24:mi:ss)where trans_id = 2;结果返回：19325.67。撤销数据已经清除了，那么这个数据来自何处呢？我们问问 Oracle。您可以使用自动跟踪来查看执行计划： SQL set autotrace traceonly explainSQL select txn_amt 2 from trans 3 as of timestamp to_timestamp (07/18/2007 12:39:00,mm/dd/yyyy hh24:mi:ss) 4 where trans_id = 2;Execution Plan-Plan hash value: 535458644-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop-| 0 | SELECT STATEMENT | | 2 | 52 | 10 (10)| 00:00:01 | | 1 | VIEW | | 2 | 52 | 10 (10)| 00:00:01 | | 2 | UNION-ALL | | | | | | |* 3 | FILTER | | | | | | | 4 | PARTITION RANGE SINGLE| | 1 | 52 | 3 (0)| 00:00:01 | 1 | 1|* 5 | TABLE ACCESS FULL | SYS_FBA_HIST_68909 | 1 | 52 | 3 (0)| 00:00:01 | 1 | 1|* 6 | FILTER | | | | | | |* 7 | HASH JOIN OUTER | | 1 | 4053 | 10 (10)| 00:00:01 | |* 8 | TABLE ACCESS FULL | TRANS | 1 | 38 | 6 (0)| 00:00:01 | | 9 | VIEW | | 2 | 8030 | 3 (0)| 00:00:01 | |* 10 | TABLE ACCESS FULL | SYS_FBA_TCRV_68909 | 2 | 8056 | 3 (0)| 00:00:01 | |-Predicate Information (identified by operation id):- 3 - filter(NULL IS NOT NULL) 5 - filter(TRANS_ID=2 AND ENDSCN161508784336056 AND ENDSCN=1073451 AND (STARTSCN IS NULL OR STARTSCN=161508784336056) 6 - filter(F.STARTSCN1073451) AND (STARTSCN IS NULL OR STARTSCN create tablespace fda datafile +DATA size 100M;Tablespace created.SQL create flashback archive near_termtablespace fdaretention 1 monthFlashback archive created.现在我们来创建一个示例表。SQL conn arup/arupConnected.SQL create table trans( trans_id number, trans_dt date, product_id number(10), quantity number(5), trans_amt number(15,2)/使用之前创建的闪回存档为此表启用闪回，同时启用行移动：SQL alter table trans flashback archive near_term;Table altered.SQL alter table trans enable row movement 2 /Table altered.现在，这个表已经启用了闪回，我们来执行一项简单的 DML 操作：SQL insert into trans values (1,sysdate,1,1,1000);1 row created.SQL commit;Commit complete.插入不会创建存档记录，因为此类操作不会更改数据。我们来执行更新：SQL update trans set trans_amt = 2000;1 row updated.SQL commit;Commit complete.现在，我们将执行可更改结构的 DDL 操作：SQL alter table trans add (price number(15,2);Table altered.注：表的更改非常容易，即便已经启用了闪回也是如此。Oracle Database 要如何获知历史表中的这个新列？为了跟踪此更改，它实际将为历史数据创建几个新表。让我们来看看这些表：SQL select * from tab;TNAME TABTYPE CLUSTERID- - -SYS_FBA_DDL_COLMAP_79410 TABLESYS_FBA_HIST_79410 TABLESYS_FBA_TCRV_79410 TABLETRANS TABLE请注意，这些新表的名称中带有 FBA，表示它们是特殊的历史表。79410 这个数字是 TRANS 表的 object_id。SYS_FBA_TCRV_79410 表记录对此表执行的操作，如下所示。Operation 列显示 I（代表插入）和 U（代表更新）。SQL select * from SYS_FBA_TCRV_79410;RID- STARTSCN ENDSCN XID O- - - -AAATYyAAEAAAATkAAA 13585319 13585472 09001B00CF430000 IAAATYyAAEAAAATkAAA 13585472 05001B000C440000 USYS_FBA_HIST_79410 表记录通过 DDL 操作对行所作的更改。 SQL select * from SYS_FBA_HIST_79410;RID- STARTSCN ENDSCN XID O TRANS_ID TRANS_DT PRODUCT_ID- - - - - - - QUANTITY TRANS_AMT PRICE- - -AAATYyAAEAAAATkAAA 13585319 13585472 09001B00CF430000 I 1 11-AUG-10 1 1 1000注意这里有一个 PRICE 列。这是因为该列被添加到主表中，Oracle 同时会自动将其添加到历史表中。但在更新完成之后，PRICE 列将不再存在；因而在这个跟踪表中，列值为空。最后，SYS_FBA_DDL_COLMAP_79410 表跟踪列添加。如果部分列进行了重命名，则此表会跟踪旧名称和新名称：SQL select * from SYS_FBA_DDL_COLMAP_79410; STARTSCN ENDSCN XID O COLUMN_NAM TYPE HISTORICAL- - - - - - 13585094 TRANS_ID NUMBER TRANS_ID 13585094 TRANS_DT DATE TRANS_DT 13585094 PRODUCT_ID NUMBER(10) PRODUCT_ID 13585094 QUANTITY NUMBER(5) QUANTITY 13585094 TRANS_AMT NUMBER(15,2) TRANS_AMT 13585719 PRICE NUMBER(15,2) PRICE6 rows selected.我们来看看另一项修改的效果 将 TRANS_AMT 列重命名为 TOT_AMT：SQL alter table trans rename column trans_amt to tot_amt;Table altered.Checking the column mapping table:SQL select * from SYS_FBA_DDL_COLMAP_79410; STARTSCN ENDSCN XID O COLUMN_NAM TYPE HISTORICAL - - - - - - - 13585094 TRANS_ID NUMBER TRANS_ID 13585094 TRANS_DT DATE TRANS_DT 13585094 PRODUCT_ID NUMBER(10) PRODUCT_ID 13585094 QUANTITY NUMBER(5) QUANTITY 13585094 13587833 TOT_AMT NUMBER(15,2) TRANS_AMT 13585719 PRICE NUMBER(15,2) PRICE 13587833 TOT_AMT NUMBER(15,2) TOT_AMT7 rows selected.可以看到，新行显示了列重命名时使用了哪个 SCN。TOT_AMT 列的历史名称显示为 TRANS_AMT。您仍然无法删除启用了闪回的表。如果删除了这样的表，它可能就不再相关了。不过，一种常见的操作可能是更改表交换分区。如何实现？临时解除关联该表，随后再重新关联：SQL begin 2 dbms_flashback_archive.disassociate_fba(ARUP,TRANS); 3 end; 4 /PL/SQL procedure successfully completed.PL/SQL 过程成功完成。操作完成之后，您可以重新关联：SQL begin 2 dbms_flashback_archive.reassociate_fba(ARUP,TRANS); 3 end; 4 /PL/SQL procedure successfully completed.这样，就不必再为常规数据库操作中常常发生的 DDL 临时挂起表的闪回存档。管理闪回存档您 可以在一个存档中添加多个表空间。反过来，您也可以从一个存档中删除表空间。如果您打算使用已经具有其他用户数据的表空间，则存在闪存数据存档数据将表空 间挤满的风险，从而没有空间供用户数据使用。为了降低这种风险，您可以设置存档在表空间内可以占用的空间定额。您可以通过以下语句设置定额：alter flashback archive near_term modify tablespace far_near_term quota 10M;您可以通过查询字典视图查看哪些表开启了闪回数据存档： SQL select * from user_flashback_archived_tables;TABLE_NAME OWNER_NAME- -FLASHBACK_ARCHIVE_NAME-TRANS ARUPNEAR_