ねら~ITエンジニア雑記

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

DDLトリガー と イベント属性ファンクション で CREATE されるオブジェクトのオーナー名とオブジェクト名を記録する。(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)

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)

直近はお仕事で 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)

いつの間にか 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 BucketSQLトレースの確認とダウンロード

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)で待機イベントやバインド変数が取得できないのが残念ですが、今後に期待や!彡(^)(^)

CMAN(Oracle Connection Manager) と ロールベース・サービス/NLB で OCI DBCS Data Guard構成 の Primary接続 をシングル・エンドポイント化してみる。(Oracle Database, Oracle Cloud Infrastructure)

本記事は JPOUG Advent Calendar 2021 および OCI Advent Calendar 2021 の 15日目 の記事となります。
彡(^)(^)

JPOUG Advent Calendar 2021
https://adventar.org/calendars/6527

Oracle Cloud Infrastructure Advent Calendar 2021
https://qiita.com/advent-calendar/2021/oci

CMAN(Oracle Connection Manager) は Oracle Database専用 のプロキシとして活用できる機能です。

10 Oracle Connection Managerの構成
https://docs.oracle.com/cd/F19136_01/netag/configuring-oracle-connection-manager.html#GUID-AF8A511E-9AE6-4F4D-8E58-F28BC53F64E4
Oracle Connection Managerは、データベースまたはプロキシ・サーバーに接続要求を転送するプロキシ・サーバーです。

本記事では CMAN と Data Guard のロールベース・サービス、NLB(Network Load Balancer)を使用して、
OCI DBCS の Data Guard構成 をシングル・エンドポイント化してみるやで彡(゚)(゚)

1. 過去記事の振り返り と やりたい事

本記事は下記記事の続編的な記事となります。

OCI DBCS VM で Data Guard を有効化して、ロールベース・サービスを構成して
PRIMARYロール に透過的に接続してみる。(Oracle Cloud Infrastructure)
https://qiita.com/ora_gonsuke777/items/7f705f29fb65050bf295

上記の構成でも Primaryロール への透過的な接続はできているのですが、
Client側で下記のような複雑な接続文字列を記載する必要があるのが欠点です。

AYUDBCS_PRIMARY2=(
  DESCRIPTION_LIST=
    (LOAD_BALANCE=OFF)(FAILOVER=ON)
    (
      DESCRIPTION=
        (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=5)(RETRY_DELAY=3)(RETRY_COUNT=3)(ENABLE=BROKEN)
        (ADDRESS_LIST=
          (ADDRESS=(PROTOCOL=TCP)(HOST=ayudbcs1-scan.ayuprvsubnet2.ayuvcn1.oraclevcn.com)(PORT=1521))
        )
        (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ayudbcs_primary2.ayuprvsubnet2.ayuvcn1.oraclevcn.com))
    )
    (
      DESCRIPTION=
        (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=5)(RETRY_DELAY=3)(RETRY_COUNT=3)(ENABLE=BROKEN)
        (ADDRESS_LIST=
          (ADDRESS=(PROTOCOL=TCP)(HOST=ayudbcs2-scan.ayuprvsubnet2.ayuvcn1.oraclevcn.com)(PORT=1521))
        )
        (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ayudbcs_primary2.ayuprvsubnet2.ayuvcn1.oraclevcn.com))
    )
)

前述の通り、本記事では CMAN/ロールベース・サービス/NLB で Data Guard の物理構成を隠蔽して、シングル・エンドポイント化してみます。シングル・エンドポイント化することで Client側の接続文字列がシンプルになります。

2. 接続トポロジー

以下の構成で PC から Data Guard の Primary に接続します。CMAN Compute が 2台 なのは
耐障害性を考慮して冗長化しているからですが、耐障害性が不要な場合は 1台 で問題ありません。
cman001.jpg

各OCIリソースの構成は以下の通りです。

Compute: E4 flex oCPU 1 * 2台(CMAN用)
OS : Oracle Linux 7.9
Oracle Client 19c(19.3, Not Instant Client, Custom Install)
DBCS : VM Standard2.2, EEHP, Version 19c(19.12), 1node * 2セット(Data Guard の Primary, Standby)
Network Load Balancer * 1台

3. OCI DBCS の Data Guard構成 と ロールベース・サービス の登録

本記事では詳細は省略します。下記記事の通りに OCI DBCS を Data Guard構成にして、さらに ロールベース・サービス を登録します。

OCI DBCS VM で Data Guard を有効化して、ロールベース・サービスを構成して
PRIMARYロール に透過的に接続してみる。(Oracle Cloud Infrastructure)
https://qiita.com/ora_gonsuke777/items/7f705f29fb65050bf295

#Primary で実行
srvctl add service -db ayucdb3_nrt1rt -service ayudbcs_primary -pdb ayupdb3 -role PRIMARY
#Standby で実行
srvctl add service -db ayucdb3_nrt1gh -service ayudbcs_primary -pdb ayupdb3 -role PRIMARY
#Primary でサービス開始
srvctl start service -db ayucdb3_nrt1rt -service ayudbcs_primary

#Service登録を確認
lsnrctl status
:
Service "ayudbcs_primary.ayuprvsubnet02.ayuvcn01.oraclevcn.com" has 1 instance(s).
  Instance "AYUCDB3", status READY, has 2 handler(s) for this service...
:

4. Oracle Client 19c のインストール(Not Instant Client、要カスタム・インストール)

CMAN Compute に Oracle Client 19c(Not Instant Cliet) をインストールします。
本記事では詳細は省略します。下記記事を参照して下さい。
カスタム・インストール で CMAN と Network Listener を導入する必要があるため、そこは注意して下さい。

Oracle Connection Managerのインストール
https://qiita.com/sa_to_ru13/items/c49366ee61afec045455

How to create a database link from Oracle Autonomous Database to a private on-premise DB
https://qiita.com/RexZheng/items/92fb55686fff9811bfbe

5. CMAN Compute の DB接続ポート開放(firewall-cmd)

CMAN Compute では CMANプロセスが起動して DB接続ポート(本記事では 1521) を
リスニングするため、OS の Firewall の ポート開放 を実施しておきます。
ワイはこれを失念していて3時間ほど無駄にした……彡(-)(-)

firewall-cmd --add-port=1521/tcp --zone=public --permanent
firewall-cmd --reload
firewall-cmd --list-all
:
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: ens3
  sources:
  services: dhcpv6-client ssh
  ports: 1521/tcp ★★ココ
:

両方の CMAN Compute でポート開放しておきます。

6. CMAN構成(cman.ora編集) と CMAN起動

cman.ora を編集して CMAN を構成します。前述の記事を参考にしています。

sudo su - oracle
cd /u01/app/oracle/product/19.0.0/client_1/network/admin
vi cman.ora

cman.ora の内容は以下の通りです。ほぼコピペで OK です。host の部分のみ Compute の ホスト名(FQDN) で修正します。

cman_listener = (configuration=
  (address=(protocol=tcp)(host=ayu-cman01.ayuprvsubnet02.ayuvcn01.oraclevcn.com)(port=1521))
  (parameter_list =
    (log_level=ADMIN)
    (max_connections=1024)
    (idle_timeout=0)
    (registration_invited_nodes = *)
    (inbound_connect_timeout=0)
    (session_timeout=0)
    (outbound_connect_timeout=0)
    (max_gateway_processes=16)
    (min_gateway_processes=2)
    (remote_admin=on)
    (trace_level=off)
    (max_cmctl_sessions=4)
    (event_group=init_and_term,memory_ops)
  )
  (rule_list=
    (rule=
       (src=*)(dst=*)(srv=*)(act=accept)
    )
  )
)

以下のコマンドで CMAN を起動します。

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/client_1
export PATH=${ORACLE_HOME}/bin:${PATH}
export TNS_ADMIN=${ORACLE_HOME}/network/admin
cmctl startup -c cman_listener
:
CMCTL for Linux: Version 19.0.0.0.0 - Production on 12-DEC-2021 12:10:20

Copyright (c) 1996, 2019, Oracle.  All rights reserved.

Current instance cman_listener is not yet started
Connecting to (DESCRIPTION=(address=(protocol=tcp)(host=ayu-cman01.ayuprvsubnet02.ayuvcn01.oraclevcn.com)(port=1521)))
Starting Oracle Connection Manager instance cman_listener. Please wait...
CMAN for Linux: Version 19.0.0.0.0 - Production
Status of the Instance
----------------------
Instance name             cman_listener
Version                   CMAN for Linux: Version 19.0.0.0.0 - Production
Start date                12-DEC-2021 12:10:20
Uptime                    0 days 0 hr. 0 min. 9 sec
Num of gateways started   2
Average Load level        0
Log Level                 ADMIN
Trace Level               OFF
Instance Config file      /u01/app/oracle/product/19.0.0/client_1/network/admin/cman.ora
Instance Log directory    /u01/app/oracle/diag/netcman/ayu-cman01/cman_listener/alert
Instance Trace directory  /u01/app/oracle/diag/netcman/ayu-cman01/cman_listener/trace
The command completed successfully.

CMAN の稼働状況は cmctl show status および cmctl show services で確認できます。

cmctl show status -c cman_listener
cmctl show services -c cman_listener

停止は cmctl shutdown となります。

cmctl shutdown -c cman_listener

7. DBCS の tnsnames.ora編集 および REMOTE_LISTENER編集、サービス登録

DBCS の tnsnames.ora を編集して、CMAN のエントリーを追加します。

sudo su - oracle
cd $ORACLE_HOME/network/admin
vi tnsnames.ora

下記が追加するエントリーとなります。CMAN のリスニングポイント(host名 or IP + ポート番号)をセットします。

CMAN01 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ayu-cman01.ayuprvsubnet02.ayuvcn01.oraclevcn.com)(PORT = 1521))

CMAN02 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ayu-cman02.ayuprvsubnet02.ayuvcn01.oraclevcn.com)(PORT = 1521))

tnsnames.ora にエントリーを追加したら、REMOTE_LISTENERパラメータを編集してエントリを登録&有効化します。

sqlplus /nolog
CONNECT /AS SYSDBA
ALTER SYSTEM SET REMOTE_LISTENER="CMAN01,CMAN02" SCOPE=BOTH SID='*';
ALTER SYSTEM REGISTER;
SHOW PARAMETER REMOTE_LISTENER

System altered.
System altered.

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_listener                      string      CMAN01,CMAN02

上記を Primary, Standby の両方で実行しておいて下さい。
上手く行けば REMOTE LISTENER相当の CMAN にDBサービスが登録されます。

DBサービスの登録状況を CMAN側 で確認します。

sudo su - oracle
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/client_1
export PATH=${ORACLE_HOME}/bin:${PATH}
export TNS_ADMIN=${ORACLE_HOME}/network/admin
cmctl show status -c cman_listener
cmctl show services -c cman_listener
:
Service "ayudbcs_primary.ayuprvsubnet02.ayuvcn01.oraclevcn.com" has 1 instance(s).
  Instance "AYUCDB3", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.82)(PORT=1521)))
:

CMAN に ロールベース・サービス が登録されているのが確認できます。

8. NLB(Network Load Balancer)の構成

本記事では詳細は省略します。下記記事を参照して NLB(Network Load Balancer) の Backends に CMAN Compute をセットして下さい。

Private Subnet に作成した DBCS に Public Subnet の Network Load Balancer経由 で接続してみる。(Oracle Cloud Infrastructure)
https://qiita.com/ora_gonsuke777/items/e20357387dfa6f12d2b7

今回は以下のように NLB の Backends として CMAN Compute を構成しています。

cman002.jpg

9. クライアント(PC)からの接続(SQL*Plus, 簡易接続)

クライアント(PC)から接続してみます。まず PowerShell の sqlplus(Instant Client) で接続してみます。

簡易接続の ホスト名(or IP):ポート番号/サービス名 で接続文字列を指定します。
ホスト名の部分は Network Load Balancer の Public IP を指定して下さい。

$env:ORACLE_HOME = "C:\tools\OracleInstantClient19c"
cd C:\tools\OracleInstantClient19c
./sqlplus /nolog

CONNECT AYSHIBAT/xxxxxxxx@1xx.1xx.1xx.1xx:1521/ayudbcs_primary.ayuprvsubnet02.ayuvcn01.oraclevcn.com

SET LINESIZE 120;
COLUMN HOST_NAME FORMAT A40;
COLUMN INSTANCE_NAME FORMAT A40;
SELECT HOST_NAME, INSTANCE_NAME FROM V$INSTANCE;
SELECT DB_UNIQUE_NAME FROM V$DATABASE;

Connected.

HOST_NAME                                INSTANCE_NAME
---------------------------------------- ----------------------------------------
ayudbcs3                                 AYUCDB3

SQL> SELECT DB_UNIQUE_NAME FROM V$DATABASE;

DB_UNIQUE_NAME
------------------------------
AYUCDB3_nrt1rt

上手く接続できました。OCI Console の Data Guard Association で Switchover してみます。 cman003.jpg

Switchover完了後に再度同じ接続を試みます。

$env:ORACLE_HOME = "C:\tools\OracleInstantClient19c"
cd C:\tools\OracleInstantClient19c
./sqlplus /nolog

CONNECT AYSHIBAT/xxxxxxxx@1xx.1xx.1xx.1xx:1521/ayudbcs_primary.ayuprvsubnet02.ayuvcn01.oraclevcn.com

SET LINESIZE 120;
COLUMN HOST_NAME FORMAT A40;
COLUMN INSTANCE_NAME FORMAT A40;
SELECT HOST_NAME, INSTANCE_NAME FROM V$INSTANCE;
SELECT DB_UNIQUE_NAME FROM V$DATABASE;

Connected.

HOST_NAME                                INSTANCE_NAME
---------------------------------------- ----------------------------------------
ayudbcs4                                 AYUCDB3

SQL> SELECT DB_UNIQUE_NAME FROM V$DATABASE;

DB_UNIQUE_NAME
------------------------------
AYUCDB3_nrt1gh

旧Standby(Switchover後のPrimary)に接続できてるで!彡(^)(^)

10. クライアント(PC)からの接続(SQL Developer, JDBC URL, 簡易接続)

次はクライアント(PC)の SQL Develper から接続してみます。
JDBC URL(JDBC接続文字列)に下記の簡易接続文字列を指定します。

jdbc:oracle:thin:@//NLBのPublic IP:ポート番号/ロールベース・サービス名

カスタムJDBC URL を下記のようにセットします。 cman004.jpg

接続後のSQL実行結果は下記の通りとなります。 cman005.jpg

再度Switchoverして、再接続&SQLを再実行してみます。 cman006.jpg

切り替わり後のPrimaryインスタンスに上手く接続できているで!彡(^)(^)

11. 注意事項&改善ポイント

まず CMAN を手動起動しているため、これは OS起動後 に自動起動するように
systemd に登録した方が良いです。今回は力尽きたq^

また Network Load Balancer が SPOF となります。NLB は自己回復性は有りますが
SLA は 100% ではないため、ここの耐障害性を高めたい場合は NLB も冗長化するのが推奨となります。

12. まとめ

DBCS(Oracle Database) の Data Guard構成を抽象化してシングル・エンドポイント化できました。

本記事では Primaryロール に接続していますが、ロールベース・サービス は Standby側 にも
設定可能なので、Active Data Guard の Read Only Standby側に接続する……と云った構成も可能です。

構成はまあまあ面倒なので、CMAN を Managed な Service として提供して貰いたいですやね。
とまれ、皆さんどんどん活用して下さいね。彡(^)(^)

Private Subnet に作成した DBCS に Public Subnet の Network Load Balancer経由 で接続してみる。(Oracle Cloud Infrastructure)

表題の通り Private Subnet に作成した DBCS に、Public Subnet の NLB経由 で接続してみるやで。
彡(゚)(゚)

1. 接続トポロジー/検証環境

接続トポロジーは以下の通りです。

PC -> (Internet) -> NLB(Public Subnet) ┬-> RAC #1(Private Subnet)
                        └-> RAC #2(Private Subnet)

検証環境は下記記事の構成を使います。

検証によく使う Oracle Cloud Infrastructure(OCI) の 環境構成
(VCN, Seculity List, 各種Gateway, Route Table, Subnet, Compute)まとめ
https://qiita.com/ora_gonsuke777/items/08e4781af8c4f7e114ff

2. DBCS(EEHP, RAC) の作成

詳細は省略します。DBCS RAC(EEEP) を Private Subnet に作成します。 NLB001.jpg

作成後に DBノード の 物理IP を確認します。 NLB002.jpg

3. NLB(Network Load Balancer) の作成

作成する LB は NLB を選択します。 NLB003.jpg

LB名は任意、Visibility Type は Public、Public IP は Ephemeral、VCN と Subnet は予め作成しておいたものを選択します。 NLB004.jpg

Listener名は任意、プロトコルTCP、ポートは1521を指定します。 NLB005.jpg

Backends名は任意、この時点で Backend Servers は指定せず、Health Check のポートは TCP/1521 に変更します。 NLB006.jpg

レビュー画面になるので、Submitボタンを押します。 NLB007.jpg

作成が終わりましたが、この時点では Backend Server が無いので追加で設定します。 NLB008.jpg

Backend Sets の画面に遷移して Add Backends ボタンを押します。 NLB009.jpg

RAC DBノード #1 の 物理IP を指定して追加します。 NLB010.jpg

RAC DBノード #2 の 物理IP も追加します。 NLB011.jpg

上手く行くと Backends の Health が OK になります。 NLB012.jpg

4. PC から NLB経由 で DBCS に接続

まず Pluggable Database の画面に遷移して、PDB のサービス名を確認します。 NLB013.jpg

PC から NLB経由 で DBCS に接続します。今回は SQL Developer を使用彡(゚)(゚)

ホスト名:NLB の Public IP Address
ポート :1521
サービス名:上記で確認した PDB のサービス名

NLB014.jpg

テストに成功したので接続して SQL を実行すると……。 NLB015.jpg

上手くいきました。

5. 注意事項

Backend Sets の IP に RAC DBノード の 物理IP を指定しているので、Oracle MAA には沿っていません。
今回の要件で Oracle MAA準拠するには Compute に CMAN(Connection Manager) を構成して、
NLB の Backends に CMAN を2台以上配置して NLB⇒CMAN経由でDBに接続するのがエエかと思います(未検証)
彡(゚)(゚)

5.6 Oracle Connection Managerのアーキテクチャの理解
https://docs.oracle.com/cd/F19136_01/netag/understanding-oracle-net-architecture.html#GUID-AAEEC927-9A69-4012-AC2C-8423274F94C6
 
(※CMANの参考記事) How to create a database link from Oracle Autonomous Database to a private on-premise DB
https://qiita.com/RexZheng/items/92fb55686fff9811bfbe

6. まとめ

注意事項があるとは云え NLB構成による Public接続化 はお手軽です。
セキュリティに留意して、ご活用下さいね彡(^)(^)

Autonomous Database(ATP) に Java(JDBC Thin Driver) で ウォレット無しで接続してみる。

Autonomous Database(ATP/ADW/AJSON) は、従来は Autonomous Database自体が持つウォレットのファイル群を
クライアント側に配置する必要がありましたが、ウォレット無しのTLS接続を許可するオプションが追加されました。

Update Network Options to Allow TLS or Require Only Mutual TLS (mTLS) Authentication on Autonomous Database
https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/support-tls-mtls-authentication.html#GUID-3F3F1FA4-DD7D-4211-A1D3-A74ED35C0AF5

About TLS Authentication
https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/connect-introduction.html#GUID-5ACCC8FF-7637-48F6-8FE4-049A720AC275
If the client is connecting with JDBC Thin using TLS authentication, the client can connect without providing a wallet.

今回はこの追加された機能を使用して、ウォレット無しで Autonomous Database に接続してみるやで。
彡(゚)(゚)

1. Autonomous Database(ATP) の作成

まず Autonomous Database を作成します。ウォレット無しのTLS接続 は ADB の
ACL(Access Control List) による接続制限を行うか、プライベートエンドポイントで作成する必要があります。

Update Network Options to Allow TLS or Require Only Mutual TLS (mTLS) Authentication on Autonomous Database
https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/support-tls-mtls-authentication.html#GUID-3F3F1FA4-DD7D-4211-A1D3-A74ED35C0AF5
If the instance is configured to operate over the public internet,
then one or more Access Control Lists (ACLs) must be defined before you allow TLS authentication.

If the instance is configured with a private endpoint, then you can allow TLS authentication.

今回は下記のトポロジーで Private Endpoint に ATP を作成します。 NoWallet001.jpg

2. Mutual TLS(mTLS)認証必須の無効化

DB作成後、Autonomous Database のコンソールからmTLS認証必須を無効化します。 NoWallet002.jpg NoWallet003.jpg

3. TLS接続の接続文字列確認

DB Connection ⇒ TLS(プルダウン) で TLS接続 の接続文字列を確認します。
従来の mTLS はポート番号が 1522 なのですが、TLS接続は 1521 となるもよう。
NoWallet004.jpg

事前定義サービスは幾つかあるのですが、今回は TP接続 を選択します。彡(゚)(゚)

4. Instant Client(jdbc) の yum install(※オプション)

Compute に Instant Client を yum install して JDBC Thin Driver をゲットしておきます。
既に JDBC Thin Driver が有る場合は省略して構いません。下記では無駄に sqlplus をインストールしていたり…。

sudo su -

cd /etc/yum.repos.d
wget http://yum.oracle.com/public-yum-ol7.repo

yum install -y yum-utils
yum-config-manager --enable ol7_oracle_instantclient

yum list | grep -i oracle-instant

yum install oracle-instantclient-basic.x86_64
yum install oracle-instantclient-sqlplus.x86_64
yum install oracle-instantclient-jdbc.x86_64
yum install oracle-instantclient-tools.x86_64

export ORACLE_HOME=/usr/lib/oracle/21/client64
export PATH=${ORACLE_HOME}/bin:${PATH}
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib

sqlplus /nolog

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Oct 30 06:38:42 2021
Version 21.3.0.0.0

5. JDKダウンロードと展開(※オプション)

Compute に JDK をダウンロードして展開します。既にJDKインストール済みの場合は省略して構いません。
今回は JDK17 をダウンロードして展開します。生の wget で tar.gz の JDK をダウンロードできる…!
彡(゚)(゚)

mkdir work
cd work
wget https://download.oracle.com/java/17/latest/jdk-17_linux-x64_bin.tar.gz
tar xvzf latest/jdk-17_linux-x64_bin.tar.gz
cd jdk-17.0.1
cd bin
./java -version

java version "17.0.1" 2021-10-19 LTS
Java(TM) SE Runtime Environment (build 17.0.1+12-LTS-39)
Java HotSpot(TM) 64-Bit Server VM (build 17.0.1+12-LTS-39, mixed mode, sharing)

6. サンプルのソースコード

ソースコードは以前の記事のものを流用します。

Autonomous DB(ADW/ATP) に JavaJDBC Thin Driver で接続してみる。(OCI, Oracle Cloud Infrastructure)
https://qiita.com/ora_gonsuke777/items/91ec0e15848a78ede385

接続文字列部分には 3. で確認した接続文字列をセットします。
接続文字列内のダブルクォートは \ でエスケープしておきましょう。

import java.sql.*;

public class GetContainerName {
    public static void main(String[] args) {
        final String path = "jdbc:oracle:thin:@(description=(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=xxxxxxxx.adb.ap-tokyo-1.oraclecloud.com))(connect_data=(service_name=xxxxxxxxxxxx_ayuatp1_tp.adb.oraclecloud.com))(security=(ssl_server_cert_dn=\"CN=adb.ap-tokyo-1.oraclecloud.com, OU=Oracle ADB TOKYO, O=Oracle Corporation, L=Redwood City, ST=California, C=US\")))";
        final String id = "ADMIN";  //ID
        final String pw = "xxxxxxxx";  //password

        try (
            Connection conn = DriverManager.getConnection(path, id, pw);
            Statement  stmt = conn.createStatement();
            ResultSet  rs   = stmt.executeQuery("SELECT NAME FROM V$CONTAINERS");
        ) {
            while (rs.next()) {
                String cn = rs.getString("name");
                System.out.println("Container Name => " + cn);
            }
        } catch(SQLException ex) {
            ex.printStackTrace();  //Error
        }
    }
}

7. コンパイルと実行

ソースをコンパイルして実行します。漢の 生javac に 生java実行……。

export JAVA_HOME=/home/opc/work/jdk-17.0.1
export PATH=${JAVA_HOME}/bin:${PATH}

javac GetContainerName.java

java -classpath /usr/lib/oracle/21/client64/lib/ojdbc8.jar:. GetContainerName

Container Name => xxxxxxxxxxxxxx_AYUATP1

ウォレット無しでも ADB(ATP) への接続が成功して、コンテナ名が取得できました。

8. まとめ

幾つか事前準備は有りますが、ウォレット無しでも Autonomous Database に接続できるようになりました。
ウォレットの管理やローテション等の手間が無くなり、より使いやすくなっています。

Autonomous Database は Exadata のリソースを安価に使用できるので、皆さん活用して下さいね。
彡(^)(^)