在配置GoldenGate环境时,刚开始的时候,我们总是需要先初始化把Source Database中需要复制的表的数据先load到目标库中,除非Source Database是一个新建的库或需要复制的对象中还没有任何数据.
GoldenGate中支持的data load 方法有
Loading data with a database utility
Loading data from file to Replicat
Loading data from file to database utility
Loading data with a GoldenGate direct load
Loading data with a direct bulk load to SQL*Loader
Loading tables with Teradata table copy
Loading data with Teradata load utilities
从DBA的角度出发,我个人认为在初始化时根据实际情况采用Database Utility或借助第三方工具和应用程序可能是最快最方便的方式,当然了,根据你对数据库和GoldenGate的熟悉程序,选择一种最合适的你的方法。
下面我将用GoldenGate的Direct Load的方法实现数据的同步,具体步骤如下:
1、首先启动MGR
Start mgr
当进程管理器正常启动后,会创建一个GoldenGate事件日志名为ggserr.log,可以通过命令来查看ERR LOG:
VIEW GGSEVT
2、创建原表
SQL> conn ggs/ggs
Connected.
SQL> create table test as select * from sys.all_objects;
Table created.
SQL> insert into test select * from sys.all_objects;
58898 rows created.
SQL> /
58897 rows created.
SQL> select count(*) from test;
COUNT(*)
----------
176694
在目标端创建一个结构与原表相同的表
SQL> conn ggt/ggt
Connected.
SQL> drop table test;
Table dropped.
SQL> create table test as select * from sys.all_objects where 2=1;
Table created.
SQL>
3、为源表添加Supplemental Logging
GGSCI (gctest1) 7> dblogin userid ggate , password ggate
Successfully logged into database.
GGSCI (gctest1) 8> add trandata ggs.test
2010-09-10 17:02:43 GGS WARNING 109 No unique key is defined for table TEST. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table GGS.TEST.
GGSCI (gctest1) 9> info trandata ggs.test
Logging of supplemental redo log data is enabled for table GGS.TEST
4、添加初始化组
原端:初始化Load capture batch task group
GGSCI (gctest1) 12> add extract extinit, sourceistable
EXTRACT added.
GGSCI (gctest1) 13> info extract *,task
EXTRACT EXTINIT Initialized 2010-09-10 17:06 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Not Available
First Record Record 0
Task SOURCEISTABLE
GGSCI (gctest1) 14> edit params extinit
extract extinit
userid ggate,password ggate
rmthost gctest3 ,mgrport 10002
rmttask replicat ,group repinit
table ggs.test;
目标端:增加一个初始加载的delivery batch task
GGSCI (gctest3) 8> add replicat repinit,specialrun
REPLICAT added.
GGSCI (gctest3) 9> info params repinit ,tasks
ERROR: Invalid command.
GGSCI (gctest3) 10> info replicat * , tasks
REPLICAT REPINIT Initialized 2010-09-10 17:18 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:00:35 ago)
Log Read Checkpoint Not Available
Task SPECIALRUN
GGSCI (gctest3) 13> edit params repinit
replicat repinit
assumetargetdefs
userid ggate,password ggate
discardfile ./dirrpt/repinit_gg.dsc, purge
map ggs.test, target ggt.test;
在原端启动服务:
GGSCI (gctest1) 35> start extract extinit
GGSCI (gctest1) 38> view report extinit
2010-09-10 17:41:03 GGS INFO 414 Wildcard resolution set to IMMEDIATE b
ecause SOURCEISTABLE is used.
***********************************************************************
Oracle GoldenGate Capture for Oracle
Version 10.4.0.19 Build 002
Linux, x86, 32bit (optimized), Oracle 10 on Sep 18 2009 00:01:59
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
Starting at 2010-09-10 17:41:03
***********************************************************************
Operating System Version:
Linux
Version #1 Fri Feb 24 16:44:51 EST 2006, Release 2.6.9-34.EL
Node: gctest1
Machine: i686
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 9278
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
EXTRACT EXTINIT
USERID GGATE, PASSWORD *****
RMTHOST gctest3, MGRPORT 10002
RMTTASK REPLICAT, GROUP REPINIT
TABLE ggs.test;
2010-09-10 17:41:10 GGS WARNING 109 No unique key is defined for table TES
T. All viable columns will be used to represent the key, but may not guarantee u
niqueness. KEYCOLS may be used to define the key.
Using the following key columns for source table GGS.TEST: OWNER, OBJECT_NAME, S
UBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, T
IMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY.
CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 2G
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 2.92G
CACHESIZEMAX (strict force to disk): 2.68G
Database Version:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Database Language and Character Set:
NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK"
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "ZHS16GBK"
Processing table GGS.TEST
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Report at 2010-09-10 17:47:34 (activity since 2010-09-10 17:41:10)
Output to REPINIT:
From Table GGS.TEST:
# inserts: 176694
# updates: 0
# deletes: 0
# discards: 0
GGSCI (gctest1) 39>
GSCI (gctest3) 19> view report repinit
***********************************************************************
Oracle GoldenGate Delivery for Oracle
Version 10.4.0.19 Build 002
Linux, x86, 32bit (optimized), Oracle 10 on Sep 18 2009 00:08:30
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
Starting at 2010-09-10 17:41:20
***********************************************************************
Operating System Version:
Linux
Version #1 Fri Feb 24 16:44:51 EST 2006, Release 2.6.9-34.EL
Node: gctest3
Machine: i686
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 8778
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
replicat repinit
assumetargetdefs
userid ggate,password *****
discardfile ./dirrpt/repinit_gg.dsc, purge
map ggs.test, target ggt.test;
CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 512M
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 1G
CACHESIZEMAX (strict force to disk): 881M
Database Version:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Database Language and Character Set:
NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK"
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "ZHS16GBK"
For further information on character set settings, please refer to user manual.
***********************************************************************
** Run Time Messages **
***********************************************************************
MAP resolved (entry GGS.TEST):
map GGS.TEST, target ggt.test;
2010-09-10 17:41:32 GGS WARNING 109 No unique key is defined for table TES
T. All viable columns will be used to represent the key, but may not guarantee u
niqueness. KEYCOLS may be used to define the key.
Using following columns in default map by name:
OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
Using the following key columns for target table GGT.TEST: OWNER, OBJECT_NAME, S
UBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, T
IMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY.
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Report at 2010-09-10 17:47:24 (activity since 2010-09-10 17:41:32)
From Table GGS.TEST to GGT.TEST:
# inserts: 176694
# updates: 0
# deletes: 0
# discards: 0
GGSCI (gctest3) 20>
原则上原端和目标端的表必须存在主键不然会报上述的ERROR。
检查目标端表的数据:
SQL> conn ggt/ggt
Connected.
SQL> select count(*) from test;
COUNT(*)
----------
176694
与目标端数据一致。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7882490/viewspace-673190/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7882490/viewspace-673190/