Oracle Golden Gate を試す（テーブルを結合させたい）

[前回の記事 STEP4 Oracle Golden Gate を使ってみる](https://qiita.com/blueskyarea/items/031a09802f1ad08da346)

ローカルの仮想環境に Oracle Golden Gate の環境を構築し、シンプルな同期処理が可能であることは確認できましたが、どこまで柔軟な同期処理が出来るのか、今回は実験的に以下のような同期処理が可能であるかを確認してみます。

### ケース：同期後、ターゲット側でテーブルを結合させる

Oracle Golden Gate の機能としては、複数のテーブルを結合しながら同期させるものはないようです。そのため、以下の何れかのアプローチによって、疑似的にテーブルの結合を実現します。
１．ソース側でテーブルをトリガーで結合した後、ターゲット側に同期する
２．ターゲットに結合前の複数テーブルを同期した後、ターゲット側でトリガーで結合する

今回は後者のアプローチを試行します。

#### Oracle Golden Gate を試してみる手順
１．サービスの起動など
２．テスト用テーブルの設計
３．テスト用テーブルの作成
４．テーブル結合用トリガー作成
５．Oracle Golden Gate の設定、各プロセス開始
６．同期テスト

##### １．サービスの起動など（ソース側、ターゲット側両方で実行する）
Listener や Oracleインスタンスが仮想環境の起動時にスタートアップする設定になっていない場合、仮想環境を起動する度にスタートしてあげる必要があります。
この作業はソース、ターゲット両方の仮想環境で実行します。

```:仮想環境へログイン
# ソース側
vagrant ssh myora1
# ターゲット側
vagrant ssh myora2
```

```:ソース、ターゲット両方の仮想環境で実行
# ユーザ変更
$ sudo su - oracle

# Listener起動
$ lsnrctl start LISTENER

# Oracleインスタンス 起動
$ sql / as sysdba
SQL> startup

# PDB open
SQL> alter pluggable database all open;
SQL> exit
```

##### ２．テスト用テーブルの設計
今回は以下のようなテーブルを用意したいと思います。
- ソース側に2つのテーブル
- ターゲット側に3つのテーブル（内、2つのテーブルはソース側と同じ。1つは結合後のテーブルとして扱います）

ソース側、ターゲット側の両方 テーブル名: MEMBER

|  カラム名  |  データ型  |  説明  |
| ---- | ---- | ---- |
|  member_id  |  NUMBER  |このテーブルの主キーでメンバーをユニークに管理する|
|  member_name  |  VARCHAR(100)  |メンバーの名前|
|  created_at  |  DATE  |メンバー情報の作成日時|
|  updated_at  |  DATE  |メンバー情報を更新した日時|
|  version  |  NUMBER  |このレコードが更新された回数。登録時は１で更新される度に１ずつ増える|
|  num_sync  |  NUMBER  |同期処理が行われた回数。登録時は０で、同期される度に１ずつ増える|

ソース側、ターゲット側の両方 テーブル名: COUNTRY

|  カラム名  |  データ型  |  説明  |
| ---- | ---- | ---- |
|  member_id  |  NUMBER  |このテーブルの主キーでメンバーをユニークに管理する|
|  country  |  VARCHAR(100)  |メンバーに紐づく国名|
|  created_at  |  DATE  |メンバー情報の作成日時|
|  updated_at  |  DATE  |メンバー情報を更新した日時|
|  version  |  NUMBER  |このレコードが更新された回数。登録時は１で更新される度に１ずつ増える|
|  num_sync  |  NUMBER  |同期処理が行われた回数。登録時は０で、同期される度に１ずつ増える|

ターゲット側のみ テーブル名: MEMBER_INFO

|  カラム名  |  データ型  |  説明  |
| ---- | ---- | ---- |
|  member_id  |  NUMBER  |このテーブルの主キーでメンバーをユニークに管理する|
|  member_name  |  VARCHAR(100)  |メンバーの名前|
|  country  |  VARCHAR(100)  |メンバーに紐づく国名|
|  created_at  |  DATE  |メンバー情報の作成日時|
|  updated_at  |  DATE  |メンバー情報を更新した日時|
|  version  |  NUMBER  |このレコードが更新された回数。登録時は１で更新される度に１ずつ増える|
|  num_sync  |  NUMBER  |同期処理が行われた回数。登録時は０で、同期される度に１ずつ増える|

##### ３．テスト用テーブルの作成
```:ソース側(myora1)
# Oracle PDB ログイン
$ sql c##gguser/oracle@//localhost:1521/ORCLPDB1

SQL> CREATE TABLE MEMBER (member_id NUMBER, member_name VARCHAR(100), created_at DATE, updated_at DATE, version NUMBER, num_sync NUMBER, primary key(member_id));
SQL> DESC MEMBER;

SQL> CREATE TABLE MEMBER_COUNTRY (member_id NUMBER, country VARCHAR(100), created_at DATE, updated_at DATE, version NUMBER, num_sync NUMBER, primary key(member_id));
SQL> DESC MEMBER_COUNTRY;

SQL> exit
```

```:ターゲット側(myora2)
# Oracle PDB ログイン
$ sql c##gguser/oracle@//localhost:1521/ORCLPDB1

SQL> CREATE TABLE MEMBER (member_id NUMBER, member_name VARCHAR(100), created_at DATE, updated_at DATE, version NUMBER, num_sync NUMBER, primary key(member_id));
SQL> DESC MEMBER;

SQL> CREATE TABLE MEMBER_COUNTRY (member_id NUMBER, country VARCHAR(100), created_at DATE, updated_at DATE, version NUMBER, num_sync NUMBER, primary key(member_id));
SQL> DESC MEMBER_COUNTRY;

SQL> CREATE TABLE MEMBER_INFO (member_id NUMBER, member_name VARCHAR(100), country VARCHAR(100), created_at DATE, updated_at DATE, version NUMBER, num_sync NUMBER, primary key(member_id));
SQL> DESC MEMBER_INFO;

SQL> exit
```

##### ４．テーブル結合用トリガー作成

```:ターゲット側(myora2)
# Oracle PDB ログイン
$ sql c##gguser/oracle@//localhost:1521/ORCLPDB1

# トリガー作成 (最後に / が必ず必要)
CREATE OR REPLACE TRIGGER ggInsert1
AFTER INSERT ON MEMBER_COUNTRY FOR EACH ROW
DECLARE
  v_member_id NUMBER;
  v_member_name VARCHAR(100);
  v_created_at DATE;
  v_updated_at DATE;
  v_version NUMBER;
  v_num_sync NUMBER;
BEGIN 
  SELECT member_id, member_name, created_at, updated_at, version, num_sync INTO v_member_id, v_member_name, v_created_at, v_updated_at, v_version, v_num_sync FROM MEMBER where member_id = :NEW.member_id;
  INSERT INTO MEMBER_INFO (member_id, member_name, country, created_at, updated_at, version, num_sync) VALUES (v_member_id, v_member_name, :NEW.country, v_created_at, v_updated_at, v_version, v_num_sync);
END;
/

# 終了
exit
```

##### ５．Oracle Golden Gate の設定、各プロセス開始

```:ターゲット側(myora2)
# ggsci 起動
$ /gg/ggsci

# repli2 編集
GGSCI (myora2) x> edit params repli2

# 以下のように設定する
REPLICAT repli2
USERID c##gguser@ORCLPDB1, PASSWORD oracle
MAP ORCLPDB1.c##gguser.MEMBER TARGET ORCLPDB1.c##gguser.MEMBER; COLMAP(usedefaults, NUM_SYNC=@COMPUTE(NUM_SYNC + 1));
MAP ORCLPDB1.c##gguser.MEMBER_COUNTRY TARGET ORCLPDB1.c##gguser.MEMBER_COUNTRY; COLMAP(usedefaults, NUM_SYNC=@COMPUTE(NUM_SYNC + 1));
```

では、ターゲット側の Golden Gate のプロセスを起動していきます。

```:ターゲット側(myora2)
# Manager 起動
GGSCI (myora2) x> start manager
Manager started.

# Replicat 起動
GGSCI (myora2) x> start repli2

# 起動プロセス確認
GGSCI (myora2) x> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REPLI2      00:00:00      187:31:09

# 終了
exit
```

ソース側の Golden Gate のプロセスを起動していきます。

```:ソース側(myora1)
# ggsci 起動
$ /gg/ggsci

# Manager 起動
GGSCI (myora1) x> start manager
Manager started.

# Capture 起動
GGSCI (myora1) x> start capture1

# Pump 起動
GGSCI (myora1) x> start pump1

# 起動プロセス確認
GGSCI (myora1) x> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     CAPTURE1    00:00:00      187:31:56
EXTRACT     RUNNING     PUMP1       00:00:00      187:33:13

# 終了
exit
```

##### ６．同期テスト
それではソース側のテーブルにデータを挿入し、ターゲット側に同期されるかを確認していきます。

```:ソース側(myora1)
# Oracle PDB ログイン
$ sql c##gguser/oracle@//localhost:1521/ORCLPDB1

# 自動コミットがOFFであることの確認（ONの場合、OFFにすること）
SQL> show autocommit
autocommit OFF

# Member テーブルへの挿入
SQL> INSERT INTO MEMBER (MEMBER_ID, MEMBER_NAME, CREATED_AT, UPDATED_AT, VERSION, NUM_SYNC) VALUES (1, 'Tom', sysdate, sysdate, 1, 0);

SQL> SELECT * from MEMBER where member_id = 1;

# Member_country テーブルへの挿入
SQL> INSERT INTO MEMBER_COUNTRY (MEMBER_ID, COUNTRY, CREATED_AT, UPDATED_AT, VERSION, NUM_SYNC) VALUES (1, 'USA', sysdate, sysdate, 1, 0);

SQL> SELECT * from MEMBER_COUNTRY where member_id = 1;

# コミット
commit;
```

ターゲット側に同期され、結合後のテーブルにも挿入されているか確認します。

```:ターゲット側(myora2)
# Oracle PDB ログイン
$ sql c##gguser/oracle@//localhost:1521/ORCLPDB1

SQL> SELECT * from MEMBER where member_id = 1;
  MEMBER_ID MEMBER_NAME   CREATED_AT   UPDATED_AT     VERSION   NUM_SYNC
          1 Tom           20-SEP-21    20-SEP-21            1          1

SQL> SELECT * from MEMBER_COUNTRY where member_id = 1;
  MEMBER_ID COUNTRY   CREATED_AT   UPDATED_AT     VERSION   NUM_SYNC
          1 USA       20-SEP-21    20-SEP-21            1          1

SQL> SELECT * from MEMBER_INFO where member_id = 1;
no rows selected
```

同期はされていましたが、残念ながら結合後のケーブルには挿入されていませんでした。
原因不明ですが、もしかしたら Golden Gate によって挿入された動作は、Oracle Trigger が反応していないのかもしれません。
念のため、もう一つのアプローチ「１．ソース側でテーブルを結合した後、ターゲット側に同期する」を試行します。

１．ターゲット側に作成したトリガーを無効にする

```:ターゲット側(myora2)
# Oracle PDB ログイン
$ sql c##gguser/oracle@//localhost:1521/ORCLPDB1

SQL> ALTER TRIGGER ggInsert1 DISABLE;
SQL> exit
```

２．ソース側に結合後のテーブルを作成する

```:ソース側(myora1)
# Oracle PDB ログイン
$ sql c##gguser/oracle@//localhost:1521/ORCLPDB1

SQL> CREATE TABLE MEMBER_INFO (member_id NUMBER, member_name VARCHAR(100), country VARCHAR(100), created_at DATE, updated_at DATE, version NUMBER, num_sync NUMBER, primary key(member_id));
SQL> DESC MEMBER_INFO;

SQL> exit
```

３．ソース側にトリガーを作成する

```:ソース側(myora1)
# Oracle PDB ログイン
$ sql c##gguser/oracle@//localhost:1521/ORCLPDB1

# トリガー作成 (最後に / が必ず必要)
CREATE OR REPLACE TRIGGER ggInsert1
AFTER INSERT ON MEMBER_COUNTRY FOR EACH ROW
DECLARE
  v_member_id NUMBER;
  v_member_name VARCHAR(100);
  v_created_at DATE;
  v_updated_at DATE;
  v_version NUMBER;
  v_num_sync NUMBER;
BEGIN 
  SELECT member_id, member_name, created_at, updated_at, version, num_sync INTO v_member_id, v_member_name, v_created_at, v_updated_at, v_version, v_num_sync FROM MEMBER where member_id = :NEW.member_id;
  INSERT INTO MEMBER_INFO (member_id, member_name, country, created_at, updated_at, version, num_sync) VALUES (v_member_id, v_member_name, :NEW.country, v_created_at, v_updated_at, v_version, v_num_sync);
END;
/

# 終了
exit
```

４．ターゲット側の Replicat 設定を変更し、プロセスを再起動する

```:ターゲット側(myora2)
# ggsci 起動
$ /gg/ggsci

# repli2 編集
GGSCI (myora2) x> edit params repli2

# 以下のように設定する
REPLICAT repli2
USERID c##gguser@ORCLPDB1, PASSWORD oracle
MAP ORCLPDB1.c##gguser.MEMBER_INFO TARGET ORCLPDB1.c##gguser.MEMBER_INFO; COLMAP(usedefaults, NUM_SYNC=@COMPUTE(NUM_SYNC + 1));

# Replicat プロセス再起動
GGSCI (myora2) x> stop repli2
GGSCI (myora2) x> start repli2
```

５．ソース側でレコードを挿入し、トリガーが動作しているか確認する

```:ソース側(myora1)
# Oracle PDB ログイン
$ sql c##gguser/oracle@//localhost:1521/ORCLPDB1

# 自動コミットがOFFであることの確認（ONの場合、OFFにすること）
SQL> show autocommit
autocommit OFF

# Member テーブルへの挿入
SQL> INSERT INTO MEMBER (MEMBER_ID, MEMBER_NAME, CREATED_AT, UPDATED_AT, VERSION, NUM_SYNC) VALUES (2, 'Ken', sysdate, sysdate, 1, 0);

SQL> SELECT * from MEMBER where member_id = 2;

# Member_country テーブルへの挿入
SQL> INSERT INTO MEMBER_COUNTRY (MEMBER_ID, COUNTRY, CREATED_AT, UPDATED_AT, VERSION, NUM_SYNC) VALUES (2, 'USA', sysdate, sysdate, 1, 0);

SQL> SELECT * from MEMBER_COUNTRY where member_id = 2;

# コミット
commit;

# Member Info テーブルの確認
SQL> SELECT * from MEMBER_INFO;
  MEMBER_ID MEMBER_NAME   COUNTRY   CREATED_AT   UPDATED_AT     VERSION   NUM_SYNC
          2 Ken           USA       23-SEP-21    23-SEP-21            1          0
```

６．ターゲット側に同期されているか確認する

```:ターゲット側(myora2)
# Oracle PDB ログイン
$ sql c##gguser/oracle@//localhost:1521/ORCLPDB1

SQL> SELECT * from MEMBER_INFO;
  MEMBER_ID MEMBER_NAME   COUNTRY   CREATED_AT   UPDATED_AT     VERSION   NUM_SYNC
          2 Ken           USA       23-SEP-21    23-SEP-21            1          1
```

ターゲット側に結合後のデータが同期されていることが確認できました。

##### 結論
他にやり方があるのかもしれませんが、以下の結論になりました。

１．ソース側でテーブルをトリガーで結合した後、ターゲット側に同期する
→可

２．ターゲットに結合前の複数テーブルを同期した後、ターゲット側でトリガーで結合する
→トリガーが発火しないため、不可？