mysql教程

GoldenGate配置（二）之双向复制配置

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:57 PM

複製配置

GoldenGate配置（二）之双向复制配置环境： Item Source System Target System Platform Red Hat Enterprise Linux Server release 5.4 Red Hat Enterprise Linux Server release 5.4 Hostname gc1 gc2 Database Oracle 10.2.0.1 Oracle 11.2.0.1 Character

GoldenGate配置（二）之双向复制配置

环境：

Item	Source System	Target System
Platform	Red Hat Enterprise Linux Server release 5.4	Red Hat Enterprise Linux Server release 5.4
Hostname	gc1	gc2
Database	Oracle 10.2.0.1	Oracle 11.2.0.1
Character Set	ZHS16GBK	ZHS16GBK
ORACLE_SID	PROD	EMREP
Listener Name/Port	LISTENER/1521	LISTENER/1521
Goldengate User	ogg	ogg

双向复制配置

说明：此篇续接第一篇“GoldenGate配置（一）之单向复制配置”之后继续进行配置关于上一篇，GoldenGate配置（一）之单向复制配置：点击打开链接

双向复制配置操作：

gc2：授权

SQL>grant CONNECT, RESOURCE to ogg;

SQL>grant CREATE SESSION, ALTER SESSION to ogg;

SQL>grant SELECT ANY DICTIONARY, SELECT ANY TABLE to ogg;

SQL>grant ALTER ANY TABLE to ogg;

SQL>grant FLASHBACK ANY TABLE to ogg;

SQL>grant EXECUTE on DBMS_FLASHBACK to ogg;

gc1：授权

SQL>grant CONNECT, RESOURCE to ogg;

SQL>grant CREATE SESSION, ALTER SESSION to ogg;

SQL>grant SELECT ANY DICTIONARY, SELECT ANY TABLE to ogg;

SQL>grant CREATE TABLE to ogg;

SQL>grant INSERT, UPDATE, DELETE on scott.tcustmer to ogg;--把需要同步表的DML操作授权给ogg

SQL>grant INSERT, UPDATE, DELETE on scott.tcustord to ogg;--把需要同步表的DML操作授权给ogg

gc2：开启补充日志

SQL>alter database add supplemental log data;

SQL>alter system switch logfile;

SQL>alter database force logging;

gc2：测试表添加到补充日志

GGSCI(gc2) 1> DBLOGIN USERID ogg, PASSWORD Ogg

Successfully logged into database.

GGSCI(gc2) 2> ADD TRANDATA scott.TCUSTMER

Logging of supplemental redo data enabled fortable SCOTT.TCUSTMER.

GGSCI(gc2) 3> ADD TRANDATA scott.TCUSTORD

Logging of supplemental redo data enabled fortable SCOTT.TCUSTORD.

gc2：配置Extract进程

GGSCI(gc2) 4> EDIT PARAMS EORA_1

-- Change Capture parameter file to capture

-- TCUSTMER and TCUSTORD changes

EXTRACT EORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD Ogg

TRANLOGOPTIONS EXCLUDEUSER ogg

EXTTRAIL ./dirdat/aa

TABLE scott.TCUSTMER;

TABLE scott.TCUSTORD;

"dirprm/eora_1.prm" [New] 9L, 257Cwritten

GGSCI(gc2) 5> ADD EXTRACT EORA_1, TRANLOG, BEGIN NOW

EXTRACT added.

GGSCI(gc2) 6> ADD EXTTRAIL ./dirdat/aa, EXTRACT EORA_1, MEGABYTES 5

EXTTRAIL added.

GGSCI(gc2) 7> START EXTRACT EORA_1

Sending START request to MANAGER ...

EXTRACT EORA_1 starting

GGSCI(gc2) 8> INFO EXTRACT EORA_1

EXTRACT EORA_1 Last Started 2014-06-1811:28 Status RUNNING

Checkpoint Lag 00:00:19 (updated 00:00:08 ago)

Log Read Checkpoint Oracle Redo Logs

2014-06-18 11:27:42 Seqno 6, RBA 35344

gc2：配置Pump进程

GGSCI(gc2) 9> EDIT PARAMS PORA_1

添加以下内容：

-- Data Pump parameter file to read thelocal

-- trail of TCUSTMER and TCUSTORDchanges

EXTRACT PORA_1

SETENV(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

PASSTHRU

RMTHOST gc1, MGRPORT 7809

RMTTRAIL ./dirdat/pa

TABLE scott.TCUSTMER;

TABLE scott.TCUSTORD;

"dirprm/pora_1.prm" [New] 10L, 250Cwritten

GGSCI(gc2) 10> ADD EXTRACT PORA_1, EXTTRAILSOURCE ./dirdat/aa

EXTRACT added.

GGSCI(gc2) 11> ADD RMTTRAIL ./dirdat/pa, EXTRACT PORA_1, MEGABYTES 5

RMTTRAIL added.

GGSCI(gc2) 12> START EXTRACT PORA_1

Sending START request to MANAGER ...

EXTRACT PORA_1 starting

gc1：配置Replicat进程

GGSCI(gc1) 1> EDIT PARAMS ./GLOBALS

CHECKPOINTTABLE ogg.ggschkpt

"./GLOBALS" [New] 1L, 29C written

GGSCI(gc1) 2> quit

[oracle@gc1ogg]$ ll GLOBALS --验证

-rw-rw-rw- 1 oracle oinstall 29 Jun 18 11:33GLOBALS

GGSCI(gc1) 1> DBLOGIN USERID ogg, PASSWORD Ogg

Successfully logged into database.

GGSCI(gc1) 2> ADD CHECKPOINTTABLE

No checkpoint table specified, using GLOBALSspecification (ogg.ggschkpt)...

Successfully created checkpoint tableOGG.GGSCHKPT.

gc1：配置Replicate进程

GGSCI(gc1) 3> EDIT PARAM RORA_1

-- Change Delivery parameter file to apply

-- TCUSTMER and TCUSTORD Changes

REPLICAT RORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD Ogg

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE ./dirrpt/RORA_aa.DSC, PURGE

MAP scott.tcustmer, TARGET scott.tcustmer;

MAP scott.tcustord, TARGET scott.tcustord;

"dirprm/rora_1.prm" [New] 12L, 327Cwritten

GGSCI(gc1) 4> ADD REPLICAT RORA_1, EXTTRAIL ./dirdat/pa

REPLICAT added.

GGSCI(gc1) 5> START REPLICAT RORA_1

Sending START request to MANAGER ...

REPLICAT RORA_1 starting

gc1：查看进程状态

GGSCI(gc1) 6> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EORA_1 00:00:00 00:00:08

EXTRACT RUNNING PORA_1 00:00:00 00:00:01

REPLICAT RUNNING RORA_1 00:00:00 00:00:06

gc2：查看进程状态

GGSCI(gc2) 13> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EORA_1 00:00:00 00:00:09

EXTRACT RUNNING PORA_1 00:00:00 00:00:06

REPLICAT RUNNING RORA_1 00:00:00 00:00:01

验证insert操作双向同步

gc1： gc1→gc2，DML操作：insert操作

SQL>insert into tcustmer VALUES('HYL','HUANG DBA.','HARBIN','CN');

1 row created.

SQL>commit;

Commit complete.

gc2：验证insert操作同步

SQL>select * from tcustmer;

CUST NAME CITY ST

---- -------------------------------------------------- --

HYL HUANGDBA. HARBIN CN

WILL BG SOFTWARE CO. SEATTLE WA

JANE ROCKY FLYER INC. DENVER CO

gc2：gc2→gc1，DML操作：insert操作

SQL>insert into tcustmer VALUES('WT','WANGDBA.','QINGDAO','CN');

1 row created.

SQL>commit;

Commit complete.

gc1：gc1→gc2，DML操作：update操作

SQL>select * from tcustmer;

CUST NAME CITY ST

---- -------------------------------------------------- --

HYL HUANGDBA. HARBIN CN

WILL BG SOFTWARE CO. SEATTLE WA

JANE ROCKY FLYER INC. DENVER CO

WT WANGDBA. QINGDAO CN

gc1：update操作

SQL>update tcustmer set city = 'BEIJING', state = 'CN' wherecust_code='HYL';

1 row updated.

SQL>commit;

Commit complete.

gc2：验证update操作同步

SQL>select * from tcustmer;

CUST NAME CITY ST

---- ------------------------------ ----------------------

HYL HUANGDBA. BEIJING CN

WILL BG SOFTWARE CO. SEATTLE WA

JANE ROCKY FLYER INC. DENVER CO

WT WANGDBA. QINGDAO CN

gc2：gc2→gc1，DML操作：update操作

SQL>update tcustmer set city = 'BEIJING', state = 'CN' wherecust_code='WT';

1 row updated.

SQL>commit;

Commit complete.

gc1：验证update操作同步

SQL>select * from tcustmer;

CUST NAME CITY ST

---- -------------------------------------------------- --

HYL HUANGDBA. BEIJING CN

WILL BG SOFTWARE CO. SEATTLE WA

JANE ROCKY FLYER INC. DENVER CO

WT WANGDBA. BEIJING CN

gc1：gc1→gc2，DML操作：delete操作

SQL>delete from tcustmer where CUST_CODE='WT';

1 row deleted.

SQL>commit;

Commit complete.

gc2：验证delete操作同步

SQL>select * from tcustmer;

CUST NAME CITY ST

---- -------------------------------------------------- --

HYL HUANGDBA. BEIJING CN

WILL BG SOFTWARE CO. SEATTLE WA

JANE ROCKY FLYER INC. DENVER CO

gc2：gc2→gc1，DML操作：delete操作

SQL>delete from tcustmer where CUST_CODE='HYL';

1 row deleted.

SQL>commit;

Commit complete.

gc1：验证delete操作同步

SQL>select * from TCUSTMER;

CUST NAME CITY ST

---- -------------------------------------------------- --

WILL BG SOFTWARE CO. SEATTLE WA

JANE ROCKY FLYER INC. DENVER CO

--至此，GoldenGate双向同步复制完成

声明：
原创作品，出自 “深蓝的blog” 博客，允许转载，转载时请务必注明出处（http://blog.csdn.net/huangyanlong）。

关于涉及版权事宜，作者有权追究法律责任。

陳述

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

mysql：blob和其他無-SQL存儲，有什麼區別？May 13, 2025 am 12:14 AM

mysql'sblobissuitableForStoringBinaryDataWithInareLationalDatabase，而ilenosqloptionslikemongodb，redis和calablesolutionsolutionsolutionsoluntionsoluntionsolundortionsolunsonstructureddata.blobobobissimplobisslowdeperformberbutslowderformandperformancewithlararengedata;

mySQL添加用戶：語法，選項和安全性最佳實踐May 13, 2025 am 12:12 AM

toaddauserinmysql，使用：createUser'username'@'host'Indessify'password'; there'showtodoitsecurely：1）choosethehostcarecarefullytocon trolaccess.2）setResourcelimitswithoptionslikemax_queries_per_hour.3）usestrong，iniquepasswords.4）Enforcessl/tlsconnectionswith

MySQL：如何避免字符串數據類型常見錯誤？May 13, 2025 am 12:09 AM

toAvoidCommonMistakeswithStringDatatatPesInMysQl，CloseStringTypenuances，chosethirtightType，andManageEngencodingAndCollationsEttingSefectery.1）usecharforfixed lengengtrings，varchar forvariable-varchar forbariaible length，andtext/blobforlargerdataa.2 seterters seterters seterters

mySQL：字符串數據類型和枚舉？May 13, 2025 am 12:05 AM

mysqloffersechar，varchar，text，and denumforstringdata.usecharforfixed Lengttrings，varcharerforvariable長度，文本forlarger文本，andenumforenforcingDataAntegrityWithaEtofValues。

mysql blob：如何優化斑點請求May 13, 2025 am 12:03 AM

優化MySQLBLOB請求可以通過以下策略：1.減少BLOB查詢頻率，使用獨立請求或延遲加載；2.選擇合適的BLOB類型（如TINYBLOB）；3.將BLOB數據分離到單獨表中；4.在應用層壓縮BLOB數據；5.對BLOB元數據建立索引。這些方法結合實際應用中的監控、緩存和數據分片，可以有效提升性能。

將用戶添加到MySQL：完整的教程May 12, 2025 am 12:14 AM

掌握添加MySQL用戶的方法對於數據庫管理員和開發者至關重要，因為它確保數據庫的安全性和訪問控制。 1)使用CREATEUSER命令創建新用戶，2)通過GRANT命令分配權限，3)使用FLUSHPRIVILEGES確保權限生效，4)定期審計和清理用戶賬戶以維護性能和安全。

掌握mySQL字符串數據類型：varchar vs.文本與charMay 12, 2025 am 12:12 AM

chosecharforfixed-lengthdata，varcharforvariable-lengthdata，andtextforlargetextfield.1）chariseffity forconsistent-lengthdatalikecodes.2）varcharsuitsvariable-lengthdatalikenames，ballancingflexibilitibility andperformance.3）

MySQL：字符串數據類型和索引：最佳實踐May 12, 2025 am 12:11 AM

在MySQL中處理字符串數據類型和索引的最佳實踐包括：1)選擇合適的字符串類型，如CHAR用於固定長度，VARCHAR用於可變長度，TEXT用於大文本；2)謹慎索引，避免過度索引，針對常用查詢創建索引；3)使用前綴索引和全文索引優化長字符串搜索；4)定期監控和優化索引，保持索引小巧高效。通過這些方法，可以在讀取和寫入性能之間取得平衡，提升數據庫效率。

See all articles