实验环境：
源端：192.168.1.30，Oracle 10.2.0.5 单实例
目标端：192.168.1.31，Oracle 10.2.0.5 单实例

• 1.模拟源数据库业务持续运行
• 2.配置OGG前期准备
• 3.配置OGG单向复制

1.模拟源数据库业务持续运行

OGG的单向配置比较简单，但实际生产过程很多业务要求不间断运行，
所以我创建了2张模拟业务表，简单模拟在业务不间断运行场景下OGG的配置。

1.1 创建模拟的业务用户

首先我创建业务用户jy，并指定密码，赋予基本业务用户的角色权限。

--user create user jy identified by jy default tablespace users; --grant grant resource, connect to jy;

1.2 在业务用户下创建表和存储过程

连接到业务用户jy下，创建1个序列，2张表（其中一张表有主键）：

--connect conn jy/jy --sequence create sequence s1; --tables --table t_second 无主键 create table t_second as select s1.nextval id, to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') time from dual; --table t_second_p 有主键 create table t_second_p as select s1.nextval id, to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') time from dual; alter table t_second_p add constraint pk_t_second_p primary key(id);

创建存储过程,功能是：每秒向2张业务表分别插入1条数据；
用来模拟持续运行的业务；

--procedure create or replace procedure p1 is begin for i in 1..86400 loop insert into t_second select s1.nextval, to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; commit; insert into t_second_p select s1.nextval, to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; commit; dbms_lock.sleep(1); end loop; end; /

执行存储过程，相当于模拟业务正式启动：

--execute [oracle@oradb30 scripts]$ cat business.sh #!/bin/bashsqlplus jy/jy <<EOF exec p1; EOF [oracle@oradb30 scripts]$ nohup sh business.sh &

2.配置OGG前期准备

2.1 创建源端和目标端OGG的管理员用户

--源数据库端： create user ggs_admin identified by ggs_admin; grant dba to ggs_admin;--目标数据库端： create user ggt_admin identified by ggt_admin; grant dba to ggt_admin;

2.2 配置源数据库端的附加日志

--查看数据库附加日志开启状态： SQL> select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database; --数据库级别开启最小附加日志： SQL> alter database add supplemental log data; --表级别开启详细附加日志： GGSCI (oradb30) 6> dblogin userid ggs_admin@ora10, password ggs_admin Successfully logged into database. GGSCI (oradb30) 7> add trandata jy.t_second 2017-01-13 23:37:38 WARNING OGG-00869 No unique key is defined for table 'T_SECOND'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key. Logging of supplemental redo data enabled for table JY.T_SECOND. GGSCI (oradb30) 8> add trandata jy.t_second_p Logging of supplemental redo data enabled for table JY.T_SECOND_P. GGSCI (oradb30) 9>

2.3 配置源端和目标端的OGG所需进程

配置进程（组）名没有具体的规范，我这里参考晓明在书中所用的组命名方式：
对于复杂环境，第一位字母，local、immdiate、remote 分别对应 l、i、r；
后面根据进程属性属于extract、datapump、replicat 而分别对应 x、p、r；

源端：
源端在上篇已经配置好Manager进程。
接下来继续配置源端的extract进程：
GGSCI (oradb30) 3> edit params lxjy1

--Local Extract lxjy1 --Author: Alfred Zhao -- EXTRACT lxjy1 SETENV(NLS_LANG=american_america.ZHS16GBK) SETENV(ORACLE_SID=ora10) USERID ggs_admin, PASSWORD ggs_admin EXTTRAIL ./dirdat/sa TABLE JY.T_SECOND; TABLE JY.T_SECOND_P;

添加lxjy1进程（local extract）：

GGSCI (oradb30) 1> add extract lxjy1, tranlog, begin now, threads 1 EXTRACT added. GGSCI (oradb30) 2> add exttrail ./dirdat/sa, extract lxjy1, megabytes 50 EXTTRAIL added.

配置源端的datapump进程：

--Local datapump lpjy1 --Author: Alfred Zhao -- EXTRACT lpjy1 PASSTHRU RMTHOST 192.168.1.31, MGRPORT 7777 RMTTRAIL ./dirdat/ta TABLE JY.T_SECOND; TABLE JY.T_SECOND_P;

添加lpjy1进程（本地datapump进程）：

GGSCI (oradb30) 2> add extract lpjy1, exttrailsource ./dirdat/sa, begin now EXTRACT added. GGSCI (oradb30) 17> ADD RMTTRAIL ./dirdat/ta, EXTRACT LPJY1, MEGABYTES 50 RMTTRAIL added.

查看目前源端进程状态：

GGSCI (oradb30) 7> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT STOPPED LPJY1 00:00:00 00:07:01 EXTRACT STOPPED LXJY1 00:00:00 00:18:55

可以看到Manager进程启动，LXJY1和LPJY1没有启动。
目标端：
参考《OGG学习笔记01-基础概述》中安装配置，对目标端也配置Manager进程。
配置checkpointtable：

GGSCI (oradb31) 1> view params ./GLOBALS checkpointtable ggt_admin.chkpt GGSCI (oradb31) 2> dblogin userid ggt_admin@ora10dg, password ggt_admin Successfully logged into database. GGSCI (oradb31) 3> add checkpointtable No checkpoint table specified, using GLOBALS specification (ggt_admin.chkpt)... Successfully created checkpoint table ggt_admin.chkpt.

然后继续配置replicat进程：

--replicat rjy1 --Author: Alfred Zhao -- REPLICAT rjy1 SETENV (ORACLE_SID=ora10) USERID ggt_admin, PASSWORD ggt_admin DISCARDFILE ./dirrpt/rjy1.dsc, PURGE HandleCollisions AssumeTargetDefs Map jy.*,Target jy.*;

添加rjy1进程（replicat进程）：

GGSCI (oradb31) 8> add replicat rjy1, exttrail ./dirdat/ta REPLICAT added.

查看当前目标端进程状态：

GGSCI (oradb31) 9> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING REPLICAT STOPPED RJY1 00:00:00 00:00:18

可以看到Manager已经启动，RJY1还没有启动。

3.配置OGG单向复制

注意以下3个步骤执行的顺序不能更改，否则很可能会造成数据不一致。

3.1 启动源库extract进程

确认已经启动Manager进程。
启动lxjy1

GGSCI (oradb30) 12> start extract lxjy1

启动lpjy1

GGSCI (oradb30) 19> start extract lpjy1

查看源端进程状态：

GGSCI (oradb30) 20> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT RUNNING LPJY1 01:08:28 00:00:00 EXTRACT RUNNING LXJY1 00:00:00 00:00:10

可以看到所有进程都是正常RUNNING状态。如果启动后状态不是RUNNING，就需要检查配置和查看 ggserr.log 日志信息定位原因。

现在可以查看源端extract进程信息：

GGSCI (oradb30) 21> info extract lxjy1EXTRACT LXJY1 Last Started 2017-01-14 00:57 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:03 ago) Log Read Checkpoint Oracle Redo Logs2017-01-14 01:05:56 Thread 1, Seqno 133, RBA 41481856SCN 0.1517654 (1517654)GGSCI (oradb30) 22> info extract lpjy1EXTRACT LPJY1 Last Started 2017-01-14 01:02 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:09 ago) Log Read Checkpoint File ./dirdat/sa0000002017-01-14 01:06:00.000000 RBA 1551197

可以看到OGG的extract进程抓取到的信息：

GGSCI (oradb30) 26> STATS ext lxjy1Sending STATS request to EXTRACT LXJY1 ...Start of Statistics at 2017-01-14 01:09:21.Output to ./dirdat/sa:Extracting from JY.T_SECOND to JY.T_SECOND:*** Total statistics since 2017-01-14 00:58:01 ***Total inserts 5217.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 5217.00*** Daily statistics since 2017-01-14 00:58:01 ***Total inserts 5217.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 5217.00*** Hourly statistics since 2017-01-14 01:00:00 ***Total inserts 560.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 560.00*** Latest statistics since 2017-01-14 00:58:01 ***Total inserts 5217.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 5217.00Extracting from JY.T_SECOND_P to JY.T_SECOND_P:*** Total statistics since 2017-01-14 00:58:01 ***Total inserts 5217.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 5217.00*** Daily statistics since 2017-01-14 00:58:01 ***Total inserts 5217.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 5217.00*** Hourly statistics since 2017-01-14 01:00:00 ***Total inserts 560.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 560.00*** Latest statistics since 2017-01-14 00:58:01 ***Total inserts 5217.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 5217.00End of Statistics.GGSCI (oradb30) 27> stats ext lpjy1Sending STATS request to EXTRACT LPJY1 ...Start of Statistics at 2017-01-14 01:09:31.Output to ./dirdat/ta:Extracting from JY.T_SECOND to JY.T_SECOND:*** Total statistics since 2017-01-14 01:02:43 ***Total inserts 4509.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 4509.00*** Daily statistics since 2017-01-14 01:02:43 ***Total inserts 4509.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 4509.00*** Hourly statistics since 2017-01-14 01:02:43 ***Total inserts 4509.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 4509.00*** Latest statistics since 2017-01-14 01:02:43 ***Total inserts 4509.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 4509.00Extracting from JY.T_SECOND_P to JY.T_SECOND_P:*** Total statistics since 2017-01-14 01:02:43 ***Total inserts 4509.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 4509.00*** Daily statistics since 2017-01-14 01:02:43 ***Total inserts 4509.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 4509.00*** Hourly statistics since 2017-01-14 01:02:43 ***Total inserts 4509.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 4509.00*** Latest statistics since 2017-01-14 01:02:43 ***Total inserts 4509.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 4509.00End of Statistics.GGSCI (oradb30) 28>

3.2 初始化目标库数据

我这里使用Oracle的exp/imp工具，从源端导出数据，导入到目标端。
exp 导出源库表数据
--exp 导出
exp jy/jy tables=t_second,t_second_p file=source_jy.dmp log=exp_source_jy.log

SQL> select count(1) from t_second;COUNT(1) ----------11056SQL> select count(1) from t_second_p; COUNT(1) ----------11072[oracle@oradb30 ~]$ exp jy/jy tables=t_second,t_second_p file=source_jy.dmp log=exp_source_jy.logExport: Release 10.2.0.5.0 - Production on Sat Jan 14 01:15:26 2017Copyright (c) 1982, 2007, Oracle. All rights reserved.Connected to: Oracle Database 0g Enterprise Edition Release 10.2.0.5.0 - 4bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses ZHS16GBK character set (possible charset conversion)About to export specified tables via Conventional Path ... . . exporting table T_SECOND 11096 rows exported EXP-00091: Exporting questionable statistics. . . exporting table T_SECOND_P 11097 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. Export terminated successfully with warnings.

imp 导入目标库数据
--创建用户
create user jy identified by jy default tablespace users;
grant resource, connect to jy;
--imp 导入
imp jy/jy tables=t_second,t_second_p file=source_jy.dmp log=imp_source_jy.log

[oracle@oradb31 ~]$ imp jy/jy tables=t_second,t_second_p file=source_jy.dmp log=imp_source_jy.logImport: Release 10.2.0.5.0 - Production on Sat Jan 14 01:20:08 2017Copyright (c) 1982, 2007, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport file created by EXPORT:V10.02.01 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses ZHS16GBK character set (possible charset conversion) . importing JY's objects into JY . importing JY's objects into JY . . importing table "T_SECOND" 11096 rows imported . . importing table "T_SECOND_P" 11097 rows imported Import terminated successfully without warnings.

3.3 开启目标库replicat应用

确认已经启动Manager进程。
启动replicat应用

GGSCI (oradb31) 2> start replicat rjy1 Sending START request to MANAGER ... REPLICAT RJY1 startingGGSCI (oradb31) 3> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RJY1 00:00:00 00:41:50

下面我来验证数据：
--开启replicat之前查询目标库，也就是初始化导入的数据：

SQL> select count(1) from t_second;COUNT(1) ----------11096 SQL> select count(1) from t_second_p;COUNT(1) ----------11097

--开启replicat后查询目标库：

SQL> select count(1) from t_second_p;COUNT(1) ----------11581 SQL> select count(1) from t_second;COUNT(1) ----------16459

为了更清晰的看到差异，我将模拟应用的会话杀掉，这样两表的数量不再变化。

--查询源库两个表的数量：

SQL> select count(1) from t_second_p;COUNT(1) ----------11966 SQL> select count(1) from t_second;COUNT(1) ----------11966

--查看目标库两个表的数量：

SQL> select count(1) from t_second_p;COUNT(1) ----------11966 SQL> select count(1) from t_second;COUNT(1) ----------16840

发现有主键的表t_second_p在ogg同步后数据完全一致，符合预期；
而没有主键的表t_second在ogg同步后数据重复插入了一部分（即源端开启extract进程后捕获到的插入，和imp导入成功前这期间的数据重复插入了）
根据这个现象，OGG需要同步的表还是最好有主键约束。
最后，一切正常后，把目标端replicat进程的参数文件中的HandleCollisions配置去掉。因为正式同步后是建议有冲突问题人工处理。

Reference

• • 张晓明. 大话Oracle Grid[M]. 人民邮电出版社, 2014.
  • Oracle GoldenGate 11g Implementer's guide

转载于:https://www.cnblogs.com/wangsicongde/p/7577082.html

总结

以上是生活随笔为你收集整理的OGG学习笔记02的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。