当前位置：Oracle研究中心 > 产品DBA > Oracle GG >
【学习笔记】Oracle goldengate 一对多复制的配置过程笔记
时间:2016-12-11 18:33 来源:Oracle研究中心 作者:网络 点击: 次
天萃荷净
Oracle研究中心学习笔记：分享一篇关于Oracle数据库goldengate的配置笔记，详细介绍OGG在一对多的复制的配置方法笔记。
本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: goldengate 学习系列3–一对多的复制配置
前面进行了1对1的复制(支持ddl)，这里进行1对多的复制，下面是完整的过程。
说明：
源端 — 10201 asm
目标端1 —11.2.0.2 asm
目标端2 —10.2.0.5 filesystem
首先在源端再添加一个pump extract进程dp2，针对第2个target端。
----源端
GGSCI (10gasm) 39> edit param dp2
EXTRACT dp2
RMTHOST 192.168.0.115, MGRPORT 7809 TCPBUFSIZE 5000000
PASSTHRU
RMTTRAIL ./dirdat/rm
NUMFILES 3000
SEQUENCE roger.*;
TABLE roger.*;
~
~
"dirprm/dp2.prm" [New] 10L, 168C written
GGSCI (10gasm) 40>
GGSCI (10gasm) 6> add extract dp2, exttrailsource ./dirdat/ex
EXTRACT added.
GGSCI (10gasm) 7> add rmttrail ./dirdat/rm, extract dp2, megabytes 10
RMTTRAIL added.
GGSCI (10gasm) 8> start dp2
Sending START request to MANAGER ...
EXTRACT DP2 starting
GGSCI (10gasm) 22> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:00:05
EXTRACT RUNNING DP2 00:00:00 00:00:01
EXTRACT RUNNING EXT1 00:00:00 00:00:05
++++++++++ 目标端
1 .创建操作系统ggs用户
useradd -g oinstall -G dba ggs
passwd ggs
2. 配置ggs用户环境变量
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
ORACLE_SID=roger
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
ORACLE_HOME=/home/ora10g/product/10.2/
export LD_LIBRARY_PATH=/home/ggs/ggs_home:$ORACLE_HOME/lib
3. 创建数据库ggs用户
SQL> conn /AS sysdba
Connected.
SQL> SELECT instance_name FROM v$instance;
INSTANCE_NAME
----------------
roger
SQL> SELECT * FROM v$version WHERE rownum <3;
BANNER
----------------------------------------------------------------
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
SQL> CREATE USER ggs IDENTIFIED BY ggs;
USER created.
SQL> GRANT CONNECT,resource TO ggs;
GRANT succeeded.
SQL> GRANT dba TO ggs;
GRANT succeeded.
SQL> ALTER USER ggs DEFAULT tablespace roger;
USER altered.
SQL> ALTER USER ggs TEMPORARY TABLESPACE temp;
USER altered.
4. 配置目标端goldengate
4.1) 创建相关目录
[ggs@killdb ggs_home]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 10g on Apr 23 2012 07:06:02
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (oracleplus.net) 1> create subdirs
Creating subdirectories under current directory /home/ggs/ggs_home
Parameter files /home/ggs/ggs_home/dirprm: already exists
Report files /home/ggs/ggs_home/dirrpt: created
Checkpoint files /home/ggs/ggs_home/dirchk: created
Process status files /home/ggs/ggs_home/dirpcs: created
SQL script files /home/ggs/ggs_home/dirsql: created
Database definitions files /home/ggs/ggs_home/dirdef: created
Extract data files /home/ggs/ggs_home/dirdat: created
Temporary files /home/ggs/ggs_home/dirtmp: created
Stdout files /home/ggs/ggs_home/dirout: created
4.2) 设置GLOBALS参数
GGSCI (oracleplus.net) 2> edit param ./GLOBALS
GGSCHEMA ggs
CHECKPOINTTABLE ggs.GGSCHKPT
~
~
"./GLOBALS" [New] 2L, 44C written
GGSCI (oracleplus.net) 3> exit
4.3) 创建checkpoint table
[ggs@killdb ggs_home]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 10g on Apr 23 2012 07:06:02
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (oracleplus.net) 1> dblogin userid ggs@Roger,password ggs
Successfully logged into database.
GGSCI (oracleplus.net) 2> add checkpointtable
No checkpoint table specified, using GLOBALS specification (ggs.GGSCHKPT)...
Successfully created checkpoint table ggs.GGSCHKPT.
5. 创建replicat 进程
GGSCI (oracleplus.net) 5> edit param rep2
replicat rep2
userid ggs,password ggs
reperror default, discard
DISCARDROLLOVER AT 20:30
discardfile ./dirrpt/rep2.dsc, append, megabytes 50
assumetargetdefs
allownoopupdates
numfiles 3000
gettruncates
map roger.*, target roger.*;
~
~
"dirprm/rep2.prm" [New] 13L, 244C written
GGSCI (oracleplus.net) 2> add replicat rep2, exttrail ./dirdat/rm
REPLICAT added.
GGSCI (oracleplus.net) 3>
6. 同步数据
---先用imp同步下数据
[ora10g@killdb ~]$ imp roger/roger file=roger.dmp fromuser=roger touser=roger
Import: Release 10.2.0.5.0 - Production on Sun Aug 19 04:44:25 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. . importing table "IND_T" 2 rows imported
.....省略部分信息
. . importing table "TEST_OGG" 100 rows imported
IMP-00015: following statement failed because the object already exists:
"CREATE SYNONYM "V$PARAMETER" FOR "SYS"."SHOW_HIDDEN_V$PARAMETER""
Import terminated successfully with warnings.
由于我这里配置2个replicat进程参数都是一样的，所以我这里直接拷贝rep1上面的/dirdat/* 文件到rep2机器上，如下：
-bash-3.2$ scp * 192.168.0.115:/home/ggs/ggs_home/dirdat
The authenticity of host '192.168.0.115 (192.168.0.115)' can't be established.
RSA key fingerprint is e5:b6:56:0a:9f:a5:d6:e6:7d:ad:2d:f4:dc:41:2c:25.
Are you sure you want to continue connecting (yes/no) yes
Warning: Permanently added '192.168.0.115' (RSA) to the list of known hosts.
ggs@192.168.0.115's password:
rm000000 100% 1146 1.1KB/s 00:00
rm000001 100% 28KB 27.9KB/s 00:00
rm000002 100% 1215 1.2KB/s 00:00
rm000003 100% 1250 1.2KB/s 00:00
这里要说明一下，ogg的初始化方式有很多种，后面会进行总结，这里不多说。
7. 启动replicat 进程
启动rep2进程：
GGSCI (oracleplus.net) 53> start rep2
Sending START request to MANAGER ...
REPLICAT REP2 starting
GGSCI (oracleplus.net) 54> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT ABENDED REP2 00:00:00 00:00:07
开始始终无法启动，通过info report 可以看到rep2是从extseq 0开始读的，显示这是不一致的。
GGSCI (oracleplus.net) 43> info rep2,detail
REPLICAT REP2 Last Started 2012-08-19 04:55 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:16:04 ago)
Log Read Checkpoint File ./dirdat/rm000000
First Record RBA 0
Extract Source Begin End
./dirdat/rm000000 * Initialized * First Record
./dirdat/rm000000 * Initialized * First Record
./dirdat/rm000000 * Initialized * First Record
./dirdat/rm000000 * Initialized * First Record
./dirdat/rm000000 * Initialized * First Record
Current directory /home/ggs/ggs_home
Report file /home/ggs/ggs_home/dirrpt/REP2.rpt
Parameter file /home/ggs/ggs_home/dirprm/rep2.prm
Checkpoint file /home/ggs/ggs_home/dirchk/REP2.cpr
Checkpoint table ggs.GGSCHKPT
Process file /home/ggs/ggs_home/dirpcs/REP2.pcr
Stdout file /home/ggs/ggs_home/dirout/REP2.out
Error log /home/ggs/ggs_home/ggserr.log
由于要保持一致，所以这里对比下rep2 机器上的信息即可：
GGSCI (11gR2test) 17> info rep1
REPLICAT REP1 Last Started 2012-08-18 22:10 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint File ./dirdat/rm000003
2012-08-19 04:52:07.295446 RBA 1250
可以看到目前extseq 为3，所以可以这样进行启动rep2进程，如下：
GGSCI (oracleplus.net) 59> alter REPLICAT rep2,EXTSEQNO 3;
REPLICAT altered.
GGSCI (oracleplus.net) 61> start rep2
Sending START request to MANAGER ...
REPLICAT REP2 starting
GGSCI (oracleplus.net) 62> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP2 00:00:00 00:00:09
说明，最后感觉还是有些问题，虽然进程能起来，发现目标端1少了个，不知道为什么，我这里重新把目标端1和目标端2初始化了一下，仍然是用的imp，不多说了。
8. check 数据一致性
----源端
SQL> show user
USER is "ROGER"
SQL> delete from test_ogg where rownum < 101;
100 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from roger.test_ogg;
COUNT(*)
----------
49805
GGSCI (10gasm) 53> send extract dp1 ,ROLLOVER
Sending ROLLOVER request to EXTRACT DP1 ...
Request processed.
GGSCI (10gasm) 54> send extract dp2 ,ROLLOVER
Sending ROLLOVER request to EXTRACT DP2 ...
Request processed.
GGSCI (10gasm) 55> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:00:04
EXTRACT RUNNING DP2 00:00:00 00:00:03
EXTRACT RUNNING EXT1 00:00:00 00:00:06
---目标端1
GGSCI (11gR2test) 22> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:09
GGSCI (11gR2test) 23>
GGSCI (11gR2test) 23> info rep1
REPLICAT REP1 Last Started 2012-08-19 06:09 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:00 ago)
Log Read Checkpoint File ./dirdat/rm000005
2012-08-19 06:10:52.085889 RBA 1203
SQL> l
1* select count(*) from roger.test_ogg
SQL> /
COUNT(*)
----------
49905
----目标端1 这里数据没有变化，但是看信息却是应用了的，很奇怪。
GGSCI (11gR2test) 47> stats rep1
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2012-08-19 06:35:09.
Replicating from ROGER.TEST_OGG to ROGER.TEST_OGG:
*** Total statistics since 2012-08-19 06:09:10 ***
Total inserts 100.00
Total updates 0.00
Total deletes 100.00
Total discards 0.00
Total operations 2http://www.oracleplus.net00.00
*** Daily statistics since 2012-08-19 06:09:10 ***
Total inserts 100.00
Total updates 0.00
Total deletes 100.00
Total discards 0.00
Total operations 200.00
*** Hourly statistics since 2012-08-19 06:09:10 ***
Total inserts 100.00
Total updates 0.00
Total deletes 100.00
Total discards 0.00
Total operations 200.00
*** Latest statistics since 2012-08-19 06:09:10 ***
Total inserts 100.00
Total updates 0.00
Total deletes 100.00
Total discards 0.00
Total operations 200.00
End of Statistics.
---目标端2
[ora10g@killdb ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.5.0 - Production on Sun Aug 19 06:14:01 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count(*) from roger.test_ogg;
COUNT(*)
----------
49805
GGSCI (oracleplus.net) 102> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP2 00:00:00 00:00:01
GGSCI (oracleplus.net) 103> info rep2
REPLICAT REP2 Last Started 2012-08-19 06:09 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint File ./dirdat/rm000005
2012-08-19 06:10:52.239906 RBA 1203
9. 再次测试
--源端
SQL> show user
USER is "ROGER"
SQL> truncate table test_ogg;
Table truncated.
---目标端1
GGSCI (11gR2test) 48> stats rep1
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2012-08-19 06:35:58.
Replicating from ROGER.TEST_OGG to ROGER.TEST_OGG:
*** Total statistics since 2012-08-19 06:09:10 ***
Total inserts 100.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 201.00
*** Daily statistics since 2012-08-19 06:09:10 ***
Total inserts 100.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 201.00
*** Hourly statistics since 2012-08-19 06:09:10 ***
Total inserts 100.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 201.00
*** Latest statistics since 2012-08-19 06:09:10 ***
Total inserts 100.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 201.00
End of Statistics.
SQL> l
1* select count(*) from roger.test_ogg
SQL> /
COUNT(*)
----------
0
---目标端2
GGSCI (oracleplus.net) 107> stats rep2
Sending STATS request to REPLICAT REP2 ...
Start of Statistics at 2012-08-19 06:35:51.
Replicating from ROGER.TEST_OGG to ROGER.TEST_OGG:
*** Total statistics since 2012-08-19 06:11:09 ***
Total inserts 0.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 101.00
*** Daily statistics since 2012-08-19 06:11:09 ***
Total inserts 0.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 101.00
*** Hourly statistics since 2012-08-19 06:11:09 ***
Total inserts 0.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 101.00
*** Latest statistics since 2012-08-19 06:11:09 ***
Total inserts 0.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 101.00
End of Statistics.
SQL> /
COUNT(*)
----------
0
10. 最后一次测试
---源端
SQL> insert into test_ogg select * from sys.dba_objects;
49906 rows created.
SQL> commit;
Commit complete.
SQL> delete from test_ogg where rownum < 1001;
1000 rows deleted.
SQL> commit;
Commit complete.
---目标端1
GGSCI (11gR2test) 49> stats rep1
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2012-08-19 06:38:22.
Replicating from ROGER.TEST_OGG to ROGER.TEST_OGG:
*** Total statistics since 2012-08-19 06:09:10 ***
Total inserts 34315.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 34416.00
*** Daily statistics since 2012-08-19 06:09:10 ***
Total inserts 34315.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 34416.00
*** Hourly statistics since 2012-08-19 06:09:10 ***
Total inserts 34315.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 34416.00
*** Latest statistics since 2012-08-19 06:09:10 ***
Total inserts 34315.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 34416.00
End of Statistics.
GGSCI (11gR2test) 50> stats rep1
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2012-08-19 06:41:02.
Replicating from ROGER.TEST_OGG to ROGER.TEST_OGG:
*** Total statistics since 2012-08-19 06:09:10 ***
Total inserts 50006.00
Total updates 0.00
Total deletes 1100.00
Total truncates 1.00
Total discards 0.00
Total operations 51107.00
*** Daily statistics since 2012-08-19 06:09:10 ***
Total inserts 50006.00
Total updates 0.00
Total deletes 1100.00
Total truncates 1.00
Total discards 0.00
Total operations 51107.00
*** Hourly statistics since 2012-08-19 06:09:10 ***
Total inserts 50006.00
Total updates 0.00
Total deletes 1100.00
Total truncates 1.00
Total discards 0.00
Total operations 51107.00
*** Latest statistics since 2012-08-19 06:09:10 ***
Total inserts 50006.00
Total updates 0.00
Total deletes 1100.00
Total truncates 1.00
Total discards 0.00
Total operations 51107.00
End of Statistics.
SQL> select count(*) from roger.test_ogg
2 /
COUNT(*)
----------
49906
SQL> l
1* select count(*) from roger.test_ogg
SQL> /
COUNT(*)
----------
48906
---目标端2
GGSCI (oracleplus.net) 108> stats rep2
Sending STATS request to REPLICAT REP2 ...
Start of Statistics at 2012-08-19 06:39:39.
Replicating from ROGER.TEST_OGG to ROGER.TEST_OGG:
*** Total statistics since 2012-08-19 06:11:09 ***
Total inserts 49906.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 50007.00
*** Daily statistics since 2012-08-19 06:11:09 ***
Total inserts 49906.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 50007.00
*** Hourly statistics since 2012-08-19 06:11:09 ***
Total inserts 49906.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 50007.00
*** Latest statistics since 2012-08-19 06:11:09 ***
Total inserts 49906.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 50007.00
End of Statistics.
GGSCI (oracleplus.net) 109> stats rep2
Sending STATS request to REPLICAT REP2 ...
Start of Statistics at 2012-08-19 06:40:50.
Replicating from ROGER.TEST_OGG to ROGER.TEST_OGG:
*** Total statistics since 2012-08-19 06:11:09 ***
Total inserts 49906.00
Total updates 0.00
Total deletes 1100.00
Total truncates 1.00
Total discards 0.00
Total operations 51007.00
*** Daily statistics since 2012-08-19 06:11:09 ***
Total inserts 49906.00
Total updates 0.00
Total deletes 1100.00
Total truncates 1.00
Total discards 0.00
Total operations 51007.00
*** Hourly statistics since 2012-08-19 06:11:09 ***
Total inserts 49906.00
Total updates 0.00
Total deletes 1100.00
Total truncates 1.00
Total discards 0.00
Total operations 51007.00
*** Latest statistics since 2012-08-19 06:11:09 ***
Total inserts 49906.00
Total updates 0.00
Total deletes 1100.00
Total truncates 1.00
Total discards 0.00
Total operations 51007.00
End of Statistics.
SQL> l
1* select count(*) from roger.test_ogg
SQL> /
COUNT(*)
----------
49906
SQL> l
1* select count(*) from roger.test_ogg
SQL> /
COUNT(*)
----------
48906
最后经过多轮测试，发现一切ok。
Oracle研究中心
本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: goldengate 学习系列3–一对多的复制配置
前面进行了1对1的复制(支持ddl)，这里进行1对多的复制，下面是完整的过程。
说明：
源端 — 10201 asm
目标端1 —11.2.0.2 asm
目标端2 —10.2.0.5 filesystem
首先在源端再添加一个pump extract进程dp2，针对第2个target端。
----源端
GGSCI (10gasm) 39> edit param dp2
EXTRACT dp2
RMTHOST 192.168.0.115, MGRPORT 7809 TCPBUFSIZE 5000000
PASSTHRU
RMTTRAIL ./dirdat/rm
NUMFILES 3000
SEQUENCE roger.*;
TABLE roger.*;
~
~
"dirprm/dp2.prm" [New] 10L, 168C written
GGSCI (10gasm) 40>
GGSCI (10gasm) 6> add extract dp2, exttrailsource ./dirdat/ex
EXTRACT added.
GGSCI (10gasm) 7> add rmttrail ./dirdat/rm, extract dp2, megabytes 10
RMTTRAIL added.
GGSCI (10gasm) 8> start dp2
Sending START request to MANAGER ...
EXTRACT DP2 starting
GGSCI (10gasm) 22> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:00:05
EXTRACT RUNNING DP2 00:00:00 00:00:01
EXTRACT RUNNING EXT1 00:00:00 00:00:05
++++++++++ 目标端
1 .创建操作系统ggs用户
useradd -g oinstall -G dba ggs
passwd ggs
2. 配置ggs用户环境变量
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
ORACLE_SID=roger
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
ORACLE_HOME=/home/ora10g/product/10.2/
export LD_LIBRARY_PATH=/home/ggs/ggs_home:$ORACLE_HOME/lib
3. 创建数据库ggs用户
SQL> conn /AS sysdba
Connected.
SQL> SELECT instance_name FROM v$instance;
INSTANCE_NAME
----------------
roger
SQL> SELECT * FROM v$version WHERE rownum <3;
BANNER
----------------------------------------------------------------
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
SQL> CREATE USER ggs IDENTIFIED BY ggs;
USER created.
SQL> GRANT CONNECT,resource TO ggs;
GRANT succeeded.
SQL> GRANT dba TO ggs;
GRANT succeeded.
SQL> ALTER USER ggs DEFAULT tablespace roger;
USER altered.
SQL> ALTER USER ggs TEMPORARY TABLESPACE temp;
USER altered.
4. 配置目标端goldengate
4.1) 创建相关目录
[ggs@killdb ggs_home]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 10g on Apr 23 2012 07:06:02
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (oracleplus.net) 1> create subdirs
Creating subdirectories under current directory /home/ggs/ggs_home
Parameter files /home/ggs/ggs_home/dirprm: already exists
Report files /home/ggs/ggs_home/dirrpt: created
Checkpoint files /home/ggs/ggs_home/dirchk: created
Process status files /home/ggs/ggs_home/dirpcs: created
SQL script files /home/ggs/ggs_home/dirsql: created
Database definitions files /home/ggs/ggs_home/dirdef: created
Extract data files /home/ggs/ggs_home/dirdat: created
Temporary files /home/ggs/ggs_home/dirtmp: created
Stdout files /home/ggs/ggs_home/dirout: created
4.2) 设置GLOBALS参数
GGSCI (oracleplus.net) 2> edit param ./GLOBALS
GGSCHEMA ggs
CHECKPOINTTABLE ggs.GGSCHKPT
~
~
"./GLOBALS" [New] 2L, 44C written
GGSCI (oracleplus.net) 3> exit
4.3) 创建checkpoint table
[ggs@killdb ggs_home]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 10g on Apr 23 2012 07:06:02
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (oracleplus.net) 1> dblogin userid ggs@Roger,password ggs
Successfully logged into database.
GGSCI (oracleplus.net) 2> add checkpointtable
No checkpoint table specified, using GLOBALS specification (ggs.GGSCHKPT)...
Successfully created checkpoint table ggs.GGSCHKPT.
5. 创建replicat 进程
GGSCI (oracleplus.net) 5> edit param rep2
replicat rep2
userid ggs,password ggs
reperror default, discard
DISCARDROLLOVER AT 20:30
discardfile ./dirrpt/rep2.dsc, append, megabytes 50
assumetargetdefs
allownoopupdates
numfiles 3000
gettruncates
map roger.*, target roger.*;
~
~
"dirprm/rep2.prm" [New] 13L, 244C written
GGSCI (oracleplus.net) 2> add replicat rep2, exttrail ./dirdat/rm
REPLICAT added.
GGSCI (oracleplus.net) 3>
6. 同步数据
---先用imp同步下数据
[ora10g@killdb ~]$ imp roger/roger file=roger.dmp fromuser=roger touser=roger
Import: Release 10.2.0.5.0 - Production on Sun Aug 19 04:44:25 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. . importing table "IND_T" 2 rows imported
.....省略部分信息
. . importing table "TEST_OGG" 100 rows imported
IMP-00015: following statement failed because the object already exists:
"CREATE SYNONYM "V$PARAMETER" FOR "SYS"."SHOW_HIDDEN_V$PARAMETER""
Import terminated successfully with warnings.
由于我这里配置2个replicat进程参数都是一样的，所以我这里直接拷贝rep1上面的/dirdat/* 文件到rep2机器上，如下：
-bash-3.2$ scp * 192.168.0.115:/home/ggs/ggs_home/dirdat
The authenticity of host '192.168.0.115 (192.168.0.115)' can't be established.
RSA key fingerprint is e5:b6:56:0a:9f:a5:d6:e6:7d:ad:2d:f4:dc:41:2c:25.
Are you sure you want to continue connecting (yes/no) yes
Warning: Permanently added '192.168.0.115' (RSA) to the list of known hosts.
ggs@192.168.0.115's password:
rm000000 100% 1146 1.1KB/s 00:00
rm000001 100% 28KB 27.9KB/s 00:00
rm000002 100% 1215 1.2KB/s 00:00
rm000003 100% 1250 1.2KB/s 00:00
这里要说明一下，ogg的初始化方式有很多种，后面会进行总结，这里不多说。
7. 启动replicat 进程
启动rep2进程：
GGSCI (oracleplus.net) 53> start rep2
Sending START request to MANAGER ...
REPLICAT REP2 starting
GGSCI (oracleplus.net) 54> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT ABENDED REP2 00:00:00 00:00:07
开始始终无法启动，通过info report 可以看到rep2是从extseq 0开始读的，显示这是不一致的。
GGSCI (oracleplus.net) 43> info rep2,detail
REPLICAT REP2 Last Started 2012-08-19 04:55 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:16:04 ago)
Log Read Checkpoint File ./dirdat/rm000000
First Record RBA 0
Extract Source Begin End
./dirdat/rm000000 * Initialized * First Record
./dirdat/rm000000 * Initialized * First Record
./dirdat/rm000000 * Initialized * First Record
./dirdat/rm000000 * Initialized * First Record
./dirdat/rm000000 * Initialized * First Record
Current directory /home/ggs/ggs_home
Report file /home/ggs/ggs_home/dirrpt/REP2.rpt
Parameter file /home/ggs/ggs_home/dirprm/rep2.prm
Checkpoint file /home/ggs/ggs_home/dirchk/REP2.cpr
Checkpoint table ggs.GGSCHKPT
Process file /home/ggs/ggs_home/dirpcs/REP2.pcr
Stdout file /home/ggs/ggs_home/dirout/REP2.out
Error log /home/ggs/ggs_home/ggserr.log
由于要保持一致，所以这里对比下rep2 机器上的信息即可：
GGSCI (11gR2test) 17> info rep1
REPLICAT REP1 Last Started 2012-08-18 22:10 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint File ./dirdat/rm000003
2012-08-19 04:52:07.295446 RBA 1250
可以看到目前extseq 为3，所以可以这样进行启动rep2进程，如下：
GGSCI (oracleplus.net) 59> alter REPLICAT rep2,EXTSEQNO 3;
REPLICAT altered.
GGSCI (oracleplus.net) 61> start rep2
Sending START request to MANAGER ...
REPLICAT REP2 starting
GGSCI (oracleplus.net) 62> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP2 00:00:00 00:00:09
说明，最后感觉还是有些问题，虽然进程能起来，发现目标端1少了个，不知道为什么，我这里重新把目标端1和目标端2初始化了一下，仍然是用的imp，不多说了。
8. check 数据一致性
----源端
SQL> show user
USER is "ROGER"
SQL> delete from test_ogg where rownum < 101;
100 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from roger.test_ogg;
COUNT(*)
----------
49805
GGSCI (10gasm) 53> send extract dp1 ,ROLLOVER
Sending ROLLOVER request to EXTRACT DP1 ...
Request processed.
GGSCI (10gasm) 54> send extract dp2 ,ROLLOVER
Sending ROLLOVER request to EXTRACT DP2 ...
Request processed.
GGSCI (10gasm) 55> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:00:04
EXTRACT RUNNING DP2 00:00:00 00:00:03
EXTRACT RUNNING EXT1 00:00:00 00:00:06
---目标端1
GGSCI (11gR2test) 22> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:09
GGSCI (11gR2test) 23>
GGSCI (11gR2test) 23> info rep1
REPLICAT REP1 Last Started 2012-08-19 06:09 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:00 ago)
Log Read Checkpoint File ./dirdat/rm000005
2012-08-19 06:10:52.085889 RBA 1203
SQL> l
1* select count(*) from roger.test_ogg
SQL> /
COUNT(*)
----------
49905
----目标端1 这里数据没有变化，但是看信息却是应用了的，很奇怪。
GGSCI (11gR2test) 47> stats rep1
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2012-08-19 06:35:09.
Replicating from ROGER.TEST_OGG to ROGER.TEST_OGG:
*** Total statistics since 2012-08-19 06:09:10 ***
Total inserts 100.00
Total updates 0.00
Total deletes 100.00
Total discards 0.00
Total operations 2http://www.oracleplus.net00.00
*** Daily statistics since 2012-08-19 06:09:10 ***
Total inserts 100.00
Total updates 0.00
Total deletes 100.00
Total discards 0.00
Total operations 200.00
*** Hourly statistics since 2012-08-19 06:09:10 ***
Total inserts 100.00
Total updates 0.00
Total deletes 100.00
Total discards 0.00
Total operations 200.00
*** Latest statistics since 2012-08-19 06:09:10 ***
Total inserts 100.00
Total updates 0.00
Total deletes 100.00
Total discards 0.00
Total operations 200.00
End of Statistics.
---目标端2
[ora10g@killdb ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.5.0 - Production on Sun Aug 19 06:14:01 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count(*) from roger.test_ogg;
COUNT(*)
----------
49805
GGSCI (oracleplus.net) 102> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP2 00:00:00 00:00:01
GGSCI (oracleplus.net) 103> info rep2
REPLICAT REP2 Last Started 2012-08-19 06:09 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint File ./dirdat/rm000005
2012-08-19 06:10:52.239906 RBA 1203
9. 再次测试
--源端
SQL> show user
USER is "ROGER"
SQL> truncate table test_ogg;
Table truncated.
---目标端1
GGSCI (11gR2test) 48> stats rep1
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2012-08-19 06:35:58.
Replicating from ROGER.TEST_OGG to ROGER.TEST_OGG:
*** Total statistics since 2012-08-19 06:09:10 ***
Total inserts 100.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 201.00
*** Daily statistics since 2012-08-19 06:09:10 ***
Total inserts 100.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 201.00
*** Hourly statistics since 2012-08-19 06:09:10 ***
Total inserts 100.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 201.00
*** Latest statistics since 2012-08-19 06:09:10 ***
Total inserts 100.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 201.00
End of Statistics.
SQL> l
1* select count(*) from roger.test_ogg
SQL> /
COUNT(*)
----------
0
---目标端2
GGSCI (oracleplus.net) 107> stats rep2
Sending STATS request to REPLICAT REP2 ...
Start of Statistics at 2012-08-19 06:35:51.
Replicating from ROGER.TEST_OGG to ROGER.TEST_OGG:
*** Total statistics since 2012-08-19 06:11:09 ***
Total inserts 0.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 101.00
*** Daily statistics since 2012-08-19 06:11:09 ***
Total inserts 0.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 101.00
*** Hourly statistics since 2012-08-19 06:11:09 ***
Total inserts 0.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 101.00
*** Latest statistics since 2012-08-19 06:11:09 ***
Total inserts 0.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 101.00
End of Statistics.
SQL> /
COUNT(*)
----------
0
10. 最后一次测试
---源端
SQL> insert into test_ogg select * from sys.dba_objects;
49906 rows created.
SQL> commit;
Commit complete.
SQL> delete from test_ogg where rownum < 1001;
1000 rows deleted.
SQL> commit;
Commit complete.
---目标端1
GGSCI (11gR2test) 49> stats rep1
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2012-08-19 06:38:22.
Replicating from ROGER.TEST_OGG to ROGER.TEST_OGG:
*** Total statistics since 2012-08-19 06:09:10 ***
Total inserts 34315.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 34416.00
*** Daily statistics since 2012-08-19 06:09:10 ***
Total inserts 34315.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 34416.00
*** Hourly statistics since 2012-08-19 06:09:10 ***
Total inserts 34315.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 34416.00
*** Latest statistics since 2012-08-19 06:09:10 ***
Total inserts 34315.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 34416.00
End of Statistics.
GGSCI (11gR2test) 50> stats rep1
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2012-08-19 06:41:02.
Replicating from ROGER.TEST_OGG to ROGER.TEST_OGG:
*** Total statistics since 2012-08-19 06:09:10 ***
Total inserts 50006.00
Total updates 0.00
Total deletes 1100.00
Total truncates 1.00
Total discards 0.00
Total operations 51107.00
*** Daily statistics since 2012-08-19 06:09:10 ***
Total inserts 50006.00
Total updates 0.00
Total deletes 1100.00
Total truncates 1.00
Total discards 0.00
Total operations 51107.00
*** Hourly statistics since 2012-08-19 06:09:10 ***
Total inserts 50006.00
Total updates 0.00
Total deletes 1100.00
Total truncates 1.00
Total discards 0.00
Total operations 51107.00
*** Latest statistics since 2012-08-19 06:09:10 ***
Total inserts 50006.00
Total updates 0.00
Total deletes 1100.00
Total truncates 1.00
Total discards 0.00
Total operations 51107.00
End of Statistics.
SQL> select count(*) from roger.test_ogg
2 /
COUNT(*)
----------
49906
SQL> l
1* select count(*) from roger.test_ogg
SQL> /
COUNT(*)
----------
48906
---目标端2
GGSCI (oracleplus.net) 108> stats rep2
Sending STATS request to REPLICAT REP2 ...
Start of Statistics at 2012-08-19 06:39:39.
Replicating from ROGER.TEST_OGG to ROGER.TEST_OGG:
*** Total statistics since 2012-08-19 06:11:09 ***
Total inserts 49906.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 50007.00
*** Daily statistics since 2012-08-19 06:11:09 ***
Total inserts 49906.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 50007.00
*** Hourly statistics since 2012-08-19 06:11:09 ***
Total inserts 49906.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 50007.00
*** Latest statistics since 2012-08-19 06:11:09 ***
Total inserts 49906.00
Total updates 0.00
Total deletes 100.00
Total truncates 1.00
Total discards 0.00
Total operations 50007.00
End of Statistics.
GGSCI (oracleplus.net) 109> stats rep2
Sending STATS request to REPLICAT REP2 ...
Start of Statistics at 2012-08-19 06:40:50.
Replicating from ROGER.TEST_OGG to ROGER.TEST_OGG:
*** Total statistics since 2012-08-19 06:11:09 ***
Total inserts 49906.00
Total updates 0.00
Total deletes 1100.00
Total truncates 1.00
Total discards 0.00
Total operations 51007.00
*** Daily statistics since 2012-08-19 06:11:09 ***
Total inserts 49906.00
Total updates 0.00
Total deletes 1100.00
Total truncates 1.00
Total discards 0.00
Total operations 51007.00
*** Hourly statistics since 2012-08-19 06:11:09 ***
Total inserts 49906.00
Total updates 0.00
Total deletes 1100.00
Total truncates 1.00
Total discards 0.00
Total operations 51007.00
*** Latest statistics since 2012-08-19 06:11:09 ***
Total inserts 49906.00
Total updates 0.00
Total deletes 1100.00
Total truncates 1.00
Total discards 0.00
Total operations 51007.00
End of Statistics.
SQL> l
1* select count(*) from roger.test_ogg
SQL> /
COUNT(*)
----------
49906
SQL> l
1* select count(*) from roger.test_ogg
SQL> /
COUNT(*)
----------
48906
最后经过多轮测试，发现一切ok。
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle goldengate 一对多复制的配置过程笔记
本文由大师惜分飞原创分享，网址：http://www.oracleplus.net/arch/1387.html
