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使用者環境（源庫、目标庫都一樣設定）

[[email protected] ~]$ 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） 軟體安裝(源端和目标端相同的操作)

[[email protected] ogg11]$ unzipogg112101_fbo_ggs_Linux_x86_ora10g_32bit.zip

[[email protected] ogg11]$ tar -xvffbo_ggs_Linux_x86_ora10g_32bit.tar

[[email protected] 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

[[email protected] 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使用者下執行)

 源庫操作：

        [[email protected] 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;

        這一步的目的是先清空資料

[[email protected] ~]$ cd $ORA_OGG_HOME  \\先要到ORA_OGG_HOME下，否則下面會出錯

[[email protected] 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程序

[[email protected] 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資料,看目标端是否和源端一緻，一緻表示複制正常.