OGG时延问题记录

转载

mob604756fb13b1 2021-07-13 11:23:00

文章标签 oracle sql 数据 linux 配置文件 文章分类 代码人生

1.抽取进程延时

OGG时延问题记录_配置文件

OGG时延问题记录_sql_02

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

OGG时延问题记录_数据_03

defsfile /ogghome12/ogghome/dirdef/ogg12s  --这是定义文件的位置
userid ogg12s,password OGG12S
table ogg12s.TEST032501;
table ogg12s.test07;

OGG时延问题记录_oracle_04

./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

　复制到目标端，改目标端的配置文件

OGG时延问题记录_数据_05