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

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

— 探してみようSQL_REDO情報 —

表（ZURA）を誤ってドロップしてしまった！！

では、今回はログ・マイナーでドロップしたテーブルの復旧に挑戦してみよう。
以下の前提で行なう。

1.ドロップしたテーブルが以下に該当しない
　　連鎖行
　　索引構成表
　　クラスタ化表／索引
　　非スカラー型
　　ダイレクト・パス・インサート
　　DDL文(alter table などを実行していない）

2.ドロップしたテーブルを作成した時点からのREDOログ・ファイル（アーカイブ
　ログ・ファイル）がすべて存在する。
　
3.復旧対象テーブルの、作成後かつドロップ前、に作成したディクショナリ
　ファイルがある。

以上、結構厳しい条件がないとだめなんです、これが。

まずは、テーブルを作成した時点のアーカイブログ・ファイルを見つけなくては
ならない。

アーカイブログをとっているマシン環境にもよるが、テーブルを作成した日付が
分かれば、どのアーカイブログ・ファイルからログ・マイナーに適用すれば
良いかが分かると思う。

テーブルを作成した日付はOBJ$表のCTIME列で確認することが出来る。

ただし、テーブルをドロップした時点で、OBJ$表から
ドロップしたテーブルに該当するレコードは削除される。

しかし、OBJ＄表を更新するSQL文をログ・マイナーで確認することが出来るので、
以下の手順でテーブルを作成した日付情報を
V$LOGMNR_CONTENTS表から検索する。

ちなみにアーカイブログ・ファイルの名前がちょっと分かりづらいが、
1_[連番].dbfである。
また、冒頭で述べたとおりドロップしたテーブルはZURAである。

# ログスイッチを発生させる

SQL> alter system switch logfile;

# 最新のアーカイブログ・ファイル（1_616.dbf）の登録

SQL> exec dbms_logmnr.add_logfile(options=>dbms_logmnr.new,
logfilename=>'../export/home/ora817/OraHome1/dbs/arch/1_616.dbf')

# logmnr 取得（ディクショナリファイル”ora817dict.ora”はドロップ前に作成済み）

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'../tmp/ora817dict.ora')

# 以下のSQL文でV$LOGMNR_CONTENTSを検索

SQL> select scn, timestamp, log_id, data_obj#, seg_name, sql_redo
  2  from v$logmnr_contents
  3  where seg_name='OBJ'
  4 and sql_redo like 'delete%ZURA%';

SCN TIMESTAMP LOG_ID DATA_OBJ# SEG_NAME SQL_REDO
-------- ---------------- ------- ---------- ---------- ------------------
1024912 2001/11/5 20:28 616 18 OBJ$ delete from "SYS"."OBJ$"

--------------------------------------------------------------------------
where "OBJ#" = 26187 and "DATAOBJ#" = 26187 and "OWNER#" = 49 
and "NAME" = 'ZURA' and "NAMESPACE" = 1 and "SUBNAME" IS NULL and "TYPE#" = 2 
and "CTIME" = TO_DATE('05-NOV-2001 19:54:12', 'DD-MON-YYYY HH24:MI:SS') 
and "MTIME" = TO_DATE('05-NOV-2001 19:54:12', 'DD-MON-YYYY HH24:MI:SS') 
and "STIME" = TO_DATE('05-NOV-2001 19:54:12', 'DD-MON-YYYY HH24:MI:SS') 
and "STATUS" = 1 and "REMOTEOWNER" IS NULL and "LINKNAME" IS NULL and "FLAGS" = 0 
and "OID$" IS NULL and "SPARE1" = 6 and ROWID = 'AAAAASAABAAAdEmAAX';

更新対象オブジェクトがOBJ$（seg_name=’OBJ$’）で、かつ、
SQL_REDOでテーブルZURAを削除（sql_redo like ‘delete%ZURA%’）
しているレコードを検索している。

上記の検索結果のSQL_REDO列のSQL文を見ると、
WHERE句に以下のような条件がある。

“CTIME” = TO_DATE(’05-NOV-2001 19:54:12’, ‘DD-MON-YYYY HH24:MI:SS’)

よって、テーブルの作成した時間は、2001/11/5（19:54）である。
ちなみにテーブルを削除した時間は、TIMESTAMP列の
2001/11/5 (20:28)である。

SQL_REDO列に”OWNER#” = 49 とあるので、
念のためオブジェクトの所有者も確認しておこう。

# テーブルの所有者がSCOTTであることを確認

SQL> select username, user_id from dba_users
  2  where user_id = 49;

USERNAME           USER_ID
--------------- ----------
SCOTT                   49

それでは、調べた時間に近い時に作成されたアーカイブログ・ファイルを
いくつかログ・マイナーに登録し、テーブル作成時の
ログ番号とSCNを特定しよう。

ログ・マイナーには先程調べた時間 ”2001/11/5（19:54）”以降の日時の
3つのアーカイブログ・ファイルを登録する。

# アーカイブログ・ファイル（3つ）の登録

SQL> exec dbms_logmnr.add_logfile(options=>dbms_logmnr.new,
logfilename=>'../export/home/ora817/OraHome1/dbs/arch/1_588.dbf')

SQL> exec dbms_logmnr.add_logfile(options=>dbms_logmnr.addfile,
logfilename=>'../export/home/ora817/OraHome1/dbs/arch/1_589.dbf')

SQL> exec dbms_logmnr.add_logfile(options=>dbms_logmnr.addfile,
logfilename=>'../export/home/ora817/OraHome1/dbs/arch/1_590.dbf')

# logmnr 取得

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'../tmp/ora817dict.ora')

# V$LOGMNR_CONTENTS表の検索
SQL> select scn, timestamp, log_id, seg_name, sql_redo
2 from v$logmnr_contents
3 where seg_name=’OBJ$’ and sql_redo like ‘insert%26187%’;

SCN TIMESTAMP LOG_ID SEG_NAME SQL_REDO
——– —————- ——- ———- ———————–
1001513 2001/11/5 19:54 588 OBJ$ insert into “SYS”.”OBJ$”

——————————————————————–
(“OBJ#”,”DATAOBJ#”,”OWNER#”,”NAME”,”NAMESPACE”,”SUBNAME”,”TYPE#”
,”CTIME”,”MTIME”,”STIME”,”STATUS”,”REMOTEOWNER”,”LINKNAME”,”FLAGS”
,”OID$”,”SPARE1″) values (26187,26187,49,’ZURA’,1,NULL,2
,TO_DATE(’05-NOV-2001 19:54:12′, ‘DD-MON-YYYY HH24:MI:SS’)
,TO_DATE(’05-NOV-2001 19:54:12′, ‘DD-MON-YYYY HH24:MI:SS’)
,TO_DATE(’05-NOV-2001 19:54:12′, ‘DD-MON-YYYY HH24:MI:SS’)
,1,NULL,NULL,0,NULL,6);
[/sql]

更新対象オブジェクトがOBJ$（seg_name=’OBJ$’）で、かつ、
SQL_REDOでOBJ$表にオブジェクト番号”26187”をインサートしている
（sql_redo like ‘insert%26187%’）レコードを検索している。

検索されたレコードを見ると、
アーカイブログ・ファイルの番号は588で、SCNは1024912である。
これがテーブル（ZURA）が作成された時点の情報である。

先程検索したレコードからテーブルがドロップされた時点のログ番号と
SCNが分かる。

SCN      TIMESTAMP        LOG_ID  DATA_OBJ#  SEG_NAME   SQL_REDO
-------- ---------------- ------- ---------- ---------- ------------------
1024912  2001/11/5 20:28  616     18         OBJ$       delete from "SYS"."OBJ$"

これでログ・マイナーに登録するべきアーカイブログ・ファイル番号の情報が
取得出来た。

アーカイブログ・ファイル：1_588.dbf　～　1_616.dbf
SCN 　　　　　　　　　　：1001513　～　1024912

では、上記のアーカイブログ・ファイルを使用して、
SQL_REDO文を取得する。

# 1_588.dbf　～　1_616.dbfのアーカイブログ・ファイルを登録

SQL> begin
  2  dbms_logmnr.add_logfile(options=>dbms_logmnr.new
     ,logfilename=>'../export/home/ora817/OraHome1/dbs/arch/1_588.dbf');
  3  for x in 589 .. 616 loop
  4  dbms_logmnr.add_logfile(options=>dbms_logmnr.addfile
     ,logfilename=>'../export/home/ora817/OraHome1/dbs/arch/1_'||x||'.dbf');
  5  end loop;
  6  end;
  7  /

# logmnr 取得（SCNを指定）

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'../tmp/ora817dict.ora'
,startscn=>1001513,endscn=&gt;1024912)

# 以下のSQL文でSQL_REDO文を取得し、ファイルに保存（redo.lst)

SQL> spool /tmp/redo
SQL> select sql_redo from v$logmnr_contents
  2  where data_obj#=26187 and operation !='INTERNAL' order by scn;
SQL> spool off

以上で、テーブルを復旧する為のSQL文が抽出されたはずだ。
次回は上記で取得したSQL文を使用してドロップしたテーブルの
復旧に挑戦したい。

以上、海はちょっと寒い茅ヶ崎にて

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