はじめに
この記事は、OCI環境のOracle DB(BaseDB)での外部表の作成の検証について解説します。外部表で参照するファイルは、オンプレミス環境ではOS領域に配置しますが、OCIではオブジェクトストレージに配置することができます。
この環境構築にはDBMS_CLOUD(OCI環境に合わせたDBツール)を利用します。
この記事では、BaseDBからオブジェクトストレージ上のファイルを参照する外部表を利用する際の手順を解説します。
コマンドやサンプルファイルは、オラクルのチュートリアルを参考にしております。
202:DBMS_CLOUDを使ってObject StorageのデータをBaseDBから参照しよう
外部表とは
CSVファイル等のファイルをOracle DBを介してSQL操作可能な表として提供するデータベース機能です。
DBMS_CLOUDとは
OCI環境に合わせたDBツールで、オブジェクトストレージに対する管理操作等が、PL/SQLプロシージャとして提供されています。このツールはAutonomous DBには同梱されていますが、BaseDBには追加インストールすることで利用可能になります。
前提条件
- OCI環境でBaseDBを利用
- オブジェクトストレージのバケットを利用できること
- BaseDBのコマンドライン操作ができること
- この記事では、パブリックサブネットに配置した踏み台サーバ経由でアクセスしますが、OCIコンソールのクラウドシェルでも同様の操作は可能です。
検証用のBaseDB
今回の検証用DBはプライベートサブネットに配置し、Object Storageへの接続にはService Gatewayを使用します。
・BaseDBのあるVCN(VCN01)にService Gatewayを配置
・BaseDBのあるサブネット（Pri01）のルートルールに、タイプ＝サービス・ゲートウェイかつオブジェクトストレージをターゲットとしたルールを追加
・BaseDBからオブジェクトストレージにアクセスできることを確認
-オブジェクトストレージにファイルを配置し、PAR(事前承認リクエスト)のURLを発行
– BaseDBからwgetまたはcurlでPARのURLにアクセスし、上記ファイルを取得できること
DBMS_CLOUDのインストールと構成
BaseDBの場合は、追加のインストールにより利用できます。
DBMS_CLOUDのダウンロード
ダウンロードサイトからダウンロードします。
(以下はダウンロードの実行例)
| wget https://objectstorage.us-phoenix-1.oraclecloud.com/p/KB63IAuDCGhz_azOVQ07Qa_mxL3bGrFh1dtsltreRJPbmb-VwsH2aQ4Pur2ADBMA/n/adwcdemo/b/CERTS/o/dbc_certs.tar |
BaseDBのコマンドラインでwgetを実行する場合は、BaseDBが存在するVCNにNAT Gatewayを配置する必要があります。
ファイルを配置先のディレクトリに配置します。
配置先のディレクトリは作成する必要があります。
| ファイル名 | 説明 | 配置先 |
|---|---|---|
| dbms_cloud_install.sql | DBMS_CLOUD のインストール | /home/oracle/dbc |
| dbc_aces.sql | Access Control Entries (ACEs)の設定 | |
| verify_aces.sql | ACEs 設定後の確認 | |
| grant_user.sql | 指定ユーザーに権限を付与 | |
| grant_role.sql | 指定ユーザーにロールを付与 | |
| config_aces_for_user.sql | 指定ユーザーに ACEs を設定 | |
| config_aces_for_role.sql | 指定ロールに ACEs を設定 | |
| validate_user_config.sql | 設定した権限を検証 | |
| dbc_certs.tar | DBMS_CLOUDの証明書 | /home/oracle/cert |
DBMS_CLOUDのインストール
BaseDBのコマンドラインに入り、oracleユーザーで環境変数が定義されていることを確認します。
| sudo su -su – oracle echo $ORACLE_HOME |
以下のスキーマ作成およびインストールコマンドはPerlで実行します。
CDBに対してSYSユーザーで操作するため、事前にSYSユーザーのパスワードを設定しておく必要があります。
| sqlplus / as sysdba; alter user sys identified by [sysパスワード] exit |
①スキーマ作成
DBMS_CLOUDのディレクトリに移動し、スキーマ作成のコマンドを打鍵します。
| cd /home/oracle/dbc$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys/[パスワード] -force_pdb_mode ‘READ WRITE’ -b dbms_cloud_install -d $ORACLE_HOME/rdbms/admin/ -l /tmp catclouduser.sql |
②パッケージ本体のインストール
同じくパッケージインストールのコマンドを打鍵します。
| $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys/[パスワード] –force_pdb_mode ‘READ WRITE’ -b dbms_cloud_install -d /home/oracle/dbc -l /home/oracle/dbc dbms_cloud_install.sql |
SSL接続のための準備
オブジェクトストレージのアクセスは、HTTPSプロトコルのため、BaseDBにはSSLの設定が必要です。
①証明書ファイルの展開
前のステップで配置したtarファイルを解凍し、証明書ファイルを展開します。
| cd /home/oracle/cert tar xvf dbc_certs.tar |
②Wallet格納用ディレクトリの作成
SSL証明書を配置するWallet用ディレクトリを作成します。
| mkdir -p /opt/oracle/dcs/commonstore/wallets/ssl cd /opt/oracle/dcs/commonstore/wallets/ssl |
③Walletの作成
Walletを作成します。
| orapki wallet create -wallet . -pwd [Walletのパスワード] -auto_login |
④DBMS_CLOUDの証明書をWalletに登録
前のステップ(①で)解凍した証明書をWalletに登録します。
#! /bin/bash
for i in /home/oracle/cert/*.cer
do
orapki wallet add -wallet . -trusted_cert -cert "$i" -pwd [Walletのパスワード]
done⑤作成されたWalletの確認
| orapki wallet display -wallet . |
登録された証明書が表示されます。(100行以上あり)
⑥Walletの有効化のための構成変更
Walletを有効化するため、構成ファイルsqlnet.oraを編集します。
以下23aiの場合
| vi /u01/app/oracle/product/23.0.0/dbhome_1/network/admin/sqlnet.ora |
変更内容 (Walletのディレクトリの場所を指定)は以下の通り
| Before | # WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/dcs/commonstore/tcps_wallet))) |
| after | WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/dcs/commonstore/wallets/ssl))) |
⑦ACE (Access Control Entries)の作成
DBMS_CLOUDのACE設定プロシージャ(dbc_aces.sql)を編集します
| cd /home/oracle/dbc/vi dbc_aces.sql |
| Before | define sslwalletdir=<Set SSL Wallet Directory> |
| after | define sslwalletdir=/opt/oracle/dcs/commonstore/wallets/ssl |
⑧ACEの作成
sysdbaでdbcs_aces.sqlを実行します。
| sqlplus / as sysdba@dbc_aces.sql |
⑨DBMS_CLOUDの設定を検証
ここまで、Wallet の作成と ACEs の設定を実施したので、それらが正しく設定されているかどうかを検証します。 検証にはverify_aces.sqlを使用します。環境に合わせて編集します
| vi verify_aces.sql |
変更内容は以下の通り
| before | CREATE OR REPLACE PROCEDURE &clouduser..GET_PAGE(url IN VARCHAR2) AS define sslwalletdir=<Set SSL Wallet Directory> define sslwalletpwd=<Set SSL Wallet password> &clouduser..GET_PAGE(‘https://objectstorage.eu-frankfurt-1.oci.customer-oci.com‘); |
| after | CREATE OR REPLACE PROCEDURE C##CLOUD$SERVICE.GET_PAGE(url IN VARCHAR2) AS define sslwalletdir=/opt/oracle/dcs/commonstore/wallets/ssldefine sslwalletpwd=[Walletのパスワード] &clouduser..GET_PAGE(‘https://objectstorage.ap-tokyo-1.oci.customer-oci.com‘); |
確認用プロシージャを実行します (差分が出力されます)
| sqlplus / as sysdba@verify_aces.sql |
PL/SQLプロシージャが正常に完了がエラーなく実行されていればOK。
利用ユーザーの作成と権限付与 (PDB単位)
対象のPDBの利用ユーザーを作成し、権限を付与します。
ユーザーの作成
ユーザーを作成します。ここではTEST01 パスワードはHoge_Pass_123##
sqlplus / as sysdba
alter session set container=pdb01;
CREATE USER TEST01 IDENTIFIED BY "Hoge_Pass_123##";
GRANT CREATE SESSION TO TEST01;
ALTER USER TEST01 QUOTA UNLIMITED ON USERS;権限の付与
権限付与用のプロシージャを編集します。(権限付与対象のユーザを指定)
| cd /home/oracle/dbc/ vi grant_user.sql |
変更内容は以下の通り (権限付与対象のユーザー:TEST01 に変更)
| before | define username=’SCOTT’ |
| after | define username=’TEST01′ |
確認用プロシージャを実行します
| sqlplus / as sysdba alter session set container=pdb01; @grant_user.sql; |
エラーが出力されていないことを確認
(「セッションが変更されました。」「権限付与が成功しました。」のメッセージが返る想定)
クレデンシャルの作成と検証
あらかじめOCIコンソールで認証トークンを登録します。
OCIコンソールにログイン後、自分のIDのプロファイルを開き、「トークンおよびキー」の「認証トークン」でトークンの生成をします。
トークンの文字列(作成時にしかアクセスできない)をコピーしておきます。
後続の手順では、この認証トークンを使用します。
トークンを登録するプロシージャを編集します。
| before | BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => ‘<クレデンシャルの名前（任意の名前）>’, username => ‘<OCIユーザーID>’, password => ‘<認証トークン>’ ); END;/ |
| after | BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => ‘mtkey3′, username => ‘xxxxx@s-style.co.jp’, password => ‘OCI管理コンソールで生成したトークンの文字列’ ); END;/ |
確認用プロシージャを実行します。
実行は、前のステップで作成/権限付与したユーザで、利用するPDBに接続して実行します。
| sqlplus TEST01/Hoge_Pass_123##@db1.pri01.vcn01.oraclevcn.com:1521/PDB01.pri01.vcn01.oraclevcn.com SQL> BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => ‘mtkey3’, username => ‘xxxxx@s-style.co.jp’,password => ‘トークン’ ); END; / |
正常実行を確認(「PL/SQLプロシージャが正常に完了しました。」)できればOKです。
外部表で参照するCSVファイルのObject Storageへの配置
オブジェクトストレージにファイルをアップロードします。
ファイルのアップロード
管理コンソールを開き、オブジェクトストレージにcsvファイルをアップロードします。
事前承認済みリクエストの取得
アップロードしたオブジェクトの事前承認リクエストを作成します
外部表の作成
オブジェクトストレージのファイルを参照する外部表を作成します。
コード
begin DBMS_CLOUD.CREATE_EXTERNAL_TABLE(table_name=>'TUTORIAL_SALES',
credential_name => 'mtkey3',
file_uri_list => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/[namespace-of-bkt]/b/bkt-external-table01/o/ocitutorials_sales.csv',
format => json_object('delimiter' value ',' ),
column_list =>'channel_short varchar2(1),
channel_long varchar2(20),
channel_class varchar2(20)');
end;
/
ALTER TABLE TUTORIAL_SALES REJECT LIMIT UNLIMITED; ここで打鍵するのはDBMS_CLOUDのコマンドであり、SQL (CREATE TABLE … ORGANIZATION EXTERNAL)ではありません。変更ポイントを解説します。
| 変更箇所 | 内容 |
| table_name | 参照するテーブル名 |
| credential_name | 前のステップで作成したクレデンシャル名 |
| file_uri_list | オブジェクトストレージ上のCSVファイルのURI |
| format | 外部表のフォーマットを記載します**(ここでは割愛します) |
| column_list | テーブルのカラム名と属性をカンマ区切りで記述します |
**例
json_object(‘characterset’ value ‘JA16SJIS’, ‘delimiter’ value ‘,’, ‘ignoremissingcolumns’ value ‘true’, ‘removequotes’ value ‘true’, ‘dateformat’ value ‘YYYY-MM-DD-HH24-MI-SS’, ‘blankasnull’ value ‘true’
外部表の確認
続いて、作成した外部表を参照してみます。
| SQL> SELECT * FROM TUTORIAL_SALES; |
以下のようにCSVファイルの内容が表示されればOKです。
| C CHANNEL_LONG CHANNEL_CLASSS Direct Sales Direct T Table Sales Direct C Catalog Indirect I Internet Indirect P Partners Others |
他のCSVでも試して、SQLでアクセスできることが確認しました。
SQL (CREATE TABLE … ORGANIZATION EXTERNAL)と違って、使い慣れないDBMS_CLOUDですが、通常のOracle DBとは互換性があります。DBMS_CLOUDのコマンドにつきましては、以下サイトをご参照ください。
https://docs.oracle.com/cd/F19136_01/arpls/DBMS_CLOUD.html#GUID-2AFBEFA4-992E-4F53-96DB-F560084C7DA9
運用上の考慮点
BaseDBでDBMS_CLOUDを使う際には、もう一点注意が必要です。
BaseDBのOS部分のカスタマイズは、オラクルではサポート外である点です。
BaseDBの構成変更やアップグレード時には、OS上のカスタマイズ部分 (当記事ではDBMS_CLOUDのインストール/設定)が引き継がれない場合があります。外部表の定義や参照先オブジェクトストレージのCSVファイルの情報等は引き継がれますが、再設定が必要となった場合には、DBMS_CLOUDのインストールから作業が必要になる場合があります。
まとめ
BaseDBでも、DBMS_CLOUDを使用することで、オブジェクトストレージ上のファイルをデータベースから利用できることが確認できました。オブジェクトストレージのようなクラウドの環境との連携により、信頼性の高いクラウドシステムの構築・運用ができます。運用は多少変わりますが、ぜひ一度お試しください。
