GOLDENGATE EXTRACT在DATABASE SWITCHOVER后表现以及处理方案

徐靖

发布于 2020-08-05 15:33:07

6790

发布于 2020-08-05 15:33:07

文章被收录于专栏：DB说DB说

【背景以及操作过程】

OGG版本是12.2,DATABASE版本是11.2.0.3 RAC.

场景1:数据库SWITCHOVER切换之前停止OGG CLASSIC EXTRACT进程,切换之后修改OGG访问新主库,OGG EXTRACT进程RBA不移动也不会报错.

场景2:数据库SWITCHOVER切换之前,没有停止OGG CLASSIC EXTRACT进程且EXTARCT无延迟,完成SWITCHOVER后,OGG提示数据库角色发生变成.ERROR OGG-02803,

修改访问新主库还是一样的错误.

以上2种情况都是因为database switchover后发生异常情况

OGG版本是11.2,DATABASE版本是11.2.0.3 RAC.

场景3:数据库SWITCHOVER切换之前停止OGG CLASSIC EXTRACT进程,切换之后修改OGG访问新主库,OGG EXTRACT无异常.

场景4:数据库SWITCHOVER切换之前没有停止OGG CLASSIC EXTRACT进程,切换之后修改OGG访问新主库,OGG EXTRACT无异常.

【总结】

针对OGG 12.2与11.2对于database switchover处理行为稍微存在一定差异.12.2需要手动干预，classic 11.2完全无视database role change且更加人性化.

【场景1】

1、数据库switchover后查看OGG进程状态--RBA不发生变化且不报错

GGSCI> info exiaoxu

EXTRACT    EXIAOXU   Last Started 2019-04-16 14:18   Status RUNNING
Checkpoint Lag       08:56:40 (updated 00:00:00 ago)
Process ID           201822
Log Read Checkpoint  Oracle Redo Logs
                     2019-04-16 05:06:39  Thread 1, Seqno 199715, RBA 33792
                     SCN 314.4245744779 (1352865475723)
Log Read Checkpoint  Oracle Redo Logs
                     2019-04-16 05:23:14  Thread 2, Seqno 216364, RBA 147568
                     SCN 314.4246055908 (1352865786852)
GGSCI> info exiaoxu detail
Target Extract Trails:
Trail Name                                       Seqno        RBA     Max MB Trail Type
./dirdat/xu                                      38           1482    500    EXTTRAIL

2、查看STANDBY_BECAME_PRIMARY_SCN的值

SQL> select STANDBY_BECAME_PRIMARY_SCN from v$database;
STANDBY_BECAME_PRIMARY_SCN
--------------------------
      1352865495728
3、查看SCN属于的ARCHIVELOG
select thread#,sequence#,first_change#,next_change#,first_time,next_time,blocks,block_size,end_of_redo_type from v$archived_log
where sequence#>=199715 and dest_id=1 and thread#=1 and rownum<= 2;
      THREAD# SEQUENCE#  FIRST_CHANGE#        NEXT_CHANGE# FIRST_TIME      NEXT_TIME    BLOCKS    BLOCK_SIZE     END_OF_RED
-------------------- -------------------- -------------------- -------------------- ------------------- ------------------- -------------------- -------------------- ----------
    1    199715  1352865475499       1352865495730 2019-04-16 05:06:33 2019-04-16 05:06:40   65   512           SWITCHOVER
    1    199716  1352865495730       1352865495733 2019-04-16 05:07:53 2019-04-16 05:07:56 
select thread#,sequence#,first_change#,next_change#,first_time,next_time,blocks,block_size,end_of_redo_type from v$archived_log
where sequence#>=216364 and dest_id=1 and thread#=2 and rownum<= 2;
      THREAD# SEQUENCE#  FIRST_CHANGE#        NEXT_CHANGE# FIRST_TIME      NEXT_TIME   BLOCKS    BLOCK_SIZE END_OF_RED
-------------------- -------------------- -------------------- -------------------- ------------------- ------------------- -------------------- -------------------- ----------
    2    216364  1352865786079       1352865786888 2019-04-16 05:23:11 2019-04-16 05:23:14      290   512
    2    216365  1352865786888       1352866289485 2019-04-16 05:23:14 2019-04-16 05:30:43    15989   512

从上面看出thread 1的199715是发生database role改变时候的日志.199716是切换后第一个日志.

4、查看延迟

GGSCI> send exiaoxu getlag
Sending GETLAG request to EXTRACT EXIAOXU ...
No records yet processed.

5、只能手动跳过到正确SCN

备注(粗暴方式):如果能确认切换后无数据写入且已处理完成日志,alter extract xxx,begin now

SCN即STANDBY_BECOME_PRIMARY后的SCN:1352865495730

GGSCI> alter exiaoxu,scn 1352865495730 thread 1
EXTRACT altered.
GGSCI> info exiaoxu
EXTRACT    EXIAOXU   Initialized   2019-04-16 17:12   Status STOPPED
Checkpoint Lag       11:49:12 (updated 00:00:15 ago)
Log Read Checkpoint  Oracle Redo Logs
                     First Record         Thread 1, Seqno 0, RBA 0
                     SCN 314.4245764786 (1352865495730)
Log Read Checkpoint  Oracle Redo Logs
                     2019-04-16 05:23:14  Thread 2, Seqno 216364, RBA 147568
                     SCN 314.4246055908 (1352865786852)
GGSCI> start exiaoxu
GGSCI> info exiaoxu
EXTRACT    EXIAOXU   Last Started 2019-04-16 17:13   Status RUNNING
Checkpoint Lag       10:16:13 (updated 00:00:02 ago)
Process ID           288955
Log Read Checkpoint  Oracle Redo Logs
                     2019-04-16 06:57:24  Thread 1, Seqno 199727, RBA 2161816
                     SCN 315.17397918 (1352932096158)
Log Read Checkpoint  Oracle Redo Logs
                     2019-04-16 06:57:02  Thread 2, Seqno 216371, RBA 1232684
                     SCN 315.17386172 (1352932084412)

至此OGG正常运行.

【场景2】

1、数据库switchover后查看OGG进程状态

GGSCI> info exiaoxu
EXTRACT    EXIAOXU   Last Started 2019-04-15 01:19:28   Status ABEND
Checkpoint Lag       08:56:40 (updated 00:00:00 ago)
Process ID           201822
Log Read Checkpoint  Oracle Redo Logs
                     2019-04-15 01:19:28  Thread 1, Seqno 199715, RBA 33792
                     SCN 314.4245744779 (1352865475723)
Log Read Checkpoint  Oracle Redo Logs
                     2019-04-15 01:19:28  Thread 2, Seqno 216364, RBA 147568
                     SCN 314.4246055908 (1352865786852)

2、查看exiaoxu report

2019-04-15 01:19:28  ERROR   OGG-02803  Encountered a Data Guard role transition. Alter Extract to SCN
 1,352,354,809,899 and restart Extract, or recreate Extract with the correct number of threads at SCN 
1,352,354,809,899.

3、查看STANDBY_BECAME_PRIMARY_SCN的值

SQL> select STANDBY_BECAME_PRIMARY_SCN from v$database;
STANDBY_BECAME_PRIMARY_SCN
--------------------------
      1352354809897

4、增加参数TRANLOGOPTIONS USEPREVRESETLOGSID

再次重启extract后继续报错后移除

5、(注意点)手动修改SCN,注意需要指定thread.否则对于rac来说，相当修改所有线程

GGSCI> alter exiaoxu,scn 1352354809899 thread 1
EXTRACT altered.
GGSCI> start exiaoxu
GGSCI> info exiaoxu
EXTRACT    EXIAOXU   Last Started 2019-04-15 01:19:28   Status RUNNING
Checkpoint Lag       08:56:40 (updated 00:00:00 ago)
Process ID           201822
Log Read Checkpoint  Oracle Redo Logs
                     2019-04-15 01:19:28  Thread 1, Seqno 199715, RBA 33792
                     SCN 314.4245744779 (1352354809899)
Log Read Checkpoint  Oracle Redo Logs
                     2019-04-15 01:19:28  Thread 2, Seqno 216364, RBA 147568
                     SCN 314.4246055908 (1352865786852)

至此OGG正常运行.

【场景3和4】

使用OGG 11.2版本,数据库SWITCHOVER切换之前不管停止还是运行OGG CLASSIC EXTRACT进程,切换之后修改OGG访问新主库,OGG EXTRACT无异常.

其中数据库switchover是thread 1的sequence=199715,经过验证发现从sequence=199711直接跳过199722

select thread#,sequence#,first_change#,next_change#,first_time,next_time,blocks,block_size,end_of_redo_type from v$archived_log
where sequence#>=199715 and dest_id=1 and thread#=1 and rownum<= 2;
      THREAD# SEQUENCE#  FIRST_CHANGE#        NEXT_CHANGE# FIRST_TIME      NEXT_TIME    BLOCKS    BLOCK_SIZE     END_OF_RED
-------------------- -------------------- -------------------- -------------------- ------------------- ------------------- -------------------- -------------------- ----------
    1    199715  1352865475499       1352865495730 2019-04-16 05:06:33 2019-04-16 05:06:40   65   512           SWITCHOVER
GGSCI 53> info txiaoxu
EXTRACT    TXIAOXU   Last Started 2019-04-16 17:51   Status RUNNING
Checkpoint Lag       13:13:46 (updated 00:00:04 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2019-04-16 04:30:37  Thread 1, Seqno 199711, RBA 89432968
                     SCN 314.4211199359 (1352830930303)
Log Read Checkpoint  Oracle Redo Logs
                     2019-04-16 04:38:09  Thread 2, Seqno 216360, RBA 2564624
                     SCN 314.4217687497 (1352837418441)
GGSCI54> info txiaoxu
EXTRACT    TXIAOXU   Last Started 2019-04-16 17:51   Status RUNNING
Checkpoint Lag       11:57:19 (updated 00:00:00 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2019-04-16 05:55:29  Thread 1, Seqno 199722, RBA 470532576
                     SCN 314.4291562433 (1352911293377)
Log Read Checkpoint  Oracle Redo Logs
                     2019-04-16 05:52:04  Thread 2, Seqno 216368, RBA 394529532
                     SCN 314.4290543373 (1352910274317)

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2019-04-16，如有侵权请联系 cloudcommunity@tencent.com 删除

css