ログマイナーに関する検証その６

＜ログマイナに関する検証その六＞ペンネームチョビひげ

— そうなのね!?SQL_REDO —

前回はテーブルの作成から削除するまでのアーカイブログ・ファイルから、
SQL_REDO列の値を取得し、ドロップしたテーブルを復旧する為のSQL文を取得した。

今回は前回取得したSQL文を使用して、実際にテーブルの復旧に挑戦したい。
テーブルを作成し、取得したSQL文を実行してみよう。

# テーブルを作成する

SQL> create table ZURA (ID number,
  2         name varchar(100),
  3         job  varchar(100));

# 取得したSQL文を実行しよう！

SQL> @redo.lst

1 row created.
1 row created.
　　　・
　　　・
　　　・
　　　・
update "SCOTT"."ZURA" set "NAME" = 'kinta2559' where ROWID = 'AAAGZLAAFAAASLSAAh'
               *
ERROR at line 1:
ORA-01410: invalid ROWID

！！
……. そうです。
テーブルをDROPした事によりデータのDELTE処理が行なわれ、テーブルを再作成
後にデータをINSERTしても、ROWIDが変わっている為、ROWID指定のUPDATE文な
どは使用出来ないのです。

結論：8iではテーブルをドロップした場合、ログ・マイナーでの復旧は出来ない。

では、試しに9iのログマイナーの機能ではDROPしてテーブルを復旧できないかを
検証して見よう。ちなみに、9iでは以下の制限はあるがDDL文をサポートしている。

9iでサポートされていない操作対象
・データ型LONGとLOB
・単純な抽象データ型とネストした抽象データ型
・コレクション
・オブジェクト参照
・索引構成表

では、9iで8iの時と同じ手順でv$logmnr_contents表を作成し、UPDATE文を見て
みよう。（8iのアーカイブログ・ファイルとディクショナリを9iのログ・マイ
ナーで使用する）

【9i】

update "SCOTT"."ZURA" set "NAME" = 'kinta2559' where "NAME" = 'momotaro7677'
and ROWID = 'AAAGZLAAAAAAAAAAAA';

【8i】

update "SCOTT"."ZURA" set "NAME" = 'kinta2559' where ROWID = 'AAAGZLAAFAAASLSAAh'

9iのUPDATE文もROWID指定のため、ドロップされたテーブルの復旧は不可能である。
ただし、8iとは違いWHERE句で更新対象の列値を指定している。
WHEREで指定されるのはあくまで更新対象の列値であって更新対象行を特定する
ものではない。

また、8iのアーカイブログ・ファイルとディクショナリを9iで分析した結果、
ROWIDの変換が正常に行なわれていなかった。
原因は分からないが、いずれにしても今回の検証ではドロップしたテーブルをロ
グ・マイナーで復旧することは出来なかった。

9iのログ・マイナーの機能についてもう少し詳しく見てみたい。
先程、SQL_REDO列に表示されるSQL文のWHERE句にROWID以外の条件が表示された
が、更新対象行を特定するものでないと書いた。

しかし、実は9iからの新機能であるサプリメンタル・ロギングを使用する事によ
り、更新対象を特定することが可能なのである。

サプリメンタル・ロギングの設定はデータベースレベルとテーブルレベルの両方
で行なえる。サプリメンタル・ロギングの設定を行った場合と、行なわない場合
のSQL_REDO列の違いを見てみよう。

ちなみにこの表（TEST9）は列（COL1、COL2）が複合主キーになっている。
複合主キーに設定してない列をWHERE句の条件、及び更新対象にして、UPDATE処
理を行ない、複合主キーがWHERE句の条件に出てくるかを確認してみよう。

# 以下のSQL文を発行（WHERE句の条件、更新対象に複合主キーを含まない）

SQL> update test9 set col4='change999' where col3=999;

# サプリメンタル・ロギングの設定をしない場合のSQL_REDO列

SQL_REDO
----------------------------------------------------------------
update "SCOTT"."TEST9" set "COL4" = 'change999'
where "COL4" = 'ora999' and ROWID = 'AAAIeJAAIAAAAFPAAv';

# データベースレベルでサプリメンタル・ロギングの設定を行なう

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
  2  (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

# サプリメンタル・ロギングの設定をした場合のSQL_REDO列

SQL_REDO
----------------------------------------------------------------
update "SCOTT"."TEST9" set "COL4" = 'change999'
where "COL1" = '999' and "COL2" = '1998'
and "COL4" = 'ora999' and ROWID = 'AAAIeJAAIAAAAFPAAv';

上記のサプリメンタル・ロギングの機能を使用することにより、9iの環境では、
以下の手順でDROPしたテーブルを復旧出来る事を確認した。

1）前回（ログマイナに関する検証  その五）と同様の手順で
　v$logmnr_contents表からSQL_REDO列のSQL文を取得。
2）取得したSQL文のWHERE句のROWIDの条件を取り除く。
3）テーブルを作成し取得したSQL文を実行。

最後に9iでテーブルのCREATE、INSERT、UPDATE、ALTER TABLE、 UPDATE、DROPを
行なった際のSQL_REDO列の一部を見てDDL文に対応している事を確認してみよう。

※今回はテーブル作成後、テーブル変更後の2回ディクショナリファイルを作成
　（REDOログ・ファイルに抽出　9iの新機能）することで、
　SQL_REDO列を得ることが出来た。
　
Oracleのマニュアルを見ると、ログ・マイナー起動時にDDL_DICT_TRACKINGオ
  プション（9iの新機能）を使用して、DDL文をトラッキングする事で、2回ディ
  クショナリを作成しなくてもALTER TABLEなどのDDLイベントを検出し、その表
  に対するDML操作を正常に分析することが出来るようである。
しかし、今回の環境(solaris7,oracle9.0.1.0.0)ではうまく取得する事は出来
  なかった。

以下が取得したSQL文である。DDL文が取得出来ているの分かると思う。

SQL_REDO
----------------------------------------------------------------
create table seba(test number,oraoraora number);
insert into "SCOTT"."SEBA"("TEST","ORAORAORA") values ('1','1');
delete from "SCOTT"."SEBA" where ROWID = 'AAAH+HAAIAAAACiAAA';
rollback;
insert into "SCOTT"."SEBA"("TEST","ORAORAORA") values ('2','2');
alter table seba drop(test);
insert into "SCOTT"."SEBA"("ORAORAORA") values ('1');

”テーブルをドロップした時の復旧”に焦点を当ててログ・マイナーの機能を検
証した。8iではドロップしたテーブルを復旧することは出来なかったが、9iでは
ある前提条件を満たせばDROPしたテーブルを復旧出来ることを
確認することが出来た。

ただし、通常ログ・マイナーはドロップしたテーブルを復旧するものではない。
今回復旧した際の前提条件もかなり厳しい。

データの整合性などを考えると、ログ・マイナーではユーザの誤った操作やアプ
リケーションエラーの開始時のSCNを調べるに留めておいて、他のしかるべきリ
カバリ手順を取るのが賢明である。

行の削除などの場合は、9iからの新機能であるフラッシュバック問い合わせを用
いて簡単に復旧できるかもしれない。

次回からは新しくなったOracle9iの検証を行う予定である。

以上、冬の海は寒そうな茅ヶ崎にて

ログマイナーに関する検証 その６