【转】OGG心跳表（2.0版）配置过程

ALLSTARS_ORACLE

OGG心跳表（2.0版）配置过程

1.   在源数据库中添加心跳表goldengate.GGS_HEARTBEAT

---- SourceHeartbeat DDL ----

---- This tableis updated via OS scirpt every 10 seconds with sysdate

CREATE TABLEgoldengate.GGS_HEARTBEAT

       ("ID" NUMBER,
       "SOURCETIME"DATE,
       CONSTRAINT"GGS_HEARTBEAT_PK" PRIMARY KEY ("ID") ENABLE);

---- Insert theinitial row into the heartbeat table ----

INSERT INTOgoldengate.GGS_HEARTBEAT VALUES (1, sysdate);

2.   在目标数据库中添加心跳表goldengate.GGS_LAGTIME，并创建触发器GGS_LAGTIME_TRIG

---- TargetLagtime DDL ----

---- This tablecollects the heartbeats that are captured on the source and ----

---- adds in thecommittime and group name (replicat name). ----

---- The triggeradds ID, targettime and calculates lagtime. ----

CREATE SEQUENCEgoldengate.SEQ_GGS_LAGTIME_ID INCREMENT BY 1 START WITH 1 ORDER ;

CREATE TABLEgoldengate.GGS_LAGTIME

( "ID"NUMBER NOT NULL ENABLE,

"SOURCE_COMMIT"DATE,

"TARGET_COMMIT"DATE,

"CAPTIME"DATE,

"CAPGROUP"VARCHAR2(8 BYTE),

"CAPLAG"NUMBER,

"PMPTIME"DATE,

"PMPGROUP"VARCHAR2(8 BYTE),

"PMPLAG"NUMBER,

"DELTIME"DATE,

"DELGROUP"VARCHAR2(8 BYTE),

"DELLAG"NUMBER,

"TOTALLAG"NUMBER,

CONSTRAINT"GGS_LAGTIME_PK" PRIMARY KEY ("ID") ENABLE

) ;

CREATE ORREPLACE TRIGGER "GGS_LAGTIME_TRIG"

BEFORE INSERT ORUPDATE ON goldengate.GGS_LAGTIME

FOR EACH ROW
BEGIN

selectseq_ggs_lagtime_id.nextval

into :NEW.ID
from dual;
select sysdate

into:NEW.TARGET_COMMIT

from dual;

selecttrunc((:NEW.CAPTIME - :NEW.SOURCE_COMMIT) * 86400 + 0.5)

into :NEW.CAPLAG
from dual;

selecttrunc((:NEW.PMPTIME - :NEW.CAPTIME) * 86400)

into :NEW.PMPLAG
from dual;

selecttrunc((:NEW.DELTIME - :NEW.PMPTIME) * 86400)

into :NEW.DELLAG
from dual;

selecttrunc((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT) * 86400)

into:NEW.TOTALLAG

from dual;
END;
/

ALTER TRIGGER"GGS_LAGTIME_TRIG" ENABLE;

3.   在源服务器上创建定时更新心跳表goldengate.GGS_HEARTBEAT的脚本/opt/ogg/11g/update_heartbeat.sh

#! /bin/ksh
cd /opt/ogg/11g
while [ 1 ]
do

sqlplus -sgoldengate/oracle <<EOF

set feedback off

UPDATEgoldengate.GGS_HEARTBEAT

SET SOURCETIME =SYSDATE

WHERE ID = 1;
EOF
sleep 60
done

4.   在源服务器上的EXTRACT配置文件中添加心跳配置

-- ChangeCapture parameter file to capture

EXTRACT EORA1

USERID test,PASSWORD test

DISCARDFILE./dirrpt/eora1.dsc, append

EXTTRAIL/queuefile/ogg/lt

TABLE test.*;

IGNOREINSERTS
IGNOREDELETES

TABLEgoldengate.ggs_heartbeat, TOKENS (

        CAPGROUP = @GETENV("GGENVIRONMENT", "GROUPNAME") ,

        CAPTIME = @DATENOW()
        ) ;
GETINSERTS
GETDELETES

5.   在源服务器上的DATAPUMP配置文件中添加心跳配置

-- Data Pumpparameter file to read the local

--
EXTRACT EPMP1
--PASSTHRU

userid test,password test

discardfile./dirrpt/epmp1.dsc, append

RMTHOST wh02_p,MGRPORT 7809

RMTTRAIL/queuefile/ogg/rt

TABLE test.*;

TABLEgoldengate.GGS_HEARTBEAT, TOKENS (

        PMPGROUP = @GETENV("GGENVIRONMENT", "GROUPNAME") ,

        PMPTIME = @DATENOW()
        ) ;

6.   在目标服务器上的REPLICAT配置文件中添加心跳配置

-- ChangeDelivery parameter file to apply

--
REPLICAT RORA1

USERID test,PASSWORD test

EOFDELAYCSECS 10
HANDLECOLLISIONS
ASSUMETARGETDEFS

DISCARDFILE./dirrpt/RORA1.DSC, PURGE

MAP test.*,TARGET test.*;

SOURCEDEFS./dirdef/heartbeat_defs.def

MAPgoldengate.GGS_HEARTBEAT, TARGET goldengate.GGS_LAGTIME,

        KEYCOLS (ID) ,
        INSERTALLRECORDS ,
        COLMAP (USEDEFAULTS,
                ID = 0 ,

                SOURCE_COMMIT   = @GETENV ("GGHEADER","COMMITTIMESTAMP"),

                CAPGROUP        = @TOKEN ("CAPGROUP"),
                CAPTIME         = @TOKEN ("CAPTIME"),
                PMPGROUP        = @TOKEN ("PMPGROUP"),
                PMPTIME         = @TOKEN ("PMPTIME"),

                DELGROUP        = @GETENV ("GGENVIRONMENT" ,"GROUPNAME"),

                DELTIME         = @DATENOW()
        ) ;

OGG心跳表使用说明1.    为了保证心跳数据的精确，每次启动测试前，建议在目标数据库中执行如下命令清空心跳表goldengate.ggs_lagtime记录:
SQL> truncatetable goldengate.ggs_lagtime;

2.    每次在源端启动TT数据操作的同时，启动OGG目录下的脚本update_heartbeat.sh,该脚本每隔10秒（可通过修改”sleep10” 修改心跳间隔），使用当前系统时间更新源心跳表goldengate.ggs_heartbeat；

3.    当TT数据操作结束的同时，停止脚本update_heartbeat.sh；

4.    此时目标端的心跳历史表goldengate.ggs_lagtime中记录了本次测试期间所有的心跳信息，其中TOTALLAG字段即是源端到目标端的延时。在目标数据库SQL命令行下，执行如下SQL命令，生成汇总统计信息，包括心跳记录数、平均延时、最大延时、标准偏差等：
SELECT DELGROUP,

TO_CHAR(TARGET_COMMIT, 'MM-DD-YY')COMMITDATE,

COUNT(*),

TO_CHAR(AVG(TOTALLAG), '999999.99')AVGLAGSECS,

TO_CHAR(MAX(TOTALLAG), '999999.99')MAXLAGSECS,

TO_CHAR(STDDEV(TOTALLAG), '999999.99')STD_DEV

FROM goldengate.GGS_LAGTIME
WHERE

TO_CHAR(TARGET_COMMIT, 'MM-DD-YYHH24:MI:SS')

> '01-01-11 01:00:00'
GROUP BY
DELGROUP,
TO_CHAR(TARGET_COMMIT, 'MM-DD-YY')
ORDER BY
DELGROUP,
TO_CHAR(TARGET_COMMIT, 'MM-DD-YY') ASC;