この記事はUlrike Schwinnによる”Oracle Object Storage Access for all Oracle Databases with DBMS_CLOUD“の日本語翻訳記事です。

2024/02/01

Oracleのオブジェクト・ストレージは、すべてのクラウド・リージョンで利用可能なクラウド・ストレージ・サービスです。開発者やIT管理者はこのサービスを使用して、データの保存とアクセスを低コストで行うことができます。オブジェクト・ストレージの価格、ドキュメントなどの詳細については、こちらをご覧ください。

データベース・アプリケーションのユースケースの1つとして、データをデータベースだけでなく、オブジェクト・ストレージにも格納できる外部テーブルやハイブリッド・パーティション・テーブルがあります。これはDBMS_CLOUD PL/SQLパッケージを使用することで実現することができます。この PL/SQL パッケージを使用すると、オブジェクト・ストレージをデータベースのデータ・ストレージとして使用できます。

DBMS_CLOUDパッケージを使うと、外部テーブルやハイブリッド・パーティション・テーブルを作成し、データベースだけでなくオブジェクト・ストレージにもデータを保存することができます。

以下のリストにいくつかの例を示します：

オブジェクト・ストレージからテーブルにデータをロードするためのCOPY_DATA
CREATE_CREDENTIAL ：クラウド・オブジェクト・ストレージの認証情報を保存する
CREATE_EXTERNAL_TABLE ：外部テーブルを作成する
CREATE_EXTERNAL_PART_TABLE ：パーティショニングされた外部テーブルを作成する
CREATE_HYBRID_PART_TABLE ：ハイブリッド・パーティション・テーブルを作成する

関数とプロシージャの完全なリストはPL/SQLパッケージおよびタイプ・リファレンスのDBMS_CLOUDでご確認いただけます。

MOS Note How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1)

このパッケージは、19.9 以降の Oracle Database 19c および 21.3 以降の Oracle Database 21c でサポートされています。

DBMS_CLOUD はOracle Autonomous Databaseでは、既に標準機能の一部として組み込まれています。しかし、Oracle Database Release 19c (19.9以上) から、DBMS_CLOUD は、オンプレミスとクラウドを問わず、他のすべてのデータベース・インストールし、利用できるようになりました。
ADB以外のデータベースでDBMS_CLOUDを利用するには、このパッケージを使用するためのユーザーまたはロールを手動で設定する必要があります。ステップ・バイ・ステップのインストールに関する情報は、以下のリソースに記載されています。

ここからは、DBMS_CLOUDのセットアップ方法と使用例を説明していきます。

まず、DBMS_CLOUD パッケージをインストール、パッケージを使用するためのロールとユーザーのセキュリティ設定をします。

DBMS_CLOUDのインストール

このパッケージは Oracle Database 19c では 19.9 から、Oracle Database 21c では 21.3 からサポートされています。CDB以外の環境へのインストールは現在サポートされていません。

インストールには以下の手順が必要です：
1) DBMS_CLOUD のオーナーとしてスキーマ C##CLOUD$SERVICE を作成します。

最初に、共通スキーマ C##CLOUD$SERVICEをCDB と PDB に作成します。マルチテナント・アーキテクチャでは、Perl スクリプト catcon.pl を使用して、すべての PDB に DBMS_CLOUD がインストールされるようにすることができます。必要なコマンドを含む dbms_cloud_install.sql という名前の SQL スクリプトが作成され、dbc ディレクトリに格納されます。(このコードは前述の MOS Note またはここにあります）。

メモ

このインストール手順は、 Database Vault オプションがインストールされていることを前提としています。このコンテキストでエラーが発生しても、Database Vaultオプションがインストールされていなければ無視できます。

その場合、呼び出しは次のようになります：

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys/<password> --force_pdb_mode 'READ WRITE' -b dbms_cloud_install -d /home/oracle/dbc -l /home/oracle/dbc dbms_cloud_install.sql
</password>

以下のコマンドで検証をします。

-- from within the ROOT Container
SQL> select con_id, owner, object_name, status, sharing, oracle_maintained
     from cdb_objects where object_name = 'DBMS_CLOUD' order by con_id;

    CON_ID OWNER                OBJECT_NAME     STATUS  SHARING            O
---------- -------------------- --------------- ------- ------------------ -
         1 PUBLIC               DBMS_CLOUD      VALID   METADATA LINK      Y
         1 C##CLOUD$SERVICE     DBMS_CLOUD      VALID   METADATA LINK      Y
         1 C##CLOUD$SERVICE     DBMS_CLOUD      VALID   METADATA LINK      Y
         3 PUBLIC               DBMS_CLOUD      VALID   METADATA LINK      Y
         3 C##CLOUD$SERVICE     DBMS_CLOUD      VALID   METADATA LINK      Y
         3 C##CLOUD$SERVICE     DBMS_CLOUD      VALID   METADATA LINK      Y
         5 PUBLIC               DBMS_CLOUD      VALID   METADATA LINK      Y
         5 C##CLOUD$SERVICE     DBMS_CLOUD      VALID   METADATA LINK      Y
         5 C##CLOUD$SERVICE     DBMS_CLOUD      VALID   METADATA LINK      Y

-- from within PDB
SQL> select owner, object_name, status, sharing, oracle_maintained
     from dba_objects where object_name = 'DBMS_CLOUD';

OWNER                OBJECT_NAME          STATUS  SHARING            O
-------------------- -------------------- ------- ------------------ -
PUBLIC               DBMS_CLOUD           VALID   METADATA LINK      Y
C##CLOUD$SERVICE     DBMS_CLOUD           VALID   METADATA LINK      Y
C##CLOUD$SERVICE     DBMS_CLOUD           VALID   METADATA LINK      Y

HTTP URI およびオブジェクト・ストレージにアクセスするための証明書を使用して SSL ウォレットを作成します。

まず、必要な証明書を以下のURLからダウンロードし、/home/oracle/jdbcディレクトリなどで解凍する必要があります。次に、以下のrapkiコマンドを使用して、/opt/oracle/dcs/commonstore/wallets/sslディレクトリにSSLウォレットを作成します。(ちなみに、ここでは他のディレクトリを選択することもできます）。

cd /opt/oracle/dcs/commonstore/wallets/ssl
orapki wallet create -wallet . -pwd <your_chosen_wallet_pw> -auto_login
#! /bin/bash
for i in 'ls <location of="" cert="" files""="">/*cer'
do
orapki wallet add -wallet . -trusted_cert -cert $i -pwd <ssl wallet="" password="">
done</ssl></location></your_chosen_wallet_pw>

SSLウォレットを有効にするには、サーバー側のsqlnet.oraに追加して、Oracleインストール用に新しく作成したsslウォレットを指す必要があります。

WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=/opt/oracle/dcs/commonstore/wallets/ssl)))

ここで、ネットワーク ACL (Access Control List の略) は DBMS_NETWORK_ACL_ADMIN によって設定されます。注意点として、ACL はユーザがデータベースからのネットワークアクセスを制御するために必要であるということです。必要なコマンドは MOS Note からコピーするか、ここからダウンロードできます。これらは簡単に実行できるように dbc_aces.sql ファイルに格納されています。さらに変数 sslwalletdir もここで正しく置き換える必要があるます。スクリプトはルートコンテナで実行される必要があります。

# Connect to CDB$ROOT
connect sys/<password> as sysdba
@@dbc_aces.sql
</password>

次のチェックは、ウォレットが作成されたことを示しています：

SQL> select * from database_properties 
     where property_name in ('SSL_WALLET','HTTP_PROXY');

PROPERTY_NAME        PROPERTY_VALUE
-------------------- ---------------------------------------------
DESCRIPTION 
------------------------------------------------------------
SSL_WALLET           /opt/oracle/dcs/commonstore/wallets/ssl
Location of SSL Wallet

最終的な検証には、MOSノートにある特別なスクリプトを使用するか、こちらからダウンロードできるスクリプトを使用します。変数を正しい項目に置き換えることを忘れないでください。実行結果は “valid response “でなければなりません。そうでない場合は、設定を再度チェックし、成功するまで必要に応じて繰り返す必要があります。

...
old   2: &clouduser..GET_PAGE('https://objectstorage.eu-frankfurt-1.oraclecloud.com');
new   2: C##CLOUD$SERVICE.GET_PAGE('https://objectstorage.eu-frankfurt-1.oraclecloud.com');
valid response
...

DBMS_CLOUD を使用するユーザーまたはロールの設定

必要な権限は、ロールを介して、または直接ユーザーに割り当てられます。どちらの手順も MOS Note で説明されています。テスト目的のため、ここではユーザ SCOTT を例に直接権限を割り当てる方法を示します。一般的には、直接個別に権限を付与するのではなく、ロールを定義することをお勧めします。

DBMS_CLOUD を使用するユーザーまたはロールの設定
必要な権限は、ロールを介して、または直接ユーザーに割り当てられます。どちらの手順も MOS Note で説明されています。テスト目的のため、ここではユーザ SCOTT を例に直接権限を割り当てる方法を示します。一般的には、直接個別に権限を付与するのではなく、ロールを定義することをお勧めします。

grant CREATE TABLE to SCOTT;
grant read, write on directory DATA_PUMP_DIR to SCOTT;
grant EXECUTE on dbms_cloud to SCOTT;

-- configure ACLs and ACEs
@$ORACLE_HOME/rdbms/admin/sqlsessstart.sql

-- Create New ACL / ACE s
begin
-- Allow all hosts for HTTP/HTTP_PROXY
dbms_network_acl_admin.append_host_ace(
      host =>'*',
lower_port => 443,
upper_port => 443,
       ace => xs$ace_type(
        privilege_list => xs$name_list('http', 'http_proxy'),
        principal_name => upper('SCOTT'),
        principal_type => xs_acl.ptype_db));

-- Allow wallet access
dbms_network_acl_admin.append_wallet_ace(
wallet_path => 'file:/opt/oracle/dcs/commonstore/wallets/ssl',
        ace => xs$ace_type(privilege_list =>
       xs$name_list('use_client_certificates', 'use_passwords'),
       principal_name => upper('SCOTT'),
       principal_type => xs_acl.ptype_db));
end;
/
@$ORACLE_HOME/rdbms/admin/sqlsessend.sql

これで SCOTT ユーザの設定は完了し、DBMS_CLOUD パッケージを使用できるようになりました。

利用シナリオ

公開されていないオブジェクト・ストアのデータにアクセスするには、該当するリージョンのオブジェクト・ストレージ・バケットに適切な権限を持つ、テナント内の OCI ユーザで認証する必要があります。OCI API の署名キーか、テナンシー内のユーザーの認証トークンを作成する必要があります。詳細はユーザー資格証明の管理を参照してください。

認証トークンを作成したと仮定して、認証用のクレデンシャル・オブジェクトをデータベース・スキーマに作成する必要があります：

SQL> connect scott/<password>@US1
Connected.

SQL> BEGIN DBMS_CLOUD.CREATE_CREDENTIAL(
     credential_name => '<credential name="">',
     username => 'oracleidentitycloudservice/ulrike.schwinn@oracle.com',
     password => '<passwort'>);
END;
/
PL/SQL procedure successfully completed.</passwort'></credential></password>

最初の例では、ユーザ SCOTT が Object Storage からバケットを読み込みます。覚えておいてください： Object Storage のバケットは、オブジェクトを格納するための論理コンテナです。ユーザやシステムは、リージョン内で必要に応じてバケットを作成します。リージョンは、ユーザがどのアクションを実行できるかを定義するポリシーを持つ1つのコンパートメントにリンクされています。私の例では、バケットはUSBUCKETと呼ばれ、以下のようになっています：
Bucket

これで、バケットのファイルを一覧できるようになりました。これには、クレデンシャル情報だけでなく、バケツの URL (location_uri) も必要です。これは、Object Storage の “View Object Details” メニュー項目から取得できます。

SQL> select object_name, bytes 
     from DBMS_CLOUD.LIST_OBJECTS('<credential name="">','https://objectstorage.<region>.oraclecloud.com/n/<objectstoragenamespace>/b/<bucketname>/o/'),

OBJECT_NAME                                             BYTES
-------------------------------------------------- ----------
CHANNELS.gz                                               109
CHANNELS_test.txt                                          79
countries01-13_25_43.DMP                                57344
cwallet.sso                                              6661
emp.csv                                                   703
emp_dept_20.csv                                           114
emp_dept_20.exp                                         12288
emp_dept_30.csv                                           121
emp_dept_30.exp                                         12288
exp_scott.dmp                                               0
exp_scott.dmp_aaaaaa                                   471040
</bucketname></objectstoragenamespace></region></credential>

次の例は、外部テーブルを使いたい場合です。ファイルはオブジェクト・ストレージに格納されています。CREATE_EXTERNAL_TABLE関数を使用します。アプリケーションを成功させるためには、少なくともテーブル名、クレデンシャル名、FILE_URI_LIST、FORMAT引数、COLUMN_LIST引数（外部テーブルの列名とデータ型のリストをカンマ区切りで指定）を指定する必要があります。保存されるファイルは必ずしもテキスト形式である必要はありません。オンプレミス・インストレーションと同様に、TYPE形式オプションを指定することでCSV形式も可能です。あらかじめ圧縮されたファイルも読み込むことができます。圧縮ファイル用のCOMPRESSIONフォーマット・オプションを使えば、圧縮タイプgzip、zlib、bzip2のファイルにアクセスできるますし、パーケットファイルも使用できます。TYPEオプションと値’parquet’をformatオプションに使用する必要があります。

SQL> drop table channels_ext;

SQL> begin DBMS_CLOUD.CREATE_EXTERNAL_TABLE(table_name=>'CHANNELS_EXT', 
                      credential_name => '<credential name="">',
                        file_uri_list => ''https://objectstorage.<region>.oraclecloud.com/n/<objectstoragenamespace>/b/<bucketname>/o/'CHANNELS.gz',
                               format => json_object('compression' value 'auto','delimiter' value '|' ),
                          column_list =>'channel_short varchar2(1),
                                         channel_long varchar2(20),
                                         channel_class varchar2(20)');
end;
/
SQL> select * from channels_ext;

C CHANNEL_LONG         CHANNEL_CLASS
- -------------------- --------------------
S Direct Sales         Direct
T Tele Sales           Direct
C Catalog              Indirect
I Internet             Indirect
P Partners             Others
CHANNELS.gz',
                               format => json_object('compression' value 'auto','delimiter' value '|' ),
                          column_list =>'channel_short varchar2(1),
                                         channel_long varchar2(20),
                                         channel_class varchar2(20)');
end;
/
SQL> select * from channels_ext;

C CHANNEL_LONG         CHANNEL_CLASS
- -------------------- --------------------
S Direct Sales         Direct
T Tele Sales           Direct
C Catalog              Indirect
I Internet             Indirect
P Partners             Others</bucketname></objectstoragenamespace></region></credential>

最後に、ファイルCHANNELS_test.txtからデータベース・テーブルCHANNELSにデータをコピーします。

SQL> drop table channels;

SQL> create table channels
   (channel_id CHAR(1),
    channel_desc VARCHAR2(20),
    channel_class VARCHAR2(20));

SQL> begin DBMS_CLOUD.COPY_DATA(table_name => 'CHANNELS', 
                           credential_name => '<credential name="">', 
                             file_uri_list => 'https://objectstorage.<region>.oraclecloud.com/n/<objectstoragenamespace>/b/<bucketname>/o/'CHANNELS_test.txt', 
                               schema_name => 'SCOTT', 
                                    format => json_object('delimiter' value '|'));
end;
/

SQL> select * from channels;

C CHANNEL_DESC         CHANNEL_CLASS
- -------------------- --------------------
T Tele Sales           Direct
C Catalog              Indirect
I Internet             Indirect
P Partners             OthersCHANNELS_test.txt', 
                               schema_name => 'SCOTT', 
                                    format => json_object('delimiter' value '|'));
end;
/

SQL> select * from channels;

C CHANNEL_DESC         CHANNEL_CLASS
- -------------------- --------------------
T Tele Sales           Direct
C Catalog              Indirect
I Internet             Indirect
P Partners             Others</bucketname></objectstoragenamespace></region></credential>

もちろん、これら以外にも利用シナリオは他にもたくさんあります。他の興味深いトピックとしては、ハイブリッド・パーティション・テーブルなどがあります。
実際に動かしてみて、テストとトライを楽しんでください。

参考文献

MOS Note How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1)

PL/SQLパッケージおよびタイプ・リファレンス：DBMS_CLOUD

サンプルSQL：GitHub

全てのデータベース・サービスからオブジェクト・ストレージへのアクセスを可能にするDBMS_CLOUD

DBMS_CLOUDのインストール

DBMS_CLOUD を使用するユーザーまたはロールの設定

利用シナリオ

参考文献

Kazumi Momoki

Cloud Solution Engineer

[セミナー資料/動画] Oracle Real Application Clusters アーキテクチャ詳説(Oracle DBaseCamp)(1/16開催)

SQL履歴を使用した23cでのSQL文トラッキング

全てのデータベース・サービスからオブジェクト・ストレージへのアクセスを可能にするDBMS_CLOUD

DBMS_CLOUDのインストール

DBMS_CLOUD を使用するユーザーまたはロールの設定

利用シナリオ

参考文献

Authors

Kazumi Momoki

Cloud Solution Engineer

[セミナー資料/動画] Oracle Real Application Clusters アーキテクチャ詳説(Oracle DBaseCamp)(1/16開催)

SQL履歴を使用した23cでのSQL文トラッキング