ログマイナーを活用した内部処理の検証・出力方法～Oracle text のcreate indexを例に～[Oracle Database or GoldenGate Advent Calendar 2018 Day 15]

このエントリは Oracle Database or GoldenGate Advent Calendar 2018 の Day 15 の記事となります。

先日は、moritaxp_oironさんの、CDBフリート管理でした。
PDBが4096を超えるケース…
遠大な世界に感じますね…

それはさて置き、自分は保守運用・ヘルプデスクあがりとして、私は調査方法についての記事を書きたいと思います。

目的

ログマイナーを使用して、oracle text のcreate index文の内部処理を表示させ、 ORA-01031の原因を確認する。

※一応、以下のMOS記事にはcrate indexが必要となる旨の記載があります。が、説明するには足らずでした。
DRG-50857 and ORA-01031 when Creating an Oracle Text Index and Privileges Granted through CONNECT Role (Doc ID 734064.1)

Creation Of A CTXCAT Index Fails With DRG-50857 and ORA-1031 (Doc ID 779535.1)

使用環境

Oracle Database 18.3
Oracle Linux 7
CDB環境
使用PDB:orcl
実行対象テーブル:HR.TEST
実行ユーザ:HRUSER
作成テーブル名:HR.TEST_IDX

※導入方法等、詳しくはこちらをご確認いただければ…同じものを使っております。
OTN の VirtualBoxイメージで Oracle DB 18c環境を楽々構築【Oracle Database or GoldenGate Advent Calendar 2018 Day 5】

事前準備

1.ログマイナーの使用準備
また、アーカイブログモードが有効化されていない場合は、有効化したうえで、
以下のコマンドでサプリメンタルロギングを有効化します。

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA ALL COLUMNS;

2.oracle textのcreate indexの実行準備
今回は以下の手順を参考に実行しました。
プリファレンス※の作成　まで実行します。
Oracle Text について | NTTデータ先端技術株式会社

3.対象SQLの実行
以下のクエリを管理ユーザでログインの後実行し、対象SQLのみを特定のREDOログに記録します。

--html出力設定
set markup html on spool on
spool create_index_log.html
set echo on

--前回作成索引の削除
alter session set container = orcl;
connect hr/oracle
drop index hr.TEST_IDX;

--現在の権限確認
connect sys/oracle as sysdba
alter session set container = cdb$root;

select grantee,privilege from cdb_sys_privs
where grantee in ('HR','HRUSER')
order by grantee,privilege;

select grantee,table_name,type from cdb_tab_privs
where grantee in ('HR','HRUSER')
order by grantee,table_name,type;

select grantee,granted_role from cdb_role_privs
where grantee in ('HR','HRUSER')
order by  grantee,granted_role;

--余計なREDOログの排除、実行直前のREDOログの状況確認
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
select GROUP#,STATUS,TO_char(FIRST_TIME,'YYYY-DD-MM hh:mi:ss')from v$log;
alter system flush buffer_cache;
alter system flush shared_pool;

alter session set container = orcl;
connect hruser/hruser

select TO_CHAR(sysdate,'YYYY-DD-MM hh:mi:ss') "before create index" from dual;


--対象コマンドの実行
create index hr.TEST_IDX on hr.TEST(IDX_TEXT)
indextype is ctxsys.context
parameters('LEXER t_lexer_pref SYNC(MANUAL)');

--対象SQLの実行結果影響のみを特定のREDOログに閉じ込め、実行後のREDOログの状態を確認
select TO_CHAR(sysdate,'YYYY-DD-MM hh:mi:ss') "after create index" from dual;

connect sys/oracle as sysdba
alter session set container = cdb$root;

alter system switch logfile;
select GROUP#,STATUS,TO_char(FIRST_TIME,'YYYY-DD-MM hh:mi:ss')from v$log;

spool off
set markup html off

4.記録したREDOログの確認
以下のクエリを実行し、REDOログに記録されている、
「実際にDBに対して実行されたSQL」を確認します。

alter session set container = cdb$root;

--html出力設定
set markup html on spool on 
spool output_log.html  
set echo on 

--対象REDOログの設定←前のSQLの実行結果で出力されたv$logの出力結果のうち
--TO_char(FIRST_TIME,'YYYY-DD-MM hh:mi:ss')の部分を比較し、redo**.logの部分を判断   
execute sys.dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ORCLCDB/redo01.log',options=>dbms_logmnr.new);   
execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);    

--ログマイナー出力    
select  
    rownum,
    to_char(timestamp,'YYYY-DD-MM hh:mi:ss') "TIMESTAMP",
    to_char(start_timestamp,'YYYY-DD-MM hh:mi:ss') "START_TIMESTAMP",
    operation,
    tx_name,
    seg_owner,
    seg_name,
    table_name,
    username,
    sql_redo
from v$logmnr_contents; 

select  
    rownum,
    to_char(timestamp,'YYYY-DD-MM hh:mi:ss') "TIMESTAMP",
    operation,
    tx_name,
    seg_owner,
    seg_name,
    table_name,
    username,
    sql_redo
from v$logmnr_contents 
where seg_owner = 'HR';

spool OFF   
set markup html off

5.内部動作の検証
4.で実行したクエリの結果を確認すると、以下のリンク先のようにcreate index が実行できるようになり、内部処理ではDR$TEST_IDX$** テーブルに対してcreate tableを実行していることがわかります。

f:id:ectogre:20181215044030p:plain — 実行結果抜粋

出力結果全文htmlファイル形式

※リンク先の下部に、HRセグメントに対する出力結果のみでまとめてあるため、そちらが見やすくておすすめです。

原因不明のORA-01031やクエリの失敗には、このような調査方法もありますよということでここはひとつ。
ログマイナーでもだめならトレースの取得ですかね？
(正直、トレースの取得はエンドユーザ様に説明がしにくいので…)

その他参考資料

Database Utilities　22 REDOログ・ファイル分析のためのLogMinerの使用

(注意)

この処理で確認できる内部処理は、手動で実行するとDBの破損の危険性があるのでくれぐれも直接実行しないでください。とのことです。 https://docs.oracle.com/cd/E96517_01/sutil/oracle-logminer-utility.html#GUID-BA4A515F-D694-4A88-AA34-97DDD421EA39

明日はs4r_agentさんのOracle 18c XEに関する記事となります。

歩苦求路の備忘録　（旧:奇人な鬼神）

お酒、特にスピリタスについて備忘のためにつらつらと。

ログマイナーを活用した内部処理の検証・出力方法～Oracle text のcreate indexを例に～[Oracle Database or GoldenGate Advent Calendar 2018 Day 15]

目的

使用環境

事前準備

その他参考資料

(注意)