一.測試背景說明

測試GG的Data pump，環(huán)境是使用之前的GG 進(jìn)行的修改，啟動(dòng)進(jìn)程后，Extract 報(bào)錯(cuò)，不能正常啟動(dòng)。

 

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.

 

提示不能打開文件，因?yàn)槲覀冎苯記]有使用data pump，從target 端將這個(gè)文件scp 到source 后，就正常啟動(dòng)了。

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 啟動(dòng)已經(jīng)沒有了問題，但是要注意的一點(diǎn)，Data Pump 默認(rèn)是從lt000000 的trail 進(jìn)行讀取，因?yàn)槲抑耙呀?jīng)進(jìn)行了一些測試，所以這里的trail已經(jīng)變成了41.

 

gg1:/u01/ggate/dirdat> ls

lt000039 lt000040  lt000041

 

所以這種解決方法還是有問題，我們要么重置extract 的trail，要么提高data pump的trail。

 

重置進(jìn)程的命令如下：

alter extractext1,extseqno 0,extrba 0

       alterreplicat rep1,extseqno 0,extrba 0

 

官網(wǎng)有關(guān)這個(gè)參數(shù)的說明如下：

EXTSEQNO

<seqno>, EXTRBA

<relative byte address>

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的序列號(hào)。

（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 文件編號(hào)。

驗(yàn)證一下：

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 進(jìn)程的Trail 序列號(hào)

 

先停進(jìn)程，然后使用如下命令，將trail 文件的編號(hào)都重置為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

 

我們看進(jìn)程，報(bào)錯(cuò)：

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 重新設(shè)置成：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

 

查看狀態(tài)還是報(bào)錯(cuò)：

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.

 

可惜這個(gè)lt000041 文件已經(jīng)被我刪除?？磥泶朔ㄐ胁煌?。 不過先驗(yàn)證一下修改data pump的trail 號(hào)。

 

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

 

結(jié)論：

根據(jù)以上結(jié)果，可以看出，重置dpump 的序列號(hào)，來實(shí)現(xiàn)DataPump 是可行的方法，不過基于我之前的環(huán)境已經(jīng)被破壞，所以我們重新測試一下。

 

三． 刪除原來的Extract 和 data pump 進(jìn)程，重新配置

 

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 測試

--創(chuàng)建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;

 

--創(chuàng)建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 創(chuàng)建replicat進(jìn)程

--這里配置進(jìn)程的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里創(chuàng)建checkpoint表

GGSCI (gg2) 13> add checkpoint tableggate.checkpoint

Successfully created checkpoint table GGATE.CHECKPOINT.

 

--創(chuàng)建replicat group

GGSCI (gg2) 14> add replicatrep1,exttrail /u01/ggate/dirdat/lt, checkpointtable ggate.checkpoint

REPLICAT added.

--注意這里的目錄要和我們的data pump 指定的目錄一致。

 

修改rep1參數(shù)：

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 的序列號(hào)。

 

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.這種情況下的特殊性在于，因?yàn)镋xtract進(jìn)程也是我們剛創(chuàng)建的，所以Extract進(jìn)程也是從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 是已經(jīng)正常工作了。

 

驗(yàn)證一下：

--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 一條數(shù)據(jù)：

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 工作正常， 以上測試結(jié)果說明一個(gè)問題： 如果在搭建GG 同步開始就采用DataPump 的架構(gòu)，那么Extract 和 Data pump 都從00開始進(jìn)行同步。

 

3.2 先Extract + replicat，然后加datapump

       這個(gè)示例就和我們的3.1 恰恰相反，我們先在用典型的架構(gòu)，即沒有采用Data Pump，那么當(dāng)我們后來啟用Data Pump 的時(shí)候，trail 的序列號(hào)就需要進(jìn)行處理。

 

3.2.1 先delete 掉我們之前的3個(gè)進(jìn)程：

--刪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 創(chuàng)建一個(gè)Extract 和一個(gè)replicat 的GG 同步

--創(chuàng)建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;

 

--創(chuàng)建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;

 

--啟動(dòng)GG 進(jìn)程：

 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 現(xiàn)在添加DataPump進(jìn)程

       Replicat進(jìn)程不用動(dòng)，我們修改一下ext1進(jìn)程，在添加一個(gè)dpump 進(jìn)程就可以了。

 

不過在修改之前，我們先執(zhí)行一些DML 操作，使trail 文件的序列號(hào)增加一點(diǎn)。

 

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;

 

--啟動(dòng)進(jìn)程

GGSCI (gg1) 67> start ext1

 

Sending START request to MANAGER ...

EXTRACT EXT1 starting

 

--ext1 進(jìn)程的錯(cuò)誤信息如下：

2011-11-16 20:10:39  ERROR  OGG-01496  Failed to open targettrail file /u01/ggate/dirdat/lt000002, at RBA 1965317.

--這2個(gè)信息很重要

 

我們將這個(gè)文件從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  

 

--現(xiàn)在我們的ext1已經(jīng)可以正常啟動(dòng)了：

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

 

現(xiàn)在看data pump 進(jìn)程，這個(gè)才是我們測試的重點(diǎn)：

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

 

能正常啟動(dòng)，但是讀取的trail文件不是我們需要的。 使用命令修改這個(gè)trail。

 

--stop 進(jìn)程

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個(gè)值，就是我們在啟動(dòng)Extract 報(bào)錯(cuò)的值，所以說這個(gè)值很重要。

 

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

 

現(xiàn)在看，我們的dpump 進(jìn)程已經(jīng)從dpump 進(jìn)行同步了。

 

驗(yàn)證：

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

 

同步正常。以上就是我們有關(guān)DataPump 的整個(gè)測試過程。

 

四． 總結(jié)

以上測試過程有點(diǎn)小亂，這里最后做一下總結(jié)：

 

1.     Data Pump 默認(rèn)情況下從/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 環(huán)境，那么Extract 和 Data Pump 都從/u01/ggate/dirdat/lt000000開始，所以這種情況同步?jīng)]有問題。

 

3.     如果是之前已經(jīng)存在的同步，之后改成Data Pump，注意這個(gè)trail 文件問題，修改稱datapump 之后，啟動(dòng)extract 進(jìn)程會(huì)報(bào)錯(cuò)，這個(gè)錯(cuò)誤提示會(huì)提示trail 文件號(hào)和RBA信息。 我們從Target 端copy 過來這個(gè)文件，然后使用如下命令：

GGSCI (gg1) 82> alter extract dpump,extseqno 2,extrba1965317

 

修改Data Pump 文件，使其從我們指定的trail 文件開始讀取catpure data，從而達(dá)到我們同步的目錄。

 

 

 

 

-------------------------------------------------------------------------------------------------------

版權(quán)所有，文章允許轉(zhuǎn)載，但必須以鏈接方式注明源地址，否則追究法律責(zé)任!

Blog:     http://blog.csdn.net/tianlesoftware

Weibo: http://weibo.com/tianlesoftware

Email:   tianlesoftware@gmail.com

Skype: tianlesoftware

 

-------加群需要在備注說明Oracle表空間和數(shù)據(jù)文件的關(guān)系，否則拒絕申請----

DBA1 群：62697716(滿);   DBA2 群：62697977(滿)  DBA3 群：62697850(滿)  

DBA 超級(jí)群：63306533(滿);  DBA4 群：83829929(滿) DBA5群： 142216823(滿) 

DBA6 群：158654907(滿)   DBA7 群：69087192(滿)  DBA8 群：172855474

DBA 超級(jí)群2：151508914  DBA9群：102954821     聊天 群：40132017(滿)