mysql add trandata_GoldenGate配置（一）之单向复制配置

最新推荐文章于 2022-07-28 15:59:47 发布

PeterPan(fake)

最新推荐文章于 2022-07-28 15:59:47 发布

阅读量158

点赞数

文章标签： mysql add trandata

本文链接：https://blog.csdn.net/weixin_27037521/article/details/113423178

版权

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

ORACLE_SID

PROD

EMREP

Listener Name/Port

LISTENER/1521

Goldengate User

ogg

单向复制配置

--环境变量里添加如下内容

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/network/lib

gc1：解压GoldenGate安装包

[oracle@gc1 ~]$ mkdir -p/u01/app/ogg

[oracle@gc1 ~]$ cd /u01/app/ogg

[oracle@gc1 ogg]$ ls

V18156-01-linux.zip

[oracle@gc1 ogg]$ unzipV18156-01-linux.zip

[oracle@gc1 ogg]$ tar -xvfggs_redhatAS40_x86_ora10g_32bit_v10.4.0.19_002.tar

gc2：解压GoldenGate安装包

[oracle@gc2 ~]$ mkdir -p/u01/app/ogg

[oracle@gc2 ~]$ cd /u01/app/ogg

[oracle@gc2 ogg]$ ls

V18156-01-linux.zip

[oracle@gc2 ogg]$ unzipV18156-01-linux.zip

[oracle@gc2 ogg]$ tar -xvfggs_redhatAS40_x86_ora10g_32bit_v10.4.0.19_002.tar

gc1：配置环境变量、建立相关子目录

[oracle@gc1 ~]$ vi .bash_profile

添加exportLD_LIBRARY_PATH=$ORACLE_HOME/lib

[oracle@gc1~]$ source .bash_profile

[oracle@gc1ogg]$./ggsci

OracleGoldenGate Command Interpreter for Oracle

Version10.4.0.19 Build 002

Linux, x86,32bit (optimized), Oracle 10 on Sep 17 2009 23:49:42

GGSCI (gc1)1> createsubdirs

Creatingsubdirectories under current directory /u01/app/ogg

Parameterfiles /u01/app/ogg/dirprm: created

Reportfiles /u01/app/ogg/dirrpt: created

Checkpointfiles /u01/app/ogg/dirchk:created

Process statusfiles /u01/app/ogg/dirpcs:created

SQL scriptfiles /u01/app/ogg/dirsql:created

Databasedefinitions files /u01/app/ogg/dirdef: created

Extract datafiles /u01/app/ogg/dirdat:created

Temporaryfiles /u01/app/ogg/dirtmp:created

Veridatafiles /u01/app/ogg/dirver: created

Veridata Lockfiles /u01/app/ogg/dirver/lock: created

VeridataOut-Of-Sync files /u01/app/ogg/dirver/oos: created

VeridataOut-Of-Sync XML files /u01/app/ogg/dirver/oosxml: created

VeridataParameter files /u01/app/ogg/dirver/params: created

Veridata Reportfiles /u01/app/ogg/dirver/report: created

Veridata Statusfiles /u01/app/ogg/dirver/status: created

Veridata Tracefiles /u01/app/ogg/dirver/trace: created

Stdoutfiles /u01/app/ogg/dirout:created

gc2：配置环境变量、建立相关子目录

[oracle@gc2 ~]$ vi .bash_profile

添加exportLD_LIBRARY_PATH=$ORACLE_HOME/lib

[oracle@gc2~]$ source .bash_profile

[oracle@gc2db_1]$ cd $ORACLE_HOME/lib

[oracle@gc2lib]$ ln -s libnnz11.so libnnz10.so

[oracle@gc2ogg]$./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 10.4.0.19 Build 002

Linux, x86, 32bit (optimized), Oracle 10 on Sep17 2009 23:49:42

GGSCI (gc2) 1> createsubdirs

Creating subdirectories under current directory/u01/app/ogg

Parameter files /u01/app/ogg/dirprm: created

Report files /u01/app/ogg/dirrpt: created

Checkpoint files /u01/app/ogg/dirchk: created

Process status files /u01/app/ogg/dirpcs: created

SQL script files /u01/app/ogg/dirsql: created

Database definitions files /u01/app/ogg/dirdef: created

Extract data files /u01/app/ogg/dirdat: created

Temporary files /u01/app/ogg/dirtmp: created

Veridata files /u01/app/ogg/dirver: created

Veridata Lock files /u01/app/ogg/dirver/lock: created

Veridata Out-Of-Sync files /u01/app/ogg/dirver/oos: created

Veridata Out-Of-Sync XML files/u01/app/ogg/dirver/oosxml: created

Veridata Parameter files /u01/app/ogg/dirver/params: created

Veridata Report files /u01/app/ogg/dirver/report: created

Veridata Status files /u01/app/ogg/dirver/status: created

Veridata Trace files /u01/app/ogg/dirver/trace: created

Stdout files /u01/app/ogg/dirout: created

gc1：建立GoldenGate用户、授权

[oracle@gc1~]$ mkdir -p /u01/app/oracle/oradata/soraeuc/

SQL>create tablespace tbs_gguser datafile'/u01/app/oracle/oradata/soraeuc/gguser.dbf' size 50M autoextend on; --创建表空间

SQL>create user ogg identified by Ogg default tablespacetbs_gguser temporary tablespace TEMPTS quota unlimited on tbs_gguser;

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;

SQL>@/u01/app/ogg/demo_ora_create --创建模拟同步的表

Table dropped.

Table created.

Table dropped.

Table created.

SQL>@/u01/app/ogg/demo_ora_insert--向模拟同步的表中插入数据

1 row created.

Commit complete.

SQL>select * from tcustmer;

CUST NAME CITY ST

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

WILL BG SOFTWARE CO. SEATTLE WA

JANE ROCKY FLYER INC. DENVER CO

SQL>select * from tcustord;

CUST ORDER_DATE PRODUCT_ ORDER_ID PRODUCT_PRICEPRODUCT_AMOUNT TRANSACTION_ID

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

WILL 30-SEP-94 CAR 144 17520 3 100

JANE 11-NOV-95 PLANE 256 133300 1 100

gc2: 建立GoldenGate用户、授权

[oracle@gc2~]$ mkdir -p /u01/app/oracle/oradata/soraeuc/

SQL>create tablespace tbs_gguser datafile'/u01/app/oracle/oradata/soraeuc/gguser.dbf' size 50M autoextend on; --创建与源库相同的表空间

SQL>create user ogg identified by Ogg default tablespacetbs_gguser temporary tablespace TEMPTS quota unlimited on tbs_gguser;

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>conn scott/tiger

SQL>@/u01/app/ogg/demo_ora_create --创建表，但不插入数据

Table dropped.

Table created.

Table dropped.

Table created.

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

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

gc1：开启补充日志

SQL>select SUPPLEMENTAL_LOG_DATA_MIN from v$database; --检查源端是否开启补充日志

SUPPLEME

--------

SQL>alter database add supplemental log data; --开启补充日志

SQL>alter system switch logfile;--切归档

gc1：开启归档

SQL>conn /as sysdba

SQL>alter system set log_archive_dest=' /u01/app/oracle/oradata/soraeuc/arch';

SQL>shutdown immediate

SQL>startup mount

SQL>alter database archivelog;

SQL>alter database open;

SQL>alter system archive log current; --检查归档日志信息

SQL>archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/oradata/soraeuc/arch

Oldest online log sequence 14

Next log sequence to archive 16

Current log sequence 16

gc1：开启补充日志

SQL>SELECT force_logging FROM v$database;

FOR

---

SQL>alter database force logging; --开启强制日志模式

SQL>SELECT force_logging FROM v$database;

FOR

---

YES

gc1：开启测试表补充日志

[oracle@gc1ogg]$ ./ggsci

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

Successfully logged into database.

GGSCI(gc1) 2> ADD TRANDATA scott.TCUSTMER

Logging of supplemental redo data enabled fortable SCOTT.TCUSTMER.

GGSCI(gc1) 3> ADD TRANDATA scott.TCUSTORD

Logging of supplemental redo data enabled fortable SCOTT.TCUSTORD.

GGSCI(gc1) 4> INFO TRANDATA scott.TCUST*

Logging of supplemental redo log data is enabledfor table SCOTT.TCUSTMER

Logging of supplemental redo log data is enabledfor table SCOTT.TCUSTORD

gc1：配置MGR

[oracle@gc1ogg]$ ./ggsci

GGSCI(gc1) 1> EDIT PARAMS MGR

添加如下：

PORT 7809

PURGEOLDEXTRACTS /u01/app/ogg/dirdat,USECHECKPOINTS --建立dirdat文件夹用于追踪

GGSCI(gc1) 2> START MGR

Manager started.

GGSCI(gc1) 3> INFO MGR --验证MGR已开启

Manager is running (IP port gc1.7809).

gc2：配置MGR

[oracle@gc2ogg]$./ggsci

GGSCI(gc2) 1> EDIT PARAMS MGR

添加如下

PORT 7809

PURGEOLDEXTRACTS /u01/app/ogg/dirdat,USECHECKPOINTS

GGSCI(gc2) 2> START MGR

Manager started.

GGSCI(gc2) 3> INFO MGR

Manager is running (IP port gc2.7809).

gc1:配置Extract进程

[oracle@gc1ogg]$ ./ggsci

GGSCI(gc1) 1> ADD EXTRACT EINI_1, SOURCEISTABLE

EXTRACT added.

GGSCI(gc1) 2> INFO EXTRACT *, TASKS

EXTRACT EINI_1 Initialized 2014-06-18 09:54 Status STOPPED

Checkpoint Lag Not Available

Log Read Checkpoint Not Available

First Record Record 0

Task SOURCEISTABLE

GGSCI(gc1) 3> EDIT PARAMS EINI_1

-- GoldenGate Initial Data Capture

-- for TCUSTMER and TCUSTORD

EXTRACT EINI_1

SETENV (NLS_LANG= AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD Ogg

RMTHOST gc2, MGRPORT 7809

RMTTASK REPLICAT, GROUP RINI_1

TABLE scott.TCUSTMER;

TABLE scott.TCUSTORD;

"dirprm/eini_1.prm" [New] 10L, 253Cwritten

gc2:配置Replicat进程

[oracle@gc2ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 10.4.0.19 Build 002

Linux, x86, 32bit (optimized), Oracle 10 on Sep17 2009 23:49:42

GGSCI(gc2) 1> ADD REPLICAT RINI_1, SPECIALRUN

REPLICAT added.

GGSCI(gc2) 2> INFO REPLICAT *, TASKS

REPLICAT RINI_1 Initialized 2014-06-18 10:03 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:00:09 ago)

Log Read Checkpoint Not Available

Task SPECIALRUN

GGSCI(gc2) 3> EDIT PARAMS RINI_1

-- GoldenGate Initial Load Delivery

REPLICAT RINI_1

SETENV (NLS_LANG= AMERICAN_AMERICA.ZHS16GBK)

ASSUMETARGETDEFS

USERID ogg, PASSWORD Ogg

DISCARDFILE ./dirrpt/RINIaa.dsc, PURGE

MAP scott.*, TARGET scott.*;

"dirprm/rini_1.prm" [New] 8L, 210Cwritten

gc1:开启Extract进程(目标端Replicate进程会自动开启)

GGSCI(gc1) 11> START EXTRACT EINI_1

Sending START request to MANAGER ...

EXTRACT EINI_1 starting

gc1:验证数据加载

GGSCI(gc1) 12> VIEW REPORT EINI_1

2014-06-18 10:13:43 GGS INFO 414 Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.

......

Output to RINI_1:

From Table SCOTT.TCUSTMER:

# inserts: 2

# updates: 0

# deletes: 0

# discards: 0

From Table SCOTT.TCUSTORD:

# inserts: 2

# updates: 0

# deletes: 0

# discards: 0

gc2:验证数据加载

GGSCI(gc2) 6> VIEW REPORT RINI_1

......

Report at 2014-06-18 10:13:57 (activity since2014-06-18 10:13:50)

From Table SCOTT.TCUSTMER to SCOTT.TCUSTMER:

# inserts: 2

# updates: 0

# deletes: 0

# discards: 0

From Table SCOTT.TCUSTORD to SCOTT.TCUSTORD:

# inserts: 2

# updates: 0

# deletes: 0

# discards: 0

gc2:确认初始化数据(由gc1传输而来)

SQL>conn scott/tiger

Connected.

SQL>select * from tcustmer;

CUST NAME CITY ST

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

WILL BG SOFTWARE CO. SEATTLE WA

JANE ROCKY FLYER INC. DENVER CO

SQL>set linesize 200

SQL>select * from tcustord

CUST ORDER_DATE PRODUCT_ ORDER_ID PRODUCT_PRICEPRODUCT_AMOUNT TRANSACTION_ID

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

WILL 30-SEP-94 CAR 144 17520 3 100

JANE 11-NOV-95 PLANE 256 133300 1 100

--从源端到目标端数据初始化成功

--数据初始完成后，源端Extract进程、目标端Replicat进程自动停止

gc1：检查Extract进程状态

GGSCI(gc1) 13> INFO EXTRACT EINI_1

EXTRACT EINI_1 Last Started 2014-06-1810:13 Status STOPPED

Checkpoint Lag Not Available

Log Read Checkpoint Table SCOTT.TCUSTORD

2014-06-18 10:13:53 Record 2

Task SOURCEISTABLE

gc2：检查Replicat进程状态

GGSCI(gc2) 1> INFO REPLICAT RINI_1

REPLICAT RINI_1 Initialized 2014-06-18 10:03 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:21:01 ago)

Log Read Checkpoint Not Available

Task SPECIALRUN

gc1：配置Extract进程

GGSCI(gc1) 14> 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

EXTTRAIL ./dirdat/aa

TABLE scott.TCUSTMER;

TABLE scott.TCUSTORD;

"dirprm/eora_1.prm" [New] 8L, 228Cwritten

GGSCI(gc1) 15> ADD EXTRACT EORA_1, TRANLOG, BEGIN NOW

EXTRACT added.

GGSCI(gc1) 16> ADD EXTTRAIL ./dirdat/aa, EXTRACT EORA_1, MEGABYTES 5

EXTTRAIL added.

GGSCI(gc1) 17> START EXTRACT EORA_1

Sending START request to MANAGER ...

EXTRACT EORA_1 starting

GGSCI(gc1) 18> INFO EXTRACT EORA_1

EXTRACT EORA_1 Last Started 2014-06-1810:29 Status RUNNING

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

Log Read Checkpoint Oracle Redo Logs

2014-06-18 10:29:19 Seqno 16, RBA 1328640

[oracle@gc1ogg]$ ll /u01/app/ogg/dirdat/ --验证跟踪文件

total 4

-rw-rw-rw- 1 oracle oinstall 893 Jun 18 10:29 aa000000

gc1：配置Pump进程

GGSCI(gc1) 1> EDIT PARAMS PORA_1

-- Data Pump parameter file to read the local

-- trail of TCUSTMER and TCUSTORD changes

EXTRACT PORA_1

SETENV (NLS_LANG= AMERICAN_AMERICA.ZHS16GBK)

PASSTHRU

RMTHOST gc2, MGRPORT 7809

RMTTRAIL ./dirdat/pa

TABLE scott.TCUSTMER;

TABLE scott.TCUSTORD;

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

GGSCI(gc1) 2> ADD EXTRACT PORA_1, EXTTRAILSOURCE ./dirdat/aa

EXTRACT added.

GGSCI(gc1) 3> INFO EXTRACT PORA_1

EXTRACT PORA_1 Initialized 2014-06-18 10:35 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:00:14 ago)

Log Read Checkpoint File ./dirdat/aa000000

First Record RBA 0

GGSCI(gc1) 4> ADD RMTTRAIL ./dirdat/pa, EXTRACT PORA_1, MEGABYTES 5

RMTTRAIL added.

GGSCI(gc1) 5> START EXTRACT PORA_1

Sending START request to MANAGER ...

EXTRACT PORA_1 starting

GGSCI(gc1) 6> INFO EXTRACT PORA_1

EXTRACT PORA_1 Last Started 2014-06-1810:36 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:04 ago)

Log Read Checkpoint File ./dirdat/aa000000

First Record RBA 0

gc2：验证gc1Pump进程配置

[oracle@gc2ogg]$ ll dirdat/--在目标端生成名为pa000000的追踪文件

total 0

-rw-rw-rw- 1 oracle oinstall 0 Jun 18 10:36pa000000

gc2：配置Checkpoint

GGSCI(gc2) 1> EDIT PARAMS ./GLOBALS

CHECKPOINTTABLE ogg.ggschkpt

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

[oracle@gc2ogg]$ll GLOBALS--检查参数已添加

-rw-rw-rw- 1 oracle oinstall 29 Jun 18 10:42GLOBALS

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

Successfully logged into database.

GGSCI(gc2) 2> ADD CHECKPOINTTABLE

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

Successfully created checkpoint tableOGG.GGSCHKPT.

gc2：配置Replicat进程

GGSCI(gc2) 3> ADD REPLICAT RORA_1, EXTTRAIL ./dirdat/pa

REPLICAT added.

GGSCI(gc2) 4> 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(gc2) 5> START REPLICAT RORA_1

Sending START request to MANAGER ...

REPLICAT RORA_1 starting

GGSCI(gc2) 6> INFO REPLICAT RORA_1

REPLICAT RORA_1 Last Started 2014-06-1810:48 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:07 ago)

Log Read Checkpoint File ./dirdat/pa000000

First Record RBA 0

gc1： DML操作:insert

SQL>conn scott/tiger

Connected.

SQL>insert into tcustmer VALUES('HYL','HUANGCO.','BEIJING','AU');

1 row created.

SQL>commit;

Commit complete.

gc2：验证insert操作是否同步

SQL>conn scott/tiger

Connected.

SQL>select * from tcustmer;

CUST NAME CITY ST

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

HYL HUANGCO. BEIJING AU

WILL BG SOFTWARE CO. SEATTLE WA

JANE ROCKY FLYER INC. DENVER CO

gc1：DML操作:update

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

1 row updated.

SQL>commit;

Commit complete

gc2：验证update操作是否同步

SQL>select * from tcustmer;

CUST NAME CITY ST

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

HYL HUANGCO. SHANGHAI CN

WILL BG SOFTWARE CO. SEATTLE WA

JANE ROCKY FLYER INC. DENVER CO

gc1：DML操作:delete

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

1 row deleted.

SQL>commit;

Commit complete.

gc2：验证delete操作是否同步

SQL>select * from tcustmer;

CUST NAME CITY ST

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

WILL BG SOFTWARE CO. SEATTLE WA

JANE ROCKY FLYER INC. DENVER CO

声明：

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

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

本文原创发布php中文网，转载请注明出处，感谢您的尊重！

PeterPan(fake)

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql add trandata_GoldenGate配置（一）之单向复制配置

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 O...
复制链接

扫一扫