メモ: Oracle Data PumpのExport/Import

Oracleデータベースでのデータ移行といえば、export/importツールが昔からありましたが、バージョン11gからはサポートされなくなったそうです。代わりに、Oracle Data Pumpというユーティリティが推奨されるようになりました。

オリジナルのエクスポートは、Oracle Database 11gからは原則としてサポートされなくなりました。（中略）オリジナルのエクスポートおよびインポートが必要な次の場合を除いて、データ・ポンプ・エクスポートおよびインポート・ユーティリティを使用することをお薦めします。
オリジナルのエクスポート

ここでは、Oracle Data Pumpを使って、あるサーバのスキーマのデータを、別のサーバのスキーマに移行する手順についてメモします。

公式マニュアルを読めばできることですが、途中でいくつかミスをしてハマって、夜中に色々調べる羽目になったので、分かりにくかった点をまとめておきます。もし記述に間違いを発見した場合はお教えください。＞Oracle DBに詳しい方

余談ですが、旧来のexport/importとData Pumpの両方を使って、同じスキーマを処理してみたところ、Data Pumpのジョブの実行時間の方が（雑な表現ですが）断然短かったです。Data Pumpでは、オプションで並列度の指定もできますし、ジョブの実行監視機能も強化されていますし、これからデータ移行をする時はこちらを使おうと思いました。

環境

Windows Server 2008 R2
Oracle 11.2.0.3.0

（旧サーバ、新サーバともに同じ）

次のような限定された状況を想定しています。

移行元と移行先は、別のホスト（以下では、移行元を旧サーバ、移行先を新サーバと呼びます）
旧サーバと新サーバで、スキーマの名称を変更する

図にするとこんな感じです。

手順

ダンプを置くフォルダを作成する＠旧サーバ
ディレクトリオブジェクトを作成する＠旧サーバ
ユーザに権限を与える＠旧サーバ
スキーマをエクスポートする＠旧サーバ
ダンプを置くフォルダを作成する＠新サーバ
ダンプファイルを旧サーバから新サーバへコピーする
ディレクトリオブジェクトを作成する＠新サーバ
ユーザ（スキーマ）を作成する＠新サーバ
ユーザに権限を与える＠新サーバ
スキーマをインポートする＠新サーバ
invalidオブジェクトを再コンパイルする＠新サーバ
接続確認をする＠fromクライアントto新サーバ

ネットワークリンクを使えばもう少し手順を削減できますが、今回は使っていません。失敗した時に原因を調べやすくするため、最小粒度で完結するタスクの組み合わせで実行しました。

詳細を順に書きます。

1. ダンプを置くフォルダを作成する＠旧サーバ

Oracle Data Pumpでは、旧来のimport/exportと違い、ダンプファイルがサーバ側に作成されます。

ダンプファイル出力先のフォルダを新規に作成します。

cd d:
mkdir expdir

ここでは、Dドライブ直下にexpdirというフォルダを作成しました。

重要。OracleがD:\expdirにダンプを出力できるように、フォルダのアクセス権を設定します。旧サーバのローカルマシンのora_dbaユーザに、読み取りと書き込みの権限を与えます。

詳細手順は、エクスプローラでフォルダを右クリック →［プロパティ］→［セキュリティ］タブ→［編集］→［追加］→［場所］→Windows セキュリティの認証ダイアログが表示されたら［キャンセル］→［場所］ウィンドウでローカルマシンを選択して［OK］→［詳細設定］→［検索］→検索結果から「ora_dba」を選択して［OK］→以下、開いたウィンドウを［OK］で閉じる、です。

2. ディレクトリオブジェクトを作成する＠旧サーバ

impor/exportでは、ダンプファイルやログファイルの出力先として、ディレクトリオブジェクトを指定します。そのため、手順1で作成した物理フォルダを、Oracleにディレクトリオブジェクトとして登録します。

旧サーバのDBにSYSTEMユーザとしてログインして、次のコマンドを実行します。

CREATE DIRECTORY expdp as 'D:\expdir';

ここでは、expdpというディレクトリオブジェクトを登録しました。

3. ユーザに権限を与える＠旧サーバ

エクスポートするスキーマのユーザに、ディレクトリオブジェクトへの読み取りと書き込みの権限を与えます。

GRANT READ, WRITE ON DIRECTORY expdp TO OLD_ONE;

4. スキーマをエクスポートする＠旧サーバ

実行時のオプションはコマンドラインから引数として渡せますが、後々管理しやすいようにパラメータファイルとしてまとめます。

ここでは、次の内容のテキストファイルをexp20140328.parという名前で作成しました。

DIRECTORY=expdp
DUMPFILE=dump20140328.dmp
LOGFILE=exp20140328.log
SCHEMAS=OLD_ONE
JOB_NAME=job_exp1

DIRECTORYは、手順2で作成したディレクトリオブジェクトです。このディレクトリオブジェクトと対応する物理パスに、ダンプファイルがエクスポートされます。

DUMPFILEは、エクスポートするダンプファイルの名前です。

LOGFILEは、エクスポート時に出力するログファイル名です。同じ名前のファイルがすでにある場合、実行時に自動的に上書きされます。

SCHEMASは、エクスポートするスキーマ名です。エクスポートには、数種類のモードがあります。デフォルトは、スキーマ・モードです。スキーマを指定しない場合、実行ユーザが所有するスキーマだけがエクスポートされます。今回はSYSTEMユーザで実行するので、スキーマを明示的に指定しています。なぜSYSTEMユーザで実行するのかについては、後述します。

JOB_NAMEは、エクスポート・ジョブの名前です。Oracle Data Pumpでは、実行中にジョブの進行状況を見ることができます。デフォルトではシステム生成の名前が使われるので、積極的に状況を監視するつもりなら、任意の名前を付けた方が便利です。

ダンプファイルはサーバ側に保存されるので、ディスクの残量に注意します。オプションESTIMATE_ONLY=YESを使うと、実際のエクスポートは行わずに、消費される容量を見積ることができます。

エクスポートコマンドは、旧サーバにログインして、コマンドプロンプトから実行します。

パラメータファイルをカレントディレクトリに置いて、次のコマンドを実行します。

expdp SYSTEM/パスワード parfile=exp20140328.par

SCHEMASオプションの説明に書いたように、スキーマ・モードでは、実行ユーザが所有するスキーマだけをエクスポートできます。たとえば、上のコマンドをSYSTEMユーザでなくOLD_ONEユーザで実行すると、OLD_ONEスキーマだけをエクスポートできます。

ユーザにEXP_FULL_DATABASEロールを与えておくと、全体エクスポート・モードを使えます。このモードでは、DBのすべてのスキーマがダンプ対象になります。

GRANT EXP_FULL_DATABASE TO OLD_ONE;

しかし、今回はSYSTEMユーザでエクスポートを実行します。そのため、たとえばOLD_ONEユーザへ、EXP_FULL_DATABASEロールを付与する必要はありません。

後でダンプファイルをインポートする時に、インポート先に存在しないスキーマをダンプファイルから作成するためには、スキーマ作成に必要なデータがダンプファイルに含まれていなければなりません。そのようなデータを含めてエクスポートするには、SYSTEMユーザでエクスポートを実行する必要があるためです。

5. ダンプを置くフォルダを作成する＠新サーバ

新サーバに、インポート対象のダンプファイルを置くフォルダを作成します。

cd d:
mkdir impdir

ここでは、Dドライブ直下にimpdirというフォルダを作成しました。

重要。旧サーバでダンプファイルを置くフォルダを作成した時と同じように、フォルダのアクセス権を設定します。D:\impdirに対して、新サーバのローカルマシンのora_dbaユーザに、読み取りと書き込みの権限を与えます。

6. ダンプファイルを旧サーバから新サーバへコピーする

手順4でエクスポートされたダンプファイルを、新サーバへ持って行きます。旧サーバと新サーバが同じネットワークにいるなら、Windowsの共有フォルダの機能を使えば便利です。

7. ディレクトリオブジェクトを作成する＠新サーバ

手順5で作成したD:\impdirを、Oracleにディレクトリオブジェクトとして登録します。

新サーバのDBにSYSTEMユーザとしてログインして、次のコマンドを実行します。

CREATE DIRECTORY impdp as 'D:\impdir';

ここでは、impdpというディレクトリオブジェクトを登録しました。

8. ユーザ（スキーマ）を作成する＠新サーバ

すでにインポート先のスキーマが新サーバに存在する場合、この手順は飛ばします。

今回は存在しないという想定で、インポート先となる空のスキーマを作成します。

CREATE USER NEW_ONE IDENTIFIED BY パスワード DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

表領域は、必要に応じて指定します。説明の都合上、今回は指定しています。

# もちろん、NEW_ONEには必要なロールを適宜付与しておきます。次のコマンドは一例です。

GRANT CONNECT TO NEW_ONE;

9. ユーザに権限を与える＠新サーバ

インポート先のスキーマのユーザに、ディレクトリオブジェクトへの読み取りと書き込みの権限を与えます。

また、空のスキーマにデータをインポートするので、スキーマを作成する権限が必要です。そのため、NEW_ONEユーザに対してIMP_FULL_DATABASEロールを付与します。

GRANT READ, WRITE ON DIRECTORY impdp TO NEW_ONE;
GRANT IMP_FULL_DATABASE TO NEW_ONE;

10. スキーマをインポートする＠新サーバ

DIRECTORY=impdp
DUMPFILE=dump20140328.dmp
LOGFILE=imp20140328.log
SCHEMAS=OLD_ONE
REMAP_SCHEMA=OLD_ONE:NEW_ONE
REMAP_TABLESPACE=OLD_ONE:USERS
TABLE_EXISTS_ACTION=REPLACE
TRANSFORM=OID:y
JOB_NAME=job_impdp1

DIRECTORYは、手順7で作成したディレクトリオブジェクトです。このディレクトリオブジェクトと対応する物理パスに、インポートするダンプファイルを置きます。

DUMPFILEは、インポートするダンプファイルの名前です。

LOGFILEは、インポート時に出力するログファイル名です。同じ名前のファイルがすでにある場合、実行時に自動的に上書きされます。

SCHEMASは、旧サーバにあったインポート対象のスキーマ名です。

SCHEMASは、ダンプファイルに含まれるデータから、インポートするスキーマを指定します。ここでは、旧サーバにあるインポート対象のスキーマ名です。

注意点は、旧サーバのインポート対象のスキーマが、新サーバのインポート先のスキーマと同じ名前でも、ダンプファイルに複数スキーマが含まれており、かつその中から特定のスキーマをインポートするには、SCHEMASオプションが必要ということです。

インポートのデフォルトは、全体インポート・モードです。これは、ダンプファイルのデータすべてインポートするモードです。

一方、エクスポートのデフォルトは、スキーマ・モードです。そのため、エクスポートではスキーマを指定しなくても、OLD_ONEユーザがエクスポートを実行すれば、ダンプファイルに含まれるのはOLD_ONEスキーマだけでした。インポートの際は、ダンプファイルに複数のスキーマが含まれている場合、スキーマを指定しなければすべてがインポートの対象になります。

エクスポート時とインポート時に選択するモードによって、インポートされるデータがどうなるか、ちょっと整理してみましょう。

ケース	エクスポートのモード	インポートのモード	インポートされるデータ
A	全体エクスポート・モード	全体インポート・モード	エクスポート元のデータベース全体
B	全体エクスポート・モード	スキーマ・モードでOLD_ONEスキーマを指定	OLD_ONEスキーマのデータ
C	スキーマ・モードでOLD_ONEスキーマを指定	全体インポート・モード	OLD_ONEスキーマのデータ
D	スキーマ・モードでOLD_ONEスキーマを指定	スキーマ・モードでOLD_ONEスキーマを指定	OLD_ONEスキーマのデータ

こんな感じになると思います。今回の手順は、ケースDの形式に該当します。

REMAP_SCHEMAは、ダンプファイル内のスキーマ名と、インポート先のスキーマ名が異なるときに、旧スキーマ名:新スキーマ名の形式で指定します。

REMAP_TABLESPACEは、ダンプファイル内のスキーマの表領域名と、インポート先のスキーマが使う表領域名が異なるときに、旧表領域名:新表領域名の形式で指定します（このオプションの話をするために、スキーマ作成時に表領域を明示的に指定しました）。

TABLE_EXISTS_ACTIONは、作成しようとする表がインポート先のスキーマにすでにある場合の挙動を指定します。SKIP、APPEND、TRUNCATE、REPLACEから選択します。ここでは、既存の表を削除して作り直すREPLACEを指定しました。

TRANSFORMは、ダンプに含まれるOIDをどう扱うかの指定です。必要に応じて指定します。

インポートコマンドは、新サーバにログインして、コマンドプロンプトから実行します。

パラメータファイルをカレントディレクトリに置いて、次のコマンドを実行します。

impdp NEW_ONE/パスワード parfile=imp20140328.par

ちなみに、インポート先のスキーマを作成していない場合、スキーマ作成のための情報がダンプファイルに含まれていれば、スキーマ作成の権限があるユーザ（SYSTEMユーザなど）で上のコマンドを実行することで、インポート時にNEW_ONEスキーマが作成されます。

11. invalidオブジェクトを再コンパイルする＠新サーバ

インポートが完了したら、ストアドプロシージャ、ストアドファンクション、ストアドパッケージなどに、invalidなオブジェクトがないかを確認します。

SELECT owner, object_type, status, object_name FROM all_objects WHERE status='INVALID';

invalidなオブジェクトがある場合、再コンパイルが必要です。

DBに接続して、次のファンクションを実行します。

conn / as sysdba;
CALL UTL_RECOMP.RECOMP_SERIAL('NEW_ONE');

12. 接続確認をする＠fromクライアントto新サーバ

インポートしたスキーマにクライアントから接続して、動作確認をします。

おつかれさまでした。

ダンプに存在しないテーブルがインポート先にある場合に何が起きるか

先ほどの手順で、インポート時のオプションに次の指定をしました。

TABLE_EXISTS_ACTION=REPLACE

作成しようとしたテーブルがすでにあったら置き換えるという指定です。

では、作成する必要のないデータがある場合や、カラムの差異がある場合には、どうなるのでしょう。

ちょっとだけ確認してみたところ、こんな感じでした。

確認

まず、テスト用にDBにデータを登録します。

CREATE TABLE Foo (FooId varchar2(10), FooName varchar2(50));
INSERT INTO Foo (FooId, FooName) VALUES ('A01', 'ほげ');
INSERT INTO Foo (FooId, FooName) VALUES ('A02', 'もが');

CREATE TABLE Bar (BarId varchar2(10), BarName varchar2(50));
INSERT INTO Bar (BarId, BarName) VALUES ('001', 'ふが');
INSERT INTO Bar (BarId, BarName) VALUES ('002', 'ぴよ');

select * from Foo;

FOOID      FOONAME
---------- --------------------------------------------------
A01        ほげ
A02        もが

select * from Bar;

BARID      BARNAME
---------- --------------------------------------------------
001        ふが
002        ぴよ

次に、このスキーマを一旦エクスポートします。

それから、テーブルを更新します。

-- 既存のFooテーブルから1行削除
DELETE FROM Foo WHERE FooId='A02';

-- 既存のFooテーブルに1行追加
INSERT INTO FOO (FooId, FooName) VALUES ('A03', 'なう');

-- 既存のBarテーブルを削除
DROP TABLE Bar;

-- 新規にBuzテーブルを作成して1行追加
CREATE TABLE Buz (BuzId varchar2(10), BuzName varchar2(50));
INSERT INTO Buz (BuzId, BuzName) VALUES ('NNN1', 'あいうえお');

select * from Foo;

FOOID      FOONAME
---------- --------------------------------------------------
A01        ほげ
A03        なう

select * from Bar;
              *
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。

select * from Buz;

BUZID      BUZNAME
---------- --------------------------------------------------
NNN1       あいうえお

最後に、先ほどエクスポートしたデータを元のスキーマにインポートします。

テーブルや行がどのように変更されたかを確認します。

select * from Foo;

FOOID      FOONAME
---------- --------------------------------------------------
A01        ほげ
A02        もが

select * from Bar;

BARID      BARNAME
---------- --------------------------------------------------
001        ふが
002        ぴよ

select * from Buz;

BUZID      BUZNAME
---------- --------------------------------------------------
NNN1       あいうえお

Fooテーブルが、ダンプファイルと同じになりました。

Barテーブルも、同様です。テーブルごと再作成されています。

ダンプファイルになかったBuzテーブルは、インポート後も残っています。

まとめ

まとめると、こうなりました。

状況	ダンプと比較したインポート前のスキーマ状態	インポート後
1	ダンプにあるテーブルが存在しない	TABLE_EXISTS_ACTIONオプションに依存
1.1	ダンプにないカラムが存在する	TABLE_EXISTS_ACTIONオプションに依存
1.2	ダンプにあるカラムが存在しない	TABLE_EXISTS_ACTIONオプションに依存
2	ダンプにないテーブルが存在する	そのまま残る（REPLACE指定でも特に削除されない）

状況2がなんだか面倒な気がします。しかし、スキーマの完全な復元が目的なら、リカバリをするとよいのではないでしょうか。

今回は開発用の個人スキーマの移動が目的だったので、インポートの都度スキーマを捨てて、一から作り直せばよいと考えています。

もしインポート時の挙動を真剣に気にするような使い方をするなら、もっと他のオブジェクトについてもマニュアルを調べるなり検証するなりしたほうがよいでしょう。制約、シーケンス、権限、等々。

どのような方針がよいか、Oracleの運用に詳しい人の考えを聞きたいです。

虎塚