UJP - Oracle Data Pumpのexpdpを試してみた

カテゴリハウツーの最新配信
RSS
RDF
ATOM

ブログ - Oracle Data Pumpのexpdpを試してみた

Oracle Data Pumpのexpdpを試してみた

カテゴリ :: ハウツー

ブロガー :: ujpblog 2019/4/19 22:28

　Oracleからデータを取り出したりいれたり，時にはバックアップ用途で使っていたimp/expコマンドですが，Oracle Data Pumpというプロダクト名？になって進化していました．exp/impだとデータ完全性が怪しい部分があったしね．

　今回はexpdpを使ってデータをエクスポートします．これ，expコマンドの時と大きく違ったのは，expdpで取り出したデータは，Oracleが稼働しているサーバの指定されたディレクトリに保存されるということ．

ディレクトリオブジェクトを作成する

　ローカルにエクスポートしたら，こんな感じでエラーがでる．

bash-4.2$ expdp system/manager@xe directory=./ dumpfile=20190408a.dmp full y🆑
Tue Apr 9 10:30:06 JST 2019

Export: Release 11.2.0.2.0 - Production on Tue Apr 9 10:30:06 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name ./ is invalid


Tue Apr 9 10:30:07 JST 2019

bash-4.2$

　これは，サーバに対象のディレクトリが無いという事．このために，ディレクトリオブジェクトというものを作らなければならん．
　まずは適当なところにディレクトリを作成．

bash-4.2$ mkdir /tmp/dumptest🆑
bash-4.2$ chmod 774 dumptest🆑
bash-4.2$ ls -la /tmp/dumpdir🆑
total 8
drwxrwxr-- 2 oracle dba 4096 Apr 9 13:30 .
drwxrwxrwt. 17 root root 4096 Apr 9 13:36 ..

bash-4.2$

　Oracle上にディレクトリを定義する。
　systemユーザで接続。


bash-4.2$ sqlplus system/manager@xe🆑

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 9 13:32:16 2019

Copyright (c) 1982, 2011, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>

　先ほど作成したファイルシステム上のディレクトリに、expdp1という名前でディレクトリオブジェクトを作成．

SQL> create directory expdp1 as '/tmp/dumptest';🆑

Directory created.🈁

SQL>

　登録されたディレクトリを確認．

SQL> select directory_name, directory_path from dba_directories;🆑

DIRECTORY_NAME DIRECTORY_PATH
--------------- -----------------------------------
EXPDP1 /tmp/dumptest🈁
DATA_PUMP_DIR /u01/app/oracle/admin/XE/dpdump/
XMLDIR /u01/app/oracle/product/11.2.0/xe/r
dbms/xml

SQL>

　デフォルトだとDATA_PUMP_DIRで指定されたディレクトリがある模様．
　作成したexpdp1に権限を付与．今回はscottユーザ．

SQL> grant read,write on directory expdp1 to scott;🆑

Grant succeeded.🈁

SQL> quit🆑
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

bash-4.2$

　成功したのでシェルに戻る．
　expdpコマンドをscottユーザで接続し，先ほど定義したディレクトリexpdp1にダンプを取る．

bash-4.2$ expdp scott/tiger@xe directory=expdp1🆑

Export: Release 11.2.0.2.0 - Production on Tue Apr 9 13:34:02 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/********@xe directory=expdp1
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
. . exported "SCOTT"."TESTTABLE_DELETEME" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/tmp/dumptest/expdat.dmp🈁
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:34:18

bash-4.2$

　ダンプが完了した模様．
　ダンプしたファイルを確認．


bash-4.2$ ls -la /tmp/dumptest🆑
total 260
drwxrwxr-- 2 oracle dba 4096 Apr 9 13:34 .
drwxrwxrwt. 17 root root 4096 Apr 9 13:36 ..

rw-r----- 1 oracle dba 253952 Apr 9 13:34 expdat.dmp🈁
rw-r--r-- 1 oracle dba 1658 Apr 9 13:34 export.log
bash-4.2$

　ログファイルを確認．


bash-4.2$ cat /tmp/dumptest/export.log🆑
;;;
Export: Release 11.2.0.2.0 - Production on Tue Apr 9 13:34:02 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/********@xe directory=expdp1
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
. . exported "SCOTT"."TESTTABLE_DELETEME" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/tmp/dumptest/expdat.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:34:18

bash-4.2$

　パスワードはマスクしてあるなぁ．
　dumpfileオプションでダンプファイル名を指定．

bash-4.2$ expdp scott/tiger@xe directory=expdp1 dumpfile=1.dmp🆑

Export: Release 11.2.0.2.0 - Production on Tue Apr 9 13:46:45 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/********@xe directory=expdp1 dumpfile=1.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
. . exported "SCOTT"."TESTTABLE_DELETEME" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/tmp/dumptest/1.dmp🈁
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:46:56

bash-4.2$

　指定されたファイルでダンプされた模様．

ダンプの容量見積もりをとる

　estimate_onlyを付けることで実際のダンプは行わずに，ファイルの容量を見積もることができる．

bash-4.2$ expdp scott/tiger@xe directory=expdp1 estimate_only=y🆑

Export: Release 11.2.0.2.0 - Production on Tue Apr 9 13:49:39 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/********@xe directory=expdp1 estimate_only=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated "SCOTT"."DEPT" 64 KB
. estimated "SCOTT"."EMP" 64 KB
. estimated "SCOTT"."SALGRADE" 64 KB
. estimated "SCOTT"."BONUS" 0 KB
. estimated "SCOTT"."TESTTABLE_DELETEME" 0 KB
Total estimation using BLOCKS method: 192 KB🈁
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:49:40

bash-4.2$

　先ほどの実行ログでデータは合計21KB程度だったが、最小の64KBブロックで算出している模様。

bash-4.2$ expdp UAM/UAM@xe directory=expdp1 estimate_only=y🆑

Export: Release 11.2.0.2.0 - Production on Tue Apr 9 13:55:05 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "UAM"."SYS_EXPORT_SCHEMA_01": UAM/********@xe directory=expdp1 estimate_only=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated "UAM"."AM_OBJ" 776 MB
. estimated "UAM"."AM_OBJ_DPD" 760 MB
. estimated "UAM"."AM_MART_OBJ_CE_FULL_EXPAND" 440 MB
. estimated "UAM"."AM_BIZ_DPD" 416 MB
. estimated "UAM"."AM_MART_OBJ_CR_FULL_EXPAND" 216 MB

～略～

. estimated "UAM"."JF_QUERY" 4 MB
. estimated "UAM"."AM_HANDLER_SAMPLE_SOURCE" 3 MB
. estimated "UAM"."AM_SQL" 3 MB
. estimated "UAM"."DMRS_DIAGRAMS" 2.125 MB
. estimated "UAM"."AM_BIZ_SRC_MAP" 2 MB
. estimated "UAM"."AM_OBJ_DPD_CC" 2 MB

～略～

Total estimation using BLOCKS method: 3.498 GB🆑
Job "UAM"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:55:08

bash-4.2$

　見積り上は，3.498 GBになる模様．
　では，ダンプを実行して見る．

bash-4.2$ expdp UAM/UAM@xe directory=expdp1 dumpfile=2.dmp🆑

Export: Release 11.2.0.2.0 - Production on Tue Apr 9 13:56:54 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "UAM"."SYS_EXPORT_SCHEMA_01": UAM/********@xe directory=expdp1
 dumpfile=2.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.498 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

～略～

. . exported "UAM"."AM_OBJ" 659.9 MB 3375931 rows
. . exported "UAM"."AM_OBJ_DPD" 644.6 MB 2934780 rows
. . exported "UAM"."AM_MART_OBJ_CE_FULL_EXPAND" 276.2 MB 3403060 rows
. . exported "UAM"."AM_BIZ_DPD" 190.0 MB 813149 rows
. . exported "UAM"."AM_MART_OBJ_CR_FULL_EXPAND" 174.9 MB 2196628 rows
. . exported "UAM"."AM_OBJ_META" 161.9 MB 3257078 rows

～略～

Master table "UAM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for UAM.SYS_EXPORT_SCHEMA_01 is:
/tmp/dumptest/2.dmp
Job "UAM"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:57:40

bash-4.2$

　ダンプしたファイルのサイズを確認する．

bash-4.2$ ls -la /tmp/dumptest/2.dmp🆑
rw-r----- 1 oracle dba 2813657088 Apr 9 13:57 /tmp/dumptest/2.dmp
bash-4.2$ ls -lah /tmp/dumptest/2.dmp🆑
rw-r----- 1 oracle dba 2.7G🈁 Apr 9 13:57 /tmp/dumptest/2.dmp
bash-4.2$

　3.5GB見積もりの実態は2.7GBなので，見積もりは30%増し程度か．(空き容量)で考えたら，実施にはワークエリアも必要なので，もっと必要かと思われる．

フルダンプをとる

　full=yオプションを使う事で，データベース全体のダンプを取ることができる。

bash-4.2$ expdp system/manager@xe directory=expdp1 dumpfile=full.dmp full=y🆑

Export: Release 11.2.0.2.0 - Production on Tue Apr 9 14:04:33 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/********@xe directory=expdp1
 dumpfile=full.dmp full=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.810 GB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE

～略～

Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/tmp/dumptest/full.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 14:05:58

bash-4.2$

日時を指定して一貫性のあるダンプを取る

　日時を細かく指定する。

bash-4.2$ expdp system/manager@xe directory=expdp1 dumpfile=full.dmp ¥🆑
 full=y flashback_time=\"TO_TIMESTAMP\(TO_CHAR\(SYSDATE, ¥🆑
 \'YYYY-MM-DD HH24:MI:SS\'\),\'YYYY-MM-DD HH24:MI:SS\'\)\"🆑

Export: Release 11.2.0.2.0 - Production on Tue Apr 9 14:21:10 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/********@xe directory=expdp1
 dumpfile=full.dmp full=y flashback_time=
 "TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')"
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

　コマンドを実行した日時(SYSDATE)を一貫性が担保する時間とする指定方法．

bash-4.2$ expdp system/manager@xe directory=expdp1 dumpfile=full.dmp ¥🆑
 full=y flashback_time="to_timestamp(sysdate)"🆑

Export: Release 11.2.0.2.0 - Production on Tue Apr 9 14:42:33 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/********@xe directory=expdp1
 dumpfile=full.dmp full=y flashback_time=to_timestamp(sysdate)
Estimate in progress using BLOCKS method...

bash-4.2$

　これでダンプができた模様．

ujpblogさんのブログを読む
トラックバック (0)
閲覧 (14350)

ブログ - Oracle Data Pumpのexpdpを試してみた

Oracle Data Pumpのexpdpを試してみた

トラックバック