环境:11.2.0.3+OEL5.7
192.168.1.55zlm sid:zlm11g
192.168.1.60zlm2 sid:zlm11g
gg软件包:ogg112101_fbo_ggs_Linux_x64_ora11g_64bit
一、安装OGG软件并配置实验环境
*******
源主库:
*******
1.把gg软件包复制到源主机,2次解压到gg安装目录gg11
[oracle@zlmdb_1]$ cd $OACLE_BASE
[oracle@zlm~]$ ls
incremental_hot_database_backup.sh ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
incremental_hot_database_backup.sh.out orz.sh
[oracle@zlmoracle]$ mkdir gg11
[oracle@zlmoracle]$ cd gg11
[oracle@zlmgg11]$ unzip /home/oracle/ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[oracle@zlmgg11]$ ll
total223764
-rw-rw-r--1 oracle oinstall 228556800 Apr 23 2012fbo_ggs_Linux_x64_ora11g_64bit.tar
-rwxrwxrwx1 oracle oinstall 220546 May 2 2012OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
-rwxrwxrwx1 oracle oinstall 93696 May 2 2012Oracle GoldenGate 11.2.1.0.1README.doc
-rwxrwxrwx1 oracle oinstall 24390 May 2 2012Oracle GoldenGate 11.2.1.0.1README.txt
[oracle@zlmgg11]$ tar xvoffbo_ggs_Linux_x64_ora11g_64bit.tar
UserExitExamples/
UserExitExamples/ExitDemo_more_recs/
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARIS
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.LINUX
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.AIX
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.vcproj
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.c
UserExitExamples/ExitDemo_more_recs/readme.txt
.............
2.修改环境变量文件.bash_profile,加入OGG的环境变量
export GGHOME=/$ORACLE_BASE/gg11
exportPATH=$ORACLE_BASE/gg11:$ORACLE_HOME/bin:/usr/bin/:$PATH
[oracle@zlmgg11]$ . ~/.bash_profile
[oracle@zlmgg11]$ ggsci
ggsci:error while loading shared libraries: libnnz11.so: cannot open shared objectfile: No such file or directory
由于没有设置LD_LIBRARY_PATH环境变量,所以无法执行ggsci,重新添加到.bash_profile
在环境变量中添加如下命令:
export LD_LIBLARY_PATH=$ORACLE_BASE/gg11:$ORACLE_HOME/lib:$ORACLE_HOME/bin
重新source一下后执行ggsci
[oracle@zlmgg11]$ . ~/.bash_profile
[oracle@zlmgg11]$ ggsci
OracleGoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux,x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright(C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
3.创建OGG专用目录subdirs
GGSCI (zlm)1> create subdirs
Creatingsubdirectories under current directory /u01/app/oracle/gg11
Parameterfiles /u01/app/oracle/gg11/dirprm: already exists
Reportfiles /u01/app/oracle/gg11/dirrpt: created
Checkpointfiles /u01/app/oracle/gg11/dirchk:created
Processstatus files /u01/app/oracle/gg11/dirpcs: created
SQLscript files /u01/app/oracle/gg11/dirsql: created
Databasedefinitions files /u01/app/oracle/gg11/dirdef: created
Extractdata files /u01/app/oracle/gg11/dirdat: created
Temporaryfiles /u01/app/oracle/gg11/dirtmp: created
Stdoutfiles /u01/app/oracle/gg11/dirout: created
4.对源数据库设置补充日志,并开启归档模式和强制归档
SQL>alter database add supplemental log data;
Databasealtered.
SQL>alter system switch logfile;
Systemaltered.
SQL>select supplemental_log_data_min,log_mode,force_logging from v$database;
SUPPLEMELOG_MODE FOR
-------------------- ---
YES ARCHIVELOG NO
SQL> alterdatabase force logging;
Databasealtered.
SQL>select supplemental_log_data_min,log_mode,force_loggingfrom v$database;
SUPPLEMELOG_MODE FOR
-------------------- ---
YES ARCHIVELOG YES
5.编辑源数据库管理进程参数文件
GGSCI(zlm) 1> edit params mgr
Cannotload ICU resource bundle 'ggMessage', error code 2 - No such file or directory
Cannotload ICU resource bundle 'ggMessage', error code 2 - No such file or directory
Aborted
这里报错是因为之前进入ggsci界面是并没有在OGG安装目录,必须要在$GGHOME(如果配置过的话,我这里$GGHOME=/u01/app/oracle/gg11)
[oracle@zlm~]$ cd $ORACLE_BASE/gg11
[oracle@zlmgg11]$ ggsci
OracleGoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux,x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright(C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI(zlm) 1> edit params mgr
在mgr中添加入如下一行:(和vi编辑器一样操作)
PORT 7809
6.启动管理进程及查看管理进程信息
GGSCI(zlm) 3> start mgr
Managerstarted.
GGSCI(zlm) 4> info mgr
Manageris running (IP port zlm.7809).
********
目标库:
********
7.在目标主机重复以上步骤1-6(具体步骤略),启动目标库管理进程并查看
GGSCI(zlm2) 4> start mgr
Managerstarted.
GGSCI(zlm2) 5> info mgr
Manageris running (IP port zlm2.7809).
*******
源主库:
*******
8.创建源数据库OGG实验用户ggtest并给予connect和resource权限
SQL>create user ggtest identified by ggtest defaulttablespace userstemporary tablespace temp quota unlimited on users;
Usercreated.
SQL>grant connect,resourceto ggtest;
Grantsucceeded.
9.创建源数据库OGG实验表(此处用到了OGG软件包中自带的2个测试脚本)
SQL>conn ggtest/ggtest
Connected.
SQL>select object_name,object_type from user_objects;
no rowsselected
SQL>@/u01/app/oracle/gg11/demo_ora_create.sql
DROPTABLE tcustmer
*
ERROR atline 1:
ORA-00942:table or view does not exist
Tablecreated.
DROPTABLE tcustord
*
ERROR atline 1:
ORA-00942:table or view does not exist
Tablecreated.
10.创建源数据库OGG实验表中的数据
SQL>@/u01/app/oracle/gg11/demo_ora_insert.sql
1 rowcreated.
1 rowcreated.
1 rowcreated.
1 rowcreated.
Commitcomplete.
********
目标库:
********
11.创建目标数据库OGG实验用户ggtest,并给予connect和resource权限
SQL>create user ggtest identified by ggtest defaulttablespace users temporary tablespace tempquota unlimited on users;
Usercreated.
SQL>grant connect,resourceto ggtest;
Grantsucceeded.
SQL>conn ggtest/ggtest
Connected.
SQL>@/u01/app/oracle/gg11/demo_ora_create.sql
DROPTABLE tcustmer
*
ERROR atline 1:
ORA-00942:table or view does not exist
执行完SQL脚本后,此时目标库的测试环境是2张空表,tcustmer和tcustord,稍后可以进行DML的测试
12.查看源数据库用户表的记录
SQL>show user
USER is"GGTEST"
SQL>select * from tcustmer;
CUST NAME CITY ST
---------------------------------- -------------------- --
WILL BGSOFTWARE CO. SEATTLE WA
JANEROCKY FLYER INC. DENVER CO
SQL>select * from tcustord;
CUSTORDER_DAT PRODUCT_ ORDER_IDPRODUCT_PRICE PRODUCT_AMOUNT TRANSACTION_ID
------------- -------- ---------- ------------- -------------- --------------
WILL30-SEP-94 CAR 144 17520 3 100
JANE11-NOV-95 PLANE 256 133300 1 100
13.查看目标库用户表中的记录
SQL>show user
USER is"GGTEST"
SQL>select * from tcustmer;
no rowsselected
SQL>select * from tcustord;
no rowsselected
二、一次性抽取同步实验(不配置任何TRAIL文件)
1.用OGG推荐的方法(即INITIAL EXTRACT)一次性抽取,初始化源库到目标库的数据
GGSCI(zlm) 1> dblogin userid system,password oracle
Successfullylogged into database.
GGSCI(zlm) 2> add trandata ggtest.tcustmer
Loggingof supplemental redo data enabled for table GGTEST.TCUSTMER.
GGSCI(zlm) 3> add trandata ggtest.tcustord
Loggingof supplemental redo data enabled for table GGTEST.TCUSTORD.
GGSCI(zlm) 4> info trandata ggtest.*
Loggingof supplemental redo log data is enabled for table GGTEST.TCUSTMER.
Columnssupplementally logged for table GGTEST.TCUSTMER: CUST_CODE.
Loggingof supplemental redo log data is enabled for table GGTEST.TCUSTORD.
Columnssupplementally logged for table GGTEST.TCUSTORD: CUST_CODE, ORDER_DATE,PRODUCT_CODE, ORDER_ID.
2.源数据库增加extrace进程组einit并配置参数
GGSCI(zlm) 5> add extract einit,sourceistable
EXTRACTadded.
GGSCI(zlm) 6> edit params einit
extracteinit
useridsystem,password oracle
rmthostzlm2,mgrport 7809
rmttaskreplicat,group rinit
tableggtest.tcustermer;
tableggtest.tcustord;
GGSCI(zlm) 7> info extract *,tasks
EXTRACT EINIT Initialized 2013-08-2301:55 Status STOPPED
CheckpointLag Not Available
Log ReadCheckpoint Not Available
First Record Record 0
Task SOURCEISTABLE
3.目标库增加replicat组rinit并配置参数
GGSCI(zlm2) 1> add replicat rinit,specialrun
REPLICATadded.
GGSCI(zlm2) 2> edit params rinit
replicatrinit
assumetargetdefs --表示是同构表(相同数据库软件之间的复制,如:oracle-oracle)
useridsystem,password oracle
discardfile ./dirrpt/rinit.dsc,purge
mapggtest.*,target ggtest.*;
GGSCI(zlm2) 3> info replicat *,task
REPLICAT RINIT Initialized 2013-08-2302:02 Status STOPPED
CheckpointLag 00:00:00 (updated 00:03:13 ago)
Log ReadCheckpoint Not Available
Task SPECIALRUN
INITIALEXTRACT是通过配置rmttask参数来指定的,SOURCEISTABLE表示初始化整个表,SPECIALRUN表示只抽取一次,这个是OGG初始化抽取必须配置的几个参数,注意,此处不用配置EXTTRAIL/RMTTRAIL参数
4.启动源库extract并查看源库extract报告
GGSCI(zlm) 8> start extract einit
SendingSTART request to MANAGER ...
EXTRACTEINIT starting
GGSCI (zlm)9> view report einit
......(略)
Output torinit:
FromTable GGTEST.TCUSTMER:
# inserts: 2
# updates: 0
# deletes: 0
# discards: 0
FromTable GGTEST.TCUSTORD:
# inserts: 2
# updates: 0
# deletes: 0
# discards: 0
5.查看目标库replicat报告
GGSCI(zlm2) 5> view report rora
......(略)
FromTable GGTEST.TCUSTMER to GGTEST.TCUSTMER:
# inserts: 2
# updates: 0
# deletes: 0
# discards: 0
FromTable GGTEST.TCUSTORD to GGTEST.TCUSTORD:
# inserts: 2
# updates: 0
# deletes: 0
# discards: 0
注意:用INITIAL EXTRACT进行一次性抽取初始化数据时,目标库的replicat进程不用手动启动,只要源库的extract进程start以后,自动会同步到目标库,而且此时用info all是看不到extract和replicat进程的,只能看见mgr进程
6.目标库用ggtest用户查看表
[oracle@zlm2gg11]$ sqlplus '/as sysdba'
SQL*Plus:Release 11.2.0.3.0 Production on Fri Aug 2303:29:33 2013
Copyright(c) 1982, 2011, Oracle. All rightsreserved.
Connectedto:
OracleDatabase 11g Enterprise Edition Release 11.2.0.3.0- 64bit Production
With thePartitioning, OLAP, Data Mining and Real Application Testing options
SQL>show user
USER is"SYS"
SQL>conn ggtest/ggtest
Connected.
SQL>select * from tcustmer;
CUST NAME CITY ST
---------------------------------- -------------------- --
WILL BGSOFTWARE CO. SEATTLE WA
JANEROCKY FLYER INC. DENVER CO
SQL>select * from tcustord;
CUSTORDER_DAT PRODUCT_ ORDER_IDPRODUCT_PRICE PRODUCT_AMOUNT TRANSACTION_ID
------------- -------- ---------- ------------- -------------- --------------
WILL30-SEP-94 CAR 144 17520 3 100
JANE11-NOV-95 PLANE 256 133300 1 100
之前通过replicat的report信息中已经知道,数据应该已经从源库同步到目标库了,现在通过查看目标库的测试表,发现数据确实已经同步过来了
三、不配置PUMP进程和本地TRAIL路径的同步实验
1.配置DML方式复制,源数据库添加extract组并配置参数
GGSCI(zlm) 1> add extract eora,tranlog,begin now,[threads 1]
EXTRACTadded.
启用tranlog日志 (mssql和oracle是用tranlog,mysql用的是vam),立即开始
threads 1可缺省
GGSCI(zlm) 2> info extract *
EXTRACT EORA Initialized 2013-08-2409:48 Status STOPPED
CheckpointLag 00:00:00 (updated 00:00:08 ago)
Log ReadCheckpoint Oracle Redo Logs
2013-08-24 09:48:13 Thread 1, Seqno 0, RBA 0
SCN 0.0 (0)
GGSCI(zlm) 3> edit params eora
extracteora
useridsystem,password oracle
rmthost zlm2,mgrport 7809 --mssql用的是7815端口
rmttrail ./dirdat/rt
tableggtest.tcustmer;
tableggtest.tcustord;
由于没有配置PUMP抽取进程,此处直接指定远端trail路径,本地不配置trail路径
2.添加源库rmttrail并启动extract
GGSCI(zlm) 5> add rmttrail ./dirdat/rt,extracteora,megabytes 5
RMTTRAILadded.
此操作执行后,会在远端创建一个rt00000文件,如果已经有了,那么继续使用原来的rt000000文件,megabytes指定TRAIL文件大小的上限,此处是5M,默认是10M一个文件
GGSCI(zlm) 6> info rmttrail *
Extract Trail: ./dirdat/rt
Extract: EORA
Seqno: 0
RBA: 0
File Size: 5M
GGSCI(zlm) 7> start extract eora
ERROR:Manager not currently running.
GGSCI(zlm) 8> start mgr
Managerstarted.
GGSCI(zlm) 9> start extract eora
SendingSTART request to MANAGER ...
EXTRACTEORA starting
3.配置目标库全局参数文件./GLOBALS,并添加checkpointtable
GGSCI(zlm2) 1> edit params ./GLOBALES
checkpointtable system.chkpt
GGSCI(zlm2) 2> dblogin userid system,password oracle
Successfullylogged into database.
GGSCI(zlm2) 3> add checkpointtable
ERROR:Missing checkpoint table specification.
报错:丢失指定的checkpoint table,查看下./GLOBALS参数,表是配置了的
GGSCI(zlm2) 4> view params ./GLOBALS
checkpointtablesystem.chkpt
指定shcema.table_name后再添加一次
GGSCI(zlm2) 5> add checkpointtable system.chkpt
Successfullycreated checkpoint table system.chkpt.
注意,此处需要指定object_name. table_name,而且必须要在./GLOBALS参数中添加checkpointtable参数,并添加相应的checkpoint table,否则replicat进程是无法启动的
4.添加replicat组rora并修改参数
GGSCI (zlm2) 6> addreplicat rora exttrail ./dirdat/rt --配置本地trail路径,该路径是在extract eora中配置rmttrail时指定的那个路径
ERROR: Nocheckpoint table specified for ADD REPLICAT.
报错,也必须指定object_name.table_name
GGSCI(zlm2) 7> add replicat rora exttrail ./dirdat/rtcheckpointtable system.chkpt
REPLICATadded.
GGSCI(zlm2) 8> edit params rora
replicatrora
useridsystem,password oracle
handlecollisions --冲突自动处理
assumetargetdefs --源和目标是同构的,如果异构,此处需指定sourcedefs ./dirdef\xxx.def
reperror default,discard --对错误的响应,可以不配置,默认为写入指定的discardfile
discardfile./dirrpt/rora.dsc,purge --也可以把purge改成append,megabytes 5
mapggtest.*, target gguser.*;
5.启动replicat,并分别查看报告、进程运行状态、replicat进程信息
GGSCI(zlm2) 9> start replicat rora
SendingSTART request to MANAGER ...
REPLICATRORA starting
GGSCI(zlm2) 10> view report rora
内容略...
GGSCI(zlm2) 11> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RORA 00:00:00 00:00:07
GGSCI(zlm2) 12> info rora
REPLICAT RORA Last Started 2013-08-24 11:08 Status RUNNING
CheckpointLag 00:00:00 (updated 00:00:03 ago)
Log ReadCheckpoint File ./dirdat/rt000000
First Record RBA 0
6.源库启动extract进程eora
GGSCI(zlm) 10> start extract eora
SendingSTART request to MANAGER ...
EXTRACTEORA starting
GGSCI(zlm) 11> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EORA 00:00:00 01:27:18
7.测试在源库表中插入/更新/删除记录
**************
测试插入表数据
**************
在源库中插入数据后提交
SQL>select * from tcustmer;
CUST NAME CITY ST
---------------------------------- -------------------- --
WILL BGSOFTWARE CO. SEATTLE WA
JANEROCKY FLYER INC. DENVER CO
SQL>desc tcustmer
Name Null? Type
------------------------------------------------- ----------------------------
CUST_CODE NOT NULLVARCHAR2(4)
NAME VARCHAR2(30)
CITY VARCHAR2(20)
STATE CHAR(2)
SQL>insert into tcustmer values('zhao','aaron','shanghai','sh');
1 rowcreated.
SQL> commit; --必须提交
Commitcomplete.
SQL>select * from tcustmer;
CUST NAME CITY ST
---------------------------------- -------------------- --
zhao aaron shanghai sh
WILL BGSOFTWARE CO. SEATTLE WA
JANEROCKY FLYER INC. DENVER CO
查看目标库相应的表
SQL>select * from tcustmer;
CUST NAME CITY ST
---------------------------------- -------------------- --
WILL BGSOFTWARE CO. SEATTLE WA
JANEROCKY FLYER INC. DENVER CO
发现此时并没有同步,源库已经commit了,为什么会么有同步呢?
肯定是哪里配置有错误了,查看备库replicat进程rora的报告
GGSCI(zlm2) 5> view report rora
在报告的Run Time Messages一栏里报了2个错误:
WARNINGOGG-00869 Couldnot retrieve defintion for the table gguser.TCUSTM
ER.
ERROR OGG-00199 Table gguser.TCUSTMER does not exist intarget database.
查看replicat参数
GGSCI(zlm2) 1> view params rora
replicatrora
useridsystem,password oracle
handlecollisions
assumetargetdefs
discardfile./dirrpt/rora.dsc,purge
mapggtest.*, target gguser.*;
原来是target的用户名写错了把gguser.*改成ggtest.*后保存,实验用户是ggtest
GGSCI(zlm2) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT ABENDED RORA 00:00:00 00:53:10
GGSCI(zlm2) 3> info rora
REPLICAT RORA Last Started 2013-08-24 12:00 Status ABENDED
CheckpointLag 00:00:00 (updated 00:53:17 ago)
Log ReadCheckpoint File ./dirdat/rt000000
First Record RBA 1017
GGSCI(zlm2) 4> start rora
SendingSTART request to MANAGER ...
REPLICATRORA starting
GGSCI(zlm2) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RORA 00:00:00 00:00:01
再次查看replicat报告
GGSCI(zlm2) 6> view report rora
***********************************************************************
** Run Time Messages **
***********************************************************************
Openedtrail file ./dirdat/rt000000 at 2013-08-24 12:21:20
WildcardMAP resolved (entry ggtest.*):
map "GGTEST"."TCUSTMER",target ggtest."TCUSTMER";
Usingfollowing columns in default map by name:
CUST_CODE, NAME, CITY, STATE
Using thefollowing key columns for target table GGTEST.TCUSTMER: CUST_CODE.
再次查看目标库表里的数据
SQL>select * from tcustmer;
CUST NAME CITY ST
---------------------------------- -------------------- --
WILL BGSOFTWARE CO. SEATTLE WA
JANEROCKY FLYER INC. DENVER CO
zhao aaron shanghai sh
完成插入的同步
**************
测试更新表数据
**************
更新源库表内新插入的那条数据并查看
SQL>select * from tcustmer;
CUST NAME CITY ST
---------------------------------- -------------------- --
zhaoaaron beijing bj
WILL BGSOFTWARE CO. SEATTLE WA
JANEROCKY FLYER INC. DENVER CO
SQL> commit;
Commitcomplete.
注意:与之前插入时一样,必须commit,以为OGG是根据事务结束为标志,开始同步数据的
SQL>select * from tcustmer;
CUST NAME CITY ST
---------------------------------- -------------------- --
zhao aaron beijing bj
WILL BGSOFTWARE CO. SEATTLE WA
JANEROCKY FLYER INC. DENVER CO
在目标库查看表
SQL>select * from tcustmer;
CUST NAME CITY ST
---------------------------------- -------------------- --
WILL BGSOFTWARE CO. SEATTLE WA
JANEROCKY FLYER INC. DENVER CO
zhao aaron shanghai sh
SQL>select * from tcustmer;
CUST NAME CITY ST
---------------------------------- -------------------- --
WILL BGSOFTWARE CO. SEATTLE WA
JANEROCKY FLYER INC. DENVER CO
zhao aaron beijing bj
完成更新的同步
**************
测试删除表数据
**************
删除源表中最新加入的那条数据
SQL>delete from tcustmer where name='aaron';
1 rowdeleted.
SQL>select * from tcustmer;
CUST NAME CITY ST
---------------------------------- -------------------- --
WILL BGSOFTWARE CO. SEATTLE WA
JANEROCKY FLYER INC. DENVER CO
SQL> commit;
Commitcomplete.
在目标库查看表
SQL>select * from tcustmer;
CUST NAME CITY ST
---------------------------------- -------------------- --
WILL BGSOFTWARE CO. SEATTLE WA
JANEROCKY FLYER INC. DENVER CO
zhaoaaron beijing bj
SQL>select * from tcustmer;
CUST NAME CITY ST
---------------------------------- -------------------- --
WILL BGSOFTWARE CO. SEATTLE WA
JANEROCKY FLYER INC. DENVER CO
在以上测试过程中,在extract及replicat进程中配置的目标trail文件夹(./dirdata/lr)下生成了lr000000的文件,我用logdump记录了3个DML操作时该文件的值
**********
插入数据时:
**********
GGHOME目录执行logdump进入操作界面:
Logdump 1>cd ./dirdat
Logdump 2>ls
rt000000
Logdump 3>open rt000000
CurrentLogTrail is /u01/app/oracle/gg11/dirdat/rt000000
Logdump 4>count
LogTrail/u01/app/oracle/gg11/dirdat/rt000000 has 2 records
TotalData Bytes 1058
Avg Bytes/Record 529
Insert 1
Others 1
After Images 1
Averageof 2 Transactions
Bytes/Trans ..... 577
Records/Trans ... 1
Files/Trans ..... 1
**********
更新数据时:
**********
Logdump 5>open ./dirdat/rt000000
CurrentLogTrail is /u01/app/oracle/gg11/dirdat/rt000000
Logdump 6>count
LogTrail/u01/app/oracle/gg11/dirdat/rt000000 has 3 records
TotalData Bytes 1093
Avg Bytes/Record 364
Insert 1
FieldComp 1
Others 1
After Images 2
Averageof 3 Transactions
Bytes/Trans ..... 412
Records/Trans ... 1
Files/Trans ..... 1
**********
删除数据时:
**********
Logdump 7>open ./dirdat/rt000000
CurrentLogTrail is /u01/app/oracle/gg11/dirdat/rt000000
Logdump 8>count
LogTrail/u01/app/oracle/gg11/dirdat/rt000000 has 4 records
TotalData Bytes 1105
Avg Bytes/Record 276
Delete 1
Insert 1
FieldComp 1
Others 1
Before Images 1
After Images 2
Averageof 4 Transactions
Bytes/Trans ..... 324
Records/Trans ... 1
Files/Trans ..... 1
以上的测试环境没有配置pump,而在实际生产环境中,一般都要再配置一个pump的extract进程,这样可以网络环境不是很稳定的情况下,保证OGG传输的可靠性,这个机制大致是:在先本地trail文件保存变更并提交的事务信息,然后可以断点续传到目标库,再由目标库的replicat进程完成同步复制,只要源库trail不被删除,网络通畅时就可以同步到目标库
四、配置PUMP EXTRACT进程的同步实验
1.先修改原extract配置文件eora,注释或删掉以下两行:
--rmthost zlm2,mgrport 7809
--rmttrail ./dirdat/rt
添加一行:exttrail ./dirdata/lt
2.新增pump进程并修改参数
GGSCI(zlm) 1> add extract pora,exttrailsource ./dirdat/lt--添加本地trail(ltxxxxxx)
EXTRACTadded.
注意:这里其实是指定在extract eora中配置的本地路径,所以用了exttrailsource,如果之前没有用add extract eora,exttrail./dirdat/lt创建过本地trail的话,那么该条命令就新建一个;有的话,就指定一下。注意2个extract用的参数是不同的
GGSCI(zlm) 2> edit params pora
extracpora
rmthost zlm2,mgrport 7809
rmttrail ./dirdat/rt
passthru –直接传递,不检测
tableggtest.tcustmer;
tableggtest.tcustord;
相当于原来配置在eora抽取进程中备注释掉的2行参数挪动到了这里,因为现在是通过PUMP进程pora与远端进行通信
GGSCI(zlm) 3> add rmttrail ./dirdat/rt,extract pora --添加远端trail(rtxxxxxx)
RMTTRAILadded.
配置完pump进程pora后,启动该进程
GGSCI(zlm) 4> start pora
SendingSTART request to MANAGER ...
EXTRACTPORA starting
GGSCI(zlm) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EORA 00:00:00 00:00:04
EXTRACT RUNNING PORA 00:00:00 00:03:27
开始测试数据(为节省篇幅,仅测试插入,更新和删除也是同样实现的):
************
插入数据测试
************
源库查询表插入一条新数据,提交后查询
SQL>select * from tcustmer;
CUST NAME CITY ST
---------------------------------- -------------------- --
WILL BGSOFTWARE CO. SEATTLE WA
JANEROCKY FLYER INC. DENVER CO
SQL>insert into tcustmer values('zhao','aaron','shanghai','sh');
1 row created.
SQL> commit;
Commitcomplete.
SQL>select * from tcustmer;
CUST NAME CITY ST
---------------------------------- -------------------- --
zhao aaron shanghai sh
WILL BGSOFTWARE CO. SEATTLE WA
JANEROCKY FLYER INC. DENVER CO
目标库查询表
SQL>select * from tcustmer;
CUST NAME CITY ST
---------------------------------- -------------------- --
WILL BGSOFTWARE CO. SEATTLE WA
JANEROCKY FLYER INC. DENVER CO
zhao aaron shanghai sh
加入并配置pump进程pora后,数据依然能够顺利地同步到目标库,分别来看看在源库配置的两个etxtract进程的报告
查看eora报告
***********************************************************************
** Run Time Messages **
***********************************************************************
TABLEresolved (entry ggtest.tcustmer):
table"GGTEST"."TCUSTMER";
Using thefollowing key columns for source table GGTEST.TCUSTMER: CUST_CODE.
查看pora报告
***********************************************************************
** Run Time Messages **
***********************************************************************
Openedtrail file ./dirdat/lt000000 at 2013-08-2414:13:21
TABLEresolved (entry ggtest.tcustmer):
table"GGTEST"."TCUSTMER";
PASSTHRUmapping resolved for source table GGTEST.TCUSTMER
2013-08-2414:16:29 INFO OGG-01054 Recovery completed for target file ./dirdat/rt000001,at
RBA 1426, CSN 1448898.
2013-08-2414:16:29 INFO OGG-01057 Recovery completed for all targets.
从上面可以看到,加入pump进程后,先由pump去本地trail路径./dirdat/lt去读取lt000000文件,然后通过网络同步传输到远程trail路径./dirdat/rt,覆盖到rt000001文件中去
再来看一下目标库上的replicat进程的报告
***********************************************************************
** Run Time Messages **
***********************************************************************
Opened trail file ./dirdat/rt000000 at 2013-08-24 12:21:20
WildcardMAP resolved (entry ggtest.*):
map "GGTEST"."TCUSTMER",target ggtest."TCUSTMER";
Usingfollowing columns in default map by name:
CUST_CODE, NAME, CITY, STATE
Using thefollowing key columns for target table GGTEST.TCUSTMER: CUST_CODE.
Switching to next trail file ./dirdat/rt000001 at 2013-08-24 14:13:17due to EOF, with current R
BA 1464
Openedtrail file ./dirdat/rt000001 at 2013-08-24 14:13:17
2013-08-2414:13:17 INFO OGG-01020 Processed extract process RESTART_ABEND record at seq 1,
rba 1367 (aborted 0 records).
目标库replicat进程rora先处理本地trail路径上的文件rt000000,由于一个EOF,又重新创建了一个rt000001文件进行数据的同步复制
-------------------------------------------------------------
关于用户权限的说明:
一般为了在使用OGG同步数据时可以顺利进行,需要给予用户以下权限:
Onsource database (extract):
GRANT CREATE SESSION, ALTER SESSION,RESOURCE, CONNECT,SELECT ANY dictionary TOggtest;
GRANT flashback ANY TABLE, SELECTANY TABLE TO ggtest;
GRANT EXECUTE ON dbms_flashback TO ggtest;
Ontarget (Replicat) database:
GRANT CREATE SESSION, ALTER SESSION,RESOURCE, CONNECT,SELECT ANY dictionary TOggtest;
GRANT SELECT ANY TABLE, INSERT ANYTABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO ggtest;
GRANT CREATE TABLE, ALTER ANY TABLE,LOCK ANY TABLE TO ggtest;
DespiteGoldenGate documentation one grant is missing (that’s probably why Oracle grant DBA to thisaccount):
GRANT ALTER ANY TABLE TO ggtest;
也可以直接给予用户DBA权限,但是这样做是有一定安全隐患的
GRANT DBA TO ggtest
如果是配置DDL操作碰到权限引起的问题,可以考虑加上以上提及的权限,之前的实验只给了2个权限,CONNECT和RESOURCE,实验中并没有遇到不能同步的问题,用的授权语句是GRANT CONNECT,RESOURCE TO ggtest;来看看这2个权限具体包含哪些权限:
SQL>select GRANTEE,PRIVILEGE from DBA_SYS_PRIVSwhere GRANTEE = 'CONNECT';
GRANTEE PRIVILEGE
----------------------------------------------------------------------
CONNECT CREATE SESSION
SQL>select GRANTEE,PRIVILEGE from DBA_SYS_PRIVSwhere GRANTEE = 'RESOURCE';
GRANTEE PRIVILEGE
----------------------------------------------------------------------
RESOURCE CREATE CLUSTER
RESOURCE CREATE INDEXTYPE
RESOURCE CREATE OPERATOR
RESOURCE CREATE PROCEDURE
RESOURCE CREATE SEQUENCE
RESOURCE CREATE TABLE
RESOURCE CREATE TRIGGER
RESOURCE CREATE TYPE
8 rowsselected
关于实验中出现错误的处理办法:
通常遇见的错误,就是参数没有配置好,如trail路径不正确,少了字母导致参数找不到,造成目标库数据库无法同步,还有在应用OGG的时候一定要记住,只有commit后,事务处理造成变化的数据才会被同步到目标库,当问题出现时,如某个extract进程不能START,一直是ABENDED状态等,最简单的方法就是直接查看某个进程的报告,里面有详细的消息日志信息,会提示你可能是什么原因造成的错误,以及如何解决等信息,除了report,其实还可以在info中加入detail参数,如下:
GGSCI(zlm) 11> info eora,detail
EXTRACT EORA Initialized 2013-08-24 11:15 Status STOPPED
CheckpointLag 00:00:00 (updated 00:05:25 ago)
Log ReadCheckpoint Oracle Redo Logs
2013-08-24 13:40:07 Thread 1, Seqno 32, RBA 35381248
SCN 0.1449446 (1449446)
Target Extract Trails:
Remote Trail Name Seqno RBA Max MB
./dirdat/rt 0 1464 5
./dirdat/lt 0 0 5
Extract Source Begin End
/u01/app/oracle/oradata/zlm11g/redo02.log * Initialized * 2013-08-24 13:40
Not Available * Initialized * 2013-08-24 09:48
Currentdirectory /u01/app/oracle/gg11
Reportfile /u01/app/oracle/gg11/dirrpt/EORA.rpt
Parameterfile /u01/app/oracle/gg11/dirprm/eora.prm
Checkpointfile /u01/app/oracle/gg11/dirchk/EORA.cpe
Processfile /u01/app/oracle/gg11/dirpcs/EORA.pce
Stdoutfile /u01/app/oracle/gg11/dirout/EORA.out
Errorlog /u01/app/oracle/gg11/ggserr.log
从以上内容可以看到,所有的错误在GGHOME下相应的目录中都是有记录的,常用的是ggserr.log,查看这个log文件就可以分析并解决错误了
另外,借助OGG自带的工具logdump,可以通过查看trail文件具体的内容,一定程度上也可以帮你辅助分析OGG在同步过程中发生了什么,帮助你加深理解OGG的同步流程。
具体如何使用可以查阅OGG官方文档e27273.pdf,以下是链接:
http://docs.oracle.com/cd/E28323_01/doc.1121/e27273.pdf
具体trail文件格式说明在第297页,APPENDIX3-Figure 26 Sample trail record as viewed with the Logdump utility
OGG主要进程作用说明:
Manager:是GoldenGate的控制进程,运行在源端和目标端上。它主要作用有以下几个方面:启动、监控、重启Goldengate的其他进程,报告错误及事件,分配数据存储空间,发布阀值报告等。在目标端和源端有且只有一个manager进程.
Extract:运行在数据库源端,负责从源端数据表或者日志中捕获数据。
初始时间装载阶段:在初始数据装载阶段,Extract进程直接从源端的数据表中抽取数据
同步变化捕获阶段:初始数据同步完成以后,Extract进程负责捕获源端数据的变化(DML和DDL)
Pump(可选):运行在数据库源端,其作用是将源端产生的本地trail文件,把trail以数据块的形式通过TCP/IP 协议发送到目标端,这通常也是推荐的方式。pump进程本质是extract进程的一种特殊形式,如果不使用trail文件,那么extract进程在抽取完数据以后,直接投递到目标端,生成远程trail文件。如果要使用pump,必须配置本地trail,否则不用。
Collector:与 Pump进程对应 的叫Server Collector进程,这个进程不需要引起我的关注,因为在实际操作过程中,无需我们对其进行任何配置,所以对我们来说它是透明的。它运行在目标端,其任务就是把Extract/Pump投递过来的数据重新组装成远程trail文件。
Replicat:通常我们也把它叫做应用进程。运行在目标端,是数据传递的最后一站,负责读取目标端trail文件中的内容,并将其解析为DML或DDL语句,然后应用到目标
数据库中。
-------------------------------------------------------------------------------------------------------
By aaron8219 ChinaunixBlog:http://blog.chinaunix.net/uid/24612962.html
原创内容,转载请注明链接,谢谢!
http://blog.csdn.net/aaron8219/article/details/10275431