OGG时延问题记录
1.抽取进程延时
2.过程时间分析脚本
[oracle@node2 dirdat]$ cat test01.sh #!/bin/bash echo "sql before time is "`date`>> test.log sqlplus / as sysdba <<EOF @1.sql EOF echo "sql after time is "`date`>> test.log while true do echo `stat st000000033 |grep Modify` >>test.log done
insert into test07 (BIGINT_FIELD, DOUBLE_FIELD, BOOLEAN_FIELD, TIMESTAMP_FIELD, STRING_FIELD, START_TIME) values (1, 3, '', null, '', sysdate); truncate table test07; select * from test07 select * from ogg12t.test07; truncate table ogg12t.test07; select arr_time-start_time from ogg12t.test07;
源端
-- Create table
create table TEST07
(
BIGINT_FIELD INTEGER,
DOUBLE_FIELD NUMBER,
BOOLEAN_FIELD VARCHAR2(2),
TIMESTAMP_FIELD DATE,
STRING_FIELD VARCHAR2(1024),
START_TIME TIMESTAMP(6) default sysdate
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
next 8
minextents 1
maxextents unlimited
);
目标端：
create table OGG12T.TEST07 ( BIGINT_FIELD INTEGER, DOUBLE_FIELD NUMBER, BOOLEAN_FIELD VARCHAR2(2), TIMESTAMP_FIELD DATE, STRING_FIELD VARCHAR2(1024), START_TIME TIMESTAMP(6), ARR_TIME TIMESTAMP(6) default sysdate )
3.数据定义文件
这是数据定义文件的配置文件
GGSCI (node2) 122> edit param test_ogg
defsfile /ogghome12/ogghome/dirdef/ogg12s --这是定义文件的位置 userid ogg12s,password OGG12S table ogg12s.TEST032501; table ogg12s.test07;
./defgen paramfile ./dirprm/jzjj.prm
./defgen paramfile ./dirprm/test_ogg.prm
生成定义文件
[oracle@node2 ogghome]$ pwd
/ogghome12/ogghome
[oracle@node2 ogghome]$ ./defgen paramfile ./dirprm/test_ogg.prm
***********************************************************************
Oracle GoldenGate Table Definition Generator for Oracle
Version 12.2.0.2.2 OGGCORE_12.2.0.2.0_PLATFORMS_170630.0419
Linux, x64, 64bit (optimized), Oracle 11g on Jun 30 2017 11:35:56
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
Starting at 2021-07-13 12:36:54
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Wed Apr 13 00:51:26 EDT 2016, Release 2.6.32-642.el6.x86_64
Node: node2
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 116655
***********************************************************************
** Running with the following parameters **
***********************************************************************
defsfile /ogghome12/ogghome/dirdef/ogg12s
userid ogg12s,password ***
table ogg12s.TEST032501;
Retrieving definition for OGG12S.TEST032501.
table ogg12s.test07;
Retrieving definition for OGG12S.TEST07.
2021-07-13 12:37:01 WARNING OGG-06439 No unique key is defined for table TEST07. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be us
ed to define the key.
Definitions generated for 2 tables in /ogghome12/ogghome/dirdef/ogg12s
复制到目标端，改目标端的配置文件
2021-07-13 12:45:11 ERROR OGG-10107 (rep_test.prm) line 15: Parsing error, parameter [sourcedefs] conflicts with parameter [assumetargetdefs].
4.使用的脚本
truncate table test07;
truncate table ogg12t.test07;
select * from ogg12t.test07;
select to_char(start_time,'yyyy-mm-dd hh24:mi:ss.ff6') from test07;
select BIGINT_FIELD,to_char(start_time,'yyyy-mm-dd hh24:mi:ss.ff6'),to_char(arr_time,'yyyy-mm-dd hh24:mi:ss.ff6'),arr_time-start_time from ogg12t.test07;
select avg(t1.col) from (
select BIGINT_FIELD,
to_char(start_time, 'yyyy-mm-dd hh24:mi:ss.ff6'),
to_char(arr_time, 'yyyy-mm-dd hh24:mi:ss.ff6'),
arr_time - start_time,
(substr(arr_time - start_time, 18, 2) * 1000000 +
substr(arr_time - start_time, 21, 6))/1000000 as col
from ogg12t.test07) t1;
select arr_time-start_time from ogg12t.test07;
select to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss.ff6') from dual;
select current_timestamp from dual;
select localtimestamp from dual;
select systimestamp from dual;
select sysdate from dual;
5.参数解释
Use the FLUSHSECS or FLUSHCSECS parameters to control when Oracle GoldenGate flushes the Extract memory buffer. When sending data to remote systems, Extract buffers data to optimize network performance. The buffer is flushed to the target system when it is full or after the amount of time specified with FLUSHSECS or FLUSHCSECS. Data changes are not available to the target users until the buffer is flushed and the data is posted. To control the size of the buffer, use the TCPBUFSIZE option of RMTHOST. See "RMTHOST" for more information.
Increasing the value of FLUSHSECS or FLUSHCSECS could result in slightly more efficient use of the network, but it could increase the latency of the target data if activity on the source system is low and the buffer does not fill up. When source tables remain busy, FLUSHSECS and FLUSHCSECS have little effect.
This parameter cannot be set to zero (0).
Default
The default is 1. The minimum is 0; the maximum is 5000.
https://docs.oracle.com/en/middleware/goldengate/core/21.1/reference/flushsecs-flushcsecs.html#GUID-9F9F8FCD-81D5-4377-9A71-79B930304E35
Valid for
Extract, Replicat
Description
Use EOFDELAY or EOFDELAYCSECS to specify the number of seconds or centiseconds to delay before looking for more data. Increase the time interval to increase the lag time between updates on the source and target systems, especially when the source system is experiencing a small amount of activity.
This parameter only applies when Extract or Replicat is reading an Oracle GoldenGate trail.
Default
1 second
Syntax
seconds | EOFDELAYCSECS centiseconds https://docs.oracle.com/en/middleware/goldengate/non-stop/19.1/reference/eofdelay-eofdelaycsecs.html#GUID-AD7F6D3E-FEDE-4ACA-B84F-5CD234E205B7
6.函数，oracle函数 current_timestamp
【功能】：以timestamp with time zone数据类型返回当前会话时区中的当前日期
参考资料：1.https://www.cnblogs.com/margiex/p/8507180.html
2.http://www.itpub.net/thread-1609416-3-1.html
