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 201100:50:35

Copyright (C) 1995, 2011, Oracle and/or its affiliates. Allrights 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> createsudbirs   --创建需要的目录

二、配置Source DB

GoldenGate通过抓取源端数据库重做日志进行分析，将获取的数据应用到目标端，实现数据同步。因此，源数据库需要必须处于归档模式，并启用附加日志和强制日志。

归档模式、附加日志、强制日志

SQL> select log_mode,supplemental_log_data_min,force_loggingfrom 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 triggersession receives implicitrecycle bin DDL operations that cause thetrigger 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 tablespaceusers 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 tablespaceusers temporary tablespace temp;

SQL>grant dba to ggs;

3.在Target DB上创建测试用户

SQL>create user ggt identified by ggt default tablespaceusers temporary tablespace temp;

SQL>grant dba to ggt;

3.退出所有使用Oracle的session，然后使用SYSDBA权限的用户执行如下脚本：

E:\>cdE:\ggate

E:\ggate>sqlplus / as sysdba

SQL>@marker_setup.sql; --提示过程中输入用户:ggate

SQL>@ddl_setup.sql;    --提示过程中输入用户:ggate;INITIALSETUP

SQL>@role_setup.sql;   --提示过程中输入用户:ggate

SQL>grantGGS_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 extractext1,tranlog, begin now

2011-11-08 20:36:47 INFO   OGG-01749 Successfully registeredEXTRACT EXT1 to start managing log retentionat SCN 1121060.

EXTRACT added.

GGSCI (gg1) 12> add exttrailE:\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

CHECKPOINTTABLEggate.checkpoint

添加如上2条记录。

GGSCI (gg2) 12> dblogin useridggate,password ggate

Successfully logged intodatabase.

--说明，这个用户是在Source 库启用DDL 创建的，我在Target库也创建了这个用户。

GGSCI (gg2) 13> add checkpointtableggate.checkpoint

Successfully created checkpointtableGGATE.CHECKPOINT.

3.2创建同步队列

GGSCI (gg2) 14> add replicatrep1,exttrail E:\ggate\dirdat\lt, checkpointtableggate.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;

CUSTNAME                          CITY                ST
---- ------------------------------ -------------------- --
WILL BG SOFTWARECO.               SEATTLE             WA
JANE ROCKY FLYERINC.              DENVER              CO

SQL> select * from TCUSTORD;

CUSTORDER_DATE         PRODUCT_   ORDER_ID PRODUCT_PRICEPRODUCT_AMOUNT
---- ------------------- -------- ---------- ---------------------------
TRANSACTION_ID
--------------
WILL 1994-09-30 15:33:00CAR            144        17520             3
          100

JANE 1995-11-11 13:52:00PLANE          256       133300             1
          100

数据同步过来了。
一些错误的处理：

ERROR  OGG-00868  Oracle GoldenGate Delivery for Oracle,REP1.prm:  OCI error (1008-ORA-01008: not allvariables bound) executing query to fetch primary key, SQL <SELECTc.constraint_name,        c.column_name   FROM all_cons_columns c   WHEREc.owner =:owner1    AND c.table_name =:table1    AND c.constraint_name=        (SELECTc1.name           FROM>.

解决：sqlplus / as sysdba

SQL>alter system flushshared_pool;

应该还有其他的办法可以解决这个问题，暂时没搞清楚。