Autonomous Database(ADB,ADW,ATP)へ、DBMS_DATAPUMPパッケージでimportする #oraclecloud

概要

Oracle CloudのAutonomous Database(ADB)へ、Datapumpでexport(expdpコマンド)したダンプファイルをimportする手順を紹介します。importの際にPL/SQLのDBMS_DATAPUMPパッケージを利用します。ADBはAutonomous Data Warehouse(ADW)とAutonomous Transaction Processing(ATP)があります。本記事はADWを使って作成しています。ATPでも同様に動作します。

(参考)Autonomous Database(ADB, ADW, ATP)の使い方に関しては、下記の記事で、さまざまな場面での詳細な手順が紹介されています。使いはじめのユーザーを意識した内容です。実際に実施したいことが下記の記事にないか確認いただくとよいです。(2020/1 追記)
Autonomous Database ハンズオンラボ（HOL）

impdpコマンドとDBMS_DATAPUMPパッケージ

ここではDBMS_DATAPUMPパッケージでADWへダンプファイルをimportする手順を紹介しますが、impdpコマンドを実行する方法もあります。impdpコマンドを利用する方法は下記のリンクが参考になります。

Using Oracle Autonomous Data Warehouse : Import Data Using Oracle Data Pump on Autonomous Data Warehouse

ADWはマネージドサービスで、動作しているサーバーのOSへログインはできません。したがってimpdpコマンドを実行するためには、ADWとは別にOracle Clientなどがインストールされている環境（Oracle CloudのcomputeインスタンスにOracle Clientをインストールした環境など）が必要です。ここで紹介するDBMS_DATAPUMPパッケージを利用することで、impdpコマンドを利用することなく、ADWへ接続してPL/SQLを実行してimportできます。このときダンプファイルはOracle Cloudのオブジェクトストレージに配置します。

前提

作成済みのADW
インストール済みのSQL Developer
importするdatapumpのダンプファイル
ダンプファイルを配置するOracle Cloudのオブジェクトストレージ

ADWの作成に関しては下記が参考になります。
Autonomous Data Warehouse Cloud(ADW)の作成とSQL Developerからの接続

SQL Developerは対象のADWにadminで接続できるようにしておいてください。

datapumpのダンプファイルは、今回オンプレミスのLinux環境上のOracle Database 11g R2から表モードでexportしたものを利用しています。exportした時のコマンドは次のようになります。

expdp system/(systemのパスワード)@(接続文字列) directory=(ディレクトリオブジェクト名) \ 
dumpfile=(ダンプファイル名) LOGFILE=(ログファイル名) \
tables=SCOTT.ADDRESS_PART1, SCOTT.ADDRESS_PART2

ここではユーザーscottの表ADDRESS_PART1とADDRESS_PART2をexportしています。

importまでの流れ

ダンプファイルをオブジェクトストレージに配置します。
オブジェクトストレージにアクセスするためのAuth Tokenを作成します。
ADWのスキーマ上でオブジェクトストレージにアクセスするためのクレデンシャルを作成します。
データをimportするユーザー（スキーマ）を作成します。
SQL DeveloperでimportのためのPL/SQLスクリプトを生成するウィザードを実行します。
生成されたDBMS_DATAPUMPパッケージを利用するPL/SQLスクリプトを実行します。
import実行の確認をします。

このimportまでの流れのうち、1.から3.まではcsvファイルをADWにロードする際の手順と同じです。3.まで実施したあとで、csvファイルをロードする場合はDBMS_CLOUDパッケージのCOPY_DATAプロシージャを利用します。datapumpのimportの際は、DBMS_DATAPUMPパッケージを利用できます。DBMS_DATAPUMPパッケージは設定項目が多くありますが、SQL Developerのウィザードを利用することで、DBMS_DATAPUMPパッケージを動作させるPL/SQLスクリプトを生成できます。
上記の5.のステップの前に、1.から3.のステップを実施しておくことはポイントのひとつです。 5.のSQL Developerのウィザードの中で作成済みのクレデンシャル情報を元にオブジェクトストレージ上のダンプファイルにアクセスします。（つまりウィザードの実行より先にオブジェクトストレージ上にダンプファイルが必要です）

ダンプファイルをオブジェクトストレージに配置する

ファイルのオブジェクトストレージへの配置する手順は、csvファイルでもダンプファイルでも同じです。
「ORACLE BY EXAMPLE」のLoading Your Data Into Autonomous Data Warehouseのページの情報を参照しながら実行することができます。
ORACLE BY EXAMPLE : Upload Data Files to Your Object Store
このドキュメントの記載にあるように、利用するバケットを作成して、そのバケットにimportするdatapumpのダンプファイル（ファイル名は通常 xxx.dmp とします）をアップロードします。

オブジェクトストレージにアクセスするためのAuth Token作成

「ORACLE BY EXAMPLE」のLoading Your Data Into Autonomous Data Warehouseのページの情報を参照しながら実行することができます。
ORACLE BY EXAMPLE : Create an Object Store Auth Token
このドキュメントの記載にあるように、オブジェクトストレージにアクセスするためにAuth Tokenを作成します。作成したAuth Token（文字列）は、次の手順であるクレデンシャルの作成で利用します。

ADWのスキーマ上でオブジェクトストレージにアクセスするためのクレデンシャルを作成

「ORACLE BY EXAMPLE」のLoading Your Data Into Autonomous Data Warehouseのページの情報を参照しながら実行することができます。こちらのページの情報をベースに説明を追加していきます。
ORACLE BY EXAMPLE : Create Object Store Credentials in your Autonomous Data Warehouse Schema
この「ORACLE BY EXAMPLE」の例では、adwc_userというユーザーを事前に作成している前提となっています。今回は管理ユーザー「admin」を使うことにします。
SQL Developerから該当のADWへadminで接続して、下記のようなSQLを実行してクレデンシャルを作成します。この例では、クレデンシャルの名前を「TEST_CRED」としています。

begin
  DBMS_CLOUD.create_credential (
    credential_name => 'TEST_CRED',
    username => '（Oracle Cloudのユーザー名）',
    password => '（上記のユーザーで作成したAuth Token）'
  ) ;
end;
/

Auto Tokenなどを間違えて作成してしまった場合は、DBMS_CLOUD.drop_credentialプロシージャーで削除して作成しなおすとよいです。

データをimportするユーザー（スキーマ）の作成

データをimportする前に、importするユーザー（スキーマ）をexportしたデータベースと同じ名前で作成しておきます。ここではユーザーscottの表をexportしたので、同じ名前のユーザーscottを作成しておきます。

create user scott identified by （パスワード）;
grant dwrole to scott;

SQL DeveloperでimportのためのPL/SQLスクリプトを生成するウィザードを実行

ここからが今回のメインです。SQL Developerを利用して、importのためのPL/SQLスクリプトを生成させます。まずSQL Developerのメニューから「表示」-「DBA」を選択してクリックします。

これでSQL Developerの画面にDBAビューが追加されます。

importのためのウィザードは、このDBAビューから実行します。DBAビューの「接続」を右クリックして「接続の追加」を選択します。

「接続の選択」が表示されます。ここで、利用するADWへadminで接続するためにすで保存してあるものを選択します。adminのパスワードの入力を求められたら入力してください。これでDBAビューに該当するADWへの接続が追加されます。
DBAビューのADWへの接続を展開すると「データ・ポンプ」という項目があります。これを右クリックすると「データ・ポンプ・インポート・ウィザード」というメニューが表示されるので選択します。

これでウィザードが起動します。「インポート・ウィザード-ステップ1/6」は次のような画面になります。ここで「ジョブ名」は自動で入力されるので、そのまま利用します。

「インポート・タ...」となっているところ（おそらくインポートタイプです）は、今回はexportで表モード（tablesパラメータ）を指定したので「表」を選択します。
「資格証明またはディレクトリ」は選択できるようになっています。前の手順で作成したクレデンシャル（ここでの例は「TEST_CRED」でした）が表示されることを確認してください。
「ジョブ名」が自動的に入力されなかったり、クレデンシャルが表示されない場合、ADWへの接続が切れている可能性があります。そのよう場合はSQL Developerの画面の「Oracle接続」で接続し直してから実行するとよいかもしれません。
「ファイル名またはURI」にはオブジェクトストレージに配置したダンプファイルへのURIを入力します。小さなフィールドへの入力なので、テキストファイルにURIを正確に書き出してからコピーするとよいでしょう。
URIは次のような形式の文字列です。これはとても間違いやすいので注意してください。それぞれの名前以外は固定です。

https://swiftobjectstorage.（リージョンの名前）.oraclecloud.com/v1/（テナントの名前）/（バケットの名前）/（ダンプファイルの名前）

ここでリージョンの名前はOracle Cloudの画面右上にある「us-ashburn-1」などの文字列になります。ダンプファイルへのURIを入力したら「次」をクリックします。

補足
オブジェクトストレージのファイルへのURIは、本記事を作成時点で2種類あります。ここで紹介したURIともう一つはオブジェクトストレージの画面上で確認できるものです。後者の画面上で確認できるURIの方が新しく導入されたものなのですが、本記事の作成時点では、まだここで紹介する手順の中では動作しませんでした。これは将来的には解消されると思います。

「インポート・ウィザード-ステップ2/6」は次のような画面になります。

ここで「使用可能なソース表」に関する表示は、全体の画面が表示されて、しばらくしてから表示されます。前の画面で入力したクレデンシャルとURIの情報を元に、ダンプファイルへアクセスしてソース表が表示されています。したがって、前の画面で入力が間違っていた場合などは、この「使用可能なソース表」が表示されずエラーが表示されます。

ここの例では、ダンプファイルの中身はSCOTTスキーマの2つの表です。同じ表に対してスキーマ名を「null」としたものと「SCOTT」としたものと2つ表示されています。今回はSCOTTスキーマに対してimportするので、「SCOTT」がついている2つの表を「選択されたソース表」に移動させて、「次へ」をクリックします。

「インポート・ウィザード-ステップ3/6」は次のような画面になります。

この画面では、exportした際とスキーマと表領域を変更する場合にその指定をします。ADWでは表領域は1つであり、ここで指定することはありません。今回はスキーマも変更しないので、そのまま「次」をクリックします。

「インポート・ウィザード-ステップ4/6」は次のような画面になります。

この画面では、importを実施するときの様々な設定を指定します。「表が存在する場合の表の処理」に関してですが、なにも指定されてない場合は「追加（Append）」になるようです。今回は何も変更しないで、そのまま「次」をクリックします。

「インポート・ウィザード-ステップ5/6」は次のような画面になります。

importの実行のジョブの設定をします。今回は何も変更しないで、そのまま「次」をクリックします。

「インポート・ウィザード-ステップ6/6」は次のような画面になります。

この画面で「PL/SQL」のタブをクリックすると、作成されたPL/SQLプログラムが表示されます。これをコピーしてください。この画面で「終了」をクリックすると実際にimportのジョブが実行されるのですが、ここでは「取り消し」をクリックして実行しないことにします。
生成されたPL/SQLプログラムを確認して、あとから実行することにします。

DBMS_DATAPUMPパッケージを利用するPL/SQLスクリプトを実行

生成されたPL/SQLプログラムは次のようなものです。

set scan off
set serveroutput on
set escape off
whenever sqlerror exit 
DECLARE
    s varchar2(1000); 
    h1 number;
    errorvarchar varchar2(100):= 'ERROR';
    tryGetStatus number := 0;
begin
    h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'TABLE', job_name => 'IMP_SD_100-12_23_47', version => 'COMPATIBLE'); 
    tryGetStatus := 1;
    dbms_datapump.set_parameter(h1, 'TRACE', 167144-96) ; 
    dbms_datapump.set_parameter (h1, 'PARTITION_OPTIONS', 'MERGE'); 
    dbms_datapump.metadata_transform(h1, 'SEGMENT_ATTRIBUTES', 0); 
    dbms_datapump.metadata_transform(h1, 'DWCS_CVT_IOTS', 1); 
    dbms_datapump.metadata_transform(h1, 'DWCS_CVT_CONSTRAINTS', 1); 
    dbms_datapump.metadata_filter(h1, 'EXCLUDE_PATH_EXPR',         'IN (''INDEX'',''INDEXTYPE'', ''MATERIALIZED_VIEW'',''MATERIALIZED_VIEW_LOG'',         ''MATERIALIZED_ZONEMAP'', ''CLUSTER'', ''CLUSTERING'')'); 
    dbms_datapump.set_parallel(handle => h1, degree => 1); 
    dbms_datapump.add_file(handle => h1, filename => 'IMPORT-'||to_char(sysdate,'hh24_mi_ss')||'.LOG', directory => 'DATA_PUMP_DIR', filetype=>DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE); 
    dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 1); 
    dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''SCOTT'')'); 
    dbms_datapump.metadata_filter(handle => h1, name => 'NAME_EXPR', value => 'IN(''ADDRESS_PART1'',''ADDRESS_PART2'')'); 
    dbms_datapump.add_file(handle => h1, filename => 'https://swiftobjectstorage.（リージョンの名前）.oraclecloud.com/v1/（テナントの名前）/（バケットの名前）/（ダンプファイルの名前）', directory => 'TEST_CRED', filetype => 5); 
    dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1); 
    dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC'); 
    dbms_datapump.set_parameter(handle => h1, name => 'SKIP_UNUSABLE_INDEXES', value => 0);
    dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0); 
    dbms_datapump.detach(handle => h1); 
    errorvarchar := 'NO_ERROR'; 
EXCEPTION
    WHEN OTHERS THEN
    BEGIN 
        IF ((errorvarchar = 'ERROR')AND(tryGetStatus=1)) THEN 
            DBMS_DATAPUMP.DETACH(h1);
        END IF;
    EXCEPTION 
    WHEN OTHERS THEN 
        NULL;
    END;
    RAISE;
END;
/

このPL/SQLプログラムを確認すると、ウィザードで指定したクレデンシャルの名前やダンプファイルへのURIがあることがわかります。このプログラムを元に必要な改修を加えて実行することもできます。そのようなケースを想定していくつか注意点があります。
プログラムの中で
job_name => 'IMP_SD_100-12_23_47'
のようにジョブ名が自動生成されています。ジョブ名でdatapumpのimportで利用されるマスター表が作成されます。2回以上このプログラムを実行する場合は、ジョブ名が重ならないようにします。またマスター表（名前はジョブ名になります）とimportする表の名前も重ならないようにしてください。つまりimportの中で「emp」という表を作成するのであれば、ジョブ名を「emp」にはしないようにします。
ここでは「インポート・ウィザード-ステップ4/6」でマスター表は削除しない、という設定にしてあります。マスター表はimportが正常終了したら削除しても問題ありません。（ただしSQL Developerからインポートジョブのログを確認する場合は必要です。）
importのログを出力するファイル名には
filename => 'IMPORT-'||to_char(sysdate,'hh24_mi_ss')||'.LOG'
のように実行の際のタイムスタンプが入っています。タイムスタンプには実行環境と時差があるので注意ください。たくさんログがたまる場合は、あとから区別しやすいように編集する、あるいは時間だけでなく日付をいれる、といったことをしてもよいと思います。
生成されたPL/SQLプログラムを実行するとimportジョブが始まります。SQL DeveloperからadminでADWへ接続して実行します。しばらくすると「PL/SQLプロシージャが正常に完了しました。」と表示されます。ただしこの時点ではimportは完了していません。次のSQLを実行してジョブが動作中か確認できます。

SELECT job_name, operation, job_mode, state, attached_sessions
FROM user_datapump_jobs ORDER BY 1,2;

ここで実行したジョブ「IMP_SD_100-12_23_47」のSTATEがNOT RUNNINGとなっており完了していることが確認できます。importの実行中はEXECUTINGと表示されます。

import実行の確認

ここでimportした2つの表の件数をカウントしてみます。

importされていることが確認できます。importのログはタイムスタンプ入ったファイル名に出力されるのでした。ログはADWのデータベースに作成されている「DATA_PUMP_DIR」というディレクトリオブジェクトに出力されています。このディレクトリオブジェクトにあるファイルを確認するには、次のようなSQLを実行します。
SELECT * FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR');

ここでは「IMPORT-13_14_35.LOG」というファイル名で出力されていることがわかります。このディレクトリオブジェクトにあるファイルは、次のPL/SQLプログラムでオブジェクトストレージにコピーできます。

BEGIN
DBMS_CLOUD.PUT_OBJECT(
  credential_name => 'TEST_CRED',
  object_uri => 'https://swiftobjectstorage.（リージョンの名前）.oraclecloud.com/v1/（テナントの名前）/（バケットの名前）/IMPORT-13_14_35.LOG',
  directory_name => 'DATA_PUMP_DIR',
  file_name => 'IMPORT-13_14_35.LOG');
END;
/

ここでクレデンシャルはいままでと同じものを利用しています。URIのファイル名は出力するファイル名になります。このようなPL/SQLを実行して、Oracle Cloudの画面でオブジェクトストレージを確認すると、ログファイルがコピーされていることがわかります。「Download」して中身を確認します。

マスター表"ADMIN"."IMP_SD_100-12_23_47"は正常にロード/アンロードされました
"ADMIN"."IMP_SD_100-12_23_47"を起動しています: 
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
. . "SCOTT"."ADDRESS_PART1"                     150.9 MB  156250行がインポートされました
. . "SCOTT"."ADDRESS_PART2"                     150.9 MB  156250行がインポートされました
オブジェクト型TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTの処理中です
ジョブ"ADMIN"."IMP_SD_100-12_23_47"が月 3月 18 13:15:37 2019 elapsed 0 00:01:05で正常に完了しました

import実行時のログを確認できます。
ディレクトリオブジェクトにあるファイルを削除したいときは、次のようなPL/SQLプログラムを実行します。

BEGIN
DBMS_CLOUD.DELETE_FILE ( 
       directory_name => 'DATA_PUMP_DIR',
       file_name => 'IMPORT-13_14_35.LOG'); 
END;
/

ADWのadminスキーマの中に、ジョブ名で表が作成されています。これはdatapumpのimportで利用されるマスター表です。importが正常終了した場合は、マスター表を削除しても構いません。（ただしSQL Developerからインポートジョブのログを確認する場合は必要です。）datapumpのマスター表に関しては下記を参照ください。
Oracle Databaseデータベース・ユーティリティ : データ・ポンプ・ジョブ実行中に行われる処理

なお自動的に命名されるマスター表の名前にはハイフン(-)が入っているので、drop table文で削除するときはダブルクオートでくくります。
drop table "IMP_SD_100-12_23_47"