ALTER SEQUENCE 导致 REPLICAT 延时

最新推荐文章于 2025-01-05 10:42:51 发布

转载最新推荐文章于 2025-01-05 10:42:51 发布 · 119 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/polestar/p/8514145.html

文章标签：

#数据库

本文介绍了使用Oracle GoldenGate (OGG) 进行数据库同步过程中遇到的Sequence同步延迟问题，通过查看OGG线程状态及系统当前运行的SQL来定位问题，并提供了调整replicat参数以缓解同步延迟的方法。

1.查看OGG线程状态

GGSCI (klcoredb-node1) 46> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXTBI       unknown       00:00:04    
EXTRACT     RUNNING     PXTBI       00:00:00      00:00:02    
REPLICAT    RUNNING     REP_1B      14:32:46      00:00:02    
REPLICAT    RUNNING     REP_1X      00:00:00      00:00:01

2.查看replicate线程在什么
send REPLICAT REP_1B trace2 /home/oracle/trace2.log
send REPLICAT REP_1B trace /home/oracle/trace.log 


send  REPLICAT REP_1B trace2 of
send REPLICAT REP_1B trace off


---
10:38:16.263 (1326639) exited READ_EXTRACT_RECORD (stat=0, seqno=7511, rba=54995215)
10:38:16.263 (1326639) processing record for KLSMISSTAND.LP_GL_INTERFACE_SEQ
10:38:16.263 (1326639) mapping record
10:38:16.263 (1326639) entering perform_sql_statements (normal)
10:38:16.303 (1326679) exited perform_sql_statements (sql_err=0,recs output=41000)
10:38:16.303 (1326679) * --- entering READ_EXTRACT_RECORD --- *
10:38:16.303 (1326679) exited READ_EXTRACT_RECORD (stat=0, seqno=7511, rba=54995430)
10:38:16.303 (1326679) processing record for KLSMISSTAND.LP_GL_INTERFACE_SEQ
10:38:16.303 (1326679) mapping record
10:38:16.303 (1326679) entering perform_sql_statements (normal)
10:38:16.333 (1326709) exited perform_sql_statements (sql_err=0,recs output=41001)
10:38:16.333 (1326709) * --- entering READ_EXTRACT_RECORD --- *
10:38:16.333 (1326709) exited READ_EXTRACT_RECORD (stat=0, seqno=7511, rba=54995645)
10:38:16.333 (1326709) processing record for KLSMISSTAND.LP_GL_INTERFACE_SEQ
10:38:16.333 (1326709) mapping record
10:38:16.333 (1326709) entering perform_sql_statements (normal)
10:38:16.373 (1326749) exited perform_sql_statements (sql_err=0,recs output=41002)
10:38:16.373 (1326749) * --- entering READ_EXTRACT_RECORD --- *
10:38:16.373 (1326749) exited READ_EXTRACT_RECORD (stat=0, seqno=7511, rba=54995860)
10:38:16.373 (1326749) processing record for KLSMISSTAND.LP_GL_INTERFACE_SEQ
10:38:16.373 (1326749) mapping record
10:38:16.373 (1326749) entering perform_sql_statements (normal)
10:38:16.403 (1326779) exited perform_sql_statements (sql_err=0,recs output=41003)
10:38:16.403 (1326779) * --- entering READ_EXTRACT_RECORD --- *
10:38:16.403 (1326779) exited READ_EXTRACT_RECORD (stat=0, seqno=7511, rba=54996075)
10:38:16.403 (1326779) processing record for KLSMISSTAND.LP_GL_INTERFACE_SEQ

4.查看系统当前运行的sql
多次刷新发现sql

ALTER SEQUENCE "LP_GL_INTERFACE_SEQ"  NOCYCLE

5.查看当前sequnce的裱花

SQL> set time on
14:14:27 SQL> SELECT LAST_NUMBER,INCREMENT_BY FROM DBA_SEQUENCES WHERE SEQUENCE_NAME='LP_GL_INTERFACE_SEQ';

LAST_NUMBER INCREMENT_BY
----------- ------------
   24330470            1

14:14:35 SQL> /

LAST_NUMBER INCREMENT_BY
----------- ------------
   24330540            1

14:14:37 SQL> /

LAST_NUMBER INCREMENT_BY
----------- ------------
   24330582            1

14:14:39 SQL> /

6.缓解同步sequence延时 在replice相关参数文件中添加参数：
DBOPTIONS _NOAUTOMATICSEQUENCEFLUSH

--Metalink 说明

The replicat changes the last_number in target sequence by querying the nextval, but this change is not secured if the target database goes down.  The observed DDL will move the high water mark (HWM) of target sequence and make sure sequences replicate reliably for each sequence record.  

 
The DDL may be skipped with replicat parameter: 


DBOPTIONS _NOAUTOMATICSEQUENCEFLUSH


However, if target db goes down, the sequence number may be out of sync with source.  then you will need to use FLUSH to make sure they are in sync (or FLUSH it before the target goes down, for scheduled shutdown).

转载于:https://www.cnblogs.com/polestar/p/8514145.html