![](https://xn--w8j8bac3czf5bl7e.com/wp-content/uploads/blog/5f19f8038b959234c30f03075d8068f4-750x301.png)
前回のあらすじ
こんにちは。プラチナホルダーの小酒井です。
前回は Autonomous Database と Goldengate のプロビジョニングを行いました。
今回はソース側、ターゲット側それぞれのデータベースの設定と同期設定を行います。
- Autonomous Databaseのプロビジョニング(前回)
- Goldengate 19c Marketplace Microservices Editionのプロビジョニング(前回)
- ソース側データベースの設定
- ターゲット側データベースの設定
- 同期確認(次回)
- DDL文の同期確認(次回)
早速ですが、宜しくお願いします!
今回はプロンプト上での作業になります。
宜しくお願いします!
3. ソース側データベースの設定
同期対象のユーザとテーブルを作成
同期対象として、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>
4.ターゲット側データベースの設定
ターゲット側データベースに接続して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>
今回はここまで。
同期確認は次回!
次回へ続く
次回はいよいよ同期確認を行います。
是非次回もご覧ください!
当社はOracle Cloudサービスを展開しておりますので、Oracle Cloud について何かお困りのことがありましたら、お気軽にお問い合せいただけますと幸いです。
Oracle Cloud、Oracle Databaseに関する各種サービスページは下記よりご覧いただけます。
投稿者プロフィール
![技術チーム](https://xn--w8j8bac3czf5bl7e.com/wp-content/uploads/blog/41007909421_6ae3fe3ef0_z-150x150.jpg)
Oracle Cloud2024年9月3日OCIとADアカウント連携してみました
インフラ2024年6月21日Zabbixエージェントをインストール・対象サーバを監視してみました!
Infrastructure(IaaS)2024年6月4日OCI ブート・ボリュームのバックアップ・リストア
Oracle Cloud2024年5月31日OCIのMySQLでリードレプリカを構成してみました!