LoginSignup
1
0

More than 1 year has passed since last update.

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

よく使うOracle Database LINK集とDatabase関連作業手順備忘録 自分用メモ

Last updated at Posted at 2021-04-21

■ Oracle Database

オフィシャル

Basic/CheatSheet

Distributed Database Management

Autonomous Database

- General

- データ連携

EAI/iPaaS : Oracle Integration Cloud/OIC

DB間リアルタイム連携/GoldenGate

ETL/ODI

- 可用性

- 性能

- 運用

- Version Up/移行

Converged Database

Handson

まとめサイト

学習に役立つサイト

■ Oracle Cloud Infrasturecture ： OCI

オフィシャル

Security

学習に役立つサイト

Tips

Database関連作業手順備忘録

Autonomous Database(ADB)

ADBへの接続

Autonomous databaseのIDを確認

$ oci db autonomous-database list

ウォレットファイルのダウンロード

$ oci db autonomous-database generate-wallet --autonomous-database-id ocid1.autonomousdatabase.oc1.iad.xxxxxx --file /home/oracle/Wallet/Wallet_ADBS-Private.zip --password <your password>

ウォレットファイルを用いてSQL接続

$ sql /nolog
SQL> set cloudconfig /home/oracle/Wallet/Wallet_ADBS-Private.zip
Operation is successfully completed.
Operation is successfully completed.
Using temp directory:/tmp/oracle_cloud_config6589439447521943899

SQL> connect admin/WelCome123#123#@adbs_high

クレデンシャルの登録

credential登録 : ADB -> Object Storage

  • username: OCI User（IAM User）
  • Password: 認証トークン
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OS_CRED',
    username => 'foo',
    password => 'baa'
  );
END;
/

credential登録 : DBLINK（ADB -> DG4O/MySQL）

  • username: MySQL User
  • Password: MySQL Password
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'MySQL_CRED',
    username => 'root',
    password => 'WelCome123#123#'
  );
END;
/

credential削除

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

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

SELECT credential_name, username, comments FROM all_credentials;

ウォレットをbucketにアップロード

$ oci os object put -bn ADB_DBLIN_DG4O --file '/home/oracle/Wallet/client_wallet/cwallet.sso' --name cwallet.sso

確認

SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('OS_CRED', 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idqyzd41fwn7/b/ADB_DBLIN_DG4O/o/');
   OBJECT_NAME    BYTES                            CHECKSUM    CREATED                          LAST_MODIFIED
______________ ________ ___________________________________ __________ ______________________________________
cwallet.sso        6269 0b7425beae28120fab237db73e9cfbfd               20-APR-21 04.56.13.733000000 AM GMT

DBLINK : From ADB to Other Databases

CREATE DIRECTORY ADB_DBLINK_DG4O_DIR AS 'ADB_DBLINK_DG4O_DIR';

Walletファイルをディレクトリに取り込み

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

確認

SELECT * FROM DBMS_CLOUD.LIST_FILES('ADB_DBLINK_DG4O_DIR');

   OBJECT_NAME    BYTES    CHECKSUM                                CREATED                          LAST_MODIFIED
______________ ________ ___________ ______________________________________ ______________________________________
cwallet.sso        6269             20-APR-21 05.18.15.000000000 AM GMT    20-APR-21 05.18.15.000000000 AM GMT

GLOBAL_NAMESをFALSEに設定

SQL> ALTER SYSTEM SET GLOBAL_NAMES = FALSE;

System altered.

SQL> SHOW PARAMETER GLOBAL_NAMES
NAME                     TYPE        VALUE
----------------------   ----------- -----------
global_names             boolean     FALSE

DBLINKの作成

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

DBLINKの作成(Database Gateway for ODBC経由)
※ gateway_link => TRUEパラメータを追加する必要あり

BEGIN
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name => 'ADB_DBLINK_DG4O', 
          hostname => 'W.X.Y.Z', 
          port => '1522',
          service_name => 'DG4ODBC',
          ssl_server_cert_dn => 'CN=dbcs',
          credential_name => 'MySQL_CRED',
          directory_name => 'ADB_DBLINK_DG4O_DIR',
          gateway_link => TRUE);
END;
/

確認

SQL> select * from all_db_links;

DBLINKの削除

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

トラブった時の切り分け

分散環境向け機能

DBLINK

Sample

作成

SQL> CREATE PUBLIC DATABASE LINK DBLINK_DG4ODBC CONNECT TO "root" IDENTIFIED BY "WelCome123#123#" USING 'DG4ODBC';

削除

SQL> drop public database link DBLINK_DG4ODBC;

確認

set linesize 1000
col owner format a10
col db_link format a50
col username format a20
col host format a15

SQL> select * from all_db_links;

OWNER                          DB_LINK                                            USERNAME             HOST            CREATED
------------------------------ -------------------------------------------------- -------------------- --------------- ---------
SYS                            DG4ODBC.SUB04121421101.VCN05.ORACLEVCN.COM         root                 dg4odbc         19-APR-21
PUBLIC                         DBLINK_DG4ODBC.SUB04121421101.VCN05.ORACLEVCN.COM  root                 DG4ODBC         19-APR-21
PUBLIC                         DBLINK_DG4ODBCS.SUB04121421101.VCN05.ORACLEVCN.COM root                 DG4ODBCS        19-APR-21

Tips

  • global_names

global_namesが有効になっている場合は、データベースリンクが接続するデータベースと同じ名前を持つ必要があります。

SQL> SELECT VALUE FROM v$parameter WHERE NAME LIKE 'global_names';

VALUE
--------------------------------------------------------------------------------
TRUE

global_namesを使わない場合は、以下設定に変更する

SQL> alter system set global_names= false;

システムが変更されました。

Oracleウォレットファイルの作成

orapkiを使って署名付き証明書の作成、Oracleウォレットの管理
Autonomous Databaseから他のDBにDBLINK接続する場合、ウォレットファイルを作成のうえ・DBLINK接続サーバへの配布が必要となる。

Sample

自己署名付きウォレットの作成

ウォレットディレクトリの作成

$ sudo mkdir -p /u01/server/wallet
$ sudo mkdir -p /u01/client/wallet
$ sudo mkdir -p /u01/certificate
$ sudo chown -R oracle:oinstall /u01/server
$ sudo chown -R oracle:oinstall /u01/client
$ sudo chown -R oracle:oinstall /u01/certificate

サーバ用自己署名付きウォレットの作成

$ sudo su - oracle
$ cd /u01/server/wallet/
$ orapki wallet create -wallet ./ -pwd Oracle123456 -auto_login
$ orapki wallet add -wallet ./ -pwd Oracle123456 -dn "CN=dbcs" -keysize 1024 -self_signed -validity 3650 -sign_alg sha256

クライアント用自己署名付きウォレットの作成

$ cd /u01/client/wallet/
$ orapki wallet create -wallet ./ -pwd Oracle123456 -auto_login
$ orapki wallet add -wallet ./ -pwd Oracle123456 -dn "CN=client" -keysize 1024 -self_signed -validity 3650 -sign_alg sha256
サーバ・クライアント間で証明書を交換（証明書のExport/Import）

サーバ証明書のExport

$ cd /u01/server/wallet/
$ orapki wallet export -wallet ./ -pwd Oracle123456 -dn "CN=dbcs" -cert /tmp/server.crt

クライアント証明書のExport

$ cd /u01/client/wallet/
$ orapki wallet export -wallet ./ -pwd Oracle123456 -dn "CN=client" -cert /tmp/client.crt

サーバウォレットにクライアント証明書をImport

$ cd /u01/server/wallet/
$ orapki wallet add -wallet ./ -pwd Oracle123456 -trusted_cert -cert /tmp/client.crt

クライアントウォレットにサーバ証明書をImport

$ cd /u01/client/wallet/
$ orapki wallet add -wallet ./ -pwd Oracle123456 -trusted_cert -cert /tmp/server.crt

サーバウォレットのパーミッションを変更

$ cd /u01/server/wallet
$ chmod 640 cwallet.sso

TCPS接続

上記で作成した自己署名付きウォレットを用いてTPCS接続できるようOracle Netを設定

Sample

サーバ（DBLINK接続先:DBCS）側の設定

ウォレットファイルを配置

$ ls /u01/server/wallet
cwallet.sso  ewallet.p12

$ chmod 640 cwallet.sso

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)

lisener.oraの編集
ファイルの先頭に以下を追記

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

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
クライアント（DBLINK接続元:oracle client）側の設定

ウォレットファイルを配置

$ ls /u01/client/wallet

cwallet.sso  ewallet.p12

sqlnet.oraの編集

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u01/client/wallet)
     )
   )

SSL_SERVER_DN_MATCH=(ON)

tnsnames.oraの編集

DBCS0502_IAD1FQ =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.136)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbcs0502_iad1fq.subnet.vcn05.oraclevcn.com)
    )
  )

DBCS_TCPS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = 10.0.1.136)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbcs0502_iad1fq.subnet.vcn05.oraclevcn.com)
    )
    (SECURITY= (SSL_SERVER_CERT_DN="CN=dbcs"))
  )

Tips

XXXXX

Sample

Tips

参考 : OCI操作手順

OCI basic

OCI CLI

OCI CLIの準備

oci cli setupに必要な情報の収集

  • OCIコンソールにログイン
  • APIキーの追加
  • 構成ファイルの表示を選択（未登録の場合はAPIキーを追加）
  • 構成ファイルをコピー

image.png

setup

  • API秘密鍵をデプロイ
  • 構成ファイルを元にconfigファイルを作成
$ mkdir -p ~/.oci
$ vim ~/.oci/config

[DEFAULT]
user=ocid1.user.oc1..aaaaaaaa4u6aqz2qqk6jnorhwunsz6bsvghki4mpsgipxcaq4ybmemwejsrq
fingerprint=91:b7:54:6e:95:37:3c:f9:88:25:dd:73:1d:6a:f7:f6
tenancy=ocid1.tenancy.oc1..aaaaaaaa7mpfrjmlgq5y7kx35a6cbsmqtnoi3nhv5urxbqh6hooj4itfp5jq
region=us-ashburn-1
key_file=<path to your private keyfile> # TODO

デフォルトコンパートメント値の設定

$ oci setup oci-cli-rc

~.oci\oci_cli_rcの編集

[DEFAULT]
 compartment-id = <コンパートメントのOCID>

確認

$ oci compute shape list
auto completeの設定
$ oci setup autocomplete

Object Stroageの操作：CLI

※ 参考 URIルール

https://objectstorage.(region Identifier).oraclecloud.com/n/(name space)/b/(bucket name)/o/(object name)

region_name例

Region Name Region Identifier Region Key
Japan East (Tokyo) ap-tokyo-1 NRT
Japan Central (Osaka) ap-osaka-1 KIX
US East (Ashburn) us-ashburn-1 IAD

例
https://objectstorage.us-ashburn-1.oraclecloud.com/n/idqyzd41fwn7/b/dbcs0502_dblink/o/cwallet.sso

list bucket

oci os bucket list [OPTIONS]

create bucket

oci os bucket create [OPTIONS]

$ oci os bucket create --name ADB_DBLIN_DG4O

list object

oci os object list --bucket-name [text] [OPTIONS]

$ oci os object list -bn dbcs0502_dblink
{
  "data": [
    {
      "etag": null,
      "md5": "C3Qlvq4oEg+rI323Ppz7/Q==",
      "name": "cwallet.sso",
      "size": 6269,
      "time-created": "2021-04-17T07:49:38.307000+00:00",
      "time-modified": null
    }
  ],
  "prefixes": []
}

get object

oci os object get [OPTIONS]

$ oci os object get -bn dbcs0502_dblink --file '/home/oracle/Wallet/client_wallet/cwallet.sso' --name cwallet.sso

put object

oci os object put [OPTIONS]

$ oci os object put -bn ADB_DBLIN_DG4O --file '/home/oracle/Wallet/client_wallet/cwallet.sso' --name cwallet.sso
{
  "etag": "db6a6d3b-f553-492a-9f71-3ca6755586fe",
  "last-modified": "Tue, 20 Apr 2021 04:56:13 GMT",
  "opc-content-md5": "C3Qlvq4oEg+rI323Ppz7/Q=="
}
1
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
1
0