【Oracle】OGG(Oracle GoldenGate)简介及搭建过程
GoldenGate公司简介
GoldenGate公司专注于数据同步领域,是实现数据同步技术的领导者。至2007年,在全球35个国家售出超过2000个许可证,客户分布在政府、银行、电信、证券、传媒、医疗等行业,大部分客户为全球500强企业,如中国海关总署、中国国家体育总局体彩管理中心、中国电子口岸、海南移动、美国银行、VISA、瑞银集团、澳大利亚海关、新加坡港务局等。GoldenGate是许多一流的数据库厂商如Oracle、Sybase、Microsoft、MySQL、Teradata等公司的认证合作伙伴,并且和著名的公司如HP、IBM、Sun等厂商建立了战略合作伙伴关系。
2009年被甲骨文Oracle公司收购。
GoldenGate产品介绍
GoldenGate公司的领先技术就是逐渐为大家所知的交易数据管理Transactional Data Management (TDM),可以在异构的IT基础结构之间实现大量数据的秒一级的数据捕捉、转换和投递。GoldenGate可以支持几乎所有常用操作系统和数据库平台,如下表所示:
Databases |
O/S and Platforms |
Oracle DB2 OS/390 DB2 UDB Microsoft SQL Server Sybase Enscribe SQL/MX Teradata MySQL, Ingres any ODBC compatible databases |
Windows 2000, 2003, XP Linux Sun Solaris HP-UX IBM AIX HP NonStop TRU64 IBM z/OS, LUW |
GoldenGate软件提供了一个单一的平台,这个平台可以为任何企业环境实现秒一级的灾难备份。GoldenGate是一种基于日志的结构化数据复制方式,它通过解析源数据库在线日志或归档日志获得数据的增删改变化(数据量只有日志的四分之一左右),再将这些变化应用到目标数据库,实现源数据库与目标数据
库同步、双活。
如上图所示,GoldenGate TDM的数据复制过程如下:
1、 利用捕捉进程(Extract Process)在源系统端读取Online Redo Log或Archive Log,然后进行解析,只提取其中数据的变化如增、删、改操作,并将相关信息转换为GoldenGate TDM自定义的中间格式存放在队列文件(trail file)中。再利用传送进程将队列文件通过TCP/IP传送到目标系统。捕捉进程在每次读完log中的数据变化并在数据传送到目标系统后,会写检查点,记录当前完成捕捉的log位置,检查点的存在可以使捕捉进程在中止开恢复后可从检查点位置继续复制。
2、目标系统接受数据变化开缓存到GoldenGate TDM队列当中,队列为系列临时存储数据变化的文件,等待投递迚程读取数据。
3、GoldenGate TDM投递过程(replicat process)从队列中读取数据变化并创建对应的SQL语句,通过数据库的本地接口执行,提交到数据库成功后更新自己的检查点,记录已经完成复制的位置,数据的复制过程最终完成。
由此可见,GoldenGate TDM是一种基于软件的数据复制方式,它从数据库的日志解析数据的变化(数据量只有日志的四分之一左右)。GoldenGate TDM将数据变化转化为自己的格式,直接通过TCP/IP网络传输,无需依赖于数据库自身的传递方式,而且可以通过高达10:1的压缩率对数据迚行压缩,可以大大降低带宽需求。在目标端,GoldenGate TDM可以通过交易重组,分批加载等技术手段大大加快数据投递的速度和效率,降低目标系统的资源占用,可以在亚秒级实现大量数据的复制,并且目标端数据库是活动的。
GoldenGate安装步骤
操作系统:OEL 5.6
Oracle 版本:11.2.0.4.0
DATABASE_ROLE |
DB_NAME |
IPADDR |
源端 |
drz |
192.168.10.111 |
目标端 |
lgr |
192.168.10.222 |
OGG安装目录:/u01/app/ogg/ |
注:配置过程需在数据库开启状态下进行,否则会报错
1. 安装前准备工作
1)创建安装目录
①源端:
[oracle@drz ~]$ mkdir -p /u01/app/ogg
②目标端:
[oracle@lgr ~]$ mkdir -p /u01/app/ogg
2)上传安装介质并解压缩
①源端、目标端均上传安装介质,步骤略...
②源端解压:
[root@drz ~]# su - oracle
[oracle@drz ~]$ cd /u01/app/ogg
[oracle@drz ogg]$ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[oracle@drz ogg]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
③目标端解压:
[root@lgr ~]# su - oracle
[oracle@lgr ~]$ cd /u01/app/ogg
[oracle@lgr ogg]$ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[oracle@lgr ogg]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
3)配置环境变量
源端、目标端均需要增加如下信息
vi ~/.bash_profile
export GG_HOME=/u01/app/ogg
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$GG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
alias ggsci='cd $GG_HOME;ggsci'
2.源端数据库配置
1)打开强制生成日志
SQL> SELECT FORCE_LOGGING FROM V$DATABASE;
FOR
---
NO
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> SELECT FORCE_LOGGING FROM V$DATABASE;
FOR
---
YES
2)数据库处于归档模式
SQL> ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch1/drz/
Oldest online log sequence 33
Next log sequence to archive 35
Current log sequence 35
##如何数据库未启用归档,请使用如下命令:
alter database archivelog;
alter database open;
3)打开补充日志
##ORACLE一般是用ROWID来唯一标示一行记录,但GoldenGate需要主键等其他标示,所以要开附加日志。
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
YES
4)修改参数
##这个参数在 11.2.0.4和 12.1.0.2以后才出现。目的是为了更好的监视你使用 OGG,所以把ogg 绑定到DB中,只有设置了改参数为true,才能使用OGG 的一些功能。
SQL> SHOW PARAMETER ENABLE_GOLDENGATE
NAME TYPE VALUE
------------------------------------ -------- -------
enable_goldengate_replication boolean FALSE
SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE;
System altered.
SQL> SHOW PARAMETER ENABLE_GOLDENGATE
NAME TYPE VALUE
------------------------------------ -------- -------
enable_goldengate_replication boolean FALSE
5)创建GoldenGate 数据库用户
①创建一个专属于OGG的表空间
SQL> CREATE TABLESPACE ts_ogg DATAFILE'/u01/app/oracle/oradata/drz/ts_ogg.dbf'SIZE 50M;
Tablespace created.
SQL> CREATE USER ogg IDENTIFIED BY oracle DEFAULT TABLESPACE ts_ogg;
User created.
②授予用户如下权限
grant resource to ogg;
grant create session, alter session to ogg;
grant select any dictionary to ogg;
grant flashback any table to ogg;
grant alter any table to ogg;
grant select any table to ogg;
grant execute on dbms_flashback to ogg;
6)准备测试数据
①建立用户并授予基本权限
SQL> CREATE USER lgr IDENTIFIED BY oracle;
User created.
SQL> GRANT CONNECT,RESOURCE TO lgr;
Grant succeeded.
SQL> GRANT SELECT ON SCOTT.emp TO lgr;
Grant succeeded.
SQL> GRANT SELECT ON SCOTT.DEPT TO LGR;
Grant succeeded.
②创建测试表,并增加主键
SQL> create table mydept as select * from scott.dept;
Table created.
SQL> create table myemp as select * from scott.emp;
Table created.
SQL> alter table mydept add primary key(deptno);
Table altered.
SQL> alter table myemp add primary key(empno);
Table altered.
③导出数据,用于目标端
[oracle@drz ~]$ exp lgr/oracle@drz file=/home/oracle/lgr.dmp tables=mydept,myemp rows=y
Export: Release 11.2.0.4.0 - Production on Sun Jan 8 19:01:22 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table MYDEPT 4 rows exported
. . exporting table MYEMP 14 rows exported
Export terminated successfully without warnings.
3.目标端数据库配置
1)将源端的dmp文件scp到目标端
[oracle@drz ~]$ scp lgr.dmp 192.168.10.222:/home/oracle/.
oracle@192.168.10.222's password:
lgr.dmp 100% 16KB 16.0KB/s 00:00
[oracle@drz ~]$
2)目标端创建用户并授予权限
SQL> create user lgr identified by oracle;
User created.
SQL> grant connect,resource to lgr;
Grant succeeded.
3)导入铺底数据
[oracle@lgr ~]$ imp lgr/oracle file=/home/oracle/lgr.dmp full=y
Import: Release 11.2.0.4.0 - Production on Sun Jan 8 19:13:14 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Prod
uctionWith the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses AL32UTF8 character set (possible charset conversion)
. importing LGR's objects into LGR
. importing LGR's objects into LGR
. . importing table "MYDEPT" 4 rows imported
. . importing table "MYEMP" 14 rows imported
Import terminated successfully without warnings.
4)验证铺底数据
SQL> conn lgr/oracle
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
MYDEPT TABLE
MYEMP TABLE
SQL> select count(*) from mydept;
COUNT(*)
----------
4
SQL> select count(*) from myemp;
COUNT(*)
----------
14
5)创建GoldenGate 数据库用户
①创建一个ogg用户专属表空间(非必须)
SQL> create tablespace ts_ogg datafile'/u01/app/oracle/oradata/lgr/ts_ogg.dbf'size 200M;
Tablespace created.
SQL> create user ogg identified by oracle default tablespace ts_ogg;
User created.
②授予ogg用户相应的权限
grant resource to ogg;
grant create session, alter session to ogg;
grant select any dictionary to ogg;
grant flashback any table to ogg;
grant alter any table to ogg;
grant select any table to ogg;
grant execute on dbms_flashback to ogg;
GRANT INSERT ANY TABLE TO ogg;
GRANT UPDATE ANY TABLE TO ogg;
GRANT DELETE ANY TABLE TO ogg;
4.OGG配置
1)源端
必须在ogg的home目录下启动ggsci,只有这样才能把子目录建在home目录下。如果不在相应位置建立子目录,在后面编辑参数文件时会报错。
[oracle@drz ~]$ cd $GG_HOME
[oracle@drz ogg]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (drz) 1> create subdirs
Creating subdirectories under current directory /u01/app/ogg
Parameter files /u01/app/ogg/dirprm: already exists
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
Database definitions files /u01/app/ogg/dirdef: created
Extract data files /u01/app/ogg/dirdat: created
Temporary files /u01/app/ogg/dirtmp: created
Stdout files /u01/app/ogg/dirout: created
GGSCI (drz) 2> edit param mgr
----添加端口----
port7809
GGSCI (drz) 3> start mgr
Manager started.
GGSCI (drz) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (drz) 5> dblogin userid ogg,password oracle
Successfully logged into database.
GGSCI (drz) 6> add trandata lgr.*
Logging of supplemental redo data enabled for table LGR.MYDEPT.
Logging of supplemental redo data enabled for table LGR.MYEMP.
GGSCI (drz) 7> add extract exta,tranlog,begin now
EXTRACT added.
GGSCI (drz) 8> add exttrail ./dirdat/ra,extract exta
EXTTRAIL added.
GGSCI (drz) 9> edit param exta
----添加如下内容----
extract exta
userid ogg,password oracle
setenv NLS_LANG = (AMERICAN_AMERICA.AL32UTF8)
exttrail ./dirdat/ra
dynamicresolution
gettruncates
Table lgr.*;
##源端数据库配置投递过程
GGSCI (drz) 10> add extract dp1,exttrailsource ./dirdat/ra
EXTRACT added.
GGSCI (drz) 11> edit param dp1
----添加如下内容----
extract dp1
userid ogg,password oracle
setenv (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)
passthru
rmthost 192.168.10.222,mgrport 7809
rmttrail ./dirdat/ra
table lgr.*;
GGSCI (drz) 12> add rmttrail ./dirdat/ra,extract dp1
RMTTRAIL added.
GGSCI (drz) 13> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DP1 00:00:00 00:02:58
EXTRACT STOPPED EXTA 00:00:00 00:08:45
GGSCI (drz) 14> start dp1
Sending START request to MANAGER ...
EXTRACT DP1 starting
GGSCI (drz) 15> start exta
Sending START request to MANAGER ...
EXTRACT EXTA starting
GGSCI (drz) 16> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:03:47
EXTRACT RUNNING EXTA 00:00:00 00:00:06
2)目标端
[oracle@lgr ~]$ cd $GG_HOME
[oracle@lgr ogg]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (lgr) 1> create subdirs
Creating subdirectories under current directory /u01/app/ogg
Parameter files /u01/app/ogg/dirprm: already exists
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
Database definitions files /u01/app/ogg/dirdef: created
Extract data files /u01/app/ogg/dirdat: created
Temporary files /u01/app/ogg/dirtmp: created
Stdout files /u01/app/ogg/dirout: created
GGSCI (lgr) 2> edit param mgr
----添加如下内容----
port 7809
GGSCI (lgr) 3> start mgr
Manager started.
GGSCI (lgr) 4> edit params ./globals
----添加如下内容----
checkpointtable ogg.checkpoint
GGSCI (lgr) 5> dblogin userid ogg,password oracle
Successfully logged into database.
GGSCI (lgr) 6> add checkpointtable ogg.checkpoint
Successfully created checkpoint table ogg.checkpoint.
GGSCI (lgr) 7> add replicat repa,exttrail ./dirdat/ra,checkpointtable ogg.checkpoint
REPLICAT added.
GGSCI (lgr) 8> edit param repa
----添加如下内容----
replicat repa
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg,password oracle
numfiles 500
grouptransops 10000
handlecollisions
assumetargetdefs
allownoopupdates
dynamicresolution
discardfile ./dirrpt/repsa_discard.txt, append, megabytes 10
map lgr.*,target lgr.*;
GGSCI (lgr) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REPA 00:00:00 00:03:38
GGSCI (lgr) 10> start repa
Sending START request to MANAGER ...
REPLICAT REPA starting
GGSCI (lgr) 11> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPA 00:00:00 00:00:04
5.验证效果
1)源端中向mydept表中插入一条数据
SQL> insert into mydept values(50,'IT','BeiJing');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from mydept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 IT BeiJing
2)目标端数据库查询表mydept,验证插入数据
SQL> select * from mydept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 IT BeiJing
至此,GoldenGate配置完成!
总结:OGG配置基本配置过程包括:操作系统层面前期准备、源端目标端数据库配置、源端中mgr/extract/pump进程配置、目标端中mgr/replicat进程配置。配置过程要保持数据库开启状态,否则ggsci中用户不能登录。
最新文章
- SortedMap基本特性
- java list排序
- 浅谈CSRF攻击方式
- zookeeper 故障重连机制
- NDK-JNI实战教程(二) JNI官方中文资料
- iOS开发中代理使用出现的问题解决
- lucene 总结收集(url)
- php创建多级目录完整封装类操作
- PostgresSQL中的限制和级联删除
- OpenStack_I版 3.glance部署
- [高级软件工程教学]团队Alpha阶段成绩汇总
- java 单元测试教程(junit)
- mySql入门-(一)
- java操作elasticsearch实现条件查询(match、multiMatch、term、terms、reange)
- 剑指offer题目java实现
- Atom与markdown
- Centos7上安装及配置Apache
- react-native 相对项目路径导入组件
- Linux初学时的一些常用命令(1)
- jenkin 不必要的Execute shell执行失败,导致jenkins都失败的解决
热门文章
- android 数据存储之SQLite
- [APIO2014] [Uoj103] [Bzoj3676] Palindromes回文串 [Manacher,后缀数组]
- [luogu1373]小a和uim之大逃离_动态规划
- [转]数据库查询 sysobjects
- HTML5:控件自动获得焦点
- 2014腾讯实习生笔试题——define与typedef
- 最全Linux 与 Linux Windows 文件共享
- 好记性不如烂笔头——DML/DDL/DCL/TCL,OLTP/OLAP
- pat1013:数素数
- [RK3288][Android6.0] 调试笔记 --- 普通串口的添加 【转】