ogg部署在一节点
extract配置参数
extract snext
setenv (NLS_LANG="AMERICAN_ AMERICA. AL32UTF8")
userid odc,password odc
exttrail ./dirdat/sn
tranlogoptions dblogreader
FETCHOPTIONS FETCHPKUPDATECOLS
ddl include objname scott.* exclude objtype 'TRIGGER'
table scott.*;
1.只关掉二节点数据库,一节点数据库正常运行
extract运行状态
GGSCI (racdb1) 131> info snext
EXTRACT SNEXT Last Started 2019-06-26 11:29 Status RUNNING
Checkpoint Lag unknown (updated 00:00:06 ago)
Log Read Checkpoint Oracle Redo Logs
2019-06-26 11:30:02 Thread 1, Seqno 68, RBA 111616
SCN 0.1826120 (1826120)
Log Read Checkpoint Oracle Redo Logs
2019-06-26 11:30:29 Thread 2, Seqno 63, RBA 2407424
SCN 0.1826119 (1826119)
关掉二节点数据库
[grid@racdb2 ~]$ srvctl stop instance -d rac -i rac2 -o immediate
关掉二节点数据库后,extract运行状态
GGSCI (racdb1) 161> info snext
EXTRACT SNEXT Last Started 2019-06-26 11:29 Status RUNNING
Checkpoint Lag 00:00:02 (updated 00:00:08 ago)
Log Read Checkpoint Oracle Redo Logs
2019-06-26 13:52:04 Thread 1, Seqno 68, RBA 9547264
SCN 0.1843248 (1843248)
Log Read Checkpoint Oracle Redo Logs
2019-06-26 13:51:06 Thread 2, Seqno 66, RBA 0
SCN 0.1843143 (1843143)
结论:关掉二节点数据库并不影响extract的运行,在info extrant中,可以看到Thread 2的RBA为0,表明没有读取到二节点的日志。
2. 只关掉一节点数据库,二节点数据库正常运行
extract运行状态
GGSCI (racdb1) 165> info snext
EXTRACT SNEXT Last Started 2019-06-26 11:29 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint Oracle Redo Logs
2019-06-26 13:55:57 Thread 1, Seqno 68, RBA 9673728
SCN 0.1845756 (1845756)
Log Read Checkpoint Oracle Redo Logs
2019-06-26 13:56:25 Thread 2, Seqno 66, RBA 135168
SCN 0.1845757 (1845757)
关掉一节点数据库
[grid@racdb2 ~]$ srvctl stop instance -d rac -i rac1 -o immediate
关掉一节点数据库后,extract运行状态
GGSCI (racdb1) 166> info snext
EXTRACT SNEXT Last Started 2019-06-26 11:29 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:00:51 ago)
Log Read Checkpoint Oracle Redo Logs
2019-06-26 13:57:41 Thread 1, Seqno 68, RBA 9756160
SCN 0.1846149 (1846149)
Log Read Checkpoint Oracle Redo Logs
2019-06-26 13:58:10 Thread 2, Seqno 66, RBA 193536
SCN 0.1846152 (1846152)
extract进程已经abended了,在ggserr.log中有以下错误日志:
2019-06-26 13:57:52 ERROR OGG-00665 Oracle GoldenGate Capture for Oracle, snext.prm: OCI Error executing single row select (status = 3135-ORA-03135: connection lost contact
Process ID: 20742
Session ID: 32 Serial number: 49), SQL<SELECT DECODE(archived, 'YES', 1, 0), status FROM v$log WHERE thread# = :ora_thread AND sequence# = :ora_seq_no>.
2019-06-26 13:57:52 ERROR OGG-00665 Oracle GoldenGate Capture for Oracle, snext.prm: OCI Error executing single row select (status = 3135-ORA-03135: connection lost contact
Process ID: 20747
Session ID: 49 Serial number: 29), SQL<SELECT DECODE(archived, 'YES', 1, 0), status FROM v$log WHERE thread# = :ora_thread AND sequence# = :ora_seq_no>.
2019-06-26 13:57:53 INFO OGG-00991 Oracle GoldenGate Capture for Oracle, snext.prm: EXTRACT SNEXT stopped normally.
再次启动extract进程,状态还是abended
GGSCI (racdb1) 167> start snext
Sending START request to MANAGER ...
EXTRACT SNEXT starting
GGSCI (racdb1) 168> info snext
EXTRACT SNEXT Last Started 2019-06-26 11:29 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:03:12 ago)
Log Read Checkpoint Oracle Redo Logs
2019-06-26 13:57:41 Thread 1, Seqno 68, RBA 9756160
SCN 0.1846149 (1846149)
Log Read Checkpoint Oracle Redo Logs
2019-06-26 13:58:10 Thread 2, Seqno 66, RBA 193536
SCN 0.1846152 (1846152)
结论:和1对比可以发现,在关掉一节点数据后,extract才会abended,原因就是在extract的配置参数userid odc,所以extract连接的是一节点的数据库,由于无法连接到一节点数据库就导致了abended,从ggserr日志中可以发现ORA-03135: connection lost contact。
那么如果配置了userid odc@service(scanip服务),是不是就不会导致extract发生abended了呢?接下来做这个实验。
修改extract参数userid odc@rac,rac为scanip的服务名。
3.只关掉二节点数据库,一节点数据库正常运行
extract运行状态
GGSCI (rracdb1) 58> info snext
EXTRACT SNEXT Last Started 2019-06-26 15:29 Status RUNNING
Checkpoint Lag 00:00:01 (updated 00:00:08 ago)
Log Read Checkpoint Oracle Redo Logs
2019-06-26 15:32:32 Thread 1, Seqno 71, RBA 189952
SCN 0.1865242 (1865242)
Log Read Checkpoint Oracle Redo Logs
2019-06-26 15:33:00 Thread 2, Seqno 69, RBA 122368
SCN 0.1865242 (1865242)
关掉二节点数据库
$ srvctl stop instance -d rac -i rac2 -o immediate
关掉二节点数据库后,extract运行状态
GGSCI (rracdb1) 62> info snext
EXTRACT SNEXT Last Started 2019-06-26 15:29 Status RUNNING
Checkpoint Lag 00:00:29 (updated 00:00:05 ago)
Log Read Checkpoint Oracle Redo Logs
2019-06-26 15:36:24 Thread 1, Seqno 71, RBA 316928
SCN 0.1866004 (1866004)
Log Read Checkpoint Oracle Redo Logs
2019-06-26 15:34:02 Thread 2, Seqno 70, RBA 0
SCN 0.1865685 (1865685)
结论:和1类似,关掉二节点数据库并不影响extract的运行,在info extrant中,可以看到Thread 2的RBA为0,表明没有读取到二节点的日志。
4.只关掉一节点数据库,二节点数据库正常运行
extract运行状态
GGSCI (rracdb1) 65> info snext
EXTRACT SNEXT Last Started 2019-06-26 15:29 Status RUNNING
Checkpoint Lag 00:00:01 (updated 00:00:04 ago)
Log Read Checkpoint Oracle Redo Logs
2019-06-26 15:41:18 Thread 1, Seqno 71, RBA 482304
SCN 0.1868514 (1868514)
Log Read Checkpoint Oracle Redo Logs
2019-06-26 15:41:46 Thread 2, Seqno 70, RBA 131584
SCN 0.1868516 (1868516)
关掉一节点数据库
$ srvctl stop instance -d rac -i rac1 -o immediate
关掉一节点数据库后,extract运行状态
GGSCI (rracdb1) 69> info snext
EXTRACT SNEXT Last Started 2019-06-26 15:29 Status ABENDED
Checkpoint Lag 00:00:01 (updated 00:01:51 ago)
Log Read Checkpoint Oracle Redo Logs
2019-06-26 15:42:31 Thread 1, Seqno 71, RBA 519680
SCN 0.1868635 (1868635)
Log Read Checkpoint Oracle Redo Logs
2019-06-26 15:42:58 Thread 2, Seqno 70, RBA 169472
SCN 0.1868635 (1868635)
extract进程已经abended了,在ggserr.log中有以下错误日志:
2019-06-26 15:43:00 ERROR OGG-00665 Oracle GoldenGate Capture for Oracle, snext.prm: OCI Error executing single row select (status = 1089-ORA-01089: immediate shutdown in progress - no operations are permitted
Process ID: 12913
Session ID: 68 Serial number: 9), SQL<SELECT member, DECODE(status, 'CURRENT', 1, 0), DECODE(archived, 'YES', 1, 0) FROM (select lf.member, l.status, 1.archived from v$logfile lf, v$log l WHERE lf.group# = l.group# AND l.thread# = :ora_thread AND l.sequence# = :ora_seq_no AND (lf.status NOT IN ('INVALID','INCOMPLETE','STALE') OR lf.status is null) order by lf.member DESC ) where rownum = 1>.
2019-06-26 15:43:00 ERROR OGG-00665 Oracle GoldenGate Capture for Oracle, snext.prm: OCI Error executing single row select (status = 1089-ORA-01089: immediate shutdown in progress - no operations are permitted
Process ID: 12907
Session ID: 66 Serial number: 27), SQL<SELECT member, DECODE(status, 'CURRENT', 1, 0), DECODE(archived, 'YES', 1, 0) FROM (select lf.member, l.status, 1.archived from v$logfile lf, v$log l WHERE lf.group# = l.group# AND l.thread# = :ora_thread AND l.sequence# = :ora_seq_no AND (lf.status NOT IN ('INVALID','INCOMPLETE','STALE') OR lf.status is null) order by lf.member DESC ) where rownum = 1>.
2019-06-26 15:43:00 INFO OGG-00991 Oracle GoldenGate Capture for Oracle, snext.prm: EXTRACT SNEXT stopped normally.
再次启动extract进程,状态是running,达到了我的预期目的
GGSCI (rracdb1) 79> info snext
EXTRACT SNEXT Last Started 2019-06-26 15:46 Status RUNNING
Checkpoint Lag 00:00:02 (updated 00:00:06 ago)
Log Read Checkpoint Oracle Redo Logs
2019-06-26 15:42:37 Thread 1, Seqno 72, RBA 0
SCN 0.1869050 (1869050)
Log Read Checkpoint Oracle Redo Logs
2019-06-26 15:47:01 Thread 2, Seqno 70, RBA 345088
SCN 0.1869435 (1869435)
结论:在一节点数据库关闭后,根据scanip的特性,scanip会跳到二节点上,这样在重启extract时,实际上extract连接的是二节点数据库,由于mgr有配置重启extract的参数,所以通过配置userid odc@rac,就能够保证在一个节点数据库关闭后,extract可以自动重新启动再运行。