Oracle Data Pumpによるデータ変換

Oracle Data Pump によるデータ変換 Oracle ホワイト ペーパー 2007 年 6 月

Oracle Data Pump によるデータ変換 はじめに DBA は データベースからエクスポートするデータまたはデータベースにインポートするデータを変更しなければならない場合があります エクスポート作業の一環として クレジットカード番号や社会保障番号などの機密データを削除する必要があります 同様に DBA はインポートの表データを更新して 内部の識別番号と既存データとの衝突を回避しようとする場合もあります 従来 こうした変換を行うためには 変更前にいくつもの手順を踏み データのステージングを行う必要がありました Oracle Database 11g Release 1 では REMAP_DATA という新しいパラメータが提供されており これらの手順を expdp コマンドおよび impdp コマンドで自動化することができます REMAP_DATA パラメータは Oracle Data Pump がデータ変換をする際に ユーザーが入力した PL/SQL 関数でユーザーのデータを変更できるようにするものです このホワイト ペーパーでは REMAP_DATA パラメータの機能について解説します エクスポートの例 たとえば データベースに次のような 2 つの表で構成されるスキーマがあるとします CREATE TABLE HOLDER ( NAME VARCHAR2(100), CARDNO NUMBER ); CREATE TABLE ACTIVITY ( VENDOR VARCHAR2(100), AMOUNT NUMBER, CARDNO NUMBER ); 最初の表は クレジットカードの所持者を示します 2 つ目の表は クレジットカードの取引を追跡するものです CARDNO 列は 2 つの表が示すカード保持者と請求内容を結び付ける共通鍵として使用します 仮に 次のサンプル データが 2 つの表に挿入されたとします Oracle Data Pump によるデータ変換 2

-- HOLDER table: NAME CARDNO -------- ------------------ John 1234567890123456 Dean 2345678901234561 Steve 3456789012345612 -- ACTIVITY table: VENDOR AMOUNT CARDNO -------- ------- ----------------- Telco $26.43 1234567890123456 CatTV $87.54 1234567890123456 Grocer $36.28 3456789012345612 Telco $13.22 2345678901234561 Telco $37.17 3456789012345612 開発グループはこのスキーマにアクセスして このデータに対応するアプリケーションの最新バージョンが何であるかを確認する必要があります しかし 開発者がセキュリティを侵害する恐れがあるので DBA はデータのコピーを提供する前に暗号化する必要があります アプリケーションを使用するためにも DBA は (1) 暗号化された数字の最初と最後の桁に変更がないことを検証テストで確認し (2)2 つの表で一貫したスクランブリングが行われており 参照関係が維持されていることを確かめる必要があります これらの課題を解決するため DBA は変換関数を含む次のパッケージを構築します CREATE OR REPLACE PACKAGE hidedata AUTHID CURRENT_USER AS FUNCTION newcc (oldid IN NUMBER) RETURN NUMBER; END hidedata; / CREATE OR REPLACE PACKAGE BODY hidedata AS TYPE cc_list IS TABLE OF NUMBER INDEX BY VARCHAR2(16); cc_remaps cc_list; cc_seed NUMBER := 000000000000010; FUNCTION newcc (oldid IN NUMBER) RETURN NUMBER IS BEGIN IF NOT cc_remaps.exists(oldid) THEN cc_seed := cc_seed + 10; cc_remaps(oldid) := ROUND (oldid, -15) + -- 1st digit cc_seed + -- 2nd-15 th digits MOD(oldid,10) ; -- 16 th digit END IF; RETURN cc_remaps(oldid); END; END hidedata; / HIDEDATA パッケージにおいて CC_REMAPS 構造はすでに表示されたクレジットカード番号に対して割り当てられた最新の値を記憶するために使用されます これにより ジョブ間の表は一貫性を持って変換されます 再マッピングされた ID の割当てでは 16 桁のクレジットカード番号のうち アプリケーションで必要となる最初と最後の数字を安全に保持します Oracle Data Pump によるデータ変換 3

クレジットカード番号を隠すパッケージの準備が整ったら DBA はパッケージを 使用してエクスポートを実行できます expdp myuser/pw tables=holder,activity remap_data=holder.cardno:hidedata.newcc remap_data=activity.cardno:hidedata.newcc directory=dpump_dir dumpfile=hremp2.dmp REMAP_DATA パラメータは エクスポート時に変換が必要な列を識別します 各 REMAP_DATA パラメータには コロンで分けられた 2 つの引数が含まれています 1 つ目の引数は 再マッピングされる列を識別するものです オプションでスキーマ名を含む場合もありますが 常に表内の表および列を指定する必要があります 2 つ目の引数は 列の再マッピングで使用した関数を指定するものです オプションでスキーマ名を含む場合もありますが 常に再マッピングを実行する機能を持つパッケージ内のパッケージおよび関数を指定する必要があります 入力された関数は 変換される列と同じデータ型を持つ単一のパラメータを受け入れる必要があります また 同じデータ型を返さなければなりません 次の例で変換されているデータ型は NUMBER です Oracle Data Pump が HOLDER 表の各行を処理するごとに CARDNO 列の値は前の CARDNO 値を入力値として HIDEDATA.NEWCC 関数を呼び出した結果と置き換えられます HIREDATA パッケージの性質により これらの変換は類似するクレジットカード番号を識別するため CC_LIST 表にキャッシュされます これは ACTIVITY 表の値をキャッシュするのと同じ構造であり 2 つの表の間で一貫した変換を行うことができます このように CARDNO 列で 2 つのテーブルを結合することで 再マッピングの前後で同じ結果を表示できます hremp2.dmp ファイルをインポートすると 次に示すとおり 2 つの表にある CARDNO 列が新規データに置き換わります Oracle Data Pump によるデータ変換 4

-- HOLDER table: NAME CARDNO -------- ------------------ John 1000000000000026 Dean 2000000000000041 Steve 3000000000000032 -- ACTIVITY table: VENDOR AMOUNT CARDNO ------ ------- ----------------- Telco $26.43 1000000000000026 CatTV $87.54 1000000000000026 Grocer $36.28 3000000000000032 Telco $13.22 2000000000000041 Telco $37.17 3000000000000032 各表において CARDNO 列のデータは機密データを保護するために隠されています ただし 値の再割当ての一貫性は保たれています これは CARDNO 列に基づいて結合されたものが元のデータベースと同じ結果になるようにするためです コードにあるとおり HIDEDATA パッケージにはいくつかの制限があります CC_LIST PL/SQL 表はメモリ内に格納されているため キャッシュできる値の数に制限があります HOLDER 表および ACTIVITY 表間の処理でジョブが停止した場合 CC_LIST の内容がセッション間で永続化されていないため Oracle Data Pump を再起動すると変換データの一貫性は失われます 同じ理由で ジョブで PARALLEL=2 が使用されていると結果の一貫性は保たれません これは 各スレッドの実行では異なる CC_LIST 表のコピーが使用されるためです DBA は 再マッピングが要求されるたびに自動変換で更新されている永続化データベース表で CC_LIST PL/SQL 表を置き換えることにより 問題を回避できます ほかにも DATA_REMAP パラメータには実行できない制限があります このパラメータはデータを変更するため データを移動するトランスポータブル メソッドを使用する Oracle Data Pump のジョブでは利用できません また LONG データ型または LONG RAW データ型の列を含む表を変更するために使用することもできません 再マッピングを実行する関数は Oracle Data Pump のジョブを実行するために使用したユーザー名で実施されます そのため DBA は信頼できるソースから入手したものではない再マッピングのパッケージを使用する際に 細心の注意を払う必要があります これらの関数は DBA のアカウントに与えられた高い権限を使用するので データベース内を危険にさらすこともできるからです DBA は Oracle Data Pump のジョブ内で使用する前に 再マッピング関数の動作を確実に理解しておく必要があります Oracle Data Pump によるデータ変換 5

インポートの例 REMAP_DATA パラメータは インポート時にも利用できます インポート時における REMAP_DATA の一般的な利用方法は 同格のデータベースが中央データベースのプロビジョニングに統合された際に 固有の識別子をリセットすることです たとえば ニューヨークとロサンゼルスに 2 つのデータベースがあると仮定します インポートの目標は これらデータベースを単一インスタンスに統合することです データベースの個人情報は 次のような表に格納されています CREATE TABLE EMP ( NAME VARCHAR2(10), EMPNO NUMBER ); CREATE TABLE TIMECARD ( HOURS NUMBER, WEEK DATE, EMPNO NUMBER ); 1 つ目の表は 企業の従業員を示します 2 つ目の表は 労働時間を追跡するものです EMPNO 列は 2 つの表が示す従業員とタイムカードを結び付ける共通鍵として使用します ニューヨークのこれらの表のコピーは次のようになります -- Table EMP for New York NAME EMPNO -------- ----- Helen 1 Lee 2 Rod 3 Stu 4 -- Table TIMECARD for Los Angeles HOURS WEEK EMPNO ----- ------ --- 42 02-APR 1 43 09-APR 1 23 02-APR 2 15 09-APR 2 45 02-APR 3 47 09-APR 3 41 02-APR 4 49 09-APR 4 Oracle Data Pump によるデータ変換 6

これらの表で ロサンゼルスのコピーは次のようになります -- Table EMP for Los Angeles NAME EMPNO -------- ----- Jim 1 Mike 2 Cindy 3 -- Table TIMECARD for Los Angeles HOURS WEEK EMPNO ----- ------ --- 16 02-APR 1 22 09-APR 1 38 02-APR 2 46 09-APR 2 44 02-APR 3 48 09-APR 3 このアプリケーションにおいて 新しい従業員 ID は LASTEMP シーケンスによって割り当てられます 従業員は両データベースで同じ従業員番号が割り当てられているため データをインポートする際は ロサンゼルスの従業員が使用している番号を再マッピングする必要があります エクスポートの例では 従業員のタイムカード データが本人を追跡するよう 2 つの表の間でデータの一貫性を保つ形で再マッピングしました 次のパッケージでは インポートの再マッピング関数をサポートするよう定義しています CREATE OR REPLACE PACKAGE BODY fixusers AS TYPE id_list IS TABLE OF NUMBER INDEX BY VARCHAR2(16); id_remaps id_list; FUNCTION newempid (oldempid IN NUMBER) RETURN NUMBER IS newid NUMBER; BEGIN IF NOT id_remaps.exists(oldempid) THEN -- Allocate a value from sequence LASTEMP SELECT lastemp.nextval INTO newid FROM DUAL; id_remaps(oldempid) := newid; END IF; RETURN id_remaps(oldempid); END; END fixusers; / このパッケージでは LASTEMP シーケンスから新規の従業員 ID を割り当てるための関数を定義し インポートされた従業員の値を再マッピングするのに使用しています エクスポートの例では 値はローカル表にキャッシュされ 発生したすべての値は同じターゲット値に再マッピングされます これで ロサンゼルスの従業員情報を含むダンプ ファイル (laemps.dmp) をインポートする準備が整いました 次のコマンドを実行すると 新規の従業員 ID にインポートされたユーザーすべてを再マッピングしながら ダンプ ファイルをインポートします Oracle Data Pump によるデータ変換 7

impdp user2/pw directory=dpump_dir dumpfile=laemps.dmp remap_data=emp.empno:fixusers.newempid remap_data=timecard.empno:fixusers.newempid table_exists_action=append インポートの実行後 マージされた表は次のようになります -- Combined EMP table NAME EMPNO -------- ----- Helen 1 Lee 2 Rod 3 Stu 4 Jim 5 Mike 6 Cindy 7 --Combined TIMECARD table HOURS WEEK EMPNO ----- ------ --- 42 02-APR 1 43 09-APR 1 23 02-APR 2 15 09-APR 2 45 02-APR 3 47 09-APR 3 41 02-APR 4 49 09-APR 4 16 02-APR 5 22 09-APR 5 38 02-APR 6 46 09-APR 6 44 02-APR 7 48 09-APR 7 Jim の EMPNO が 両方の表で 1 から 5 に変更されたことを確認してください 同じような多くの制限は エクスポートの例にある HIDEDATA パッケージに適用された FIXUSERS パッケージにも当てはまります 特に インメモリの ID_REMAPS 表で取り扱えるデータベースのサイズは制限されています さらに インポート ジョブの再実行や高度な並列処理の実行は 予測できない結果を招く可能性があります エクスポートの例でも述べたとおり これらの制限は再マッピング情報をキャッシュする永続的な格納を使用できるようパッケージを再記述すれば緩和できます Oracle Data Pump によるデータ変換 8

結論 Oracle Data Pump 内の新しい DATA_REMAP パラメータは エクスポートおよびインポートのジョブ実行中にデータの値を利用する強力なツールです このホワイト ペーパーのエクスポート例では エクスポート時に列を隠すことで 機密性の高い顧客情報を保護する方法を紹介しました インポート例では ターゲットのデータベースの要件に一致するようデータを加工する方法を紹介しました こうした例は Oracle データベース間で移動させる必要のあるデータを管理する上で DBA を補助する ごく一部の機能に過ぎません DATA_REMAPは強力なツールですが DBA が信頼する関数でのみ使用するよう 細心の注意を払ってください Oracle Data Pump によるデータ変換 9

Oracle Data Pump によるデータ変換 2007 年 5 月著者 :Bill Fisher Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA 94065 U.S.A. 海外からのお問合せ窓口 : 電話 : +1.650.506.7000 ファクシミリ : +1.650.506.7200 www.oracle.com Copyright 2007, Oracle.All rights reserved. 本文書は情報提供のみを目的として提供されており ここに記載される内容は予告なく変更されることがあります 本文書は一切間違いがないことを保証するものではなく さらに 口述による明示または法律による黙示を問わず 特定の目的に対する商品性もしくは適合性についての黙示的な保証を含み いかなる他の保証や条件も提供するものではありません オラクル社は本文書に関するいかなる法的責任も明確に否認し 本文書によって直接的または間接的に確立される契約義務はないものとします 本文書はオラクル社の書面による許可を前もって得ることなく いかなる目的のためにも 電子または印刷を含むいかなる形式や手段によっても再作成または送信することはできません Oracle は米国 Oracle Corporation およびその子会社 関連会社の登録商標です その他の名称はそれぞれの会社の商標です