GoldenGateでレプリカ表にNOT NULLの更新日付を入れる [GoldenGate]
１．はじめに
前回の記事で、GoldenGateを使ってレプリケーションする際に、更新日付を入れる方法について記載した。その後、このカラムをNOT NULLにしたとき、初期値を入れられないか、という話になり、動作確認をしたので、備忘までメモを残したい。
前回の方法では、元表には入っていない更新日付（作成日時、更新日時）を、レプリカ側の表にカラム追加した。初期同期ではNULLとしておき、GGで更新されたもののみ、作成日付や更新日付を入れる前提としていた。今回は、レプリカ側のこれらのカラムがNOT NULLになるため、初期同期のタイミングで両方のカラムに初期値を入れておきたい。
GGにおいて初期同期をするには、元表の断面をexpdpで抜き、レプリカ側の表に入れる。このとき、元表にないカラムは、NULLとなってしまう。このため、レプリカ側の定義にdefault句を設定し、systimestampを入れる必要がある。しかし、今回はdefault句を使いたくないとの要件があったため、expdpのremap_dataという機能を使った実機検証を行った。remap_dataは特定のカラムの値をパッケージ・ファンクションで変換することができるdatapumpの機能である。なお、この方法を使うには、元表にも（値はNULLで良いが）更新日付カラムが存在している必要がある点、留意する必要がある。
２．remap_dataについて
まず、remap_dataについておさらいしておこう。マニュアル（参考[1]）からの記載の抜粋は以下の通り。
REMAP_DATAパラメータを使用すると再マップ・ファンクションを指定できます。これにより、指定した列の元の値をソースとして再マップした値を返し、ダンプ・ファイル内の元の値をこの値に置き換えます。このオプションは、一般的に、本番システムからテスト・システムへ移動するときにデータをマスクするために使用されます。たとえば、クレジット・カード番号などの顧客の機密データの列を、REMAP_DATAファンクションで生成された番号に置き換えることができます。機密データを数値に置換することで、重要な書式設定および処理特性を、権限のない担当者に個人データを公開することなく保持できます。
REMAP_DATA=[schema.]tablename.column_name:[schema.]pkg.function
今回は上記機能を利用し、エクスポート時にNULLが設定されている更新日付カラムについて、systimestampを返却する関数を指定することにより、ダンプファイルにエクスポート時の時刻を設定する。具体的には、以下のようにtimestamp型を入力引数として、systimestampを返却する関数を定義する。remap_dataに渡す関数はパッケージとする必要がある点、注意が必要である。
function current_dt(p_value timestamp) return timestamp is begin return(systimestamp); end;
３．モデル
準備として、ソース(oradb.scott)にdeptrep表を作成し、初期データを投入する。DDLは以下の通り、更新日付(cre_dt, upd_dt)をnot nullなしとし、10万件の初期データではこれらカラムはnullを設定する。
drop table deptrep cascade constraints;
create table deptrep(
deptno number(7,0),
dname varchar2(14),
loc varchar2(13),
cre_dt timestamp,
upd_dt timestamp,
constraint pk_deptrep primary key (deptno)
);
conn scott/ABcd#-123qwe@oradb
insert into deptrep select
rownum deptno,
dbms_random.string('u',14) dname,
dbms_random.string('u',13) loc,
null, null
from dual connect by level <=100000;
次にターゲット(oradbrep.scott)に以下DDL実行し、同期対象となるターゲット表を作成する。こちらの更新日付(cre_dt, upd_dt)はnot nullを付与する。
conn scott/ABcd#-123qwe@oradbrep drop table deptrep cascade constraints; create table deptrep( deptno number(7,0), dname varchar2(14), loc varchar2(13), cre_dt timestamp not null, upd_dt timestamp not null, constraint pk_deptrep primary key (deptno) );
以上で準備は完了である。
４．初期同期
初期同期を行うため、ソース(oradb.scott)のdeptrepをエクスポートし、そのダンプファイルをターゲット(oradbrep.scott)にインポートする。
まず、remap_dataを用いたエクスポートを行うため、ソース側で必要となるパッケージ・ファンクションを作成する。
create or replace package remappkg as function current_dt(p_value timestamp) return timestamp; end; / create or replace package body remappkg as function current_dt(p_value timestamp) return timestamp is begin return(systimestamp); end; end; /
expdpコマンドにremap_dataを指定し、deptrepをエクスポートする。remap_dataはカラム毎に変換する関数をパッケージ名.ファンクション名で指定する。
expdp scott/xxx@oradb tables=scott.deptrep directory=expdir dumpfile=scott_deptrep.dmp logfile=expdpscott_deptrep.log content=data_only reuse_dumpfiles=yes remap_data=scott.deptrep.cre_dt:remappkg.current_dt remap_data=scott.deptrep.upd_dt:remappkg.current_dt
上記でエクスポートされたダンプファイルを、ターゲット(oradbrep.scott)のdeptrep表にインポートする。not null制約のある更新日付には、すでにダンプファイルに値が入っているため、特別なことを行うことなくインポートが完了する。
impdp scott/xxx@oradbrep tables=scott.deptrep directory=expdir dumpfile=scott_deptrep.dmp logfile=impdpscott_deptrep.log content=data_only table_exists_action=truncate
５．Goldengateの設定
Goldengateの定義を変更する。Extractの設定については、今回作成したdeptrep表を追加する必要がある。ただ、すでに以下のような設定で抽出対象に含まれているため、変更は不要であった。
TABLE ORADB.SCOTT.*;
続いてReplicatの設定であるが、ここには前回同様に、以下のようにMAP文を指定して、更新日付を入れるように設定を行う。なお、upd_dtの更新で、INSERTの時にも更新日付を入れるようにしているのは、INSERT時にNOT NULL違反となることを避けるためである。
MAP SCOTT.DEPTREP, TARGET SCOTT.DEPTREP,
COLMAP(USEDEFAULTS,
cre_dt=@IF(@STREQ(@GETENV('GGHEADER','OPTYPE'),'INSERT'),@DATENOW(),@COLSTAT(MISSING)),
upd_dt=@IF(@VALONEOF(@GETENV('GGHEADER','OPTYPE'), 'INSERT', 'UPDATE', 'SQL COMPUPDATE', 'PK UPDATE'), @DATENOW(), @COLSTAT(MISSING)));
上記変更が完了したら、GGのプロセス（エクストラクト、レプリキャット）を停止、起動を行う。
６．動作確認
動作確認として、ソース側のdeptrepをINSERT、UPDATE（2回）、DELETEを実行し、ターゲット側のdeptrepの更新カラムを確認した。INSERT実行時に、cre_dt, upd_dt両方に更新日付が入っている。その後の更新では、upd_dtのみが更新されていることがわかる。
-- insert a record
insert into deptrep values (100001,'support','tokyo',null,null);
commit;
08:09:04 SCOTT@oradb> select * from deptrep where deptno=100001;
DEPTNO DNAME LOC CRE_DT UPD_DT
---------- -------------- ------------- ----------------------------------- -----------------------------------
100001 support tokyo
-- target
08:09:19 SCOTT@oradbrep> select * from deptrep where deptno=100001;
DEPTNO DNAME LOC CRE_DT UPD_DT
---------- -------------- ------------- ----------------------------------- -----------------------------------
100001 support tokyo 2024/04/21 08:20:04.000000 2024/04/21 08:20:04.000000
-- update a record #1
update deptrep set loc='saitama' where deptno=100001;
commit;
-- target
08:22:53 SCOTT@oradbrep> select * from deptrep where deptno=100001;
DEPTNO DNAME LOC CRE_DT UPD_DT
---------- -------------- ------------- ----------------------------------- -----------------------------------
100001 support saitama 2024/04/21 08:20:04.000000 2024/04/21 08:22:55.000000
-- update a record #2
update deptrep set loc='kanagawa' where deptno=100001;
commit;
-- target
08:23:07 SCOTT@oradbrep> select * from deptrep where deptno=100001;
DEPTNO DNAME LOC CRE_DT UPD_DT
---------- -------------- ------------- ----------------------------------- -----------------------------------
100001 support kanagawa 2024/04/21 08:20:04.000000 2024/04/21 08:23:10.000000
-- delete a record #1
delete from deptrep where deptno=100001;
commit;
-- target
08:23:30 SCOTT@oradbrep> select * from deptrep where deptno=100001;
no rows selected
７．まとめ
本稿では、GoldenGateでNOT NULLの更新日付を入れる方法について、初期同期の方法を含めて記載した。レプリカ表でdefault句を使えない場合、ソース側と初期同期を行うためには、更新日付に初期値をどう入れるかが問題となる。元表側にも更新日付カラムを付与することが条件となるが、ダンプのremap_dataを用いて初期値をソース側で設定したダンプファイルを作成できる。この方法であれば、レプリカ側は通常の方法でダンプをインポートするだけでよい。remap_dataに渡すためのファンクションを作成する必要はあるが、元表のエクスポート側でひと手間かけるだけで良い。
なお、本稿ではexpdp時にremap_dataを用いたが、remap_data自体はimpdpでも利用できる。ターゲットDB側に変換関数を作成し、null値をインポートのタイミングのsystimestampに設定することも原理的には可能だろう。この場合においても、ソースDB側には更新日付のカラムは必須である。remap_dataはあくまで変換するカラムが存在することが前提で、ダンプに存在しないカラムを追加で設定することはできない。
GoldenGateでターゲット側に更新日付を入れる必要がある場合の参考になれば幸いである。
参考
[1]Oracle Database ユーティリティ 19c, 2.4.38 REMAP_DATA
2024-04-21 21:01
