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);
- LogMinerを起動する
- DICT_FROM_ONLINE_CATALOG・・データディクショナリをデータベースのものを使用する
- COMMITTED_DATA_ONLY・・コミットされたトランザクションのみ
- NO_ROWID_IN_STMT ・・・ROWIDを追記しない
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の内容取得
| 時系列 | 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());
