Goldengate双向复制配置

一、Goldengate双向复制配置1.1.在进行如下配置之前，先在源数据库source system（原来的目标数据库）端添加辅助的redolog配置：SQL>alter database add supplemental log data;SQL>alter system switch logfile;SQL>alter database force logging;1.2.在原Source和原Target分别用scott用户创建一张emp_ogg表SQL> create table emp_ogg as select * from emp
where 1=0; //原source库建立表但不插入数据Table created.单项复制时Source端是EINI_1进程，Target端是RINI_1进程,双向复制时反之，以便完成两表的初始化同步，配置如下：原SourceGGSCI (gc2) 36> ADD REPLICAT RINI_1, SPECIALRUNREPLICAT added.GGSCI (gc2) 43> EDIT PARAMS RINI_1— GoldenGate Initial Load Delivery—REPLICAT RINI_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)ASSUMETARGETDEFSUSERID ogg, PASSWORD oggDISCARDFILE ./dirrpt/RINIaa.dsc, PURGEMAP scott.*, TARGET scott.*;原TargetGGSCI
(oraclelinux54.cuug.net) 10> ADD EXTRACT
EINI_1, SOURCEISTABLEEXTRACT added.GGSCI
(oraclelinux54.cuug.net) 11> INFO EXTRACT *, TASKSEXTRACT EINI_1
Initialized 2014-08-12
23:05 Status STOPPEDCheckpoint Lag Not AvailableLog Read Checkpoint Not Available First Record Record 0Task SOURCEISTABLEGGSCI
(oraclelinux54.cuug.net) 12> EDIT PARAMS EINI_1— GoldenGate Initial Data Capture— for EMP_OGG and DEPT_OGG—EXTRACT EINI_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)USERID ogg, PASSWORD oggRMTHOST gc2, MGRPORT 7809RMTTASK REPLICAT, GROUP RINI_1TABLE scott.EMP_OGG; //这里我们只为初始化scott用户下的emp_ogg表GGSCI
(oraclelinux54.cuug.net) 19> START EXTRACT
EINI_1Sending START request to
MANAGER …EXTRACT EINI_1 startingGGSCI
(oraclelinux54.cuug.net) 20> VIEW REPORT
EINI_1………************************************************************************ ** Run Time Statistics
** ************************************************************************Report at 2014-08-12
23:11:04 (activity since 2014-08-12 23:10:59)Output to RINI_1:From Table SCOTT.EMP_OGG: #inserts: 14 //可以看出两边已经同步成功 # updates: 0 # deletes: 0 # discards: 01.3.在ogg环境下，添加日志跟踪：GGSCI
(oraclelinux54.cuug.net) DBLOGIN USERID ogg, PASSWORD oggGGSCI
(oraclelinux54.cuug.net) ADD TRANDATA scott.*//这里为了试验方便，我们设置为scott下全部表均设为可同步状态2013-08-13
03:21:18 GGS WARNING
109 No unique key is defined for table EMP_OGG. All
viable columns will be used to represent the key, but may not guarantee
uniqueness. KEYCOLS may be used to
define the key.2013-08-13
03:21:18 GGS WARNING 301
Failed to add supplemental log group on table SCOTT.EMP_OGG due to
ORA-01031: insufficient privileges,
SQL ALTER TABLE “SCOTT”.”EMP_OGG” ADD SUPPLEMENTAL LOG
GROUP “GGS_EMP_OGG_74686”
(“EMPNO”,”ENAME”,”JOB”,”MGR”,”HIREDATE”,”SAL”,”COMM”,”DEPTNO”)
ALWAYS /* GOLDENGATE_DDL_REPLICATION */.解决办法：SQL>alter table emp_ogg add constraint emp_ogg_pk primary
key(empno);SQL> grant alter any table to ogg;Grant
succeeded.1.4.配置extractGGSCI
(gc2) 71> EDIT PARAMS
EORA_1 //编辑前先停止进程EXTRACT EORA_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)USERID ogg, PASSWORD oggEXTTRAIL ./dirdat/aaTABLE scott.*;DDL INCLUDE OBJNAME “scott.*”TRANLOGOPTIONS EXCLUDEUSER ogg //双向复制关键GGSCI
(oraclelinux54.cuug.net) 74> EDIT PARAMS EORA_1 //编辑前先停止进程EXTRACT EORA_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)USERID ogg, PASSWORD oggTRANLOGOPTIONS EXCLUDEUSER oggEXTTRAIL ./dirdat/aaTABLE scott.*;DDL INCLUDE OBJNAME “scott.*”GGSCI (双节点执行)> ADD EXTRACT
EORA_1, TRANLOG, BEGIN NOW注：ADD EXTRACT EORA_1:添加EXTRACT进程，这就是一直运行，一段停止redo 里的日志就没人去抓取了TRANLOG, BEGIN NOW：现在开始同步日志，也可以用异步，那就要另外配置EXTRACT added.GGSCI (双节点执行)> ADD EXTTRAIL
./dirdat/aa, EXTRACT EORA_1, MEGABYTES 5 //添加跟踪文件给EORA_1用，大小为5MEXTTRAIL added.GGSCI (双节点执行)> START EXTRACT EORA_11.5.配置pump进程GGSCI
(gc2) 72> EDIT PARAMS
PORA_1 //编辑前先停止进程EXTRACT PORA_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)PASSTHRURMTHOST oraclelinux54.cuug.net, MGRPORT 7809RMTTRAIL /u01/app/ogg/dirdat/paTABLE scott.*;GGSCI
(oraclelinux54.cuug.net) 75> EDIT PARAMS PORA_1//编辑前先停止进程EXTRACT PORA_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)PASSTHRURMTHOST gc2, MGRPORT 7809RMTTRAIL ./dirdat/paTABLE scott.*;GGSCI (双节点执行)> ADD EXTRACT PORA_1, EXTTRAILSOURCE ./dirdat/aa // 告诉PORA_1要传送哪个路径下的跟踪信息EXTRACT
added.GGSCI (双节点执行)> ADD RMTTRAIL ./dirdat/pa, EXTRACT
PORA_1, MEGABYTES 5 // 表示把捕获到的信息传送到远程的哪个目录的文件中RMTTRAIL
added.GGSCI (双节点执行)> START EXTRACT PORA_1配置replicateGGSCI (gc2) 74> EDIT PARAM RORA_1REPLICAT RORA_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)USERID ogg, PASSWORD oggHANDLECOLLISIONSASSUMETARGETDEFSDISCARDFILE ./dirrpt/RORA_aa.DSC, PURGEDDL INCLUDE ALLDDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3
RETRYDELAY 5DDLERROR DEFAULT DISCARDDDLERROR DEFAULT IGNORE RETRYOPMAP scott.*, TARGET scott.*;GGSCI
(oraclelinux54.cuug.net) 87> EDIT PARAM
RORA_1REPLICAT RORA_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)USERID ogg, PASSWORD oggHANDLECOLLISIONSASSUMETARGETDEFSDISCARDFILE ./dirrpt/RORA_aa.DSC, PURGEDDL INCLUDE ALLDDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5DDLERROR DEFAULT DISCARDDDLERROR DEFAULT IGNORE RETRYOPMAP scott.*, TARGET scott.*;GGSCI
(双节点执行) 87> ADD REPLICAT RORA_1, EXTTRAIL ./dirdat/pa //表示从哪里提取传送过来的信息GGSCI (双节点执行)
87> START REPLICAT RORA_1SQL> conn / as sysdbaConnected.SQL> grant insert on scott.emp_ogg to ogg;Grant succeeded.SQL> conn / as sysdbaConnected.SQL> grant delete on scott.emp_ogg to ogg;Grant succeeded.SQL> grant update on scott.emp_ogg to ogg;Grant succeeded.二、配置checkpointGGSCI
(oraclelinux54.cuug.net) EDIT PARAMS ./GLOBALSCHECKPOINTTABLE ogg.ggschkptGGSCI
(oraclelinux54.cuug.net) exitGGSCI
(oraclelinux54.cuug.net) DBLOGIN USERID ogg, PASSWORD oggSuccessfully
logged into database.GGSCI
(oraclelinux54.cuug.net) ADD CHECKPOINTTABLE //如果单项复制已经做了，这里不用再添加应该看到，两节点的进程都为Running状态GGSCI (gc2) 73> info allProgram Status
Group Lag Time Since ChkptMANAGER RUNNINGEXTRACT RUNNING EORA_1
00:00:00 00:00:02EXTRACT RUNNING PORA_1
00:00:00 00:00:04REPLICAT RUNNING RORA_1
00:00:00 00:00:01GGSCI
(oraclelinux54.cuug.net) 88> info allProgram Status
Group Lag Time Since ChkptMANAGER RUNNINGEXTRACT RUNNING EORA_1
00:00:00 00:00:09EXTRACT RUNNING PORA_1
00:00:00 00:00:02REPLICAT RUNNING RORA_1
00:00:00 00:00:05三、测试双向传送结果3.1.source 到targetSource systemSQL> INSERT INTO emp_ogg
VALUES(8000,’HL’,’CLERK’,7902,’12-DEC-80′,800,100,20);1 row created.SQL> commit;Commit complete.SQL> select * from emp_ogg; EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO———- ———-
——— ———- ——— ———- ———- ———- 8000 HL CLERK 7902 12-DEC-80 800 100
20Target systemSQL> select * from emp_ogg; EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO———- ———-
———- ———- ——————— ———- ———- ———- 8000 HL CLERK 7902 1980-12-12 : 00:00:00 800 100 203.2target 到 sourceTarget systemSQL> select * from tcustmer;CUST NAME CITY ST—-
—————————— ——————– –WILL BG
SOFTWARE CO. SEATTLE WAJANE ROCKY
FLYER INC. DENVER COSQL> insert into tcustmer values (‘HL’,’zai’,’cuug’,’en’);1 row created.SQL> commit;Commit
complete.Source systemSQL> select * from tcustmer;CUST NAME CITY ST—-
—————————— ——————– –WILL BG
SOFTWARE CO. SEATTLE WAJANE ROCKY
FLYER INC. DENVER COhelei zai cuug en四、支持DDL复制配置4.1在两个节点执行执行DDL同步脚本命令：先进入goldengate软件安装目录，以SYSDBA身份登录oracle执行以下脚本，执行脚本过程中，需要输入的用户全部是ogg，安装模式为INITIALSETUP，如果数据字典或者某些内部的包有错误，则需要运行c免费云主机域名atalog.sql和catproc.sql脚本。SQL>show parameter recyclebin;NAME TYPE VALUE————————————
———– ——————————recyclebin
string off必须是off这里执行第二个脚本@ddl_setup时会报一个recyclebin的错误，而且只能在Pfile中修改recyclebin=off后用pfile起库，执行两个清除脚本，再重新运行一下脚本SQL>@marker_setupSQL>@ddl_setupSQL>@role_setupSQL>grant GGS_GGSUSER_ROLE to ogg;SQL>@ddl_enable如果某项脚本执行错误，需要重新执行时，先要执行清除的脚本：ddl_remove.sql和marker_remove.sql

相关推荐: SQL Server 2017 AlwaysOn on Linux 配置和维护（12）

移动AG资源手动故障转移AG资源ag_cluster到群集节点nodeName2：删除位置约束先查看新的目标节点上添加的资源的位置约束：例如免费云主机域名：Enabled on: Node1 (score:INFINITY) (role: Master) (i…