Oracle GoldenGate安装配置

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;

    }