ねら～ITエンジニア雑記

やきうのお兄ちゃんが綴るOracle Databaseメインのブログ

PL/SQL の ネイティブ・コンパイル(PLSQL_CODE_TYPE=NATIVE) でループ処理を高速化してみる。(Oracle Database)

Database Oracle

Oracle Database の PL/SQL には ネイティブ・コンパイル という機能があり、計算集中型のプロシージャーを高速化できます。
計算集中型とは DML(SELECT, INSERT, UPDATE, DELETE等)以外のロジック部分、例えばループ処理等で時間が掛かるプログラムの事を指します。

この記事では PL/SQL の ネイティブ・コンパイル でループ処理を高速化してみますやで。
彡(ﾟ)(ﾟ)

1. 参考マニュアル

マニュアルの記述は以下の通りです。

12.10 システム固有の実行のためのPL/SQLユニットのコンパイル
https://docs.oracle.com/cd/F19136_01/lnpls/plsql-optimization-and-tuning.html#GUID-7C4557E7-4C35-4CAB-A95D-CB96BDC6D487
：
PL/SQLのネイティブ・コンパイルによって、計算集中型のプロシージャ操作のパフォーマンスは大幅に改善されます
：

関連のパラメータは下記(PLSQL_CODE_TYPE)で INTERPRETED と NATIVE を選択します。デフォルトは INTERPRETED です。

1.277 PLSQL_CODE_TYPE
https://docs.oracle.com/cd/F19136_01/refrn/PLSQL_CODE_TYPE.html#GUID-19CE54C7-6B2A-4F3E-92C5-D30AFCD01E3F
：
PLSQL_CODE_TYPE = { INTERPRETED | NATIVE }
：

2. サンプルプログラムと実行結果(ネイティブ・コンパイル前)

下記のストアド・プロシージャーでテストしてみます。10億回ループするプログラムとなります。

ALTER SESSION SET PLSQL_CODE_TYPE = INTERPRETED;
CREATE OR REPLACE PROCEDURE PRC_TEST_LOOP
IS
  i PLS_INTEGER;
  j PLS_INTEGER;
BEGIN
  j := 0;
  FOR i IN 1..1000000000
  LOOP
    j := i;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('j => ' || TO_CHAR(j));
END;
/

SET SERVEROUTPUT ON SIZE 1000000;
SET TIMING ON;
EXEC PRC_TEST_LOOP;

実行結果は以下の通りです。

SQL> ALTER SESSION SET PLSQL_CODE_TYPE = INTERPRETED;

Session altered.

Elapsed: 00:00:00.01
SQL> CREATE OR REPLACE PROCEDURE PRC_TEST_LOOP
  2  IS
  3    i PLS_INTEGER;
  4    j PLS_INTEGER;
  5  BEGIN
  6    j := 0;
  7    FOR i IN 1..1000000000
  8    LOOP
  9      j := i;
 10    END LOOP;
 11    DBMS_OUTPUT.PUT_LINE('j => ' || TO_CHAR(j));
 12  END;
 13  /


Procedure created.

Elapsed: 00:00:00.02
SQL> SQL> SET SERVEROUTPUT ON SIZE 1000000;
SQL> SET TIMING ON;
SQL> EXEC PRC_TEST_LOOP;
j => 1000000000

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.54
SQL>

10秒弱程度の時間が掛かっています。

SQLトレースも取得してみました。

ALTER SESSION SET TRACEFILE_IDENTIFIER = "AYSHIBAT";
EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE, plan_stat => 'ALL_EXECUTIONS');
EXEC PRC_TEST_LOOP;
EXEC DBMS_SESSION.SESSION_TRACE_DISABLE;

SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = "AYSHIBAT";

Session altered.

Elapsed: 00:00:00.00
SQL> EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE, plan_stat => 'ALL_EXECUTIONS');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
SQL> EXEC PRC_TEST_LOOP;
j => 1000000000

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.91
SQL> EXEC DBMS_SESSION.SESSION_TRACE_DISABLE;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

SQLトレースの結果(tkprof後の抜粋)は以下の通り。CPU がブン廻ってますやね。
彡(^)(^)

SQL ID: 9azc0vuqbanzf Plan Hash: 0

BEGIN PRC_TEST_LOOP; END;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     11.89      11.90          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     11.89      11.91          0          0          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 155

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

3. ネイティブ・コンパイルとコンパイル後の実行結果

下記コマンドでサンプルのストアドをネイティブ・コンパイルしてみます。

ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE;
ALTER PROCEDURE PRC_TEST_LOOP COMPILE;

SQL> ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE;

Session altered.

SQL> ALTER PROCEDURE PRC_TEST_LOOP COMPILE;

Procedure altered.

ネイティブ・コンパイル後のプロシージャーを実行してみます。

SET SERVEROUTPUT ON SIZE 1000000;
SET TIMING ON;
ALTER SESSION SET TRACEFILE_IDENTIFIER = "AYSHIBAT";
EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE, plan_stat => 'ALL_EXECUTIONS');
EXEC PRC_TEST_LOOP;
EXEC DBMS_SESSION.SESSION_TRACE_DISABLE;

SQL> SET SERVEROUTPUT ON SIZE 1000000;
SQL> SET TIMING ON;
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = "AYSHIBAT";

Session altered.

Elapsed: 00:00:00.00
SQL> EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE, plan_stat => 'ALL_EXECUTIONS');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> EXEC PRC_TEST_LOOP;
j => 1000000000

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.65
SQL> EXEC DBMS_SESSION.SESSION_TRACE_DISABLE;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>

3秒弱程度で完了しました。SQLトレース(tkprof後)の抜粋は以下の通り

SQL ID: 9azc0vuqbanzf Plan Hash: 0

BEGIN PRC_TEST_LOOP; END;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      2.51       2.63          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      2.51       2.63          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 155

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

CPU時間が短くなっているのが SQLトレース でも確認できました。 彡(^)(^)

4. まとめ

ネイティブ・コンパイルでPL/SQLのロジック部分が高速化するのを確認できました。
ストアド・プロシージャ(or ファンクション/パッケージ)のロジック部分で CPU がブン回っているようなら、試してみて下さいね。
彡(^)(^)

V$SQL_PLAN の OTHER_XML列 に格納された XMLデータ を整形して出力してみる(Oracle Database)

Oracle Database 実行計画

V$SQL_PLAN は Oracle Database の実行計画に関する情報が格納されていますが、該当SQLに関連する情報が格納されています。

SET LONG 1000000;
SET LONGC 1000000;
SET LINESIZE 100;
COLUMN OTHER_XML_VALUE FORMAT A100;
SELECT OTHER_XML FROM V$SQL_PLAN S
 WHERE S.SQL_ID       = 'f9r2y6xdz6t84'
   AND S.CHILD_NUMBER = 4;

OTHER_XML
----------------------------------------------------------------------------------------------------
<other_xml><info type="cardinality_feedback" note="y">yes</info><info type="db_version">12.2.0.1</in
fo><info type="parse_schema"><![CDATA["AYSHIBAT"]]></info><info type="dynamic_sampling" note="y">11<
/info><info type="plan_hash_full">247910413</info><info type="plan_hash">5458159</info><info type="p
lan_hash_2">247910413</info><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint>
<![CDATA[OPTIMIZER_FEATURES_ENABLE('12.2.0.1')]]></hint><hint><![CDATA[DB_VERSION('12.2.0.1')]]></hi
nt><hint><![CDATA[OPT_PARAM('optimizer_dynamic_sampling' 11)]]></hint><hint><![CDATA[ALL_ROWS]]></hi
nt><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[FULL(@"SEL$1" "B"@"SEL$1")]]></hint
><hint><![CDATA[FULL(@"SEL$1" "A"@"SEL$1")]]></hint><hint><![CDATA[LEADING(@"SEL$1" "B"@"SEL$1" "A"@
"SEL$1")]]></hint><hint><![CDATA[USE_HASH(@"SEL$1" "A"@"SEL$1")]]></hint></outline_data></other_xml>

このXMLデータを整形して出力してみますやで彡(ﾟ)(ﾟ)

1. テストデータ

DBMS_XPLAN の以下の実行計画(SQL_ID f9r2y6xdz6t84, child number 4)の OTHER_XML列 を使用します。

SQL> SET AUTOTRACE OFF;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('f9r2y6xdz6t84', NULL, 'ADVANCED ALLSTATS LAST ADAPTIVE'));
：
：
SQL_ID  f9r2y6xdz6t84, child number 4
-------------------------------------
SELECT /*+ MONITOR */        B.ITEM_NAME      , TRUNC(A.ORDER_DATE,
'DD') AS ORDER_DAILY      , COUNT(*)   FROM ORDER_TBL A      , ITEM_TBL
B  WHERE A.ITEM_NO  = B.ITEM_NO --   AND B.ITEM_NO BETWEEN 1 AND 100
AND TO_CHAR(B.REGIST_DATE, 'YYYYMMDD') = '20120801'  GROUP BY
B.ITEM_NAME, TRUNC(A.ORDER_DATE, 'DD')  ORDER BY 1

Plan hash value: 5458159

------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |       |  2435 (100)|          |    261 |00:00:00.51 |    8922 |       |       |          |
|   1 |  SORT GROUP BY      |           |      1 |    261 |  6786 |  2435   (1)| 00:00:01 |    261 |00:00:00.51 |    8922 | 18432 | 18432 |16384  (0)|
|*  2 |   HASH JOIN         |           |      1 |  33601 |   853K|  2433   (1)| 00:00:01 |  26000 |00:00:00.50 |    8922 |  2171K|  2171K| 1135K (0)|
|*  3 |    TABLE ACCESS FULL| ITEM_TBL  |      1 |     10 |   150 |    26   (4)| 00:00:01 |     10 |00:00:00.01 |      91 |       |       |          |
|   4 |    TABLE ACCESS FULL| ORDER_TBL |      1 |   2600K|    27M|  2399   (1)| 00:00:01 |   2600K|00:00:00.14 |    8831 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / B@SEL$1
   4 - SEL$1 / A@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('optimizer_dynamic_sampling' 11)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "B"@"SEL$1")
      FULL(@"SEL$1" "A"@"SEL$1")
      LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
      USE_HASH(@"SEL$1" "A"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."ITEM_NO"="B"."ITEM_NO")
   3 - filter(TO_CHAR(INTERNAL_FUNCTION("B"."REGIST_DATE"),'YYYYMMDD')='20120801')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=2) "B"."ITEM_NAME"[VARCHAR2,100], TRUNC(INTERNAL_FUNCTION("A"."ORDER_DATE"),'fmdd')[8], COUNT(*)[22]
   2 - (#keys=1) "B"."ITEM_NAME"[VARCHAR2,100], "A"."ORDER_DATE"[DATE,7]
   3 - (rowset=256) "B"."ITEM_NO"[NUMBER,22], "B"."ITEM_NAME"[VARCHAR2,100]
   4 - "A"."ITEM_NO"[NUMBER,22], "A"."ORDER_DATE"[DATE,7]

Note
-----
   - dynamic statistics used: dynamic sampling (level=AUTO)
   - statistics feedback used for this statement

2. 一階層目と二階層目のXMLデータを出力してみる。

まず一階層目のXMLデータを出力してみます。

SET LONG 1000000;
SET LONGC 1000000;
SET LINESIZE 100;
COLUMN XML_VALUE FORMAT A100;
SELECT INFO.XML_VALUE
  FROM (
    SELECT EXTRACT(VALUE(p), '/*') AS XML_VALUE
      FROM V$SQL_PLAN S, 
           TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE.CREATEXML(OTHER_XML), '/other_xml/*'))) p
     WHERE S.SQL_ID       = 'f9r2y6xdz6t84'
       AND S.CHILD_NUMBER = 4
) INFO;

XML_VALUE
----------------------------------------------------------------------------------------------------
<info type="cardinality_feedback" note="y">yes</info>
<info type="db_version">12.2.0.1</info>
<info type="parse_schema"><![CDATA["AYSHIBAT"]]></info>
<info type="dynamic_sampling" note="y">11</info>
<info type="plan_hash_full">247910413</info>
<info type="plan_hash">5458159</info>
<info type="plan_hash_2">247910413</info>
<outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_
ENABLE('12.2.0.1')]]></hint><hint><![CDATA[DB_VERSION('12.2.0.1')]]></hint><hint><![CDATA[OPT_PARAM(
'optimizer_dynamic_sampling' 11)]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LE
AF(@"SEL$1")]]></hint><hint><![CDATA[FULL(@"SEL$1" "B"@"SEL$1")]]></hint><hint><![CDATA[FULL(@"SEL$1
" "A"@"SEL$1")]]></hint><hint><![CDATA[LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")]]></hint><hint><![C
DATA[USE_HASH(@"SEL$1" "A"@"SEL$1")]]></hint></outline_data>

SQL を少し編集して二階層目の情報を出力してみます。

SET LONG 1000000;
SET LONGC 1000000;
SET LINESIZE 100;
COLUMN INFO.XML_VALUE FORMAT A100;
SELECT INFO.XML_VALUE
  FROM (
    SELECT EXTRACT(VALUE(p), '/*') AS XML_VALUE
      FROM V$SQL_PLAN S, 
           TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE.CREATEXML(OTHER_XML), '/other_xml/*/*'))) p
     WHERE S.SQL_ID       = 'f9r2y6xdz6t84'
       AND S.CHILD_NUMBER = 4
) INFO;

XML_VALUE
----------------------------------------------------------------------------------------------------
<hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint>
<hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('12.2.0.1')]]></hint>
<hint><![CDATA[DB_VERSION('12.2.0.1')]]></hint>
<hint><![CDATA[OPT_PARAM('optimizer_dynamic_sampling' 11)]]></hint>
<hint><![CDATA[ALL_ROWS]]></hint>
<hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint>
<hint><![CDATA[FULL(@"SEL$1" "B"@"SEL$1")]]></hint>
<hint><![CDATA[FULL(@"SEL$1" "A"@"SEL$1")]]></hint>
<hint><![CDATA[LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")]]></hint>
<hint><![CDATA[USE_HASH(@"SEL$1" "A"@"SEL$1")]]></hint>

3. OUTLINEヒントを抽出してみる。

下記のような SQL にすると DBMS_XPLAN の Outline Dataセクション に相当する情報(OUTLINEヒント)を出力できます。

SELECT HINT.HINT_VALUE
  FROM (
    SELECT EXTRACTVALUE(VALUE(p), '/hint') AS HINT_VALUE
      FROM V$SQL_PLAN S, 
           TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE.CREATEXML(OTHER_XML), '/other_xml/outline_data/hint'))) p
     WHERE S.SQL_ID       = 'f9r2y6xdz6t84'
       AND S.CHILD_NUMBER = 4
) HINT;

HINT_VALUE
----------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
OPT_PARAM('optimizer_dynamic_sampling' 11)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "B"@"SEL$1")
FULL(@"SEL$1" "A"@"SEL$1")
LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
USE_HASH(@"SEL$1" "A"@"SEL$1")

4. まとめ

XML DB は難解……しかも今回使用したファンクションは非推奨となっています。 OTHER_XML列に格納されているデータも含めて自己責任で使用して下さいませ。

EXTRACT (XML) https://docs.oracle.com/cd/F19136_01/sqlrf/EXTRACT-XML.html#GUID-593295AA-4F46-4D75-B8DC-E7BCEDB1D4D7

EXTRACTVALUE https://docs.oracle.com/cd/F19136_01/sqlrf/EXTRACTVALUE.html#GUID-20AB974B-7544-4F44-B539-787FB6145680

まあ今時は JSON だとは思いますが。 彡(ﾟ)(ﾟ)

OCI の ネットワーク・ソース で コンソール に ログイン可能 な IP を制限する(Oracle Cloud Infrastructure)

OCI Oracle Cloud

表題の通り OCI の ネットワーク・ソース で コンソール に ログイン可能 な IP を制限してみますやで。
彡(ﾟ)(ﾟ)

1. 参考マニュアル

以下のOCIマニュアルを参考にしています。

https://docs.oracle.com/ja-jp/iaas/Content/Identity/Tasks/managingnetworksources.htm

https://docs.oracle.com/ja-jp/iaas/Content/Identity/Tasks/managingpasswordrules.htm#Managing_Authentication_Settings

2. ネットワーク・ソース の 作成

画面左上ハンバーガーメニュー → Identity & Security → Network Sources を選択します。

NetworkSource001.png

Create Network Sourceボタンをクリックして接続を許可する CIDR(or IP) を入力します。

NetworkSource002.png

Createボタンをクリックしてネットワーク・ソースを作成します。

3. 認証設定の管理 の 変更

画面左上ハンバーガーメニュー → Identity & Security → Authentication Settings を選択します。 NetworkSource003.png

Edit Authentication Settingsボタンをクリックして認証設定を編集します。

NetworkSource004.png

  1. で作成した ネットワーク・ソース を選択して Save Changesボタンをクリックします。

4. ログインの試行(許可されていないネットワークより)

許可されていないネットワークからのOCIログインを試してみます。
下記のようなエラーが発生してログインできません。

NetworkSource005.png

5. ログインの試行(許可されたネットワークより)

許可されたネットワークからOCIにログインしてみます。

NetworkSource006.png

正常にログインできます。

6. まとめ

すでに同様の記事は幾つもあるのですが、復習のためにワイ自身でもやってみました。
マニュアルにも記載があるのですが設定をしくじるとログイン不可になって何もできなくなります。

https://docs.oracle.com/ja-jp/iaas/Content/Identity/Tasks/managingpasswordrules.htm#Managing_Authentication_Settings

 注意

ネットワーク・ソース制限を設定する前に、許可されたネットワークが使用できない場合に
テナンシへのアクセスを可能にするAPIキーが設定されていることを確認してください。
APIキーを設定せず、許可されたネットワークを使用できない場合は、Oracleサポートに
連絡するまですべてのユーザーがテナンシからロックアウトされます。
API署名キーの設定の詳細は、必要なキーとOCIDを参照してください。

管理ユーザーのAPIキー発行やサポートサイト(MOS)へのログイン確認など、
あらかじめ対策をしておいて下さいね彡(^)(^)

DDLトリガー と イベント属性ファンクション で CREATE されるオブジェクトのオーナー名とオブジェクト名を記録する。(Oracle Database)

Oracle Database

表題の通り Oracle Database の DDLトリガー と イベント属性ファンクションで、CREATE されるオブジェクトのオーナー名とオブジェクト名を記録してみますやで。
彡(ﾟ)(ﾟ)

1. 参考マニュアル

以下のマニュアルを参考にしました。イベント属性ファンクションが肝ですね。

Oracle Database データベース PL/SQL言語リファレンス 19c

9.15.1 イベント属性ファンクション
https://docs.oracle.com/cd/F19136_01/lnpls/plsql-triggers.html#GUID-266DBF6D-AA74-490C-ADE5-962C10708C2D
ora_dict_obj_name ... VARCHAR2(128): DDL操作が発生したディクショナリ・オブジェクトの名前
ora_dict_obj_owner ... VARCHAR2(128): DDL操作が発生したディクショナリ・オブジェクトの所有者
ora_dict_obj_type ... VARCHAR2(20) : DDL操作が発生したディクショナリ・オブジェクトの型
　
9.5.3 INSTEAD OF CREATEトリガー
https://docs.oracle.com/cd/F19136_01/lnpls/plsql-triggers.html#GUID-7B32FE4B-AB8E-48B0-A74C-599584A485A7
　
14.12 CREATE TRIGGER文 ※構文図
https://docs.oracle.com/cd/F19136_01/lnpls/CREATE-TRIGGER-statement.html#GUID-AF9E33F1-64D1-4382-A6A4-EC33C36F237B

2. 事前準備(記録用テーブル と DDLトリガー作成)

記録用テーブル と DDLトリガー を作成します。SYSユーザーで作成します。
今回は ayshibatスキーマ の CREATE を記録するようにしました。

CONNECT SYS/xxxxxxxx@ORCL AS SYSDBA

CREATE TABLE ayshibat.tbl_create_object_logging (
    OBJECT_OWNER     VARCHAR2(128)
  , OBJECT_NAME      VARCHAR2(128)
  , OBJECT_TYPE      VARCHAR2(23)
  , CREATE_TIMESTAMP TIMESTAMP
);

CREATE OR REPLACE TRIGGER ayshibat.trg_create_object_logging
INSTEAD OF CREATE ON ayshibat.SCHEMA
BEGIN
  INSERT INTO ayshibat.tbl_create_object_logging
  VALUES (
      ora_dict_obj_owner
    , ora_dict_obj_name
    , ora_dict_obj_type
    , SYSTIMESTAMP
  );
END;
/

3. オブジェクト作成 と 記録結果の確認

対象スキーマ(今回は ayshibatスキーマ)でテーブルを CREATE してみます。

CONNECT ayshibat/xxxxxxxx@ORCL;

CREATE TABLE TAB1(ID NUMBER);

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY/MM/DD HH24:MI:SS.FF';
SET LINESIZE 170;
COLUMN OBJECT_OWNER FORMAT A30;
COLUMN OBJECT_NAME  FORMAT A30;
COLUMN CREATE_TIMESTAMP FORMAT A30;
SELECT * FROM ayshibat.tbl_create_object_logging;

結果は以下の通りです。

SQL> CONNECT ayshibat/xxxxxxxx@ORCL;
Connected.

SQL> CREATE TABLE TAB1(ID NUMBER);

Table created.

SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY/MM/DD HH24:MI:SS.FF';

Session altered.

SQL> SET LINESIZE 170;
SQL> COLUMN OBJECT_OWNER FORMAT A30;
SQL> COLUMN OBJECT_NAME  FORMAT A30;
SQL> COLUMN CREATE_TIMESTAMP FORMAT A30;
SQL> SELECT * FROM ayshibat.tbl_create_object_logging;

OBJECT_OWNER                   OBJECT_NAME                    OBJECT_TYPE             CREATE_TIMESTAMP
------------------------------ ------------------------------ ----------------------- ------------------------------
AYSHIBAT                       TAB1                           TABLE                   2022/05/30 10:11:44.580414

オーナー名とオブジェクト名を記録できました。

4. まとめ

CREATE されたオブジェクトのオーナー名やオブジェクト名を記録できました。
当初はイベント属性ファンクションに辿り着けず、教えて頂いたN家さんに感謝感激雨霰
彡(^)(^)ｱﾘｶﾞﾄｺﾞｻﾞﾏｰｽ

OCI API Gateway の機能と OCI Vault のシークレットで OCI Functions を保護(認証)してみる。(Oracle Cloud Infrastructure)

Oracle OCI Cloud Java

OCI API Gateway には認証機能が付いています。今回は API Gateway の認証機能を使用して OCI Functions を保護(認証)してみますやで。 彡(ﾟ)(ﾟ)

1. やりたい事＆元ネタ

元ネタは下記の記事となります。

Oracle Functionsを利用したAPI Gatewayの認証
https://oracle-japan.github.io/ocitutorials/cloud-native/functions-apigateway-for-intermediates/

上記記事の 認証用Functions を改変して、リクエストヘッダーにセットした文字列(トークン) と OCI Vault のシークレットを突合する、以下のような処理を実装してみます。

(1). API Gateway の エンドポイント をコールする。
(2). API Gateway から認証用の Functions がコールされる。
(3). 認証用Functions で OCI Vault のシークレットを取り出して、ヘッダーの文字列(トークン)と突合する。
(4). ヘッダーの文字列(トークン) と OCI Vault のシークレット が一致した場合は本体の Functions をコールする。

接続トポロジは以下の通りです。
apigw_auth001.png

2. シンプルな Functions の作成

詳細は省略します。下記記事を参照して下さい。

https://qiita.com/ora_gonsuke777/items/a9bb52faadcb9f2af38e

下記の結果が得られるように少し改変してみました。wai ga AYU ya!彡(^)(^)

$ fn invoke ayu-functions1 ayu-app
Hello, wai ga AYU ya!

3. API Gateway から OCI Functions の呼び出し設定

これも詳細は省略します。下記記事を参照してシンプルな Functions を API Gateway からコールできるようにしておいて下さい。

https://qiita.com/ora_gonsuke777/items/e2cc19d38f056241fb07

4. OCI Vault, キー, シークレットの作成

下記記事を参照して、認証に使用する文字列(トークン)を OCI Vault のシークレットとして登録して下さい。

https://qiita.com/kenwatan/items/5867a06ef6a00749dcf0

作成したシークレットの OCID はこの後使用するので、メモしておいて下さい。 apigw_auth002.png

5. 認証用Functions のダウンロード(git clone)と YAML編集、ビルド

認証用Functions のサンプルを下記に置いておきました。

https://github.com/gonsuke777/Functions

Functions の Cloud Shell から git clone でダウンロードします。

git clone https://github.com/gonsuke777/Functions

ダウンロード後に func.yaml を編集して、シークレットの OCID を登録したものに書き換えて下さい。

cd Functions/hello-java/
vi vi 
schema_version: 20180708
name: hello-java
version: 0.0.46
runtime: java
build_image: fnproject/fn-java-fdk-build:jdk17-1.0.146
run_image: fnproject/fn-java-fdk:jre17-1.0.146
cmd: com.example.fn.HelloFunction::handleRequest
timeout: 60
config:
  SECRET1_ID: ocid1.vaultsecret.oc1.ap-tokyo-1.xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

yaml編集後にビルド＆デプロイします。

fn -v deploy --app ayu-functions1

デプロイされた Functions の OCID を控えておきます。 apigw_auth003.png

6. 動的グループの作成と Functions からの OCI Vault読取ポリシー(権限)付与(リソース・プリンシプル)

認証用Functions が OCI Vault のシークレットを読み取れるように権限を付与します。(リソース・プリンシプル)

  • 動的グループ(ayu-dynamic-group2)のマッチングルール ※Functions の OCID を指定
All {resource.id = 'ocid1.fnfunc.oc1.ap-tokyo-1.xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'}
  • 動的グループ(ayu-dynamic-group2)に付与したポリシー(権限) ※シークレットの読み取り権限を付与
allow dynamic-group ayu-dynamic-group2 to read secret-family in compartment xxxxx_compartment
  • OCI Vault のポリシー(権限)をコンパートメントに付与
allow service VaultSecret to use vaults in compartment ayu_compartment
allow service VaultSecret to use keys in compartment ayu_compartment

その他、本記事のメインではありませんが API Gateway用に下記の動的グループ／ポリシー(権限)を付与しています。

  • 動的グループ(ayu-dynamic-group1)のマッチングルール ※API Gateway の OCID を指定
ALL {resource.type = 'ApiGateway', resource.id = 'ocid1.apigateway.oc1.ap-tokyo-1.xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'}
  • 動的グループ(ayu-dynamic-group1)に付与したポリシー(権限) ※API Gateway に Functions の実行権限を付与
ALLOW any-user to use functions-family in compartment ayu_compartment where ALL { request.principal.type= 'ApiGateway', request.resource.compartment.id = 'ocid1.compartment.oc1..xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' }

7. 認証用Functions の動作確認(Cloud Shell)

Functions の Cloud Shell からデプロイした認証用Functions の動作確認をしてみます。

# Case1...No token.
fn invoke ayu-functions1 hello-java
# Case2...Invalid token
echo "{\"type\":\"TOKEN\",\"token\":\"xxxxxxxxx\"}" | fn invoke ayu-functions1 hello-java | jq -a
# Case3...Correct token
echo "{\"type\":\"TOKEN\",\"token\":\"yyyyyyyyy\"}" | fn invoke ayu-functions1 hello-java | jq -a

ビルドや権限付与が上手く行っていれば、それぞれ異なる結果を返却します。

$ fn invoke ayu-functions1 hello-java
Error invoking function. status: 502 message: function failed

$ echo "{\"type\":\"TOKEN\",\"token\":\"xxxxxxxxx\"}" | fn invoke ayu-functions1 hello-java | jq -a
{
  "active": false,
  "principal": null,
  "scope": null,
  "expiresAt": "2020-04-30T10:15:30+01:00",
  "wwwAuthenticate": "Bearer realm=\"example.com\", error=\"invalid token\", error_description=\"token should be \"Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1nHyDtTwR3SEJ3z489...\"\"",
  "clientId": null,
  "context": {
    "email": "john.doe@example.com"
  }
}

$ echo "{\"type\":\"TOKEN\",\"token\":\"yyyyyyyyy\"}" | fn invoke ayu-functions1 hello-java | jq -a
{
  "active": true,
  "principal": "https://example.com/users/jdoe",
  "scope": [
    "list:hello",
    "read:hello",
    "create:hello",
    "update:hello",
    "delete:hello",
    "someScope"
  ],
  "expiresAt": "2022-04-10T09:47:16.703Z",
  "wwwAuthenticate": null,
  "clientId": "host123",
  "context": {
    "email": "john.doe@example.com"
  }
}
$

8. API Gateway Deployment の作成

API Gateway の Deployment を作成します。この時に認証用Functionsを指定します。

変数名 入力する値
NAME 任意の名前
PATH PREFIX 任意のPREFIXを指定
AUTHENTICATION TYPE Custorm
CONPARTMENT 認証用Functionsを作成したコンパートメント
FUNCTION NAME デプロイ済みの認証用Functions
AUTHENTICATION TOKEN ヘッダー
HEADER NAME token
PATH 任意のアプリケーションパス
METHODS 今回は GET を指定
TYPE Oracle Functions を選択
APPPLICATION IN xxx_COMPARTMENT 本体のFunctionsを指定
FUNCTION NAME 呼び出す本体のFunctions

apigw_auth004.png apigw_auth005.png apigw_auth006.png

9. ソースコードの簡単な解説

Functions の初期化処理(FnConfigurationアノテーション)で yaml からシークレットの OCID を取得しています。

    @FnConfiguration
    public void setUp(RuntimeContext ctx) throws Exception {
        config = ctx.getConfiguration();
        secret1Id = config.get("SECRET1_ID");
        String version = System.getenv("OCI_RESOURCE_PRINCIPAL_VERSION");
        if( version != null ) {
            provider = ResourcePrincipalAuthenticationDetailsProvider.builder().build();
        } else {
            try {
                provider = new ConfigFileAuthenticationDetailsProvider("~/.oci/config", "DEFAULT");
            }
            catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

Autonomous Database に Functions で接続する記事のソースをほぼ流用させて頂いています。OCID を基にシークレットに格納されたトークン(文字列)を取得しています。認証用Functions には前述の通りリソース・プリンシプルでシークレットの読取権限(ポリシー)を付与しています。

    private String getSecret(String secretOcid) {
        try (SecretsClient secretsClient = new SecretsClient(provider)) {
            //region setting
            secretsClient.setRegion(Region.AP_TOKYO_1);
            GetSecretBundleRequest getSecretBundleRequest = GetSecretBundleRequest
                .builder()
                .secretId(secretOcid)
                .stage(GetSecretBundleRequest.Stage.Current)
                .build();
            GetSecretBundleResponse getSecretBundleResponse = secretsClient
                .getSecretBundle(getSecretBundleRequest);
            Base64SecretBundleContentDetails base64SecretBundleContentDetails =
                (Base64SecretBundleContentDetails) getSecretBundleResponse.
                        getSecretBundle().getSecretBundleContent();
            byte[] secretValueDecoded = Base64.decodeBase64(base64SecretBundleContentDetails.getContent());
            return new String(secretValueDecoded);
        } catch (Exception e) {
            throw new RuntimeException("Couldn't get content from secret - " + e.getMessage(), e);
        }
    }

ヘッダーのトークンとシークレットの文字列を比較して、合致した場合は正常終了の結果を返却しています。

        if (secret1.equals(input.token)) {
            result = trueResult();
        } else {
            result.wwwAuthenticate = "Bearer realm=\"example.com\", error=\"invalid token\", error_description=\"token should be \"Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1nHyDtTwR3SEJ3z489...\"\"";
            return result;
        }

結果を返却する部分は元ネタをそのまま流用しています。

https://github.com/oracle-japan/function-authorizer-for-apigw

    private Result trueResult() {
        Result trueResult = new Result();
        trueResult.active = true;
        trueResult.principal = "https://example.com/users/jdoe";
        trueResult.scope = new String[]{"list:hello", "read:hello", "create:hello", "update:hello", "delete:hello", "someScope"};
        trueResult.clientId = "host123";
        trueResult.expiresAt = new Date().toInstant().plusMillis(60000).toString();
        Map<String, Object> contextMap = new HashMap<>();
        contextMap.put("email", "john.doe@example.com");
        trueResult.context = contextMap;
        return trueResult;
    }

    private Result falseResult() {
        Result falseResult = new Result();
        falseResult.active = false;
        falseResult.expiresAt = "2020-04-30T10:15:30+01:00";
        Map<String, Object> contextMap = new HashMap<>();
        contextMap.put("email", "john.doe@example.com");
        falseResult.context = contextMap;
        falseResult.wwwAuthenticate = "Bearer realm=\"example.com\"";
        return falseResult;
    }

10. PC端末 の PowerShell から API Gateway Deployment のエンドポイント(URL)をコール

API Gateway Deployment画面から作成した Deployment のエンドポイント(URL)をコピーします。 ※画面からコピーできるのはPREFIXまでなので、APIGWデプロイ時のアプリケーションパスを追加しておきます。 apigw_auth007.png

PowerShell から API Gateway Deployment エンドポイント(URL) をコールしてみます。

# Case1...No header
$headers = @{}
curl -Headers $headers https://xxxxxxxxxxxxxxxxxxxxxxxxxx.apigateway.ap-tokyo-1.oci.customer-oci.com/hello/ayuapp

# Case2...No token
$headers = @{}
$headers["type"] = "TOKEN"
curl -Headers $headers https://xxxxxxxxxxxxxxxxxxxxxxxxxx.apigateway.ap-tokyo-1.oci.customer-oci.com/hello/ayuapp

# Case3...Invalid token
$headers = @{}
$headers["type"] = "TOKEN"
$headers["token"] = "xxxxxxxx"
curl -Headers $headers https://xxxxxxxxxxxxxxxxxxxxxxxxxx.apigateway.ap-tokyo-1.oci.customer-oci.com/hello/ayuapp

# Case4...Correct token
$headers = @{}
$headers["type"] = "TOKEN"
$headers["token"] = "yyyyyyyy"
curl -Headers $headers https://xxxxxxxxxxxxxxxxxxxxxxxxxx.apigateway.ap-tokyo-1.oci.customer-oci.com/hello/ayuapp

以下のように、正しいトークンを指定すると本体のFunctionsの実行結果("Hello, wai ga AYU ya!")を得られました。

PS C:\Users\AYSHIBAT> $headers = @{}
PS C:\Users\AYSHIBAT> curl -Headers $headers https://xxxxxxxxxxxxxxxxxxxxxxxxxx.apigateway.ap-tokyo-1.oci.customer-oci.com/hello/ayuapp
curl : リモート サーバーがエラーを返しました: (401) 許可されていません
発生場所 行:1 文字:1
+ curl -Headers $headers https://xxxxxxxxxxxxxxxxxxxxxxxxxx.apigateway. ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest]、WebException


PS C:\Users\AYSHIBAT> $headers = @{}
PS C:\Users\AYSHIBAT> $headers["type"] = "TOKEN"
PS C:\Users\AYSHIBAT> curl -Headers $headers https://xxxxxxxxxxxxxxxxxxxxxxxxxx.apigateway.ap-tokyo-1.oci.customer-oci.com/hello/ayuapp
curl : リモート サーバーがエラーを返しました: (401) 許可されていません
発生場所 行:1 文字:1
+ curl -Headers $headers https://xxxxxxxxxxxxxxxxxxxxxxxxxx.apigateway. ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest]、WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand


PS C:\Users\AYSHIBAT> $headers = @{}
PS C:\Users\AYSHIBAT> $headers["type"] = "TOKEN"
PS C:\Users\AYSHIBAT> $headers["token"] = "xxxxxxxx"
PS C:\Users\AYSHIBAT> curl -Headers $headers https://xxxxxxxxxxxxxxxxxxxxxxxxxx.apigateway.ap-tokyo-1.oci.customer-oci.com/hello/ayuapp
curl : リモート サーバーがエラーを返しました: (401) 許可されていません
発生場所 行:1 文字:1
+ curl -Headers $headers https://xxxxxxxxxxxxxxxxxxxxxxxxxx.apigateway. ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest]、WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand

PS C:\Users\AYSHIBAT> $headers = @{}
PS C:\Users\AYSHIBAT> $headers["type"] = "TOKEN"
PS C:\Users\AYSHIBAT> $headers["token"] = "yyyyyyyy"
PS C:\Users\AYSHIBAT> curl -Headers $headers https://xxxxxxxxxxxxxxxxxxxxxxxxxx.apigateway.ap-tokyo-1.oci.customer-oci.com/hello/ayuapp


StatusCode        : 200
StatusDescription : OK
Content           : Hello, wai ga AYU ya!
RawContent        : HTTP/1.1 200 OK
                    Connection: keep-alive
                    opc-request-id: /7516FB0E1242D7AB12CE7F9DFEAACCBB/708B115FEB126E2B96E78CE9E87376C6
                    X-XSS-Protection: 1; mode=block
                    Strict-Transport-Security: max-age=3153600...
Forms             : {}
Headers           : {[Connection, keep-alive], [opc-request-id, /7516FB0E1242D7AB12CE7F9DFEAACCBB/708B115FEB126E2B96E78
                    CE9E87376C6], [X-XSS-Protection, 1; mode=block], [Strict-Transport-Security, max-age=31536000]...}
Images            : {}
InputFields       : {}
Links             : {}
ParsedHtml        : mshtml.HTMLDocumentClass
RawContentLength  : 21



PS C:\Users\AYSHIBAT>

11. まとめ

リクエストのヘッダーに文字列を埋め込むだけというとても簡易的な実装ですが、API Gateway と OCI Vault の機能で Functions を保護(認証)できました。
次回はもう少し凝った実装をしてみたいですやね。彡(^)(^)

12. 参考

下記の記事やマニュアルを参考にしました。

  • APIデプロイメントへの認証と認可の追加

https://docs.oracle.com/ja-jp/iaas/Content/APIGateway/Tasks/apigatewayaddingauthzauthn.htm#Adding_Authentication_and_Authorization_to_API_Deployments

  • 認可プロバイダ・ファンクションを使用したAPIデプロイメントへの認証および認可の追加

https://docs.oracle.com/ja-jp/iaas/Content/APIGateway/Tasks/apigatewayusingauthorizerfunction.htm

https://oracle-japan.github.io/ocitutorials/cloud-native/functions-apigateway-for-intermediates/

  • OCI API Gateway の認証・認可機能について

https://qiita.com/shukawam/items/107987bba2e44222c3aa

  • [OCI] OCIシークレットを使ってOracle FunctionsからAutonomous DBに接続してみた。

https://qiita.com/kenwatan/items/5867a06ef6a00749dcf0

OCI GoldenGate(OCI GG) で DBCS SE から DBCS EE にレプリケーションしてみる。(Oracle Cloud Infrastructure)

GoldenGate Oracle Database OCI

直近はお仕事で OCI GoldenGate を触ることが多いので、記事を書いてみます。 従量課金なのがエエですね。 表題のとおり OCI GoldenGate で Oracle Database(DBCS) のレプリケーションを設定してみるやで。
彡(^)(^)

下記マニュアルの手順(クイックスタート)でやってみます。

同じリージョン内のクラウド・データベース間でのデータのレプリケート
https://docs.oracle.com/ja-jp/iaas/goldengate/doc/replicating-data-two-cloud-databases.html

1. OCI GoldenGate とは？

OCI GoldenGate は OCI上で動作する論理レプリケーション/データ連携のツール/PaaSとなります。

Oracle Cloud Infrastructure GoldenGateの概要
https://docs.oracle.com/ja-jp/iaas/goldengate/doc/overview-goldengate.html

オンプレの GoldenGate は 様々なデータベース/データストアに対応しますが、
現時点(2022年3月末)ではOCI GoldenGate は Oracle Database にのみ対応しています。

データベース登録の管理
https://docs.oracle.com/ja-jp/iaas/goldengate/doc/database-registrations.html
サポートされるデータベース
：
次のデータベースは、OCI GoldenGateをソースまたはターゲットとして使用することが保証されています:
Oracle Database 11.2.0.4、12.1.0.2以上
Oracle Exadata
Oracle ExadataCS
Oracle Autonomous Transaction Processing
Oracle Autonomous Data Warehouse
：

2. 検証環境の構成(OCI)

OCI DBCS SE(11.2.0.4, Source) ⇒ OCI GG ⇒ OCI DBCS EE(19.14, Target) の構成でレプリケーションします。 構成図.jpg これらの構成に必要な コンパートメント/VCN/Subnet/DBCS等は作成済みのものとします。

また DBCS SE(11.2.0.4, Source) にはマニュアル記載の推奨パッチを適用しておきます。

https://docs.oracle.com/ja-jp/iaas/goldengate/doc/database-registrations.html
：
Oracleサポートが推奨するように、OCI GoldenGateで使用するデータベースの最新パッチを適用してください。
Oracle 11gの推奨パッチ
https://support.oracle.com/epmos/faces/DocumentDisplay?id=1557031.1
：

伝播対象のテーブルも予め作成しておきます。Source と Target の両方に作成しておきます。

CREATE TABLE AYSHIBAT.TEST_TBL01 (
    ID   NUMBER
  , NAME VARCHAR2(20)
);

ALTER TABLE AYSHIBAT.TEST_TBL01 ADD CONSTRAINT TEST_TBL01_PK PRIMARY KEY(ID) USING INDEX;

3. OCI GG用 の DBユーザー作成＆権限付与、初期化パラメータ設定

下記のマニュアルに従って OCI GG用 の DBユーザー を作成＆権限付与します。

Oracle DatabaseでのOracle GoldenGateの使用 21c (21.3.0)
3.1.1.1 適切なユーザー権限の付与
https://docs.oracle.com/cd/F51462_01/oracle-db/establishing-oracle-goldengate-credentials.html#GUID-F9EBB989-E22F-4355-BE60-40F957B8515E

下記のパラメータも Source/Target の両方で TRUE に変更します。

Oracle Database データベース・リファレンス 19c
1.119 ENABLE_GOLDENGATE_REPLICATION
https://docs.oracle.com/cd/F19136_01/refrn/ENABLE_GOLDENGATE_REPLICATION.html#GUID-600FC071-1516-49B2-B3B3-C1C5430C5917

  • Source側(DBCS 11.2.0.4) のDBユーザー作成、権限付与、パラメータ変更、サプリメンタル・ロギング設定
CREATE USER OGGAYSHIBAT IDENTIFIED BY xxxxxxxxxxxx
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

GRANT CREATE SESSION         TO OGGAYSHIBAT;
GRANT RESOURCE               TO OGGAYSHIBAT;
GRANT ALTER SYSTEM           TO OGGAYSHIBAT;
GRANT ALTER USER             TO OGGAYSHIBAT;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('OGGAYSHIBAT');
GRANT SELECT ANY DICTIONARY  TO OGGAYSHIBAT;
GRANT SELECT ANY TRANSACTION TO OGGAYSHIBAT;

-- For User Object Privilege
GRANT DBA TO OGGAYSHIBAT;

ALTER SYSTEM SET enable_goldengate_replication = TRUE SCOPE=BOTH SID='*';
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
  • Target側(DBCS EE 19.14) のDBユーザー作成、権限付与、パラメータ変更
ALTER SESSION SET CONTAINER = PDB1;
SHOW CON_NAME;

CREATE USER OGGAYSHIBAT IDENTIFIED BY xxxxxxxxxxxx
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

GRANT CREATE SESSION         TO OGGAYSHIBAT;
GRANT RESOURCE               TO OGGAYSHIBAT;
GRANT ALTER SYSTEM           TO OGGAYSHIBAT;
GRANT ALTER USER             TO OGGAYSHIBAT;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('OGGAYSHIBAT', CONTAINER => 'PDB1');
GRANT LOCK ANY TABLE         TO OGGAYSHIBAT;
GRANT SELECT ANY DICTIONARY  TO OGGAYSHIBAT;

-- For User Object Privilege
GRANT DBA                    TO OGGAYSHIBAT;

-- These commands are required execute in CDB.
ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER SYSTEM SET enable_goldengate_replication = TRUE SCOPE=BOTH SID='*';

4. OCI GG Deployment の作成

OCI Console の 左上のハンバーガーボタン ⇒ Oracle Database ⇒ GoldenGate(右側)と遷移して Deployments の Create deployment をクリックします。 OCIGG000a.jpg OCIGG000b.jpg

以下のように入力して行きます。

変数名 入力値
Name 任意の文字列を入力
Description 任意の説明
Compartment 任意のコンパートメントを選択
oCPU count 必要なoCPU数 ※後から変更可能です。
Subnet OCI GG をデプロイする VCN Subnet を選択
Choose a license type ライセンス持ち込み以外は License included を選択します。
GoldenGate instance name 任意の文字列を入力
Administrator usrename 管理ユーザー名を入力
Administrator password 管理ユーザーのパスワードを入力

OCIGG001.jpg OCIGG002.jpg

デプロイが完了すると以下の画面のようになります。 OCIGG003.jpg

5. データベースの登録

OCI Console の 左上のハンバーガーボタン ⇒ Oracle Database ⇒ GoldenGate(右側) と遷移して、Register Databases の Register Database をクリックします。

OCIGG004.jpg

以下のように入力して行きます。Source と Target の両方を登録して下さい。

変数名 入力値
Name 任意の文字列
Alias Name 任意の文字列ただし記号等の制限有り
Description 任意の説明を入力
Compartment OCI GG を作成したコンパートメントを選択
Database Type 今回は DB system database(Bare Metal, VM, Exadata) を選択します。
Database system in ～ Source または Target の DBCS を選択します。

データベース(今回はDBCS)を選択すると他の変数は自動で入力されます。下記を追加入力＆修正しています。 | 変数名 | 入力値 | |-|-| |Database connection string | DBサービス名部分を必要に応じて修正します。今回は Target DB の接続サービス名として pdb1 に修正しています。| |Database username | 上記 3. の手順で作成した OCI GG用 の DBユーザー名を入力します。| |Database user password | OCI GG用DBユーザー名 の パスワード を入力します。|

OCIGG005.jpg OCIGG006.jpg

6. Deployment Console への接続 と データベース接続確認

OCI Console の Deploymentトップ画面から Deployment Console に接続します。

なお今回は Private Subnet に OCI GG Deployment を作成して手元のPCからの直接接続は不可だったため、Compute に SSH接続して SSHポートフォワードで Deployment Console に接続しています。

OCIGG011.jpg OCIGG009.jpg

Deployment Console のトップ画面から 構成 ⇒ データベース(サブタブ) と遷移して、登録データベースのハンバーガーボタンを押して接続を確認します。 OCIGG010.jpg

接続が成功すると「チェックポイント」「TRANDATA情報」「ハートビート」といった項目が表示されます。

7. スキーマ単位 または 表単位 のサプリメンタル・ロギング設定(Sourceのみ)

Deployment Console のトップ画面から 構成 ⇒ データベース(サブタブ) ⇒ Source のハンバーガーボタンと遷移して Sourceデータベースに接続します。

TRANDATA情報からスキーマ単位 または 表単位 のサプリメンタル・ロギングを設定します。
今回は スキーマAYSHIBAT に対してサプリメンタル・ロギングを設定します。

OCIGG012.jpg

実行後にTRANDATA情報を検索すると、以下のようにTRANDATAが設定されていることを確認できます。OCIGG013.jpg

下記マニュアルも参照して下さい。

Oracle GoldenGate Microservicesアーキテクチャを使用してのステップ・バイ・ステップ・データ・レプリケーション
TRANDATAまたはSCHEMATRANDATA情報の有効化
https://docs.oracle.com/cd/F51462_01/ggmas/working-data-replications.html#GUID-C3B8B4EE-B13F-43BF-AE94-CFCB01B26F60

8. チェックポイント表の作成(Targetのみ)

チェックポイント表は Replicat がレプリケーションの進行状況を記録するための GoldenGate内部表 です。

Deployment Console のトップ画面から 構成 ⇒ データベース(サブタブ) ⇒ Source のハンバーガーボタンと遷移して Sourceデータベースに接続します。

チェックポイントからチェックポイント表の作成を発行します。

OCIGG014.jpg

チェックポイント表の作成に成功すると、以下のように作成されたチェックポイント表が表示されます。 OCIGG015.jpg

下記マニュアルも参照して下さい。

Oracle GoldenGate Microservicesアーキテクチャを使用してのステップ・バイ・ステップ・データ・レプリケーション
3.6 Replicatを作成する前に
https://docs.oracle.com/cd/F51462_01/ggmas/working-data-replications.html#GUID-2CE071E8-1B2F-4138-A01D-B5BC6A010062

9. ハートビート表の作成(Source と Target両方)

ハートビート表は GoldenGate の伝播状況、特にラグ(伝播遅延)を確認するのに極めて有益な情報となります。

17.4 自動ハートビート表を使用した監視
https://docs.oracle.com/cd/F51462_01/admin/monitoring-oracle-goldengate-processing.html#GUID-59E61274-BDDE-4D4B-9681-ED0BC39E9FCF

Deployment Console のトップ画面から 構成 ⇒ データベース(サブタブ) ⇒ Source のハンバーガーボタンと遷移して、まずは Sourceデータベースに接続します。

ハートビートからハートビート表の更新頻度、保存期間、パージ頻度を設定して発行ボタンをクリックします。
この時に Target Only のチェックは解除して下さい。

OCIGG016.jpg

次に Deployment Console のトップ画面から 構成 ⇒ データベース(サブタブ) ⇒ Source のハンバーガーボタンと遷移して、まずは Targetデータベースに接続します。

ハートビートからハートビート表の保存期間、パージ頻度を設定して発行ボタンをクリックします。この時に Target Only のチェックは設定しておいて下さい。

OCIGG017.jpg

10. Extract の作成

Deployment Console のトップ画面から Extract を作成します。Extract の +ボタン をクリックします。 OCIGG020.jpg

統合Extract を選択して次へをクリックします。

OCIGG021.jpg

以下を入力します。変更する部分のみをピックアップしています。

変数名 入力値
プロセス名 任意の文字列ただし8文字まで
トレイル名 2文字のPREFIXを入力
資格証明ドメイン OracleGoldenGate を選択
資格証明別名 Source DB の Alias を選択
共有 11.2.0.4 は統合ExtractのShareに対応していないため なし を選択

OCIGG022.jpg

OCIGG023.jpg

Extract のパラメータは以下のように設定します。GoldenGate は色々と多機能でここの設定も色々有るのですが、今回はシンプルに構成します。

EXTRACT EXT99
USERIDALIAS dbcsseogays DOMAIN OracleGoldenGate
EXTTRAIL tr
TABLE AYSHIBAT.*;

OCIGG024.jpg

作成および実行をクリックして、正常に終了すると Extractプロセス が作成＆起動します。

OCIGG025.jpg

11. Replicat の作成

Deployment Console のトップ画面から Replicat を作成します。Replicat の +ボタン をクリックします。 OCIGG030.jpg

Replicatプロセスの種別を選択します。色々あるのですが今回は 非統合Replicat を選択して次をクリックします。 OCIGG031.jpg

以下を入力します。変更する部分のみをピックアップしています。

変数名 入力値
プロセス名 任意の文字列ただし8文字まで
資格証明ドメイン OracleGoldenGate を選択
資格証明別名 Target DB の Alias を選択
トレイル名 Extract に設定した 2文字のPREFIXを入力
チェックポイント表 上記8. で作成したチェックポイント表を選択

OCIGG032.jpg

Replicat のパラメータは以下のように設定します。やはり色々と設定はあるのですが、今回はここもシンプルに構成します。

REPLICAT REP99
USERIDALIAS dbcseeogays DOMAIN OracleGoldenGate
MAP AYSHIBAT.*, TARGET AYSHIBAT.*;

OCIGG033.jpg

作成および実行をクリックして、正常に終了すると Replicatプロセス が作成＆起動します。

OCIGG034.jpg

12. レプリケーションの確認(INSERT, UPDATE, DELETE, 統計, ラグ)

レプケーションを確認します。まず Source側 でデータを INSERT してみます。

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
se01

SQL> INSERT INTO TEST_TBL01 SELECT LEVEL, 'REC' || LEVEL FROM DUAL CONNECT BY LEVEL <= 10;

10 rows created.

SQL> COMMIT;

Commit complete.

Target側 で対象テーブルを SELECT してみます。

SQL> SHOW CON_NAME;

CON_NAME
------------------------------
PDB1
SQL> SELECT * FROM TEST_TBL01;

        ID NAME
---------- --------------------
         1 REC1
         2 REC2
         3 REC3
         4 REC4
         5 REC5
         6 REC6
         7 REC7
         8 REC8
         9 REC9
        10 REC10

10 rows selected.

レプリケーションされてるやで！彡(^)(^)

UPDATE や DELETE も Source側 で実行してみます。

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
se01

SQL> UPDATE TEST_TBL01 SET NAME = 'XXXXXXXX' WHERE ID = 10;

1 row updated.

SQL> DELETE FROM TEST_TBL01 WHERE ID = 9;

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> SHOW CON_NAME;

CON_NAME
------------------------------
PDB1
SQL> SELECT * FROM TEST_TBL01;

        ID NAME
---------- --------------------
         1 REC1
         2 REC2
         3 REC3
         4 REC4
         5 REC5
         6 REC6
         7 REC7
         8 REC8
        10 XXXXXXXX

9 rows selected.

UPDATE や DELETE もレプリケーションされていますやね彡(^)(^)

Deployment Console の 統計タブ からも件数レベルでの伝播状況を確認できます。 OCIGG040.jpg

Deployment Console の ハートビート からはラグ(伝播遅延)の状況を確認可能です。 OCIGG041.jpg

まとめ

OCI GoldenGate(OCI GG) で DBCS のデータをレプリケーションできたやで！彡(^)(^)
OCI GoldenGate は従量課金で利用可能なので、皆さんどんどん使ってくださいね。

Autonomous Database で SQLトレース を採取してみる。(Oracle Cloud Infrastructure)

Oracle Database Cloud OCI

いつの間にか Autonomous Database で SQLトレース を採取できるようになったので、下記のマニュアルを見ながらやってみます。
彡(ﾟ)(ﾟ)

Autonomous DatabaseでのSQLトレースの実行
https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbsa/application-tracing.html#GUID-25A5160B-C72A-4897-9CC9-0BE23EA7EC01

1. Object Storage Bucket作成/IAMユーザー作成/ポリシー付与/Auth Token生成/クレデンシャル登録

詳細は本記事では省略します。下記記事を参照して下さい。

Object Storage にアップロードしたファイルを Autonomous Database の DATA_PUMP_DIR にコピーする。(Oracle Cloud Infrastructure)
https://qiita.com/ora_gonsuke777/items/3e8b63b1d878c7fe343e

2. DATABASE PROPERTY の設定(DEFAULT_LOGGING_BUCKET, DEFAULT_CREDENTIAL)

上記 1. で作成した Object Storage Bucket や クレデンシャル を DATABASE PROPERTY として登録します。

SET DEFINE OFF;
ALTER DATABASE PROPERTY SET 
   DEFAULT_LOGGING_BUCKET = 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/xxxxxxxxxxxxxx/b/ayu-bucket1/o/';

ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.DEF_CRED_NAME2';

3. SQLトレースの採取

まずは IDENTIFIER名 と MODULE名 をセットします。これらはオプションですが SQLトレース の ファイル名 を特定するのに便利です。

BEGIN
  DBMS_SESSION.SET_IDENTIFIER('ayu_sqlt');
END;
/

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('test1', null);
END;
/

SQLトレースをセットします。

ALTER SESSION SET SQL_TRACE = TRUE;

トレースをセットした状態で、性能を測定したい SQL を実行します。
ダミーデータをセットアップして下記SQLを実行してみました。

SELECT /*+ MONITOR
           NO_VECTOR_TRANSFORM */
       /* AYSHIBAT */
       B.ITEM_NAME
     , TRUNC(A.ORDER_DATE, 'DD') AS ORDER_DAILY
     , COUNT(*)
  FROM ORDER_TBL A
     , ITEM_TBL B
 WHERE A.ITEM_NO  = B.ITEM_NO
--   AND B.ITEM_NO BETWEEN 1 AND 100
   AND TO_CHAR(B.REGIST_DATE, 'YYYYMMDD') = '20120801'
 GROUP BY B.ITEM_NAME, TRUNC(A.ORDER_DATE, 'DD')
 ORDER BY 1;

SQLトレースを解除します。

ALTER SESSION SET SQL_TRACE = FALSE;

4. Object Storage Bucket の SQLトレースの確認とダウンロード

Object Storage の Bucket に出力された SQLトレース を確認してダウンロードします。 autonomous_sqltrace01.jpg

5. SQLトレース の tkprof による整形と確認

Autonomous Database は OS にはログインできないため、
SQLトレースを整形する場合は別環境で実施する必要があります。

対応するバージョンの tkprofコマンドがセットアップされていれば環境は問わないのですが、
今回は下記の VirtualBox環境 で実施してみました。

OTN の VirtualBoxイメージ で Oracle DB 19c環境 を 楽々構築
https://qiita.com/ora_gonsuke777/items/b41f37637e59319796b4

採取した SQLトレース を VirtualBox の 仮想OS にアップロードして、tkprofコマンド で整形して確認します。

export ORACLE_HOME=/u01/app/oracle/product/version/db_1
export PATH=${PATH}:${ORACLE_HOME}/bin
tkprof sqltrace_ayu_sqlt_test1_sqltrace_21569_30852.trc sqltrace_ayu_sqlt_test1_sqltrace_21569_30852.trc.txt
view sqltrace_ayu_sqlt_test1_sqltrace_21569_30852.trc.txt

下記のように整形後のSQLトレースを確認できました。

：
SELECT /*+ MONITOR
           NO_VECTOR_TRANSFORM */
       /* AYSHIBAT */
       B.ITEM_NAME
     , TRUNC(A.ORDER_DATE, 'DD') AS ORDER_DAILY
     , COUNT(*)
  FROM ORDER_TBL A
     , ITEM_TBL B
 WHERE A.ITEM_NO  = B.ITEM_NO
--   AND B.ITEM_NO BETWEEN 1 AND 100
   AND TO_CHAR(B.REGIST_DATE, 'YYYYMMDD') = '20120801'
 GROUP BY B.ITEM_NAME, TRUNC(A.ORDER_DATE, 'DD')
 ORDER BY 1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       19      0.48       0.49          0       9177          0         261
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       21      0.48       0.49          0       9177          0         261

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 157
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
       261        261        261  SORT GROUP BY (cr=9177 pr=0 pw=0 time=496578 us starts=1 cost=30 size=26 card=1)
     26000      26000      26000   HASH JOIN  (cr=9177 pr=0 pw=0 time=490431 us starts=1 cost=29 size=26 card=1)
   2600000    2600000    2600000    JOIN FILTER CREATE :BF0000 (cr=9086 pr=0 pw=0 time=148913 us starts=1 cost=3 size=286 card=26)
   2600000    2600000    2600000     TABLE ACCESS STORAGE FULL ORDER_TBL (cr=9086 pr=0 pw=0 time=69464 us starts=1 cost=3 size=286 card=26)
        10         10         10    JOIN FILTER USE :BF0000 (cr=91 pr=0 pw=0 time=8325 us starts=1 cost=26 size=4500 card=300)
        10         10         10     TABLE ACCESS STORAGE FULL ITEM_TBL (cr=91 pr=0 pw=0 time=7935 us starts=1 cost=26 size=4500 card=300)

一番時間が掛かっていそうなのは、下位Operattion との time値 のギャップが大きい HASH JOIN の部分ですかね～～。
彡(ﾟ)(ﾟ)

6. まとめ

Autonomous Database で SQLトレース を採取して、他環境で整形できるのを確認できました。
現時点(2022/2/28)で待機イベントやバインド変数が取得できないのが残念ですが、今後に期待や！彡(^)(^)