oracle glodenguard,oracle goldengate参数学习GETUPDATEBEFORES

oracle goldengate参数学习GETUPDATEBEFORES

1实验目的主要目的有两个:

1: 加强goldengate的参数学习 如COMPRESSDELETES | NOCOMPRESSDELETES

GETUPDATEBEFORES 等等

2： 用ogg实现交易事务跟踪，比如业务系统需要实时记录某些数据的各种变化，很多人可能会用到分析归档日志或trigger,或写程序等去实现,oracle goldengate同样也可以实现这个需求。

以下是实验过程:2实验环境Oracle 10.2.0.1+windows 2003 32 bit 上面建立两个库

源端 orcl 实例 用户paul.emp_sal表

表结构为

create table paul.emp_sal

( empno number,

sal number(8,2),

constraint pk_emp_sal primary key(empno) using index

);

目标端 target 实例 用户paul.emp_sal_his 历史交易跟踪表

create table paul.emp_sal_his

( empno number,

sal number(8,2),

before_after varchar2(32),

deal_date timestamp,

op_flag varchar2(32)

);3 Oracle GlodenGate for windows安装进入以下网站下载oracle glodendate安装软件

GGV18162-01ForOracle10g.zip

直接用unzip解压到c:\GG目录下3.1 创建文件夹点击运行cmd，进入到c:\gg目录

C:\Documents and Settings\Oracle.XP_ONE>cd ..

C:\Documents and Settings>cd ..

C:\>cd gg

C:\gg>ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version10.4.0.19 Build 002

Windows (optimized), Oracle 10 on Sep 18 2009 15:54:55

Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.

GGSCI (source) 1> create subdirs

Creating subdirectories under current directory C:\gg

Parameter files C:\gg\dirprm: created

Report files C:\gg\dirrpt: created

Checkpoint files C:\gg\dirchk: created

Process status files C:\gg\dirpcs: created

SQL script files C:\gg\dirsql: created

Database definitions files C:\gg\dirdef: created

Extract data files C:\gg\dirdat: created

Temporary files C:\gg\dirtmp: created

Veridata files C:\gg\dirver: created

Veridata Lock files C:\gg\dirver\lock: created

Veridata Out-Of-Sync files C:\gg\dirver\oos: created

Veridata Out-Of-Sync XML files C:\gg\dirver\oosxml: created

Veridata Parameter files C:\gg\dirver\params: created

Veridata Report files C:\gg\dirver\report: created

Veridata Status files C:\gg\dirver\status: created

Veridata Trace files C:\gg\dirver\trace: created

Stdout files C:\gg\dirout: createdExit;3.2 源库和目标库都创建goldengate用户并赋予dba权限,并打开附加日志C:\Documents and Settings\Oracle.XP_ONE>s

蓝幽水月:22n (00:36:17):

qlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jan 24 00:20:38 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> create user goldengate identified by goldengate;

User created.

SQL> grant dba,connect,resource to goldengate

Grant succeeded.

SQL> alter database add supplemental log data;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> exit;

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options3.3 安装GlodenGate 管理进程Install the glodendate manager process

C:\gg>ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version10.4.0.19 Build 002

Windows (optimized), Oracle 10 on Sep 18 2009 15:54:55

Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.

GGSCI (source) 1> edit params ./GLOBAlS

新建个文本文件输入以下内容MGRSERVNAME PAUL,然后保存

添加管理进程到windows服务中，以便随windows操作系统自动启动C:\gg>install addservice addevents

Oracle GoldenGate messages installed successfully.

Service 'PAUL' created.

Install program terminated normally.然后检查windows服务，已经存在paul这个服务了

PAUL Automatic LOCAL SYSTEM

4 源库配置抽取进程4.1 启动mgr进程cd c:\gg

c:\gg\ggsci

GGSCI (source) 2> edit param mgr

输入以下文件，然后按保存

--GoldenGate Manager parameter file

port 7809

输入7809的端口GGSCI (source) 4> start mgr

Starting Manager as service ('PAUL')...

Service started.

GGSCI (source) 5> info mgr

Manager is running (IP port source.7809).可以看到启动mgr进程成功

4.2在源端创建基表C:\gg>sqlplus goldengate/goldengatecreate table paul.emp_sal

( empno number,

sal number(8,2),

constraint pk_emp_sal primary key(empno) using index

);4.3 在源端打开表级附加日志属性GGSCI (source) 2> dblogin userid goldengate@orcl,password source

Successfully logged into database.

GGSCI (source) 3> add trandata paul.emp_sal

Logging of supplemental redo data enabled for table paul.emp_sal4.4 启动mgr进程GGSCI (source) 4> start mgr

Starting Manager as service ('PAUL')...

Service started.

GGSCI (source) 5> info mgr

Manager is running (IP port source.7809).可以看到启动mgr进程成功

4.5添加抽取进程ADD EXTRACT eora01,TRANLOG, BEGIN NOW, THREADS 1

edit param eora01

EXTRACT Eora01

USERID goldengate@orcl, PASSWORD goldengate

RMTHOST yibin-PC, MGRPORT 7809

RMTTRAIL ./dirdat/sq

GETUPDATEBEFORES

NOCOMPRESSDELETES

TABLE PAUL.EMP_SAL;ADD RMTTRAIL ./dirdat/sq,extract eora01 MEGABYTES 54.6 生成emp_sal表的定义结构Cd ./dirprm

Vi 0803.prm

Defsfile ./dirdef/mydefs.def, purge

Userid goldengate@orcl, password goldengate

table paul.EMP_SAL;cd c:\gg

defgen paramfile ./dirprm/0803.prm4.7启动抽取进程Start extract Eora01

5 目标端配置复制进程5.1目标端创建交易跟踪表create table paul.emp_sal_his

( empno number,

sal number(8,2),

before_after varchar2(32),

deal_date timestamp,

op_flag varchar2(32)

);5.2 在目标端增加checkpoint tableGGSCI (target) 1> dblogin userid goldengate@goldengate,password goldengate

Successfully logged into database.

GGSCI (target) 2> add checkpointtable goldengate.GGSCHKPT5.3 在目标端增加复制进程GGSCI (target) 3> ADD REPLICAT RORA01,EXTTRAIL ./dirdat/sq ,checkpointtable GOLDENGATE.GGSCHKPT

REPLICAT added.GGSCI (target) 4> edit param rora01

REPLICAT rora01

SOURCEDEFS ./dirdef/MYDEFS.DEF

USERID goldengate@oragg2, PASSWORD goldengate

ASSUMETARGETDEFS

INSERTALLRECORDS

DISCARDFILE ./dirrpt/rora01.DSC, APPEND, MEGABYTES 10M

MAP paul.EMP_SAL, TARGET paul.EMP_SAL_HIS,

COLMAP (empno=empno,

sal=sal,

BEFORE_AFTER=@getenv("GGHEADER", "BEFOREAFTERINDICATOR"),

DEAL_DATE=@getenv ("GGHEADER", "COMMITTIMESTAMP"),

OP_FLAG=@getenv ("GGHEADER", "OPTYPE")

);5.4在目标端启动复制进程GGSCI (target) 6> start rora016测试验证结果6.1验证插入验证insert操作

Source system

Conn paul/paul@orcl

插入几条记录

SQL> select * from emp_sal;

no rows selected

SQL> insert into emp_sal values(1,100);

1 row created.

SQL> insert into emp_sal values(2,200);

1 row created.

SQL> insert into emp_sal values(3,300);

1 row created.

SQL> commit;

蓝幽水月:22n (00:36:23):

Commit complete.

SQL> insert into emp_sal values(4,400);

1 row created.

SQL> commit;

Commit complete.Target system

SQL> select * from emp_sal_his;

EMPNO SAL BEFORE_AFTER DEAL_DATE OP_FLAG

----- --------- -------------- ----------------------------- -------1 100.00 AFTER03-8月-11 03.09.50.000000下午INSERT

2 200.00 AFTER03-8月-11 03.09.50.000000下午INSERT

3 300.00 AFTER03-8月-11 03.09.50.000000下午INSERT

4 400.00 AFTER03-8月-11 03.09.51.000000下午INSERT可以看到插入操作全部捕获。6.2验证更新Source system

修改记录,让每个empno的sal增加100.

SQL> update emp_sal set sal=sal+100;

4 rows updated.

SQL> select * from emp_sal;

EMPNO SAL

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

1 200

2 300

3 400

4 500

SQL> commit;

Commit complete.

Target system

目标系统上看看是不是捕获了修改操作信息，

SQL> select * from emp_sal_his;

EMPNO SAL BEFORE_AFTER DEAL_DATE OP_FLAG1 100.00 BEFORE03-8月-11 03.09.55.000000下午SQL COMPUPDATE

1 200.00 AFTER03-8月-11 03.09.55.000000下午SQL COMPUPDATE2 200.00 BEFORE03-8月-11 03.09.55.000000下午SQL COMPUPDATE

2 300.00 AFTER03-8月-11 03.09.55.000000下午SQL COMPUPDATE3 300.00 BEFORE03-8月-11 03.09.55.000000下午SQL COMPUPDATE

3 400.00 AFTER03-8月-11 03.09.55.000000下午SQL COMPUPDATE4 400.00 BEFORE03-8月-11 03.09.55.000000下午SQL COMPUPDATE

4 500.00 AFTER03-8月-11 03.09.55.000000下午

蓝幽水月:22n (00:36:28):

SQL COMPUPDATE可以看到修改前后的值都有。6.3验证删除Source system

SQL> delete from emp_sal;

4 rows deleted.

SQL> commit;

Commit complete.

SQL>

Target system

再到target系统上看看是不是捕获了删除操作信息

SQL> select * from emp_sal_his;

EMPNO SAL BEFORE_AFTER DEAL_DATE OP_FLAG1 200.00 BEFORE 03-8月 -11 03.10.14.000000 下午DELETE

2 300.00 BEFORE 03-8月 -11 03.10.14.000000 下午DELETE

3 400.00 BEFORE 03-8月 -11 03.10.14.000000 下午DELETE

4 500.00 BEFORE 03-8月 -11 03.10.14.000000 下午DELETE可以看到删除操作全部记录下来。