oracle goldengate参数学习GETUPDATEBEFORES

1 [size=15pt]实验目的
主要目的有两个:
1: 加强goldengate的参数学习 如COMPRESSDELETES | NOCOMPRESSDELETES

GETUPDATEBEFORES 等等

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

以下是实验过程:
[size=15pt]
2 [size=15pt]实验环境
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)
);[size=15pt]
3 Oracle GlodenGate for windows安装
进入以下网站下载oracle glodendate安装软件
<[img=16,16]alires:///MsgHistory/UnknownUrl.bmp[/img]http://www.oracle.com/technology/software/products/goldengate/index.html]http://www.oracle.com/technology/software/products/goldengate/index.html>
这里为32bit for windows平台下的安装软件
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
Version 10.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: created

Exit;

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

蓝幽水月:22n [color=#939393](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 options

3.3 安装GlodenGate 管理进程
Install the glodendate manager process
C:\gg>ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 10.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
新建个文本文件输入以下内容[font=Courier New]
MGRSERVNAME PAUL,然后保存
添加管理进程到[font=Courier New]windows 服务中，以便随[font=Courier New]windows操作系统自动启动[font=Courier New]
C:\gg>install addservice addevents
Oracle GoldenGate messages installed successfully.
Service 'PAUL' created.
Install program terminated normally.
然后检查windows服务，已经存在paul这个服务了
PAUL Automatic LOCAL SYSTEM
[size=15pt]
4 源库配置抽取进程
4.1 启动mgr进程
cd c:\gg
c:\gg\ggsci
GGSCI (source) 2> edit param mgr
输入以下文件，然后按保存
--GoldenGate Manager parameter file
port 7809
输入7809的端口[font=Courier New]
[font=CenturySchoolbookBT-Roman]
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/goldengate
create 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_sal

4.4 启动mgr进程[font=Courier New]
[font=CenturySchoolbookBT-Roman]
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;[font=Courier New]
[font=CenturySchoolbookBT-Roman]
ADD RMTTRAIL ./dirdat/sq,extract eora01 MEGABYTES 5
[font=CenturySchoolbookBT-Roman]

4.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.prm

4.7 启动抽取进程
Start extract Eora01
[size=15pt]
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 table
GGSCI (target) 1> dblogin userid goldengate@goldengate,password goldengate
Successfully logged into database.

GGSCI (target) 2> add checkpointtable goldengate.GGSCHKPT

5.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 rora01

[size=15pt]
6 [size=15pt]测试验证结果

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 [color=#939393](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 AFTER [font=Courier New]03-8月[font=Courier New] -11 03.09.50.000000 下午 INSERT
2 200.00 AFTER [font=Courier New]03-8月[font=Courier New] -11 03.09.50.000000 下午 INSERT
3 300.00 AFTER [font=Courier New]03-8月[font=Courier New] -11 03.09.50.000000 下午 INSERT
4 400.00 AFTER [font=Courier New]03-8月[font=Courier New] -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_FLAG

1 100.00 BEFORE [font=Courier New]03-8月[font=Courier New] -11 03.09.55.000000 下午 SQL COMPUPDATE
1 200.00 AFTER [font=Courier New]03-8月[font=Courier New] -11 03.09.55.000000 下午 SQL COMPUPDATE

2 200.00 BEFORE [font=Courier New]03-8月[font=Courier New] -11 03.09.55.000000 下午 SQL COMPUPDATE
2 300.00 AFTER [font=Courier New]03-8月[font=Courier New] -11 03.09.55.000000 下午 SQL COMPUPDATE

3 300.00 BEFORE [font=Courier New]03-8月[font=Courier New] -11 03.09.55.000000 下午 SQL COMPUPDATE
3 400.00 AFTER [font=Courier New]03-8月[font=Courier New] -11 03.09.55.000000 下午 SQL COMPUPDATE

4 400.00 BEFORE [font=Courier New]03-8月[font=Courier New] -11 03.09.55.000000 下午 SQL COMPUPDATE
4 500.00 AFTER [font=Courier New]03-8月[font=Courier New] -11 03.09.55.000000 下午

蓝幽水月:22n [color=#939393](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_FLAG

1 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
　
可以看到删除操作全部记录下来。