Oracle GoldenGate软件基于数据库日志结构变化,通过解析源端在线日志或归档日志获得数据增量,再将这些变化应用到目标数据库,从而实现源库和目标库的数据同步。下面通过一个简单的示例,详细介绍利用GoldenGate实现Oracle数据库之间的同步。基本架构如下图所示:
1. 安装
1.1 下载介质
GoldenGate的安装介质可以从Oracle的官网上下载。
http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html
1.2 配置GoldenGate用户
下载完成后将其拷贝到源和目标的相应位置解压完成后,即可以开始进行配置。
# useradd -g oinstall -G dba ggate
# su – ggate
$ mkdir /oracle/ggate
$ cd /oracle/ggate
$ unzip ……
$tar ……
$ vi ~/.bash_profile
添加如下的内容:
export ORACLE_BASE=/oracle
export ORACLE_HOME=$ORACLE_BASE/db
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/oracle/ggate
export GGATE=/oracle/ggate
1.3 创建目录
使用ggsci工具,创建必要的目录。
$ cd /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 282010 13:24:18
Copyright (C) 1995, 2010, Oracle and/or itsaffiliates. All rights reserved.
GGSCI (gridcontrol) 1> create subdirs
至此,GoldenGate基本的安装完成。
注意: 此部分需要在源端和目标端完成。
2. 源数据库配置
GoldenGate主要通过抓取源端数据库重做日志进行分析,将获取的数据应用到目标端,实现数据同步。因此,为了让GoldenGate能够正常工作,源数据库需要进行一定配置。
2.1 设置源库为归档模式
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
2.2 开启minimal supplemental logging
SQL> alter database add supplemental log data;
SQL> select SUPPLEMENTAL_LOG_DATA_MIN fromv$database;
SUPPLEME
——–
YES
2.3 关闭数据库的recyblebin
SQL> alter system set recyclebin=off scope=spfile;
如果数据库是10g,需要关闭recyclebin并重启;或者手工purge recyclebin。
2.4 配置复制的DDL支持
SQL> create user ggate identified by ggate defaulttablespace users temporary tablespace temp;
SQL> grant connect,resource,dba,unlimitedtablespace 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;
2.5 创建源端和目标端的测试用户
source
SQL> create user sender identified by oracledefault tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace tosender;
destination
SQL> create user ggate identified by ggate defaulttablespace users temporary tablespace temp;
SQL> grant connect,resource,dba,unlimitedtablespace to ggate;
SQL> create user receiver identified by oracledefault tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace toreceiver;
3. 配置manager
在源端和目标端分别执行下面的步骤。
3.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
ggate (gridcontrol) 3> start manager
Manager started.
4. 配置源端复制队列
GGSCI (gridcontrol) 1> add extract ext1, tranlog,begin now
EXTRACT added.
GGSCI (gridcontrol) 2> add exttrail/Oracle/ggate/dirdat/lt, extract ext1
EXTTRAIL added.
GGSCI (gridcontrol) 3> edit params ext1
extract ext1
userid ggate@testdg, password ggate
tranlogoptions ASMUSER sys@+ASM,ASMPASSWORD 123456 (服务器用ASM增加此项,另listener.ora和tnsname.ora增加ASM,示例最后)
rmthost CentOS4, mgrport 7809
rmttrail /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
5. 配置目标端同步队列
5.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 checkpointtableggate.checkpoint
Successfully created checkpoint tableGGATE.CHECKPOINT.
5.2 创建同步队列
GGSCI (centos4) 4> add replicat rep1, exttrail/u01/app/oracle/ggate/dirdat/lt, checkpointtable ggate.checkpoint
REPLICAT added.
GGSCI (centos4) 5> edit params rep1
replicat rep1
HANDLECOLLISIONS
ASSUMETARGETDEFS
userid ggate@testdg2, password ggatetranlogoptionsASMUSER sys@+ASM,ASMPASSWORD 123456 (客户端用ASM增加此项,另listener.ora和tnsname.ora增加ASM,示例如后)
discardfile /oracle/ggate/dirdat/rep1_discard.txt,append, megabytes 10
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP
map sender.*, target receiver.*;
discardfile /oracle/ggate/dirdat/rep1_discard.txt,append, megabytes 10
DDL
map sender.*, target receiver.*;
6. 开启同步
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
7. 验证结果
源端:
SQL> create table sender.test_tab_1 (idnumber,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
listener.ora:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION=
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL = TCP)(HOST = Oracledg)(PORT = 1521))
(ADDRESS= (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
)
ADR_BASE_LISTENER = /oracle
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = testdg1)
(ORACLE_HOME = /oracle/db)
(SID_NAME= tang)
)
(SID_DESC =
(GLOBAL_DBNAME = +ASM)
(ORACLE_HOME = /oracle/db)
(SID_NAME= +ASM)
)
)
tnsname.ora:
+ASM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =(PROTOCOL = TCP)(HOST = oracledg)(PORT = 1521))
)
(CONNECT_DATA=
(SERVICE_NAME = +ASM)
)
)
TESTDG1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =(PROTOCOL = TCP)(HOST = 10.60.60.35)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdg1)
)
)
run {
allocatechannel c1 device type disk;
allocatechannel c2 device type disk;
backupincremental level 0
format'/oracle/cad/db_full_%U.bkp'
tag'2012-08-2-FULL'
databaseplus archivelog;
release channelc1;
releasechannel c2;
}