オラクルデータ移行方法の概要
データベースはデータをメンテナスするシステムシステムだから、当然データの移行、いわゆる投入と抽出は非常に重要かつ頻繁である。
オラクルデータベースは多様なデータメンテナンス方法を提供していて、データ移行業務に使える手法は七種類ほどある。普通は戸惑うんであろう。
それぞれの手法は特徴があって、一律にいい、わるいとはいいきれないが、敢えてよく問われる効率性、安全性の面から、実務とあわせて整理してみたいと思う。
結論からいうと
- 一般的な処理、最も効率的なデータ移行方法はCREATE TABLE AS(CTAS)である。複数データベース間であれば、DB LINK経由の効率はいい。複数のプロセッサーとIOチャネルがあれば、PARALLELオプションを利用すればいい。パーティションオプションがあれば、もっと合理にコントロールできる。
- その次、構造のまま移行する作業に限定、RMAN圧縮バックアップセットは効率である。
- また、作業はやや繁雑だが、Transportable Tablespaceは効率である。
- 伝統の方法であれば、複数CSVファイルにダンプし、複数SQLLDRを起動してダイレクトパスでロードするのはパラレル効果があるが、CSVファイル上のエラーチェックなどはなくて、万の一エラーがあったら、致命的な問題になる。あまりお勧めできない。
- 持続的な移行であれば、STREAMは移行元に負荷が低い。
- exp/impとexpdp/impdpはあまり効率てきとはいえないが、使いやすくて、効率を要求しない場合、お勧め。
- expdp/impdpはDB LINK経由でファイル経由せずに移行可能なので、便利である 。
exp/impで移行
exp/impとexpdp/impdpは効率があまりいいとはいえないが、使いやすくて、効率を要求しない場合、お勧め。
exp/impとexpdp/impdpを比較するなら、効率の面からみると、あまり差がない。
expdp/impdpは新機能と提携しやすくて、サーバ上で実行される。
exp/impはSQL*Net経由で、端末にデータを落とすことが可能。
expでデータのダンプ
exp userid=system/manager fromuser=schema table=(table1,table2...) file=(file1.dmp, file2.dmp,...) rows=y log=exp.log
impでデータダンプのロード
imp userid=system/manager fromuser=schema table=(table1,table2...) file=(file1.dmp, file2.dmp,...) rows=y log=imp.log
expdp/impdp で移行
exp/impとexpdp/impdpは効率があまりいいとはいえないが、使いやすくて、効率を要求しない場合、お勧め。
exp/impとexpdp/impdpを比較するなら、効率の面からみると、あまり差がない。
expdp/impdpは新機能と提携しやすくて、サーバ上で実行される。
exp/impはSQL*Net経由で、端末にデータを落とすことが可能。
expdpでデータのダンプ
create directory expdpdir as '/tmp'; expdp system/manager tables=employee directory=expdpdir dumpfile=expdp.dmp
impdpでデータダンプのロード
create directory imp dpdir as '/tmp'; impdp system/manager tables=employee directory=impdpdir dumpfile=expdp.dmp
RMANで移行
RMANのバックアップ効率は圧倒的にいい。また、データベース全体の構造と一貫性を管理できる。更に、DBAはほぼRMANのバックアップを既に日々取得しているのであれば、データ取得のステップは不要である。
RMANで移行の場合、移行元でRMANでバックアップの取得、移行先でリカバリの手順になるが、問題は移行先に同様な物理構造維持できない場合、（DISKの違い、FILE既に存在などケース）、別のパスに変更する必要がある。この場合、NEW_NAMEであるはCOVERTで、新しいパスを指定する必要がある。
RMANでバックアップの取得
rman target / nocatalog backup database as compressed backupset plus archivelog; exit;
RMANでバックアップのリカバリ
- 移行先へcontrol fileとinit.oraあるいはspfileのコピー
- init.oraあるいはspfileでcovertの設定
rman target / nocatalog set new_name set untile to_date('2011/11/11 11:11:11', 'yyyy/mm/dd hh24:mi:ss'); backup database as compressed backupset plus archivelog; exit;
CREATE TABLE AS SELECT(DB LINK)で移行
CREATE TABLE AS SELECT は最も効率なデータ転送方法である。
CREATEのため、データ自体のREDO、UNDOが作成しない。
パラレルとDBLINKも利用可能である。
CREATE TABLE tablename PARALLEL AS SELECT /*+ PARALLEL(tablename)+/ ... FROM tablename;
INSERT SELECT (DB LINK)で移行
INSERT SELECT は最も効率なデータ転送方法である。
CREATEのため、データ自体のREDO、UNDOが作成しない。
パラレルとDBLINKも利用可能である。
APPENDは既存の空き領域探すコストを省いて、NOLOGGINGは最小限にREDOを出力することで
スピードを更にはやくすることができる。
INSERT /*+ PARALLEL(a) APPEND NOLOGGING*/ INTO tablename a SELECT /*+ PARALLEL(tablename)+/ ... FROM tablename;
SQLPLUS COPY コマンド
copyコマンドの文法：
usage: COPY FROM <db> TO <db> <opt> <table> { (<cols>) } USING <sel>
<db> : database string
<opt> : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
<table>: name of the destination table
<cols> : a comma-separated list of destination column aliases
<sel> : any valid SQL SELECT statement
sqlplus /nolog SQL> set arraysize 5000 SQL> copy from serol/luo@mescp to serol/luo@ractest create test_copy_table using select * from dba_objects; Array fetch/bind size is 5000. (arraysize is 5000) Will commit when done. (copycommit is 0) Maximum long size is 5000. (long is 5000) SQLRCN in cpytbl failed: -2120 Table TEST_COPY_TABLE created. 12579 rows selected from serol@mescp. 12579 rows inserted into TEST_COPY_TABLE. 12579 rows committed into TEST_COPY_TABLE at serol@ractest.
CSV+SQLLDR
伝統の方法であれば、複数CSVファイルにダンプし、複数SQLLDRを起動してダイレクトパスでロードするのはパラレル効果があるが、CSVファイル上のエラーチェックなどはなくて、万の一エラーがあったら、致命的な問題になる。あまりお勧めできない。
CSVの形式はカンマ区切りだから簡単と言えるが、情報にカンマがある場合、処理方法は問題になる。
解決方法はコーテーションマーク""で値を指定する。すると、情報にコーテーションマーク""があれば処理方法は問題になる。
更に、UNIX、Windowｓ、UNICODE、UTF8と多様な文字コードがある。
また、情報自体に形式説明できなくて、チェックサムがないため、問題があっても自動的な確認はできない。
DDLの取得
dbms_metadata.get_ddl('TABLE','tablename');
SQLPLUSでCSVの出力
sqlplus username/password set lines 1000 set pages 0 set colsep , set trimspool on spool data.csv select * from tablename; spool off exit;
SQLLDRでロードする