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

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

— 探してみよう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=>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文を使用してドロップしたテーブルの
復旧に挑戦したい。

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