如何实现OGG单到单部署

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

这篇文章主要介绍了如何实现OGG单到单部署，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

环境介绍：
项目源环境目标环境
操作系统：Oracle Linux Server release 6.5
主机名：ogg1，ogg2
IP地址：ogg1：192.168.1.11，ogg2：192.168.1.12
数据库及版本：11.2.0.4
数据库字符集：AL32UTF8
SID：ogg1：mtx，ogg2：ymx
OGG用户：ogg1：ogg，ogg2：ogg
OGG版本：11.2.1.0.1
归档模式：archive log

部署步骤：
1.创建OGG操作系统用户（非必须，使用Oracle用户也可以）
useradd -u 2100 -g oinstall -G dba ogg
passwd ogg2008

2.修改操作系统用户的环境变量，因为本环境没有对OGG创建操作系统用户，直接使用的Oracle用户，所以环境变量为Oracle用户的环境变量
export PATH
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2/db
export ORACLE_SID=mtx
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/usr/X11R6/lib64
export CLASSPATH=$ORACLE_HOME/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
注意：如果是新创建的操作系统用户，配置环境变量之后要重载一下：source .bash_profile

3.两台服务器创建OGG安装路径并更改权限
mkdir /u01/app/ogg -pv
chown oracle.oinstall /u01/app/ogg -R

4.将OGG安装文件上传到两台服务器的OGG路径下，本人的路径为“/u01/app/ogg”
OGG软件的下载网址在Oracle官网，https://edelivery.oracle.com/osdc/faces/SearchSoftware 找到对应版本即可。
解压上传的文件“V34339-01.zip”，这时你会看到路径下又会生成其他多个文件，包括文档、使用说明等
其中有一个tar包“fbo_ggs_Linux_x64_ora11g_64bit.tar”，我们需要解压这个tar文件。
[oracle@ogg1 ogg]$ unzip V34339-01.zip （两个服务器都要做）
Archive: V34339-01.zip
inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar
inflating: Oracle_GoldenGate_11.2.1.0.3_README.doc
inflating: Oracle GoldenGate_11.2.1.0.3_README.txt
inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.3.pdf
[oracle@ogg1 ogg]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar（两个服务器都要做）

5.解压成功之后，登录OGG测试：
[oracle@ogg1 ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (ogg1) 1>
注意：如果LD_LIBRARY_PATH变量设置正确，会像上面演示一样正确进入命令行，如果提示找不到so文件，就需要查看LD_LIBRARY_PATH变量是否配置正确

6.创建OGG的管理目录（两端都要做）
GGSCI (ogg1) 1> create subdirs

Creating subdirectories under current directory /u01/app/ogg

Parameter files /u01/app/ogg/dirprm: already exists--存放OGG参数的配置信息
Report files /u01/app/ogg/dirrpt: created--存放进程报告文件
Checkpoint files /u01/app/ogg/dirchk: created--存放检查点文件
Process status files /u01/app/ogg/dirpcs: created--存放进程状态文件
SQL script files /u01/app/ogg/dirsql: created--存放sql脚本文件
Database definitions files /u01/app/ogg/dirdef: created--存放defgen工具生成的数据定义文件
Extract data files /u01/app/ogg/dirdat: created--存放Trail文件，也就是捕获进程的文件
Temporary files /u01/app/ogg/dirtmp: created--当事物需要的内存超过已分配内存时，默认存储在这个目录
Stdout files /u01/app/ogg/dirout: created

7.源端创建OGG表空间和OGG用户
Oracle建议使用单独的表空间存放OGG数据，表空间大小可以自己定义，也可以设置数据文件是否自动扩展，建议自动扩展
SYS@mtx> create tablespace ogg datafile '/u01/app/oracle/oradata/mtx/ogg01.dbf'
2 size 1024m
3 autoextend on;

SYS@mtx> create user ogg identified by ogg2008 default tablespace ogg;

8.给予ogg用户相应的权限，有些人为了省事，直接给OGG用户DBA的权限
SYS@mtx> grant CONNECT, RESOURCE to ogg;
SYS@mtx> grant SELECT ANY DICTIONARY, SELECT ANY TABLE to ogg;
SYS@mtx> grant ALTER ANY TABLE to ogg;
SYS@mtx> grant FLASHBACK ANY TABLE to ogg;
SYS@mtx> grant EXECUTE on DBMS_FLASHBACK to ogg;
SYS@mtx> grant insert any table to ogg;
SYS@mtx> grant update any table to ogg;
SYS@mtx> grant delete any table to ogg;
或者
SYS@mtx> grant resource,connect,dba to ogg;

9.建立测试表
源端基于scott用户的EMP和DEPT表建立相应的测试表，包括其中的数据
SCOTT@mtx> create table emp_ogg as select * from emp;

Table created.

SCOTT@mtx> create table dept_ogg as select * from dept;

Table created.

目标端基于scott用户的EMP和DEPT表建立相应的测试表，不包含数据
SCOTT@ymx> create table emp_ogg as select * from emp where 1=2;

Table created.

SCOTT@ymx> create table dept_ogg as select * from dept where 1=2;

Table created.

源端和目标端分别在两个测试表上建立主键或唯一键约束
SCOTT@mtx> alter table EMP_OGG add constraint PK_EMPNO_OGG primary key (EMPNO);

Table altered.

SCOTT@mtx> alter table DEPT_OGG add constraint PK_DEPTNO_OGG primary key (DEPTNO);

Table altered.

10.源端添加最小补充日志和表级补充日志

打开最小补充日志：
SYS@mtx> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
NO

SYS@mtx> alter database add supplemental log data;

Database altered.

打开表级补充日志（需要以最小补充日志为前提）
GGSCI (ogg1) 1> dblogin userid ogg,password ogg2008
Successfully logged into database.

GGSCI (ogg1) 2> add trandata scott.emp_ogg

Logging of supplemental redo data enabled for table SCOTT.EMP_OGG.

GGSCI (ogg1) 3> add trandata scott.dept_ogg

Logging of supplemental redo data enabled for table SCOTT.DEPT_OGG.

SYS@mtx> alter table scott.emp_ogg logging;

Table altered.

SYS@mtx> alter table scott.dept_ogg logging;

Table altered.

11.配置MGR管理进程
源端和目标端：
GGSCI (ogg1) 1> edit param mgr
PORT 7809--服务监听端口，默认7809
DYNAMICPORTLIST 7840-7939--动态端口，指定端口不可用时，从列表中选择一个作为可用端口，可以制定最大256个
AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 3--自动重启参数设置，本处设置表示每3分钟尝试重新启动所有EXTRACT进程，共尝试5次
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, minkeepdays 7--定期清理trail文件，本处设置表示对超过7天的trail文件进行删除，从此参数也能看到trail文件的位置
LAGREPORTHOURS 1--定义延迟预警机制，本处设置每隔1小时检查extract的延迟情况
LAGINFOMINUTES 30--定义延迟预警机制，本处设置超过了30分钟就把延迟新信息记录到错误日志中
LAGCRITICALMINUTES 45--定义延迟预警机制，本处设置延迟超过45分钟，则把它作为警告写到错误日志中

启动MGR管理进程
GGSCI (ogg1) 2> start mgr

12.配置抽取进程
添加捕获进程：
GGSCI (ogg1) 4> add extract ext1,tranlog,begin now
EXTRACT added.
指定捕获进程生成的trail文件所在路径
GGSCI (ogg1) 7> add exttrail /u01/app/ogg/dirdat/ea, extract ext1, megabytes 100
EXTTRAIL added.
配置进程的参数文件
GGSCI (ogg1) 6> edit param ext1
extrail ext1
SETENV (ORACLE_SID="mtx")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg , PASSWORD ogg2008配置
EXTTRAIL /u01/app/ogg/dirdat/ea
NUMFILES 3000
GETTRUNCATES
DYNAMICRESOLUTION
table scott.dept_ogg;
table scott.emp_ogg;

启动捕获进程
GGSCI (ogg1) 31> start ext1

Sending START request to MANAGER ...
EXTRACT EXT1 starting

13.配置传输进程
添加传输进程：
GGSCI (ogg1) 16> add extract pum1,exttrailsource /u01/app/ogg/dirdat/ea
EXTRACT added.
指定传输进程的trail文件路径
GGSCI (ogg1) 18> add rmttrail /u01/app/ogg/dirdat/pm,extract pum1,megabytes 100
RMTTRAIL added.
配置进程的参数文件
extract pum1
SETENV (ORACLE_SID="mtx")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
RMTHOST 192.168.1.12,MGRPORT 7809, TCPBUFSIZE 100000, TCPFLUSHBYTES 300000
CACHEMGR CACHEBUFFERSIZE 64KB, CACHESIZE 1024MB, CACHEDIRECTORY /u01/app/ogg/dirtmp
rmttrail /u01/app/ogg/dirdat/pm
PASSTHRU
DYNAMICRESOLUTION
TABLE scott.*;

启动传输进程

GGSCI (ogg1) 32> start pum1

Sending START request to MANAGER ...
EXTRACT PUM1 starting

14.做初始化操作
查询当前数据库的系统改变号
SYS@mtx> select to_char(sysdate,'yyyy-mm-dd hh34:mi:ss') ,dbms_flashback.get_system_change_number || '' from dual;
15158302130192
SYSDATE DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER|
------------------ ----------------------------------------
13-JUL-17 286349

按SCN号导出两个表的数据，用数据泵的方式，目录对象需要自己创建
expdp ogg/ogg2008 directory=pump exclude=grant dumpfile=dept_ogg.dmp tables=SCOTT.DEPT_OGG flashback_scn=286349
expdp ogg/ogg2008 directory=pump exclude=grant dumpfile=emp_ogg.dmp tables=SCOTT.EMP_OGG flashback_scn=286349

通过SCP或者FTP的方式将导出的数据文件传送到目标端做导入操作
impdp ogg/ogg2008 directory=pump dumpfile=dept_ogg.dmp tables=SCOTT.DEPT_OGG table_exists_action=append
impdp ogg/ogg2008 directory=pump dumpfile=emp_ogg.dmp tables=SCOTT.EMP_OGG table_exists_action=append

--15.目标端配置OGG的检查点
--此步骤不是必须的，但是为了让OGG网络中断、服务器宕机、掉电等在突发情况下也能正确断点续传，Oracle建议配置OGG的检查点队列

--GGSCI (ogg2) 75> edit param ./GLOBALS
--checkpointtable ogg.checkpoint_tab

--GGSCI (ogg2) 7> add checkpointtable ogg.checkpoint_tab

--Successfully created checkpoint table ogg.checkpoint_tab.

16.目标端配置replycat进程
GGSCI (ogg2) 8> add replicat rep1 exttrail /u01/app/ogg/dirdat/pm, checkpointtable ogg.checkpoint_tab
REPLICAT added.

GGSCI (ogg2) 12> edit param rep1
replicat rep1
SETENV (ORACLE_SID="ymx")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg , PASSWORD ogg2008
sqlexec "Alter session set commit_write=nowait"
DISCARDFILE /u01/app/ogg/dirrpt/rep1err.dsc,append,megabytes 100
ASSUMETARGETDEFS
ALLOWNOOPUPDATES
DYNAMICRESOLUTION
NUMFILES 3000
EOFDELAYCSECS 30
GETTRUNCATES
BATCHSQL BATCHESPERQUEUE 200,OPSPERBATCH 2000
MAP SCOTT.DEPT_OGG, TARGET SCOTT.DEPT_OGG*;
MAP SCOTT.EMP_OGG, TARGET SCOTT.EMP_OGG*;

开启replycat进程
GGSCI (ogg2) 18> start replicat rep1,aftercsn 286349

17.测试效果
源端：
SCOTT@mtx> select * from dept_ogg;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SCOTT@mtx> insert into dept_ogg values (50,'TEC','BEIJING');

1 row created.

SCOTT@mtx> commit;

Commit complete.

源端捕获到的结果：
GGSCI (ogg1) 71> stats ext1

Sending STATS request to EXTRACT EXT1 ...

Start of Statistics at 2017-07-13 14:39:28.

Output to /u01/app/ogg/dirdat/ea:

Extracting from SCOTT.DEPT_OGG to SCOTT.DEPT_OGG:

*** Total statistics since 2017-07-13 14:37:03 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Daily statistics since 2017-07-13 14:37:03 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Hourly statistics since 2017-07-13 14:37:03 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Latest statistics since 2017-07-13 14:37:03 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

End of Statistics.

GGSCI (ogg1) 72> stats pum1

Sending STATS request to EXTRACT PUM1 ...

Start of Statistics at 2017-07-13 14:39:33.

Output to /u01/app/ogg/dirdat/pm:

Extracting from SCOTT.DEPT_OGG to SCOTT.DEPT_OGG:

*** Total statistics since 2017-07-13 14:37:03 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Daily statistics since 2017-07-13 14:37:03 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Hourly statistics since 2017-07-13 14:37:03 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Latest statistics since 2017-07-13 14:37:03 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

End of Statistics.

目标端：
GGSCI (ogg2) 38> stats rep1

Sending STATS request to REPLICAT REP1 ...

Start of Statistics at 2017-07-13 18:11:18.

Replicating from SCOTT.DEPT_OGG to SCOTT.DEPT_OGG:

*** Total statistics since 2017-07-13 18:11:11 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Daily statistics since 2017-07-13 18:11:11 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Hourly statistics since 2017-07-13 18:11:11 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Latest statistics since 2017-07-13 18:11:11 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

End of Statistics.

目标端将数据应用到数据库中的结果：
SCOTT@ymx> select * from dept_ogg;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 TEC BEIJING