摘自：https://blog.csdn.net/lihuarongaini/article/details/46500233
一、在完成ogg的单向复制配置后，自然会想着向前推进一层，实现双向复制；在实际应用中，双向复制面临着许多问题，主要有如下几点：
1. 如果两个库同时更新同一条记录 如何处理？
2. 如果网络出现失败如何处理？
3. 如果数据不同步后如何修复？
二、准备（本章将ogg_src,ogg_trg完全作为OGG对像用户使用，而不做为业务用户，因此配置文件中映射、map全部注释掉）
1.配置文件中映射、map全部注释掉
（1）OGG1中的extract参数中注释掉：--table ogg_src.*;
（2）OGG1中的pump参数中注释掉：--table ogg_src.*;
（3）OGG2中的replicate参数中注释掉：--MAP OGG_SRC.*,target OGG_TRG.*;
2. OGG1 创建新业务用户（非OGG用户即OGG_SRC,OGG_TRG），使两端一致，便于后面测试；--在OGG1中建立，用DDL同步过去
create tablespace tbs_test1 datafile '/data/u01/app/oracle/oradata/tbs_test.dbf' size 20M; create user ogg_test identified by ogg_test default tablespace tbs_test1 temporary tablespace TEMP profile DEFAULT; grant connect,resource to ogg_test; --在用户ogg_test下创建表：t1,t2,t3 create table ogg_test.t1(ID int primary key,NAME varchar(200));---带主键 create table ogg_test.t2(ID int,NAME varchar(200));--不带主键
三、配置：
1.数据库级别设置，如果需要作为源端，需要开启achivelog日志，方便后期注册等
SQL> shutdown immediate; SQL> startup mount; SQL> alter database archivelog; SQL> alter database open; SQL> archive log list;
注：当前源端为OGG2,目标端为OGG1,
2.源端——配置OGG2,开启附加日志
（1）增加全局变量
GGSCI (ORACLEOGG2) 5> view params ./GLOBALS GGSCHEMA OGG_TRG CHECKPOINTTABLE OGG_TRG.OGG_CHK
（2）开启数据库级别的附加日志（单向配置时，只是OGG1开启，当前源端是OGG2也需要开启）
[oracle@ORACLEOGG2 ogg_trg]$ sqlplus / as sysdba SQL> select name,SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING from v$database; NAME SUPPLEME FORCE_LOGGING --------- -------- --------------------------------------- ORCLOGG2 NO NO
SQL> alter database add supplemental log data; SQL> ALTER DATABASE FORCE LOGGING;SQL> alter system set enable_goldengate_replication=true scope=both; --（源端和目标端都要开启）SQL> alter system switch logfile;SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;SUPPLEME FOR-------- ---YES YES（3）用户级别的附加日志：（未开启,开启见https://www.topunix.com/post-335.html，2.2）
（4）表级别的附加日志（如果不做双向复制，OGG1开启即可；双向复制，OGG2也需要开启）
GGSCI (ORACLEOGG2) 1> dblogin userid ogg_trg,password ogg_trg Successfully logged into database. GGSCI (ORACLEOGG2 as ogg_trg@orclogg2) 2> add trandata ogg_test.* GGSCI (ORACLEOGG2 as ogg_trg@orclogg2) 7> add trandata ogg_test.* 2019-07-27 16:06:28 INFO OGG-15132 Logging of supplemental redo data enabled for table OGG_TEST.T1. 2019-07-27 16:06:28 INFO OGG-15133 TRANDATA for scheduling columns has been added on table OGG_TEST.T1. 2019-07-27 16:06:28 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table OGG_TEST.T1. 2019-07-27 16:06:30 WARNING OGG-06439 No unique key is defined for table T2. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key. 2019-07-27 16:06:30 INFO OGG-15130 No key found for table OGG_TEST.T2. All viable columns will be logged. 2019-07-27 16:06:30 INFO OGG-15132 Logging of supplemental redo data enabled for table OGG_TEST.T2. 2019-07-27 16:06:30 INFO OGG-15133 TRANDATA for scheduling columns has been added on table OGG_TEST.T2. 2019-07-27 16:06:30 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table OGG_TEST.T2. 2019-07-27 16:06:31 INFO OGG-10471 ***** Oracle Goldengate support information on table OGG_TEST.T1 ***** Oracle Goldengate support native capture on table OGG_TEST.T1. Oracle Goldengate marked following column as key columns on table OGG_TEST.T1: ID. 2019-07-27 16:06:31 INFO OGG-10471 ***** Oracle Goldengate support information on table OGG_TEST.T2 ***** Oracle Goldengate support native capture on table OGG_TEST.T2. Oracle Goldengate marked following column as key columns on table OGG_TEST.T2: ID, NAME No unique key is defined for table OGG_TEST.T2.
3.源端——OGG2上配置extract和pump进程
（1）MGR——编辑OGG2（源端）mgr
GGSCI (ORACLEOGG2 as ogg_trg@orclogg2) 103> edit params mgr PORT 7909 DYNAMICPORTLIST 7910-7920 AUTOSTART EXTRACT * AUTOSTART REPLICAT * AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 2 AUTORESTART REPLICAT *, RETRIES 5, WAITMINUTES 2 PURGEOLDEXTRACTS /data/u01/app/oracle/product/ogg_trg/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 10 ACCESSRULE, PROG REPLICAT, IPADDR 172.18.1.245, ALLOW
（2）Primary Extract——创建主抽取进程(Primary Extract)，并配置trail文件目录
GGSCI (ORACLEOGG2 as ogg_trg@orclogg2) 7> add extract elldb001,tranlog,begin now EXTRACT added. GGSCI (ORACLEOGG2 as ogg_trg@orclogg2) 8> ADD EXTTRAIL /data/u01/app/oracle/product/ogg_trg/dirdat/el, EXTRACT elldb001 EXTTRAIL added.
（3）Primary Extract——主进程需要注册：
GGSCI (ORACLEOGG2 as ogg_trg@orclogg2) 2> register extract elldb001 database 2019-07-06 09:01:41 INFO OGG-02003 Extract ELLDB001 successfully registered with database at SCN 3122788.
（4）Primary Extract——配置主抽取进程(Primary Extract)参数
（5）Data Pump——创建和配置Data Pump进程(Secondly Extract)，并进行编辑，
（6）Data Pump——配置trail文件目录，将OGG1（目标端）的trail文件添加到队列中
源端可以配置多个主抽取进程，也可以配置多个Data Pump进程，但必须为每个要同步的目标端配置一个Data Pump进程；
4.目标端——OGG1端设置检查点、增加全局变量、配置Extract、Replicat,
（1）创建检查点
GGSCI (ORACLEOGG1) 10> dblogin userid ogg_src,password ogg_src Successfully logged into database. GGSCI (ORACLEOGG1 as ogg_src@orclogg1) 11> add checkpointtable ogg_src.ogg_chk Successfully created checkpoint table ogg_src.ogg_chk
（2）增加全局变量
GGSCI (ORACLEOGG1) 9> edit params ./GLOBALS GGSCHEMA ogg_src CHECKPOINTTABLE ogg_src.ogg_chk
（3）创建目录创建Discard file目录
（4）创建和配置Replicat进程
GGSCI (ORACLEOGG1 as ogg_src@orclogg1) 2> add replicat rlldb001,exttrail /data/u01/app/oracle/product/ogg_src/dirdat/pl,checkpointtable OGG_SRC.OGG_CHK REPLICAT added. GGSCI (ORACLEOGG1 as ogg_src@orclogg1) 3> edit param rlldb001 REPLICAT rlldb001 SETENV(ORACLE_SID="orclogg1") SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) USERID ogg_src,password ogg_src DISCARDFILE /data/u01/app/oracle/product/ogg_src/discrd/reptr.desc,append,megabytes 512 ALLOWNOOPUPDATES ASSUMETARGETDEFS MAP OGG_test.*,target OGG_test.*; --DDLOPTIONS MAPSESSIONSCHEMA ogg_src target ogg_trg --用于不同用户结构
（5）配置mgr，增加自动启动replicat进程
PORT 7809 DYNAMICPORTLIST 7810-7820 AUTOSTART EXTRACT * AUTORESTART EXTRACT *,RETRIES 4, WAITMINUTES 4 AUTOSTART REPLICAT * AUTORESTART REPLICAT *,RETRIES 4, WAITMINUTES 4 PURGEOLDEXTRACTS /data/u01/app/oracle/product/ogg_src/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 6 STARTUPVALIDATIONDELAY 5
（6）配置extract
extract esydb001 SETENV(ORACLE_SID="orclogg1") SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8) TRANLOGOPTIONS EXCLUDEUSER ogg_src DDL include all userid ogg_src, password ogg_src EXTTRAIL /data/u01/app/oracle/product/ogg_src/dirdat/es --table ogg_src.*; table ogg_test.*;
5.测试
-
OGG1创建表t1，通过OGG1的DDL同步到OGG2(现在做为源端）
-
检测OGG2,t1已经存在，同步成功；
-
在OGG2写入数据到表t1中，OGG1也存在数据；
-
但OGG1再写入数据t1,导致OGG2的replicate进程报错，显示：
-
2019-07-29T16:45:27.870+0800 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rsydb001.prm: Aborted grouped transaction on OGG_TEST.T1, Database error 1 (OCI Error ORA-00001: unique constraint (OGG_TEST.SYS_C007508) violated (status = 1), SQL <INSERT INTO "OGG_TEST"."T1" ("ID","NAME") VALUES (:a0,:a1)>). 2019-07-29T16:45:27.870+0800 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rsydb001.prm: Repositioning to rba 3544 in seqno 7. 2019-07-29T16:45:27.871+0800 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rsydb001.prm: SQL error 1 mapping OGG_TEST.T1 to OGG_TEST.T1 OCI Error ORA-00001: unique constraint (OGG_TEST.SYS_C007508) violated (status = 1), SQL <INSERT INTO "OGG_TEST"."T1" ("ID","NAME") VALUES (:a0,:a1)>. 2019-07-29T16:45:27.874+0800 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rsydb001.prm: Error mapping from OGG_TEST.T1 to OGG_TEST.T1. 2019-07-29T16:45:27.874+0800 INFO OGG-02333 Oracle GoldenGate Delivery for Oracle, rsydb001.prm: Reading /data/u01/app/oracle/product/ogg_trg/dirdat/ps000000007, current RBA 3,544, 0 records, m_file_seqno = 7, m_file_rba = 3,676. -
删除OGG2中的t1,重新通过OGG1同步，所有进程都running
-
在OGG1中插入新数据，数据在OGG2中正常写入。
-
在OGG2中插入新数据，同步至OGG1时，出现4中的错误
-
尝试删除OGG
DML双向测试问题：
（DML的双向测试问题较多，
（1）双向写数据，如果表设置了主键，复制时总是出现违反唯一约束，A同步到B，A是业务用户执行，B是ogg用户执行复制，考虑还是OGG用户问题，需要进一步排查
（2）双向写数据，如果表没有设置了主键，复制时会出现无限循环写数据问题。
因DDL后，DML的table ogg_src.* 不能正常被检测到，先不使用DLL的双向复制
6、、源端——配置OGG2，运行相关的脚本，支持DDL的复制
如果想使用DDL功能，需要在之前运行支持DDL的相关脚本。
0.使用ogg_trg登录sqlplus
1.@marker_setup.sql
2.@ddl_setup.sql
3.@role_setup.sql
4.GRANT GGS_GGSUSER_ROLE TO ogg_src
5.@ddl_enable.sql
6.@?/rdbms/admin/dbmspool.sql
7.@ddl_pin.sql ogg_src
（0）使用ogg_trg登录sqlplus
[oracle@ORACLEOGG2 ogg_trg]$ sqlplus ogg_trg/ogg_trg as sysdba
（1）执行@marker_setup.sql
SQL> @marker_setup.sql Marker setup script You will be prompted for the name of a schema for the Oracle GoldenGate database objects. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter Oracle GoldenGate schema name:ogg_trg Marker setup table script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to OGG_TRG MARKER TABLE ------------------------------- OK MARKER SEQUENCE ------------------------------- OK Script complete.
（2）执行@ddl_setup.sql
SQL> @ddl_setup.sql Oracle GoldenGate DDL Replication setup script Verifying that current user has privileges to install DDL Replication... You will be prompted for the name of a schema for the Oracle GoldenGate database objects. NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:ogg_trg
Working, please wait ... Spooling to file ddl_setup_spool.txt Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ... Check complete. Using OGG_TRG as a Oracle GoldenGate schema name. Working, please wait ... DDL replication setup script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to OGG_TRG CLEAR_TRACE STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors CREATE_TRACE STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors TRACE_PUT_LINE STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors INITIAL_SETUP STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors DDLVERSIONSPECIFIC PACKAGE STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors DDLREPLICATION PACKAGE STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors DDLREPLICATION PACKAGE BODY STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors DDL IGNORE TABLE ----------------------------------- OK DDL IGNORE LOG TABLE ----------------------------------- OK DDLAUX PACKAGE STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors DDLAUX PACKAGE BODY STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors SYS.DDLCTXINFO PACKAGE STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors SYS.DDLCTXINFO PACKAGE BODY STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors DDL HISTORY TABLE ----------------------------------- OK DDL HISTORY TABLE(1) ----------------------------------- OK DDL DUMP TABLES ----------------------------------- OK DDL DUMP COLUMNS ----------------------------------- OK DDL DUMP LOG GROUPS ----------------------------------- OK DDL DUMP PARTITIONS ----------------------------------- OK DDL DUMP PRIMARY KEYS ----------------------------------- OK DDL SEQUENCE ----------------------------------- OK GGS_TEMP_COLS ----------------------------------- OK GGS_TEMP_UK ----------------------------------- OK DDL TRIGGER CODE STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors DDL TRIGGER INSTALL STATUS ----------------------------------- OK DDL TRIGGER RUNNING STATUS ------------------------------------------------------------------------------------------------------------------------ ENABLED STAYMETADATA IN TRIGGER ------------------------------------------------------------------------------------------------------------------------ OFF DDL TRIGGER SQL TRACING ------------------------------------------------------------------------------------------------------------------------ 0 DDL TRIGGER TRACE LEVEL ------------------------------------------------------------------------------------------------------------------------ NONE LOCATION OF DDL TRACE FILE ------------------------------------------------------------------------------------------------------------------------ /data/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/log/ggs_ddl_trace.log Analyzing installation status... VERSION OF DDL REPLICATION ------------------------------------------------------------------------------------------------------------------------ OGGCORE_19.1.0.0.0_PLATFORMS_190524.2201 STATUS OF DDL REPLICATION ------------------------------------------------------------------------------------------------------------------------ SUCCESSFUL installation of DDL Replication software components Script complete.
（3）执行role_setup.sql脚本
SQL> @role_setup.sql GGS Role setup script This script will drop and recreate the role GGS_GGSUSER_ROLE To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.) You will be prompted for the name of a schema for the GoldenGate database objects. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter GoldenGate schema name:ogg_trg Wrote file role_setup_set.txt PL/SQL procedure successfully completed. Role setup script complete Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command: GRANT GGS_GGSUSER_ROLE TO <loggedUser> where <loggedUser> is the user assigned to the GoldenGate processes.
（4）执行GRANT GGS_GGSUSER_ROLE TO ogg_trg授权
SQL> GRANT GGS_GGSUSER_ROLE TO ogg_trg ; Grant succeeded.
（5）执行ddl_enable.sql脚本
SQL> @ddl_enable.sql Trigger altered.
（6）执行@?/rdbms/admin/dbmspool.sql 脚本
SQL> @?/rdbms/admin/dbmspool.sql Session altered. Package created. Grant succeeded. Session altered.
（7）执行ddl_pin.sql ogg_trg脚本
SQL> @ddl_pin.sql ogg_trg PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
6.测试数据--ddl操作可以，dml操作没有数据
文章评论