一、摘要
Oracle GoldenGate软件基于数据库日志结构变化,通过解析源端在线日志或归档日志获得数据增量,再将这些变化应用到目标数据库,从而实现源库和目标库的 数据同步。
下面通过一个简单的示例,详细介绍利用GoldenGate实现Oracle数据库之间的同步。
基本架构如下图所示:
二、安装
1.1 下载介质
GoldenGate的安装介质可以从Oracle的官网上下载。
http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html
1.2 配置GoldenGate用户
(1). 下载完成后将其拷贝到源和目标的相应位置解压完成后,即可以开始进行配置。
# useradd -g oinstall -G dba ggate # su – ggate # passwd ggate $ mkdir /opt/oracle/ggate $ cd /opt/oracle/ggate $ tar -xvf 解压包
(2). 修改bash_profile
$ vi ~/.bash_profile 添加如下的内容: export ORACLE_BASE=opt/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/gavinprod export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/opt/oracle/ggate export GGATE=/opt/oracle/ggate
1.3 创建目录
使用ggsci工具,创建必要的目录。
$ cd /opt/oracle/ggate $ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.1.1.0.0 Build 078 Linux, x86, 32bit (optimized), Oracle 10 on Jul 28 2010 13:24:18 Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved. GGSCI (gridcontrol) 1> create subdirs
至此,GoldenGate基本的安装完成。
Note. 此部分需要在源端和目标端完成。
三、配置OGG源端和目标端,并测试
1、配置源数据端
GoldenGate主要通过抓取源端数据库重做日志进行分析,将获取的数据应用到目标端,实现数据同步。因此,为了让GoldenGate能够正常工作,源数据库需要进行一定配置。
1.1 设置源库为归档模式
SQL> shutdown immediate SQL> startup mount SQL> alter database archivelog; SQL> alter database open;
1.2 开启minimal supplemental logging
SQL> alter database add supplemental log data; SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database; SUPPLEME ——– YES
1.3 关闭数据库的recyblebin
SQL> alter system set recyclebin=off scope=spfile;
如果数据库是10g,需要关闭recyclebin并重启;或者手工purge recyclebin。
1.4 配置复制的DDL支持
SQL> create user ggate identified by ggate default tablespace users temporary tablespace temp; SQL> grant connect,resource,unlimited tablespace to ggate; SQL> grant execute on utl_file to ggate; SQL> @$GGATE/marker_setup.sql; SQL> @$GGATE/ddl_setup.sql; SQL> @$GGATE/role_setup.sql; SQL> grant GGS_GGSUSER_ROLE to ggate; SQL> @$GGATE/ddl_enable.sql;
1.5 创建源端和目标端的测试用户
(1). source
SQL> create user sender identified by oracle default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to sender;
(2). destination
SQL> create user receiver identified by oracle default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to receiver;
2. 配置远端和目标端的manager
在源端和目标端分别执行下面的步骤。
1. 创建manager
[ggate@gridcontrol gg]$ ./ggsci GGSCI (gridcontrol) 1> info all Program Status Group Lag Time Since Chkpt MANAGER STOPPED GGSCI (gridcontrol) 2> edit params mgr PORT 7809 GGSCI (gridcontrol) 3> start manager Manager started.
3. 配置源端复制队列
复制队列配置
GGSCI (gridcontrol) 1> add extract ext1, tranlog, begin now EXTRACT added. GGSCI (gridcontrol) 2> add exttrail /opt/oracle/ggate/dirdat/lt, extract ext1 EXTTRAIL added. GGSCI (gridcontrol) 3> edit params ext1 extract ext1 userid ggate@gavinprod, password oracle rmthost centos4, mgrport 7809 rmttrail /u01/app/oracle/ggate/dirdat/lt ddl include mapped objname sender.*; table sender.*; GGSCI (gridcontrol) 6> info all Program Status Group Lag Time Since Chkpt MANAGER STOPPED EXTRACT STOPPED EXT1 00:00:00 00:10:55
4. 配置目标端同步对队列
1. 配置目标端同步队列
1.1 在目标端添加checkpoint表
[oracle@centos4 ggate]$ ./ggsci GGSCI (centos4) 1> edit params ./GLOBAL –添加下列内容 GGSCHEMA ggate CHECKPOINTTABLE ggate.checkpoint GGSCI (centos4) 2> dblogin userid ggate@target Password: Successfully logged into database. GGSCI (centos4) 3> add checkpointtable ggate.checkpoint Successfully created checkpoint table GGATE.CHECKPOINT.
1.2 创建同步队列
GGSCI (centos4) 4> add replicat rep1, exttrail /opt/oracle/ggate/dirdat/lt, checkpointtable ggate.checkpoint REPLICAT added. GGSCI (centos4) 5> edit params rep1 replicat rep1 ASSUMETARGETDEFS userid ggate@gavinsit, password oracle discardfile /opt/oracle/ggate/dirdat/rep1_discard.txt, append, megabytes 10 DDL map sender.*, target receiver.*;
5. 开始同步并测试
1. 开启同步
GGSCI (gridcontrol) 14> start extract ext1 GGSCI (gridcontrol) 15> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXT1 00:00:00 00:00:05 GGSCI (centos4) 7> start replicat rep1 GGSCI (centos4) 8> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REP1 00:00:00 00:00:00
2. 验证结果
源端:
SQL> create table sender.test_tab_1 (id number,rnd_str varchar2(12)); SQL> insert into sender.test_tab_1 values (1,’test_1′); SQL> commit;
目标端:
SQL> select * from receiver.test_tab_1; ID RND_STR ———- ———— 1 test_1