ogg的安装配置配置双向同步（含DDL）

最新推荐文章于 2021-01-28 09:48:20 发布

weixin_33858336

最新推荐文章于 2021-01-28 09:48:20 发布

阅读量280

点赞数

原文链接：http://www.cnblogs.com/junnor/p/4307228.html

版权

第一部分先配置单向同步（含DDL）

一源端安装GoldenGate

创建用户

创建目录

mkdir -p /opt/ogg

chmod -R 777 /opt/ogg

chown -R oracle:oinstall /opt/ogg

ogg用户

useradd -g oinstall -G dba ggate

解压软件
设置环境变量

配置goldengate用户变量

su – ggate

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1

export ORACLE_SID=prm

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/ggate

export GGATE=/opt/ogg

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export PATH=$PATH:$ORACLE_HOME/bin:$GGATE

安装GoldenGate

图形化安装方法中，在software location部分选择环境变量中GGATE的值，不过要注意给GGATE目录适当的权限；

Database Location部分就是ORACLE_HOME的值。

二目标端安装GoldenGate

创建用户

创建目录

mkdir -p /opt/ogg

chmod -R 755 /opt/ogg

chown -R oracle:oinstall /opt/ogg

ogg用户

useradd -g oinstall -G dba ggate

解压软件
设置环境变量

配置goldengate用户变量

su – ggate

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1

export ORACLE_SID=prm

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/ggate

export GGATE=/opt/ogg

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export PATH=$PATH:$ORACLE_HOME/bin:$GGATE

安装GoldenGate

图形化安装方法中，在software location部分选择环境变量中GGATE的值，不过要注意给GGATE目录适当的权限；

Database Location部分就是ORACLE_HOME的值。

三配置源端数据库

开启归档

开启归档

修改之前创建参数文件做备份

create pfile='/home/oracle/initdemo.ora' from spfile;

alter system set log_archive_dest='/arch/prm' scope=spfile; --修改归档的路径

---alter system set db_recovery_file_dest='' scope=spfile; --关闭闪回

shutdown immediate;

startup mount;

alter database archivelog;

archive log list;

alter database open;

设置数据库force logging

实际生产应用中，最好同时打开ORACLE的强制日志模式，以防止源数据库因直接路径加载忽略redo生成而导致这部分数据无法同步：

SQL> select force_logging from v$database;

SQL> Alter database force logging;

开启最小附加日志（DDL的话好开启数据库级别）

Oracle数据库需要开启归档日志，并开启最小附加日志模式。

SQL> select supplemental_log_data_min from v$database; --查看是否开启了最小附加日志模式

SQL> alter database add supplemental log data; --开启最小附加日志模式

千万不要小看这步日志设置，其实在GoldenGate的配置中，这步是最容易出错的环节。如果开启DDL复制做冗灾备份，最好直接在数据库级别打开补全日志：

SQL> alter database add supplemental log data (primary key,unique,foreign key) columns;

检查一下，全是YES就OK了（整个数据库级别补全）

SQL> select supplemental_log_data_min,

supplemental_log_data_pk,supplemental_log_data_ui from v$database;

源库关闭回收站

SQL> show parameter recyclebin

SQL> alter system set recyclebin=off scope=spfile;

SQL>shutdown immediate;

SQL>startup;

SQL> show parameter recyclebin

UNDO设置

alter system set undo_retention=86400;

创建goldengate用户并且授权

创建表空间、用户、赋予dba权限

create tablespace ogg datafile '/u01/app/oracle/oradata/prm/ogg01.dbf' size 1024M;

create user ggate identified by ggate default tablespace ogg temporary tablespace temp;

grant connect,resource,unlimited tablespace to ggate;

grant connect,resource,dba to ggate;

grant execute on utl_file to ggate;

grant select any dictionary,select any table to ggate;

grant alter any table to ggate;

grant flashback any table to ggate;

grant execute on DBMS_FLASHBACK to ggate;

grant insert any table to ggate;

grant delete any table to ggate;

grant update any table to ggate;

运行DDL同步需要的脚本

cd $GGATE

@marker_setup.sql;

@ddl_setup.sql;

@role_setup.sql;

grant GGS_GGSUSER_ROLE to ggate;

@ddl_enable.sql;

安装性能优化包。

SQL> @?/rdbms/admin/dbmspool

ddl_pin将触发器用到的plsql包放进内存中

cd $GGATE

SQL> @ddl_pin ggate

添加传输表【transdata】

光开启最小附加日志模式还不够，还需要打开表级的补全日志，可以在GoldenGate中使用add trandata命令强制重做日志记录主键值，以保证在目标端能成功复制：

GGSCI> dblogin userid ggate,password ggate --GoldenGate中登录OARCLE数据库

GGSCI>add trandata sender.* --表名可以使用通配符

GGSCI> add trandata coss3.per_test,nokey,cols(sampletime, objectid)

--无主键指定字段补全的示例

也可以在数据库中打开：

SQL> alter table <tablename> add supplemental log data (primary key) columns;

示例：

GGSCI(node1) 1> dblogin userid ogg,password oracle

GGSCI(node1) 2> add trandata SCOTT.DEPT

四配置源端进程组

配置管理进程【1】mgr

GGSCI(node1) 1> edit param mgr

（粘贴下面这段配置）

PORT 8839

--DYNAMICPORTLIST 8000-9000

--AUTOSTART ER *

AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3

PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3

--PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30

--PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

配置源端./GLOBALS

GGSCI (node1) > edit params ./GLOBALS

GGSCHEMA ggate

启动管理进程

GGSCI(node1) 2> start mgr

Managerstarted.

GGSCI(node1) 3> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

配置抽取进程【2】extnd

增加EXTRAIL

GGSCI(node1)> add extract extnd, tranlog, begin now

EXTRACT added.

修改EXTRAIL参数

GGSCI (GOLDENGATE1) 4> edit params extnd

EXTRACT extnd

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")

SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")

SETENV (ORACLE_SID = "prm")

USERID ggate, PASSWORD ggate

EXTTRAIL ./dirdat/nd

DISCARDFILE ./dirdat/extnd.dsc,APPEND,MEGABYTES 5

TRANLOGOPTIONS ALTARCHIVELOGDEST /arch/prm

TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT %t_%s_%r.dbf

DDL INCLUDE ALL

DDLOPTIONS ADDTRANDATA

FETCHOPTIONS, USESNAPSHOT, NOUSELATESTVERSION, MISSINGROW REPORT

STATOPTIONS REPORTFETCH

WARNLONGTRANS 1H, CHECKINTERVAL 5M

TABLE sender.*;

添加该extract对应的trail文件，用来存储抽取的数据。单个文件大小设置为100MB

GGSCI(node1)> add exttrail ./dirdat/nd, extract extnd, megabytes 100

EXTTRAIL added.

添加传输进程【3】dpend，配置参数

增加PUMP

GGSCI(node1)>add extract dpend, exttrailsource ./dirdat/nd, begin now

EXTRACT added.

修改PUMP参数

GGSCI(node1)> edit params dpend

EXTRACT dpend

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")

USERID ggate, PASSWORD ggate

RMTHOST 192.168.80.166, MGRPORT 8809

RMTTRAIL ./dirdat/nd

DISCARDFILE ./dirdat/dpend.dsc,APPEND,MEGABYTES 5

TABLE sender.*;

添加RMTRAILS

GGSCI(node1)> add rmttrail ./dirdat/nd, extract dpend, megabytes 100

RMTTRAIL added.

五配置目标数据库【含源端业务新增数据模拟】

数据库初始化【1】
数据库先在目标端进行初始化（使用rman备份恢复的方式）
源数据库端进行一些数据的更新，并且刷新几个日志
创建GoldenGate数据库用户并授权【2】

创建表空间、用户、赋予dba权限

create tablespace ogg datafile '/u01/app/oracle/oradata/prm/ogg01.dbf' size 1024M;

create user ggate identified by ggate default tablespace ogg temporary tablespace temp;

grant connect,resource,unlimited tablespace to ggate;

grant connect,resource,dba to ggate;

grant execute on utl_file to ggate;

grant select any dictionary,select any table to ggate;

grant alter any table to ggate;

grant flashback any table to ggate;

grant execute on DBMS_FLASHBACK to ggate;

grant insert any table to ggate;

grant delete any table to ggate;

grant update any table to ggate;

运行DDL同步需要的脚本

@$GGATE/marker_setup.sql;

@$GGATE/ddl_setup.sql;

@$GGATE/role_setup.sql;

grant GGS_GGSUSER_ROLE to ggate;

@$GGATE/ddl_enable.sql;

安装性能优化包。

SQL> @?/rdbms/admin/dbmspool

ddl_pin将触发器用到的plsql包放进内存中

SQL> @ddl_pin ggate

六配置目标端进程组

配置MGR参数mgr

GGSCI(node2) 1> edit param mgr

（粘贴下面这段配置）

PORT 8839

--DYNAMICPORTLIST 8000-9000

--AUTOSTART ER *

AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3

PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3

--PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30

--PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

配置目标端./GLOBALS

GGSCI (node2)> edit params ./GLOBALS

GGSCHEMA ggate

CHECKPOINTTABLE ggate.chkpoint

添加checkpoint表

需要先退出ggsci

GGSCI (node2)> dblogin userid ggate, password ggate

Successfully logged into database.

GGSCI (node2)> add checkpointtable ggate.chkpoint

Successfully created checkpoint table goldengate.chkpoint.

配置复制队列【4】repnd

增加目标端REP

GGSCI (node2)> add replicat repnd, exttrail ./dirdat/nd, checkpointtable ggate.chkpoint

REPLICAT added.

修改REP参数

GGSCI (node2)> edit params repnd

REPLICAT repnd

ASSUMETARGETDEFS

USERID ggate, PASSWORD ggate

DISCARDFILE ./dirrpt/repnd.dsc,APPEND,MEGABYTES 5

DDL INCLUDE ALL

DDLOPTIONS REPORT

BATCHSQL

DBOPTIONS DEFERREFCONST

DBOPTIONS LOBWRITESIZE 102400

DDLERROR DEFAULT DISCARD RETRYOP MAXRETRIES 5 RETRYDELAY 20

MAP sender.* , TARGET sender.* ;

七启动进程进行数据库同步

启动源端进程组

start mgr

启动抽取进程和传输进程

start extnd

start dpend

启动目标端进程

start mgr

start repnd

八数据同步测试

数据同步测试

源数据库上创建数据

conn sender/oracle

create table t1 (id number CONSTRAINT id_nn NOT NULL,

name VARCHAR2(40),

PRIMARY KEY (id)

);

insert into t1 values (1,'aaa');

insert into t1 values (2,'bbb');

insert into t1 values (3,'ccc');

insert into t1 values (4,'ddd');

create table t2 (id number CONSTRAINT id_nm NOT NULL,

addr VARCHAR2(40),

PRIMARY KEY (id)

);

insert into t2 values (1,'word');

insert into t2 values (2,'now');

insert into t2 values (3,'values');

insert into t2 values (4,'beijing');

测试时使用语句

insert into t1 values (5,'eee')

insert into t1 values (6,'fff')

insert into t1 values (7,'ggg')

create table t3 as select * from t1 where 1=0;

create table t4 as select * from t1;

alter table t2 rename to t4;

九环境说明

环境介绍

十各程序组配置名称及参数【成功后记录】

源端进程组

管理进程：mgr

参数：

GGSCI(node1) 1> edit param mgr

（粘贴下面这段配置）

PORT 8839

--DYNAMICPORTLIST 8000-9000

--AUTOSTART ER *

AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3

PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3

--PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30

--PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

GLOBALS：

GGSCI (node1) > edit params ./GLOBALS

GGSCHEMA ggate

抽取进程：extnd

参数：

增加EXTRAIL

GGSCI(node1)> add extract extnd, tranlog, begin now

EXTRACT added.

修改EXTRAIL参数

GGSCI (GOLDENGATE1) 4> edit params extnd

EXTRACT extnd

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")

SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")

SETENV (ORACLE_SID = "prm")

USERID ggate, PASSWORD ggate

EXTTRAIL ./dirdat/nd

DISCARDFILE ./dirdat/extnd.dsc,APPEND,MEGABYTES 5

TRANLOGOPTIONS ALTARCHIVELOGDEST /arch/prm

TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT %t_%s_%r.dbf

DDL INCLUDE ALL

DDLOPTIONS ADDTRANDATA

FETCHOPTIONS, USESNAPSHOT, NOUSELATESTVERSION, MISSINGROW REPORT

STATOPTIONS REPORTFETCH

WARNLONGTRANS 1H, CHECKINTERVAL 5M

TABLE sender.*;

添加该extract对应的trail文件，用来存储抽取的数据。单个文件大小设置为100MB

GGSCI(node1)> add exttrail ./dirdat/nd, extract extnd, megabytes 100

EXTTRAIL added.

传输进程：dpend

参数：

增加PUMP

GGSCI(node1)>add extract dpend, exttrailsource ./dirdat/nd, begin now

EXTRACT added.

修改PUMP参数

GGSCI(node1)> edit params dpend

EXTRACT dpend

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")

USERID ggate, PASSWORD ggate

RMTHOST 192.168.80.166, MGRPORT 8809

RMTTRAIL ./dirdat/nd

DISCARDFILE ./dirdat/dpend.dsc,APPEND,MEGABYTES 5

TABLE sender.*;

添加RMTRAILS

GGSCI(node1)> add rmttrail ./dirdat/nd, extract dpend, megabytes 100

RMTTRAIL added.

目标端进程组

管理进程：mgr

参数：

GGSCI(node2) 1> edit param mgr

（粘贴下面这段配置）

PORT 8839

--DYNAMICPORTLIST 8000-9000

--AUTOSTART ER *

AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3

PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3

--PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30

--PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

GLOBALS：

GGSCI (node2)> edit params ./GLOBALS

GGSCHEMA ggate

CHECKPOINTTABLE ggate.chkpoint

复制进程：repnd

参数：

增加目标端REP

GGSCI (node2)> add replicat repnd, exttrail ./dirdat/nd, checkpointtable ggate.chkpoint

REPLICAT added.

修改REP参数

GGSCI (node2)> edit params repnd

REPLICAT repnd

ASSUMETARGETDEFS

USERID ggate, PASSWORD ggate

DISCARDFILE ./dirrpt/repnd.dsc,APPEND,MEGABYTES 5

DDL INCLUDE ALL

DDLOPTIONS REPORT

BATCHSQL

DBOPTIONS DEFERREFCONST

DBOPTIONS LOBWRITESIZE 102400

DDLERROR DEFAULT DISCARD RETRYOP MAXRETRIES 5 RETRYDELAY 20

MAP sender.* , TARGET sender.* ;