您的位置：首页 > 博客中心 > 数据库 >

DBA_Oracle GoldenGate安装详解（案例）

时间：2022-03-13 23:39

2014-08-31 BaoXinjian

一、摘要

Oracle GoldenGate软件基于数据库日志结构变化，通过解析源端在线日志或归档日志获得数据增量，再将这些变化应用到目标数据库，从而实现源库和目标库的数据同步。

下面通过一个简单的示例，详细介绍利用GoldenGate实现Oracle数据库之间的同步。

基本架构如下图所示：

gxlsystem.com,布布扣

二、安装

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

# passwd ggate

$ mkdir /opt/oracle/ggate
$ cd /opt/oracle/ggate
$ tar -xvf

注意，如果使用Oracle 11g的数据库，需要创建一个link文件。

$ ln -s /u01/app/oracle/product/11.2.0/db_1/lib/libnnz11.so -
/u01/app/oracle/product/11.2.0/db_1/lib/libnnz10.so

$ vi ~/.bash_profile

添加如下的内容：

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/ggate
export GGATE=/u01/app/oracle/ggate

1.3 创建目录

使用ggsci工具，创建必要的目录。

$ cd /u01/app/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. 此部分需要在源端和目标端完成。

三、配置源数据端

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 创建源端和目标端的测试用户

source

SQL> create user sender identified by oracle default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to sender;

destination

SQL> create user receiver identified by oracle default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to receiver;

2 配置manager

在源端和目标端分别执行下面的步骤。

2.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.

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 , 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

四、配置目标端

1. 配置目标端同步队列

1.1 在目标端添加checkpoint表

[oracle@centos4 ggate]$ ./ggsci

GGSCI (centos4) 1> edit params ./GLOBAL –添加下列内容

GGSCHEMA ggate
CHECKPOINTTABLE ggate.checkpoint

GGSCI (centos4) 2> dblogin userid
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 , password oracle
discardfile /opt/oracle/ggate/dirdat/rep1_discard.txt, append, megabytes 10
DDL
map sender.*, target receiver.*;

2. 开启同步

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

3. 验证结果

源端：

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

GGSCI (gavinprod.com) 9> view report EXT1

***********************************************************************
                 Oracle GoldenGate Capture 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:42:16

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

                    Starting at 2014-09-10 02:24:08
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Mon Nov 12 02:14:55 EST 2007, Release 2.6.18-53.el5
Node: gavinprod.com
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 4793

Description:

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************

2014-09-10 02:24:08 INFO    OGG-03035 Operating system character set identifie
d as UTF-8. Locale: en_US, LC_ALL:.
extract ext1
userid ggate@gavinprod, password ******
rmthost centos5, mgrport 7809
rmttrail /opt/oracle/ggate/dirdat/lt
ddl include mapped objname sender.*;
table sender.*;

2014-09-10 02:24:19 INFO    OGG-01635 BOUNDED RECOVERY: reset to initial or al
tered checkpoint.

2014-09-10 02:24:19 INFO    OGG-01815 Virtual Memory Facilities for: BR
    anon alloc: mmap(MAP_ANON) anon free: munmap
    file alloc: mmap(MAP_SHARED) file free: munmap
    target directories:
    /opt/oracle/ggate/BR/EXT1.

Bounded Recovery Parameter:
Options    = BRRESET
BRINTERVAL = 4HOURS
BRDIR      = /opt/oracle/ggate

2014-09-10 02:24:19 INFO    OGG-01815 Virtual Memory Facilities for: COM
    anon alloc: mmap(MAP_ANON) anon free: munmap
    file alloc: mmap(MAP_SHARED) file free: munmap
    target directories:
    /opt/oracle/ggate/dirtmp.

CACHEMGR virtual memory values (may have been adjusted)
CACHESIZE:                               64G
CACHEPAGEOUTSIZE (normal):                8M
PROCESS VM AVAIL FROM OS (min):         128G
CACHESIZEMAX (strict force to disk):     96G

Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Database Language and Character Set:
NLS_LANG         = ".AL32UTF8"
NLS_LANGUAGE     = "AMERICAN"
NLS_TERRITORY    = "AMERICA"
NLS_CHARACTERSET = "AL32UTF8"

2014-09-10 02:24:20 WARNING OGG-01423 No valid default archive log destination
directory found for thread 1.

2014-09-10 02:24:21 INFO    OGG-01515 Positioning to begin time Sep 10, 2014 1
:27:40 AM.

2014-09-10 02:24:21 INFO    OGG-01516 Positioned to Sequence 11, RBA 32997376,
SCN 0.0, Sep 10, 2014 1:27:40 AM.

2014-09-10 02:24:37 INFO    OGG-01226 Socket buffer size set to 27985 (flush s
ize 27985).

2014-09-10 02:24:37 INFO    OGG-01052 No recovery is required for target file
/opt/oracle/ggate/dirdat/lt000000, at RBA 0 (file not opened).

2014-09-10 02:24:37 INFO    OGG-01478 Output file /opt/oracle/ggate/dirdat/lt
is using format RELEASE 11.2.

***********************************************************************
**                     Run Time Messages                             **
***********************************************************************

2014-09-10 02:24:37 INFO    OGG-01517 Position of first record processed Seque
nce 11, RBA 32997392, SCN 0.1148088, Sep 10, 2014 1:27:41 AM.

2014-09-10 02:24:38 INFO    OGG-00732 Found crash recovery marker from thread
#1 on sequence 11 at RBA 33305616. Aborting uncommitted transactions.

2014-09-10 02:24:38 INFO    OGG-00732 Found crash recovery marker from thread
#1 on sequence 12 at RBA 1040. Aborting uncommitted transactions.