GoldenGate复制1：linux下oracle到oracle简单测试
操作系统: Oracle linux 6.3
Oracle: 11.2.0.3.0
GoldenGate: fbo_ggs_Linux_x64_ora11g_64bit.tar
是否支持DDL：否 （如果想支持需要源端关闭recyclebin）
Init load方式： direct load
复制架构：一对一
具体环境：
|
| 源端(单实例) | 目标端(单实例) |
| hostname | OEL63 | vmzsh |
| OS | OEL6.4_64位 | OEL6.4_64位 |
| ip | 192.168.112.135 | 192.168.115.232 |
| database | 11g | 11g |
| ogg | fbo_ggs_Linux_x64_ora11g_64bit.tar | fbo_ggs_Linux_x64_ora11g_64bit.tar |
|
|
|
|
一．GoldenGate安装
这部分源端、目标端都需要做，以源端操作演示，两端操作大致一样，个别不一样的地方会有说明。
1.软件解压
[root@OEL63 ~]# su - oracle
[oracle@OEL63 gg11]$ pwd
/u01/oracle/gg11
[oracle@OEL63 gg11]$ tar xvffbo_ggs_Linux_x64_ora11g_64bit.tar
2.环境变量：
新增：
PATH=$ORACLE_BASE/gg11
LD_LIBRARY_PATH=$ORACLE_BASE/gg11:$ORACLE_HOME/lib
具体内容如下：
export PATH
export ORACLE_BASE=/u01/oracle
exportORACLE_HOME=/u01/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_BASE/gg11:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/bin
export ORACLE_SID=qing
export LD_LIBRARY_PATH=$ORACLE_BASE/gg11:$ORACLE_HOME/lib:$ORACLE_HOME/lib32
3.安装GoldenGate
[oracle@OEL63 gg11]$ pwd
/u01/oracle/gg11
[oracle@OEL63 gg11]$ ggsci
Oracle GoldenGate Command Interpreter forOracle
Version 11.1.1.1.2OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 11gon Oct 4 2011 23:49:46
Copyright (C) 1995, 2011, Oracle and/or itsaffiliates. All rights reserved.
GGSCI (OEL63) 1> create subdirs
Creating subdirectories under currentdirectory /u01/oracle/gg11
Parameter files /u01/oracle/gg11/dirprm:created
Report files /u01/oracle/gg11/dirrpt:created
Checkpoint files /u01/oracle/gg11/dirchk: created
Process status files /u01/oracle/gg11/dirpcs: created
SQL script files /u01/oracle/gg11/dirsql: created
Database definitions files /u01/oracle/gg11/dirdef: created
Extract data files /u01/oracle/gg11/dirdat: created
Temporary files /u01/oracle/gg11/dirtmp:created
Veridata files /u01/oracle/gg11/dirver:created
Veridata Lock files /u01/oracle/gg11/dirver/lock:created
Veridata Out-Of-Sync files /u01/oracle/gg11/dirver/oos: created
Veridata Out-Of-Sync XML files/u01/oracle/gg11/dirver/oosxml: created
Veridata Parameter files /u01/oracle/gg11/dirver/params: created
Veridata Report files /u01/oracle/gg11/dirver/report:created
Veridata Status files /u01/oracle/gg11/dirver/status:created
Veridata Trace files /u01/oracle/gg11/dirver/trace:created
Stdout files /u01/oracle/gg11/dirout: created
二．配置
1.配置数据库
源端启用归档：
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 295
Next log sequence to archive 298
Current log sequence 298
源端启用supplement log:
目标端可以不用打开
SQL> Select supplemental_log_data_minfrom V$database;
SQL> Alterdatabase add supplemental log data;
SQL> alter system switch logfile;
SQL> Select supplemental_log_data_minfrom V$database;
SUPPLEME
--------
YES
由于Oracle中每行记录是由rowid来唯一标识的，但是逻辑复制（如goldengate,stream等）源端和目标端数据库的数据块的结构可能完全不一样，Rowid无法定位。所以使用了supplement log后，就可以精准定位每一条记录，解决了该问题。
源端启用force log:
Alter database force logging;
Select name,open_mode,force_logging,supplemental_log_data_minfrom V$database;
两端创建管理用户:
SQL> create user ggsidentified by ggs default tablespace users;
SQL> grant dba to ggs;
可以不用直接给dba仅限，给仅仅需要的权限，具体参考官方文档。
2.配置GoldenGate
源端与目标端都需要配
配置mgr进程
GGSCI (OEL63) 1> edit params mgr
port 7809
GGSCI (OEL63) 4> start mgr
GGSCI (OEL63) 3> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
GGSCI (OEL63) 7> view report mgr
GGSCI (OEL63) 8> info mgr
Manager is running (IP port OEL63.7809).
GGSCI (OEL63)2> edit params ./GLOBALS
GGSCHEMA ggs
3.DML同步
a)首先源库和备库都创建相同的表
操作过程是在源库和目标库创建两个表（通过demo_ora_create.sql;）
[oracle@OEL63 gg11]$ cd /u01/oracle/gg11/
[oracle@OEL63 gg11]$ sqlplus ggs/ggs
SQL> @demo_ora_create.sql;
SQL> conn gguser/gguser
Connected.
SQL> @demo_ora_create
B）只在源端插入数据
SQL> @demo_ora_insert
c)在源端对要传输的表设置trandata
add trandata 命令可以是直接操作具体的表，也是用*号操作所有表。
例如： add trandata ggs.tabl1
Add trandata ggs.*
GGSCI (OEL63) 9> dblogin userid ggs, password ggs
Successfully logged into database.
GGSCI (OEL63) 10> add trandata ggs.*
Logging of supplemental redo data enabledfor table GGS.TCUSTMER.
Logging of supplemental redo data enabledfor table GGS.TCUSTORD.
GGSCI (OEL63) 13> info trandata ggs.*
Logging of supplemental redo log data isenabled for table GGS.TCUSTMER
Logging of supplemental redo log data isenabled for table GGS.TCUSTORD
d) initial Load初始化数据
本次使用goldengate的方法初始化，direct load
源库上操作
GGSCI (OEL63) 14> add extract einitan,sourceistable
EXTRACT added.
GGSCI (OEL63) 15> edit params einitan
EXTRACT EINITAN
USERID ggs, password "ggs"
RMTHOST 192.168.115.232, MGRPORT 7809
RMTTASK REPLICAT, GROUP RINITAN
TABLE ggs.TCUSTMER;
TABLE ggs.TCUSTORD;
目标库操作
GGSCI (vmzsh) 6> add replicat rinitan, specialrun
REPLICAT added.
GGSCI (vmzsh) 7> info replicat *, tasks
REPLICAT RINITAN Initialized 2014-05-07 14:19 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:00:22 ago)
Log Read Checkpoint Not Available
Task SPECIALRUN
GGSCI (vmzsh) 8> edit params RINITAN
REPLICAT RINITAN
ASSUMETARGETDEFS
USERID ggs, password ggs
DISCARDFILE ./dirrpt/RINITAN.dsc, PURGE
MAP ggs.*, TARGET ggs.*;
启动extract进程
GGSCI (OEL63) 17> start extract einitan
Sending START request to MANAGER ...
EXTRACT EINITAN starting
GGSCI (OEL63) 18> view report einitan
GGSCI (vmzsh) 13> view report rinitan
目标端检查：
SQL> /
CUST NAME CITY ST
---- -------------------------------------------------- --
WILL BG SOFTWARE CO. SEATTLE WA
JANE ROCKY FLYER INC. DENVER CO
SQL> select * from tcustord ;
CUST ORDER_DAT PRODUCT_ ORDER_ID PRODUCT_PRICE PRODUCT_AMOUNTTRANSACTION_ID
---- --------- -------- ----------------------- -------------- --------------
WILL 30-SEP-94 CAR 144 17520 3 100
JANE 11-NOV-95 PLANE 256 133300 1 100
e)配置capture
GGSCI (OEL63) 14> add extract eoratan, tranlog,begin now,threads1
EXTRACT added.
GGSCI (OEL63) 15> info extract *
EXTRACT EORATAN Initialized 2014-05-07 15:01 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:00:11 ago)
Log Read Checkpoint Oracle Redo Logs
2014-05-07 15:01:11 Thread 1, Seqno 0, RBA 0
GGSCI (OEL63) 16> edit params eoratan
EXTRACT EORATAN
userid ggs, password ggs
RMTHOST 192.168.115.232, mgrport 7809
RMTTRAIL ./dirdat/ta
TABLE ggs.TCUSTMER;
TABLE ggs.TCUSTORD;
增加remotetrail文件
GGSCI (OEL63) 20> add RMTTRAIL./dirdat/ta, EXTRACT EORATAN, MEGABYTES 5
RMTTRAIL added.
--注意这儿的文件名必须是2个字符，超长会报错提醒
GGSCI (OEL63) 22> info rmttrail *
Extract Trail: ./dirdat/ta
Extract: EORATAN
Seqno: 0
RBA: 0
File Size: 5M
启动capture
GGSCI (OEL63) 23> start extract eoratan
Sending START request to MANAGER ...
EXTRACT EORATAN starting
GGSCI (OEL63) 30> view report eoratan
GGSCI (OEL63) 35> info extract eoratan ,detail
EXTRACT EORATAN Last Started 2014-05-0715:39 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:10 ago)
Log Read Checkpoint Oracle Redo Logs
2014-05-07 16:26:19 Thread 1, Seqno 323, RBA 16501248
Target Extract Trails:
Remote Trail Name Seqno RBA Max MB
./dirdat/ta 0 1131 5
Extract Source Begin End
/u01/oracle/oradata/qing/redo04.raw 2014-05-07 15:01 2014-05-07 16:26
NotAvailable *Initialized * 2014-05-07 15:01
Current directory /u01/oracle/gg11
Report file /u01/oracle/gg11/dirrpt/EORATAN.rpt
Parameter file /u01/oracle/gg11/dirprm/eoratan.prm
Checkpoint file /u01/oracle/gg11/dirchk/EORATAN.cpe
Process file /u01/oracle/gg11/dirpcs/EORATAN.pce
Stdout file /u01/oracle/gg11/dirout/EORATAN.out
Error log /u01/oracle/gg11/ggserr.log
f) 配置replicate
在目标端操作
GGSCI (vmzsh) 19>edit params ./GLOBALS
CHECKPOINTTABLEggs.ggs_checkpoint
GGSCI(vmzsh) 20> exit
[oracle@vmzshgg11]$ ggsci
GGSCI(vmzsh) 1> dblogin userid ggs, password ggs
Successfullylogged into database.
GGSCI(vmzsh) 2> add checkpointtable
检查：
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ -----------------
GGS_CHECKPOINT TABLE
TCUSTMER TABLE
TCUSTORD TABLE
下面名字得和源端写的一样。
GGSCI (vmzsh) 3> add replicat roratan,exttrail ./dirdat/ta
REPLICAT added.
GGSCI (vmzsh) 4> edit params roratan
REPLICAT RORATAN
USERID ggs, PASSWORD ggs
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/RORAKK.DSC, PURGE
MAP ggs.*, TARGET ggs.*;
GGSCI (vmzsh) 2> start replicat roratan
GGSCI (vmzsh) 3> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RORATAN 00:00:00 00:00:03
g) 检查
在源端插入数据，在目标端查看变化
源端
SQL> insert into tcustmer values('aaaa','tan','bj','tt');
1 row created.
SQL> commit;
目标端：
SQL> /
CUST NAME CITY ST
---- ------------------------------ ----------------------
WILL BG SOFTWARE CO. SEATTLE WA
JANE ROCKY FLYER INC. DENVER CO
aaaa tan bj tt
1435
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
