goldengate單向複製的配置

quanshengaa發表於2016-04-01

本文介紹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部落格 ” ,連結:http://blog.itpub.net/15797451/viewspace-2073581/,如需轉載,請註明出處,否則將追究法律責任。

相關文章