oracle ogg双向通步,使用ogg的Oracle-Oracle的双向复制

不使用数据泵完成Oracle-Oracle的双向复制。

操作系统：redhat  as   4.5

oracle version:10.2.0.1

源端ip:          172.17.61.131          rhel131

目标端ip：172.17.61.132          rhel132

目标：将scott.test1表从源端可以复制到目标端，并且目标端的数据更改也能同步到源端。

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_SID PATHLD_LIBRARY_PATHORA_OGG_HOME

unset USERNAME

umask 022

2)  初始化源端数据库(由于是双向同步，所以两台机器都要设置)

a)        开启归档模式

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 archivelog;

SQL> alter database open;

SQL>alter database add supplemental log data;

SQL>altersystem switch logfile;

SQL>selectsupplemental_log_data_min from v$database;

c)开启强制日志模式

SQL>alter database forcelogging;

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 to ogg;

SQL>grant select any dictionary,select any table 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;

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

[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

GGSCI (rhel131) 1>create subdirs

4)  源端和目标端都配置好manager进程

在源库和目标库上配置 GoldenGate 管理进程(源库、目标库都一样) GGSCI (rhel131) 1>edit params mgr

PORT7809PURGEOLDEXTRACTS/u01/app/oracle/ogg11/dirdat, USECHECKPOINTS, MINKEEPDAYS 10

GGSCI (rhel131) 1>start mgr Manager started.

5)  源端配置extract进程

GGSCI(rhel131) 1> add extract ext1,tranlog,begin now

EXTRACTadded.

GGSCI(rhel131) 2> add rmttrail/u01/app/oracle/ogg11/dirdat/lt, extract ext1

EXTTRAILadded.

这里不能用add exttrail，因为是直接将trail文件传输到目的端。

GGSCI(rhel131) 3> edit params ext1

extractext1

TRANLOGOPTIONS EXCLUDEUSER ogg

--加上这个参加是为了避免双向复制时目标和源重复复制数据。

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

useridogg, password ogg

rmthost172.17.61.132, mgrport 7809

rmttrail/u01/app/oracle/ogg11/dirdat/lt

tablescott.test1;

GGSCI(rhel131) 8>  start extract ext1

SendingSTART request to MANAGER ...

EXTRACTEXT1 starting

GGSCI(rhel131) 9>  info all

Program     Status     Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

EXTRACT     RUNNING    EXT1        00:13:28      00:00:01

至此源库的操作已结束。

6)  目标库建立checkpoint

GGSCI (rhel132) 1> edit params GLOBALS

GGSCHEMAOGG

CHECKPOINTTABLEogg.checkpoint

GGSCI(rhel132) 2> dblogin userid ogg password ogg

GGSCI(rhel132) 3> add checkpointtable ogg.checkpoint

7)  目标库建立replicate进程

GGSCI(rhel132) 4> add replicat rep1, exttrail ./dirdat/lt, checkpointtableogg.checkpoint

REPLICATadded.

GGSCI(rhel132) 5> edit params rep1

replicatrep1

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

useridogg, password ogg

handlecollisions

assumetargetdefs

discardfile./dirrpt/rora_aa.dsc, purge

mapscott.test1, target scott.test1;

~

GGSCI(rhel132) 6> start replicat rep1

GGSCI(rhel132) 7> info all

Program     Status     Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

REPLICAT    RUNNING    REP1        00:00:00      00:00:02

8)  测试一下是否能同步过来

SQL>insert into scott.test1

2 values (1);

1row created.

SQL>commit;

目标库同步没有问题：

SQL>  select * from test1;

T1

----------

1

下面要进行双向同步的操作：

9)  在目标端配置extract进程

GGSCI(rhel132) 8> add extract ext2, tranlog, begin now

EXTRACTadded.

GGSCI(rhel132) 10> add rmttrail /u01/app/oracle/ogg11/dirdat/tl, extract ext2

RMTTRAILadded.

Trail产生的文件名必须要和之前的分开，之前用的是lt,现在是tl

GGSCI(rhel132) 11> edit params ext2

extractext2

TRANLOGOPTIONSEXCLUDEUSER ogg

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

useridogg, password ogg

rmthost172.17.61.131, mgrport 7809

rmttrail/u01/app/oracle/ogg11/dirdat/tl

tablescott.test1;

GGSCI(rhel132) 19> info all

Program     Status     Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

EXTRACT     RUNNING    EXT2        00:00:00      00:00:02

REPLICAT    RUNNING    REP1        00:00:00      00:00:00

10)             源库建立checkpoint

GGSCI (rhel131) 1> edit params GLOBALS

GGSCHEMAOGG

CHECKPOINTTABLEogg.checkpoint

GGSCI(rhel131) 2> dblogin userid ogg password ogg

GGSCI(rhel131) 3> add checkpointtable ogg.checkpoint

11)             源库建立replicate进程

GGSCI(rhel131) 1> add replicat rep2, exttrail ./dirdat/tl, checkpointtableogg.checkpoint

REPLICATadded.

GGSCI(rhel131) 2> edit params rep2

replicatrep2

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

useridogg, password ogg

handlecollisions

assumetargetdefs

discardfile./dirrpt/rora_aa.dsc, purge

mapscott.test1, target scott.test1;

GGSCI(rhel131) 3> start replicat rep2

GGSCI(rhel131) 4> info all

Program     Status     Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

EXTRACT     RUNNING    EXT1        00:00:00      00:00:08

REPLICAT    RUNNING    REP2        00:00:00      00:00:01

12)             测试一下反向的同步：

目标端：

SQL> insertinto test1 values(4);

1 row created.

SQL> commit;

Commitcomplete.

SQL>  select * from test1;

T1

----------

4

4

2

3

1

源端：

SQL>select * from test1;

T1

----------

2

3

1

4

同步成功了!