【再掲載：2021/5/13現在】Autonomous Database間のOracle GoldenGateによるデータ同期(2/3)

2022年11月7日 Autonomous Database

1 前回のあらすじ
2 ３. ソース側データベースの設定
3 ４．ターゲット側データベースの設定
4 次回へ続く

前回のあらすじ

こんにちは。プラチナホルダーの小酒井です。

前回は Autonomous Database と Goldengate のプロビジョニングを行いました。
今回はソース側、ターゲット側それぞれのデータベースの設定と同期設定を行います。

Autonomous Databaseのプロビジョニング（前回）
Goldengate 19c Marketplace Microservices Editionのプロビジョニング（前回）
ソース側データベースの設定
ターゲット側データベースの設定
同期確認（次回）
DDL文の同期確認（次回）

早速ですが、宜しくお願いします！

今回はプロンプト上での作業になります。
宜しくお願いします！

３. ソース側データベースの設定

同期対象のユーザとテーブルを作成

同期対象として、testuserとテーブルtab1を作成し、サンプルデータを登録します。

-bash-4.2$ export ORACLE_HOME='/u01/app/client/oracle19'
-bash-4.2$ export TNS_ADMIN='/u02/deployments/Source/etc/'
-bash-4.2$ /u01/app/client/oracle19/bin/sqlplus admin/****************@source_high


SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 13 06:25:26 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Last Successful login time: Mon Apr 12 2021 04:56:18 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> 
SQL> create user testuser identified by **************** default tablespace DATA quota unlimited on DATA;

User TESTUSER created.

SQL> create table testuser.tab1 (id number primary key, name varchar2(10));

Table TESTUSER.TAB1 created.

SQL> insert into testuser.tab1 values (1, 'aaa');

1 row inserted.

SQL> commit;

Commit complete.

SQL> select * from testuser.tab1 order by id;

ID NAME 
---------- ----------
1 aaa

ggadminユーザのロック解除とサプリメンタル・ロギングの有効化

Oracle GoldenGate 管理者スキーマであるggadminユーザのロックを解除し、プラガブル・データベースのサプリメンタル・ロギングを有効化します。
また、ソース側データベースの現在のSCNを確認します。

SQL> alter user ggadmin identified by **************** account unlock;

User GGADMIN altered.

SQL> alter pluggable database add supplemental log data;

Pluggable database ADD altered.

SQL> select minimal from dba_supplemental_logging;

MIN
---
YES

SQL> select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN) 
----------------------------------------
37098882721526

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
-bash-4.2$

Extractパラメータ・ファイルの作成

ソース側データベースからデータ抽出に必要となるExtractプロセスのパラメータ・ファイルを作成します。

-bash-4.2$ mkdir /u02/trails/dirdat
-bash-4.2$ ls -ld /u02/trails/dirdat
drwxrwxr-x. 2 opc opc 4096 Apr 12 01:47 /u02/trails/dirdat
-bash-4.2$ vi /u02/deployments/Source/etc/conf/ogg/ext1.prm
-bash-4.2$
-bash-4.2$ cat /u02/deployments/Source/etc/conf/ogg/ext1.prm
extract ext1
useridalias source_high
exttrail /u02/trails/dirdat/lt
ddl include mapped
table testuser.*;
-bash-4.2$

Extractプロセスの作成

Extractプロセスを作成し、ソース側データベースに登録します。

-bash-4.2$ /u01/app/ogg/oracle19/bin/adminclient
Oracle GoldenGate Administration Client for Oracle
Version 19.1.0.0.201013 32153823

Copyright (C) 1995, 2020, Oracle and/or its affiliates. All rights reserved.

Linux, x64, 64bit (optimized) on Nov 16 2020 20:36:08
Operating system character set identified as UTF-8.

OGG (not connected) 2> CONNECT https://localhost/ deployment Source as oggadmin password **************** !

OGG (https://localhost/ Source) 3> alter credentialstore add user ggadmin@source_high password **************** alias source_high
2021-04-12T01:58:21Z INFO OGG-15102 Credential store created.
2021-04-12T01:58:21Z INFO OGG-15116 No information found in credential store.
2021-04-12T01:58:21Z INFO OGG-15114 Credential store altered.

OGG (https://localhost/ Source) 4> info credentialstore

Default domain: OracleGoldenGate

Alias: source_high
Userid: ggadmin@source_high

OGG (https://localhost/ Source) 5> dblogin useridalias source_high
Successfully logged into database.

OGG (https://localhost/ Source as source_high@E2U1POD) 6> info all
Program Status Group Type Lag at Chkpt Time Since Chkpt

ADMINSRVR RUNNING 
DISTSRVR RUNNING 
PMSRVR RUNNING 
RECVSRVR RUNNING

OGG (https://localhost/ Source as source_high@E2U1POD) 7> add extract ext1, integrated tranlog, scn 37098882721526
2021-04-12T01:59:54Z INFO OGG-08100 EXTRACT (Integrated) added.
2021-04-12T01:59:54Z INFO OGG-08100 EXTRACT altered.

OGG (https://localhost/ Source as source_high@E2U1POD) 8> register extract ext1 database
2021-04-12T02:00:31Z INFO OGG-02003 Extract EXT1 successfully registered with database at SCN 37098890477188.

OGG (https://localhost/ Source as source_high@E2U1POD) 9> add exttrail /u02/trails/dirdat/lt, extract ext1
2021-04-12T02:01:06Z INFO OGG-08100 EXTTRAIL added.

OGG (https://localhost/ Source as source_high@E2U1POD) 10> info all
Program Status Group Type Lag at Chkpt Time Since Chkpt

ADMINSRVR RUNNING 
DISTSRVR RUNNING 
PMSRVR RUNNING 
RECVSRVR RUNNING 
EXTRACT STOPPED EXT1 INTEGRATED 00:00:00 00:01:16

OGG (https://localhost/ Source as source_high@E2U1POD) 11> start extract ext1
2021-04-12T02:01:19Z INFO OGG-00975 EXTRACT EXT1 starting
2021-04-12T02:01:19Z INFO OGG-15426 EXTRACT EXT1 started

OGG (https://localhost/ Source as source_high@E2U1POD) 12> info all
Program Status Group Type Lag at Chkpt Time Since Chkpt

ADMINSRVR RUNNING 
DISTSRVR RUNNING 
PMSRVR RUNNING 
RECVSRVR RUNNING 
EXTRACT RUNNING EXT1 INTEGRATED 00:00:00 00:01:33

OGG (https://localhost/ Source as source_high@E2U1POD) 13> info extract ext1, detail

EXTRACT EXT1 Last Started 2021-04-12 02:01 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:06 ago)
Process ID 26798
Log Read Checkpoint Oracle Integrated Redo Logs
First Record 
SCN 8637.3250185974 (37098882721526)
Encryption Profile LocalWallet

Target Extract Trails:

Trail Name Seqno RBA Max MB Trail Type

/u02/trails/dirdat/lt 0 1277 500 EXTTRAIL


Integrated Extract outbound server first scn: 8637.3257941636 (37098890477188)

Integrated Extract outbound server filtering start scn: 8637.3257941636 (37098890477188)

Extract Source Begin End

Not Available * Initialized * First Record 
Not Available * Initialized * First Record 
Not Available * Initialized * First Record 
Not Available * Initialized * First Record 
Not Available * Initialized * 2021-04-12 01:59


Current directory /

Report file /u02/deployments/Source/var/lib/report/EXT1.rpt
Parameter file /u02/deployments/Source/etc/conf/ogg/ext1.prm
Checkpoint file /u02/deployments/Source/var/lib/checkpt/EXT1.cpe
Process file /u02/deployments/Source/var/run/EXT1.pce
Error log /u02/deployments/Source/var/log/ggserr.log

OGG (https://localhost/ Source as source_high@E2U1POD) 14>

同期対象テーブルにデータを登録

ソース側の同期対象テーブルに、データを登録します。

SQL> insert into testuser.tab1 values (2, 'bbb');

1 row inserted.

SQL> insert into testuser.tab1 values (3, 'ccc');

1 row inserted.

SQL> commit;

Commit complete.

SQL> select * from testuser.tab1 order by id;

ID NAME 
---------- ----------
1 aaa 
2 bbb 
3 ccc

SQL> 
SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
-bash-4.2$

同期対象テーブルのExportダンプを取得

現時点ではターゲット側データベースには、対象ユーザと対象テーブルが存在しておらず、このタイミングでReplicatプロセスを作成して同期が開始されると、id=2とid=3のデータの、ターゲット側データベースへのinsertでエラーとなります。
そのため、ソース側データベースのSCN 37098882721526時点（id=1のデータが登録されている時点）のデータを、ターゲット側データベースに初期移行する必要があります。
これには、Data Pump Export でFLASHBACK_SCNパラメーターを使用します。

-bash-4.2$ /u01/app/client/oracle19/bin/expdp admin/****************@source_high \
> directory=DATA_PUMP_DIR \
> dumpfile=export01.dmp \
> logfile=export.log \
> schemas=testuser \
> FLASHBACK_SCN=37098882721526

Export: Release 19.0.0.0.0 - Production on Mon Apr 12 02:12:35 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "ADMIN"."SYS_EXPORT_SCHEMA_01": admin/********@source_high directory=DATA_PUMP_DIR dumpfile=export01.dmp logfile=export.log schemas=testuser FLASHBACK_SCN=37098882721526 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "TESTUSER"."TAB1" 5.484 KB 1 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "ADMIN"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ADMIN.SYS_EXPORT_SCHEMA_01 is:
/u03/dbfs/BFBCAE290DBA39F0E0535410000A83B7/data/dpdump/export01.dmp
Job "ADMIN"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Apr 12 02:13:16 2021 elapsed 0 00:00:37

-bash-4.2$

Exportダンプファイルをオブジェクト・ストレージにアップロード

ソース側データベースのDATA_PUMP_DIRディレクトリ・オブジェクトに出力されたExportダンプファイルをオブジェクト・ストレージにアップロードします。
アップロード前に、DBMS_CLOUD.CREATE_CREDENTIALプロシージャを使用して、オブジェクト・ストアの資格証明をソース側データベースに格納します。

-bash-4.2$ /u01/app/client/oracle19/bin/sqlplus admin/****************@source_high

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 12 04:08:53 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Last Successful login time: Mon Apr 12 2021 04:08:42 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> 
SQL> BEGIN
2 DBMS_CLOUD.CREATE_CREDENTIAL(
3 credential_name => 'LOAD_DATA',
4 username => 'oracleidentitycloudservice/t-kozakai@sts-inc.co.jp',
5 password => '*******************));'
6 );
7 END;
8 /

PL/SQL procedure successfully completed.

SQL> 
SQL> BEGIN
2 DBMS_CLOUD.PUT_OBJECT(
3 credential_name=>'LOAD_DATA',
4 object_uri=>'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/nrqmmb9ftjj0/b/bucket-20210412-1120/o/export01.dmp',
5 directory_name=>'DATA_PUMP_DIR',
6 file_name=>'export01.dmp');
7 END;
8 /

PL/SQL procedure successfully completed.

SQL>

４．ターゲット側データベースの設定

ターゲット側データベースに接続してCredentialを作成

DBMS_CLOUD.CREATE_CREDENTIALプロシージャを使用して、オブジェクト・ストアの資格証明をターゲット側データベースに格納します。

-bash-4.2$ export ORACLE_HOME='/u01/app/client/oracle19'
-bash-4.2$ export TNS_ADMIN='/u02/deployments/Target/etc/'
-bash-4.2$ 
-bash-4.2$ /u01/app/client/oracle19/bin/sqlplus admin/************@target_high

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 12 04:34:42 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> 
SQL> BEGIN
2 DBMS_CLOUD.CREATE_CREDENTIAL(
3 credential_name => 'LOAD_DATA',
4 username => 'oracleidentitycloudservice/t-kozakai@sts-inc.co.jp',
5 password => '*******************));'
6 );
7 END;
8 /


PL/SQL procedure successfully completed.

SQL>

ターゲット側ggadminユーザのロック解除

ターゲット側データベースのOracle GoldenGate 管理者スキーマであるggadminユーザのロックを解除します。

SQL> alter user ggadmin identified by ************ account unlock;

User altered.

SQL>

ソース側ExportデータをData Pump Import でターゲット側にインポート

先程オブジェクト・ストレージにアップロードしたソース側Exportデータを、ターゲット側データベースにData Pump Importでインポートします。

-bash-4.2$ /u01/app/client/oracle19/bin/impdp admin/************@target_high \
> credential=LOAD_DATA \
> schemas=testuser \
> directory=DATA_PUMP_DIR \
> dumpfile=https://objectstorage.ap-tokyo-1.oraclecloud.com/ n/nrqmmb9ftjj0/b/bucket-20210412-1120/o/export01.dmp \ \
> logfile=import01.log

Import: Release 19.0.0.0.0 - Production on Mon Apr 12 04:40:52 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "ADMIN"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "ADMIN"."SYS_IMPORT_SCHEMA_01": admin/********@target_high credential=LOAD_DATA schemas=testuser directory=DATA_PUMP_DIR dumpfile=https://objectstorage.ap-tokyo-1.oraclecloud.com/p/s-dd3XsF2TUK7keQogm5EaEKjBx60HK9uV6Asi7APbwx8gfiL5POtVBKn30XiAD0/n/nrqmmb9ftjj0/b/bucket-20210412-1120/o/export01.dmp logfile=import01.log 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER"."TAB1" 5.484 KB 1 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "ADMIN"."SYS_IMPORT_SCHEMA_01" successfully completed at Mon Apr 12 04:41:07 2021 elapsed 0 00:00:10

-bash-4.2$ 
bash-4.2$ export ORACLE_HOME='/u01/app/client/oracle19'
bash-4.2$ export TNS_ADMIN='/u02/deployments/Target/etc'
bash-4.2$ 
bash-4.2$ /u01/app/client/oracle19/bin/sqlplus admin/************@target_high

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 13 11:38:39 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.


Last Successful login time: Tue Apr 13 2021 11:37:05 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> SQL> 
SQL> desc testuser.tab1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(10)

SQL> select * from testuser.tab1 order by id;

ID NAME
---------- ----------
1 aaa

SQL>

Replicatパラメータ・ファイルの作成

ターゲット側データベースへの適用に必要となるReplicatプロセスのパラメータ・ファイルを作成します。

-bash-4.2$ vi /u02/deployments/Target/etc/conf/ogg/rep1.prm
-bash-4.2$ 
-bash-4.2$ cat /u02/deployments/Target/etc/conf/ogg/rep1.prm
replicat rep1
useridalias target_high
map testuser.*, target testuser.*;
-bash-4.2$

Replicatプロセスの作成

Replicatプロセスを作成し、ターゲット側データベースに登録します。

-bash-4.2$ /u01/app/ogg/oracle19/bin/adminclient
Oracle GoldenGate Administration Client for Oracle
Version 19.1.0.0.201013 32153823

Copyright (C) 1995, 2020, Oracle and/or its affiliates. All rights reserved.

Linux, x64, 64bit (optimized) on Nov 16 2020 20:36:08
Operating system character set identified as UTF-8.

OGG (not connected) 1> CONNECT https://localhost deployment Target as oggadmin password **************** !

OGG (https://localhost Target) 2> alter credentialstore add user ggadmin@target_high password ************ alias target_high
2021-04-12T04:48:49Z INFO OGG-15102 Credential store created.
2021-04-12T04:48:49Z INFO OGG-15116 No information found in credential store.
2021-04-12T04:48:49Z INFO OGG-15114 Credential store altered.

OGG (https://localhost Target) 3> info credentialstore

Default domain: OracleGoldenGate

Alias: target_high
Userid: ggadmin@target_high

OGG (https://localhost Target) 4> dblogin useridalias target_high
Successfully logged into database.

OGG (https://localhost Target as target_high@EV11POD) 5> info all
Program Status Group Type Lag at Chkpt Time Since Chkpt

ADMINSRVR RUNNING 
DISTSRVR RUNNING 
PMSRVR RUNNING 
RECVSRVR RUNNING

OGG (https://localhost Target as target_high@EV11POD) 6> add checkpointtable ggadmin.chkpt

OGG (https://localhost Target as target_high@EV11POD) 7> info checkpointtable ggadmin.chkpt
2021-04-12T04:52:29Z INFO OGG-08100 Checkpoint table GGADMIN.CHKPT created 2021-04-12 04:52:08.

OGG (https://localhost Target as target_high@EV11POD) 8> add replicat rep1 exttrail /u02/trails/dirdat/lt checkpointtable ggadmin.chkpt
2021-04-12T04:53:07Z INFO OGG-08100 REPLICAT added.

OGG (https://localhost Target as target_high@EV11POD) 9> info all
Program Status Group Type Lag at Chkpt Time Since Chkpt

ADMINSRVR RUNNING 
DISTSRVR RUNNING 
PMSRVR RUNNING 
RECVSRVR RUNNING 
REPLICAT STOPPED REP1 NONINTEGRATED 00:00:00 00:00:07

OGG (https://localhost Target as target_high@EV11POD) 10> start replicat rep1
2021-04-12T04:53:22Z INFO OGG-00975 REPLICAT REP1 starting
2021-04-12T04:53:22Z INFO OGG-15426 REPLICAT REP1 started

OGG (https://localhost Target as target_high@EV11POD) 11> info all
Program Status Group Type Lag at Chkpt Time Since Chkpt

ADMINSRVR RUNNING 
DISTSRVR RUNNING 
PMSRVR RUNNING 
RECVSRVR RUNNING 
REPLICAT RUNNING REP1 NONINTEGRATED 00:00:00 00:00:01

OGG (https://localhost Target as target_high@EV11POD) 12> info replicat rep1, detail

REPLICAT REP1 Last Started 2021-04-12 04:53 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:06 ago)
Process ID 9868
Log Read Checkpoint File /u02/trails/dirdat/lt000000000
First Record RBA 0
Encryption Profile LocalWallet

Current Log BSN value: (no data)

Last Committed Transaction CSN value: (no data)

Extract Source Begin End

/u02/trails/dirdat/lt000000000 * Initialized * First Record


Current directory /

Report file /u02/deployments/Target/var/lib/report/REP1.rpt
Parameter file /u02/deployments/Target/etc/conf/ogg/rep1.prm
Checkpoint file /u02/deployments/Target/var/lib/checkpt/REP1.cpr
Checkpoint table ggadmin.chkpt
Process file /u02/deployments/Target/var/run/REP1.pcr
Error log /u02/deployments/Target/var/log/ggserr.log

OGG (https://localhost Target as target_high@EV11POD) 13>

今回はここまで。
同期確認は次回！