DBMS_FILE_TRANSFER を利用したデータベース間のファイル転送

Oracle データベース間でDBMS_FILE_TRANSFER というパッケージを使ってファイル転送を行ってみたいと思います。普通にオンプレ Oracle であれば、サーバ間でファイルコピーしたら終わりなんですが、RDS for Oracle だったりすると必要になってきます。また、オンプレから RDS に移行したい場合にも便利ではないかと思います。ということで、少し実践的に権限等にも配慮したファイル転送をやってみます。

シナリオ

環境の問題もあるので、このようなシナリオにします。

  • ２つのデータベースはいずれも RDS for Oracle とする
  • ひとつを本番環境（データベース名：ORCL）、もうひとつをテスト環境（データベース名：TESTDB）とする
  • テスト環境に本番環境の DATA_PUMP_DIR ディレクトリに存在するファイルを転送する
  • ファイル転送作業は本番環境にログインすることなくテスト環境から実施できるようにする
  • テスト環境から本番環境の DATA_PUMP_DIR ディレクトリに存在するファイルのリストは確認できるようにする
  • 本番環境からテスト環境へのファイル転送のみを許し、テスト環境から本番環境への転送はできないようにする

ファイル転送専用のユーザを作成する

まず、本番環境（データベース名：ORCL）にてファイル転送をする専用ユーザを作成します。ユーザ名は何でも良いのですが “GUESTUSER" とします。シナリオにある通り、DATA_PUMP_DIR の読み取り権限のみを許すようにします。動作確認の関係もあるので、ディクショナリを参照する権限はつけておきましょう。また、ファイルのリストを取得できないといけませんので、RDS_FILE_UTIL パッケージの実行権限は必要です。

CREATE USER GUESTUSER IDENTIFIED BY ********;
GRANT CONNECT,SELECT ANY DICTIONARY TO GUESTUSER;
GRANT READ ON DIRECTORY DATA_PUMP_DIR TO GUESTUSER;
GRANT EXECUTE ON RDSADMIN.RDS_FILE_UTIL TO GUESTUSER;

データベースリンクを作成する

次に、テスト環境（データベース名：TESTDB）にて本番環境（データベース名：ORCL）に接続するためのデータベースリンク（TO_ORCL）を作成します。エンドポイントは伏字（#）にしています。

CREATE DATABASE LINK TO_ORCL
  CONNECT TO GUESTUSER IDENTIFIED BY ********
    USING 'orcl.#####.#####.rds.amazonaws.com:1521/ORCL';

動作確認のため、自分とデータベースリンクの先のSID（インスタンス名）を確認してみます。

SELECT * FROM V$INSTANCE UNION ALL
SELECT * FROM V$INSTANCE@TO_ORCL;
データベースリンクの確認

DATA_PUMP_DIR ディレクトリ配下のファイルリストを取得する

次に テスト環境 （データベース名：TESTDB） から、データベースリンク越しに本番環境（データベース名：ORCL）の DATA_PUMP_DIR ディレクトリに存在するファイルのリストを確認してみます。

ファイルリストの取得（１）

おっとエラーですね。「ORA-06553: PLS-752: Table function LISTDIR is in an inconsistent state.」だそうです。マニュアルを見る限り、どうもユーザ定義型についてはデータベースリンク経由では利用できないようです。

現在、オブジェクトまたはユーザー定義型(特に、PL/SQLパッケージ内部で宣言する型とは対照的な、SQL CREATE TYPE文を使用して宣言する型)は、1つのデータベース内部のみで有効です。

2.1.9 リモート・データベースでユーザー定義型を使用する際の制限事項

でも、リモートからでもファイルのリストくらいは取りたいので、手っ取り早く本番環境（データベース名：ORCL）側に VIEW （VIEW名：LISTDIR_DATA_PUMP_DIR）を作成しましょう。

CREATE VIEW GUESTUSER.LISTDIR_DATA_PUMP_DIR AS
  SELECT * FROM TABLE(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR'));

では、テスト環境（データベース名：TESTDB）からデータベースリンク越しに VIEW を参照してみます。

SELECT * FROM LISTDIR_DATA_PUMP_DIR@TO_ORCL;
ファイルリストの取得（２）

当たり前ですが、ちゃんと取れたようです。

ファイルを転送する

では、テスト環境 （データベース名：TESTDB） で、本番環境（データベース名：ORCL）からファイルを転送（GET）してみます。DMBS_TRANSFER パッケージの GET_FILE プロシジャを利用します。わかりやすくするために転送後のファイル名は変更してみます。

EXEC DBMS_FILE_TRANSFER.GET_FILE(
   'DATA_PUMP_DIR','SCOTT.DMP'
  ,'TO_ORCL'
  ,'DATA_PUMP_DIR','SCOTT_COPIED.DMP'
);

転送後のファイルを確認してみます。うまく転送できたようです。

SELECT * FROM TABLE(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR'));
ファイルを転送する（１）

ちなみに、既にファイルが存在する状態で実行すると以下のエラーとなります。上書きモードはないので、一度、UTL_FILE.FREMOVE を使ってファイルは削除しておく必要があります。

ORA-19504: ファイル"/rdsdbdata/datapump/SCOTT_COPIED.DMP"の作成に失敗しました。
ORA-27038: 作成したファイルはすでに存在します
Additional information: 1
ORA-06512: "SYS.DBMS_FILE_TRANSFER", 行37
ORA-06512: "SYS.DBMS_FILE_TRANSFER", 行132

逆に、テスト環境（データベース名：TESTDB）から本番環境（データベース名：ORCL）にファイルを転送（PUT）してみます。ファイル名はさらに変更してみています。

EXEC DBMS_FILE_TRANSFER.PUT_FILE(
   'DATA_PUMP_DIR','SCOTT_COPIED.DMP'
  ,'DATA_PUMP_DIR','SCOTT_FROM_TESTDB.DMP'
  ,'TO_ORCL'
);

以下のエラーが発生しました。書き込みできないはずなので OK ですね。

ORA-06564: オブジェクトDATA_PUMP_DIRは存在しません。
ORA-02063: 先行のエラー・メッセージを参照してくださいline(TO_ORCL)。
ORA-06512: "SYS.DBMS_FILE_TRANSFER", 行60
ORA-06512: "SYS.DBMS_FILE_TRANSFER", 行168

念のため、本番環境（データベース名：ORCL）にて、DATA_PUMP_DIR への書き込み権限を与えてみます。

GRANT WRITE ON DIRECTORY DATA_PUMP_DIR TO GUESTUSER;

その後、テスト環境（データベース名：TESTDB）から再度、PUT_FILE してみると、以下の通り、ちゃんと転送されました。

ファイルを転送する（２）

最後に

冒頭に書いたように普通にオンプレデータベース（EC2上のデータベースを含む）であれば何も考えなくても良さそうなことでも、相手がマネージドサービスであるというだけで、ひと工夫が必要になります。ファイル送受信方法のまとめは別記事「RDS for Oracle 上のファイルの扱いについて」にも書いてるので参考にしてください。少なくとも RDS for Oracle 間のファイル転送であれば、DBMS_FILE_TRANSFER が最も手軽で効率的と言えそうです。

AWS,データベースOracle,RDS

Posted by takmaru