Oracle 数据库 11g重要新特性：事务管理

Oracle 企业管理器中的 LogMiner 界面

LogMiner 是 Oracle 数据库中一个经常受到忽视但功能却非常强大的工具。它可用于从重做日志文件中提取 DML 语句 — 引发事务的初始 SQL 以及甚至能取消事务的 SQL。（有关 LogMiner 的介绍及其工作方式，请参阅我在 Oracle 杂志上发表的文章。）至今为止，这一功能强大的工具由于缺少较简单的界面而得不到普遍认可。然而，在 Oracle 数据库 11g 中， Oracle 企业管理器具有一个使用 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 数据库 11g 数据库控制中的 LogMiner 检查事务。

在企业管理器屏幕中，从数据库主页进入 Availability 选项卡。

单击 Manage 下的 View and Manage Transactions。LogMiner 主界面显示，如下图所示：

您可以输入具体的时间范围或 SCN 范围来搜索事务。在上图中，我已经在 Query Time Range 中输入了一个时间范围进行搜索。在 Query Filter 中，我只用了 SCOTT 的事务，因为其曾用来执行过所有的 DML。在 Advanced Query 部分中，您可以输入其他任何过滤器。填完所有的域后，单击 Continue。

这将激活日志挖掘进程对重做日志（在线的和存档的，必要的话）进行搜索，寻找用户 SCOTT 发布的事务。进程结束后，您将看到结果屏幕。

结果屏幕的顶端部分与下图类似：

结果表明，通过搜索找到两个由 SCOTT 发布的事务，这些事务影响了两条记录。

屏幕的底端部分显示了这些事务的详细信息。这是屏幕的局部视图。您可以看到，事务显示为 1 ins （表示“1 条插入语句”）。最左边一栏显示了事务标识符 (XID)，是唯一标识事务的编号。

如果点击事务标识符，您可以看到相应事务的详细信息，如下图所示：

如您所见，您可以使用数据库控制搜索和识别事务。单击按钮 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 数据库 11g 中 Dependent Transaction 视图特性的用途所在。单击 Flashback Transaction。经过一些搜索后，系统将显示类似下图的屏幕：

该屏幕将为您显示相关事务以及更新和删除。现在，在收回事务时，您还可以收回其依赖项。 要实现这一目的，从下面的列表中选择 Cascade 单选按钮，然后单击 OK 即可。

它会为您显示所需收回的不同事务。单击事务 ID 查看 Oracle 将执行什么 SQL 语句来撤消这个事务。

例如，要撤消插入，Oracle 必须执行删除，如上图所示。如果点击下一个事务（就在它的下面），您将看到收回下一个事务所需的详细操作：

这样，您就了解了操作构思。单击 Submit，所有这些事务都将一次性全部回滚。这是撤消事务及其依赖项最彻底的方法。

命令行界面

如果您无权访问企业管理器怎么办？或者也许您想通过脚本来完成这一切，那该怎么办？程序包 DBMS_FLASHBACK（Oracle 数据库 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;

xid_array 类型也是 Oracle 数据库 11g 的新增内容。它用来向该过程传递一系列事务标识符。

LogMiner 的其他改进

如果您一直使用 XML Type 作为数据类型，那么在 Oracle 数据库 11g 中您更有理由使用它，您会很高兴地看到 LogMiner 也可以挖掘 XML 数据。XML 数据在 SQL_REDO 和 SQL_UNDO 列中都有显示。

启动 LogMiner 时，您可以设置选项 SKIP_CORRUPTION，它会跳过重做日志中的受损块。因此，即使部分数据损坏了，您也可以回收重做日志里的有效数据。 以下是这一改进语法的用法：

begin
   dbms_logmnr.start_logmnr(
        options => dbms_logmnr.skip_corruption
   ) ;
end;

闪回数据存档

Oracle9i 数据库第 2 版以闪回查询的形式引入了众所周知的时间机器。闪回查询允许您选择更改前的数据。例如，如果您将一个值从 100 改为 200 并将其提交，即使更改已经提交了，您仍然可以选择两分钟前的值。这种技术使用了还原段中更改前的数据。在 Oracle 数据库 10g 中，这项功能通过引入闪回版本查询得到增强，您甚至可以将某一行的更改追溯到还原段所能提供的最久远的更改状态。

但是，这里存在一个小问题：数据库回收后，还原数据被清除了，随之更改前的值也消失了。即使不回收数据库，数据也可能因时间太长而退出还原段为新更改让出空间。

由于 11g 之前的闪回操作依赖于还原数据，而还原数据的可用时间短暂，您无法真正长期使用这些数据或者将其用于审计之类较永久的记录。作为变通手段，我们通过编写触发器来长期记录数据库的更改。

不过，不要感到失望。在 Oracle 数据库 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.67

SQL> update trans set txn_amt = 2000 where trans_id = 2;

1 row updated.

SQL> commit;

Commit complete.

现在，如果您选择了该行，则这一列将始终显示 2000。要查找早先某一时间点时的值，您可以使用闪回查询，如下所示：

elect txn_amt
from trans
as 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_amt
from trans
as 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 explain
SQL> 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 "ENDSCN">161508784336056 AND "ENDSCN"<=1073451 AND ("STARTSCN" IS NULL
              OR "STARTSCN"<=161508784336056))
   6 - filter("F"."STARTSCN"<=161508784336056 OR "F"."STARTSCN" IS NULL)
   7 - access("T".ROWID=("F"."RID"(+)))
   8 - filter("T"."VERSIONS_STARTSCN" IS NULL AND "T"."TRANS_ID"=2)
  10 - filter(("ENDSCN" IS NULL OR "ENDSCN">1073451) AND ("STARTSCN" IS NULL OR "STARTSCN"<1073451))

Note
-----
   - dynamic sampling used for this statement

该输出回答了我们的疑问“这个数据来自何处？”，数据来自表 SYS_FBA_HIST_68909，这是您先前为那个表定义的闪回存档中的某个位置。您可以查看这个表，但是 Oracle 不支持在那里直接查看数据。不过，我觉得您也没有必要那样做。

存档中的数据能保留多长时间呢？这就是保留期限的作用了。数据可以在该期限内一直保留。保留期限过后，当有新的数据进来时，较老的数据将被清除。您也可以自己清除数据，例如：

alter flashback archive near_term purge before scn 1234567;

管理闪回存档

您可以在一个存档中添加多个表空间。反过来，您也可以从一个存档中删除表空间。如果您打算使用已经具有其他用户数据的表空间，则存在闪存数据存档数据将表空间挤满的风险，从而没有空间供用户数据使用。为了降低这种风险，您可以设置存档在表空间内可以占用的空间定额。您可以通过以下语句设置定额：

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                          ARUP
NEAR_TERM

您可以通过查询字典视图查找存档：

sql> select * from flashback_archives;

FLASHBACK_ARCHI FLASHBACK_ARCHIVE# RETENTION_IN_DAYS  PURGE_SCN STATUS
--------------- ------------------ ----------------- ---------- -------
NEAR_TERM                        1                30    1042653
MED_TERM                         2               365    1042744
LONG_TERM                        3              1825    1042838

使用多个存档可以使您在不同情况下通过独创方式对其进行应用。例如，某酒店企业的数据库可能需要保留一年的预定信息，而需要保留三年的付款信息。那么，您可以定义多个带有不同保留策略的存档，然后将其分配给各个表。或者，如果有统一的保留策略，您可以只定义一个存档并将其设为默认选项。

alter flashback archive near_term set default;

当某个表不需要存档时，您可以通过以下命令将存档关闭：

alter table trans no flashback archive;

如您所见，您一行代码都不需要编写就实现了一个功能强大的更改记录系统。

与常规审计的区别

闪回数据存档与常规审计的区别是什么？首先，后者需要将 audit_trail 参数设置为 DB 或 DB_EXTENDED，并将审计线索写入 SYSTEM 表空间的 AUD$ 表中。闪回数据存档可在任何表空间上（或多个表空间上，甚至存储用户数据的表空间的某些部分上）进行定义，因此可以在更廉价的存储设备上进行定义。

其次，审计以自主事务为基础，会造成一些性能开销。闪回数据存档由专用后台进程 FBDA 编写，因此对性能的影响较小。

最后，闪回数据存档可定期自动清除。审计线索必须通过人工进行维护。

使用案例

闪回数据存档具有多种便捷的用途。这里给出部分用途：

• 审计以记录数据的更改方式
• 支持应用程序撤消更改（纠正错误）
• 调试数据的更改方式
• 遵守某些规定，不准数据在某段时间内出现更改。闪回数据存档不属于常规表，所以普通用户无法对其进行更改操作。
• 可在更廉价的存储设备上记录审计线索，因而能以较低的成本保留更多的信息。

结论

错误发生了，但是现在您可以自信地断定，您能够识别引起错误的具体更改，而且您拥有通过事务收回完全回滚这些错误的工具。但是，您的方法不再局限于从存档的和在线的重做日志中挖掘更改，更改已经永久地记录在了闪回存档中。现在，只需通过几个命令就可以针对任何实际目的在闪回恢复区审计更改了。