本文共 8698 字,大约阅读时间需要 28 分钟。
本次测试同一个cdb的pdborcl下source用户同步到target用户下。
源端使用cdb common user 来访问源端DB可以访问redo log 和所有pdbs 。
To capture from a multitenant database, you must use an integrated Extract that is configured at the root level using a c##
account. To apply data into a multitenant database, a separate Replicat is needed for each PDB, because a Replicat connects at the PDB level and doesn't have access to objects outside of that PDB
[oracle@rac1 legacy]$ cd /ogg/bin
[oracle@rac1 bin]$ ll total 112520 -rwxrwxr-x 1 oracle oinstall 4614896 Oct 17 2019 adminclient -rwxrwxr-x 1 oracle oinstall 12883912 Oct 17 2019 adminsrvr -rwxrwxr-x 1 oracle oinstall 1592032 Oct 17 2019 cachefiledump -rwxrwxr-x 1 oracle oinstall 4098888 Oct 17 2019 checkprm -rwxrwxr-x 1 oracle oinstall 4124000 Oct 17 2019 convchk -rwxrwxr-x 1 oracle oinstall 5332160 Oct 17 2019 convprm -rwxrwxr-x 1 oracle oinstall 5307976 Oct 17 2019 defgen -rwxrwx--- 1 oracle oinstall 11086424 Oct 17 2019 distsrvr -rwxrwxr-x 1 oracle oinstall 4594960 Oct 17 2019 emsclnt -rwxrwx--- 1 oracle oinstall 12361928 Oct 17 2019 extract -rwxrwxr-x 1 oracle oinstall 4627856 Oct 17 2019 ggcmd -rwxrwxr-x 1 oracle oinstall 108824 Oct 17 2019 keygen -rwxrwxr-x 1 oracle oinstall 6035504 Oct 17 2019 logdump -rwxr-x--- 1 oracle oinstall 10352 Mar 21 2019 oggca.sh -rwxrwxr-x 1 oracle oinstall 1641208 Oct 17 2019 oggerr -rwxrwxr-x 1 oracle oinstall 4366 Oct 17 2019 orapki -rwxrwxr-x 1 oracle oinstall 7664936 Oct 17 2019 pmsrvr -rwxrwx--- 1 oracle oinstall 9011128 Oct 17 2019 recvsrvr -rwxrwx--- 1 oracle oinstall 11310280 Oct 17 2019 replicat -rwxrwxr-x 1 oracle oinstall 1637112 Oct 17 2019 retrace -rwxrwxr-x 1 oracle oinstall 7127768 Oct 17 2019 ServiceManager -rwxrwxr-x 1 oracle oinstall 6809 Oct 18 2018 XAGTask [oracle@rac1 bin]$ ./adminclient Oracle GoldenGate Administration Client for Oracle Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
Linux, x64, 64bit (optimized) on Oct 17 2019 14:43:45
Operating system character set identified as UTF-8.OGG (not connected) 1> connect http://192.168.52.150:8000
ERROR: Deployment name must be specified
Available deployments are: - oggma - targetOGG (not connected) 2> connect http://192.168.52.150:8000 Deployment oggma as oggadmin password oggadmin
OGG (http://192.168.52.150:8000 oggma) 3> info all
Program Status Group Type Lag at Chkpt Time Since ChkptADMINSRVR RUNNING
DISTSRVR RUNNING PMSRVR RUNNING RECVSRVR RUNNING EXTRACT RUNNING EXT INTEGRATED 00:00:00 00:00:04 REPLICAT RUNNING REP INTEGRATED 00:00:00 00:00:02OGG (http://192.168.52.150:8000 oggma) 4> view params ext
extract ext useridalias s_ca domain s_domain DDL INCLUDE MAPPED SOURCECATALOG pdborcl exttrail cq sourcecatalog pdborcl TABLE source.*;OGG (http://192.168.52.150:8000 oggma) 5> view params rep
replicat rep USERID ogg@pdborcl, PASSWORD ogg DDL INCLUDE MAPPED SOURCECATALOG pdborcl sourcecatalog pdborcl MAP source.*, TARGET target.*;-- If performing precise instantiation after an Initial Load as of a SCN
-- Run the replicat with a start option to only start apply after the SCN at which the initial load was performed. -- Example Replicat->Start Option->After CSN with SCN value used for the Initial Load. -- See start options for running a replicat for more details.OGG (http://192.168.52.150:8000 oggma) 6> DBLOGIN USERIDALIAS s_ca domain s_domain
Successfully logged into database CDB$ROOT.经测试以上配置支持源端和目标端ddl的复制。
同步测试记录:truncate/insert/delete/update/add col增加字段/ctas等均正常
15:09:14 SQL> conn source/oracle@pdborclConnected.15:09:18 SQL> create table t6 as select * from t5;Table created.15:09:34 SQL> conn target/oracle@pdborclConnected.15:09:39 SQL> select * from t6; ID---------- 1 2 315:09:43 SQL> conn source/oracle@pdborclConnected.15:09:48 SQL> insert into t6 values(4);1 row created.15:09:58 SQL> commit;Commit complete.15:10:24 SQL> ALTER TABLE T6 ADD CONSTRAINT PK_t6 PRIMARY KEY (ID) USING INDEX;Table altered.15:10:35 SQL> insert into t6 values(5);1 row created.15:10:42 SQL> commit;Commit complete.15:10:45 SQL> insert into t6 values(6);1 row created.15:11:00 SQL> commit;Commit complete.15:11:02 SQL> conn target/oracle@pdborclConnected.15:11:24 SQL> select * from t6; ID---------- 1 2 3 4 5 66 rows selected.15:11:26 SQL> 15:11:26 SQL> ALTER TABLE T6 ADD CONSTRAINT PK_t6 PRIMARY KEY (ID) USING INDEX;ALTER TABLE T6 ADD CONSTRAINT PK_t6 PRIMARY KEY (ID) USING INDEX *ERROR at line 1:ORA-02260: table can have only one primary key15:11:33 SQL> ALTER TABLE T5 ADD CONSTRAINT PK_t5 PRIMARY KEY (ID) USING INDEX;ALTER TABLE T5 ADD CONSTRAINT PK_t5 PRIMARY KEY (ID) USING INDEX *ERROR at line 1:ORA-02260: table can have only one primary key15:12:07 SQL> select * from t5; ID---------- 1 2 315:12:23 SQL> conn source/oracle@pdborclConnected.15:12:36 SQL> truncate table t5;Table truncated.15:12:41 SQL> select * from t5; no rows selected15:12:47 SQL> conn target/oracle@pdborclConnected.15:12:54 SQL> select * from t5; no rows selected15:12:56 SQL> 15:12:56 SQL> conn source/oracle@pdborclConnected.15:48:08 SQL> alter table t5 add col1 varchar2(20);Table altered.15:48:40 SQL> select * from t5;no rows selected15:48:54 SQL> conn target/oracle@pdborclConnected.15:49:01 SQL> desc t5; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(38) COL1 VARCHAR2(20)15:49:06 SQL> conn source/oracle@pdborclConnected.15:49:12 SQL> desc t6; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(38)15:49:17 SQL> alter table t6 add col1 varchar2(20); Table altered.15:49:30 SQL> select * from t6; ID COL1---------- -------------------- 1 2 3 4 5 66 rows selected.15:50:02 SQL> update t6 set col1=id where id>4;2 rows updated.15:50:12 SQL> commit;Commit complete.15:50:14 SQL> select * from t6; ID COL1---------- -------------------- 1 2 3 4 5 5 6 66 rows selected.15:50:18 SQL> conn target/oracle@pdborclConnected.15:50:27 SQL> select * from t6; ID COL1---------- -------------------- 1 2 3 4 5 5 6 66 rows selected.15:51:09 SQL> conn source/oracle@pdborclConnected.15:51:32 SQL> create table t7 as select * from t6;Table created.15:51:37 SQL> select * from t7; ID COL1---------- -------------------- 1 2 3 4 5 5 6 66 rows selected.15:51:43 SQL> update t7 set col1=44 where id=4;1 row updated.15:52:03 SQL> commit;Commit complete.15:52:05 SQL> select * from t7; ID COL1---------- -------------------- 1 2 3 4 44 5 5 6 66 rows selected.15:52:22 SQL> conn target/oracle@pdborclConnected.15:52:33 SQL> select * from t7; ID COL1---------- -------------------- 1 2 3 4 44 5 5 6 66 rows selected.15:52:48 SQL> conn source/oracle@pdborclConnected.15:52:55 SQL> delete from t7 where id=2;1 row deleted.15:53:08 SQL> commit;Commit complete.15:53:10 SQL> conn target/oracle@pdborclConnected.15:53:14 SQL> select * from t7; ID COL1---------- -------------------- 1 3 4 44 5 5 6 615:53:16 SQL>
其它参考:
Oracle 19C OGG基础运维-01环境准备 Oracle GoldenGate 12c 配置Oracle Database 12c Multitenant database GoldenGate 12.2抽取Oracle 12c多租户配置过程Oracle GoldenGate 19 Microservices完整高可用安装、配置与测试
Oracle GoldenGate 19 Microservices数据同步实战与故障处理 转载地址:http://ezsof.baihongyu.com/