oracle goldengate 参数学习 GETUPDATEBEFORES
1 实验目的
主要目的有两个:
1: 加强goldengate的参数学习 如COMPRESSDELETES | NOCOMPRESSDELETES
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 5
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
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
6 测试验证结果
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;
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 03-8月 -11 03.09.50.000000 下午 INSERT
2 200.00 AFTER 03-8月 -11 03.09.50.000000 下午 INSERT
3 300.00 AFTER 03-8月 -11 03.09.50.000000 下午 INSERT
4 400.00 AFTER 03-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_FLAG
1 100.00 BEFORE 03-8月 -11 03.09.55.000000 下午 SQL COMPUPDATE
1 200.00 AFTER 03-8月 -11 03.09.55.000000 下午 SQL COMPUPDATE
2 200.00 BEFORE 03-8月 -11 03.09.55.000000 下午 SQL COMPUPDATE
2 300.00 AFTER 03-8月 -11 03.09.55.000000 下午 SQL COMPUPDATE
3 300.00 BEFORE 03-8月 -11 03.09.55.000000 下午 SQL COMPUPDATE
3 400.00 AFTER 03-8月 -11 03.09.55.000000 下午 SQL COMPUPDATE
4 400.00 BEFORE 03-8月 -11 03.09.55.000000 下午 SQL COMPUPDATE
4 500.00 AFTER 03-8月 -11 03.09.55.000000 下午
主要目的有两个:
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安装软件
<
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
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安装软件
<
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 (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
新建个文本文件输入以下内容
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/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进程
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进程成功
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
新建个文本文件输入以下内容
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/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进程
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 5
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
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
6 测试验证结果
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 AFTER 03-8月 -11 03.09.50.000000 下午 INSERT
2 200.00 AFTER 03-8月 -11 03.09.50.000000 下午 INSERT
3 300.00 AFTER 03-8月 -11 03.09.50.000000 下午 INSERT
4 400.00 AFTER 03-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_FLAG
1 100.00 BEFORE 03-8月 -11 03.09.55.000000 下午 SQL COMPUPDATE
1 200.00 AFTER 03-8月 -11 03.09.55.000000 下午 SQL COMPUPDATE
2 200.00 BEFORE 03-8月 -11 03.09.55.000000 下午 SQL COMPUPDATE
2 300.00 AFTER 03-8月 -11 03.09.55.000000 下午 SQL COMPUPDATE
3 300.00 BEFORE 03-8月 -11 03.09.55.000000 下午 SQL COMPUPDATE
3 400.00 AFTER 03-8月 -11 03.09.55.000000 下午 SQL COMPUPDATE
4 400.00 BEFORE 03-8月 -11 03.09.55.000000 下午 SQL COMPUPDATE
4 500.00 AFTER 03-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_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
可以看到删除操作全部记录下来。
可以看到修改前后的值都有。
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
可以看到删除操作全部记录下来。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7199859/viewspace-704546/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
OGG 新增复制表实施步骤
请登录后发表评论
登录
全部评论
最新文章
- 《手把手教你用AI安装Oracle 19c（附详细步骤）》
- 《3小时手工安装Oracle，用AI只需15分钟》
- oracle 20c 不支持non-cdb架构
- 当删除oracle数据库user时发生row cache lock 等待事件
- 通过 Oracle_Cloud_Infrastructure oci 云认证考试
- ORA-32021: parameter value longer than 255 characters 解决方法
- online清理oracle 监听日志推荐的做法
- 分享查看统计信息非常好的脚本
- impdp 导入 schema的数据时会自动创建对应的用户
- 如何移动asm磁盘组内的数据文件到另外一个磁盘组
