Oracle GoldenGate 配置实践

操作系统： redhat  as   4.5

oracle version: 10.2.0.1

 

源端ip:          172.17.61.131          rhel131

目标端ip：     172.17.61.132          rhel132

 

1） 设置源库和目标库的ORACLE用户环境（源库、目标库都一样设置）

 

[oracle@rhel131 ~]$ cat .bash_profile

# .bash_profile

 

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

       . ~/.bashrc

fi

 

# User specific environment and startupprograms

 

PATH=$PATH:$HOME/bin

 

export PATH

unset USERNAME

 

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=$ORACLE_BASE/product/10201

ORA_OGG_HOME=$ORACLE_BASE/ogg11

ORACLE_SID=orcl

PATH=$HOME/bin:$ORACLE_HOME/bin:$ORA_OGG_HOME:$PATH:$ORACLE_HOME/OPatch

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:$ORA_GG_HOME

export ORACLE_BASE ORACLE_HOME ORACLE_SIDPATH LD_LIBRARY_PATH ORA_OGG_HOME

unset USERNAME

umask 022

 

 

2）  初始化源端数据库

 

a)         开启归档模式

GoldenGate是基于oracle日志变化的捕获，所以为了完整的捕获到oracle数据库的变化，有必要将归档模式开启。

 

SQL> alter system setlog_archive_dest_1='location=/u01/app/oracle/oradata/orcl/arch' scope=both;

 

SQL> shutdown immediate;

 

SQL> startup mount;

 

SQL> alter database open;

 

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/oradata/orcl/arch

Oldest online log sequence     1

Next log sequence to archive   2

Current log sequence           2

 

b)        开启附加日志

   我们知道，在oracle中我们可以通过rowid来定位某条记录，但是目标端的数据库和源端数据库的数据库可能完全不一样，所以无法通过rowid来确定源端数据库的逻辑变化，这时附件日志supplemental log便登上了表演的舞台。数据库在开启附加日志功能后，对于源端的修改操作，oracle会同时追加能够唯一标示记录的列到redo log。这样目标端数据库就可以知道源端发生了哪些具体的变化。

 

SQL>    alter database add supplemental log data;

    SQL>    alter system switch logfile;

   SQL>    select supplemental_log_data_min fromv$database;

 

c)         开启强制日志模式

 SQL>alter database force logging;

 

d)         为goldengate创建用户并授权

SQL> create tablespace tbs_oggdatafile

 2 '/u01/app/oracle/oradata/orcl/tbs_ogg01.dbf' size 100m autoextend on;

 

SQL> create user ogg identified byogg default tablespace tbs_ogg temporary tablespace temp quota unlimited ontbs_ogg;

 SQL>        grant connect,resource to ogg;

 SQL>        grant create session,alter session toogg;

 SQL>        grant select any dictionary,select anytable to ogg;

 SQL>        grant alter any table to ogg;

 SQL>        grant flashback any table to ogg;

 SQL>        grant execute on dbms_flashback to ogg;

 

 

3） 初始化目标端数据库

 

SQL> createtablespace tbs_ogg datafile

 2  '/u01/app/oracle/oradata/orcl/tbs_ogg01.dbf' size 100m autoextend on;

 

SQL> create userogg identified by ogg default tablespace tbs_ogg temporary tablespacetemp quota unlimited on tbs_ogg;

 

SQL>       grantconnect,resource to ogg;

  SQL>       grant create session,alter session toogg;

  SQL>       grant select any dictionary,select anytable to ogg;

  SQL>     grant alter any table to ogg;

  SQL>     grant flashback any table to ogg;

  SQL>       grant execute on dbms_flashback to ogg;

  SQL>       grant insert any table to ogg;

  SQL>       grant delete any table to ogg;

  SQL>       grant update any table to ogg;

 

 

4） 软件安装(源端和目标端相同的操作)

 

[oracle@rhel131 ogg11]$ unzipogg112101_fbo_ggs_Linux_x86_ora10g_32bit.zip

[oracle@rhel131 ogg11]$ tar -xvffbo_ggs_Linux_x86_ora10g_32bit.tar

 

[oracle@rhel131 ogg11]$ ggsci

 

Oracle GoldenGate Command Interpreter forOracle

Version 11.2.1.0.1OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x86, 32bit (optimized), Oracle 10gon Apr 23 2012 07:06:02

 

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

 

 

 

GGSCI (rhel131) 1> create subdirs

 

Creating subdirectories under currentdirectory /u01/app/oracle/ogg11

 

Parameter files                /u01/app/oracle/ogg11/dirprm:already exists

Report files                   /u01/app/oracle/ogg11/dirrpt: created

Checkpoint files               /u01/app/oracle/ogg11/dirchk:created

Process status files           /u01/app/oracle/ogg11/dirpcs:created

SQL script files               /u01/app/oracle/ogg11/dirsql:created

Database definitions files     /u01/app/oracle/ogg11/dirdef: created

Extract data files             /u01/app/oracle/ogg11/dirdat:created

Temporary files                /u01/app/oracle/ogg11/dirtmp:created

Stdout files                   /u01/app/oracle/ogg11/dirout:created

 

 

GGSCI (rhel131) 2> exit

[oracle@rhel131 ogg11]$

 

 

5）  初始化数据加载

a)        在源库和目标库上配置 GoldenGate 管理进程（源库、目标库都一样）

        cd $ORA_OGG_HOME
        ggsci
        help     --可以获得命令帮助
        帮助格式是： HELP <command> <object>


        GGSCI (rhel131) 1> edit params mgr  
            
 PORT7809
 PURGEOLDEXTRACTS/u01/app/oracle/ogg11/dirdat, USECHECKPOINTS, MINKEEPDAYS 10


        GGSCI (rhel131) 1> start mgr
            Manager started.

 

 

b)   在源端添加要初始化数据的用户和表(在oracle用户下执行)

 

 源库操作：
        [oracle@ rhel131gg11]$ ggsci        
        GGSCI (rhel131)1> dblogin userid ogg,password ogg
                Successfully logged into database.

        GGSCI (rhel131)3> add trandata scott.emp
                Logging of supplemental redo data enabledfor table SCOTT.EMP.

        GGSCI (rhel131)4> add trandata scott.dept
                Logging of supplemental redo data enabledfor table SCOTT.DEPT.

 

 

 

c)   在源端配置extract 进程

 

GGSCI (rhel131) 4> add extract eini_1,sourceistable;

EXTRACT added.

 

GGSCI (rhel131) 5> edit params eini_1

 

extract eini_1

setenv (nls_lang=AMERICAN_AMERICA.WE8ISO8859P1)

userid ogg, password ogg

rmthost 172.17.61.132,mgrport 7809

rmttask replicat, grouprini_1

table scott.dept;

table scott.emp;

~

 

d)       在目标端配置replicat 进程

      目标库上操作:

        SQL> showuser;
                USER is "SCOTT"

        SQL> deletefrom emp;  
        SQL> deletefrom dept;
        SQL> commit;
        
        这一步的目的是先清空数据

 

[oracle@rhel132 ~]$ cd $ORA_OGG_HOME  \\先要到ORA_OGG_HOME下，否则下面会出错

 

[oracle@rhel132 ogg11]$ ggsci

 

GGSCI (rhel132) 1> add replicat rini_1,specialrun

REPLICAT added.

 

GGSCI (rhel132) 2> info replicat *,tasks

 

REPLICAT  RINI_1    Initialized   2013-08-21 16:46   Status STOPPED

Checkpoint Lag       00:00:00 (updated 00:02:40 ago)

Log Read Checkpoint  Not Available

Task                 SPECIALRUN

 

 

GGSCI (rhel132) 3> edit params rini_1

 

replicat rini_1

setenv(nls_lang=AMERICAN_AMERICA.WE8ISO8859P1)

assumetargetdefs

userid ogg, password ogg

discardfile./dirrpt/riniaa.dsc, purge

map scott.*, targetscott.*;

 

e)   启动源端的extract进程

 

GGSCI (rhel131) 7> start extract eini_1

 

Sending START request to MANAGER ...

EXTRACT EINI_1 starting

 

 

GGSCI (rhel131) 9> info eini_1

 

EXTRACT   EINI_1    Last Started 2013-08-2116:53   Status STOPPED

Checkpoint Lag       Not Available

Log Read Checkpoint  Table SCOTT.EMP

                     2013-08-21 16:53:55  Record 14

Task                 SOURCEISTABLE

 

 

GGSCI (rhel131) 10> view report eini_1

 

….

Report at 2013-08-21 16:53:55 (activitysince 2013-08-21 16:53:48)

 

Output to rini_1:

 

From Table SCOTT.DEPT:

      #                   inserts:         4

      #                   updates:         0

      #                   deletes:         0

      #                  discards:         0

From Table SCOTT.EMP:

      #                   inserts:        14

      #                   updates:         0

       #                   deletes:         0

      #                  discards:         0

 

 

REDO Log Statistics

 Bytes parsed                    0

 Bytes output                 2984

 

 

之后再到目标数据库上去看下，看数据有没有初始化加载过来

 

SQL> select count(*) from scott.emp;

 

 COUNT(*)

----------

       14

 

SQL> select count(*) from scott.dept;

 

 COUNT(*)

----------

        4

 

发现这两张表已成功同步过来了。

 

 

6）  同步数据库数据

 

由于要同步scott.test1/test2，所以要目标和源都先建立这两张表：

 

Create table scott.test1 as select * fromscott.dmp where 1=2;

 

Create table scott.test2 as select * fromscott.dmp where 1=2;

 

 

a)                 1、在源端配置extrac进程

 

[oracle@rhel131 ogg11]$ ggsci

 

 

GGSCI (rhel131) 1> edit params eora_1

 

extract eora_1

setenv(oracle_sid=orcl,nls_lang=AMERICAN_AMERICA.WE8ISO8859P1)

userid ogg, password ogg

exttrail ./dirdat/aa

table scott.test1;

table scott.test2;

 

 GGSCI (rhel131) 2>add extract eora_1,tranlog, begin now

        GGSCI (rhel131) 3> info extract *

        GGSCI (rhel131) 4> add exttrail ./dirdat/aa, extract eora_1, megabytes5  

        GGSCI (rhel131) 2> info rmttrail *

                      Extract Trail:  ./dirdat/aa
                            Extract:  EORA_1
                             Seqno:  0
                               RBA:  0
                          File Size:  5M

        GGSCI (rhel131) 3> start extract eora_1
        GGSCI (rhel131)4>  info extract eora_1 或  info all
        GGSCI (rhel131) 19> info extracteora_1,detail  --看详情

 

b)        在源端配置pump进程

 

GGSCI (rhel131) 11> edit params pora_1

 

extract pora_1

setenv(nls_lang=AMERICAN_AMERICA.WE8ISO8859P1)

passthru

rmthost 172.17.61.132,mgrport 7809

rmttrail ./dirdat/pa

table scott.test1;

table scott.test2;

 

GGSCI (rhel131) 12> add extract pora_1,exttrailsource ./dirdat/aa

EXTRACT added.

 

GGSCI (rhel131) 13> info extract pora_1

 

EXTRACT    PORA_1    Initialized   2013-08-22 10:00   Status STOPPED

Checkpoint Lag       00:00:00(updated 00:00:20 ago)

Log Read Checkpoint  File./dirdat/aa000000

                     FirstRecord  RBA 0

 

 

GGSCI (rhel131) 14> add rmttrail./dirdat/pa, extract pora_1, megabytes 5  \\   --会在远程节点上创建目标库操作

 

RMTTRAIL added.

 

GGSCI (rhel131) 15>START  EXTRACT pora_1

 

c)         目标库操作

 

 

GGSCI (rhel132) 1> edit params ./GLOBALS

CHECKPOINTTABLE          ogg.ggschkpt

 

   GGSCI (rhel132) 2> quit    --要先退出，再重登        

 

 

   GGSCI (rhel132) 1> dbloginuserid ogg,password ogg

        GGSCI (rhel132) 2> add checkpointtable

        GGSCI (rhel132) 3> add replicat rora_1, exttrail ./dirdat/pa

        GGSCI (rhel132) 4> edit param rora_1

                replicatrora_1
                setenv(oracle_sid=orcl,nls_lang= AMERICAN_AMERICA.WE8ISO8859P1)
                userid ogg,password ogg
               handlecollisions
               assumetargetdefs
                discardfile./dirrpt/rora_aa.dsc, purge
                mapscott.test1, target scott.test1;
                mapscott.test2, target scott.test2;


        GGSCI (rhel132) 5> start replicat rora_1

        GGSCI (rhel132) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER    RUNNING

REPLICAT   RUNNING     RORA_1      00:00:00      00:00:07

        GGSCI (rhel132) 7> info replicatrora_1

REPLICAT   RORA_1    Last Started 2013-08-22 10:10   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:02 ago)

Log Read Checkpoint  File ./dirdat/pa000000

                     First Record  RBA 0

 

 

d)        测试数据是否可以正常同步

 

         在源端insert、update、delete数据,看目标端是否和源端一致，一致表示复制正常.