ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > goldengate单向复制的配置

goldengate单向复制的配置

Linux操作系统 作者：quanshengaa 时间：2016-04-01 10:33:33 0 删除 编辑

本文介绍goldengate单向复制的配置，包括：

1．oracle环境下,复制lt_test schema下的两张表test1和test2. 其中，表test1没有创建主键，但列id逻辑上能够唯一标识记录. 表test2上建有主键;这里只复制DML操作,不包括DDL.

2．配置heartbeat表, 用于监控复制延迟;

3．注册Extract进程,用于利用stream进程来实现过期归档日志的清理.

说明: 本文测试环境为oracle 10.2.0.5, Goldengate安装目录为/oracle/goldengate/ggs

 

以下为具体步骤：

1.在source和target数据上创建Goldengate管理用户并授权

create user ggs_owner identified by *** default tablespace users temporary tablespace temp;

grant connect,resource,dba to ggs_owner;

grant select any dictionary, select any table to ggs_owner;

grant create table to ggs_owner;

grant ALTER ANY  TABLE TO ggs_owner;

grant flashback any table to ggs_owner;

grant execute on dbms_flashback to ggs_owner;

grant execute on utl_file to ggs_owner;

2.在source数据库上打开supplemental logging

a> enable minimal supplemental logging on source DB at the database level:

alter database add supplemental log data;

b> enable supplemental logging at table level:

cd /oracle/goldengate/ggs

GGSCI>dblogin userid ggs_owner, password ***

GGSCI>add trandata LT_TEST.TEST1

GGSCI>add trandata LT_TEST.TEST2

3.  在source数据库上ggs_owner用户下创建heartbeat表

CREATE TABLE GGS_OWNER.HEARTBEAT

(    

ID NUMBER ,

SRC_DB            VARCHAR2(30),

EXTRACT_NAME      varchar2(8),

SOURCE_COMMIT     TIMESTAMP,

TARGET_COMMIT     TIMESTAMP,

CAPTIME     TIMESTAMP,

CAPLAG            NUMBER,

PMPTIME     TIMESTAMP,

PMPGROUP    VARCHAR2(8 BYTE),

PMPLAG            NUMBER,

DELTIME     TIMESTAMP,

DELGROUP    VARCHAR2(8 BYTE),

DELLAG            NUMBER,

TOTALLAG    NUMBER,

thread            number,

update_timestamp timestamp,

EDMLDELTASTATS    number,

RDMLDELTASTATS    number,

CONSTRAINT HEARTBEAT_PK PRIMARY KEY (SRC_DB) ENABLE

);

INSERT INTO GGS_OWNER.HEARTBEAT (SRC_DB) select db_unique_name from V$database;

commit;

4.  在TARGET数据库上ggs_owner用户下创建heartbeat表及相关数据对象

CREATE SEQUENCE GGS_OWNER.SEQ_GGS_HEARTBEAT_ID INCREMENT BY 1 START WITH 1 ORDER ;
CREATE TABLE GGS_OWNER.GGS_HEARTBEAT
(    
ID NUMBER ,
SRC_DB            VARCHAR2(30),
EXTRACT_NAME      varchar2(8),
SOURCE_COMMIT     TIMESTAMP,
TARGET_COMMIT     TIMESTAMP,
CAPTIME     TIMESTAMP,
CAPLAG            NUMBER,
PMPTIME     TIMESTAMP,
PMPGROUP    VARCHAR2(8 BYTE),
PMPLAG            NUMBER,
DELTIME     TIMESTAMP,
DELGROUP    VARCHAR2(8 BYTE),
DELLAG            NUMBER,
TOTALLAG    NUMBER,
thread            number,
update_timestamp timestamp,
EDMLDELTASTATS    number,
RDMLDELTASTATS    number,
CONSTRAINT GGS_HEARTBEAT_PK PRIMARY KEY (DELGROUP) ENABLE
);
 
CREATE OR REPLACE TRIGGER GGS_OWNER.GGS_HEARTBEAT_TRIG
BEFORE INSERT OR UPDATE ON GGS_OWNER.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.PMPTIME - :NEW.CAPTIME),1, instr(:NEW.PMPTIME - :NEW.CAPTIME,' ')))) * 86400
+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+1,2)) * 3600
+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+4,2) ) * 60
+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+7,2))
+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+10,6)) / 1000000
into :NEW.PMPLAG
from dual;
select trunc(to_number(substr((:NEW.DELTIME - :NEW.PMPTIME),1, instr(:NEW.DELTIME - :NEW.PMPTIME,' ')))) * 86400
+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+1,2)) * 3600
+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+4,2) ) * 60
+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+7,2))
+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+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;
end ;
/
 
ALTER TRIGGER GGS_OWNER.GGS_HEARTBEAT_TRIG ENABLE;
 
CREATE SEQUENCE GGS_OWNER.SEQ_GGS_HEARTBEAT_HIST INCREMENT BY 1 START WITH 1 ORDER ;
CREATE TABLE GGS_OWNER.GGS_HEARTBEAT_HISTORY
(    
ID NUMBER ,
SRC_DB            VARCHAR2(30),
EXTRACT_NAME      varchar2(8),
SOURCE_COMMIT     TIMESTAMP,
TARGET_COMMIT     TIMESTAMP,
CAPTIME     TIMESTAMP,
CAPLAG            NUMBER,
PMPTIME     TIMESTAMP,
PMPGROUP    VARCHAR2(8 BYTE),
PMPLAG            NUMBER,
DELTIME     TIMESTAMP,
DELGROUP    VARCHAR2(8 BYTE),
DELLAG            NUMBER,
TOTALLAG    NUMBER,
thread            number,
update_timestamp timestamp,
EDMLDELTASTATS    number,
RDMLDELTASTATS    number
);
 
CREATE OR REPLACE TRIGGER GGS_OWNER.GGS_HEARTBEAT_TRIG_HIST
BEFORE INSERT OR UPDATE ON GGS_OWNER.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;
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.PMPTIME - :NEW.CAPTIME),1, instr(:NEW.PMPTIME - :NEW.CAPTIME,' ')))) * 86400
+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+1,2)) * 3600
+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+4,2) ) * 60
+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+7,2))
+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+10,6)) / 1000000
into :NEW.PMPLAG
from dual;
select trunc(to_number(substr((:NEW.DELTIME - :NEW.PMPTIME),1, instr(:NEW.DELTIME - :NEW.PMPTIME,' ')))) * 86400
+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+1,2)) * 3600
+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+4,2) ) * 60
+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+7,2))
+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+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;
end ;
/

ALTER TRIGGER GGS_OWNER.GGS_HEARTBEAT_TRIG_HIST ENABLE;

5.   在source数据库上ggs_owner用户下创建heartbeat job

grant select on v_$instance to ggs_owner;

grant select on v_$database to ggs_owner;

 

CREATE OR REPLACE PROCEDURE ggs_owner.gg_update_hb_tab IS

v_thread_num     NUMBER;

v_db_unique_name VARCHAR2 (128);

BEGIN

SELECT db_unique_name

INTO  v_db_unique_name

FROM v$database;

 

UPDATE ggs_owner.heartbeat

SET update_timestamp = SYSTIMESTAMP

,src_db = v_db_unique_name;

COMMIT;

END;

/

 

BEGIN

SYS.DBMS_SCHEDULER.CREATE_JOB (

job_name => 'ggs_owner.OGG_HB',

job_type => 'STORED_PROCEDURE',

job_action => 'ggs_owner.GG_UPDATE_HB_TAB',

number_of_arguments => 0,

start_date => NULL,

repeat_interval => 'FREQ=MINUTELY',

end_date => NULL,

job_class => '"SYS"."DEFAULT_JOB_CLASS"',

enabled => FALSE,

auto_drop => FALSE,

comments => 'GoldenGate');

 

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(

name => 'ggs_owner.OGG_HB',

attribute => 'restartable', value => TRUE);

 

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(

name => 'ggs_owner.OGG_HB',

attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);

 

SYS.DBMS_SCHEDULER.enable(

name => 'ggs_owner.OGG_HB');

END;

/

6.  在source数据库上为ggs_owner用户授予stream相关权限

exec dbms_streams_auth.grant_admin_privilege('GGS_OWNER')

grant insert on system.logmnr_restart_ckpt$ to GGS_OWNER;

grant update on sys.streams$_capture_process to GGS_OWNER;

grant become user to GGS_OWNER;

7. 在source端配置manager进程

配置manager进程参数

cd /oracle/goldengate/ggs

GGSCI>edit params mgr

PORT 7809

USERID ggs_owner, PASSWORD ggs_owner

PURGEOLDEXTRACTS /oracle/goldengate/ggs/dirdat/*,usecheckpoints, minkeepdays 3

AUTOSTART EXTRACT *

8.  在source端配置extract进程

cd /oracle/goldengate/ggs

GGSCI>edit params ext01

SETENV (NLS_LANG = AMERICAN_AMERICA.UTF8)

USERID ggs_owner, PASSWORD ***

TRANLOGOPTIONS, BUFSIZE 4096000

EXTTRAIL /oracle/goldengate/ggs/dirdat/e1

include dirprm/HB_Ext.inc

TABLE LT_TEST.TEST1, KEYCOLS(id);

TABLE LT_TEST.TEST2;

GGSCI>ADD EXTRACT E_DSE01, TRANLOG, BEGIN NOW

GGSCI>ADD EXTTRAIL /oraarch/CNR/ggs/dirdat/e1,EXTRACT E_DSE01,MEGABYTES 100

GGSCI>DBLOGIN USERID GGS_OWNER, PASSWORD GGS_OWNER

GGSCI>REGISTER EXTRACT E_DSE01 LOGRETENTION

Heartbeat配置文件HB_Ext.inc内容如下：

TABLE GGS_OWNER.HEARTBEAT,

TOKENS (

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

CAPTIME =  @DATE ("YYYY-MM-DD HH:MI:SS.FFFFFF","JTS",@GETENV ("JULIANTIMESTAMP")),

EDMLDELTASTATS = @GETENV ("DELTASTATS", "DML")

);

9.在source数据库服务器上配置data pump进程

cd /oracle/goldengate/ggs

GGSCI>edit params p_dse01

USERID GGS_OWNER,PASSWORD ***

RMTHOST <ip>, MGRPORT 7809

RMTTRAIL /oracle/goldengate/ggs/dirdat/r1

include dirprm/HB_Pmp.inc

TABLE LT_TEST.TEST1, KEYCOLS(id);

TABLE LT_TEST.TEST2;

GGSCI> ADD EXTRACT P_DSE01, EXTTRAILSOURCE /oracle/goldengate/ggs/dirdat/e1

GGSCI> ADD RMTTRAIL /oracle/goldengate/ggs/dirdat/r1, EXTRACT P_DSE01,MEGABYTES 100

Heartbeat配置文件HB_Pmp.inc内容如下：

table GGS_OWNER.HEARTBEAT,

TOKENS (

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

PMPTIME = @DATE ("YYYY-MM-DD HH:MI:SS.FFFFFF","JTS",@GETENV ("JULIANTIMESTAMP"))

);

10.在target数据库上创建checkpoint table

cd /oracle/goldengate/ggs

GGSCI>DBLOGIN USERID ggs_owner, PASSWORD ***

GGSCI>ADD CHECKPOINTTABLE GGS_OWNER.CHKPTAB

11.  在target server上编辑GLOBALS参数文件

vi /oracle/goldengate/ggs/GLOBALS

--add below entry

CHECKPOINTTABLE GGS_OWNER.CHKPTAB

12.  在target端配置manager进程

配置manager进程参数

cd /oracle/goldengate/ggs

GGSCI>edit params mgr

PORT 7809

USERID ggs_owner, PASSWORD ***

PURGEOLDEXTRACTS /oracle/goldengate/ggs/dirdat/*,usecheckpoints, minkeepdays 3

AUTOSTART REPLICAT *

13.  配置replicate进程

cd /oracle/goldengate/ggs

GGSCI>edit params r_dse01

SETENV (NLS_LANG = AMERICAN_AMERICA.UTF8)

USERID ggs_owner, PASSWORD ***

Assumetargetdefs

DISCARDFILE /oracle/goldengate/ggs/dirrpt/record01.dsc,APPEND,MEGABYTES 50

DiscardRollover at 00:00 ON SUNDAY

BATCHSQL

include ./dirprm/HB_Rep.inc

REPERROR (DEFAULT, DISCARD)

MAP LT_TEST.TEST1,TARGET LT_TEST.TEST1, KEYCOLS(id);

MAP LT_TEST.TEST2,TARGET LT_TEST.TEST2;

GGSCI> ADD REPLICAT R_DSE01,EXTTRAIL /oracle/goldengate/ggs/dirdat/r1

Heartbeat配置文件HB_Rep.inc内容如下：

MAP GGS_OWNER.HEARTBEAT, TARGET GGS_OWNER.GGS_HEARTBEAT_HISTORY,

KEYCOLS (ID),

INSERTALLRECORDS,

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")),

EDMLDELTASTATS = @TOKEN ("EDMLDELTASTATS"),

RDMLDELTASTATS = @GETENV ("DELTASTATS", "DML")

);

 

MAP GGS_OWNER.HEARTBEAT, TARGET GGS_OWNER.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")),

EDMLDELTASTATS = @TOKEN ("EDMLDELTASTATS"),

RDMLDELTASTATS = @GETENV ("DELTASTATS", "DML")

);

14.  在target端启动manager进程

cd /oracle/goldengate/ggs

GGSCI>start mgr

15.  在source端启动manager进程

cd /oracle/goldengate/ggs

GGSCI>start mgr

 

关于以上配置的说明：

1.这里使用了heartbeat表来监控复制延迟及消息量,关于heartbeat的更详细信息,参考metalink文档1299679.1;

2.文中使用了register extract操作,该操作会创建一个underlying stream进程,之后goldengate可利用stream的checkpoint机制来更新extract进程所需要的最小SCN值,记录在dba_capture视图的REQUIRED_CHECKPOINT_SCN列.该SCN值之前的oracle日志就不再被goldengate所需要，可以安全地删除；

3.文中所使用参数的具体含义，可参考goldengate文档，我也会在另一篇文章中作详细解释。

 

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/15797451/viewspace-2073581/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： oracle goldengate 10g--->11g配置
下一篇： AIX vmstat分析
请登录后发表评论 登录
全部评论
quanshengaa

注册时间：2013-11-08

  • 博文量
    51
  • 访问量
    125611

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593