一、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 empwhere 1=0; //原source库建立表但不插入数据

Table created.

单项复制时Source端是EINI_1进程，Target端是RINI_1进程,双向复制时反之，以便完成两表的初始化同步，配置如下：

原Source

GGSCI (gc2) 36> ADD REPLICAT RINI_1, SPECIALRUN

REPLICAT added.

GGSCI (gc2) 43> EDIT PARAMS RINI_1

-- GoldenGate Initial Load Delivery

--

REPLICAT RINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

ASSUMETARGETDEFS

USERID ogg, PASSWORD ogg

DISCARDFILE ./dirrpt/RINIaa.dsc, PURGE

MAP scott.*, TARGET scott.*;

原Target

GGSCI(oraclelinux54.cuug.net) 10> ADD EXTRACTEINI_1, SOURCEISTABLE

EXTRACT added.

GGSCI(oraclelinux54.cuug.net) 11> INFO EXTRACT *, TASKS

EXTRACT EINI_1Initialized 2014-08-1223:05 Status STOPPED

Checkpoint Lag Not Available

Log Read Checkpoint Not Available

First Record Record 0

Task SOURCEISTABLE

GGSCI(oraclelinux54.cuug.net) 12> EDIT PARAMS EINI_1

-- GoldenGate Initial Data Capture

-- for EMP_OGG and DEPT_OGG

--

EXTRACT EINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

RMTHOST gc2, MGRPORT 7809

RMTTASK REPLICAT, GROUP RINI_1

TABLE scott.EMP_OGG; //这里我们只为初始化scott用户下的emp_ogg表

GGSCI(oraclelinux54.cuug.net) 19> START EXTRACTEINI_1

Sending START request toMANAGER ...

EXTRACT EINI_1 starting

GGSCI(oraclelinux54.cuug.net) 20> VIEW REPORTEINI_1

……...

***********************************************************************

* ** Run Time Statistics** *

***********************************************************************

Report at 2014-08-1223: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: 0

1.3.在ogg环境下，添加日志跟踪：

GGSCI(oraclelinux54.cuug.net) DBLOGIN USERID ogg, PASSWORD ogg

GGSCI(oraclelinux54.cuug.net) ADD TRANDATA scott.*//这里为了试验方便，我们设置为scott下全部表均设为可同步状态

2013-08-1303:21:18 GGS WARNING109 No unique key is defined for table EMP_OGG. Allviable columns will be used to represent the key, but may not guaranteeuniqueness. KEYCOLS may be used todefine the key.

2013-08-1303:21:18 GGS WARNING 301Failed to add supplemental log group on table SCOTT.EMP_OGG due toORA-01031: insufficient privileges,SQL ALTER TABLE "SCOTT"."EMP_OGG" ADD SUPPLEMENTAL LOGGROUP "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 primarykey(empno);

SQL> grant alter any table to ogg;

Grantsucceeded.

1.4.配置extract

GGSCI(gc2) 71> EDIT PARAMSEORA_1 //编辑前先停止进程

EXTRACT EORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

EXTTRAIL ./dirdat/aa

TABLE scott.*;

DDL INCLUDE OBJNAME "scott.*"

TRANLOGOPTIONS EXCLUDEUSER ogg //双向复制关键

GGSCI(oraclelinux54.cuug.net) 74> EDIT PARAMS EORA_1 //编辑前先停止进程

EXTRACT EORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

TRANLOGOPTIONS EXCLUDEUSER ogg

EXTTRAIL ./dirdat/aa

TABLE scott.*;

DDL INCLUDE OBJNAME "scott.*"

GGSCI (双节点执行)> ADD EXTRACTEORA_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用，大小为5M

EXTTRAIL added.

GGSCI (双节点执行)> START EXTRACT EORA_1

1.5.配置pump进程

GGSCI(gc2) 72> EDIT PARAMSPORA_1 //编辑前先停止进程

EXTRACT PORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

PASSTHRU

RMTHOST oraclelinux54.cuug.net, MGRPORT 7809

RMTTRAIL /u01/app/ogg/dirdat/pa

TABLE scott.*;

GGSCI(oraclelinux54.cuug.net) 75> EDIT PARAMS PORA_1//编辑前先停止进程

EXTRACT PORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

PASSTHRU

RMTHOST gc2, MGRPORT 7809

RMTTRAIL ./dirdat/pa

TABLE scott.*;

GGSCI (双节点执行)> ADD EXTRACT PORA_1, EXTTRAILSOURCE ./dirdat/aa // 告诉PORA_1要传送哪个路径下的跟踪信息

EXTRACTadded.

GGSCI (双节点执行)> ADD RMTTRAIL ./dirdat/pa, EXTRACTPORA_1, MEGABYTES 5 // 表示把捕获到的信息传送到远程的哪个目录的文件中

RMTTRAILadded.

GGSCI (双节点执行)> START EXTRACT PORA_1

配置replicate

GGSCI (gc2) 74> EDIT PARAM RORA_1

REPLICAT RORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE ./dirrpt/RORA_aa.DSC, PURGE

DDL INCLUDE ALL

DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3RETRYDELAY 5

DDLERROR DEFAULT DISCARD

DDLERROR DEFAULT IGNORE RETRYOP

MAP scott.*, TARGET scott.*;

GGSCI(oraclelinux54.cuug.net) 87> EDIT PARAMRORA_1

REPLICAT RORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE ./dirrpt/RORA_aa.DSC, PURGE

DDL INCLUDE ALL

DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5

DDLERROR DEFAULT DISCARD

DDLERROR DEFAULT IGNORE RETRYOP

MAP scott.*, TARGET scott.*;

GGSCI(双节点执行) 87> ADD REPLICAT RORA_1, EXTTRAIL ./dirdat/pa //表示从哪里提取传送过来的信息

GGSCI (双节点执行)87> START REPLICAT RORA_1

SQL> conn / as sysdba

Connected.

SQL> grant insert on scott.emp_ogg to ogg;

Grant succeeded.

SQL> conn / as sysdba

Connected.

SQL> grant delete on scott.emp_ogg to ogg;

Grant succeeded.

SQL> grant update on scott.emp_ogg to ogg;

Grant succeeded.

二、配置checkpoint

GGSCI(oraclelinux54.cuug.net) EDIT PARAMS ./GLOBALS

CHECKPOINTTABLE ogg.ggschkpt

GGSCI(oraclelinux54.cuug.net) exit

GGSCI(oraclelinux54.cuug.net) DBLOGIN USERID ogg, PASSWORD ogg

Successfullylogged into database.

GGSCI(oraclelinux54.cuug.net) ADD CHECKPOINTTABLE //如果单项复制已经做了，这里不用再添加

应该看到，两节点的进程都为Running状态

GGSCI (gc2) 73> info all

Program StatusGroup Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EORA_100:00:00 00:00:02

EXTRACT RUNNING PORA_100:00:00 00:00:04

REPLICAT RUNNING RORA_100:00:00 00:00:01

GGSCI(oraclelinux54.cuug.net) 88> info all

Program StatusGroup Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EORA_100:00:00 00:00:09

EXTRACT RUNNING PORA_100:00:00 00:00:02

REPLICAT RUNNING RORA_100:00:00 00:00:05

三、测试双向传送结果

3.1.source 到target

Source system

SQL> INSERT INTO emp_oggVALUES(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 COMMDEPTNO

---------- ------------------- ---------- --------- ---------- ---------- ----------

8000 HL CLERK 7902 12-DEC-80 800 10020

Target system

SQL> select * from emp_ogg;

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO

---------- -------------------- ---------- --------------------- ---------- ---------- ----------

8000 HL CLERK 7902 1980-12-12 : 00:00:00 800 100 20

3.2target 到 source

Target system

SQL> select * from tcustmer;

CUST NAME CITY ST

---------------------------------- -------------------- --

WILL BGSOFTWARE CO. SEATTLE WA

JANE ROCKYFLYER INC. DENVER CO

SQL> insert into tcustmer values ('HL','zai','cuug','en');

1 row created.

SQL> commit;

Commitcomplete.

Source system

SQL> select * from tcustmer;

CUST NAME CITY ST

---------------------------------- -------------------- --

WILL BGSOFTWARE CO. SEATTLE WA

JANE ROCKYFLYER INC. DENVER CO

helei zai cuug en

四、支持DDL复制配置

4.1在两个节点执行执行DDL同步脚本命令：

先进入goldengate软件安装目录，以SYSDBA身份登录oracle执行以下脚本，执行脚本过程中，需要输入的用户全部是ogg，安装模式为INITIALSETUP，如果数据字典或者某些内部的包有错误，则需要运行catalog.sql和catproc.sql脚本。

SQL>show parameter recyclebin;

NAME TYPE VALUE

----------------------------------------------- ------------------------------

recyclebinstring off必须是off

这里执行第二个脚本@ddl_setup时会报一个recyclebin的错误，而且只能在Pfile中修改recyclebin=off后用pfile起库，执行两个清除脚本，再重新运行一下脚本

SQL>@marker_setup

SQL>@ddl_setup

SQL>@role_setup

SQL>grant GGS_GGSUSER_ROLE to ogg;

SQL>@ddl_enable

如果某项脚本执行错误，需要重新执行时，先要执行清除的脚本：ddl_remove.sql和marker_remove.sql