Oracleデータベースをエクスポートしてダンプファイルにする方法

• 環境
  • Red Hat Enterprise Linux Server release 5.11 (Tikanga)
  • SQL*Plus: Release 11.2.0.1.0
  • Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
  • GNU bash, version 3.2.25
• やりたいこと : 任意のスキーマをエクスポートしてDumpファイルにしたい。

Dumpファイルを出力するディレクトリを作成する

# 1. oracleというユーザでエクスポートする
$ cat /etc/group | grep dba
dba:x:502:oracle

# 2. oracleユーザに変更する
$ sudo su - oracle
[sudo] password for ponsuke:

# 3. ディレクトリを作成する
$ mkdir dpdump
$ ls -la /path/to/ | grep dpdump
drwxr-x--- 2 oracle oinstall    4096 Apr 20 14:22 dpdump

# 4. 所有者をoracleユーザかつDBAグループにして権限を変える
$ chown oracle:dba /path/to/dpdump/
$ chmod 774 /path/to/dpdump/
$ ls -la /path/to/ | grep dpdump
drwxrwxr-- 2 oracle dba         4096 Apr 20 14:22 dpdump

• 参考
  • Linux グループ一覧の確認と/etc/group ファイル
  • ファイル・ディレクトリの所有者とグループを変更する - chown
• oinstallグループってなに？ : オペレーティング・システム・グループおよびユーザーの作成要件

データベースにディレクトリ・オブジェクトを作成する

-- 1. データベースにログインする
$ sqlplus / as sysdba

-- 2. 作成したディレクトリで、data_pump_dirというディレクトリ・オブジェクトを作成する
SQL> create directory data_pump_dir as '/path/to/dpdump/';
Directory created.
SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------------------------------------
SYS                            DATA_PUMP_DIR                  /path/to/dpdump/

-- 3. エクスポートするスキーマ(hoge)に権限を付与する
SQL> grant read,write on directory data_pump_dir to hoge;
Grant succeeded.

-- 4. ログアウトする
SQL> exit

• 参考 : とりあえずExport。: 今日もなんだかいい天気♪

任意のスキーマをDumpファイルにエクスポートする

# 1. 任意のスキーマをDumpファイルにエクスポートする
# (意味)[hoge]スキーマにある[テーブルデータ・定義の両方]を[DATA_PUMP_DIR]の[hoge.dmp]にエクスポートして、ログを[hoge.log]に出力する
$ expdp username/password content=all directory=DATA_PUMP_DIR dumpfile=hoge.dmp log=hoge.log schemas=hoge
...省略...
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /path/to/dpdump/hoge.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:26:41

# 2. Dumpファイルとログファイルができる
$ ls -l /path/to/dpdump/
total 21052
-rw-r----- 1 oracle oinstall 21520384 Apr 20 14:26 hoge.dmp
-rw-r--r-- 1 oracle oinstall     6102 Apr 20 14:26 hoge.log

• 参考 : OracleのEXPDP/IMPDP(エクスポート・インポート)の使い方 | Oracle初心者でもスッキリわかる

スキーマがいっぱいあって面倒くさいときはシェルを使う

データベース全体や表領域単位でエクスポートしたい場合は、FULL=YやTABLESPACES={表領域}をexpdpコマンドで使えばできる。
が、「スキーマ単位にDumpファイルを分けたい」&&「スキーマがいっぱいある」時はシェルを使ってみます。

#!/bin/bash

# エクスポートしたいスキーマを一覧にして配列に書く
schemas=(
    hoge
    fuga
    ponsuke
    ...ひたすら並べる...
)

# 出力する日付(出力に時間がかかるので秒は入れない)
out_date=`date +%Y-%m-%d-%H-%M`
for schema in ${schemas[@]}; do
    # 任意のスキーマをDumpファイルにエクスポートする
    `expdp username/password content=all directory=DATA_PUMP_DIR dumpfile=${schema}-${out_date}.dmp log=${schema}-${out_date}.log schemas=${schema}`
done

• 参考 : date コマンド | コマンドの使い方(Linux) | hydroculのメモ

失敗したこと

ORA-39070: Unable to open the log file.

$ expdp username/password content=all directory=DATA_PUMP_DIR dumpfile=hoge.dmp log=hoge.log schemas=hoge
...省略...
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation

• 原因 : データベースにディレクトリ・オブジェクトを作成していないから
• 対応 : ディレクトリ・オブジェクトを作成する

ORA-06512: at "SYS.UTL_FILE"

$ expdp username/password content=all directory=DATA_PUMP_DIR dumpfile=hoge.dmp log=hoge.log schemas=hoge
...省略...
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

• 原因 : Dump格納用のディレクトリの所有者がDBAグループじゃない上に権限を付与していないから
• 対応 : ディレクトリの所有者と権限を見直す