11gロゴ

Oracle Database 11g:
DBAと開発者のための主要な新機能

著者:Arup Nanda Oracle ACEディレクタ

LogMinerとフラッシュバック・データ・アーカイブを利用したトランザクション管理

特定のトランザクションとその依存トランザクションをバック・アウトし、ロールバックする方法について説明します。

ダウンロード Oracle Database 11gをダウンロードする

Oracle Enterprise ManagerのLogMinerインタフェース

LogMinerは見落とされやすいものの、Oracleデータベースの強力なツールです。 LogMinerは、DML文(トランザクションの引き金となったオリジナルのSQLや、そのトランザクションを取り消すことのできるSQLまでも)をREDOログ・ファイルから抽出するために使用されます(LogMinerの概要と機能については、『Oracle Magazine』に掲載された記事 "Mining for Clues"を参照してください)。 この強力なツールにはこれまで使いやすいインタフェースが存在しなかったため、一般に過小評価されていました。しかし、Oracle Database 11gのOracle Enterprise Managerでは、LogMinerを使用してREDOログからトランザクションを抽出するためのグラフィカル・インタフェースが用意されたため、トランザクションを調べてロールバックするツールをとても簡単に使用できるようになりました(注:必要に応じて、旧バージョンと同様に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.
 
それぞれの文の後に、COMMIT文が続いていることに注目してください。これは、各文がトランザクションであることを表しています。 Oracle Database 11g Database ControlのLogMinerで、トランザクションをどのように調べることができるのかを確認してみましょう。

Enterprise Manager画面で、Databaseホームページから Availabilityタブへ移動します。

図1

Manageの下にある「 View and Manage Transactions」をクリックします。 これで、以下の図のようにLogMinerのメイン・インタフェースが表示されます。

図2

トランザクションを検索するための時間およびSCNの範囲を入力できます。 上図では、 Query Time Rangeに検索対象時間の範囲が入力されています。 すべてのDML実行がユーザーSCOTTのトランザクションであるため、 Query FilterにはSCOTTだけが入力されています。 Advanced Queryセクションには、任意の追加フィルタを入力できます。 すべてのフィールドへの入力が完了したら、「 Continue」をクリックします。

これで、ログ・マイニング・プロセスが開始されます。このプロセスでは、REDOログ(必要であればオンラインREDOログとアーカイブされたREDOログの両方)からユーザーSCOTTによって発行されたトランザクションが検索されます。 プロセスが完了したら、結果画面が表示されます。

結果画面の上部は、下図のようになります。

図3

この検索結果は、SCOTTによるトランザクションが2件見つかったことを示しています。また、これらのトランザクションによって影響を受けたレコードが2件あります。

画面の下部には、それらのトランザクションの詳細が表示されます。 以下の図はその画面の一部です。 トランザクションが 1 ins("1 件のInsert文")として表示されています。 最左列には、トランザクションを一意に識別する番号である、トランザクションID(XID)が表示されています。

図4

トランザクションIDをクリックすると、下図の画面のようにトランザクションの詳細が表示されます。

図5

このように、Database Controlを使用して、トランザクションを検索および特定できます。 検索で見つかったすべてのトランザクションを確認したい場合、「 Previous Transaction」ボタンと「 Next Transaction」ボタンをクリックします。

ユースケース

この機能には、いくつかの用途があります。 もっとも重要な用途は、"誰"が"何"をしたのかを把握することです。 パフォーマンス上の理由から監査を有効化していない場合、あるいは単に監査を記録していない場合は、オンラインREDOログとアーカイブされたREDOログのマイニングを実行して、LogMinerインタフェースでその手掛かりを探すことができます。 検索画面では、 Query Filterにある Advanced Queryフィールドに、追加フィルタリング条件を入力できます。

RES_ID = 100002のレコードが挿入、削除、または更新されたトランザクションを検索するには、以下の図のように、dbms_logmnrパッケージの関数column_presentを使用して、REDOストリームの特定値を探します。

図6

この関数では、SCOTTスキーマのRES表で、RES_ID列の100002に関連するすべてのトランザクションが抽出されます。

また、この機能を使用して、データベースに対して発行されたDDLコマンドを見つけることもできます。 DDLコマンドを見つけるには、 Query Filterセクションにあるラジオ・ボタン「 View DDL Only」を選択します。

選択したトランザクションのバック・アウト

トランザクションを調べたら、それを何に使いますか。誤って作成されたトランザクションを取り消したいということもあるでしょう。 トランザクションを取り消すのはとても簡単です。トランザクションが挿入である場合には、そのトランザクションを削除するだけです。トランザクションが更新である場合には、UNDOによって行が古い値に書き換えられます。

ただし、例の中で使用されているトランザクションを注意して見てください。 最初のトランザクションでは、行が挿入されます。 2番目のトランザクションでは、その前に挿入された行が更新されます。3番目のトランザクションでは、挿入された行が削除されます。 最初のトランザクション(挿入)がバック・アウトする対象のトランザクションですが、ここで問題があります。その行は後続トランザクションによってすでに削除されています。この場合、UNDOトランザクションはどうなるのでしょうか。

ここではOracle Database 11gの依存トランザクション照会機能が役立ちます。 「 Flashback Transaction」をクリックします。 検索後には、下図のような画面が表示されます。

図7

この画面には、依存トランザクション、更新、および削除が表示されます。 トランザクションをバック・アウトする際には、依存トランザクションもバック・アウトすることができます。 バック・アウトするには、下図のリストから「 Cascade」ラジオ・ボタンを選択し、「 OK」をクリックします。

図8

これで、バック・アウトするさまざまなトランザクションが表示されます。トランザクションIDをクリックすると、特定のトランザクションを取り消すためにOracleデータベースが発行するSQL文が表示されます。

図9

たとえば、挿入を取り消すには、上図のようにOracleデータベースが削除を発行する必要があります。 次(直下)のトランザクションをクリックすると、そのトランザクションをバック・アウトするためにしなければならないことの詳細が表示されます。

図10

内容を理解してから、「 Submit」をクリックすると、すべてのトランザクションが一括でロールバックされます。 これがトランザクションとその依存トランザクションをもっともきれいに取り消す方法です。

コマンドライン・インタフェース

Oracle Enterprise Managerを利用できない場合、あるいはスクリプトを使用してバック・アウトする場合を考えてみます。 Oracle Database 10g にもあるパッケージDBMS_FLASHBACKには、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 Database 11g の新しい機能です。 プロシージャへ一連のトランザクションIDを渡します。

LogMinerのほかの改善点

データ型としてXMLTypeを使用していて、さらにOracle Database 11gでXMLTypeを使用する理由があるユーザーにとって、LogMinerでXMLデータもマイニングされれば、これは非常に便利な機能となります。 XMLデータは、SQL_REDO列とSQL_UNDO列の両方に表示されます。

LogMinerの起動中にSKIP_CORRUPTIONと呼ばれるオプションを設定すると、REDOログの破損しているブロックをスキップできます。 これで、部分的に破損しているREDOログからでも、有効なデータをまだ復旧できます。 改善された構文は、以下のとおり使用できます。

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

フラッシュバック・データ・アーカイブ

Oracle9i Database Release 2では、フラッシュバック問合せが導入されました。フラッシュバック問合せとは、データの変更後に変更前のバージョンを選択できる、いわゆるタイム・マシンです。 たとえば、値を100から200に変更して、変更をコミットした後でも、2分前の時点の値をまだ選択できるというものです。 このフラッシュバック技術には、UNDOセグメントの変更前データが使用されていました。 Oracle Database 10g では、フラッシュバック・バージョン問合せが導入されたことにより、この機能が強化されました。フラッシュバック・バージョン問合せでは、UNDOセグメントに変更がまだ残っている限り、行に対する変更を追跡できます。

ただし、これにはやや問題がありました。 データベースがリサイクルされると、UNDOデータはクリーンアウトされ、変更前の値は消えてしまうのです。 また、データベースがリサイクルされていない場合でも、新しい変更の領域を作るために、データがUNDOセグメントからエージ・アウトされる可能性があります。

Oracle Database 11g より前のバージョンでは、フラッシュバックの動作はUNDOデータに依存していますが、UNDOデータは短期間しか利用できないため、UNDOデータを長期間使用したり、監査などの永続的な記録のために使用したりすることはできません。 その回避策として、データベースに対する変更をより永続的に記録するトリガーを書き込むという手段を取っていました。

しかし、Oracle Database 11g では、フラッシュバック・データ・アーカイブに両方の長所が生かされています。フラッシュバック・データ・アーカイブは、フラッシュバック問合せの簡単さとその機能を提供しますが、UNDOなどの一時的な格納場所には依存しないのです。 その代わり、変更はより永続的なフラッシュバック・リカバリ領域に記録されます。

例を見てみましょう(注:フラッシュバック・データ・アーカイブの自動UNDO管理を有効化する必要があります)。 まず、フラッシュバック・データ・アーカイブを以下のように作成します。

SQL> create flashback archive near_term
  2  tablespace far_near_term
  3  retention 1 month
  4  /

Flashback archive created.
                       
差し当たり、"retention(保存)"という単語の意味は気にしないでください。これについては、後で説明します(これは変更が記録される場所です)。 表領域far_near_termにアーカイブが生成されます。

TRANSと呼ばれる表に変更を記録する必要があるとします。 アーカイブに変更の記録を始めるには、TRANS表のフラッシュバック・データ・アーカイブ・ステータスを有効化するだけです。

SQL> alter table trans flashback archive near_term;

Table altered.
                       
これにより、TRANS表はフラッシュバック・データ・アーカイブ・モードになります。 TRANS表の行に対するすべての変更は、永続的に追跡されるようになります。 デモンストレーションを見てみましょう。

まず、表の特定の行を選択します。

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が常に表示されます。 特定の時点での古い値を見つけるには、以下のようにフラッシュバック問合せを使用できます。
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;

   TXN_AMT
----------
  19325.67
                       
しばらくして、UNDOデータがUNDOセグメントから消去された後に、フラッシュバック・データを再度問い合わせます。
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が返されます。 UNDOデータはなくなっているにも関わらず、このデータはどこから来たのでしょうか。

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
                       
複数のアーカイブを使用すれば、さまざまな状況下でアーカイブを工夫して活用できます。 たとえば、ホテル会社のデータベースには、予約データ1年分、支払いデータ3年分を保存しておく必要があるとします。 この場合、さまざまな保存方針で複数のアーカイブを定義し、それらのアーカイブを表に割り当てることができます。 保存方針が一律である場合には、アーカイブを1つだけ定義し、それをデフォルトに設定することができます。
alter flashback archive near_term set default;
                       
表にアーカイブが必要ない場合には、以下のとおりアーカイブを無効化できます。
alter table trans no flashback archive;
                       
このように、コードを1行も書き込むことなく、強力な変更記録システムを有効化しました。

通常の監査との違い

フラッシュバック・データ・アーカイブは通常の監査と何が違うのでしょうか。 まず、通常の監査では、audit_trailパラメータをDBまたはDB_EXTENDEDに設定する必要があり、監査証跡はSYSTEM表領域のAUD$と呼ばれる表に書き込まれます。 フラッシュバック・データ・アーカイブの場合、任意の表領域(あるいは複数の表領域、またはユーザー・データが存在する表領域の部分)に定義できます。そのため、フラッシュバック・データ・アーカイブは安価なストレージに定義できます。

次に、監査はいくらかのパフォーマンス・オーバーヘッドがある自律型トランザクションに基づいています。 フラッシュバック・データ・アーカイブは、FBDAと呼ばれる専用バックグラウンド・プロセスで書き込まれているため、パフォーマンスへの影響が少なくて済みます。

最後に、フラッシュバック・データ・アーカイブは、一定の間隔で自動的に消去できます。 監査証跡の場合は手動で維持する必要があります。

ユースケース

フラッシュバック・データ・アーカイブは多くの目的に役立ちます。 使用目的には以下のようなものがあります。

  • データがどのように変更されたのかを記録するために、監査を実施する
  • アプリケーションによって変更を取り消せるようにする(誤りの修正)
  • データがどのように変更されたのかをデバッグする
  • 一定期間後のデータ変更を禁止する規制に従う - フラッシュバック・データ・アーカイブは通常の表ではないため、一般的なユーザーはフラッシュバック・データ・アーカイブを変更できません
  • 安価なストレージに監査証跡を記録することで、より少ない費用でより長期間保存する

結論

間違いは起こるものですが、間違いの原因となった変更を特定できるようになりました。それらの間違いのすべてを、トランザクション・バック・アウトでロールバックするツールがあります。 変更部分のマイニングは、アーカイブされたREDOログとオンラインREDOログに限定されることはありません。変更はフラッシュバック・アーカイブに永続的に記録されます。 わずかなコマンドを使用するだけで、フラッシュバック・リカバリ領域にある、あらゆる実用目的を持った表の変更を監査できるようになりました。

"Oracle Database 11g :DBAと開発者のための主要な機能"ホームページに戻る
Arup Nanda Arup Nanda( arup@proligence.com)は、Oracle Databaseテクノロジのあらゆる分野で12年以上の経験を持つ優秀なOracle DBAであり、2003年の『Oracle Magazine』で"DBA of the Year"に選ばれました。 Arupは、オラクル関連のイベントの講演や雑誌の寄稿を頻繁におこなっている Oracle ACE Directorです。 また、『 RMAN Recipes for Oracle Database 11g: A Problem Solution Approach』を含む4冊の書籍を共同で執筆しています。
Left Curve
図でイメージする
Oracle DatabaseのSQL全集
Right Curve