tech_memo / ORACLE / ODI

tech_memo/ORACLE

概要 †

• http://web.plus-idea.net/2013/02/oracle-bi-business-intelligence-odi-data-integrator/

構築 †

1. Oracle DB setup
   • http://web.plus-idea.net/2013/02/oraclebi-odi-step-by-stepdb-db/
2. ODI用リポジトリ作成
   • http://web.plus-idea.net/2013/02/oraclebi-odi-step-by-step-repository/
3. ODIインストール
   • http://web.plus-idea.net/2013/02/oracle-bi-odi-step-step-odi/

• Windows7 64bitへのODIインストール（番外編？）
  • http://web.plus-idea.net/2013/03/64bit-windows7-oracle-odi/

ODIのインストールの流れ (11g) †

1. DBのインストールと作成
2. 上記で作成したデータベースにODI用のリポジトリを作成
3. ODIのインストール
   • 11gと12cでインストール方法が異なる、(下記Windowsのケース)
     • 11g : setup.exe
     • 12c : javaコマンドでjarファイルを起動
       http://docs.oracle.com/middleware/12212/lcm/ODING/GUID-D5AFD830-8A7D-42CC-8C22-CE68C452CF4A.htm#INSOA371
4. ODIサンプルのインストール

ODI 12cインストール手順 (youtube) †

• https://www.youtube.com/watch?v=UMWf7A-mWYY

チュートリアル †

ODI 12c †

youtube †

• https://www.youtube.com/watch?v=lZqiJCKwz8A

チュートリアル ライブラリ(一覧) †

• https://apexapps.oracle.com/pls/apex/f?p=44785:141:3361676123256::::P141_PAGE_ID,P141_SECTION_ID:178,1317

マスターリポジトリ、ワークリポジトリ作成 + 接続 by RUC †

• http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/odi/odi_12c/odi12c_mst_wrk_repos/odi12c_mst_wrk_repos.html#overview

ODI エージェントの作成 †

• http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/odi/odi_12c/odi12c_agent/odi12c_agent.html

プロシージャ、シナリオ、スケジュール作成 †

• http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/odi/odi_12c/odi12c_proc_scenario/odi12c_proc_scenario.html

ODI 11g, 10g †

Oracle Fusion Middleware チュートリアル (ODIチュートリアル目次あり) †

• http://www.oracle.com/webfolder/technetwork/jp/obe/fusion_middleware/index.htm#t4

マスターリポジトリ、ワークリポジトリの作成と接続 †

• http://www.oracle.com/webfolder/technetwork/jp/obe/fusion_middleware/ODI/ODImaster_work_repos/ODImaster_work_repos.htm
  • 12cだと上記は古く、あまり参考にならない。以下のドキュメントがよい(11gだが）
    • https://docs.oracle.com/cd/E28389_01/integrate.1111/b62260/admin_reps.htm#BABGDGII

リレーショナルテーブルからデータ収集するODIプロシージャの作成 †

• http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/odi/odi_11g/odi_proc_crt_pop_tbl/odi_procedure_create_populate_table.htm

開発者ガイド (12c, 日本語) †

• http://docs.oracle.com/cd/E50629_01/odi/ODIDG/toc.htm

ジャーナル化とCDC設定 †

• https://docs.oracle.com/cd/E50629_01/odi/ODIDG/data_capture.htm

CDC Stream †

• Oracle Streamsを利用した、REDOログからジャーナル表への更新データの抽出

ODI 設定方法 †

• https://www.rittmanmead.com/blog/2015/07/using-streams-with-odi12c-for-oracle-to-oracle-change-data-capture/

ジャーナル対象テーブルの更新時の処理 †

構成テーブルとビュー(サンプル) †

• ジャーナル化対象テーブル
  • CON_PARTテーブル

    列名	型	制約等
    ABBR	VARCHAR2(4)	PK
    NAME	VARCHAR2(25)
    LAND_KM	NUMBER(12)

• ジャーナル表
  • J$CON_PARTテーブル

    列名	型	制約等
    WINDOW_ID	NUMBER
    ABBR	VARCHAR2(4)

• ジャーナルビュー
  • JV$CON_PART (ODIから抽出時に初めて参照可能)

    列名	型	制約等
    JRN_FLAG	VARCHAR2(1)	実表データが削除済みであれば'D'、それ以外は'I'が入る
    JRN_DATE	DATE	SYSDATE
    JRN_SUBSCRIBER	VARCHAR2(400 CHAR)	サブスクライバ名
    ABBR	VARCHAR2(4)	ジャーナル対象テーブルの更新後の値
    NAME	VARCHAR2(25)
    LAND_KM	NUMBER(12)

    • DDL

        CREATE OR REPLACE FORCE EDITIONABLE VIEW "HR"."JV$CON_PART" ("JRN_FLAG", "JRN_DATE", "JRN_SUBSCRIBER", "ABBR", "NAME", "LAND_KM") AS
        select
             decode(TARG.ROWID, null, 'D', 'I')   AS  JRN_FLAG,
             sysdate      AS  JRN_DATE,
             JRN.CDC_SUBSCRIBER       AS  JRN_SUBSCRIBER,
             JRN.ABBR     AS  ABBR
             ,TARG.NAME   AS  NAME,
             TARG.LAND_KM     AS  LAND_KM
        from
             (
                 select
                     J.ABBR   AS  ABBR,
                     SUB.CDC_SUBSCRIBER  AS  CDC_SUBSCRIBER
                 from
                     HR.J$CON_PART       J,
                     HR.SNP_CDC_SUBS     SUB
                 where
                     SUB.CDC_SET_NAME    = 'HR.CONCON'
                     and J.WINDOW_ID > SUB.MIN_WINDOW_ID
                     and J.WINDOW_ID <= SUB.MAX_WINDOW_ID
                 group by
                     J.ABBR,
                     SUB.CDC_SUBSCRIBER
             ) JRN,
             HR.CON_PART    TARG
        where
             JRN.ABBR    = TARG.ABBR (+);

• JV$DCON_PART (ODIから抽出前に参照可能)

  列名	型	制約等
  JRN_FLAG	VARCHAR2(1)	実表データが削除済みであれば'D'、それ以外は'I'が入る
  JRN_DATE	DATE	SYSDATE
  JRN_SUBSCRIBER	VARCHAR2(400 CHAR)	SNP_CDC_SUBS表のサブスクライバ名
  ABBR	VARCHAR2(4)	ジャーナル対象テーブルの更新後の値
  NAME	VARCHAR2(25)
  LAND_KM	NUMBER(12)

• DDL

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "HR"."JV$DCON_PART" ("JRN_FLAG", "JRN_DATE", "JRN_SUBSCRIBER", "ABBR", "NAME", "LAND_KM") AS
    select
     decode(TARG.ROWID, null, 'D', 'I')   AS  JRN_FLAG,
     sysdate      AS  JRN_DATE,
     JRN.CDC_SUBSCRIBER       AS  JRN_SUBSCRIBER,
     JRN.ABBR     AS  ABBR
     ,TARG.NAME   AS  NAME,
     TARG.LAND_KM     AS  LAND_KM
  from
     (
         select
             J.ABBR   AS  ABBR,
             SUB.CDC_SUBSCRIBER  AS  CDC_SUBSCRIBER
         from
             HR.J$CON_PART       J,
             HR.SNP_CDC_SUBS     SUB,
             ALL_APPLY_PROGRESS          APP
         where
             SUB.CDC_SET_NAME    = 'HR.CONCON'
             and J.WINDOW_ID > SUB.MIN_WINDOW_ID
             and J.WINDOW_ID <= APP.APPLIED_MESSAGE_NUMBER
         group by
             J.ABBR,
             SUB.CDC_SUBSCRIBER
     )    JRN,
     HR.CON_PART    TARG
  where
     JRN.ABBR    = TARG.ABBR (+);

• APPLIED_MESSAGE_NUMBERの参照元

  ALL_APPLY_PROGRESSビュー
   ↑ created by DBA_APPLY_PROGRESSビュー
     ↑ created by _DBA_APPLY_MILESTONEビュー
       ↑ created by sys.streams$_apply_milestone表

処理概要 †

1. 実表UPDATE時
   • 下記プロシージャが実行され、UPDATEした時のSCNと、レコードのPKがJV$CON_PARTにINSERTされる

     PROCEDURE ODI_UPDATE_HANDLER (in_any IN ANYDATA)
     IS
            lcr     SYS.LCR$_ROW_RECORD;
            rc      PLS_INTEGER;
            key_chg BOOLEAN;
      BEGIN
            rc := in_any.GETOBJECT(lcr);
            key_chg := FALSE;
            IF (lcr.GET_VALUE('NEW', 'ABBR', 'N') IS NOT NULL) THEN    -- ★ 主キーが更新されていれば、TRUE
                    key_chg := TRUE;
            END IF;
     
            INSERT INTO HR.J$CON_PART  　　　　　　　　　-- ★ 実表レコードの主キー(old)と、コミット時のSCNをWINDOW_IDとしてINSERT
            (ABBR,
            WINDOW_ID)
            VALUES
            (lcr.GET_VALUE('OLD', 'ABBR').AccessVarchar2(),
            lcr.get_commit_scn);                                                 
     
            IF (key_chg) THEN
                    INSERT INTO HR.J$CON_PART  　　　　　　 -- ★ 主キーが更新されていれば、新しい値とSCNをINSERT
                    (ABBR,
                    WINDOW_ID)
                    VALUES
                    (lcr.GET_VALUE('NEW', 'ABBR', 'Y').AccessVarchar2(),
                    lcr.get_commit_scn);
            END IF;
     END;

     • サンプル (LogMiner?を使用して、V$LOGMNR_CONTENTSのREDO_SQLから取得)

       実行ユーザ	COMMIT_SCN	START_TIMESTAMP	COMMIT_TIMESTAMP	REDO_SQL	メモ
       HR	1029077	2017/03/12 19:07:35	2017/03/12 19:07:37	update "HR"."CON_PART" set "NAME" = 'JDK' where "ABBR" = 'DJ' and "NAME" = 'DJDJD' and ROWID = 'AAAW8dAAEAAAAakAAA';	実表更新
       SYS	1029081	2017/03/12 19:07:41	2017/03/12 19:07:41	insert into "HR"."J$CON_PART"("WINDOW_ID","ABBR") values ('1029077','DJ');	ジャーナル表へSCNとPKを挿入
       SYS	1029081	2017/03/12 19:07:41	2017/03/12 19:07:41	insert into "SYS"."STREAMS$_APPLY_PROGRESS"("APPLY#","SOURCE_DB_NAME","XIDUSN","XIDSLT","XIDSQN","COMMIT_SCN","SPARE1","COMMIT_POSITION","TRANSACTION_ID") values ('1','ORCL','3','7','1310','1029077',NULL,HEXTORAW('0000000fb3d500000001000000010000000fb3d5000000010000000101'),NULL);	上記SCN(1029077)を登録。ただし、この表のSCNはジャーナルビューには連携しない
       SYS	1029115	2017/03/12 19:08:11	2017/03/12 19:08:11	update "SYS"."STREAMS$_APPLY_MILESTONE" set "OLDEST_SCN" = '1029077', "COMMIT_SCN" = '1029077', "SYNCH_SCN" = '0', "EPOCH" = '4', "PROCESSED_SCN" = '1029091', "APPLY_TIME" = TO_DATE('12-MAR-17', 'DD-MON-RR'), "APPLIED_MESSAGE_CREATE_TIME" = TO_DATE('12-MAR-17', 'DD-MON-RR'), "SPARE1" = '0', "START_SCN" = '1029113', "OLDEST_TRANSACTION_ID" = '3.7.1310', "LWM_EXTERNAL_POS" = NULL, "OLDEST_POSITION" = NULL, "PROCESSED_POSITION" = NULL, "START_POSITION" = NULL, "XOUT_PROCESSED_POSITION" = NULL, "XOUT_PROCESSED_CREATE_TIME" = NULL, "XOUT_PROCESSED_TID" = NULL, "XOUT_PROCESSED_TIME" = NULL, "APPLIED_HIGH_POSITION" = NULL, "OLDEST_CREATE_TIME" = TO_DATE('12-MAR-17', 'DD-MON-RR'), "SPARE5" = NULL, "PTO_RECOVERY_SCN" = '875335', "PTO_RECOVERY_INCARNATION" = '1' where "OLDEST_SCN" = '1029075' and "COMMIT_SCN" = '1024949' and "SYNCH_SCN" = '0' and "EPOCH" = '4' and "PROCESSED_SCN" = '1029077' and "APPLY_TIME" = TO_DATE('12-MAR-17', 'DD-MON-RR') and "APPLIED_MESSAGE_CREATE_TIME" = TO_DATE('12-MAR-17', 'DD-MON-RR') and "SPARE1" = '0' and "START_SCN" = '1029078' and "OLDEST_TRANSACTION_ID" = '3.7.1310' and "LWM_EXTERNAL_POS" IS NULL and "OLDEST_POSITION" IS NULL and "PROCESSED_POSITION" IS NULL and "START_POSITION" IS NULL and "XOUT_PROCESSED_POSITION" IS NULL and "XOUT_PROCESSED_CREATE_TIME" IS NULL and "XOUT_PROCESSED_TID" IS NULL and "XOUT_PROCESSED_TIME" IS NULL and "APPLIED_HIGH_POSITION" IS NULL and "OLDEST_CREATE_TIME" = TO_DATE('12-MAR-17', 'DD-MON-RR') and "SPARE5" IS NULL and "PTO_RECOVERY_SCN" = '875335' and "PTO_RECOVERY_INCARNATION" = '1' and ROWID = 'AAAANKAABAAABbJAAA';	上記SCN(1029077)に更新。この表のSCNが、ジャーナルビューに連携(WHERE句で使用)される。以降、30secおきに、ほぼ同様のSQLが発行されている模様。

1. ODIから差分レコード抽出時
   1. SNP_CDC_SETのうち、ジャーナル化したモデルのレコードのWINDOW_IDが更新される。更新値は、実表に結びつけたAPPLYが処理した最新のSCNと思われる (ODI Studio ログ名 「Extend Window」)

      update HR.SNP_CDC_SET
      set 	CUR_WINDOW_ID= 
      		NVL((select	APPLIED_MESSAGE_NUMBER
      		from ALL_APPLY_PROGRESS 
      		where apply_name = 'ODI_CDC_A'), 0),
      	CUR_WINDOW_ID_DEL= 
      		NVL((select	APPLIED_MESSAGE_NUMBER
      		from ALL_APPLY_PROGRESS 
      		where apply_name = 'ODI_CDC_A'), 0),
      	CUR_WINDOW_ID_INS= 
      		NVL((select	APPLIED_MESSAGE_NUMBER
      		from ALL_APPLY_PROGRESS 
      		where apply_name = 'ODI_CDC_A'), 0)
      where	CDC_SET_NAME = 'HR.CONCON'

   2. SNP_CDC_SUBSのWINDOW_IDを更新。ジャーナル化実表対象に対して登録しているサブスクライバの分、実行される (SNP_CDC_SUBSはサブスクライバ単位にレコードをもつ。ODI Studio ログ名 「Lock subscriber」)。これで、JV$CON_PARTからレコードが取得できるようになる

      	update	HR.SNP_CDC_SUBS    SUBS
      	set	(
      		SUBS.MAX_WINDOW_ID,
      		SUBS.MAX_WINDOW_ID_INS,
      		SUBS.MAX_WINDOW_ID_DEL
      		) =	(
      			select	CDC.CUR_WINDOW_ID,
      				CDC.CUR_WINDOW_ID_INS,
      				CDC.CUR_WINDOW_ID_DEL
      			from	HR.SNP_CDC_SET    CDC
      			where	CDC.CDC_SET_NAME = 'HR.CONCON'
      			)
      	where	SUBS.CDC_SET_NAME	= 'HR.CONCON'
      	and	SUBS.CDC_SUBSCRIBER	= ?

   3. DELETE以外で作成された、ビューのレコードを全件取得して、ターゲットに出力

      select 	
      	CON_PART.ABBR	AS ABBR,
      	CON_PART.NAME	AS NAME,
      	CON_PART.LAND_KM	AS AREA_KM
      from	HR.JV$CON_PART CON_PART
      where	
      	JRN_FLAG <> 'D'
      AND JRN_SUBSCRIBER = 'SUNOPSIS' /* AND JRN_DATE < sysdate */

   4. 今回取得したレコードのMAX_WINDOW_ID_INS(SCN)をMIN_WINDOW_IDに保存。(次回は、このSCNより後のレコードを対象にするため)

      	update	HR.SNP_CDC_SUBS    SUBS
      	set	SUBS.MIN_WINDOW_ID	= SUBS.MAX_WINDOW_ID_INS
      	where	SUBS.CDC_SET_NAME	= 'HR.CONCON'
      	and	SUBS.CDC_SUBSCRIBER	= ?

   5. 上記で保存したSCN以下のレコードを削除

      delete from	HR.J$CON_PART    JRN
      where	JRN.WINDOW_ID <=	(
      			select	min(SUBS.MIN_WINDOW_ID)
      			from	HR.SNP_CDC_SUBS    SUBS
      			where	SUBS.CDC_SET_NAME = 'HR.CONCON'
      			)

Partitionテーブルに対する、Journal(Streams)の利用時の注意 †

• DBのFORCE_LOGGINGがOFFの場合、ジャーナル開始時に、対象テーブルのテーブルスペースに対して、以下のSQLが実行される

  ALTER TABLESPACE <テーブルスペース名> FORCE LOGGING;

• テーブルスペース名はdba_tables, dba_tablespacesを結合して取得されるが、パーティション表の場合、dba_tablesのテーブルスペース名がNULLになるので、エラーになる
• これを避けるには、JKMを編集する(パーティションテーブルの判断するよう、PL/SQL修正)か、DB自体にFOURCE_LOGGINGをONにする必要がある。後者に関しては以下。

  SQL> select force_logging from v$database;
  
  FORCE_LOGGING
  ---------------------------------------
  NO
  
  SQL> ALTER DATABASE FORCE LOGGING;
  
  Database altered.
  
  SQL> select force_logging from v$database;
  
  FORCE_LOGGING
  ---------------------------------------
  YES

STREAMSによるSYSAUX拡張エラー †

• https://community.oracle.com/message/3310469#3310469
• how to fix
  • http://blog.yannickjaquier.com/oracle/oracle-streams-errors.html

Memo †

• Studio GUIからエージェントにスケジュールを依頼した後、Studio GUIを停止してもスケジュールは動き続けるか？
  • Agentが起動していれば動き続ける