読者です 読者をやめる 読者になる 読者になる

kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

Oracle LogMinerを使用してトランザクションごとコミット順に並び替えて表示する

アーカイブログやオンラインREDOログからLogMinerを使用して、実行されたSQLを調査する際に
不要な内部SQLがあったりして確認が手間だったりする。
不要な内部SQLを取得せずにトランザクションごとにまとめて、コミットした順番で表示させる。

前提

  • サプリメンタルロギングの状態
    • 最小サプリメンタルロギングは有効化
SQL>SELECT SUPPLEMENTAL_LOG_DATA_MIN,
           SUPPLEMENTAL_LOG_DATA_PK,
           SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEME SUP SUP
-------- --- ---
YES      NO  NO
  • ログを読み込ませる
exec SYS.DBMS_LOGMNR.ADD_LOGFILE( 
        LOGFILENAME => '/orabackup/online-redo/redo01.log', 
        OPTIONS => SYS.DBMS_LOGMNR.NEW); 
exec SYS.DBMS_LOGMNR.START_LOGMNR( 
               OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG 
                        + SYS.DBMS_LOGMNR.COMMITTED_DATA_ONLY  
                        + SYS.DBMS_LOGMNR.NO_ROWID_IN_STMT);

トランザクションごとコミット順で取得

時系列 Tx1 Tx2
1 set transaction read write;
2 insert into TT values (999,999);
3 set transaction read write;
4 update TT set ID2=999 where id=1;
5 update TT set ID2=666 where id=1;
6 commit;
7 commit;

Tx2は後からトランザクションを開始しているがTx1より先にコミットしているため
Tx2、Tx1の順で取得するようにする。

set line 200
set pagesize 3000
col SQL_REDO format a40
col SCN format 99999999999999
col START_SCN format 99999999999999
col COMMIT_SCN format 99999999999999
  
SELECT
  *
FROM
  (
  SELECT
    SCN,
    MIN(START_SCN) OVER (PARTITION BY RAWTOHEX(xid)) as START_SCN,
    MAX(COMMIT_SCN) OVER (PARTITION BY RAWTOHEX(xid)) as COMMIT_SCN,
    RAWTOHEX(xid) as xid,
    SEQUENCE#,
    MIN(START_TIMESTAMP) OVER (PARTITION BY RAWTOHEX(xid)) as START_TIMESTAMP,
    MAX(COMMIT_TIMESTAMP) OVER (PARTITION BY RAWTOHEX(xid)) as COMMIT_TIMESTAMP,
    substr(SQL_REDO,0,40) SQL_REDO,
    OPERATION_CODE,
    MIN(OPERATION_CODE) OVER (PARTITION BY RAWTOHEX(xid) ) as OPE_CODE_MIN
  FROM V$LOGMNR_CONTENTS
  WHERE
    USERNAME in ('ORAUSER')                          --取得したいユーザ名を指定
    AND (SEG_OWNER <> 'SYS' or SEG_OWNER is null)    --内部SQLを取得しない
    AND (INFO not like '%INTERNAL%' or INFO is null) --内部SQLを取得しない
    AND OPERATION_CODE in (1,2,3,5,6,7,36)           --DDL、DML、START、COMMITとROLLBACKのみ取得
  )
WHERE
OPE_CODE_MIN <> 6                                    --DML,DDLがなかったトランザクションのSTARTとCOMMITを表示しない
AND (OPE_CODE_MIN <> 5 or OPERATION_CODE = 5)        --内部SQLでないDDLを取得
ORDER BY  COMMIT_SCN,xid,SEQUENCE#;
 SCN STAR COMM XID              S START_TIMESTAMP     COMMIT_TIMESTAMP    SQL_REDO                                 O O
---- ---- ---- ---------------- - ------------------- ------------------- ---------------------------------------- - -
7496 7496 7503 09000700172A0600 1 2016/02/18 14:41:27 2016/02/18 14:41:42 set transaction read write;              6 3
7496 7496 7503 09000700172A0600 2 2016/02/18 14:41:27 2016/02/18 14:41:42 update "ORAUSER"."TT" set "ID2" = '999'  3 3
7503 7496 7503 09000700172A0600 3 2016/02/18 14:41:27 2016/02/18 14:41:42 commit;                                  7 3
7493 7493 7505 0A0014002FCD0B00 1 2016/02/18 14:41:18 2016/02/18 14:41:46 set transaction read write;              6 1
7493 7493 7505 0A0014002FCD0B00 2 2016/02/18 14:41:18 2016/02/18 14:41:46 insert into "ORAUSER"."TT"("ID","ID2") v 1 1
7504 7493 7505 0A0014002FCD0B00 3 2016/02/18 14:41:18 2016/02/18 14:41:46 update "ORAUSER"."TT" set "ID2" = '666'  3 1
7505 7493 7505 0A0014002FCD0B00 4 2016/02/18 14:41:18 2016/02/18 14:41:46 commit;                                  7 1

XIDトランザクションIDで09000700172A0600がTx2、0A0014002FCD0B00がTx1。

DDLの内容取得

  • このSQLを使えばDDLの内容も時系列かつ余分な内部SQLを出力することなく確認することができる。
時系列 Tx1
1 create table TEST (id int primary key);|
2 insert into TEST values (999);
3 commit;
4 truncate table TEST;
 SCN STAR COMM XID              SE START_TIMESTAMP     COMMIT_TIMESTAMP    SQL_REDO                                 O O
---- ---- ---- ---------------- -- ------------------- ------------------- ---------------------------------------- - -
8873 8848 8878 0A00120003D60B00 14 2016/02/18 15:04:43 2016/02/18 15:04:43 create table TEST (id int primary key);  5 5
8882 8882 8883 0A0006000FD70B00  1 2016/02/18 15:04:53 2016/02/18 15:04:53 set transaction read write;              6 1
8882 8882 8883 0A0006000FD70B00  2 2016/02/18 15:04:53 2016/02/18 15:04:53 insert into "ORAUSER"."TEST"("ID") value 1 1
8883 8882 8883 0A0006000FD70B00  3 2016/02/18 15:04:53 2016/02/18 15:04:53 commit;                                  7 1
8916 8895 8924 0700070050CD0700  2 2016/02/18 15:05:00 2016/02/18 15:05:00 truncate table TEST;                     5 5

LOBは内容は出力されないが、登録時に行われる初期化EMPTY_CLOB()は拾うことはできるのでXIDから検索すれば中身の確認もできる。

insert into "ORAUSER"."LOBLOB"("ID","LL") values ('2',EMPTY_CLOB());

このSQLを使ってツール作れば調査とか楽になりそう。