oracle+GoldenGate学习笔记 下载本文

-------------------------------------------------------------------------------- 0

DDL TRIGGER TRACE LEVEL

-------------------------------------------------------------------------------- 0

LOCATION OF DDL TRACE FILE

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

C:\\ORACLE\\PRODUCT\\10.2.0\\ADMIN\\TEST\\UDUMP/ggs_ddl_trace.log

Analyzing installation status...

STATUS OF DDL REPLICATION

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

SUCCESSFUL installation of DDL Replication software components

Script complete.

SQL> @role_setup.sql

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE

To use a different role name, quit this script and then edit the params.sql scri pt to change the gg_role parameter to the preferred name. (Do not run the script .)

You will be prompted for the name of a schema for the GoldenGate database object s.

NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:goldengate 已写入 file role_setup_set.txt

PL/SQL 过程已成功完成。

Role setup script complete

21

Grant this role to each user assigned to the Extract, GGSCI, and Manager process es, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO

where is the user assigned to the GoldenGate processes. SQL> GRANT GGS_GGSUSER_ROLE TO goldengate;

授权成功。

SQL> @ddl_enable.sql

触发器已更改

SQL> exit

安装完成

Note:

Supported Oracle objects and operations for DDL replication

clusters functions indexes packages procedure tables tablespaces roles sequences synonyms triggers types views

materialized views

22

users

Non-supported Oracle objects and operations for DDL replication

Oracle-reserved schemas Oracle recycle bin

Limitation of GG DDL Support

1) GoldenGate supports DDL replication for uni-directional configurations. Bi-directional replication of DDL is not supported.(只支持单向复制)

2) GoldenGate supports DDL synchronization only in a like-to-like configuration.GoldenGate DDL support requires the following:

? Source and target object definitions must be identical.

? The ASSUMETARGETDEFS parameter must be used in the Replicat parameter file. Replicat will abend if objects are configured for DDL support and the SOURCEDEFS parameter is being used.(必须在Replicat参数文件中指定ASSUMETARGETDEFS参数)

5.玩玩GoldenGate

前一段跟着Quest的朋友学习了一些SharePlex的东西,最近Oracle则在大力推广另一个类似的产品GoldenGate,手头的资料不多,只有一份官方文档和一份所谓的培训教材PPT,不过可操作性不强,上手有点难,后来看了这篇文章,才终于把基本的同步实现了。不过这位作者显然已经研究不少时间的GG,所以很多命令并没有写明,这里我把它补充一下吧。

1,数据库准备

1.1 检查SUPPLEMENTAL_LOG

和SharePlex一样,GG要求打开SUPPLEMENTAL_LOG,通过如下SQL可以查看:

SQL>select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

23

如果没有开启,则执行如下语句打开SUPPLEMENTAL_LOG:

SQL>alter database add supplemental log data;

1.2 建立测试用户

ggs和ggt分别为源数据用户和目标用户。

create user ggs identified by ggs default tablespace users; grant connect,resource to ggs;

create user ggt identified by ggt default tablespace users; grant connect,resource to ggt;

建立GG使用的用户gg。

create user gg identified by gg default tablespace users; grant dba to gg;

2. 下载安装GoldenGate

需要到http://edelivery.oracle.com/下载对应版本的GoldenGate,我不太明白的是为什么这个GG的Product Pack是Oracle Fusion Middleware,另外,我是在RHEL5.0上做的实验,所以Platform我选择了Linux x86,下载文件大约27M。

为了简单,我直接使用操作系统的oracle用户安装GG。建立目录/home/oracle/ggs,把下载的文件unzip,然后存放到这个目录后执行:

tar -xvof ggs_redhatAS50_x86_ora11g_32bit_v10.4.0.19_002.tar

这样,/home/oracle/ggs就是我们的GG的目录了。这时候我们进入ggs目录,执行ggsci可能会报找不到库的错误,那是我们还没有配置LIB,对于我这个系统,我在.bash_profile里增加了如下几句:

LD_LIBRARY_PATH=/home/oracle/ggs:/u01/app/oracle/product/11.1.0/db_1/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH

PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:/home/oracle/ggs; export PATH

运行’. .bash_profile’,或者重新登入oracle,再执行ggsci,就可以进入ggsci的管理界面,可以运行GG的命令了。这时,首先要做的就是建立GG的工作目录,我们运行:

CREATE SUBDIRS

(这一步最好在cd到/home/oracle/ggs,然后运行ggsci后执行,别的地方建立的环境有问题,我还是初学,应该有配置的方法,暂时不懂)

24

运行完毕,就生成了许多目录,这是GG配置文件,日志等文件的存放目录。

3. 配置最简单的实验

本次测试模拟的情况是:为了把ggs模式下的表中的数据变化同步到ggt模式的相关表中。本次配置不在源端缓存数据,直接把extract进程抓取的数据存储在目标端数据库服务器上,然后目标端数据库服务器的Replicat进程把队列中的数据投递到目标数据库中。相关配置命令和参数如下:

添加相关进程:

ADD EXTRACT ext1 TRANLOG, BEGIN NOW

ADD rmttrail /home/oracle/ggs/dirdat/r1 extract ext1

ADD replicat rep1 EXTTRAIL /home/oracle/ggs/dirdat/r1, nodbcheckpoint

运行edit param mgr,然后保存文件内容如下:

port 7809

运行edit param ext1 ,然后保存文件内容如下:

extract ext1

userid gg,password gg

rmthost 127.0.0.1, mgrport 7809 rmttrail /home/oracle/ggs/dirdat/r1 dynamicresolution table ggs.*;

运行edit param rep1,然后保存文件内容如下:

replicat rep1

userid gg,password gg assumetargetdefs

map ggs.*, target ggt.*;

然后启动必要的进程:

start mgr start ext1 start rep1

这时可以运行info all来查看是否所有的进程都是正常运行:

25

GGSCI> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT1 00:00:00 00:00:07 REPLICAT RUNNING REP1 00:00:00 00:00:08

4. 测试同步

使用sqlplus执行SQL语句:

create table ggs.tb1 (a number, CONSTRAINT pk1 PRIMARY KEY (a))

create table ggt.tb1 (a number, CONSTRAINT pk1 PRIMARY KEY (a))

然后运行:

insert into ggs.tb1 values (1); commit;

看看是否同步到了ggt.tb1了?

; 26

;