测试RAC下的ogg，在任意节点数据库关掉后，extract的运行状况

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可以自动重新启动再运行。