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 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 |
单向复制配置
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
Copyright (C)1995, 2009, Oracle and/or its affiliates. All rights reserved.
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
Copyright (C) 1995, 2009, Oracle and/or itsaffiliates. All rights reserved.
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.
1 row created.
1 row created.
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
--------
NO
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
---
NO
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
Copyright (C) 1995, 2009, Oracle and/or itsaffiliates. All rights reserved.
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/
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学习(九)--特殊的流程控制语句
推荐阅读
-
前端笔记之Vue(一)初识SPA和Vue&webpack配置和vue安装&指令
-
spring boot之入门配置(一)
-
轻量级mvc框架之:stripes (一、约定大于配置)
-
NoSql选型:Hbase+hadoop想说爱你不容易之单机配置(一)
-
NoSql选型:Hbase+hadoop想说爱你不容易之单机配置(一)
-
vscode 入门之配置python环境和插件(一)
-
创建持久化和映射文件并配置单向一对多关系
-
oracle12.2RAC之OGG安装配置(一)
-
WCF学习笔记一之通过配置web.config可以通过http访问接口
-
Zend Framework入门之环境配置及第一个Hello World示例(附demo源码下载)_php实例