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

最新推荐文章于 2022-09-28 16:01:28 发布

茄肥猫

最新推荐文章于 2022-09-28 16:01:28 发布

阅读量1.3k

点赞数

分类专栏： ORACLE 文档文章标签： oracle oracle goldengate dba

ORACLE 文档专栏收录该内容

224 篇文章 1 订阅

订阅专栏

测试环境：

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

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.

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>.