因数据库跨平台迁移，以下为Oracle GoldenGate搭建测试过程，分享一下。  1.ogg解压安装 <o:p></o:p> oracle@card:/opt/app/oracle/product$ ls -ll total 90252 drwxr-xr-x   2 oracle   oinstall     512 Mar 24 13:56 ogg -rw-r--r--   1 oracle   oinstall 46165710 Mar 20 16:33 ogg112101_fbo_ggs_Solaris_sparc_ora10g_64bit.zip oracle@card:/opt/app/oracle/product$ unzip ogg112101_fbo_ggs_Solaris_sparc_ora10g_64bit.zip Archive:  ogg112101_fbo_ggs_Solaris_sparc_ora10g_64bit.zip   inflating: fbo_ggs_Solaris_sparc_ora10g_64bit.tar    inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf    inflating: Oracle GoldenGate 11.2.1.0.1 README.doc    inflating: Oracle GoldenGate 11.2.1.0.1 README.txt  oracle@card:/opt/app/oracle/product$ ls -l total 331444 drwxr-xr-x  63 oracle   oinstall    1536 Jun 28  2007 10.2 drwxr-xr-x  64 oracle   oinstall    1536 Dec 21  2010 10.2.5 -rw-r--r--   1 oracle   oinstall  220546 Apr 27  2012 OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf -rw-r--r--   1 oracle   oinstall   93696 May  1  2012 Oracle GoldenGate 11.2.1.0.1 README.doc -rw-r--r--   1 oracle   oinstall   24390 May  1  2012 Oracle GoldenGate 11.2.1.0.1 README.txt drwxr-xr-x  51 oracle   oinstall    1024 Mar 18  2013 agent10g -rw-rw-r--   1 oracle   oinstall 123064320 Apr 24  2012 fbo_ggs_Solaris_sparc_ora10g_64bit.tar drwxr-xr-x   2 oracle   oinstall     512 Mar 24 13:56 ogg -rw-r--r--   1 oracle   oinstall 46165710 Mar 20 16:33 ogg112101_fbo_ggs_Solaris_sparc_ora10g_64bit.zip<o:p></o:p> oracle@card:/opt/app/oracle/product$ tar -xvf fbo_ggs_Solaris_sparc_ora10g_64bit.tar -C ogg/<o:p></o:p>   2.创建ogg管理目录(目标，源数据库)<o:p></o:p> oracle@card:/opt/app/oracle/product/ogg$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO Solaris, sparc, 64bit (optimized), Oracle 10g on Apr 24 2012 09:06:57 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (card.cyou.com) 1> create subdirs Creating subdirectories under current directory /opt/app/oracle/product/ogg Parameter files                /opt/app/oracle/product/ogg/dirprm: already exists Report files                   /opt/app/oracle/product/ogg/dirrpt: created Checkpoint files               /opt/app/oracle/product/ogg/dirchk: created Process status files           /opt/app/oracle/product/ogg/dirpcs: created SQL script files               /opt/app/oracle/product/ogg/dirsql: created Database definitions files     /opt/app/oracle/product/ogg/dirdef: created Extract data files             /opt/app/oracle/product/ogg/dirdat: created Temporary files                /opt/app/oracle/product/ogg/dirtmp: created Stdout files                   /opt/app/oracle/product/ogg/dirout: created 各目录用途如下表： 名字用途 dirprm 存放 OGG 参数各的配置信息 dirrpt 存放进程报告文件 dirchk 存放检查点文件 dirpcs 存放进程状态文件 dirsql 存放 SQL 脚本文件 dirdef 存放 DEFGEN 工具生成的数据定义文件  dirdat 存放 Trail 文件，也就是 Capture 进程捕获的日志文件 dirtmp 当事物需要的内存超过已分配内存时，默认存储在这个目录  <o:p></o:p>   3.建立OGG管理用户<o:p></o:p> 源数据库<o:p></o:p> sys@GCDB> create tablespace ogg datafile '/data/app/oracle/oradata/gcdb/ogg01_data.dbf' size 50M autoextend on; Tablespace created. sys@GCDB> create user ogg identified by ogg default tablespace ogg; User created. sys@GCDB> grant dba,CONNECT, RESOURCE to ogg; Grant succeeded.<o:p></o:p> 目标数据库<o:p></o:p> SQL> create tablespace ogg datafile '/U01/app/oracle/oradata/gcdb/ogg01_data.dbf' size 50M autoextend on; Tablespace created. SQL> create user ogg identified by ogg default tablespace ogg; User created. SQL> grant dba,CONNECT, RESOURCE to ogg; Grant succeeded.<o:p></o:p>   4.源数据库日志设置<o:p></o:p>   sys@GCDB>  select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database; NAME      OPEN_MODE  FOR SUPPLEME --------- ---------- --- -------- GCDB      READ WRITE NO  NO sys@GCDB> alter database force logging; Database altered. sys@GCDB> alter database add SUPPLEMENTAL log data; Database altered. sys@GCDB> select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database; NAME      OPEN_MODE  FOR SUPPLEME --------- ---------- --- -------- GCDB      READ WRITE YES YES<o:p></o:p>     5.源数据库建立测试数据表<o:p></o:p> sys@GCDB> create table tlbb.swd_drawweapon_25 as select * from tlbb.swd_drawweapon; Table created. sys@GCDB> alter table TLBB.SWD_DRAWWEAPON_25 add primary key (WEAPONLISTNO, ITEMID); Table altered.<o:p></o:p>   sys@GCDB> select count(*) from TLBB.SWD_DRAWWEAPON_25 ;   COUNT(*) ----------       3615<o:p></o:p>   6.目标数据库建立表，无数据<o:p></o:p>   SQL> create table scott.SWD_DRAWWEAPON_25   2  (   3    WEAPONLISTNO NUMBER(6) not null,   4    ITEMID       VARCHAR2(20) not null,   5    COUNT        NUMBER(4),   6    RATE         NUMBER(5),   7    DES          VARCHAR2(50),   8    TYPE         VARCHAR2(40)   9  ); Table created. SQL> alter table scott.SWD_DRAWWEAPON_25 add primary key (WEAPONLISTNO, ITEMID); Table altered. SQL> select count(*) from scott.SWD_DRAWWEAPON_25;   COUNT(*) ----------          0<o:p></o:p>   7.源数据库添加TRANDATA<o:p></o:p>   oracle@card:/opt/app/oracle/product/ogg$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO Solaris, sparc, 64bit (optimized), Oracle 10g on Apr 24 2012 09:06:57 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (card.cyou.com) 1> dblogin userid ogg,password ogg Successfully logged into database. GGSCI (card.cyou.com) 2> add trandata tlbb.SWD_DRAWWEAPON_25 Logging of supplemental redo data enabled for table TLBB.SWD_DRAWWEAPON_25.<o:p></o:p>     8.源数据库启动管理进程<o:p></o:p>   GGSCI (card.cyou.com) 6> edit params mgr "/opt/app/oracle/product/ogg/dirprm/mgr.prm" [New file] port 7809 purgeoldextracts /opt/app/oracle/product/ogg/dirdat,usecheckpoints<o:p></o:p>   GGSCI (card.cyou.com) 7> start mgr   Manager started. GGSCI (card.cyou.com) 8> info mgr Manager is running (IP port card.cyou.com.7809).<o:p></o:p> 9.配置源数据库初始化进程<o:p></o:p>   GGSCI (card.cyou.com) 9> ADD EXTRACT e_init, SOURCEISTABLE<o:p></o:p> EXTRACT added. GGSCI (card.cyou.com) 10> info extract *,tasks EXTRACT    EINT_1    Initialized   2014-03-25 15:10   Status STOPPED Checkpoint Lag       Not Available Log Read Checkpoint  Not Available                      First Record         Record 0 Task                 SOURCEISTABLE GGSCI (card.cyou.com) 11> edit params e_init "/opt/app/oracle/product/ogg/dirprm/einit_1.prm" [New file] EXTRACT e_init<o:p></o:p> USERID ogg, PASSWORD ogg RMTHOST 10.127.2.143, MGRPORT 7809 RMTTASK replicat, GROUP r_init TABLE TLBB.SWD_DRAWWEAPON_25;<o:p></o:p>     10.配置目标数据库replicat进程<o:p></o:p>   GGSCI (scard.cyou.com) 6> edit params rinit_1 REPLICAT r_init USERID ogg, PASSWORD ogg ASSUMETARGETDEFS MAP tlbb.SWD_DRAWWEAPON_25, TARGET scott.SWD_DRAWWEAPON_25;<o:p></o:p>   11.启动源数据库进程<o:p></o:p> GGSCI (card.cyou.com) 24> start extract einit_1 Sending START request to MANAGER ... EXTRACT EINIT_1 starting<o:p></o:p> GGSCI (card.cyou.com) 16> info extract e_init<o:p></o:p>   EXTRACT    E_INIT    Last Started 2014-03-26 13:24   Status STOPPED<o:p></o:p> Checkpoint Lag       Not Available<o:p></o:p> Log Read Checkpoint  Table TLBB.SWD_DRAWWEAPON_25<o:p></o:p>                      2014-03-26 13:24:39  Record 3615<o:p></o:p> Task                 SOURCEISTABLE<o:p></o:p>   12.验证目标数据库数据是否同步<o:p></o:p>   SQL> select count(*) from scott.SWD_DRAWWEAPON_25 ;   COUNT(*) ----------       3615<o:p></o:p>   13.配置检查点（源端与目标端都需要配置）<o:p></o:p> GGSCI (card.cyou.com) 18> EDIT PARAMS ./GLOBALS<o:p></o:p>   GGSCHEMA ogg CHECKPOINTTABLE ogg.CHKPTAB GGSCI (card.cyou.com) 1> dblogin userid ogg,password ogg  Successfully logged into database. GGSCI (card.cyou.com) 2> ADD CHECKPOINTTABLE No checkpoint table specified, using GLOBALS specification (ogg.CHKPTAB)... Successfully created checkpoint table ogg.CHKPTAB. GGSCI (card.cyou.com) 3> exit oracle@card:/opt/app/oracle/product/ogg$ sqlplus ogg/ogg SQL*Plus: Release 10.2.0.3.0 - Production on Wed Mar 26 16:54:24 2014 Copyright (c) 1982, 2006, Oracle.  All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning and Data Mining options ogg@GCDB> desc CHKPTAB Name                                                  Null?    Type ----------------------------------------------------- -------- ------------------------------------ GROUP_NAME                                            NOT NULL VARCHAR2(8) GROUP_KEY                                             NOT NULL NUMBER(19) SEQNO                                                          NUMBER(10) RBA                                                   NOT NULL NUMBER(19) AUDIT_TS                                                       VARCHAR2(29) CREATE_TS                                             NOT NULL DATE LAST_UPDATE_TS                                        NOT NULL DATE CURRENT_DIR                                           NOT NULL VARCHAR2(255) LOG_CSN                                                        VARCHAR2(129) LOG_XID                                                        VARCHAR2(129) LOG_CMPLT_CSN                                                  VARCHAR2(129) LOG_CMPLT_XIDS                                                 VARCHAR2(2000) VERSION                                                        NUMBER(3)<o:p></o:p>   14.配置捕获进程<o:p></o:p> GGSCI (card.cyou.com) 26> EDIT PARAMS EORA_1 "/opt/app/oracle/product/ogg/dirprm/eora_1.prm" 4 lines, 117 characters  EXTRACT EORA_1 USERID ogg, PASSWORD ogg EXTTRAIL /opt/app/oracle/product/ogg/dirdat/ss TABLE tlbb.SWD_DRAWWEAPON_25;<o:p></o:p>   GGSCI (card.cyou.com) 3>  ADD EXTRACT EORA_1, TRANLOG, BEGIN NOW EXTRACT added. GGSCI (card.cyou.com) 5> ADD EXTTRAIL /opt/app/oracle/product/ogg/dirdat/ss, EXTRACT EORA_1 EXTTRAIL added.<o:p></o:p>   GGSCI (card.cyou.com) 6> START EXTRACT EORA_1<o:p></o:p> Sending START request to MANAGER ... EXTRACT EORA_1 starting GGSCI (card.cyou.com) 7> INFO EXTRACT EORA_1 EXTRACT    EORA_1    Initialized   2014-03-26 17:22   Status STOPPED Checkpoint Lag       00:00:00 (updated 00:00:52 ago) Log Read Checkpoint  Oracle Redo Logs                      2014-03-26 17:22:45  Seqno 0, RBA 0                      SCN 0.0 (0)<o:p></o:p> 15.配置源端PUMP传输进程<o:p></o:p> GGSCI (card.cyou.com) 10> EDIT PARAMS PORA_1 "/opt/app/oracle/product/ogg/dirprm/pora_1.prm" [New file]  EXTRACT PORA_1  PASSTHRU  RMTHOST 10.127.2.143, MGRPORT 7809  RMTTRAIL  /U01/app/oracle/product/ogg/dirdat/ss  TABLE tlbb.SWD_DRAWWEAPON_25;<o:p></o:p> GGSCI (card.cyou.com) 12> ADD EXTRACT PORA_1, EXTTRAILSOURCE /opt/app/oracle/product/ogg/dirdat/ss  EXTRACT added. GGSCI (card.cyou.com) 80> ADD RMTTRAIL /U01/app/oracle/product/ogg/dirdat/ss, EXTRACT PORA_1 RMTTRAIL added. GGSCI (card.cyou.com) 38>  START EXTRACT PORA_1 Sending START request to MANAGER ... EXTRACT PORA_1 starting GGSCI (card.cyou.com) 39> INFO EXTRACT PORA_1 EXTRACT    PORA_1    Last Started 2014-03-27 10:01   Status RUNNING Checkpoint Lag       00:00:00 (updated 00:01:09 ago) Log Read Checkpoint  File /opt/app/oracle/product/ogg/dirdat/ss000000                      First Record  RBA 0<o:p></o:p>   16.目标端配置同步进程<o:p></o:p>   GSCI (scard.cyou.com) 15>  ADD REPLICAT RORA_1, EXTTRAIL /U01/app/oracle/product/ogg/dirdat/ss REPLICAT added. GGSCI (scard.cyou.com) 16> EDIT PARAMS RORA_1  REPLICAT RORA_1 USERID ogg, PASSWORD ogg ASSUMETARGETDEFS MAP tlbb.SWD_DRAWWEAPON_25, TARGET scott.SWD_DRAWWEAPON_25;<o:p></o:p>   GGSCI (scard.cyou.com) 24> start REPLICAT RORA_1 Sending START request to MANAGER ... REPLICAT RORA_1 starting GGSCI (scard.cyou.com) 25> info all Program     Status      Group       Lag at Chkpt  Time Since Chkpt MANAGER     RUNNING                                            REPLICAT    RUNNING     RORA_1      00:00:00      00:00:07  <o:p></o:p>   17.验证数据同步<o:p></o:p> 源端<o:p></o:p> sys@GCDB>  select * from tlbb.SWD_DRAWWEAPON_25 t where t.weaponlistno=980; WEAPONLISTNO ITEMID                    COUNT       RATE DES                            TYPE ------------ -------------------- ---------- ---------- ------------------------------ ------------------------------          980 setflag 1500 1                1      10000 CDKEY sys@GCDB> update tlbb.SWD_DRAWWEAPON_25 t set count=8888 where t.weaponlistno=980 ; 1 row updated. sys@GCDB> commit; Commit complete.<o:p></o:p>   目标端<o:p></o:p> SQL>  select * from scott.SWD_DRAWWEAPON_25 t where t.weaponlistno=980; WEAPONLISTNO ITEMID                    COUNT       RATE DES                                                TYPE ------------ -------------------- ---------- ---------- -------------------------------------------------- ----------------------------------------          980 setflag 1500 1             8888      10000 CDKEY<o:p></o:p>

ITPub博客

GoldenGate 实战

最新文章