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