oracle 11g ogg配置,Oracle GoldenGate学习之windows下ogg单向复制配置

.

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

数据同步过来了。

一些错误的处理：

ERROR OGG-00868 Oracle GoldenGate Delivery for Oracle,

REP1.prm: OCI error (1008-ORA-01008: not all

variables bound) executing query to fetch primary key, SQL <

SELECT

c.constraint_name, c.column_name FROM all_cons_columns c WHERE

c.owner =

:owner1 AND c.table_name =

:table1 AND c.constraint_name

= (SELECT

c1.name FROM>.

解决：sqlplus / as sysdba

SQL>alter system flush

shared_pool;

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