@源端
GGSCI (rhlinux) 21> stop ext_1
Sending STOP request to EXTRACT EXT_1 ...
Request processed.
GGSCI (rhlinux) 22> stop pump_1
Sending STOP request to EXTRACT PUMP_1 ...
Request processed.
GGSCI (rhlinux) 23> start ext_1
Sending START request to MANAGER ...
EXTRACT EXT_1 starting
GGSCI (rhlinux) 24> start pump_1
Sending START request to MANAGER ...
EXTRACT PUMP_1 starting
GGSCI (rhlinux) 25> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_1 00:00:19 00:00:04
EXTRACT RUNNING E_TMP 00:00:00 00:00:09
EXTRACT RUNNING PUMP_1 00:00:00 00:00:16
EXTRACT RUNNING P_TMP 00:00:00 00:00:07
GGSCI (rhlinux) 27> !
info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_1 00:00:00 00:00:08
EXTRACT RUNNING E_TMP 00:00:00 00:00:03
EXTRACT RUNNING PUMP_1 00:00:00 00:00:00
EXTRACT RUNNING P_TMP 00:00:00 00:00:01
GGSCI (rhlinux) 28> view params ext_1
extract ext_1
userid ogg,password ogg
exttrail ./dirdat/e1
table scott.emp;
table scott.dept;
table scott.t;
table scott.tv;
table scott.t2;
GGSCI (rhlinux) 29> view params pump_1
extract pump_1
userid ogg ,password ogg
rmthost 192.168.73.134, mgrport 7811
rmttrail ./dirdat/p1
--passthru
table scott.emp;
table scott.t;
table scott.dept;
table scott.tv;
table scott.t2;
@目标端
GGSCI (ogg) 20> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_1 00:00:23 00:00:05
REPLICAT RUNNING R_TMP 00:00:00 00:00:09
GGSCI (ogg) 21> view params rep_1
replicat rep_1
userid ogg,password ogg
assumetargetdefs
reperror(1,discard)
reperror(1403,discard)
discardfile /opt/ogg/discard/e1.log,append,megabytes 10
map scott.emp , target scott.emp;
map scott.dept, target scott.dept;
map scott.t,target scott.t;
map scott.t2,target scott.t2;
map scott.tv,target scott.tv;
重启投递进程
GGSCI (ogg) 23> stop rep_1
Sending STOP request to REPLICAT REP_1 ...
Request processed.
GGSCI (ogg) 24> start rep_1
Sending START request to MANAGER ...
REPLICAT REP_1 starting
@源端
重启抽取和投递进程
GGSCI (rhlinux) 33> stop ext_1
Sending STOP request to EXTRACT EXT_1 ...
Request processed.
GGSCI (rhlinux) 34> stop pump_1
Sending STOP request to EXTRACT PUMP_1 ...
Request processed.
GGSCI (rhlinux) 35> start ext_1
Sending START request to MANAGER ...
EXTRACT EXT_1 starting
GGSCI (rhlinux) 36> start pump_1
Sending START request to MANAGER ...
EXTRACT PUMP_1 starting
插入数据:
SQL> insert into scott.tv select 23,23,23 from dual;
1 row created.
SQL> commit;
Commit complete.
@目标端
查看同步数据量
GGSCI (ogg) 25> stats rep_1,hourly
Sending STATS request to REPLICAT REP_1 ...
Start of Statistics at 2015-08-08 10:44:58.
Replicating from SCOTT.TV to SCOTT.TV:
*** Hourly statistics since 2015-08-08 10:44:46 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
以上是正常操作。
下面是源端事务未提交时如何将数据写入trail文件
1.源端强制写入trial后,回滚事务
@源端
插入数据但不提交:
SQL> insert into scott.tv select 234,234,234 from dual;
1 row created.
使用send命令查看未提交事务:
GGSCI (rhlinux) 40> send ext_1,showtrans
Sending SHOWTRANS request to EXTRACT EXT_1 ...
Oldest redo log file necessary to restart Extract is:
Redo Log Sequence Number 631, RBA 3629584
------------------------------------------------------------
XID: 19.16.2535
Items: 1
Extract: EXT_1
Redo Thread: 1
Start Time: 2015-07-16:12:23:55
SCN: 0.10492565 (10492565)
Redo Seq: 631
Redo RBA: 3629584
Status: Running
根据XID将该事务强制写入trail文件:
GGSCI (rhlinux) 41> send ext_1,forcetrans 19.16.2535
Sending forcetrans request to EXTRACT EXT_1 ...
Are you sure you sure you want to force transaction to trail file [XID 19.16.2535, Redo Thread 1, Start Time 2015-07-16:12:23:55, SCN 0.10492565 (10492565)] (y/n)?y
Sending forcetrans request to EXTRACT EXT_1 ...
Transaction [XID 19.16.2535, Redo Thread 1, Start Time 2015-07-16:12:23:55, SCN 0.10492565 (10492565)] forced to trail file.
几秒钟后查看pump_1进程,该insert语句已经写入trail
GGSCI (rhlinux) 43> stats pump_1,hourly
Sending STATS request to EXTRACT PUMP_1 ...
Start of Statistics at 2015-07-16 12:25:04.
Output to ./dirdat/p1:
Extracting from SCOTT.TV to SCOTT.TV:
*** Hourly statistics since 2015-07-16 12:19:54 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
End of Statistics.
@目标端
确认已同步:
SQL> select * from scott.tv;
T1 T2 T3
---------- ---------- --------------------------------------------------
234 234 234
23 23 23
@源端:
SQL> insert into scott.tv select 2345,2345,2345 from dual;
1 row created.
SQL> rollback;
Rollback complete.
SQL> select * from scott.tv;
T1 T2 T3
---------- ---------- ------------------------------
23 23 23
@目标端:
SQL> select * from scott.tv;
T1 T2 T3
---------- ---------- --------------------------------------------------
234 234 234
23 23 23
2.源端强制写入trail后,提交事务:
将目标端"T1=234"的记录重复上述操作,将rollback改为commit:
@源端:
SQL> select * from scott.tv;
T1 T2 T3
---------- ---------- ------------------------------
23 23 23
插入数据
SQL> insert into scott.tv select 234,234,234 from dual;
1 row created.
查看事务并将其强制写入trail
GGSCI (rhlinux) 45> send ext_1,showtrans
Sending SHOWTRANS request to EXTRACT EXT_1 ...
Oldest redo log file necessary to restart Extract is:
Redo Log Sequence Number 631, RBA 3767312
------------------------------------------------------------
XID: 12.13.2533
Items: 1
Extract: EXT_1
Redo Thread: 1
Start Time: 2015-07-16:12:32:46
SCN: 0.10492954 (10492954)
Redo Seq: 631
Redo RBA: 3767824
Status: Running
GGSCI (rhlinux) 46> send ext_1,forcetrans 12.13.2533
Sending forcetrans request to EXTRACT EXT_1 ...
Are you sure you sure you want to force transaction to trail file [XID 12.13.2533, Redo Thread 1, Start Time 2015-07-16:12:32:46, SCN 0.10492954 (10492954)] (y/n)?y
Sending forcetrans request to EXTRACT EXT_1 ...
Transaction [XID 12.13.2533, Redo Thread 1, Start Time 2015-07-16:12:32:46, SCN 0.10492954 (10492954)] forced to trail file.
继续在该事务中插入数据并提交操作
SQL> insert into scott.tv select 2345,2345,2345 from dual;
1 row created.
SQL> commit;
Commit complete.
@目标端:
查看同步结果
SQL> select * from scott.tv;
T1 T2 T3
---------- ---------- --------------------------------------------------
234 234 234
23 23 23
3.源端跳过事务,之后提交操作
@源端
清理垃圾数据:
SQL> select * from scott.tv;
T1 T2 T3
---------- ---------- ------------------------------
23 23 23
234 234 234
2345 2345 2345
SQL> delete from scott.tv where t1 in (234,2345);
2 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from scott.tv;
T1 T2 T3
---------- ---------- ------------------------------
23 23 23
插入测试数据:
SQL> insert into scott.tv select 234,234,234 from dual;
1 row created.
跳过该事务:
GGSCI (rhlinux) 47> send ext_1,showtrans
Sending SHOWTRANS request to EXTRACT EXT_1 ...
Oldest redo log file necessary to restart Extract is:
Redo Log Sequence Number 631, RBA 4039184
------------------------------------------------------------
XID: 14.4.2547
Items: 1
Extract: EXT_1
Redo Thread: 1
Start Time: 2015-07-16:12:39:23
SCN: 0.10493336 (10493336)
Redo Seq: 631
Redo RBA: 4039184
Status: Running
GGSCI (rhlinux) 48> send ext_1,skiptrans 14.4.2547
Sending skiptrans request to EXTRACT EXT_1 ...
Are you sure you sure you want to skip transaction [XID 14.4.2547, Redo Thread 1, Start Time 2015-07-16:12:39:23, SCN 0.10493336 (10493336)]? (y/n)y
Sending skiptrans request to EXTRACT EXT_1 ...
Transaction [XID 14.4.2547, Redo Thread 1, Start Time 2015-07-16:12:39:23, SCN 0.10493336 (10493336)] skipped.
继续插入一条数据,并提交:
SQL> insert into scott.tv select 2345,2345,2345 from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from scott.tv;
T1 T2 T3
---------- ---------- ------------------------------
23 23 23
234 234 234
2345 2345 2345
@目标端:
SQL> select * from scott.tv;
T1 T2 T3
---------- ---------- --------------------------------------------------
23 23 23
源端ogg跳过事务后,回滚该事务的实验略。
综上可知,
(1)使用如下命令可以查看当前未提交事务:
send extract <进程名>,showtrans [thread n ] [count n]
(2)使用如下命令跳过特定事务,该事务不会被同步到目标端
send extract <进程名>,skiptrans <XID> thread <xxx>
(3)使用如下命令将事务当前状态写入trail,源端该事务的后续操作不会记录到trail,
源端该事务后续的回滚和提交动作只影响其本身,不影响目标端
Send extract <进程名>,forcetrans <XID> thread <xxx>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26451536/viewspace-1813704/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26451536/viewspace-1813704/