Oracle GoldenGate学习之windows下ogg单向复制配置

Oracle GoldenGate学习之windows下ogg单向复制配置
测试环境：
OS： Windows Xp
DB: Oracle 10.2.0.3 32bit
一、安装goldengate
   goldengate安装不是很复杂，在oracle官方网站上下载ggs_Windows_x86_ora10g_32bit.zip，放到一个目录(我的目录是E:\ggate)解压即可。
E:\ggate>ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Windows (optimized), Oracle 10g on Oct  5 2011 00:50:35
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (mybole) 2> help all
GGSCI Command Summary:
Object:          Command:
SUBDIRS          CREATE
ER               INFO, KILL, LAG, SEND, STATUS, START, STATS, STOP
EXTRACT          ADD, ALTER, CLEANUP, DELETE, INFO, KILL,
                 LAG, REGISTER, SEND, START, STATS, STATUS, STOP
                 UNREGISTER
EXTTRAIL         ADD, ALTER, DELETE, INFO
GGSEVT           VIEW
MANAGER          INFO, SEND, START, STOP, STATUS
MARKER           INFO
PARAMS           EDIT, VIEW
REPLICAT         ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, SEND,
                 START, STATS, STATUS, STOP
REPORT           VIEW
RMTTRAIL         ADD, ALTER, DELETE, INFO
TRACETABLE       ADD, DELETE, INFO
TRANDATA         ADD, DELETE, INFO
SCHEMATRANDATA   ADD, DELETE, INFO
CHECKPOINTTABLE  ADD, DELETE, CLEANUP, INFO
Commands without an object:
(Database)       DBLOGIN, LIST TABLES, ENCRYPT PASSWORD, FLUSH SEQUENCE
(DDL)            DUMPDDL
(Miscellaneous)  FC, HELP, HISTORY, INFO ALL, OBEY, SET EDITOR, SHELL,
                 SHOW, VERSIONS, ! (note: you must type the word
                 COMMAND after the ! to display the ! help topic.)
                 i.e.: GGSCI (sys1)> help ! command
 
For help on a specific command, type HELP .
Example: HELP ADD REPLICAT
GGSCI (mybole) 2> create sudbirs   --创建需要的目录
二、配置Source DB
GoldenGate通过抓取源端数据库重做日志进行分析，将获取的数据应用到目标端，实现数据同步。因此，源数据库需要必须处于归档模式，并启用附加日志和强制日志。
归档模式、附加日志、强制日志
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;
LOG_MODE     SUPPLEME FOR
------------ -------- ---
ARCHIVELOG   YES      YES
如果为NO，需要添加，命令如下
1）archivelog
SQL>shutdown immediate
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;
（2） force logging
SQL>alterdatabase force logging;
（3）supplemental log data
SQL>alterdatabase add supplemental log data
如果启用DDL 支持，必须关闭recycle bin。官网的解释如下：
If the recyclebin is enabled, the Oracle GoldenGate DDL trigger session receives implicitrecycle bin DDL operations that cause the trigger to fail.
Oracle 11g：
SQL> alter system set recyclebin=offscope=spfile;
System altered.
如果数据库是10g，需要关闭recyclebin并重启；或者手工purge recyclebin。
三、创建user
1. 创建存放DDL信息的user并赋权
SQL> create user ggate identified by ggate default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource to ggate;
Grant succeeded.
SQL> grant execute on utl_file to ggate;
Grant succeeded.
2.在Source Db上创建测试用户
SQL>create user ggs identified by ggs default tablespace users temporary tablespace temp;
SQL>grant dba to ggs;
3.在Target DB上创建测试用户
SQL>create user ggt identified by ggt default tablespace users temporary tablespace temp;
SQL>grant dba to ggt;
3.退出所有使用Oracle 的session，然后使用SYSDBA权限的用户执行如下脚本：
E:\>cd E:\ggate
E:\ggate>sqlplus / as sysdba
SQL>@marker_setup.sql;  --提示过程中输入用户:ggate
SQL>@ddl_setup.sql;     --提示过程中输入用户:ggate;INITIALSETUP
SQL>@role_setup.sql;    --提示过程中输入用户:ggate
SQL>grant GGS_GGSUSER_ROLE to ggate;
SQL>@ddl_enable.sql;
四、配置相关进程
1.在Source 和Target 上配置Manager
GGSCI (gg1) 1> info all
Program    Status      Group       Lag           Time Since Chkpt
MANAGER    STOPPED                                          
GGSCI (gg1) 2> edit params mgr
输入如下内容:
PORT 7809
GGSCI (gg1) 3> start manager
Manager started.
2.配置SourceDB 的复制队列
 先连接到数据库，测试连接：
GGSCI (gg1) 10> dblogin userid ggate, password ggate
Successfully logged into database.
增加一个抽取：
GGSCI (gg1) 11> add extract ext1,tranlog, begin now
2011-11-08 20:36:47  INFO   OGG-01749  Successfully registeredEXTRACT EXT1 to start managing log retention at SCN 1121060.
EXTRACT added.
GGSCI (gg1) 12> add exttrail E:\ggate\dirdat\lt, extract ext1
EXTTRAIL added
编辑抽取进程ext1参数：
GGSCI (gg1) 13> edit params ext1
extract ext1
userid ggate,password ggate
rmthost 127.0.0.1,mgrport 7809
rmttrail E:\ggate\dirdat\lt
dynamicresolution
table ggs.*;
GGSCI (gg1) 14> start ext1
GGSCI (gg1) 14> info all
3.配置TargetDB 同步队列
3.1在Target 端添加checkpoint表：
GGSCI (gg2) 6> edit params ./GLOBAL
GGSCHEMA ggate
CHECKPOINTTABLE ggate.checkpoint
添加如上2条记录。
GGSCI (gg2) 12> dblogin userid ggate,password ggate
Successfully logged into database.
--说明，这个用户是在Source 库启用DDL 创建的，我在Target 库也创建了这个用户。
GGSCI (gg2) 13> add checkpointtable ggate.checkpoint
Successfully created checkpoint tableGGATE.CHECKPOINT.
3.2 创建同步队列
GGSCI (gg2) 14> add replicat rep1,exttrail E:\ggate\dirdat\lt, checkpointtable ggate.checkpoint
REPLICAT added.
GGSCI (gg2) 15> edit params rep1
replicat rep1
userid ggate,password ggate
assumetargetdefs
discardfile E:\ggate\dirdat\rep1_discard.txt,append
MAP ggs.*, TARGET ggt.*;
3.3开启同步队列
GGSCI (gg2) 14> start ext1
GGSCI (gg2) 14> start rep1
GGSCI (gg2) 14> info all
Program     Status      Group       Lag           Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:07
REPLICAT    RUNNING     REP1        00:00:00      00:00:06
所有的进程状态都是RUNNING，正常。
五、测试Data 复制
1.在Source DB端和Target DB上都建立测试表
SQL> conn ggs/ggs
Connected.
SQL> host pwd
E:\ggate
SQL> @demo_ora_create.sql
Table Created.
SQL> conn ggt/ggt
Connected.
SQL> host pwd
E:\ggate
SQL> @demo_ora_create.sql
Table Created.
2.在Source DB端插入数据
SQL> @demo_ora_insert.sql
1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.
3.在Target DB端查看
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TCUSTMER                       TABLE
TCUSTORD                       TABLE
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_PRICE PRODUCT_AMOUNT
---- ------------------- -------- ---------- ------------- --------------
TRANSACTION_ID
--------------
WILL 1994-09-30 15:33:00 CAR             144         17520              3
           100
JANE 1995-11-11 13:52:00 PLANE           256        133300              1
           100
数据同步过来了。