環境：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
[[email protected]_1]$ cd $OACLE_BASE
[[email protected]~]$ ls
incremental_hot_database_backup.sh ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
incremental_hot_database_backup.sh.out orz.sh
[[email protected]]$ mkdir gg11
[[email protected]]$ cd gg11
[[email protected]]$ unzip /home/oracle/ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[[email protected]]$ 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
[[email protected]]$ 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
[[email protected]]$ . ~/.bash_profile
[[email protected]]$ 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
[[email protected]]$ . ~/.bash_profile
[[email protected]]$ 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)
[[email protected]~]$ cd $ORACLE_BASE/gg11
[[email protected]]$ 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使用者檢視表
[[email protected]]$ 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
![Hive中的DDL與DML語句一、DDL資料定義二、DML資料操作三、 查詢[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)