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
GoldenGateでレプリカ表に更新日付を入れる [GoldenGate]
１．はじめに
最近の案件で、GoldenGateを使ってレプリケーションする際に、更新日付を入れられないか、という問い合わせがあった。初回INSERT時と、UPDATEされたときに、それぞれ更新日付を記録したいという要件である。実現方式を調べて、OCIで動作確認したので、備忘までメモしておく。
検証はOCIで、BaseDBと、適当なコンピュート上に構成したGoldenGate（マイクロサービス）で行った。BaseDBは1台の上に、ORADBとORADBREPという２つのPDBを構成し、ORADBのEMP表をORADBREPにレプリケーションした。その際、更新日付のない通常のレプリケーション（EMP表）と更新日付のあるレプリケーション（EMPREP表）の２つのレプリカを作成した。
２．検証モデル
ソース側となるORADBのEMP表は以下の通り。初期データとして100万件入れておく。また、レプリカの初期同期用にダンプを作成する。
EMP表 Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(7) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(7) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(7)
ターゲット側となるORADBREPのEMP表、EMPREP表は以下の通り。初期データとして100万件入れておく。EMPREP表には、CRE_DTとUPD_DTが追加されている。初期データとしてソースのダンプをインポートする。EMPREPはカラムが追加されているので、インポート時はNULLで入る。
EMP表 Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(7) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(7) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(7) EMPREP表 Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(7) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(7) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(7) CRE_DT TIMESTAMP(6)★作成日付 UPD_DT TIMESTAMP(6)★更新日付
次にGGの設定である。エクストラクトの設定は以下の通り。トレイルとして出力する対象として、ORADBのSCOTTスキーマのオブジェクトを指定した。
EXTRACT EORADB USERIDALIAS CORADB_ORADBVM1 DOMAIN ORADBVM1 EXTTRAIL AA TABLE ORADB.SCOTT.*;
レプリキャットの設定は以下の通り。非統合レプリキャットを使った。MAP文でEMP表とEMPREP表にレプリケートする設定としている。このうち、EMPは単純なMAP文（単純に全てのカラムをマッピング）しているが、EMPREPの方はCOLMAPでCRE_DTとUPD_DTカラムに更新日付を入れる処理を追加している。ここが今回のポイントである。
REPLICAT RORAREP
USERIDALIAS ORADBREP DOMAIN ORADBVM1
SOURCECATALOG ORADB
MAP SCOTT.EMP, TARGET SCOTT.EMP;
MAP SCOTT.EMP, TARGET SCOTT.EMPREP,
COLMAP(USEDEFAULTS,
cre_dt=@IF(@STREQ(@GETENV('GGHEADER','OPTYPE'),'INSERT'),@DATENOW(),@COLSTAT(MISSING)),
upd_dt=@IF(@VALONEOF(@GETENV('GGHEADER','OPTYPE'), 'UPDATE', 'SQL COMPUPDATE', 'PK UPDATE'), @DATENOW(), @COLSTAT(MISSING)));
少し補足すると、@GETENV('GGHEADER','OPTYPE')はDMLの種類を返す関数である。この返り値がINSERTなのかUPDATEなのかを、@STREQで文字列が一致するか、@VALONEOFでいくつかの選択肢のうち１つが一致すれば真を返す関数を利用し、@IFで切り替える。設定値は、現在時刻である@DATENOWを設定するか、何も設定しない@COLSTAT(MISSING)を設定する。なお、UPDATEとSQL COMPUPDATEの違いは、UPDATEが全てのカラムを含むのに対し、SQL COMPUPDATEは更新対象となるカラムしか含まない場合である（Oracleは通常更新対象となるカラムしかREDOに含まないが、SQL Serverなどでは全てのカラムをログに含めるものもある）。
３．動作確認
ここでは、ソース側のEMP表EMPNO=1000001のレコードをINSERT、UPDATE（2回）、DELETEを実行して、ターゲット側の状態を確認した。まず、INSERT後の状態である。正しくターゲットのEMP、EMPREP表にレコードが伝搬されている。また、EMPREPには、作成日を示すCRE_DTが入っており、UPD_DTはNULLのままである。
19:57:27 SCOTT@oradb> @insemp
Connected.
19:57:30 SCOTT@oradb> @ckemp
Connected.
★ソース：EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
1000001 kazuhiro dba 1 20240408 19:57:30 1000 1000 1
Connected.
★ターゲット：EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
1000001 kazuhiro dba 1 20240408 19:57:30 1000 1000 1
★ターゲット：EMPREP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO CRE_DT UPD_DT
---------- ---------- --------- ---------- ----------------- ---------- ---------- ---------- -------------------- --------------------
1000001 kazuhiro dba 1 20240408 19:57:30 1000 1000 1 20240408 19:57:32
次にUPDATE後の状態である。正しくターゲットのEMP、EMPREP表にSAL,COMが+1されて伝搬されている。また、EMPREPには、更新日付を示すUPD_DTが入る。
19:57:35 SCOTT@oradb> @updemp
Connected.
19:57:39 SCOTT@oradb> @ckemp
Connected.
★ソース：EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
1000001 kazuhiro dba 1 20240408 19:57:39 1001 1001 1
Connected.
★ターゲット：EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
1000001 kazuhiro dba 1 20240408 19:57:39 1001 1001 1
★ターゲット：EMPREP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO CRE_DT UPD_DT
---------- ---------- --------- ---------- ----------------- ---------- ---------- ---------- -------------------- --------------------
1000001 kazuhiro dba 1 20240408 19:57:39 1001 1001 1 20240408 19:57:32 20240408 19:57:42
もう一度UPDATEを実行した後の状態である。正しくターゲットのEMP、EMPREP表にSAL,COMが+1されて伝搬されている。EMPREPは、更新日付を示すUPD_DTが更新されている。
19:57:48 SCOTT@oradb> @updemp
Connected.
19:57:53 SCOTT@oradb> @ckemp
Connected.
★ソース：EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
1000001 kazuhiro dba 1 20240408 19:57:53 1002 1002 1
Connected.
★ターゲット：EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
1000001 kazuhiro dba 1 20240408 19:57:53 1002 1002 1
★ターゲット：EMPREP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO CRE_DT UPD_DT
---------- ---------- --------- ---------- ----------------- ---------- ---------- ---------- -------------------- --------------------
1000001 kazuhiro dba 1 20240408 19:57:53 1002 1002 1 20240408 19:57:32 20240408 19:57:58
DELETEを実行した後は、正しくターゲットのEMP、EMPREP表がDELETEされることも確認した。
４．まとめ
本稿では、GoldenGateを使ってレプリカを作成する際に、更新日付を追加する方法について、実機検証した結果を記載した。日付を入れる処理ロジックは若干複雑であるが、このような柔軟な対応ができるのは、GoldenGateの良いところである。Goldengateの構成方法についてはまた別の機会に触れたいと思う。
参考
[1]Reference for Oracle GoldenGate, 2.18 @GETENV
2024-04-09 23:36
