OGG oracle DDL双向复制

来源：互联网发布：日语段落翻译软件编辑：程序博客网时间：2024/05/20 18:44

为了实现双向复制，需要在source和target数据库上配置数据库为归档模式，打开数据库的最小补充日志

[oracle@source ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 27 13:32:40 2013

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> alter system set recyclebin=off scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 603979776 bytes

Fixed Size 1220820 bytes

Variable Size 167776044 bytes

Database Buffers 432013312 bytes

Redo Buffers 2969600 bytes

Database mounted.

SQL> alter database force logging;

Database altered.

SQL> alter database add supplemental log data;

Database altered.

SQL> alter database archivelog;

Database altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 2

Current log sequence 2

SQL> alter database open;

Database altered.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;

SUPPLEME FOR

-------- ---

YES YES

2，配置gg环境

下载介质，到主机

分别在source 和target主机上执行以下命令：

mkdir /u01/app/gg

cd /u01/app/gg

cp -r /mnt/Share/oracle11g/64/ogg112101_fbo_ggs_Linux_x64_ora11g_64bit/ogg/* ./

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

在oracle用户下执行

[oracle@target ~]$ ln -s /u01/app/oracle/product/11.2.0/db/lib/libnnz11.so /u01/app/oracle/product/11.2.0/db/lib/libnnz10.so

编辑两个节点的oracle用户的环境变量

vim .bash_profile

添加如下内容：

export GGATE=/u01/app/gg

export LD_LIBRARY_PATH=/u01/app/gg:$ORACLE_HOME/lib:/lib:/usr/lib

export PATH=$GGATE:$PATH

source .bash_profile

创建GG的目录

在两个节点都要执行

[oracle@source ~]$ cd /u01/app/gg/

[oracle@source gg]$ ggsci

Oracle GoldenGate Command Interpreter 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:32:14

GGSCI (source.up.com) 1> create subdirs

Creating subdirectories under current directory /u01/app/gg

Parameter files /u01/app/gg/dirprm: already exists

Report files /u01/app/gg/dirrpt: created

Checkpoint files /u01/app/gg/dirchk: created

Process status files /u01/app/gg/dirpcs: created

SQL script files /u01/app/gg/dirsql: created

Database definitions files /u01/app/gg/dirdef: created

Extract data files /u01/app/gg/dirdat: created

Temporary files /u01/app/gg/dirtmp: created

Stdout files /u01/app/gg/dirout: created

在source和target数据分别创建gg用户

create tablespace gg datafile '/u01/app/oracle/oradata/source/gg01.dbf' size 30M autoextend on extent management local segment space management auto;

create user gg identified by gg default tablespace gg temporary tablespace temp;

grant connect,resource to gg;

grant execute on utl_file to gg;

create tablespace gg datafile '/u01/app/oracle/oradata/target/gg01.dbf' size 30M autoextend on extent management local segment space management auto;

create user gg identified by gg default tablespace gg temporary tablespace temp;

grant connect,resource to gg;

grant execute on utl_file to gg;

在两个数据库分别都执行DDL脚本

cd /u01/app/gg

sqlplus / as sysdba

执行以下脚本

@marker_setup.sql;

@ddl_setup.sql;

@role_setup.sql;

grant GGS_GGSUSER_ROLE to gg;

@ddl_enable.sql;

分别在两个节点创建业务schema

create user sender identified by oracle default tablespace users temporary tablespace temp;

grant connect,resource,dba to sender;

create user receiver identified by oracle default tablespace users temporary tablespace temp;

grant connect,resource,dba to receiver;

在两个节点创建mgr进程

GGSCI (target.up.com) 3> edit params mgr

添加如下内容：

PORT 7809

GGSCI (target.up.com) 4> start manager

Manager started.

GGSCI (target.up.com) 5> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

GGSCI (target.up.com) 6> view params mgr

PORT 7809

在source机器上配置源端复制队列

GGSCI (source.up.com) 5> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

GGSCI (source.up.com) 6> add extract ext1, tranlog, begin now

EXTRACT added.

GGSCI (source.up.com) 7> add exttrail /u01/app/gg/dirdat/ta, extract ext1

EXTTRAIL added.

GGSCI (source.up.com) 8> edit params ext1

添加如下内容：

extract ext1

userid gg@source, password gg

rmthost target, mgrport 7809

rmttrail /u01/app/gg/dirdat/ta

ddl include mapped objname sender.*;

table sender.*;

GGSCI (source.up.com) 9> start ext1

Sending START request to MANAGER ...

EXTRACT EXT1 starting

GGSCI (source.up.com) 10> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT1 00:00:00 00:03:45

在target端配置replicat进程

GGSCI (target.up.com) 5> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

GGSCI (target.up.com) 6> edit params ./GLOBAL

添加如下内容

GGSCHEMA gg

CHECKPOINTTABLE gg.checkpoint

测试数据库连接

GGSCI (target.up.com) 7> dblogin userid gg@target , password gg

Successfully logged into database.

GGSCI (target.up.com) 8> add checkpointtable gg.checkpoint

Successfully created checkpoint table gg.checkpoint.

GGSCI (target.up.com) 9> add replicat rep1, exttrail /u01/app/gg/dirdat/ta, checkpointtable gg.checkpoint

REPLICAT added.

GGSCI (target.up.com) 10> edit params rep1

GGSCI (target.up.com) 11> edit params rep1

添加如下内容：

replicat rep1

ASSUMETARGETDEFS

userid gg@target, password gg

discardfile /u01/app/gg/dirdat/rep1_discard.txt,append, megabytes 10

DDL

map sender.*, target receiver.*;

GGSCI (target.up.com) 12> start rep1

Sending START request to MANAGER ...

REPLICAT REP1 starting

GGSCI (target.up.com) 13> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

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

测试单向复制：

在源端

SQL> conn sender/oracle;

Connected.

SQL> select * from tab;

no rows selected

SQL> create table a (id number);

Table created.

SQL> commit;

Commit complete.

SQL> insert into a values (1);

1 row created.

SQL> commit;

Commit complete.

在目标端：

SQL> conn receiver/oracle;

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

A TABLE

1 row selected.

SQL> select * from a;

no rows selected

SQL> select * from a;

no rows selected

SQL> select * from a;

----------

1 row selected.

单项配置已经配置好了，现在执行从target端到source端的配置。

首先在target数据库配置抽取进程

GGSCI (target.up.com) 16> add extract ext2, tranlog, begin now

EXTRACT added.

GGSCI (target.up.com) 17> add exttrail /u01/app/gg/dirdat/tb, extract ext2

EXTTRAIL added.

GGSCI (target.up.com) 18> edit params ext2

添加如下内容：

extract ext2

userid gg@target, password gg

rmthost source, mgrport 7809

rmttrail /u01/app/gg/dirdat/tb

ddl include mapped objname receiver.*;

table receiver.*;

GGSCI (target.up.com) 19> start ext2

Sending START request to MANAGER ...

EXTRACT EXT2 starting

GGSCI (target.up.com) 20> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT2 00:00:00 00:01:12

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

配置source数据库的replicate进程

GGSCI (source.up.com) 13> edit params ./GLOBAL

GGSCI (source.up.com) 14> view params ./GLOBAL

GGSCHEMA gg

CHECKPOINTTABLE gg.checkpoint

GGSCI (source.up.com) 15> dblogin userid gg@source , password gg

Successfully logged into database.

GGSCI (source.up.com) 16> add checkpointtable gg.checkpoint

Successfully created checkpoint table gg.checkpoint.

GGSCI (source.up.com) 17> add replicat rep2, exttrail /u01/app/gg/dirdat/tb, checkpointtable gg.checkpoint

REPLICAT added.

GGSCI (source.up.com) 18> edit params rep2

GGSCI (source.up.com) 19> start rep2

Sending START request to MANAGER ...

REPLICAT REP2 starting

GGSCI (source.up.com) 20> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT1 00:00:00 00:00:02

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

测试：

在target数据库插入数据

SQL> insert into a values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from a;

----------

4 rows selected.

SQL> select * from a;

----------

8 rows selected.

这就出现了反复插入的问题

是因为参数配置不正确而导致的。修改ext1参数

分别edit params ext1 和edit params ext2

添加

TRANLOGOPTIONS EXCLUDEUSER gg

该语句的作用是不抽取gg产生的redo

再次进行测试：

在source机器上

SQL> create table b (id number);

Table created.

SQL> insert into b values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from b;

----------

2 rows selected.

在target机器上

SQL> select * from b;

no rows selected

SQL> /

----------

1 row selected.

SQL> insert into b values(2);

1 row created.

SQL> commit;

Commit complete.

至此，双向复制配置结束

参数文件配置，请参考《OGG mgr ext rep 参数文件说明》

http://blog.csdn.net/guduchangjian/article/details/10438019