替代oracle ogg,Oracle Goldengate （OGG） 单机 实验

本次实验主要是模拟在单机上面安装和配置OGG，并实现简单的DDL 和 DML复制

1. 首先到oracle官网下载相应的OGG 版本，本人下载的是版本为：ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip。

2. 查看OS 和DB 版本

$ uname -a

SQL> SELECT * FROM V$VERSION WHERE ROWNUM <2;

3. 配置环境变量

vi  .bash_profile

export PATH=/u01/ogg:$PATH

export GGATE=/u01/ogg

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

(LD_LIBRARY_PATH)要配置，否则汇报 ./ggsci : error while loading shared libraries:libnnzl1.so)

3. 安装ogg

把ogg软件copy到/u01/ogg下

$ cd /u01/ogg

$ unzip ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip

$ tar -xvf fbo_ggs_Linux_x86_ora11g_32bit.tar

$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (ogg.us.oracle.com) 1> create subdirs

4. 创建存放DDL /DML 信息的用户并授权

SQL> create user ggate idendified by ggate;

SQL> grant connect,resource,dba to ggate;

SQL> grant execute on utl_file to ggate;

$ cd $GGATE  --- 就是环境变量中指定的路径

$ sqlplus  / as sysdba

SQL> @marker_setup.sql;

SQL> @ddl_setup.sql;

SQL> @role_setup.sql;

SQL> grant GGS_GGSUSER_ROLE TO ggate;

SQL> @ddl_enable.sql;

5. 打开辅助日志

goldengate 通过抓取源端数据库重做日志进行分析，将获取的数据应用到目标端，实现数据同步。

5.1 打开归档模式

archive log list;

alter database archivelog;

5.2 supplemental log

select log_mode,supplemental_log_data_min, force_logging from v$database;

强制日志    alter database force logging;

附加日志    alter database add supplemental log data;

5.3 禁用recyclebin(oracle10g 需要禁用， oracle11g 可以不禁用)

alter systemn set recyclebin = off scope=spfile;

6.  测试

在源数据库和目标数据库分别创建一个user ，用于测试

-----源数据库

create user source identified by oracle;

grant connect,resource,dba to source;

----目标数据库

create user target identified by oracle;

grant connect, resource, dba to target;

6. 在source 和 target 分别配置manager

---源端

GGSCI (ogg.us.oracle.com) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED

GGSCI (ogg.us.oracle.com) 2> edit params mgr

port 7809

GGSCI (ogg.us.oracle.com) 3> start mgr

GGSCI (ogg.us.oracle.com) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

--连接数据库

GGSCI (ogg.us.oracle.com) 5> dblogin userid , password ggate

--add extract

GGSCI (ogg.us.oracle.com) 6> add extract ext1, tranlog, begin now

GGSCI (ogg.us.oracle.com) 7> add exttrail /u01/ogg/dirdat/lt, extract ext1

GGSCI (ogg.us.oracle.com) 8> edit params ext1

extract ext1

userid ggate

rmthost ogg.us.oracle.com,mgrport 7809

rmttrail /u01/ogg/dirdat/lt

ddl include mapped objname source.*;

table source.*;

GGSCI (ogg.us.oracle.com) 9> info all

如果不是running 可以start mgr， 在不是需要查看上面的内容是否有错。

---目标端

GGSCI (ogg.us.oracle.com) 1> edit params ./GLOBAL

GGSCHEMA ggate

CHECKPOINTTABLE ggate.checkpoint

GGSCI (ogg.us.oracle.com) 2> dblogin userid ggate

GGSCI (ogg.us.oracle.com) 3> add checkpointtable ggate.checkpoint

GGSCI (ogg.us.oracle.com) 4> add replicat rep1, extrail /u01/ogg/dirdat/lt, checkpointtable ggate.checkpoint

GGSCI (ogg.us.oracle.com) 5> edit params rep1

replicat rep1

ASSUMETARGETDEFS

userid ggate

discardfile /u01/ogg/dirdat/rep1_discard.txt,append,megabytes 10

DDL

map source.*, target target.*;

7. 开启同步

--源端

GGSCI (ogg.us.oracle.com) 1> start extract ext1

GGSCI (ogg.us.oracle.com) 2> info all

--目标端

GGSCI (ogg.us.oracle.com) 1>start replicat rep1

GGSCI (ogg.us.oracle.com) 2> info all

8. 测试数据复制

$ sqlplus source/oracle

SQL> create table test1 as select * from dba_objects;

SQL> create table test2(id number);

SQL> INSERT into test2 values(1);

SQL> commit;

---目标端查看是否复制成功

$sqlplus target/oracle

SQL> select count(*) from test1;

SQL>select count(*) from test2;

如果没有成功，则要检查：

在源端，首先查看要复制的表是否日志信息完整，必须是logging

select owner|'.'||table_name table_name, logging from dba_tables where owner='SOURCE';

--把nologing变成logging

alter table source.* logging;

还有查看表级附件日志是否添加成功。以ggate 这个schema login

GGSCI (ogg.us.oracle.com) 3> dblogin userid ggate, password ggate

查看：

GGSCI (ogg.us.oracle.com) 4> info trandata source.*

Logging of supplemental redo log data is disabled for table SOURCE.TEST1.

Logging of supplemental redo log data is disabled for table SOURCE.TEST2.

发现是disable， 需要添加

GGSCI (ogg.us.oracle.com) 5> add trandata source.*

2015-01-06 06:29:23  WARNING OGG-00869  No unique key is defined for table 'TEST1'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table SOURCE.TEST1.

2015-01-06 06:29:29  WARNING OGG-00869  No unique key is defined for table 'TEST2'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

如果没有unique key 报warning， 但复制不受影响。

GGSCI (ogg.us.oracle.com) 5> info trandata source.*