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