Oracle Golden Gate 有關Data Pump 重置 trail 序列號 測試 說明
一.測試背景說明
測試GG的Data pump，環境是使用之前的GG 進行的修改，啟動程式後，Extract 報錯，不能正常啟動。
GGSCI (gg1) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPUMP 00:00:00 00:00:06
EXTRACT ABENDED EXT1 00:00:00 01:09:26
檢視log：
GGSCI (gg1) 2> view report ext1
….
2011-11-16 16:12:25 ERROR OGG-01496 Failed to open targettrail file /u01/ggate/dirdat/lt000039, at RBA 867334.
2011-11-16 16:12:25 ERROR OGG-01668 PROCESS ABENDING.
提示不能開啟檔案，因為我們直接沒有使用data pump，從target 端將這個檔案scp 到source 後，就正常啟動了。
gg2:/u01/ggate/dirdat> ls
lt000000 lt000008 lt000016 lt000024 lt000032 lt000040
lt000001 lt000009 lt000017 lt000025 lt000033 rep1_discard.txt
lt000002 lt000010 lt000018 lt000026 lt000034 rep2_discard.txt
lt000003 lt000011 lt000019 lt000027 lt000035
lt000004 lt000012 lt000020 lt000028 lt000036
lt000005 lt000013 lt000021 lt000029 lt000037
lt000006 lt000014 lt000022 lt000030 lt000038
lt000007 lt000015 lt000023 lt000031 lt000039
gg2:/u01/ggate/dirdat> scp lt000039 192.168.3.100:/u01/ggate/dirdat
oracle@192.168.3.100's password:
lt000039 100% 847KB 847.0KB/s 00:00
gg2:/u01/ggate/dirdat> scp lt000040 192.168.3.100:/u01/ggate/dirdat
oracle@192.168.3.100's password:
lt000040 100%1346 1.3KB/s 00:00
GGSCI (gg1) 9> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPUMP 00:00:00 00:00:03
EXTRACT RUNNING EXT1 00:00:00 00:00:04
GGSCI (gg1) 10> info extract dpump
EXTRACT DPUMP Last Started 2011-11-1616:09 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:06 ago)
Log Read Checkpoint File /u01/ggate/dirdat/lt000000
First Record RBA 0
Extract 啟動已經沒有了問題，但是要注意的一點，Data Pump 預設是從lt000000 的trail 進行讀取，因為我之前已經進行了一些測試，所以這裡的trail已經變成了41.
gg1:/u01/ggate/dirdat> ls
lt000039 lt000040 lt000041
所以這種解決方法還是有問題，我們要麼重置extract 的trail，要麼提高data pump的trail。
重置程式的命令如下：
alter extractext1,extseqno 0,extrba 0
alterreplicat rep1,extseqno 0,extrba 0
官網有關這個引數的說明如下：
EXTSEQNO
Valid for a primary Extract for Oracle and NonStop SQL/MX,and for a data pump Extract. Specifies either of thefollowing:
（1） sequence number of an Oracle redo log and RBA within that log at whichto begin capturing data.
Sequence number 是redolog的序列號。
（2） the NonStop SQL/MX TMF audit trailsequence number and relative byte address within that file at which to begincapturing data. Together these specify the location in the TMF Master Audit Trail(MAT).
（3） the file in a trail in which tobegin capturing data (for a data pump). Specify the sequence number, but notany zeroes used for padding. For example, if the trail file isc:\ggs\dirdat\aa000026, you would specify EXTSEQNO 26. By default, processingbegins at the beginning of a trail unless this option is used.
-- EXTSEQNO 等於trail 檔案編號。
驗證一下：
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archivelog
Oldest online log sequence 17
Next log sequence to archive 19
Current log sequence 19
二 測試: 重置Extract 和Replicat 程式的Trail 序列號
先停程式，然後使用如下命令，將trail 檔案的編號都重置為0.
GGSCI (gg1) 78> alter extract ext1,begin now
EXTRACT altered.
GGSCI (gg1) 79> alter extract ext1,extseqno 0,extrba 0
EXTRACT altered.
GGSCI (gg1) 80> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
我們看程式，報錯：
GGSCI (gg1) 109> info ext1
EXTRACT EXT1 Last Started 2011-11-1618:12 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:38:43 ago)
Log Read Checkpoint Oracle Redo Logs
2011-11-16 17:35:27 Seqno 19, RBA20679696
我們將extract 重新設定成：Seqno 19,RBA 20679696
GGSCI (gg1) 111> alter extract ext1,begin now
EXTRACT altered.
GGSCI (gg1) 112> alter extractext1,extseqno 19,extrba 20679696
EXTRACT altered.
GGSCI (gg1) 113> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
檢視狀態還是報錯：
GGSCI (gg1) 118> view report ext1
…
2011-11-16 18:21:37 ERROR OGG-01496 Failed to open targettrail file /u01/ggate/dirdat/lt000041, at RBA 1462.
2011-11-16 18:21:37 ERROR OGG-01668 PROCESS ABENDING.
可惜這個lt000041 檔案已經被我刪除。看來此法行不通。 不過先驗證一下修改data pump的trail 號。
GGSCI (gg1) 11> alter extract dpump,extseqno 42,extrba 0
EXTRACT altered.
GGSCI (gg1) 14> view report dpump
…
2011-11-16 19:07:53 ERROR OGG-01091 Unable to open file"/u01/ggate/dirdat/lt000042" (error 2, No such file ordirectory).
….
GGSCI (gg1) 21> info dpump
EXTRACT DPUMP Last Started 2011-11-1619:07 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:03:39 ago)
Log Read Checkpoint File /u01/ggate/dirdat/lt000042
First Record RBA 0
結論：
根據以上結果，可以看出，重置dpump 的序列號，來實現DataPump 是可行的方法，不過基於我之前的環境已經被破壞，所以我們重新測試一下。
三． 刪除原來的Extract 和 data pump 程式，重新配置
GGSCI (gg1) 29> dblogin userid ggate,password ggate
Successfully logged into database.
GGSCI (gg1) 30> delete extract ext1
2011-11-16 19:22:05 INFO OGG-01750 Successfullyunregistered EXTRACT EXT1 from database.
Deleted EXTRACT EXT1.
GGSCI (gg1) 31> delete extract dpump
GGSCI (gg2) 96> dblogin userid ggate,passwordggate
Successfully logged into database.
GGSCI (gg2) 98> delete replicat rep1
Deleted REPLICAT REP1.
3.1 Extract+datapump+replicat 測試
--建立Extract
GGSCI (gg1) 31> add extract ext1,tranlog, begin now
2011-11-16 19:23:35 INFO OGG-01749 Successfully registeredEXTRACT EXT1 to start managing log retention at SCN 1282074.
EXTRACT added.
GGSCI (gg1) 32> add exttrail /u01/ggate/dirdat/lt, extract ext1
EXTTRAIL added.
GGSCI (gg1) 34> view params ext1
extract ext1
userid ggate@gg1, password ggate
exttrail /u01/ggate/dirdat/lt
table dave.pdba;
--建立pump
GGSCI (gg1) 35> add extract dpump,exttrailsource /u01/ggate/dirdat/lt
EXTRACT added.
GGSCI (gg1) 36> add rmttrail /u01/ggate/dirdat/lt, extract dpump
RMTTRAIL added.
GGSCI (gg1) 37> view params dpump
extract dpump
userid ggate@gg1, password ggate
rmthost gg2, mgrport 7809
rmttrail /u01/ggate/dirdat/lt
passthru
table dave.pdba;
--Target 建立replicat程式
--這裡配置程式的checkpoint
GGSCI (gg2) 6> edit params ./GLOBAL
GGSCHEMA ggate
CHECKPOINTTABLE ggate.checkpoint
新增如上2條記錄。
GGSCI (gg2) 12> dblogin useridggate@gg2,password ggate
Successfully logged into database.
--連線到DB 的GGATE 使用者，在db裡建立checkpoint表
GGSCI (gg2) 13> add checkpoint tableggate.checkpoint
Successfully created checkpoint table GGATE.CHECKPOINT.
--建立replicat group
GGSCI (gg2) 14> add replicatrep1,exttrail /u01/ggate/dirdat/lt, checkpointtable ggate.checkpoint
REPLICAT added.
--注意這裡的目錄要和我們的data pump 指定的目錄一致。
修改rep1引數：
GGSCI (gg2) 44> view params rep1
replicat rep1
ASSUMETARGETDEFS
userid ggate@gg2,password ggate
discardfile/u01/ggate/dirdat/rep1_discard.txt, append, megabytes 10
--HANDLECOLLISIONS
map dave.pdba, target dave.pdba;
--測試
GGSCI (gg1) 38> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (gg1) 41> info ext1
EXTRACT EXT1 Last Started 2011-11-1619:27 Status RUNNING
Checkpoint Lag 00:03:39 (updated 00:00:07 ago)
Log Read Checkpoint Oracle Redo Logs
2011-11-16 19:23:26 Seqno 19, RBA 27086864
--這裡是我們archivelog 的序列號。
GGSCI (gg1) 43> info dpump
EXTRACT DPUMP Last Started 2011-11-1619:27 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint File /u01/ggate/dirdat/lt000000
First Record RBA 0
注意這裡的讀取的位置還是0.這種情況下的特殊性在於，因為Extract程式也是我們剛建立的，所以Extract程式也是從lt000000開始。
gg1:/u01/ggate/dirdat> ls
lt000000
GGSCI (gg2) 102> start rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (gg2) 103> info rep1
REPLICAT REP1 Last Started 2011-11-16 19:38 Status RUNNING
Checkpoint Lag 00:05:34 (updated 00:00:00 ago)
Log Read Checkpoint File /u01/ggate/dirdat/lt000000
2011-11-1619:33:21.587938 RBA 1123
所以這種情況下，我們的Datapump 是已經正常工作了。
驗證一下：
--Source DB：
SQL> conn dave/dave;
Connected.
SQL> select count(*) from pdba;
COUNT(*)
----------
2706623
--Target DB：
SQL> select count(*) from pdba;
COUNT(*)
----------
2706623
在Source DB insert 一條資料：
SQL> insert into pdba values(2,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from pdba;
COUNT(*)
----------
2706624
到Target DB 查詢：
SQL> select count(*) from pdba;
COUNT(*)
----------
2706624
同步正常。 Data Pump 工作正常， 以上測試結果說明一個問題： 如果在搭建GG 同步開始就採用DataPump 的架構，那麼Extract 和 Data pump 都從00開始進行同步。
3.2 先Extract + replicat，然後加datapump
這個示例就和我們的3.1 恰恰相反，我們先在用典型的架構，即沒有采用Data Pump，那麼當我們後來啟用Data Pump 的時候，trail 的序列號就需要進行處理。
3.2.1 先delete 掉我們之前的3個程式：
--刪Source
GGSCI (gg1) 44> dblogin userid ggate,password ggate
Successfully logged into database.
GGSCI (gg1) 46> stop ext1
Sending STOP request to EXTRACT EXT1 ...
Request processed.
GGSCI (gg1) 47> stop dpump
Sending STOP request to EXTRACT DPUMP ...
Request processed.
GGSCI (gg1) 48> delete ext1
2011-11-16 19:43:33 INFO OGG-01750 Successfullyunregistered EXTRACT EXT1 from database.
Deleted EXTRACT EXT1.
GGSCI (gg1) 49> delete dpump
Deleted EXTRACT DPUMP.
--刪Target
GGSCI (gg2) 104> dblogin userid ggate,password ggate
Successfully logged into database.
GGSCI (gg2) 105> stop rep1
Sending STOP request to REPLICAT REP1 ...
Request processed.
GGSCI (gg2) 106> delete rep1
Deleted REPLICAT REP1.
3.2.2 建立一個Extract 和一個replicat 的GG 同步
--建立Extract
GGSCI (gg1) 50> add extract ext1,tranlog, begin now
2011-11-16 19:49:21 INFO OGG-01749 Successfully registeredEXTRACT EXT1 to start managing log retention at SCN 1284318.
EXTRACT added.
GGSCI (gg1) 51> add exttrail /u01/ggate/dirdat/lt, extract ext1
EXTTRAIL added.
GGSCI (gg1) 53> view params ext1
extract ext1
userid ggate@gg1, password ggate
rmthost gg2,mgrport 7809
rmttrail /u01/ggate/dirdat/lt
table dave.pdba;
--建立Replicat
GGSCI (gg2) 109> add replicat rep1,exttrail /u01/ggate/dirdat/lt, checkpointtable ggate.checkpoint
REPLICAT added.
GGSCI (gg2) 110> view params rep1
replicat rep1
ASSUMETARGETDEFS
userid ggate@gg2,password ggate
discardfile/u01/ggate/dirdat/rep1_discard.txt, append, megabytes 10
--HANDLECOLLISIONS
map dave.pdba, target dave.pdba;
--啟動GG 程式：
GGSCI (gg1) 54> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (gg1) 56> info ext1
EXTRACT EXT1 Last Started 2011-11-1619:52 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:03:32 ago)
Log Read Checkpoint Oracle Redo Logs
2011-11-16 19:49:16 Seqno 19, RBA 29986832
GGSCI (gg2) 112> start rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (gg2) 113> info rep1
REPLICAT REP1 Last Started 2011-11-1619:53 Status RUNNING
Checkpoint Lag 00:20:01 (updated 00:00:00 ago)
Log Read Checkpoint File /u01/ggate/dirdat/lt000000
2011-11-1619:33:21.587938 RBA 1123
--測試同步：
--Source DB
SQL> select count(*) from pdba;
COUNT(*)
----------
2706623
SQL> insert into pdba values(3,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from pdba;
COUNT(*)
----------
2706624
--Target DB
SQL> select count(*) from pdba;
COUNT(*)
----------
2706624
SQL> select * from pdba where id=3;
ID TIME
---------- ------------
3 16-NOV-11
單Extract 同步正常正常。
3.2.3 現在新增DataPump程式
Replicat程式不用動，我們修改一下ext1程式，在新增一個dpump 程式就可以了。
不過在修改之前，我們先執行一些DML 操作，使trail 檔案的序列號增加一點。
GGSCI (gg1) 62> view params ext1
extract ext1
userid ggate@gg1, password ggate
--rmthost gg2,mgrport 7809
--rmttrail /u01/ggate/dirdat/lt
exttrail /u01/ggate/dirdat/lt
table dave.pdba;
GGSCI (gg1) 63> add extract dpump,exttrailsource /u01/ggate/dirdat/lt
EXTRACT added.
GGSCI (gg1) 64> add rmttrail /u01/ggate/dirdat/lt,extract dpump
RMTTRAIL added.
GGSCI (gg1) 65> view params dpump
extract dpump
userid ggate@gg1, password ggate
rmthost gg2, mgrport 7809
rmttrail /u01/ggate/dirdat/lt
passthru
table dave.pdba;
--啟動程式
GGSCI (gg1) 67> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
--ext1 程式的錯誤資訊如下：
2011-11-16 20:10:39 ERROR OGG-01496 Failed to open targettrail file /u01/ggate/dirdat/lt000002, at RBA 1965317.
--這2個資訊很重要
我們將這個檔案從target庫scp 過來：
gg2:/u01/ggate/dirdat> scp lt000002192.168.3.100:/u01/ggate/dirdat
oracle@192.168.3.100's password:
lt000002 100%1919KB 1.9MB/s 00:00
--現在我們的ext1已經可以正常啟動了：
GGSCI (gg1) 71> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (gg1) 73> info ext1
EXTRACT EXT1 Last Started 2011-11-1620:12 Status RUNNING
Checkpoint Lag 00:02:36 (updated 00:00:01 ago)
Log Read Checkpoint Oracle Redo Logs
2011-11-16 20:10:19 Seqno 19, RBA 37947392
現在看data pump 程式，這個才是我們測試的重點：
GGSCI (gg1) 75> start dpump
Sending START request to MANAGER ...
EXTRACT DPUMP starting
GGSCI (gg1) 76> info dpump
EXTRACT DPUMP Last Started 2011-11-1620:13 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:04:35 ago)
Log Read Checkpoint File /u01/ggate/dirdat/lt000000
First Record RBA 0
能正常啟動，但是讀取的trail檔案不是我們需要的。 使用命令修改這個trail。
--stop 程式
GGSCI (gg1) 80> send extract dpump,forcestop
Sending FORCESTOP request to EXTRACT DPUMP...
STOP request will be executed immediately(recovery aborted).
=
GGSCI (gg1) 81> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT ABENDED DPUMP 00:00:00 00:00:05
EXTRACT RUNNING EXT1 00:00:00 00:00:09
GGSCI (gg1) 82> alter extract dpump,extseqno 2,extrba 1965317
EXTRACT altered.
--這裡使用的2個值，就是我們在啟動Extract 報錯的值，所以說這個值很重要。
GGSCI (gg1) 83> start dpump
Sending START request to MANAGER ...
EXTRACT DPUMP starting
GGSCI (gg1) 84> info dpump
EXTRACT DPUMP Last Started 2011-11-1620:16 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:25 ago)
Log Read Checkpoint File /u01/ggate/dirdat/lt000002
First Record RBA 1965317
現在看，我們的dpump 程式已經從dpump 進行同步了。
驗證：
SQL> select count(*) from pdba;
COUNT(*)
----------
2691630
SQL> delete from pdba whererownum<1000;
999 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from pdba;
COUNT(*)
----------
2690631
--Target DB
SQL> select count(*) from pdba;
COUNT(*)
----------
2690631
同步正常。以上就是我們有關DataPump 的整個測試過程。
四． 總結
以上測試過程有點小亂，這裡最後做一下總結：
1. Data Pump 預設情況下從/u01/ggate/dirdat/lt000000 檔案開始讀取trail。
GGSCI (gg1) 76> info dpump
EXTRACT DPUMP Last Started 2011-11-1620:13 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:04:35 ago)
Log Read Checkpoint File /u01/ggate/dirdat/lt000000
First Record RBA 0
2. 如果是新搭建的GG 環境，那麼Extract 和 Data Pump 都從/u01/ggate/dirdat/lt000000開始，所以這種情況同步沒有問題。
3. 如果是之前已經存在的同步，之後改成Data Pump，注意這個trail 檔案問題，修改稱datapump 之後，啟動extract 程式會報錯，這個錯誤提示會提示trail 檔案號和RBA資訊。 我們從Target 端copy 過來這個檔案，然後使用如下命令：
GGSCI (gg1) 82> alter extract dpump,extseqno 2,extrba1965317
修改Data Pump 檔案，使其從我們指定的trail 檔案開始讀取catpure data，從而達到我們同步的目錄。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/196700/viewspace-1249344/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle golden gate程式說明OracleGo
- golden gate 引數說明Go
- ORACLE golden gate 機制OracleGo
- 初探Oracle Golden Gate(一）OracleGo
- zt_oracle golden gateOracleGo
- 初次參與Golden -Gate POC,希望有機會做Golden -Gate for ERPGo
- Oracle Golden Gate 系列小結OracleGo
- oracle golden gate 安裝配置OracleGo
- Oracle Golden Gate 安裝指南OracleGo
- Oracle Golden Gate 系列 小結OracleGo
- Golden Gate 初探Go
- data pump (資料抽取)測試
- 有關Data Pump的學習
- oracle data pumpOracle
- Oracle Golden Gate體系架構詳解OracleGo架構
- golden gate 引數 PURGEOLDEXTRACTSGo
- 配置Golden Gate 密碼加密Go密碼加密
- Golden Gate的definition檔案Go
- Oracle Data Gurad Physical Standby 相關說明Oracle
- Oracle Data Pump 研究(一)Oracle
- Oracle Golden Gate 系列十二 -- GG 資料初始化裝載二 基於SCN 的初始化 說明 與 示例OracleGo
- Oracle Data Gurad -- Logical Standby 相關說明Oracle
- Golden Gate的一次教訓Go
- 文件筆記--Oracle Data Pump 2筆記Oracle
- 文件筆記--Oracle Data Pump 1筆記Oracle
- Oracle 10g Data Pump IOracle 10g
- Oracle 10g Data Pump IIOracle 10g
- 一個奇怪的Golden Gate的問題Go
- 【Data Pump】Data Pump的並行引數原理並行
- Oracle11gR2 Smart Flash Cache測試說明Oracle
- Oracle 10g Data Pump ComponentsOracle 10g
- Oracle RAC序列效能測試Oracle
- golden gate同步的表結構修改檢查Go
- 《Oracle Golden Gate 概念和原理》-新手入門->深入解析-圖文-可下載OracleGo
- Oracle資料泵(Oracle Data Pump) 19cOracle
- 嘗試使用data pump時出錯解決
- 高速的匯出/匯入：Oracle Data PumpOracle
- data pump總結