|
一.测试背景说明测试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 <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的序列号。 （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，从而达到我们同步的目录。
------------------------------------------------------------------------------------------------------- 版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任! Blog: http://blog.csdn.net/tianlesoftware Weibo: http://weibo.com/tianlesoftware Email: tianlesoftware@gmail.com Skype: tianlesoftware
-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请---- DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满) DBA 超级群：63306533(满); DBA4 群：83829929(满) DBA5群： 142216823(满) DBA6 群：158654907(满) DBA7 群：69087192(满) DBA8 群：172855474 DBA 超级群2：151508914 DBA9群：102954821 聊天 群：40132017(满) |
|
|
来自： jacklopy > 《GoldenGate》
