sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置: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-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle goldengate 一对多复制的配置过程笔记

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1387.html

Oracle研究中心

关键词:

Oracle Goldengate

Oracle GG一对多的复制配置方法

Oracle goldengate 支持ddl复制配置笔记