来自农村的老实娃
分类： Oracle
2011-08-04 11:16:49
学习如何通过收回具体的事务及其相关项来识别并回滚时间。
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.
在 Enterprise Manager 屏幕中，从 Database 主页进入 Availability 选项卡。
您可以输入具体的时间范围或 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 的新增内容。它用来向该过程传递一系列事务标识符。
如果您一直使用 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.
假设您需要记录表 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;
在第 2 版中，FDA 功能进行了扩展，包括 DDL 操作，如添加/删除/重命名/修改列、分区或子分区删除/截断、截断表、重命名表以及添加/删除/重命名约束。执行这些 DDL 操作时，历史表将自动更新。无需人工干预。
让我们看一看它的作用方式。我们将需要为某个表启用闪回。首先，我们要创建一个表空间来保存闪回数据（历史表），然后在该表空间内创建一个闪回存档：
SQL> create tablespace fda datafile '+DATA' size 100M; Tablespace created. SQL> create flashback archive near_term tablespace fda retention 1 month Flashback archive created.
现在我们来创建一个示例表。
SQL> conn arup/arup
Connected.
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 TABLE SYS_FBA_HIST_79410 TABLE SYS_FBA_TCRV_79410 TABLE TRANS 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 I AAATYyAAEAAAATkAAA 13585472 05001B000C440000 U
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) PRICE 6 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_AMT 7 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 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 编写，因此对性能的影响较小。
最后，闪回数据存档可定期自动清除。审计线索必须手动进行维护。
闪回数据存档具有多种便捷的用途。这里给出部分用途：
错误发生了，但是现在您可以自信地断定，您能够识别引起错误的具体更改，而且您拥有通过事务收回完全回滚这些错误的工具。但是，您的方法不再局限于从存档的和在线的重做日志中挖掘更改，更改已经永久地记录在了闪回存档中。现在，只需通过几个命令就可以针对任何实际目的在闪回恢复区审计更改了