【转】OGG心跳表（2.0版）配置过程
OGG心跳表（2.0版）配置过程1. 在源数据库中添加心跳表goldengate.GGS_HEARTBEAT---- SourceHeartbeat DDL -------- This tableis updated via OS scirpt every 10 seconds with sysdateCREATE 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_LAGTIMEFOR EACH ROW
BEGIN
selectseq_ggs_lagtime_id.nextvalinto :NEW.ID
from dual;
select sysdate
into:NEW.TARGET_COMMITfrom 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.TOTALLAGfrom 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 <<EOFset feedback off
UPDATEgoldengate.GGS_HEARTBEATSET SOURCETIME =SYSDATEWHERE ID = 1;
EOF
sleep 60
done
4. 在源服务器上的EXTRACT配置文件中添加心跳配置-- ChangeCapture parameter file to captureEXTRACT EORA1
USERID test,PASSWORD testDISCARDFILE./dirrpt/eora1.dsc, appendEXTTRAIL/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 testdiscardfile./dirrpt/epmp1.dsc, appendRMTHOST wh02_p,MGRPORT 7809RMTTRAIL/queuefile/ogg/rtTABLE test.*;
TABLEgoldengate.GGS_HEARTBEAT, TOKENS ( PMPGROUP = @GETENV("GGENVIRONMENT", "GROUPNAME") , PMPTIME = @DATENOW()
) ;
6. 在目标服务器上的REPLICAT配置文件中添加心跳配置-- ChangeDelivery parameter file to apply--
REPLICAT RORA1
USERID test,PASSWORD testEOFDELAYCSECS 10
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE./dirrpt/RORA1.DSC, PURGE
MAP test.*,TARGET test.*;
SOURCEDEFS./dirdef/heartbeat_defs.defMAPgoldengate.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_DEVFROM 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;
页:
[1]