OGG 是一种基于日志的结构化数据复制软件,能够实现大量交易数据的实时捕捉，变换和投递，实现源数据库与目标数据库的数据同步，保持最少10ms的数据延迟。本文raksmart美国服务器将介绍ogg的安装与配置
一、安装Oracle goldengate
直接解压即可
[oracle@tomato-@ORACLE_SID ggs]$ ls
fbo_ggs_Linux_x86_ora11g_32bit.tar
[oracle@tomato-@ORACLE_SID ggs]$ tar -xvf
fbo_ggs_Linux_x86_ora11g_32bit.tar
[oracle@tomato-@ORACLE_SID ggs]$ rm -rf
fbo_ggs_Linux_x86_ora11g_32bit.tar
二、数据库中的配置与准备
打开数据库最小附加日志（源端）
SQL> select supplemental_log_data_min
from v$database;
SUPPLEME
——–
NO
SQL> alter database add supplemental log
data;
Database altered.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
建立OGG数据库用户并授予权限（源端） 推荐阅读：CPANEL与WHM的区别
SQL> CREATE USER goldengate IDENTIFIED
BY goldengate;
User created.
SQL> GRANT CONNECT TO goldengate;
Grant succeeded.
SQL> GRANT CREATE SESSION TO goldengate;
Grant succeeded.
SQL> GRANT ALTER SESSION TO goldengate;
Grant succeeded.
SQL> GRANT RESOURCE TO goldengate;
Grant succeeded.
SQL> GRANT SELECT ANY DICTIONARY TO
goldengate;
Grant succeeded.
SQL> GRANT SELECT ANY TABLE TO
goldengate;
Grant succeeded.
SQL> GRANT FLASHBACK ANY TABLE TO
goldengate;
Grant succeeded.
SQL> GRANT ALTER ANY TABLE TO
goldengate;
Grant succeeded.
建立OGG数据库用户并授予权限（目标端）
这里goldengate所需要的权限要比源端的多 推荐阅读：RLWRAP的安装与注意事项
SQL> CREATE USER goldengate IDENTIFIED
BY goldengate;
User created.
SQL> GRANT ALTER SESSION TO goldengate;
Grant succeeded.
SQL> GRANT CREATE SESSION TO goldengate;
Grant succeeded.
SQL> GRANT CONNECT TO goldengate;
Grant succeeded.
SQL> GRANT RESOURCE TO goldengate;
Grant succeeded.
SQL> GRANT SELECT ANY DICTIONARY TO
goldengate;
Grant succeeded.
SQL> GRANT SELECT ANY TABLE TO
goldengate;
Grant succeeded.
SQL> GRANT FLASHBACK ANY TABLE TO
goldengate;
Grant succeeded.
SQL> GRANT INSERT ANY TABLE TO
goldengate;
Grant succeeded.
SQL> GRANT UPDATE ANY TABLE TO
goldengate;
Grant succeeded.
SQL> GRANT DELETE ANY TABLE TO
goldengate;
Grant succeeded.
三、goldengate中的配置
1.目标和源的准备
执行./ggsci
[oracle@tomato-@ORACLE_SID ggs]$ ./ggsci
./ggsci: error while loading shared
libraries: libnnz11.so: cannot open shared object file: No such file or
directory
ps：若是报错，在oracle的profile文件加入一行语句即可 更多内容请阅读：什么是WEB SERVICE？
[oracle@tomato-@ORACLE_SID ggs]$ vi
/home/oracle/profile
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
[oracle@tomato-@ORACLE_SID ggs]$ .
/home/oracle/profile
[oracle@tomato-@ORACLE_SID ggs]$ ./ggsci
Oracle GoldenGate Command Interpreter for
Oracle
Version 11.2.1.0.1
OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g
on Apr 23 2012 08:09:25
Copyright (C) 1995, 2012, Oracle and/or its
affiliates. All rights reserved.
GGSCI (tomato.oracle.com) 1>
创建所需文件夹
GGSCI (tomato.oracle.com) 1> create
subdirs
Creating subdirectories under current
directory /oracle/ggs
Parameter files /oracle/ggs/dirprm: already
exists
Report files /oracle/ggs/dirrpt: created
Checkpoint files /oracle/ggs/dirchk:
created
Process status files /oracle/ggs/dirpcs:
created
SQL script files /oracle/ggs/dirsql:
created
Database definitions files
/oracle/ggs/dirdef: created
Extract data files /oracle/ggs/dirdat: created
Temporary files /oracle/ggs/dirtmp: created
Stdout files /oracle/ggs/dirout: created
配置Manager进程并打开
GGSCI (potato.oracle.com) 2> edit param
mgr
port 7809 –指定端口号
GGSCI (linfan) 7> START MGR
Manager started.
2.目标端的配置
加入trandata（表级附加日志）
GGSCI (potato.oracle.com) 4> dblogin
userid goldengate , password goldengate
Successfully logged into database.
GGSCI (potato.oracle.com) 5> add
trandata lala.*
2017-03-06 20:11:24 WARNING OGG-00869 No
unique key is defined for table ‘STUDENT’. 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 LALA.STUDENT.
ps：这里的WARNING是因为lala用户下的student表没有主键，所以把所有的列都加入附加日志 了解更多相关知识请阅读：什么是WORDPRESS？
添加EXTRACT进程并配置
GGSCI (potato.oracle.com) 6> ADD EXTRACT
capdb1, TRANLOG, BEGIN NOW —添加进程
EXTRACT added.
GGSCI (potato.oracle.com) 7> ADD
EXTTRAIL /oracle/ggs/dirdat/lt, EXTRACT capdb1 —改进使用本地队列
EXTTRAIL added.
GGSCI (potato.oracle.com) 8> EDIT PARAM
capdb1
—配置进程参数
EXTRACT capdb1
SETENV (ORACLE_HOME =
“/u01/app/oracle/product/11.2.0/dbhome_1”)
SETENV (ORACLE_SID = DB1)
USERID goldengate, PASSWORD goldengate
EXTTRAIL /oracle/ggs/dirdat/lt
TABLE lala.*
添加配置pump进程
GGSCI (potato.oracle.com) 9> ADD EXTRACT
pumpdb12, EXTTRAILSOURCE /oracle/ggs/dirdat/lt, BEGIN NOW
EXTRACT added.
GGSCI (potato.oracle.com) 10> ADD
RMTTRAIL /oracle/ggs/dirdat/rt, EXTRACT pumpdb12
RMTTRAIL added.
GGSCI (potato.oracle.com) 11> EDIT PARAM
pumpdb12
EXTRACT pumpdb12
SETENV (ORACLE_HOME =
“/u01/app/oracle/product/11.2.0/dbhome_1”)
SETENV (ORACLE_SID = DB1)
USERID goldengate, PASSWORD goldengate
RMTHOST tomato, MGRPORT 7809
rmttrail /oracle/ggs/dirdat/rt
TABLE lala.*
将配置好的两个进程开启
GGSCI (potato.oracle.com) 49> start
capdb1
Sending START request to MANAGER …
EXTRACT CAPDB1 starting
GGSCI (potato.oracle.com) 50> start
pumpdb12
Sending START request to MANAGER …
EXTRACT PUMPDB12 starting
GGSCI (potato.oracle.com) 51> info all
Program Status Group Lag at Chkpt Time
Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING CAPDB1 00:38:04 00:00:01
EXTRACT RUNNING PUMPDB12 00:00:00 00:00:00
3.配置源端
添加进程REPLICAT进程
GGSCI (tomato.oracle.com) 10> dblogin
userid goldengate , password goldengate —首先先要登录
Successfully logged into database.
GGSCI (tomato.oracle.com) 11> ADD
CHECKPOINTTABLE goldengate.checkpoint —添加检查点表
Successfully created checkpoint table
goldengate.checkpoint.
GGSCI (tomato.oracle.com) 12> ADD
REPLICAT repdb2, EXTTRAIL /oracle/ggs/dirdat/rt, BEGIN NOW, CHECKPOINTTABLE
goldengate.checkpoint
REPLICAT added.
—添加REPLICAT进程，并且使用CHECKPOINTTABLE方式
GGSCI (tomato.oracle.com) 13> EDIT PARAM
repdb2
—配置REPLICAT进程
REPLICAT repdb2
SETENV (ORACLE_HOME =
“/u01/app/oracle/product/11.2.0/dbhome_1”)
SETENV (ORACLE_SID = DB2)
ASSUMETARGETDEFS
USERID goldengate, PASSWORD goldengate
MAP lala.*, TARGET lala.*
开启该进程
GGSCI (tomato.oracle.com) 14> start
repdb2
Sending START request to MANAGER …
REPLICAT REPDB2 starting
GGSCI (tomato.oracle.com) 15> info all
Program Status Group Lag at Chkpt Time
Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPDB2 00:00:00 00:00:03
四、进行环境测试
源端插入一条数据
SQL> insert into student
values(1,’zhangsan’);
1 row created.
SQL> commit
Commit complete.
目标端查看得到这条数据
GGSCI (tomato.oracle.com) 18> stats
repdb2
Sending STATS request to REPLICAT REPDB2
…
Start of Statistics at 2017-03-06 23:49:04.
Replicating from LALA.STUDENT to
LALA.STUDENT:
*** Total statistics since 2017-03-06
21:42:56 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2017-03-06
21:42:56 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2017-03-06
23:00:00 ***
No database operations have been performed.
*** Latest statistics since 2017-03-06
21:42:56 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
SQL> select * from student;
ID NAME
———- —————
1 zhangsan
环境搭建成功 更多内容请阅读：什么是DOCKER
