goldengate原理与简单配置

goldengate的原理：http://www.ipresst.com/works/52bc44c42954a7d73b0003f2

简单单向配置：

在oracle官网下载下载 GoldenGate

http://www.oracle.com/technetwork/cn/middleware/goldengate/downloads/index.html

2.解压文件到一个目录下，在文件目录下打开ggsci.exe,界面如下，输入help,可看到各种命令提示

创建需要的目录：我这里已经创建过了

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

5.创建存放DDL信息的user并赋权

SQL>create user ggate identified by ggate default tablespace users

SQL>grant connect,tesource to ggate;

SQL>grant execute on utl_file to ggate;

在Source DB上创建测试用户

SQL>create user ggs identified by ggs default tablespace users

SQL>grant dba to ggs;

在target DB上创建测试用户

SQL>create user ggt identified by ggt default tablespace users

SQL>grant dba to ggt;

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

D:>cd D:studysoftwaregoldengate

D:studysoftwaregoldengate>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;

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

配置SourceDb的复制队列

连接到数据库，测连接

GGSCI (WIN-E1A8FKUJLVS) 4> dblogin userid ggate,password ggate

Successfully logged into database.

增加一个抽取

GGSCI (WIN-E1A8FKUJLVS) 5> add extract ext1,tranlog,begin now

GGSCI (WIN-E1A8FKUJLVS) 6> add exttrail D:studysoftwaregoldengatedirdatlt,ex

tract ext1 ---该路径是goldengate的路径

编辑抽取进程ext1参数

GGSCI (WIN-E1A8FKUJLVS) 8> edit params ext1

extract ext1
userid ggate,password ggate
rmthost 127.0.0.1,mgrport 7809
rmttrail D:studysoftwaregoldengatedirdatlt
dynamicresolution
table ggs.*; --ggs为源数据库，即要备份的数据库，*表示所有表

GGSCI (WIN-E1A8FKUJLVS) 9> start ext1

Sending START request to MANAGER ...

EXTRACT EXT1 starting

9.配置TargetDB同步队列

(1)在Target端添加checkpoint表

GGSCI (WIN-E1A8FKUJLVS) 1> edit params ./GLOBAL

GGSCHEMA ggate

CHECKPOINTTABLE ggate.checkpoint

添加以上2条数据

GGSCI (WIN-E1A8FKUJLVS) 2> dblogin userid ggate,password ggate

Successfully logged into database.

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

GGSCI (WIN-E1A8FKUJLVS) 3> add checkpointtable ggate.checkpoint

Successfully created checkpoint table ggate.checkpoint.

创建同步队列

GGSCI (WIN-E1A8FKUJLVS) 4> add replicat rep1,exttrail D:studysoftwaregoldenga

edirdatlt,checkpointtable ggate.checkpoint

REPLICAT added.

GGSCI (WIN-E1A8FKUJLVS) 5> edit params rep1

replicat rep1
userid ggate,password ggate
assumetargetdefs
discardfile E:ggatedirdat ep1_discard.txt,append
MAP ggs.*, TARGET ggt.*;---目标表和源表的映射

开启同步队列

GGSCI (WIN-E1A8FKUJLVS) 6> start ext1

EXTRACT EXT1 is already running.

GGSCI (WIN-E1A8FKUJLVS) 7> start rep1

Sending START request to MANAGER ...

REPLICAT REP1 starting

测试Data 复制

1.在Source DB端和Target DB上都建立测试表

SQL> conn ggs/ggs
Connected.

SQL> @D:studysoftwaregoldengatedemo_ora_create.sql;

SQL> conn ggt/ggt
Connected.

SQL> @D:studysoftwaregoldengatedemo_ora_create.sql;

在sourceDB端插入数据

SQL> @D:studysoftwaregoldengatedemo_ora_insert.sql

在target端就能看到数据了