Oracle GoldenGate心跳表检测延迟

最新推荐文章于 2024-05-23 17:32:38 发布

沧hi

最新推荐文章于 2024-05-23 17:32:38 发布

阅读量2.7k

点赞数 1

分类专栏：性能优化日常问题处理文章标签： oracle linux GoldenGate

本文链接：https://blog.csdn.net/zjltju1203/article/details/120543554

版权

日常问题处理同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

性能优化

2 篇文章 0 订阅

订阅专栏

环境：

Centos 7 虚拟机（1核、1G内存）* 2

Oracle 11.2.0.2.0 * 2

Oracle GoldenGate 11.2.1.0.3

源端IP:192.168.153.9 目标端IP:192.168.153.10

一，源端数据库设置

1.1,创建源端Oracle 测试账户

SQL> create user goldengate identified by 123 default tablespace users;

SQL> grant dba to goldengate;

1.2,创建源端心跳表

CREATE TABLE HEARTBEAT(
  "ID" NUMBER,
  "SRC_DB" VARCHAR2(30 BYTE) NOT NULL,
  "EXTRACT_NAME" VARCHAR2(8 BYTE),
  "SOURCE_COMMIT" TIMESTAMP(6),
  "TARGET_COMMIT" TIMESTAMP(6),
  "CAPTIME" TIMESTAMP(6),
  "CAPLAG" NUMBER,
  "PMPTIME" TIMESTAMP(6),
  "PMPGROUP" VARCHAR2(8 BYTE),
  "PMPLAG" NUMBER,
  "DELTIME" TIMESTAMP(6),
  "DELGROUP" VARCHAR2(8 BYTE),
  "DELFLAG" NUMBER,
  "TOTALLAG" NUMBER,
  "THREAD" NUMBER,
  "UPDATE_TIMESTAMP" TIMESTAMP(6),
  "EDDLDELTASTATS" NUMBER,
  "EDMLDELTASTATS" NUMBER,
  "RDDLDELTASTATS" NUMBER,
  "RDMLDELTASTATS" NUMBER,
   PRIMARY KEY ("SRC_DB"),
  CHECK ("SRC_DB" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE
);

1.3,创建源端更新存储程序

CREATE OR REPLACE PROCEDURE gg_update_hb_tab IS v_thread_num NUMBER;
BEGIN
		UPDATE HEARTBEAT 
		SET update_timestamp = SYSTIMESTAMP,
		source_commit = SYSTIMESTAMP,
		src_db = 'XE';
	Commit;
END;

1.4，创建源端定时器，每分钟更新一次记录。

BEGIN
		SYS.DBMS_SCHEDULER.DROP_JOB (
			job_name => 'OGG_HB',
			defer => false,
			force => false 
		);
END;

BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB (
	job_name => 'OGG_HB',
		job_type => 'STORED_PROCEDURE',
		job_action => 'GG_UPDATE_HB_TAB',
		number_of_arguments => 0,
		start_date => to_date( NULL ),
		repeat_interval => 'Freq=MINUTELY;INTERVAL=1',
		end_date => to_date( NULL ),
		job_class => 'DEFAULT_JOB_CLASS',
		enabled => FALSE,
		auto_drop => FALSE,
		comments => 'GoldenGate',
		credential_name => NULL,
		destination_name => NULL 
	);
	SYS.DBMS_SCHEDULER.SET_ATTRIBUTE (
		name => 'OGG_HB',
		attribute => 'restartable',
		value => TRUE 
	);
	SYS.DBMS_SCHEDULER.SET_ATTRIBUTE (
		name => 'OGG_HB',
		attribute => 'logging_level',
		value => DBMS_SCHEDULER.LOGGING_OFF 
	);
	SYS.DBMS_SCHEDULER.enable ( name => 'OGG_HB' );
END;

1.5，查看存储过程和JOB是否运行正常；

SELECT * from user_procedures WHERE OBJECT_NAME = 'GG_UPDATE_HB_TAB';

select job_name,job_type,job_action,to_char(start_date,'yyyy-mm-dd hh24:mi:ss') TM,repeat_interval,next_run_date,enabled,state from user_scheduler_jobs;

二，目标端数据库设置

2.1 创建目标端Oracle 测试账户

SQL> create user goldengate identified by 123 default tablespace users;

SQL> grant dba to goldengate;

2.2,创建目标端心跳表

CREATE TABLE GGS_HEARTBEAT(
	"ID" NUMBER,
  "SRC_DB" VARCHAR2(30 BYTE) NOT NULL,
  "EXTRACT_NAME" VARCHAR2(8 BYTE),
  "SOURCE_COMMIT" TIMESTAMP(6),
  "TARGET_COMMIT" TIMESTAMP(6),
  "CAPTIME" TIMESTAMP(6),
  "CAPLAG" NUMBER,
  "PMPTIME" TIMESTAMP(6),
  "PMPGROUP" VARCHAR2(8 BYTE),
  "PMPLAG" NUMBER,
  "DELTIME" TIMESTAMP(6),
  "DELGROUP" VARCHAR2(8 BYTE),
  "DELFLAG" NUMBER,
  "TOTALLAG" NUMBER,
  "THREAD" NUMBER,
  "UPDATE_TIMESTAMP" TIMESTAMP(6),
  "EDDLDELTASTATS" NUMBER,
  "EDMLDELTASTATS" NUMBER,
  "RDDLDELTASTATS" NUMBER,
  "RDMLDELTASTATS" NUMBER,
   PRIMARY KEY ("SRC_DB"),
  CHECK ("SRC_DB" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE
);

2.3 创建目标心跳历史记录表

CREATE TABLE GGS_HEARTBEAT_HISTORY (
  "ID" NUMBER NOT NULL,
  "SRC_DB" VARCHAR2(30 BYTE),
  "EXTRACT_NAME" VARCHAR2(8 BYTE),
  "SOURCE_COMMIT" TIMESTAMP(6),
  "TARGET_COMMIT" TIMESTAMP(6),
  "CAPTIME" TIMESTAMP(6),
  "CAPLAG" NUMBER,
  "PMPTIME" TIMESTAMP(6),
  "PMPGROUP" VARCHAR2(8 BYTE),
  "PMPLAG" NUMBER,
  "DELTIME" TIMESTAMP(6),
  "DELGROUP" VARCHAR2(8 BYTE),
  "DELFLAG" NUMBER,
  "TOTALLAG" NUMBER,
  "THREAD" NUMBER,
  "UPDATE_TIMESTAMP" TIMESTAMP(6),
  "EDDLDELTASTATS" NUMBER,
  "EDMLDELTASTATS" NUMBER,
  "RDDLDELTASTATS" NUMBER,
  "RDMLDELTASTATS" NUMBER,
  CONSTRAINT "SYS_C007013" PRIMARY KEY ("ID"),
  CONSTRAINT "SYS_C007012" CHECK ("SRC_DB" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE,
  CONSTRAINT "SYS_C007014" CHECK ("ID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE
)

2.4 创建HEARTBEAT表触发器

CREATE SEQUENCE seq_ggs_HEARTBEAT_id INCREMENT BY 1 START WITH 1 ORDER;

CREATE OR REPLACE TRIGGER GGS_HEARTBEAT_TRIG
BEFORE INSERT OR UPDATE ON GGS_HEARTBEAT
FOR EACH ROW
BEGIN
SELECT seq_ggs_HEARTBEAT_id.nextval
into :NEW.ID
FROM dual;
SELECT SYSTIMESTAMP INTO :NEW.TARGET_COMMIT FROM dual;
SELECT TRUNC(TO_NUMBER(SUBSTR((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), 1, INSTR(:NEW.CAPTIME - :NEW.SOURCE_COMMIT, ' ')))) * 86400 
+ TO_NUMBER(SUBSTR((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), INSTR((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), ' ')+1, 2)) * 3600
+ TO_NUMBER(SUBSTR((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), INSTR((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), ' ')+4, 2)) * 60
+ TO_NUMBER(SUBSTR((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), INSTR((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), ' ')+7, 2))
+ TO_NUMBER(SUBSTR((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), INSTR((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), ' ')+10, 6)) / 1000000
INTO :NEW.CAPLAG FROM dual;

SELECT TRUNC(TO_NUMBER(SUBSTR((:NEW.PIPTIME - :NEW.CAPTIME), 1, INSTR(:NEW.PIPTIME - :NEW.CAPTIME, ' ')))) * 86400 
+ TO_NUMBER(SUBSTR((:NEW.PIPTIME - :NEW.CAPTIME), INSTR((:NEW.PIPTIME - :NEW.CAPTIME), ' ')+1, 2)) * 3600
+ TO_NUMBER(SUBSTR((:NEW.PIPTIME - :NEW.CAPTIME), INSTR((:NEW.PIPTIME - :NEW.CAPTIME), ' ')+4, 2)) * 60
+ TO_NUMBER(SUBSTR((:NEW.PIPTIME - :NEW.CAPTIME), INSTR((:NEW.PIPTIME - :NEW.CAPTIME), ' ')+7, 2))
+ TO_NUMBER(SUBSTR((:NEW.PIPTIME - :NEW.CAPTIME), INSTR((:NEW.PIPTIME - :NEW.CAPTIME), ' ')+10, 6)) / 1000000
INTO :NEW.PMPLAG FROM dual;

SELECT TRUNC(TO_NUMBER(SUBSTR((:NEW.DELTIME - :NEW.PIPTIME), 1, INSTR(:NEW.DELTIME - :NEW.PIPTIME, ' ')))) * 86400 
+ TO_NUMBER(SUBSTR((:NEW.DELTIME - :NEW.PIPTIME), INSTR((:NEW.DELTIME - :NEW.PIPTIME), ' ')+1, 2)) * 3600
+ TO_NUMBER(SUBSTR((:NEW.DELTIME - :NEW.PIPTIME), INSTR((:NEW.DELTIME - :NEW.PIPTIME), ' ')+4, 2)) * 60
+ TO_NUMBER(SUBSTR((:NEW.DELTIME - :NEW.PIPTIME), INSTR((:NEW.DELTIME - :NEW.PIPTIME), ' ')+7, 2))
+ TO_NUMBER(SUBSTR((:NEW.DELTIME - :NEW.PIPTIME), INSTR((:NEW.DELTIME - :NEW.PIPTIME), ' ')+10, 6)) / 1000000
INTO :NEW.DELLAG FROM dual;

SELECT TRUNC(TO_NUMBER(SUBSTR((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), 1, INSTR(:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT, ' ')))) * 86400 
+ TO_NUMBER(SUBSTR((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), INSTR((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), ' ')+1, 2)) * 3600
+ TO_NUMBER(SUBSTR((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), INSTR((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), ' ')+4, 2)) * 60
+ TO_NUMBER(SUBSTR((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), INSTR((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), ' ')+7, 2))
+ TO_NUMBER(SUBSTR((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), INSTR((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), ' ')+10, 6)) / 1000000
INTO :NEW.TOTALLAG FROM dual;

SELECT SYSTIMESTAMP INTO :NEW.TARGET_COMMIT FROM dual;
END;

ALTER TRIGGER GGS_HEARTBEAT_TRIG enable;

2.5 创建GGS_HEARTBEAT_HISTORY表触发器

CREATE SEQUENCE SEQ_GGS_HEARTBEAT_HIST INCREMENT BY 1 START WITH 1 ORDER;

CREATE OR REPLACE TRIGGER GGS_HEARTBEAT_TRIG_HIST
BEFORE INSERT ON GGS_HEARTBEAT_HISTORY
FOR EACH ROW
BEGIN
SELECT SEQ_GGS_HEARTBEAT_HIST.nextval into :NEW.ID FROM dual;
SELECT SYSTIMESTAMP INTO :NEW.TARGET_COMMIT FROM dual;
END;

ALTER TRIGGER GGS_HEARTBEAT_TRIG_HIST enable;

2.6 查看触发器是否创建成功

SELECT * FROM user_triggers;

三、源端OGG设置

3.1 打开源端表级日志

GGSCI (localhost.localdomain) 2> dblogin userid goldengate,password goldengate;
Successfully logged into database.

GGSCI (localhost.localdomain) 5> add trandata goldengate.*

GGSCI (localhost.localdomain) 3> info trandata goldengate.*

Logging of supplemental redo log data is enabled for table GOLDENGATE.HEARTBEAT.

Columns supplementally logged for table GOLDENGATE.HEARTBEAT: SRC_DB.

3.2 配置MGR

GGSCI (localhost.localdomain) 6> edit param mgr


    port 7809

    dynamicportlist 7810-7820,7830

    PURGEOLDEXTRACTS /opt/ogg/dirdat/*, usecheckpoints, minkeepdays 3

    LAGREPORTHOURS 1

    LAGINFOMINUTES 1

    LAGCRITICALMINUTES 1


GGSCI (localhost.localdomain) 7> start mgr


GGSCI (localhost.localdomain) 7> info all

3.3 配置EXTRACT

GGSCI (localhost.localdomain) 8> add extract ext_hb,tranlog,begin now;

GGSCI (localhost.localdomain) 8> add exttrail /opt/ogg/dirdat/hb, extract ext_hb;

GGSCI (localhost.localdomain) 8> edit param ext_hb

    extract ext_hb

    setenv(NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")

    SETENV(ORACLE_SID = XE)

    USERID goldengate, PASSWORD goldengate

    EXTTRAIL /opt/ogg/dirdat/hb

    dynamicresolution
    REPORTCOUNT EVERY 5 MINUTES, RATE
    FETCHOPTIONS, NOUSESNAPSHOT, NOUSELATESTVERSION, MISSINGROW REPORT
    STATOPTIONS REPORTFETCH

    TABLE goldengate.HEARTBEAT,
    TOKENS(
    CAPGROUP = @GETENV("GGENVIRONMENT","GROUPNAME"),
    CAPTIME = @DATE("YYYY-MM-DD HH:MI:SS.FFFFFF","JTS",@GETENV("JULIANTIMESTAMP")),
    EDDLDELTASTATS = @GETENV("DELTASTATS","DDL"),
    EDMLDELTASTATS = @GETENV("DELTASTATS","DML")
    );



GGSCI (localhost.localdomain) 8> start ext_hb

3.4 配置PUMP

GGSCI (localhost.localdomain) 9> add extract dump_hb, exttrailsource /opt/ogg/dirdat/hb;


GGSCI (localhost.localdomain) 9> add rmttrail /opt/ogg/dirdat/hb, extract dump_hb;

GGSCI (localhost.localdomain) 9> edit param dump_hb

    extract dump_hb

    setenv(NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
    userid goldengate,password goldengate
    rmthost 192.168.153.10, mgrport 7809, compress
    rmttrail /opt/ogg/dirdat/hb

    dynamicresolution

    table goldengate.HEARTBEAT,
    TOKENS(
    PMPGROUP = @GETENV("GGENVIRONMENT","GROUPNAME"),
    PMPTIME = @DATE("YYYY-MM-DD HH:MI:SS.FFFFFF","JTS",@GETENV("JULIANTIMESTAMP"))
    );


GGSCI (localhost.localdomain) 10> start dump_hb

四，目标端OGG配置

4.1 配置MGR

GGSCI (localhost.localdomain) 6> edit param mgr


    port 7809

    dynamicportlist 7810-7820,7830



GGSCI (localhost.localdomain) 7> start mgr


GGSCI (localhost.localdomain) 7> info all

4.2 配置REPLICAT

GGSCI (localhost.localdomain) 32> add replicat rep_hb, exttrail /opt/ogg/dirdat/hb，nodbcheckpoint


GSCI (localhost.localdomain) 34> edit param rep_hb


    replicat rep_hb
    setenv(NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
    setenv(ORACLE_SID = XE)
    userid goldengate, password goldengate

    reperror default,abend

    discardfile /opt/ogg/dirrpt/repa.dsc,append, megabytes 10
    assumetargetdefs

    dynamicresolution

    REPORTCOUNT EVERY 5 MINUTES, RATE

    MAP goldengate.HEARTBEAT, TARGET goldengate.GGS_HEARTBEAT,
    KEYCOLS(DELGROUP)
    INSERTMISSINGUPDATES,
    COLMAP(USEDEFAULTS,
    ID = 0,
    SOURCE_COMMIT = @GETENV("GGHEADER","COMMITTIMESTAMP"),
    EXTRACT_NAME = @TOKEN("CAPGROUP"),
    CAPTIME = @TOKEN("CAPTIME"),
    PMPGROUP = @TOKEN("PMPGROUP"),
    PMPTIME = @TOKEN("PMPTIME"),
    DELGROUP = @GETENV("GGENVIRONMENT","GROUPNAME"),
    DELTIME = @DATE("YYYY-MM-DD HH:MI:SS.FFFFFF","JTS",@GETENV("JULIANTIMESTAMP")),

    EDDLDELTASTATS = @TOKEN("EDDLDELTASTATS"),
    RDDLDELTASTATS = @GETENV("DELTASTATS","DDL"),
    EDMLDELTASTATS = @TOKEN("EDMLDELTASTATS"),
    RDMLDELTASTATS = @GETENV("DELTASTATS","DML")
    );

    MAP goldengate.HEARTBEAT, TARGET goldengate.GGS_HEARTBEAT_HISTORY,
    KEYCOLS(ID)
    INSERTMISSINGUPDATES,
    COLMAP(USEDEFAULTS,
    ID = 0,
    SOURCE_COMMIT = @GETENV("GGHEADER","COMMITTIMESTAMP"),
    EXTRACT_NAME = @TOKEN("CAPGROUP"),
    CAPTIME = @TOKEN("CAPTIME"),
    PMPGROUP = @TOKEN("PMPGROUP"),
    PMPTIME = @TOKEN("PMPTIME"),
    DELGROUP = @GETENV("GGENVIRONMENT","GROUPNAME"),
    DELTIME = @DATE("YYYY-MM-DD HH:MI:SS.FFFFFF","JTS",@GETENV("JULIANTIMESTAMP")),

    EDDLDELTASTATS = @TOKEN("EDDLDELTASTATS"),
    RDDLDELTASTATS = @GETENV("DELTASTATS","DDL"),
    EDMLDELTASTATS = @TOKEN("EDMLDELTASTATS"),
    RDMLDELTASTATS = @GETENV("DELTASTATS","DML")
    );


GGSCI (localhost.localdomain) 34> start rep_hb

GGSCI (localhost.localdomain) 34> info all

五，检验数据

1，源HEARTBEAT表

ID	SRC_DB	EXTRACT_NAME	SOURCE_COMMIT	TARGET_COMMIT	CAPTIME	CAPLAG	PMPTIME	PMPGROUP	PMPLAG	DELTIME	DELGROUP	DELFLAG	TOTALLAG	THREAD	UPDATE_TIMESTAMP	EDDLDELTASTATS	EDMLDELTASTATS	RDDLDELTASTATS	RDMLDELTASTATS

1	XE	11	2021-09-29 13:34:14.556454		2021-09-28 18:12:29.000000	1	2021-09-28 18:12:33.000000								2021-09-29 13:34:14.556454

2，目标HEARTBEAT表

ID	SRC_DB	EXTRACT_NAME	SOURCE_COMMIT	TARGET_COMMIT	CAPTIME	CAPLAG	PMPTIME	PMPGROUP	PMPLAG	DELTIME	DELGROUP	DELFLAG	TOTALLAG	THREAD	UPDATE_TIMESTAMP	EDDLDELTASTATS	EDMLDELTASTATS	RDDLDELTASTATS	RDMLDELTASTATS

241	XE	EXT_HB	2021-09-29 12:33:14.391273	2021-09-29 12:33:23.850704	2021-09-29 12:33:17.375003		2021-09-29 12:33:18.777528	DUMP_HB		2021-09-29 12:33:23.849767	REP_HB				2021-09-29 12:33:14.894928	0	1	0	1

3，目标GGS_HEARTBEAT_HISTORY表

ID	SRC_DB	EXTRACT_NAME	SOURCE_COMMIT	TARGET_COMMIT	CAPTIME	CAPLAG	PMPTIME	PMPGROUP	PMPLAG	DELTIME	DELGROUP	DELFLAG	TOTALLAG	THREAD	UPDATE_TIMESTAMP	EDDLDELTASTATS	EDMLDELTASTATS	RDDLDELTASTATS	RDMLDELTASTATS

153	XE	EXT_HB	2021-09-29 12:29:14.715437	2021-09-29 12:31:01.688170	2021-09-29 12:30:57.868776		2021-09-29 12:30:58.992115	DUMP_HB		2021-09-29 12:31:01.687550	REP_HB				2021-09-29 12:29:14.564376	0	1	0	1

154	XE	EXT_HB	2021-09-29 12:30:14.715437	2021-09-29 12:31:01.688775	2021-09-29 12:30:57.892636		2021-09-29 12:30:58.992125	DUMP_HB		2021-09-29 12:31:01.687550	REP_HB				2021-09-29 12:30:14.623236	0	1	0	1

155	XE	EXT_HB	2021-09-29 12:31:13.931523	2021-09-29 12:31:21.719145	2021-09-29 12:31:16.091333		2021-09-29 12:31:18.024943	DUMP_HB		2021-09-29 12:31:21.718289	REP_HB				2021-09-29 12:31:14.683932	0	1	0	1

156	XE	EXT_HB	2021-09-29 12:32:14.079169	2021-09-29 12:32:21.788977	2021-09-29 12:32:15.872898		2021-09-29 12:32:17.100318	DUMP_HB		2021-09-29 12:32:21.788139	REP_HB				2021-09-29 12:32:14.777180	0	1	0	1

157	XE	EXT_HB	2021-09-29 12:33:14.391273	2021-09-29 12:33:23.852268	2021-09-29 12:33:17.375003		2021-09-29 12:33:18.777528	DUMP_HB		2021-09-29 12:33:23.851895	REP_HB				2021-09-29 12:33:14.894928	0	1	0	1

沧hi

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Oracle GoldenGate心跳表检测延迟

环境：Centos 7 虚拟机（1核、1G内存）* 2Oracle 11.2.0.2.0 * 2Oracle GoldenGate11.2.1.0.3源端IP:192.168.153.9 目标端IP:192.168.153.10一，源端数据库设置1.1,创建源端Oracle 测试账户SQL> create user goldengate identified by 123 default table...
复制链接

扫一扫