Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@TakuyaAma(A Takuya)in日本オラクル株式会社

Autonomous DatabaseとDBCS間のDBLINK（Private IPを利用）の自分用メモ

Last updated at Posted at 2021-04-21

#■この記事で記載すること
Private subnet上に足を出しているAutonomous Database（ADB）とDatabase Cloud service（DBCS）間で、DBLINK接続するフローを自分用メモとして記載します。

基本的なフローはADBからADBへのDBLINK接続（VCN:Private IPアドレスを利用）の記事をベースに、以下を追加するだけです。

  • DBLINKのターゲットDBを、SSL(TCPS)認証できるようにデータベースを構成する

##●システム構成イメージ

ADB（ローカル）--[TCPS] --> 中継サーバ --[TCPS]--> DBCS（リモート）

##●システム構成説明

  • ADB（ローカル）とDBCS（リモート）を中継サーバを介してTPCS接続でDBLINK接続

##●設定例

  • 前提
  • ウォレットファイルの作成、配置
  • Oracle（リモート）のOracle Net設定
  • 中継サーバの設定
  • DBLINKの作成

###- 前提

  • DB（ADB/DBCS）がデプロイされている
  • 中継サーバ（標準のOracle Linux Imange）がデプロイされている ※
  • OCI CLIの準備が完了している

※ 本環境ではDB接続テストを円滑に進める目的で、Oracle Cloud Developer Imageを使いました。

###- ウォレットファイルの作成、配置

この記事を参考にウォレットファイルを作成のうえ、以下に配置する。

・クライアントウォレットファイル
Oracle(ローカル)の/u01/client/wallet/に配置

・サーバウォレットファイル
Oracle(リモート)の/u01/server/wallet/に配置

###- Oracle（リモート）のOracle Net設定

  • ネットワーク(sqlnet.ora/listener.ora)ファイルにウォレットの場所を追加

grid userでsqlnet.oraを編集

  • wallet_locationを追加
  • SSL設定の有効化
$ORACLE_HOME/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/19.0.0.0/grid/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

wallet_location =
 (SOURCE=
  (METHOD=File)
  (METHOD_DATA=
   (DIRECTORY=/u01/server/wallet)))
 
SSL_SERVER_DN_MATCH=(ON)

grid userでlistener.oraを編集

  • wallet_locationを追加
$ORACLE_HOME/network/admin/listener.ora
wallet_location =
 (SOURCE=
  (METHOD=File)
  (METHOD_DATA=
   (DIRECTORY=/u01/server/wallet)))

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON               # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent
  • TCPSエンドポイントをDB listenerに構成

変更前のlistener構成を確認

$ sudo su - grid
$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-APR-2021 17:23:59

Copyright (c) 1991, 2020, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                16-APR-2021 07:31:44
Uptime                    0 days 9 hr. 52 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19.0.0.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/dbcs0502/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.136)(PORT=1521)))
Services Summary...
Service "+APX" has 1 instance(s).
  Instance "+APX1", status READY, has 1 handler(s) for this service...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_RECO" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "dbcs0502XDB.subnet.vcn05.oraclevcn.com" has 1 instance(s).
  Instance "dbcs0502", status READY, has 1 handler(s) for this service...
Service "dbcs0502_iad1fq.subnet.vcn05.oraclevcn.com" has 1 instance(s).
  Instance "dbcs0502", status READY, has 2 handler(s) for this service...
The command completed successfully

grid userでTCPSエンドポイントの変更

$ srvctl modify listener -p "TCP:1521/TCPS:1522"
$ srvctl stop listener
$ srvctl start listener
$ srvctl stop database -database dbcs0502_iad1fq #11gの場合は、-databaseの代わりに-dを用いる
$ srvctl start database -database dbcs0502_iad1fq
$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-APR-2021 02:59:33

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                17-APR-2021 01:06:13
Uptime                    0 days 1 hr. 53 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19.0.0.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/dbcs0502/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=10.0.1.136)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.136)(PORT=1521)))
Services Summary...
Service "+APX" has 1 instance(s).
  Instance "+APX1", status READY, has 1 handler(s) for this service...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_RECO" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "dbcs0502XDB.subnet.vcn05.oraclevcn.com" has 1 instance(s).
  Instance "dbcs0502", status READY, has 1 handler(s) for this service...
Service "dbcs0502_iad1fq.subnet.vcn05.oraclevcn.com" has 1 instance(s).
  Instance "dbcs0502", status READY, has 2 handler(s) for this service...
The command completed successfully

###- 中継サーバの設定

  • 中継サーバのport forwardingを設定

ADBSのIPアドレスとDBCS0502のPrivate IPアドレスを用いてFirewallを設定

sudo firewall-cmd --add-rich-rule='rule family=ipv4 \
source address="(ADBのOutbound IPアドレス)" \
forward-port to-addr="(DBCSのPrivate IPアドレス)" to-port="1522" \
protocol="tcp" port="1522"'
sudo firewall-cmd --add-masquerade
sudo firewall-cmd --runtime-to-permanent
  • 設定情報の確認
$ sudo firewall-cmd --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: ens3
  sources:
  services: dhcpv6-client ssh
  ports:
  protocols:
  masquerade: yes
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:
        rule family="ipv4" source address="(ADBのOutbound IPアドレス)" forward-port port="1522" protocol="tcp" to-port="1522" to-addr="(DBCSのPrivate IPアドレス)"

###- DBLINKの作成

  • Object Storage接続用クレデンシャルの登録
  • DBLINK設定用ディレクトリの作成
  • クライアントウォレットを登録
  • DBLINKを設定

クレデンシャルの登録

オブジェクトストレージ接続用のクレデンシャルの登録
Adminでsqlclを用いてADBに接続

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DBCS0502_OS_CRED',
    username => 'oracleidentitycloudservice/hoge@oracle.local',
    password => 'xxxxxxxxxxxxxxx'
  );
END;
/

DBLINK用のクレデンシャルの登録

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DBCS0502_DBLINK_CRED',
    username => 'ADMIN',
    password => '(PASSWORD)'
  );
END;
/

DBLINK設定用ディレクトリの作成

CREATE DIRECTORY DBCS0502_DBLINK_DIR AS 'DBCS0502_DBLINK_DIR';

クライアントウォレットを登録

Object StorageにBucketを作成のうえ、クライアントウォレットファイルをアップロード

oci os bucket create --name dbcs0502_dblink
oci os object put -bn dbcs0502_dblink --file /u01/client/wallet
/cwallet.sso

oci os object list -bn dbcs0502_dblink

クライアントウォレットファイルの取り込み

BEGIN 
     DBMS_CLOUD.GET_OBJECT(
	credential_name => 'DBCS0502_OS_CRED',	
        object_uri => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idqyzd41fwn7/b/dbcs0502_dblink/o/cwallet.sso',
        directory_name => 'DBCS0502_DBLINK_DIR'); 
END;
/

DBLINKを設定

BEGIN
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name => 'DBCS0502_DBLINK', 
          hostname => 'W.X.Y.Z', 
          port => '1522',
          service_name => 'dbcs0502_iad1fq.subnet.vcn05.oraclevcn.com',
          ssl_server_cert_dn => 'CN=dbcs',
          credential_name => 'DBCS0502_DBLINK_CRED',
          directory_name => 'DBCS0502_DBLINK_DIR');
END;
/

接続の確認

SELECT * FROM dual@DBCS0502_DBLINK;

参考情報

環境

本記事は、以下の環境で構成

種別 NW スペック DBバージョン
ADB:ADW VCN(Private) 1ocpu/1tb 19c
DBCS Private Compute2.2/256gb 11.2.0.4.210119
中継ノード Public VM.Standard.E2.2 -

資料

Documentations

Blogs

DBMS_CLOUDパッケージ関連

Credential

credential登録

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'hoge_CRED',
    username => 'foo',
    password => 'baa'
  );
END;
/

credential削除

BEGIN
  DBMS_CLOUD.DROP_CREDENTIAL(
    credential_name => 'DBCS0502_OS_CRED'
  );
END;
/

登録されたクレデンシャルの確認

SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('DBCS0502_OS_CRED', 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idqyzd41fwn7/b/dbcs0502_dblink/o/');

DBLINK

DBLINK用ディレクトリの作成

CREATE DIRECTORY ADBD_DBLINK_DIR AS 'ADBD_DBLINK_DIR';

Walletファイルの取り込み

BEGIN 
     DBMS_CLOUD.GET_OBJECT(
	credential_name => 'DBCS0502_OS_CRED',	
        object_uri => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idqyzd41fwn7/b/dbcs0502_dblink/o/cwallet.sso',
        directory_name => 'DBCS0502_DBLINK_DIR'); 
END;
/

DBLINKの作成

BEGIN
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name => 'ADBD_DBLINK', 
          hostname => 'adb.us-ashburn-1.oraclecloud.com', 
          port => '1522',
          service_name => 'xxxxxxxxxxxx_adbd_medium.adb.oraclecloud.com',
          ssl_server_cert_dn => 'CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US',
          credential_name => 'ADBD_DBLINK_CRED',
          directory_name => 'ADBD_DBLINK_DIR');
END;
/

DBLINKの削除

BEGIN
     DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK(
          db_link_name => 'DBCS0502_DBLINK'
     );
END;
/
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?