Oracle Data Pump（データ・ポンプ）

exp/imp から Data Pump への切り替えのための備忘録。
※参考シェルスクリプトを追記

概要

Oracle Database 10g から搭載された、コマンドで起動するエクスポート／インポート・ユーティリティ
論理バックアップ ※Oracle は物理バックアップの補足としての使い方を推奨
従来の exp/imp よりも高速（サーバー側で実行され、dmp もサーバー側に格納される）

メリット

API により呼び出しが可能
ジョブ管理・状況監視が可能
停止・再開が可能
REMAP_DATA パラメータにより、ユーザが指定した PL/SQL ファンクションでデータ変換可能（11gから）
パラレル化が可能 ※要Enterprise Edition
ダンプファイルの圧縮 ※要Advanced Compression Option
ダンプファイルの暗号化 ※要Advanced Security Option

事前準備

実行するデータベース上の SYS ユーザでの、Catexp.sql または catalog.sql スクリプトの実行
※通常はデータベース作成時に catalog.sql が実行されるので、改めて実行する必要はない、らしい
実行時に接続するデータベース・ユーザへの CREATE SESSION 権限の付与。
別のユーザが所有する表もエクスポートする場合には、 EXP_FULL_DATABASE ロールも付与
※RESOURCE ロールは非推奨になった？各種 CREATE 権限が必要であれば付与
出力先ディレクトリの指定（例）

-- ディレクトリオブジェクト作成
SQL> CREATE OR REPLACE DIRECTORY dpump_dir1 AS '/home/oracle/oradata/dpump_dir';

-- 権限付与
SQL> GRANT READ,WRITE ON DIRECTORY dpump_dir1 TO scott;

COMMAND> expdp scott/tiger tables=emp,dept directory=dpump_dir1

/* パラメータでディレクトリを指定していない場合、
   OS参照変数"DATA_PUMP_DIR"、なければディレクトリオブジェクト"DATA_PUMP_DIR"を参照 */
-- 環境変数の設定
COMMAND> set DATA_PUMP_DIR=dpump_dir1; export DATA_PUMP_DIR

Data Pumpの使用例

Data Pump Export (EXPDP)

-- SCOTT ユーザのテーブル EMP を export する場合
COMMAND> expdp scott/tiger directory=dpump_dir1 tables=emp

Data Pump Import (IMPDP)

-- SCOTT ユーザにテーブル EMP を import する場合
COMMAND> impdp scott/tiger directory=dpump_dir1 dumpfile=exp.dmp tables=emp

主な、エクスポート／インポート対象を指定するオプション

オプション	用途
TABLES	テーブル名を指定
SCHEMAS	スキーマ名を指定
TABLESPACES	表領域名を指定
FULL	FULL=y でデータベース全体を指定
CONTENT
( CONTENT=data_only )	表のデータのみ
( CONTENT=metadata_only )	表の定義のみ
( CONTENT=all（デフォルト） )	表のデータと定義の両方
NOLOGFILE	NOLOGFILE=y で、ログファイルの出力を行わない
ESTIMATE_ONLY	ESTIMATE_ONLY=y で、領域の見積もりのみ実施
EXCLUDE	一部のオブジェクトを除く

Export - Tips -

EXPDP で利用可能なモード。

モード	機能説明	パラメータ
フル	データベース全体のエクスポート	FULL=y
スキーマ	指定したスキーマ全体のエクスポート	SCHEMAS=schema[,...]
テーブル	指定したテーブル全体のエクスポート	TABLES=table[,...]
テーブルスペース	指定したテーブルスペース全体のエクスポート	TABLESPACES=tablespace[,...]

-- フルモード
COMMAND> expdp scott/tiger full=y

-- スキーマモード（ディレクトリ指定）
COMMAND> expdp scott/tiger directory=dpump_dir1 schemas=scott dumpfile=scott_dpump`date +%y%m%d`.dump logfile=scott_dpump`date +%y%m%d`.log

-- テーブルモード
COMMAND> expdp scott/tiger dumpfile=exp.dmp tables=emp,dept

フラッシュバック・モードの指定

指定した SCN もしくは時刻における一貫性を維持したデータのエクスポートが可能。

-- 時刻指定 ※TO_TIMESTAMP()で時刻を指定
-- エスケープ文字が必要となるので、下記はパラメータファイルで指定
COMMAND> cat parfile.txt
FLASHBACK_TIME="TO_TIMESTAMP('2004/03/20 10:00','YYYY/MM/DD HH:MI')"

COMMAND> expdp scott/tiger parfile=parfile.txt

-- パラメータファイル使用せずに時刻指定する FLASHBACK_TIME の設定例
flashback_time=\"TO_TIMESTAMP\(TO_CHAR\(SYSDATE,\'YYYY-MM-DD HH24:MI:SS\'\),\'YYYY-MM-DD HH24:MI:SS\'\)\"

-- SCN（System Change Number）指定
COMMAND> expdp scott/tiger flashback_scn=364909

パラレル・エクスポートの指定 ※要Enterprise Edition

エクスポート処理をパラレルで行うことが可能。

-- 並列度の指定
-- ※置き換え変数（%U）を指定しない場合、dumpfile のファイル数は parallel の値に合わせる必要あり
COMMAND> expdp scott/tiger parallel=3 dumpfile=dpump_dir1:expdat%U.dmp,dpump_dir2%U.dmp

-- 生成されたダンプファイルセットの確認
COMMAND> ls -lR
dpump_dir1:
......expdat01.dmp
......expdat02.dmp
dpump_dir2:
......expdat01.dmp

ダンプファイルの見積もり

ダンプファイルを生成せず、生成されるダンプファイルのサイズを見積もり可能。

指定	説明
BLOCKS	ブロックサイズ × オブジェクトのブロック数（精度高くない）
STATISTICS	統計情報を元に見積もり

-- 見積もりのみ出力する場合には、ESTIMATE_ONLY=y を指定
COMMAND> expdp scott/tiger tables=emp,dept estimate=blocks estimate_only=y

Import - Tips -

EXPDP で指定できるパラメータのほとんどを、IMPDP でも指定可能。

インポート先の指定

インポートの際に、格納先を変更することが可能。

オプション	説明
REMAP_SCHEMA	export 時と異なるスキーマに import
REMAP_TABLESPACE	export 時と異なる表領域に import
REMAP_DATAFILE	export 時と異なる名前のデータファイルで import
SQLFILE	IMPDP 実行時に実行される SQL 文のコマンドを任意のファイルに出力

-- SCOTT ユーザの持つオブジェクトを TEST ユーザに import する場合
COMMAND> impdp scott/tiger directory=dpump_dir1 dumpfile=exp.dmp remap_schema=scott:test

-- USERS 表領域のオブジェクトを TESTTBS 表領域に import する場合
COMMAND> impdp scott/tiger directory=dpump_dir1 dumpfile=exp.dmp remap_tablespace=users:testtbs

-- USERS 表領域のデータファイル名を変更して import する場合
COMMAND> impdp system/manager directory=dpump_dir1 dumpfile=exp.dmp remap_datafile='/home/users.dbf:/home/test/users.dbf'

-- import 時に実行される SQL 文を確認する場合
COMMAND> impdp scott/tiger directory=dpump_dir1 dumpfile=exp.dmp sqlfile=test.sql

ネットワーク・リンクを使用（network_link）

データベース・リンクを使用し、リモートデータベースから直接ローカルデータベースにインポートが可能。

エクスポートのネットワーク・リンク指定は、リモートデータベースのデータをローカルにエクスポートする。
インポートの場合には、直接ローカルデータベースに書き込むので、ダンプファイルを作成しない。

/* 接続するリモートデータベースを、NETWORK_LINK パラメータで指定。
   指定する値はデータベース・リンク名 */
-- DB LINK の指定
COMMAND> impdp scott/tiger tables=emp,dept directory=dpump_dir network_link=scott.jp.oracle.com

SQL文を受け取る（sqlfile）

インポートが他のパラメータに基づいて実行する、全ての SQL DDL の書き込み先のファイルを指定。

※パスワードは SQL ファイルには含まれない。
例）DLL に CONNECT 文あり → コメントで置き換えられ、スキーマ名のみ表示

-- TEST_TABLE表をインポートする際の SQLFILE を受け取る
COMMAND> impdp oradirect/oradirect dumpfile=test_table.dmp tables=test_table sqlfile=sqlfile.txt

作成しようとしている表がすでに存在する場合（table_exists_action）

インポート・ユーティリティに対して、作成しようとしている表がすでに存在する場合に行う操作を指定。

指定	説明
SKIP	表はそのままにして、次のオブジェクトに移動。 CONTENT パラメータが DATA_ONLY に設定されている場合、このオプションは無効
APPEND	ソースから行をロードし、既存の行は変更しない
TRUNCATE	既存の行を削除した後、ソースから行をロード
REPLACE	既存の表を削除した後、ソースから表を作成およびロード。 CONTENT パラメータが DATA_ONLY に設定されている場合、このオプションは無効

デフォルトは SKIP。
CONTENT=DATA_ONLY が指定されている場合、デフォルトは SKIP ではなく APPEND)

COMMAND> impdp hr TABLES=employees DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp TABLE_EXISTS_ACTION=REPLACE

※確認環境での TRUNCATE または REPLACE 指定時、オブジェクトが存在する場合は、ORA-31684 が多発。
　ユーザの再作成か、スキーマ内の全オブジェクトを全削除してから実行するのが無難と思われる。

参考シェルスクリプト

※<>内は環境に合わせて修正

以下環境変数は、必要に応じて設定

export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=${ORACLE_BASE}/product/12.2.0
export ORACLE_SID=<ORACLE_SID>

export LANG=ja_JP.UTF-8
export NLS_LANG=Japanese_Japan.AL32UTF8
export PATH=${PATH}:${ORACLE_HOME}/bin

expdp

#!/bin/sh

# 指定期間より古いdmpファイルの削除
export BACKUP_DAYS=3
if [ $BACKUP_DAYS -gt 0 ]; then
    find $<Data Pump's DIRECTORY_PATH> -maxdepth 1 -type f -daystart -mtime +$BACKUP_DAYS -exec rm -f {} \;
fi

# Data Pump(expdp)
export EXP_TIMESTAMP='date+"%Y%m%d%H%M%S"'
expdp system/<Password> directory=<Data Pump's DIRECTORY_NAME> \
                        schemas=<Schema names> \
                        dumpfile=expdp_${EXP_TIMESTAMP}.dmp \
                        logfile=expdp_${EXP_TIMESTAMP}.log \
                        flashback_time=$EXP_TIMESTAMP

impdp

#!/bin/sh

# Selete dmp File
echo "Enter dmp file name."
read DmpFile

export SYSTUSR=system/<Password>
export LGPATH=<Data Pump's DIRECTORY_PATH>/
export DROPSQL=Drop_User.sql

# DROP USER
sqlplus $SYSTUSR as SYSDBA << EOF
    shutdown immediate;
    startup;
EOF
sqlplus -s $SYSTUSR @DROPSQL $LGPATH

# Data Pump(impdp)
export IMP_TIMESTAMP='date+"%Y%m%d%H%M%S"'
impdp $SYSTUSR directory=<Data Pump's DIRECTORY_NAME> \
               schemas=<Schema names> \
            　 dumpfile=$DmpFile \
               logfile=impdp_${IMP_TIMESTAMP}.log

Drop_User.sql (impdpで呼び出し)

set echo on
set heading off

define SPL = '&1\Drop_User.log'
spool &SPL

SELECT 'DROP <Username>: ' || TO_CHAR(SYSDATE, 'yyyy/mm/dd HH24:mi:ss') FROM DUAL;
DROP USER <Username> CASCADE;

spool off
set echo off

/
exit

参照URL

意外と知らない！？ Export/Import の基礎
https://blogs.oracle.com/oracle4engineer/exportimport-v2

Data Pump(expdp/impdp) の使い方～エクスポート／インポート、データ移動、論理バックアップ
https://blogs.oracle.com/oracle4engineer/data-pumpexpdpimpdp

Oracle® Database ユーティリティ 12cリリース1(12.1)
https://docs.oracle.com/cd/E49329_01/server.121/b71303/toc.htm

Oracle Data Pump について