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

不正IP報告数

Okan Sensor
 
メイン
ログイン
ブログ カテゴリ一覧

  • カテゴリ ハウツー の最新配信
  • 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$ 
 これでダンプができた模様.

トラックバック


広告スペース
Google