Oracleのインポート、エクスポート(ダンプ)が全く理解できてなかったので調査メモ。exp/impコマンド、impdp/expdpコマンドを使ってやってみました。
Oracleについて詳しい訳ではないので誤っている部分があればコメント頂ければ幸いです。
なお、以下の表記で書きます。
- SQL>はSQLPlusでの操作を意味します
- $はターミナルでの操作を意味します。
環境
以下のサイトを参考にvagrantを使ってCentOS6.4(64bit)にOracle11gをインストールしてそれから試してみました
Varagnt 環境の CentOS 6.6 に Oracle XE(11g Release 2) を導入してみる
imp,expコマンドを使ってデータのエクスポート、インポートをやってみる
imp,expコマンドを使ってデータのエクスポート、インポートを行ってみます。
# エクスポート.export.dmpというバイナリが生成される
$exp usr01/12345 FILE=export.dmp
# 確認のため、テーブル削除
SQL>drop table emp cascade constraints;
# インポート
$imp usr01/12345 FILE=export.dmp
# 確認.リストアできてempテーブルが存在
SQL> desc emp;
名前 NULL? 型
----------------------------------------- -------- ----------------------------
EMP_ID NOT NULL CHAR(3)
JOB_ID NOT NULL CHAR(3)
EMP_NAME VARCHAR2(10)
ただし、imp/expコマンドは下位互換のためにあるコマンドであるため、現在はimpdp/expdpを使うことが推奨されているようです
ということで次の項ではimpdp/expdpを使ってみます。
impdb,expdbコマンドを使ってデータをエクスポート、インポートしてみたが色々はまった
成功するまで色々障壁がありましたので一つずつポイントを記載します。
ダンプファイル配置ディレクトリ作成が必要
imp/expでは特に事前準備が必要なかったのですが、impdpでは予めダンプファイルを配置するディレクトリ作成が必要です。
また、作成したディレクトリにはimpdpコマンドを実行するユーザーの権限が必要です。
ディレクトリ作成、権限付与は以下のようにできます
SQL>create or replace directory TEST_DIR as '/u01/app/oracle';
SQL>grant read, write on directory TEST_DIR to usr01
メモリ割り当てが少ないとエラーになる
正確にはメモリ割り当てというかShared Poolが足りず、エラーとなることがありました。
$expdp usr01/12345 directory=test_dir;
Export: Release 11.2.0.2.0 - Production on 土 3月 12 09:31:41 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
接続先: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
"USR01"."SYS_EXPORT_SCHEMA_01"を起動しています: usr01/******** directory=test_dir
BLOCKSメソッドを使用して見積り中です...
オブジェクト型SCHEMA_EXPORT/TABLE/TABLE_DATAの処理中です
BLOCKSメソッドを使用した見積り合計: 0 KB
オブジェクト型SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/TABLEの処理中です
UDE-04031: 操作でOracleエラー4031が発生しました
ORA-04031: 共有メモリーの120バイトを割当てできません("shared pool","select name,online$,contents...","SQLA^56d22d85","opn: qkexrInitOpn")
ORA-06512: "SYS.DBMS_DATAPUMP", 行3326
ORA-06512: "SYS.DBMS_DATAPUMP", 行4551
ORA-06512: 行1
UDE-04031: 操作でOracleエラー4031が発生しました
ORA-04031: 共有メモリーの88バイトを割当てできません("shared pool","select name,online$,contents...","SQLA^56d22d85","opn: qkexrInitOpn")
ORA-06508: PL/SQL: コールしているプログラム単位が見つかりませんでした: "SYS.KUPC$QUE_INT"
ORA-06512: "SYS.DBMS_DATAPUMP", 行4373
ORA-06512: 行1
ORA-04031: 共有メモリーの??バイトを割当てできません
この時はそもそもVMで512Mbyteしかメモリを割り当ててなかったのがよくなかったと思います(freeでみたら余裕なかった)
これは単純にVMなどに割り当てるメモリを増やすかShared Poolへの割り当てを増やせば改善すると思います。自分は単純に割り当てるメモリを増やして改善しました。
途中でexpdpが失敗した場合にはジョブが残る
上記Shared Poolの不足が原因で途中で処理が失敗すると、Oracleではエクスポート処理(ジョブ)を未完了処理として残しています。
そのため、再度expdpを行うと1回目に実施した未完了のジョブもエクスポート対象となってしまい、サイズがどんどん大きくなってしまいます。
以下はそれに気づかず、5回もexpdpをやってしまった時のログです。
$expdp usr01/12345 directory=test_dir;
Export: Release 11.2.0.2.0 - Production on 土 3月 12 11:12:31 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
接続先: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
"USR01"."SYS_EXPORT_SCHEMA_05"を起動しています: usr01/******** directory=test_dir
BLOCKSメソッドを使用して見積り中です...
オブジェクト型SCHEMA_EXPORT/TABLE/TABLE_DATAの処理中です
BLOCKSメソッドを使用した見積り合計: 768 KB
オブジェクト型SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAの処理中です
UDE-04031: 操作でOracleエラー4031が発生しました
ORA-04031: 共有メモリーの32バイトを割当てできません("shared pool","select /*+ rule */ bucket_cn...","SQLA","tmp")
ORA-06512: "SYS.KUPV$FT_INT", 行2904
ORA-06512: "SYS.KUPC$QUE_INT", 行572
ORA-25254: メッセージ待ちの間にLISTENコールがタイムアウトになりました。
ORA-06512: "SYS.DBMS_DATAPUMP", 行3326
ORA-06512: "SYS.DBMS_DATAPUMP", 行4551
ORA-06512: 行1
「BLOCKSメソッドを使用した見積り合計: 768 KB」この部分がどんどん増えていきます。。。
expdpではオプションを付与することでエクスポート時のサイズチェックができるのでやってみました。
$expdp usr01/12345 directory=test_dir ESTIMATE_ONLY=Y ESTIMATE=BLOCKS;
Export: Release 11.2.0.2.0 - Production on 土 3月 12 11:15:15 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
接続先: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
"USR01"."SYS_EXPORT_SCHEMA_06"を起動しています: usr01/******** directory=test_dir ESTIMATE_ONLY=Y ESTIMATE=BLOCKS
BLOCKSメソッドを使用して見積り中です...
オブジェクト型SCHEMA_EXPORT/TABLE/TABLE_DATAの処理中です
. 見積"USR01"."SYS_EXPORT_SCHEMA_01" 192 KB
. 見積"USR01"."SYS_EXPORT_SCHEMA_02" 192 KB
. 見積"USR01"."SYS_EXPORT_SCHEMA_03" 192 KB
. 見積"USR01"."SYS_EXPORT_SCHEMA_04" 192 KB
. 見積"USR01"."SYS_EXPORT_SCHEMA_05" 192 KB
BLOCKSメソッドを使用した見積り合計: 960 KB
ジョブ"USR01"."SYS_EXPORT_SCHEMA_06"が11:15:20で正常に完了しました
どんどん増えていっているのが分かります。。。
この残ってしまったジョブは以下のように削除できます。
対象ジョブにアタッチしてkillで削除できるようです。
スレッド: SYS_EXPORT_SCHEMA_01について
$expdp usr01/12345 attach=SYS_EXPORT_SCHEMA_05;
export> kill
残ったジョブが消せない
よし、この調子でジョブを全部消そう!と思ったら、出来なかった。。。。
$expdp usr01/12345 attach=SYS_EXPORT_SCHEMA_04;
Export: Release 11.2.0.2.0 - Production on 土 3月 12 11:28:13 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
接続先: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
ORA-39002: 操作が無効です
ORA-39000: ダンプ・ファイル指定が無効です
ORA-31640: ダンプ・ファイル"/u01/app/oracle/expdat.dmp"を読取りのためにオープンできません
ORA-27037: ファイル・ステータスを取得できません。
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
expdpコマンド実行時にダンプファイル名を指定しないとデフォルトでexpdat.dmpとなるのですが、すべてのジョブでこのファイル名としているため、アタッチしようとしてもファイルがないのでエラーとなるようです。。。
時間があれば調べてみたかったのですが、よく分からず、VMを再構築して最初からやり直しました。。
そもそもダンプファイル名は別にしようという話はあると思うのですが、もしこの状態からジョブを消す方法があればどなたか教えて頂けると嬉しいです。
expdp,impdpを使ってインポート、エクスポートしてみる
色々はまったのですが、十分なメモリを割り当て(自分の環境では1GByte)みたらすんなりできました。
# ダンプ用のディレクトリを作成する
SQL>create or replace directory TEST_DIR as '/u01/app/oracle';
SQL>grant read, write on directory TEST_DIR to usr01
# エクスポートをする
$expdp usr01/12345 directory=test_dir dumpfile=test.dmp;
Export: Release 11.2.0.2.0 - Production on 土 3月 12 20:27:41 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
接続先: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
"USR01"."SYS_EXPORT_SCHEMA_01"を起動しています: usr01/******** directory=test_dir dumpfile=test.dmp
BLOCKSメソッドを使用して見積り中です...
オブジェクト型SCHEMA_EXPORT/TABLE/TABLE_DATAの処理中です
BLOCKSメソッドを使用した見積り合計: 0 KB
オブジェクト型SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/TABLEの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/INDEX/INDEXの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/COMMENTの処理中です
. . "USR01"."EMP" 0 KB 0行がエクスポートされました
マスター表"USR01"."SYS_EXPORT_SCHEMA_01"は正常にロード/アンロードされました
******************************************************************************
USR01.SYS_EXPORT_SCHEMA_01に設定されたダンプ・ファイルは次のとおりです:
/u01/app/oracle/test.dmp
ジョブ"USR01"."SYS_EXPORT_SCHEMA_01"が20:28:12で正常に完了しました
# 結構でかい
$ll test.dmp
-rw-r----- 1 oracle dba 163840 Mar 12 20:28 test.dmp
# テーブル削除
SQL>drop table emp cascade constraints;
# インポートしてみる
$impdp usr01/12345 directory=test_dir dumpfile=test.dmp
impdp usr01/12345 directory=test_dir dumpfile=test.dmp
Import: Release 11.2.0.2.0 - Production on 土 3月 12 20:35:39 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
接続先: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
マスター表"USR01"."SYS_IMPORT_FULL_01"は正常にロード/アンロードされました
"USR01"."SYS_IMPORT_FULL_01"を起動しています: usr01/******** directory=test_dir dumpfile=test.dmp
オブジェクト型SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/TABLEの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/TABLE_DATAの処理中です
. . "USR01"."EMP" 0 KB 0行がインポートされました
オブジェクト型SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTの処理中です
ジョブ"USR01"."SYS_IMPORT_FULL_01"が20:35:42で正常に完了しました
# 戻ってた
SQL> desc emp;
指定表領域だけのインポート、エクスポートをやってみる
先ほどのは特に何も指定していないのでフルダンプな気がして、そしてその場合、ファイルサイズが大きい(そして処理に必要なメモリも多い)気がしたので指定票領域だけのインポート、エクスポートもやってみました
# 表領域MY_DATAのみのエクスポート
$expdp usr01/12345 TABLESPACES= MY_DATA directory=test_dir dumpfile=my_data.dmp;
# 結構でかいけど元のサイズ比60%
$ll my_data.dmp
-rw-r----- 1 oracle dba 98304 Mar 12 20:45 my_data.dmp
# 表領域を指定してインポート
$impdp usr01/12345 TABLESPACES=MY_DATA directory=test_dir dumpfile=my_data.dmp;
# 戻ってた
SQL> desc emp;
無事できました。
ダンプのデータサイズが小さくなったのもそうですが、処理時間もかなり短くなっていた気がしました（計測してないですが、実感)