oracle-goldgate

来源：互联网发布：更改淘宝店铺主营类目编辑：程序博客网时间：2024/04/27 18:24

/**********************************************************************************/
oracle 11.2 上利用 OGG 12.2.0.1 向 INFORMIX 复制数据
/**********************************************************************************/
一环境信息
oracle :11.2.0.4.0
oracle ogg :12.2.0.1.1
oracle ogg 用户:数据库用户ggs12

informix :1.6.3.71
informix ogg :12.2.0.1.1
informix ogg 复制用户:操作系统用户rep(为双向复制建立,如果是单向的,不需要此用户)

测试数据库 : oracle 端 kkbi 模式
informix 端 kkbi 数据库

1 本次2端使用 ogg 12.2 版本,解决了 TRUNCATE 的问题

2 解决了异构数据库双向复制的问题。
双向复制的关键点:
1) 在 ORACLE 的 EXTRACT 中加入 ,其中 ggs12为ggs 用户
TRANLOGOPTIONS EXCLUDEUSER ggs12

2) 在INFORMIX 的REPLICAT 中加入, 其中rep为用于复制的用户
TARGETDB kkbi userid rep password rep

3) 在INFORMIX 的 EXTRACT 中加入 , 其中1103 为操作用户rep 的用户ID
TRANLOGOPTIONS EXCLUDEUSERID 1103

如果不在 INFORMIX 端加入上述处理,ORACLE 的数据会再进行一遍复制

3 解决rac 结构复制的问题
1）安装ACFS
2）在共享目录上安装 OGG
3) 可以在每个节点启动相关OGG 进程
4）LINUX 更改 osds_acfslib.pm 脚本相关版本信息
5）LINUX ,AIX,SOLARIS 上可行
6) asm 12版本不需要指定归档日志目录

问题：
AIX,SOLARIS 未测试
HP-UNIX 上RAC 如何处理？

4 本次遇到的问题
1) 2端版本不同 oracel ogg 为 12.2.0.1.1,informix ogg 为 12.1.0.1.1
导致 INFORMIX 端读ORACLE 的 DEF 文件时报 DEF 的版本为5.0,informix 中换成 12.2.0.1.1 后解决

2)informix 端未执行 add trandata kkbi.informix.*
导致 informix 不能 EXTRACT 数据,加入后 ,informix的数据复制到 ORACLE 端

3)informix 未建用于REPLICAT 的用户,使用informix 导致 TRANLOGOPTIONS EXCLUDEUSERID 501 后, informix的数据不能复制到 ORACLE 端. 501为 informix 的用户ID

4) 本次测试双向复制,所以原先将 DELETE ,UPDATE 转化成INSERT 的选项去掉。
去掉的是下面2行
INSERTUPDATES
INSERTDELETES

5) 检查点为 REPLICAT 使用。

6) syscdcv1 为 informix 作为源端时使用,需要创建

7) 双向复制如果表存在主键,则不用加入本文提到的为解决双向复制而添加的选项

/**********************************************************************************/
一单向复制 ORACLE 到INFORMIX
oracle kk12 复制到 INFORMIX kk12
/**********************************************************************************/
1 ORACLE 端上传软件 ,解压,安装ggs (ogg 12版本为图形界面)
/u01/software/fbo_ggs_Linux_x64_shiphome.zip

unzip fbo_ggs_Linux_x64_shiphome.zip

cd /u01/software/fbo_ggs_Linux_x64_shiphome/Disk1
export DISPLAY=192.168.6.101:0.0

选择 oracle goldengate for oracle database 11g
software location :/u01/ggs12

2 ORACLE 数据库端配置
SQL> conn / as sysdba
sql> alter system set RECYCLEBIN =OFF SCOPE=SPFILE;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY,UNIQUE INDEX) COLUMNS;
SQL> alter system switch logfile;
SQL> select supplemental_log_data_min from v$database;
SQL> alter system set enable_goldengate_replication=true;

SQL> ALTER DATABASE FORCE LOGGING;
SQL> ARCHIVE LOG LIST;
SQL> startup force;
SQL> conn / as sysdba
SQL> create tablespace ggs12 datafile size 100m autoextend on;

SQL> CREATE USER ggs12 IDENTIFIED BY ggs12 default tablespace ggs12;
SQL> GRANT EXECUTE ON UTL_FILE TO ggs12;
SQL> @marker_setup.sql

Enter GoldenGate schema name:ggs12

SQL> @ddl_setup.sql

Enter GoldenGate schema name:ggs12

You will be prompted for the mode of installation.
To install or reinstall DDL replication, enter INITIALSETUP
To upgrade DDL replication, enter NORMAL
Enter mode of installation:INITIALSETUP

Using GOLDENGATE as a GoldenGate schema name, INITIALSETUP as a mode of installation.

Working, please wait ...

RECYCLEBIN must be empty.
This installation will purge RECYCLEBIN for all users.
To proceed, enter yes. To stop installation, enter no.

Enter yes or no:yes

SQL> GRANT CREATE TABLE,CREATE SEQUENCE TO ggs12;

SQL> @role_setup.sql

SQL> GRANT GGS_GGSUSER_ROLE TO ggs12;

SQL> GRANT CREATE SESSION, ALTER SESSION to ggs12;
SQL> GRANT RESOURCE,CONNECT to ggs12;
SQL> GRANT SELECT ANY DICTIONARY to ggs12;
SQL> GRANT FLASHBACK ANY TABLE to ggs12;
SQL> GRANT SELECT ANY TABLE to ggs12;
SQL> GRANT INSERT ANY TABLE to ggs12;
SQL> GRANT UPDATE ANY TABLE to ggs12;
SQL> GRANT DELETE ANY TABLE to ggs12;
SQL> GRANT CREATE ANY TABLE to ggs12;
SQL> GRANT DROP ANY TABLE to ggs12;
SQL> GRANT CREATE ANY INDEX to ggs12;
SQL> GRANT DROP ANY INDEX to ggs12;
SQL> GRANT CREATE ANY VIEW to ggs12;
SQL> GRANT DROP ANY VIEW to ggs12;
SQL> GRANT CREATE ANY PROCEDURE to ggs12;
SQL> GRANT DROP ANY PROCEDURE to ggs12;
SQL> GRANT ALTER ANY TABLE to ggs12;
SQL> GRANT ALTER ANY PROCEDURE to ggs12;
SQL> GRANT EXECUTE ON DBMS_FLASHBACK to ggs12;
SQL> GRANT GGS_GGSUSER_ROLE to ggs12;

3 ORACLE 端配置GGSCI
su - oracle
cd /u01/ggs12
ggsci

GGSCI (host-172-16-21-61) 1> create subdirs
GGSCI (host-172-16-21-61) ) 2> edit params mgr
输入:
PORT 7845
DYNAMICPORTLIST 7845-7909
--AUTOSTART ER *
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
LAGREPORTHOURS 1
LAGtomysqlMINUTES 30
LAGCRITICALMINUTES 45

注：
LAGtomysqlMINUTES 30 参数ORACLE 中不合法

GGSCI (host-172-16-21-61) 3>edit param ./GLOBALS
输入:
GGSCHEMA ggs12
CHECKPOINTTABLE ggs12.ggs_checkpoint

GGSCI (host-172-16-21-61) 4> DBLOGIN USERID ggs12@orcl, PASSWORD ggs12
GGSCI (host-172-16-21-61) 5> add checkpointtable ggs12.ggs_checkpoint

4 informix 配置ODBC
a:
# vi $ODBCINI
[ODBC Data Sources]
kk12=IBM INFORMIX ODBC DRIVER
syscdcv1=IBM INFORMIX ODBC DRIVER
[kk12]
Driver=/home/informix/gbase/lib/cli/iclis09b.so
Description=IBM INFORMIX ODBC DRIVER
Database=kk12
LogonID=informix
pwd=informix
Servername=gbaseserver

b:在[ODBC] 下面添加
UNICODE=UTF-8

--测试
/home/informix/gbase/demo/cli/catalog.c
cd /home/informix/gbase/demo/cli
make catalog
cp catalog /home/informix/gbase/

[informix@node1 ggs]$ cd /home/informix/gbase/

[informix@node1 gbase]$ ./catalog kk12

5 创建 defgen.def文件

1)下面在 ORACLE 端数据库机器上执行
su - oracle
cd /u01/ggs12/dirprm
[oracle@host-172-16-21-61 dirprm]$ vi defgenkk12.prm
输入:
defsfile ./dirdef/kk12.def purge
userid ggs12,password ggs12
table kk12.*;

2)下面在 ORACLE 端数据库机器上执行
su - oracle
cd /u01/ggs12
./defgen paramfile ./dirprm/defgenkk12.prm
su - root
cd /u01/ggs12/dirdef/
cp /u01/ggs12/dirdef/kk12.def /home/informix/ggs/dirdef/
chown -R informix:informix /home/informix/ggs/dirdef/
chmod -R 777 /home/informix/ggs/dirdef/

6 ORACLE 端执行下面的配置

su - oracle
cd /u01/ggs12
ggsci

GGSCI (host-172-16-21-61) 1> DBLOGIN USERID ggs12@orcl, PASSWORD ggs12
GGSCI (host-172-16-21-61) 5> add trandata kk12.ggs_checkpoint
/*---------------------------------------------------------------------------------------*/
注: oracle端不执行 add trandata 也能复制
/*---------------------------------------------------------------------------------------*/

GGSCI (host-172-16-21-61) 3> edit param ekk12_1
输入:
extract ekk12_1
GETTRUNCATES
FETCHOPTIONS FETCHPKUPDATECOLS
userid ggs12,password ggs12
exttrail /u01/ggs12/dirdat/k2
table kk12.*;

GGSCI (host-172-16-21-61) 4> add extract ekk12_1,tranlog,begin now
GGSCI (host-172-16-21-61) 5> add exttrail /u01/ggs12/dirdat/k2,extract EKK12_1,megabytes 5
GGSCI (host-172-16-21-61) 6> edit param pkk12_1
输入:
extract pkk12_1
passthru
userid ggs12,password ggs12
rmthost 192.168.6.200,mgrport 7809
rmttrail /home/informix/ggs/dirdat/k2
table kk12.*;

GGSCI (host-172-16-21-61) 7> add extract pkk12_1,exttrailsource ./dirdat/k2
GGSCI (host-172-16-21-61) 8> add rmttrail /home/informix/ggs/dirdat/k2,extract pkk12_1,megabytes 5
GGSCI (host-172-16-21-61) 9> start manager

7 informix 端执行下面的配置

su - informix
mkdir -p /home/informix/ggs
cd /home/informix/ggs
unzip ggs_Linux_x64_Informix_SDK410_64bit.zip
tar -xvf ggs_Linux_x64_Informix_SDK410_64bit.tar

cd /home/informix/ggs
ggsci

GGSCI (mysql as) 1> create subdirs
GGSCI (mysql as) 2> edit params mgr

GGSCI (mysql as ) 1> dblogin sourcedb kk12 userid informix password informix
GGSCI (mysql as)) 5> ADD CHECKPOINTTABLE kk12.informix.ggs_checkpoint
/*---------------------------------------------------------------------------------------*/
--注: ADD CHECKPOINTTABLE 是复制端需要的
/*---------------------------------------------------------------------------------------*/
GGSCI (host-172-16-21-61) 1> create subdirs

GGSCI (mysql as) 2> edit params mgr
输入:
port 7809
DYNAMICPORTLIST 7810-7909
--AUTOSTART ER *
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
LAGREPORTHOURS 1
LAGtomysqlMINUTES 30
LAGCRITICALMINUTES 45

注：
LAGtomysqlMINUTES 30 参数ORACLE 中不合法

GGSCI (mysql as ) 2> edit param rkk12_1
输入:
replicat rkk12_1
sourcedefs /home/informix/ggs/dirdef/kk12.def
handlecollisions
assumetargetdefs
GETTRUNCATES
applynoopupdates
REPERROR DEFAULT,DISCARD
TARGETDB kk12 userid informix password informix
Map kk12.*,target kk12.informix.*,colmap(usedefaults,informatica_row_id=@GETENV('RECORD','ROWID'),informatica_date_time=@GETENV ('GGHEADER', 'COMMITTIMESTAMP'),informatica_flag=@CASE(@GETENV('GGHEADER','OPTYPE'),'DELETE','D','UPDATE','U','PK UPDATE','U','INSERT','I','SQL COMPUPDATE','U',0),informatica_ogg_seq=@COLSTAT(MISSING));

/*---------------------------------------------------------------------------------------*/
--注: 此处进行了表列的映射,用于增加的列的取值等,其中informatica_ogg_seq为自动增1列 ,需要设为 informatica_ogg_seq=@COLSTAT(MISSING),
和 MYSQL 中不同,MYSQL中设置为 informatica_ogg_seq=@COLSTAT(NULL)
/*---------------------------------------------------------------------------------------*/

GGSCI (mysql as ) 7> add replicat rkk12_1,exttrail /home/informix/ggs/dirdat/k2,checkpointtable kk12.informix.ggs_checkpoint

GGSCI (mysql as ) 8> start manager
GGSCI (mysql as ) 9> start rkk12_1

--测试数据
oracle端 :
SQL> create user kk12 identified by kk12;
SQL> grant dba to kk12;
SQL> conn kk12/kk12
SQL> create table t1 (a int,b int);
SQL> create table t2 (a int,b int);
SQL> create table t3 (a int,b int);

--informix 端 :
[informix@node1 dirrpt]$ dbaccess kk12 -
> create table t1 (a int,b int);
> create table t2 (a int,b int);
> create table t3 (a int,b int);

alter table t1 add( informatica_row_id varchar(20), informatica_flag varchar(1), informatica_date_time datetime year to second, informatica_ogg_seq serial);
alter table t2 add( informatica_row_id varchar(20), informatica_flag varchar(1), informatica_date_time datetime year to second, informatica_ogg_seq serial);
alter table t3 add( informatica_row_id varchar(20), informatica_flag varchar(1), informatica_date_time datetime year to second, informatica_ogg_seq serial);

/*******************************************************************************/
二 INFORMIX 复制到 ORACLE
INFORMIX kk12 复制到 ORACLE KK12
/*******************************************************************************/
1 INFORMIX 端
a: 需要建立SYSCDC 数据库,并且需要登录syscdcv1
--创建syscdcv1 数据库
dbaccess kk12 $INFORMIXDIR/etc/syscdcv1.sql

b: extract 指向 SYSCDC 数据库
c: add extract 加 VAM
如 :add extract ekk12_2,VAM,begin now

d: 配置ODBC
[syscdcv1]
Driver=/home/informix/gbase/lib/cli/iclis09b.so
Description=IBM INFORMIX ODBC DRIVER
Database=syscdcv1
LogonID=informix
pwd=informix
Servername=gbaseserver

--测试
[informix@dbrac2 ~]$ isql -v syscdcv1 informix informix
[informix@node1 ggs]$ cd /home/informix/gbase/
[informix@node1 gbase]$ ./catalog syscdcv1

GGSCI (host-172-16-21-61) 3> dblogin sourcedb syscdcv1 userid informix password informix

GGSCI (node1 as informix@infdrv1/d819) 2> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE syscdcv1.informix.ggs_checkpoint
CHECKPOINTTABLE kk12.informix.ggs_checkpoint

GGSCI (node1 as informix@syscdcv1/syscdcv1) 17> ADD CHECKPOINTTABLE syscdcv1.informix.ggs_checkpoint
GGSCI (node1 as informix@syscdcv1/syscdcv1) 3> add trandata kk12.informix.*

/*---------------------------------------------------------------------------------------*/
注:add trandata kk12.informix.* 要以 syscdcv1登录,否则报
/*---------------------------------------------------------------------------------------*/

2017-01-20 21:41:58 ERROR OGG-00551 Database operation failed: SQLExecDirect. ODBC error: SQLSTATE S1000 native database error -674.
[Informix][Informix ODBC Driver][Informix]Routine (cdc_set_fullrowlogging) can not be resolved.

GGSCI (host-172-16-21-61) 3> edit param ekk12_2
输入:
extract ekk12_2
SOURCEDB syscdcv1 userid informix password informix
GETTRUNCATES
FETCHOPTIONS FETCHPKUPDATECOLS
exttrail /home/informix/ggs/dirdat/m2
table kk12.informix.*;

/*---------------------------------------------------------------------------------------*/
注: 此处的 SOURCEDB 要为syscdcv1 否则报下面的错:
2017-01-20 21:37:56 ERROR OGG-10036 The SYSCDC functions required for Oracle GoldenGate capture are not installed in the current data
base.
/*---------------------------------------------------------------------------------------*/

GGSCI (node1 as informix@infdrv3/info) 35> add extract ekk12_2,VAM,begin now
EXTRACT added..

GGSCI (node1 as informix@infdrv3/info) 36> add exttrail /home/informix/ggs/dirdat/m2,extract ekk12_2,megabytes 5
EXTTRAIL added.

GGSCI (host-172-16-21-61) 6> edit param pkk12_2
输入:
extract pkk12_2
passthru
SOURCEDB syscdcv1 userid informix password informix
rmthost 192.168.6.200,mgrport 7845
rmttrail /u01/ggs12/dirdat/m2
table kk12.informix.*;
/*---------------------------------------------------------------------------------------*/
注: 此处的 SOURCEDB 不需要为syscdcv1
/*---------------------------------------------------------------------------------------*/

GGSCI (node1 as informix@infdrv3/info) 39> add extract pkk12_2,exttrailsource /home/informix/ggs/dirdat/m2
EXTRACT added.

GGSCI (node1 as informix@infdrv3/info) 40> add rmttrail /u01/ggs12/dirdat/m2,extract pkk12_2,megabytes 5
RMTTRAIL added.

GGSCI (host-172-16-21-61) 9> start manager

2 INFORMIX 端创建DEF 文件

1)下面在 informix 端数据库机器上执行
su - informix
cd /home/informix/ggs/dirprm
[informix@node1 dirprm]$ vi informixkk12.prm
输入:
defsfile ./dirdef/informixkk12.def purge
SOURCEDB kk12 userid informix password informix
table kk12.informix.*;

2)下面在 ORACLE 端数据库机器上执行
su - informix
cd /home/informix/ggs
./defgen paramfile ./dirprm/informixkk12.prm
su - root
cd /home/informix/ggs/dirdef/
cp /home/informix/ggs/dirdef/informixkk12.def /u01/ggs12/dirdef/
chown -R oracle:oinstall /u01/ggs12/dirdef/
chmod -R 777 /u01/ggs12/dirdef/
chmod -R 777 /u01/ggs12

3 oracle 端
GGSCI (mysql as ) 2> edit param rkk12_2
输入:
replicat rkk12_2
userid ggs12@orcl,password ggs12
sourcedefs /u01/ggs12/dirdef/kk12.def
handlecollisions
ASSUMETARGETDEFS
applynoopupdates
REPERROR DEFAULT,DISCARD
DISCARDFILE ./dirrpt/rkk12_2.dsc,PURGE
MAP kk12.informix.*, TARGET kk12.* ;

GGSCI (h70) 3> add replicat rkk12_2,checkpointtable ggs12.ggs_checkpoint,exttrail ./dirdat/m2
GGSCI (h70) 12> start rkk12_2

-- 如果手动启动　replicat,需要replicat 中加入 SHOWSYNTAX 选项,执行下面的命令

replicat paramfile dirprm/rkk12_2.prm

--测试数据
oracle端 :
SQL> create user kk12 identified by kk12;
SQL> grant dba to kk12;
SQL> conn kk12/kk12
SQL> create table t11 (a int,b int);
SQL> create table t12 (a int,b int);
SQL> create table t13 (a int,b int);

--informix 端 :
[informix@node1 dirrpt]$ dbaccess kk12 -
create table t11 (a int,b int);
create table t12 (a int,b int);
create table t13 (a int,b int);

/*******************************************************************************/
三 INFORMIX 到 ORACLE 双向复制
oracle kkbi 到 INFORMIX kkbi
/*******************************************************************************/
1 准备表结构
oracle :
create user kkbi identified by kkbi;
grant dba to kkbi;
conn kkbi/kkbi
create table t1(a int, b int);
create table t2(a int, b int);
create table t3(a int, b int);

informix:
[informix@node1 ~]$ dbaccess kkbi -

Database selected.

> create table t1(a int, b int);
create table t2(a int, b int);
create table t3(a int, b int);

2 informix 配置ODBC
kkbi=IBM INFORMIX ODBC DRIVER

[kkbi]
Driver=/home/informix/gbase/lib/cli/iclis09b.so
Description=IBM INFORMIX ODBC DRIVER
Database=kkbi
LogonID=informix
pwd=informix
Servername=gbaseserver

--ODBC 测试
[informix@node1 ~]$ cd gbase/
[informix@node1 gbase]$ ./catalog kkbi

3 oracle 端准备DEF 文件
su - oracle
cd /u01/ggs12/dirprm
[oracle@host-172-16-21-61 dirprm]$ vi defgenkkbi.prm
输入:
defsfile ./dirdef/kkbi.def purge
userid ggs12,password ggs12
table kkbi.*;

su - oracle
cd /u01/ggs12
./defgen paramfile ./dirprm/defgenkkbi.prm
su - root
cd /u01/ggs12/dirdef/
cp /u01/ggs12/dirdef/kkbi.def /home/informix/ggs/dirdef/
chown -R informix:informix /home/informix/ggs/dirdef/
chmod -R 777 /home/informix/ggs/dirdef/

4 informix 端准备DEF 文件

su - informix
cd /home/informix/ggs/dirprm
[informix@node1 dirprm]$ vi informixkkbi.prm
输入:
defsfile ./dirdef/informixkkbi.def purge
SOURCEDB kkbi userid informix password informix
table kkbi.informix.*;

2)下面在 ORACLE 端数据库机器上执行
su - informix
cd /home/informix/ggs
./defgen paramfile ./dirprm/informixkkbi.prm
su - root
cd /home/informix/ggs/dirdef/
cp /home/informix/ggs/dirdef/informixkkbi.def /u01/ggs12/dirdef/
chown -R oracle:oinstall /u01/ggs12/dirdef/
chmod -R 777 /u01/ggs12/dirdef/
chmod -R 777 /u01/ggs12
chmod -R 777 /home/informix/ggs/dirdat

5 ORACLE 端配置EXTRACE,DUMP
GGSCI (host-172-16-21-61) 1> DBLOGIN USERID ggs12@orcl, PASSWORD ggs12

GGSCI (host-172-16-21-61) 3> edit param ekkbi_1
输入:
extract ekkbi_1
GETTRUNCATES
FETCHOPTIONS FETCHPKUPDATECOLS
userid ggs12,password ggs12
TRANLOGOPTIONS EXCLUDEUSER ggs12
exttrail /u01/ggs12/dirdat/b1
table kkbi.*;

/*---------------------------------------------------------------------------------------*/
注:因为是双向复制 extract 加上
TRANLOGOPTIONS EXCLUDEUSER ggs12
/*---------------------------------------------------------------------------------------*/

GGSCI (host-172-16-21-61) 4> add extract ekkbi_1,tranlog,begin now
GGSCI (host-172-16-21-61) 5> add exttrail /u01/ggs12/dirdat/b1,extract Ekkbi_1,megabytes 5
GGSCI (host-172-16-21-61) 6> edit param pkkbi_1
输入:
extract pkkbi_1
passthru
userid ggs12,password ggs12
rmthost 192.168.6.200,mgrport 7809
rmttrail /home/informix/ggs/dirdat/b1
table kkbi.*;

GGSCI (host-172-16-21-61) 7> add extract pkkbi_1,exttrailsource ./dirdat/b1
GGSCI (host-172-16-21-61) 8> add rmttrail /home/informix/ggs/dirdat/b1,extract pkkbi_1,megabytes 5
GGSCI (host-172-16-21-61) 9> start manager

6 INFORMIX 端配置REPLICAT
GGSCI (mysql as ) 1> dblogin sourcedb kkbi userid informix password informix
GGSCI (mysql as)) 5> ADD CHECKPOINTTABLE kkbi.informix.ggs_checkpoint
GGSCI (node1 as informix@infdrv1/d819) 2> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE kkbi.informix.ggs_checkpoint

GGSCI (mysql as ) 2> edit param rkkbi_1
输入:
replicat rkkbi_1
sourcedefs /home/informix/ggs/dirdef/kkbi.def
handlecollisions
assumetargetdefs
GETTRUNCATES
applynoopupdates
REPERROR DEFAULT,DISCARD
TARGETDB kkbi userid rep password rep
Map kkbi.*,target kkbi.informix.*;

/*---------------------------------------------------------------------------------------*/
注:
--因为是双向复制,此处的用户用另一个复制用户 rep
TARGETDB kkbi userid rep password rep

其通过操作系统创建:
groupadd rep
useradd -g informix -d /home/rep -s /bin/bash -m rep
passwd rep

[informix@node1 home]$ chmod g+rwx informix
[informix@node1 dbs]$ dbaccess kkbi -
> GRANT DBA TO rep;
/*---------------------------------------------------------------------------------------*/

GGSCI (mysql as ) 7> add replicat rkkbi_1,exttrail /home/informix/ggs/dirdat/b1,checkpointtable kkbi.informix.ggs_checkpoint

GGSCI (mysql as ) 8> start manager
GGSCI (mysql as ) 9> start rkkbi_1

7 INFORMIX 端配置EXTRACE,DUMP
GGSCI (host-172-16-21-61) 3> dblogin sourcedb syscdcv1 userid informix password informix
GGSCI (node1 as informix@syscdcv1/syscdcv1) 3> add trandata kkbi.informix.*

/*---------------------------------------------------------------------------------------*/
注: 不执行 add trandata INFORMIX 不能 EXTRACT 数据
[informix@node1 ~]$ id rep --此用户为上面创建的用于复制的用户其ID 为TRANLOGOPTIONS EXCLUDEUSERID 后的 ID
uid=1103(rep) gid=1201(informix) groups=1201(informix)
/*---------------------------------------------------------------------------------------*/

GGSCI (host-172-16-21-61) 3> edit param ekkbi_2
输入:
extract ekkbi_2
SOURCEDB syscdcv1 userid informix password informix
GETTRUNCATES
FETCHOPTIONS FETCHPKUPDATECOLS
TRANLOGOPTIONS EXCLUDEUSERID 1103
exttrail /home/informix/ggs/dirdat/i2
table kkbi.informix.*;

/*---------------------------------------------------------------------------------------*/
--注: 因为是双向复制 ,此处的ID 1103 为 INFORMIX 端的复制用户rep
TRANLOGOPTIONS EXCLUDEUSERID 1103
/*---------------------------------------------------------------------------------------*/

GGSCI (node1 as informix@infdrv3/info) 35> add extract ekkbi_2,VAM,begin now
EXTRACT added..

GGSCI (node1 as informix@infdrv3/info) 36> add exttrail /home/informix/ggs/dirdat/i2,extract ekkbi_2,megabytes 5
EXTTRAIL added.

GGSCI (host-172-16-21-61) 6> edit param pkkbi_2
输入:
extract pkkbi_2
passthru
SOURCEDB syscdcv1 userid informix password informix
rmthost 192.168.6.200,mgrport 7845
rmttrail /u01/ggs12/dirdat/i2
table kkbi.informix.*;
/*---------------------------------------------------------------------------------------*/
注: 此处的 SOURCEDB 不需要为syscdcv1
/*---------------------------------------------------------------------------------------*/

GGSCI (node1 as informix@infdrv3/info) 39> add extract pkkbi_2,exttrailsource /home/informix/ggs/dirdat/i2
EXTRACT added.

GGSCI (node1 as informix@infdrv3/info) 40> add rmttrail /u01/ggs12/dirdat/i2,extract pkkbi_2,megabytes 5
RMTTRAIL added.

8 ORACLE 端配置REPLICAT
GGSCI (mysql as ) 2> edit param rkkbi_2
输入:
replicat rkkbi_2
userid ggs12@orcl,password ggs12
sourcedefs /u01/ggs12/dirdef/informixkkbi.def
handlecollisions
ASSUMETARGETDEFS
applynoopupdates
REPERROR DEFAULT,DISCARD
DISCARDFILE ./dirrpt/rkkbi_2.dsc,PURGE
MAP kkbi.informix.t1, TARGET kkbi.t1 ;
MAP kkbi.informix.t2, TARGET kkbi.t2 ;
MAP kkbi.informix.t3, TARGET kkbi.t3 ;

GGSCI (h70) 3> add replicat rkkbi_2,checkpointtable ggs12.ggs_checkpoint,exttrail ./dirdat/i2
GGSCI (h70) 12> start rkkbi_2

--因为是双向复制,所以2端需要加入add tracetable
GGSCI (f80) 72> add tracetable
No trace table name is specified for ADD TRACETABLE, using default (GGS_TRACE)...

Successfully created trace table GGS_TRACE.

--测试

-- oracle
insert into t2 values(1,91111);
insert into t2 values(1,91112);
commit;

--infromix:

insert into t2 values(2,911111);
insert into t2 values(2,911112);

truncate table t1;
truncate table t2;
truncate table t3;

select count(1) from t1;
select count(1) from t2;
select count(1) from t3;

/*******************************************************************************/
四 INFORMIX 到 ORACLE rac(11.2) 双向复制
oracle kkrac 到 INFORMIX kkrac
对RAC 数据库需要将OGG 安装在共享文件系统上，11.2 上使用ACFS(redhat 5 )
10 使用OCFS2

可在任意节点启动OGG 进程

/*******************************************************************************/
/*--------------------------------------------------------------------------*/
一 ORACLE rac 端创建ACFS 文件系统作为 OGG 的安装目录
su - grid
1 安装ACFS(在有的系统上ACFS 不能使用，ASMCA 中的 VOLUME,ACFS为灰色，此时需要安装ACFS)
运行下面命令，如果显示 ACFS-9459: ADVM/ACFS is not supported on this OS version: 'centos-release-6-8.el6.centos.12.3.x86_64'
则需要下面相应配置
[root@dbrac1 bin]# pwd
/u01/app/11.2.0/grid/bin

[root@dbrac1 bin]# ./acfsload start
ACFS-9459: ADVM/ACFS is not supported on this OS version: 'centos-release-6-8.el6.centos.12.3.x86_64'

1)修改osds_acfslib.pm脚本代码,rac2节点做同样的修改
[root@dbrac1 lib]# cd /u01/app/11.2.0/grid/lib
[root@dbrac1 lib]# cp -p osds_acfslib.pm osds_acfslib.pm.bak

更改下面的代码：2个地方
[root@rac1 lib]# vi osds_acfslib.pm
if ((defined($release)) && # Redhat or OEL if defined
(($release =~ /^redhat-release/) || # straight RH
($release =~ /^enterprise-release/) || # Oracle Enterprise Linux
($release =~ /^oraclelinux-release/))) # Oracle Linux
{

将上面的代码片段修改如下:
if ((defined($release)) && # Redhat or OEL if defined
(($release =~ /^redhat-release/) || # straight RH
($release =~ /^enterprise-release/) || # Oracle Enterprise Linux
($release =~ /^centos-release/) || # CentOS hack
($release =~ /^oraclelinux-release/))) # Oracle Linux
{

[root@dbrac1 lib]# scp osds_acfslib.pm dbrac2:/u01/app/11.2.0/grid/lib/

2)安装acfs，配置acfs和advm模块启动自动加载，rac2节点做同样的配置
[root@dbrac1 lib]# /u01/app/11.2.0/grid/bin/acfsroot install

ACFS-9300: ADVM/ACFS distribution files found.
ACFS-9307: Installing requested ADVM/ACFS software.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9321: Creating udev for ADVM/ACFS.
ACFS-9323: Creating module dependencies - this may take some time.
ACFS-9154: Loading 'oracleoks.ko' driver.
WARNING: Deprecated config file /etc/modprobe.conf, all config files belong into /etc/modprobe.d/.
ACFS-9154: Loading 'oracleadvm.ko' driver.
WARNING: Deprecated config file /etc/modprobe.conf, all config files belong into /etc/modprobe.d/.
ACFS-9154: Loading 'oracleacfs.ko' driver.
WARNING: Deprecated config file /etc/modprobe.conf, all config files belong into /etc/modprobe.d/.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9156: Detecting control device '/dev/asm/.asm_ctl_spec'.
ACFS-9156: Detecting control device '/dev/ofsctl'.
ACFS-9309: ADVM/ACFS installation correctness verified.

[root@dbrac1 lib]# /u01/app/11.2.0/grid/bin/acfsload start -s

[root@dbrac1 lib]# lsmod |grep oracle
oracleacfs 1990406 0
oracleadvm 250040 0
oracleoks 427672 2 oracleacfs,oracleadvm

[root@dbrac1 lib]# vi /etc/init.d/acfsload
#!/bin/sh
# chkconfig: 2345 30 21
# description: Load Oracle ASM volume driver on system startup
ORACLE_HOME=/u01/app/11.2.0/grid
export ORACLE_HOME
$ORACLE_HOME/bin/acfsload start -s

[root@rac1 ~]# chmod 755 /etc/init.d/acfsload
[root@rac1 ~]# chkconfig --add acfsload
[root@rac1 ~]# chkconfig --list | grep acfsload

3) 添加ora.registry.acfs到ocr配置中
[root@rac1 ~]# /u01/app/11.2.0/grid/bin/crsctl add type ora.registry.acfs.type -basetype ora.local_resource.type -file /u01/app/11.2.0/grid/crs/template/registry.acfs.type

[root@rac1 ~]# /u01/app/11.2.0/grid/bin/crsctl add resource ora.registry.acfs -attr ACL=\'owner:root:rwx,pgrp:oinstall:r-x,other::r--\' -type ora.registry.acfs.type -f

[root@rac1 ~]# su - grid -c crs_stat | grep acfs
NAME=ora.registry.acfs
TYPE=ora.registry.acfs.type

[root@rac2 ~]# su - grid -c crs_stat | grep acfs
NAME=ora.registry.acfs
TYPE=ora.registry.acfs.type
[root@rac1 ~]# /sbin/acfsutil registry

4)创建卷并格式化成acfs集群文件系统并挂载
a:使用asmca图形化工具，在asm磁盘组中创建volume并格式化成ACFS文件系统

su - grid
--创建共享目录
mkdir /u01/app/grid/acfsmounts/acfs_acfsvol

export DISPLAY=192.168.6.101:0.0
asmca/创建磁盘组/创建VOL/创建ACFS 文件系统

ASM Cluster File System created on /dev/asm/acfsvol-140 successfully.
Registering Mount Point </dev/asm/acfsvol-140, /u01/app/grid/acfsmounts/acfs_acfsvol> failed with the following message:
acfsutil registry: ACFS-03168: Internal error: CrsResUpdate: clscrs_register_resource2 219
acfsutil registry: ACFS-03168: Internal error: CrsResUpdate 219
acfsutil registry: ACFS-03168: Internal error: ora.registry.acfs 219
acfsutil registry: ACFS-03166: Unable to add a STOP_DEPENDENCY to the ACFS registry for device /dev/asm/acfsvol-140.
acfsutil registry: ACFS-03111: unable to add ACFS mount /u01/app/grid/acfsmounts/acfs_acfsvol within Oracle Registry

You can mount the file system manually for use.

b:执行命令：
/sbin/mkfs -t acfs /dev/asm/acfsvol-140
/sbin/acfsutil registry -a -f /dev/asm/acfsvol-140 /u01/app/grid/acfsmounts/acfs_acfsvol

ASM Cluster File System creation on /dev/asm/acfsvol-140 failed with the following message:

c: MOUNT ACFS 文件系统
[root@dbrac1 ~]# /sbin/mount.acfs -o all

d: 显示相关信息
[root@dbrac1 ~]# /sbin/acfsutil info fs
/u01/app/grid/acfsmounts/acfs_acfsvol
ACFS Version: 11.2.0.4.0
flags: MountPoint,Available
mount time: Wed Feb 1 16:26:29 2017
volumes: 1
total size: 10737418240
total free: 10636148736
primary volume: /dev/asm/acfsvol-140
label:
flags: Primary,Available,ADVM
on-disk version: 39.0
allocation unit: 4096
major, minor: 252, 71681
size: 10737418240
free: 10636148736
ADVM diskgroup ACFS
ADVM resize increment: 33554432
ADVM redundancy: unprotected
ADVM stripe columns: 4
ADVM stripe width: 131072
number of snapshots: 0
snapshot space usage: 0
replication status: DISABLE

root@dbrac2 ~]# cd /u01/app/11.2.0/grid/bin/
[root@dbrac2 bin]# ./crs_stat -t -v ora.registry.acfs
Name Type R/RA F/FT Target State Host
----------------------------------------------------------------------
ora....ry.acfs ora....fs.type 0/5 0/ ONLINE ONLINE dbrac1

[root@dbrac2 bin]# ./crs_stat -t -v ora.ACFS.dg
Name Type R/RA F/FT Target State Host
----------------------------------------------------------------------
ora.ACFS.dg ora....up.type 0/5 0/ ONLINE ONLINE dbrac

--测试
[root@dbrac1 ~]# cd /u01/app/grid/acfsmounts/acfs_acfsvol
[root@dbrac1 ~]# touch 1.txt

e:或者用下面的方法

[grid@rac1 ~]$ sqlplus / as sysasm
SQL> alter diskgroup acfs add volume acfsvol2 size 10G;

[root@rac1 ~]# mkdir /u01/ggs
[root@rac2 ~]# mkdir /u01/ggs
[root@rac1 ~]# /sbin/mkfs -t acfs -n ACFSVOL2 /dev/asm/acfsvol2-140

mkfs.acfs: version = 11.2.0.4.0
mkfs.acfs: on-disk version = 39.0
mkfs.acfs: volume = /dev/asm/acfsvol2-140
mkfs.acfs: volume size = 10737418240
mkfs.acfs: Format complete.

[root@rac1 ~]# /sbin/acfsutil registry -a -f /dev/asm/acfsvol2-140 /u01/ggs/

[root@rac1 ~]# mount.acfs -o all
[root@dbrac1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda5 93G 25G 63G 29% /
tmpfs 939M 402M 537M 43% /dev/shm
/dev/sda1 190M 39M 141M 22% /boot
/dev/sda3 2.0G 8.3M 1.8G 1% /tmp
/dev/asm/acfsvol-140 10G 161M 9.9G 2% /u01/app/grid/acfsmounts/acfs_acfsvol
/dev/asm/acfsvol2-140
10G 97M 10G 1% /u01/ggs
[root@rac1 ~]# /sbin/acfsutil info fs

注：要在2端 MOUNT 磁盘组

--删除掉一个ACFS 文件系统
[root@dbrac1 ~]# umount /u01/app/grid/acfsmounts/acfs_acfsvol
[root@rac1 ~]# /sbin/acfsutil registry -d /u01/app/grid/acfsmounts/acfs_acfsvol
[root@dbrac2 ~]# umount /u01/app/grid/acfsmounts/acfs_acfsvol

二安装OGG
[root@dbrac1 u01]# chmod 777 /u01/ggs

1 准备表结构
oracle :
create user kkrac identified by kkrac;
grant dba to kkrac;
conn kkrac/kkrac
create table t1(a int, b int);
create table t2(a int, b int);
create table t3(a int, b int);

informix:
[informix@node1 ~]$ dbaccess kkrac -

Database selected.

> create table t1(a int, b int);
create table t2(a int, b int);
create table t3(a int, b int);

2 informix 配置ODBC
kkrac=IBM INFORMIX ODBC DRIVER

[kkrac]
Driver=/home/informix/gbase/lib/cli/iclis09b.so
Description=IBM INFORMIX ODBC DRIVER
Database=kkrac
LogonID=informix
pwd=informix
Servername=gbaseserver

--ODBC 测试
[informix@node1 ~]$ cd gbase/
[informix@node1 gbase]$ ./catalog kkrac

3 oracle 端准备DEF 文件
su - oracle
cd /u01/ggs/dirprm
[oracle@host-172-16-21-61 dirprm]$ vi defgenkkrac.prm
输入:
defsfile ./dirdef/kkrac.def purge
userid ggs12,password ggs12
table kkrac.*;

su - oracle
cd /u01/ggs
./defgen paramfile ./dirprm/defgenkkrac.prm
su - root
cd /u01/ggs/dirdef/
scp /u01/ggs/dirdef/kkrac.def dbrac2:/home/informix/ggs/dirdef/kkrac.def
chown -R informix:informix /home/informix/ggs/dirdef/
chmod -R 777 /home/informix/ggs/dirdef/

4 informix 端准备DEF 文件

su - informix
cd /home/informix/ggs/dirprm
[informix@node1 dirprm]$ vi informixkkrac.prm
输入:
defsfile ./dirdef/informixkkrac.def purge
SOURCEDB kkrac userid informix password informix
table kkrac.informix.*;

2)下面在 ORACLE 端数据库机器上执行
su - informix
cd /home/informix/ggs
./defgen paramfile ./dirprm/informixkkrac.prm
su - root
cd /home/informix/ggs/dirdef/
scp /home/informix/ggs/dirdef/informixkkrac.def dbrac1:/u01/ggs/dirdef/
chown -R oracle:oinstall /u01/ggs/dirdef/
chmod -R 777 /u01/ggs/dirdef/
chmod -R 777 /u01/ggs
chmod -R 777 /home/informix/ggs/dirdat

5 对于RAC 的数据库,增加 listener.ora和 tnsnames.ora
1) su - grid
sqlplus / as sysasm
alter user sys identified by oracle;

或者
cd $ORACLE_HOME/dbs
orapwd file=orapw+ASM2 password=oracle
orapwd file=orapw+ASM1 password=oracle
orapwd file=orapw+ASM password=oracle

2) listener.ora
su - grid
[grid@dbrac1 admin]$ echo $ORACLE_HOME
/u01/app/11.2.0/grid

cd $ORACLE_HOME/network/admin

vi listener.ora
--------------------------------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = +ASM)
(ORACLE_HOME =/u01/app/11.2.0/grid)
(SID_NAME = +ASM1)
)
)

[grid@dbrac2 admin]$ SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = +ASM)
(ORACLE_HOME =/u01/app/11.2.0/grid)
(SID_NAME = +ASM2)
)
)

3) tnsnames.ora
su - oracle
[oracle@dbrac1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1
cd $ORACLE_HOME
cd network/admin
vi tnsnames.ora
ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.201)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
(SID_NAME= +ASM1)
)
)

[oracle@dbrac2 ~]$ echo $ORACLE_HOME
vi tnsnames.ora
ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.203)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
(SID_NAME= +ASM2)
)
)

6 ORACLE 端配置EXTRACE,DUMP
GGSCI (host-172-16-21-61) 1> DBLOGIN USERID ggs12@orcl, PASSWORD ggs12

GGSCI (host-172-16-21-61) 3> edit param ekkrac_1
输入:
extract ekkrac_1
GETTRUNCATES
FETCHOPTIONS FETCHPKUPDATECOLS
userid ggs12,password ggs12
TRANLOGOPTIONS ASMUSER sys@asm, ASMPASSWORD oracle
TRANLOGOPTIONS EXCLUDEUSER ggs12
exttrail /u01/ggs/dirdat/b1
table kkrac.*;

注：
对于RAC extract 中增加
TRANLOGOPTIONS ASMUSER sys@asm, ASMPASSWORD oracle

/*---------------------------------------------------------------------------------------*/
注:因为是双向复制 extract 加上
TRANLOGOPTIONS EXCLUDEUSER ggs12
/*---------------------------------------------------------------------------------------*/

注：
对于RAC 增加 threads 2

GGSCI (host-172-16-21-61) 4> add extract ekkrac_1,tranlog,threads 2,begin now
GGSCI (host-172-16-21-61) 5> add exttrail /u01/ggs/dirdat/b1,extract Ekkrac_1,megabytes 5
GGSCI (host-172-16-21-61) 6> edit param pkkrac_1
输入:
extract pkkrac_1
passthru
userid ggs12,password ggs12
rmthost 192.168.6.203,mgrport 7809
rmttrail /home/informix/ggs/dirdat/b1
table kkrac.*;

GGSCI (host-172-16-21-61) 7> add extract pkkrac_1,exttrailsource ./dirdat/b1
GGSCI (host-172-16-21-61) 8> add rmttrail /home/informix/ggs/dirdat/b1,extract pkkrac_1,megabytes 5
GGSCI (host-172-16-21-61) 9> start manager

6 INFORMIX 端配置REPLICAT
GGSCI (mysql as ) 1> dblogin sourcedb kkrac userid informix password informix
GGSCI (mysql as)) 5> ADD CHECKPOINTTABLE kkrac.informix.ggs_checkpoint
GGSCI (node1 as informix@infdrv1/d819) 2> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE kkrac.informix.ggs_checkpoint

GGSCI (mysql as ) 2> edit param rkkrac_1
输入:
replicat rkkrac_1
sourcedefs /home/informix/ggs/dirdef/kkrac.def
handlecollisions
assumetargetdefs
GETTRUNCATES
applynoopupdates
REPERROR DEFAULT,DISCARD
TARGETDB kkrac userid rep password rep
Map kkrac.*,target kkrac.informix.*;

/*---------------------------------------------------------------------------------------*/
注:
--因为是双向复制,此处的用户用另一个复制用户 rep
TARGETDB kkrac userid rep password rep

其通过操作系统创建:
groupadd rep
useradd -g informix -d /home/rep -s /bin/bash -m rep
passwd rep

[informix@node1 home]$ chmod g+rwx informix
[informix@node1 dbs]$ dbaccess kkrac -
> GRANT DBA TO rep;
/*---------------------------------------------------------------------------------------*/

GGSCI (mysql as ) 7> add replicat rkkrac_1,exttrail /home/informix/ggs/dirdat/b1,checkpointtable kkrac.informix.ggs_checkpoint

GGSCI (mysql as ) 8> start manager
GGSCI (mysql as ) 9> start rkkrac_1

7 INFORMIX 端配置EXTRACE,DUMP
GGSCI (host-172-16-21-61) 3> dblogin sourcedb syscdcv1 userid informix password informix
GGSCI (node1 as informix@syscdcv1/syscdcv1) 3> add trandata kkrac.informix.*

/*---------------------------------------------------------------------------------------*/
注: 不执行 add trandata INFORMIX 不能 EXTRACT 数据
[informix@node1 ~]$ id rep --此用户为上面创建的用于复制的用户其ID 为TRANLOGOPTIONS EXCLUDEUSERID 后的 ID

[oracle@dbrac2 dirrpt]$ id rep
uid=1103(rep) gid=1201(informix) groups=1201(informix)
/*---------------------------------------------------------------------------------------*/

GGSCI (host-172-16-21-61) 3> edit param ekkrac_2
输入:
extract ekkrac_2
SOURCEDB syscdcv1 userid informix password informix
GETTRUNCATES
FETCHOPTIONS FETCHPKUPDATECOLS
TRANLOGOPTIONS EXCLUDEUSERID 1103
exttrail /home/informix/ggs/dirdat/i2
table kkrac.informix.*;

/*---------------------------------------------------------------------------------------*/
--注: 因为是双向复制 ,此处的ID 1103 为 INFORMIX 端的复制用户rep
TRANLOGOPTIONS EXCLUDEUSERID 1103
/*---------------------------------------------------------------------------------------*/

GGSCI (node1 as informix@infdrv3/info) 35> add extract ekkrac_2,VAM,begin now
EXTRACT added..

GGSCI (node1 as informix@infdrv3/info) 36> add exttrail /home/informix/ggs/dirdat/i2,extract ekkrac_2,megabytes 5
EXTTRAIL added.

GGSCI (host-172-16-21-61) 6> edit param pkkrac_2
输入:
extract pkkrac_2
passthru
SOURCEDB syscdcv1 userid informix password informix
rmthost 192.168.6.203,mgrport 7845
rmttrail /u01/ggs/dirdat/i2
table kkrac.informix.*;
/*---------------------------------------------------------------------------------------*/
注: 此处的 SOURCEDB 不需要为syscdcv1
/*---------------------------------------------------------------------------------------*/

GGSCI (node1 as informix@infdrv3/info) 39> add extract pkkrac_2,exttrailsource /home/informix/ggs/dirdat/i2
EXTRACT added.

GGSCI (node1 as informix@infdrv3/info) 40> add rmttrail /u01/ggs/dirdat/i2,extract pkkrac_2,megabytes 5
RMTTRAIL added.

8 ORACLE 端配置REPLICAT
GGSCI (host-172-16-21-61) 1> DBLOGIN USERID ggs12@orcl, PASSWORD ggs12

GGSCI (mysql as ) 2> edit param rkkrac_2
输入:
replicat rkkrac_2
userid ggs12@orcl,password ggs12
sourcedefs /u01/ggs/dirdef/informixkkrac.def
handlecollisions
ASSUMETARGETDEFS
applynoopupdates
REPERROR DEFAULT,DISCARD
DISCARDFILE ./dirrpt/rkkrac_2.dsc,PURGE
MAP kkrac.informix.t1, TARGET kkrac.t1 ;
MAP kkrac.informix.t2, TARGET kkrac.t2 ;
MAP kkrac.informix.t3, TARGET kkrac.t3 ;

GGSCI (h70) 3> add replicat rkkrac_2,checkpointtable ggs12.ggs_checkpoint,exttrail ./dirdat/i2
GGSCI (h70) 12> start rkkrac_2

--因为是双向复制,所以2端需要加入add tracetable
GGSCI (f80) 72> add tracetable
No trace table name is specified for ADD TRACETABLE, using default (GGS_TRACE)...

Successfully created trace table GGS_TRACE.

--测试

-- oracle
insert into t2 values(1,91111);
insert into t2 values(1,91112);
commit;

--infromix:

insert into t2 values(2,911111);
insert into t2 values(2,911112);

truncate table t1;
truncate table t2;
truncate table t3;

select count(1) from t1;
select count(1) from t2;
select count(1) from t3;

/*******************************************************************************/
五 INFORMIX 到 INFORMIX 双向复制
INFORMIX kkkk 到 INFORMIX kkkk
/*******************************************************************************/

[informix@dbrac1 dirdat]$ dbaccess kkkk /home/informix/gbase/etc/syscdcv1.sql

[informix@dbrac1 dirrpt]$ echo $ODBCINI
/home/informix/gbase/etc/odbc.ini

[informix@dbrac1 dirrpt]$ dbaccess kkkk -
create table t11 (a int,b int);
create table t12 (a int,b int);
create table t13 (a int,b int);

[root@dbrac2 home]# id rep
uid=1103(rep) gid=1201(informix) groups=1201(informix)

dbaccess kkkk -
grant dba to rep;
--dbrac2:
GGSCI (mysql as) 2> edit params mgr
输入:
port 7809
DYNAMICPORTLIST 7810-7909
--AUTOSTART ER *
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
LAGREPORTHOURS 1
LAGCRITICALMINUTES 45

GGSCI (host-172-16-21-61) 3> edit param ekkkk_1
输入:
extract ekkkk_1
SOURCEDB syscdcv1 userid informix password informix
GETTRUNCATES
FETCHOPTIONS FETCHPKUPDATECOLS
TRANLOGOPTIONS EXCLUDEUSERID 1103
exttrail /home/informix/ggs/dirdat/k1
table kkkk.informix.*;

GGSCI (node1 as informix@infdrv3/info) 35> dblogin sourcedb syscdcv1 userid informix password informix
GGSCI (node1 as informix@infdrv1/d819) 2> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE syscdcv1.informix.ggs_checkpoint
CHECKPOINTTABLE kkkk.informix.ggs_checkpoint

GGSCI (node1 as informix@syscdcv1/syscdcv1) 17> ADD CHECKPOINTTABLE syscdcv1.informix.ggs_checkpoint

GGSCI (node1 as informix@syscdcv1/syscdcv1) 3> add trandata kkkk.informix.*

GGSCI (node1 as informix@infdrv3/info) 35> add extract ekkkk_1,VAM,begin now
EXTRACT added..

GGSCI (node1 as informix@infdrv3/info) 36> add exttrail /home/informix/ggs/dirdat/k1,extract ekkkk_1,megabytes 5
EXTTRAIL added.

GGSCI (host-172-16-21-61) 6> edit param pkkkk_1
输入:
extract pkkkk_1
passthru
SOURCEDB syscdcv1 userid informix password informix
rmthost 192.168.6.201,mgrport 7846
rmttrail /home/informix/ggs/dirdat/k1
table kkkk.informix.*;

GGSCI (node1 as informix@infdrv3/info) 39> add extract pkkkk_1,exttrailsource /home/informix/ggs/dirdat/k1
EXTRACT added.

GGSCI (node1 as informix@infdrv3/info) 40> add rmttrail /home/informix/ggs/dirdat/k1,extract pkkkk_1,megabytes 5
RMTTRAIL added.

-------dbrac2:
GGSCI (mysql as) 2> edit params mgr
输入:
port 7846
DYNAMICPORTLIST 7810-7909
--AUTOSTART ER *
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
LAGREPORTHOURS 1
LAGtomysqlMINUTES 30
LAGCRITICALMINUTES 45

GGSCI (mysql as ) 1> dblogin sourcedb kkkk userid informix password informix
GGSCI (mysql as)) 5> ADD CHECKPOINTTABLE kkkk.informix.ggs_checkpoint

GGSCI (mysql as ) 2> edit param rkkkk_1
输入:
replicat rkkkk_1
handlecollisions
assumetargetdefs
GETTRUNCATES
applynoopupdates
REPERROR DEFAULT,DISCARD
TARGETDB kkkk userid rep password rep
Map kkkk.informix.*,target kkkk.informix.*;

GGSCI (mysql as ) 7> add replicat rkkkk_1,exttrail /home/informix/ggs/dirdat/k1,checkpointtable kkkk.informix.ggs_checkpoint

GGSCI (mysql as ) 8> start manager
GGSCI (mysql as ) 9> start rkkkk_1

--加入 SHOWSYNTAX

replicat paramfile dirprm/rkkkk_1.prm

注：
在12.2 版本中 TRUNCATE 有效，但是在 replicat 中是看不到 TRUNCATE 语句的。
例如：源端
> insert into t11 values(13,109);

1 row(s) inserted.

> insert into t11 values(14,109);

1 row(s) inserted.

> insert into t11 values(15,109);

1 row(s) inserted.

> truncate table t11;

Table truncated.

> insert into t11 values(16,109);

1 row(s) inserted.

目标端：
INSERT INTO kkkk:informix.t11 (a,b) VALUES (13,109)
Statement length: 51
(S)top display, (K)eep displaying (default):

INSERT INTO kkkk:informix.t11 (a,b) VALUES (14,109)
Statement length: 51
(S)top display, (K)eep displaying (default):

INSERT INTO kkkk:informix.t11 (a,b) VALUES (15,109)
Statement length: 51
(S)top display, (K)eep displaying (default):

INSERT INTO kkkk:informix.t11 (a,b) VALUES (16,109)
Statement length: 51
(S)top display, (K)eep displaying (default):

> select * from t11;
a b
13 109
14 109
15 109

3 row(s) retrieved.
> select * from t11;
a b
No rows found.
> select * from t11;
a b
16 109

1 row(s) retrieved.

--dbrac1:
groupadd rep
useradd -g informix -d /home/rep -s /bin/bash -m rep
passwd rep
[root@dbrac1 ~]# cd /home
[root@dbrac1 home]# chmod g+rwx informix
su - informix
[informix@node1 dbs]$ dbaccess kkkk -
> GRANT DBA TO rep;

GGSCI (host-172-16-21-61) 3> edit param ekkkk_2
输入:
extract ekkkk_2
SOURCEDB syscdcv1 userid informix password informix
GETTRUNCATES
FETCHOPTIONS FETCHPKUPDATECOLS
TRANLOGOPTIONS EXCLUDEUSERID 1103
exttrail /home/informix/ggs/dirdat/m1
table kkkk.informix.*;

GGSCI (node1 as informix@infdrv3/info) 35> dblogin sourcedb syscdcv1 userid informix password informix
GGSCI (node1 as informix@infdrv1/d819) 2> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE syscdcv1.informix.ggs_checkpoint
CHECKPOINTTABLE kkkk.informix.ggs_checkpoint

GGSCI (node1 as informix@syscdcv1/syscdcv1) 17> ADD CHECKPOINTTABLE syscdcv1.informix.ggs_checkpoint

GGSCI (node1 as informix@syscdcv1/syscdcv1) 3> add trandata kkkk.informix.*

GGSCI (node1 as informix@infdrv3/info) 35> add extract ekkkk_2,VAM,begin now
EXTRACT added..

GGSCI (node1 as informix@infdrv3/info) 36> add exttrail /home/informix/ggs/dirdat/m1,extract ekkkk_2,megabytes 5
EXTTRAIL added.

GGSCI (host-172-16-21-61) 6> edit param pkkkk_2
输入:
extract pkkkk_2
passthru
SOURCEDB syscdcv1 userid informix password informix
rmthost 192.168.6.203,mgrport 7809
rmttrail /home/informix/ggs/dirdat/m1
table kkkk.informix.*;

GGSCI (node1 as informix@infdrv3/info) 39> add extract pkkkk_2,exttrailsource /home/informix/ggs/dirdat/m1
EXTRACT added.

GGSCI (node1 as informix@infdrv3/info) 40> add rmttrail /home/informix/ggs/dirdat/m1,extract pkkkk_2,megabytes 5
RMTTRAIL added.

--dbrac2
GGSCI (mysql as ) 1> dblogin sourcedb kkkk userid informix password informix
GGSCI (mysql as)) 5> ADD CHECKPOINTTABLE kkkk.informix.ggs_checkpoint

GGSCI (mysql as ) 2> edit param rkkkk_2
输入:
replicat rkkkk_2
handlecollisions
assumetargetdefs
GETTRUNCATES
applynoopupdates
REPERROR DEFAULT,DISCARD
TARGETDB kkkk userid rep password rep
Map kkkk.informix.*,target kkkk.informix.*;

GGSCI (mysql as ) 7> add replicat rkkkk_2,exttrail /home/informix/ggs/dirdat/m1,checkpointtable kkkk.informix.ggs_checkpoint

GGSCI (mysql as ) 8> start manager
GGSCI (mysql as ) 9> start rkkkk_2

/*******************************************************************************/
六目标端版本比源端版本高的双向复制
oracle eeee( ogg 11.2) 到 oracle eeee(ogg 12.2)
一版本
oracle 版本 :11.2.0.4.0
goldengate for oracle 版本:11.2.1.0.1

8s 版本:1.6.3.71
goldengate for informix 版本:12.2.0.1.1

ogg 用户名/口令 ggs11/ggs11
数据库需要复制的模式 eeee
ogg 安装位置 /u01/ggs11

/*******************************************************************************/
1 ogg11.2 端ORACLE 安装
SQL> create tablespace ggs11 datafile size 100m autoextend on;

SQL> CREATE USER ggs11 IDENTIFIED BY ggs11 default tablespace ggs11;
SQL> GRANT EXECUTE ON UTL_FILE TO ggs11;
SQL> @marker_setup.sql
SQL> @ddl_setup.sql
GRANT CREATE TABLE,CREATE SEQUENCE TO ggs11;

@role_setup.sql

GRANT GGS_GGSUSER_ROLE TO ggs11;

GRANT CREATE SESSION, ALTER SESSION to ggs11;
GRANT RESOURCE,CONNECT to ggs11;
GRANT SELECT ANY DICTIONARY to ggs11;
GRANT FLASHBACK ANY TABLE to ggs11;
GRANT SELECT ANY TABLE to ggs11;
GRANT INSERT ANY TABLE to ggs11;
GRANT UPDATE ANY TABLE to ggs11;
GRANT DELETE ANY TABLE to ggs11;
GRANT CREATE ANY TABLE to ggs11;
GRANT DROP ANY TABLE to ggs11;
GRANT CREATE ANY INDEX to ggs11;
GRANT DROP ANY INDEX to ggs11;
GRANT CREATE ANY VIEW to ggs11;
GRANT DROP ANY VIEW to ggs11;
GRANT CREATE ANY PROCEDURE to ggs11;
GRANT DROP ANY PROCEDURE to ggs11;
GRANT ALTER ANY TABLE to ggs11;
GRANT ALTER ANY PROCEDURE to ggs11;
GRANT EXECUTE ON DBMS_FLASHBACK to ggs11;
GRANT GGS_GGSUSER_ROLE to ggs11;

[oracle@node1 ggs11]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

GGSCI (node1) 1> create subdirs

GGSCI (host-172-16-21-61) ) 2> edit params mgr
输入：
PORT 7860
DYNAMICPORTLIST 7845-7909
--AUTOSTART ER *
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
LAGREPORTHOURS 1
LAGCRITICALMINUTES 45

GGSCI (host-172-16-21-61) 3>edit param ./GLOBALS
输入:
GGSCHEMA ggs11
CHECKPOINTTABLE ggs11.ggs_checkpoint

GGSCI (host-172-16-21-61) 4> DBLOGIN USERID ggs11@orcl, PASSWORD ggs11
GGSCI (host-172-16-21-61) 5> add checkpointtable ggs11.ggs_checkpoint

--执行下面脚本时报下面的错误：
SQL> @role_setup.sql
SQL> GRANT CREATE TABLE,CREATE SEQUENCE TO ggs;
GRANT CREATE TABLE,CREATE SEQUENCE TO OGG
*
ERROR at line 1:
ORA-04098: 触发器 'SYS.GGS_DDL_TRIGGER_BEFORE' 无效且未通过重新验证

--解决（参看网页http://blog.csdn.net/cc_dba/article/details/40082149
3.3 Enabling Oracle GoldenGate in the Database

The database services required to support Oracle GoldenGate capture and apply must be
enabled explicitly for an Oracle 11.2.0.4 database. This is required for all modes of Extract and Replicat.

To enable Oracle GoldenGate, set the following database initialization parameter.
All instances in Oracle RAC must have the same setting.

ENABLE_GOLDENGATE_REPLICATION=true
）

SQL> @ddl_disable.sql

2 数据库
SQL> create user eeee identified by eeee;
SQL> grant dba to eeee;
SQL> conn eeee/eeee
SQL> create table t1 (a int,b int);
SQL> create table t2 (a int,b int);
SQL> create table t3 (a int,b int);

3 ogg11.2 端复制到 ogg12.2 端
1）Extract 配置( group name must be at most 8 characters).
源数据库端：
GGSCI (node1) 1> DBLOGIN USERID ggs11@orcl, PASSWORD ggs11
Successfully logged into database.

GGSCI (node1) 2> add trandata eeee.*

GGSCI (node1) 3> edit param ex_eeee1
输入：
extract ex_eeee1
GETTRUNCATES
dynamicresolution
NOCOMPRESSDELETES
NOCOMPRESSUPDATES
FETCHOPTIONS FETCHPKUPDATECOLS,USEROWID
userid ggs11@orcl,password ggs11
TRANLOGOPTIONS EXCLUDEUSER ggs11
exttrail ./dirdat/dd
table eeee.*;

GGSCI (node1) 4> add extract ex_eeee1,tranlog,begin now
EXTRACT added.

GGSCI (node1) 5> add exttrail ./dirdat/dd,extract ex_eeee1,megabytes 5
EXTTRAIL added.

2) data pump 配置 dp_eeee1
源数据库端:
GGSCI (f80) 11> edit param dp_eeee1
输入：
extract dp_eeee1
dynamicresolution
rmthost 192.168.6.201,mgrport 7845
USERID ggs11, PASSWORD ggs11
rmttrail ./dirdat/dd
table eeee.*;

GGSCI (node1) 8> add extract dp_eeee1,exttrailsource ./dirdat/dd
EXTRACT added.

GGSCI (node1) 9> add rmttrail ./dirdat/dd,extract dp_eeee1,megabytes 5

GGSCI (node1) 18> start manager
GGSCI (node1) 20> start ex_eeee1
GGSCI (node1) 21> start dp_eeee1

3) replicat 配置
目标数据库端 ogg12.2 (注意数据从 eeee 复制到 eeee)
GGSCI (node1) 3>DBLOGIN USERID ggs12@orcl, PASSWORD ggs12

GGSCI (h70) 2> edit param rp_eeee1
输入：
replicat rp_eeee1
userid ggs12@orcl,password ggs12
handlecollisions
ASSUMETARGETDEFS
GETTRUNCATES
applynoopupdates
INSERTUPDATES
INSERTDELETES
REPERROR DEFAULT,DISCARD
DISCARDFILE /u01/ggs/dirrpt/rp_eeee1.dsc,PURGE
MAP eeee.*, TARGET eeee.*;

GGSCI (node1 as informix@infdrv3/info) 7> add replicat rp_eeee1,checkpointtable ggs12.ggs_checkpoint,exttrail ./dirdat/dd
REPLICAT added.

GGSCI (node1 as informix@infdrv3/info) 8> start rp_eeee1

4 ogg12.2 端复制到 ogg11.2 端
FORMAT RELEASE 11.2 解决高端到低端的版本问题

1）Extract 配置( group name must be at most 8 characters).
源数据库端：
GGSCI (node1) 1> DBLOGIN USERID ggs12@orcl, PASSWORD ggs12
Successfully logged into database.

GGSCI (node1) 2> add trandata eeee.*

GGSCI (node1) 3> edit param ex_eeee2
输入：
extract ex_eeee2
GETTRUNCATES
dynamicresolution
NOCOMPRESSDELETES
NOCOMPRESSUPDATES
FETCHOPTIONS FETCHPKUPDATECOLS,USEROWID
userid ggs12@orcl,password ggs12
TRANLOGOPTIONS ASMUSER sys@asm, ASMPASSWORD oracle
TRANLOGOPTIONS EXCLUDEUSER ggs12
exttrail ./dirdat/ee,FORMAT RELEASE 11.2
table eeee.*;

GGSCI (node1) 4> add extract ex_eeee2,tranlog,threads 2,begin now
EXTRACT added.

GGSCI (node1) 5> add exttrail ./dirdat/ee,extract ex_eeee2,megabytes 5
EXTTRAIL added.

2) data pump 配置 dp_eeee2
源数据库端:
GGSCI (f80) 11> edit param dp_eeee2
输入：
extract dp_eeee2
dynamicresolution
rmthost 192.168.6.200,mgrport 7860
USERID ggs12, PASSWORD ggs12
rmttrail ./dirdat/ee,FORMAT RELEASE 11.2
table eeee.*;

GGSCI (node1) 8> add extract dp_eeee2,exttrailsource ./dirdat/ee
EXTRACT added.

GGSCI (node1) 9> add rmttrail ./dirdat/ee,extract dp_eeee2,megabytes 5

GGSCI (node1) 18> start manager
GGSCI (node1) 20> start ex_eeee2
GGSCI (node1) 21> start dp_eeee2

3) replicat 配置
目标数据库端 ogg12.2 (注意数据从 eeee 复制到 eeee)
GGSCI (node1) 3>DBLOGIN USERID ggs11@orcl, PASSWORD ggs11

GGSCI (h70) 2> edit param rp_eeee2
输入：
replicat rp_eeee2
userid ggs11@orcl,password ggs11
handlecollisions
ASSUMETARGETDEFS
GETTRUNCATES
applynoopupdates
INSERTUPDATES
INSERTDELETES
REPERROR DEFAULT,DISCARD
DISCARDFILE /u01/ggs/dirrpt/rp_eeee2.dsc,PURGE
MAP eeee.*, TARGET eeee.*;

GGSCI (node1 as informix@infdrv3/info) 7> add replicat rp_eeee2,checkpointtable ggs11.ggs_checkpoint,exttrail ./dirdat/ee
REPLICAT added.

GGSCI (node1 as informix@infdrv3/info) 8> start rp_eeee2

--问题：

2017-02-03 14:11:43 ERROR OGG-01332 File ./dirdat/ee000000, with compatibility level 5, is not compatible with the current softw
are version's compatibility level of 3. Modify the file writer's parameter file to generate the appropriate format using the FORMAT
LEVEL 3 option.

/*******************************************************************************/
七 oracle 12.1 cdb 到 informix 双向复制
oracle sscdb(win oracle 12.1, ogg 12.2) 到 informix sscdb( ogg 12.2)

-- 1 cdb数据库 EXTRACT 需要登录Successfully logged into database CDB$ROOT
-- 2 cdb数据库 EXTRACT 需要 INTEGRATED TRANLOG: add extract esscdb_1,INTEGRATED TRANLOG,begin now
-- 3 cdb数据库 EXTRACT 需要使用 Logmining server .注册 Register the Integrated Extract
GGSCI (host-172-16-21-61) 6> REGISTER EXTRACT esscdb_1 DATABASE CONTAINER (pp1)

--1 一个 CDB包含多个PDB，源端部署的一个extract可访问所有pdb redo，理论上不需要每个pdb单独配置extract.
--2 抽取模式只能是integrated(集成)模式，不支持claasic capture传统方式捕获；
--3 因为要使用integrated extract，因此，需要能访问log mining server，而这个只能从cdb$root中访问；
--4 源端要使用common user，即c##ogg这种用户来访问源端DB，这样能访问DB的redo log & all pdbs。
--5 在GGSCI或参数文件中，可以使用pdb.schema.table来访问具体的表或序列；
--6 可以在参数文件中使用sourceCatalog参数，指定一个PDB，后面的参数中只需要schema.table即可；
--7 目标端每个pdb要有一个replicat进程，即一个replicat进程只能投递到一个PDB，不能投递到多个。
--8 源端OGG用户需要赋权：dbms_goldengate_auth.grant_admin_privilege(‘C##GGADMIN’,container=>‘all’)，
-- 同时建议将ogg的用户设置赋权为：grant dba to c##ogg container=all;
--9 源端DB除了以前要打开归档, force logging, 最小附加日志，可能还需要打开一个开关：alter system set enable_goldengate_replication=true;

/*******************************************************************************/
--1 win 下 OGG12.2 安装
将 ggs_Windows_x64_shiphome.zip 拷贝到 c:\ 解压 ggs_Windows_x64_shiphome，安装到c:\ggs12 选择 12C

--2 创建目录
C:\GGS12>GGSCI
GGSCI (sy-PC) 1> CREATE SUBDIRS

--3:增加服务
C:\GGS12>install addservice

Service 'oracleggsmgr1' created.
Install program terminated normally.

--4 :安装 Windows 事件消息
C:\GGS12>install addevents

Oracle GoldenGate messages installed successfully.
Install program terminated normally.

--5 oracle 12c 数据库设置
--1) orclcdb (cdb 容器数据库,PDB 插接式数据库数为2，PDB 前缀pp )
C:\Users\sy>sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on 星期二 2月 7 09:31:50 2017

SQL> select pdb_name from dba_pdbs;

PDB_NAME
------------------------------------------------------

PP1
PDB$SEED
PP2

SQL> select name from dba_services;

NAME
--------------------------------------------
SYS$BACKGROUND
SYS$USERS
orclcdbXDB
orclcdb

C:\Users\sy>lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 07-2月 -2017 10:5
0:36

Copyright (c) 1991, 2014, Oracle. All rights reserved.

正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sy-PC)(PORT=1521)))
LISTENER 的 STATUS
------------------------
别名 LISTENER
版本 TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Produ
ction
启动日期 07-2月 -2017 09:32:54
正常运行时间 0 天 1 小时 17 分 43 秒
跟踪级别 off
安全性 ON: Local OS Authentication
SNMP OFF
监听程序参数文件 D:\app\oracle\product\12.1.0\dbhome_1\network\admin\li
stener.ora
监听程序日志文件 D:\app\oracle\diag\tnslsnr\sy-PC\listener\alert\log.xm
l
监听端点概要...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sy-PC)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
服务摘要..
服务 "CLRExtProc" 包含 1 个实例。
实例 "CLRExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
服务 "orclcdb" 包含 1 个实例。
实例 "orclcdb", 状态 READY, 包含此服务的 1 个处理程序...
服务 "orclcdbXDB" 包含 1 个实例。
实例 "orclcdb", 状态 READY, 包含此服务的 1 个处理程序...
服务 "pp1" 包含 1 个实例。
实例 "orclcdb", 状态 READY, 包含此服务的 1 个处理程序...
服务 "pp2" 包含 1 个实例。
实例 "orclcdb", 状态 READY, 包含此服务的 1 个处理程序...
命令执行成功

# tnsnames.ora 增加
pp1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sy-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pp1)
)
)

pp2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sy-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pp2)
)
)

6 oracel cdb 数据库端 OGG设置
注：
If the source database is a multitenant container database, the Extract user must be a
common user and must log into the root container. See
SQL> conn / as sysdba
sql> alter system set RECYCLEBIN =OFF SCOPE=SPFILE;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY,UNIQUE INDEX) COLUMNS;
SQL> alter system switch logfile;
SQL> select supplemental_log_data_min from v$database;
SQL> alter system set enable_goldengate_replication=true;
SQL> ALTER DATABASE FORCE LOGGING;
SQL> ARCHIVE LOG LIST;
SQL> startup force;

-- 创建 Commer user
sql> conn / as sysdba
sql> Create user c##ggs identified by ggs;
sql> exec dbms_goldengate_auth.grant_admin_privilege('c##ggs',Container=>'ALL');
SQL> grant dba to c##ggs container=all;
SQL> connect c##ggs/ggs

GGSCI>edit param ./GLOBALS
MGRSERVNAME oracleggsmgr1
GGSCHEMA c##ggs

GGSCI (host-172-16-21-61) ) 2> edit params mgr
输入:
PORT 7878
DYNAMICPORTLIST 7845-7909
--AUTOSTART ER *
--AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
LAGREPORTHOURS 1
LAGCRITICALMINUTES 45

SQL> conn sys/oracle@pp1 as sysdba
已连接。
SQL> startup
插接式数据库已打开。

SQL> create user sscdb identified by sscdb;
SQL> grant dba to sscdb;
SQL> conn sscdb/sscdb@pp1
SQL> create table t1 (a int,b int);
SQL> create table t2 (a int,b int);
SQL> create table t3 (a int,b int);

--设置环境变量
ORACLE_HOME = "D:\app\oracle\product\12.1.0\dbhome_1"

GGSCI (sy-PC) 1> DBLOGIN USERID c##ggs@orclcdb, PASSWORD ggs
Successfully logged into database CDB$ROOT.

GGSCI (sy-PC) 1> GGSCI (node1) 2> ADD SCHEMATRANDATA pp1.sscdb
GGSCI (sy-PC) 1> GGSCI (node1) 2> add trandata pp1.sscdb.*

GGSCI (host-172-16-21-61) 3> edit param esscdb_1
输入:
--win
extract esscdb_1
SETENV (ORACLE_HOME = "D:\app\oracle\product\12.1.0\dbhome_1")
SETENV (ORACLE_SID = "ORCLCDB")
GETTRUNCATES
FETCHOPTIONS FETCHPKUPDATECOLS
userid c##ggs@orclcdb,password ggs
TRANLOGOPTIONS EXCLUDEUSER c##ggs
SOURCECATALOG pp1
exttrail ./dirdat/c1
table sscdb.*;

--Linux
extract esscdb_1
GETTRUNCATES
FETCHOPTIONS FETCHPKUPDATECOLS
userid c##ggs@orclcdb,password ggs
TRANLOGOPTIONS EXCLUDEUSER c##ggs
SOURCECATALOG pp1
exttrail ./dirdat/c1
table sscdb.*;

GGSCI (host-172-16-21-61) 4> add extract esscdb_1,INTEGRATED TRANLOG,begin now
GGSCI (host-172-16-21-61) 5> add exttrail ./dirdat/c1,extract esscdb_1,megabytes 5

--注册 Register the Integrated Extract
GGSCI (host-172-16-21-61) 6> REGISTER EXTRACT esscdb_1 DATABASE CONTAINER (pp1)

2017-02-07 13:40:15 INFO OGG-02003 Extract ESSCDB_1 successfully registered
with database at SCN 2443177.

注：如果一个PLUG数据库所有多个EXTRACT ，REGISTER 加上 SHARE参数
如：REGISTER EXTRACT group DATABASE [CONTAINER (container[, ...])] [SCN
system_change_number] SHARE

GGSCI (host-172-16-21-61) 6> edit param psscdb_1
输入:
extract psscdb_1
passthru
userid c##ggs@orclcdb,password ggs
rmthost 192.168.6.200,mgrport 7809
rmttrail ./dirdat/c1
table pp1.sscdb.*;

GGSCI (host-172-16-21-61) 7> add extract psscdb_1,exttrailsource ./dirdat/c1
GGSCI (host-172-16-21-61) 8> add rmttrail ./dirdat/c1,extract psscdb_1,megabytes 5
GGSCI (host-172-16-21-61) 9> start manager
GGSCI (mysql as ) 8> start esscdb_1
GGSCI (mysql as ) 8> start psscdb_1

7 创建 defgen.def文件

1)下面在 ORACLE 端数据库机器上执行
cd c:\ggs12\dirprm

[oracle@host-172-16-21-61 dirprm]$ vi defgensscdb.prm
输入:
defsfile ./dirdef/sscdb.def purge
userid c##ggs@orclcdb,password ggs
table pp1.sscdb.*;

cd c:\ggs12
defgen paramfile ./dirprm/defgensscdb.prm

将 sscdb.def 上传到 INFORMIX 端

--6 informix 数据库设置
--1) 创建数据库
dbaccess sscdb -

SQL> create table t1 (a int,b int);
SQL> create table t2 (a int,b int);
SQL> create table t3 (a int,b int);

--为双向复制创建用户，授权
[informix@node1 dbs]$ dbaccess sscdb -
> GRANT DBA TO rep;

--2） ODBC
sscdb=IBM INFORMIX ODBC DRIVER

[sscdb]
Driver=/home/informix/gbase/lib/cli/iclis09b.so
Description=IBM INFORMIX ODBC DRIVER
Database=sscdb
LogonID=informix
pwd=informix
Servername=gbaseserver

--3）测试odbc
[informix@node1 ~]$ cd gbase/
[informix@node1 gbase]$ ./catalog sscdb
[informix@node1 ggs]$ ggsci

Oracle GoldenGate Command Interpreter for Informix
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401

GGSCI (node1) 3> edit params mgr
port 7809
AUTOSTART REPLICAT r*
AUTORESTART REPLICAT r*, RETRIES 4, WAITMINUTES 4
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3

GGSCI (mysql as ) 1> dblogin sourcedb sscdb userid informix password informix
GGSCI (mysql as)) 5> ADD CHECKPOINTTABLE sscdb.informix.ggs_checkpoint

GGSCI (mysql as ) 2> edit param rsscdb_1
输入:
replicat rsscdb_1
sourcedefs /home/informix/ggs/dirdef/sscdb.def
handlecollisions
assumetargetdefs
GETTRUNCATES
applynoopupdates
REPERROR DEFAULT,DISCARD
TARGETDB sscdb userid rep password rep
Map pp1.sscdb.*,target sscdb.informix.*;

GGSCI (mysql as ) 7> add replicat rsscdb_1,exttrail ./dirdat/c1,checkpointtable sscdb.informix.ggs_checkpoint
GGSCI (mysql as ) 8> start manager
GGSCI (mysql as ) 8> start rsscdb_1

7 INFORMIX 向 ORACLE12C 复制
GGSCI (mysql as ) 1>dblogin sourcedb syscdcv1 userid informix password informix

GGSCI (node1 as informix@syscdcv1/syscdcv1) 3> add trandata sscdb.informix.*

GGSCI (host-172-16-21-61) 3> edit param esscdb_2
输入:
extract esscdb_2
SOURCEDB syscdcv1 userid informix password informix
GETTRUNCATES
FETCHOPTIONS FETCHPKUPDATECOLS
TRANLOGOPTIONS EXCLUDEUSERID 1103
exttrail ./dirdat/c2
table sscdb.informix.*;

GGSCI (node1 as informix@infdrv3/info) 35> add extract esscdb_2,VAM,begin now
EXTRACT added..

GGSCI (node1 as informix@infdrv3/info) 36> add exttrail ./dirdat/c2,extract esscdb_2,megabytes 5
EXTTRAIL added.

GGSCI (host-172-16-21-61) 6> edit param psscdb_2
输入:
extract psscdb_2
passthru
SOURCEDB syscdcv1 userid informix password informix
rmthost 192.168.6.200,mgrport 7878
rmttrail ./dirdat/c2
table sscdb.informix.*;

GGSCI (node1 as informix@infdrv3/info) 39> add extract psscdb_2,exttrailsource ./dirdat/c2
EXTRACT added.

GGSCI (node1 as informix@infdrv3/info) 40> add rmttrail ./dirdat/c2,extract psscdb_2,megabytes 5
RMTTRAIL added.

8 informix 端准备DEF 文件

su - informix
cd /home/informix/ggs/dirprm
[informix@node1 dirprm]$ vi informixsscdb.prm
输入:
defsfile ./dirdef/informixsscdb.def purge
SOURCEDB sscdb userid informix password informix
table sscdb.informix.*;

cd /home/informix/ggs
./defgen paramfile ./dirprm/informixsscdb.prm
su - root
cd /home/informix/ggs/dirdef/
cp /home/informix/ggs/dirdef/informixsscdb.def /u01/ggs12c/dirdef/
chown -R oracle:oinstall /u01/ggs12c/dirdef/
chmod -R 777 /u01/ggs12c/dirdef/
chmod -R 777 /u01/ggs12c
chmod -R 777 /home/informix/ggs/dirdat

9 ORACLE 端配置REPLICAT

GGSCI>edit param ./GLOBALS
MGRSERVNAME oracleggsmgr1
GGSCHEMA c##ggs
CHECKPOINTTABLE c##ggs.ggs_checkpoint

--oracle12c replicat 需要连接到 PLUG 数据库
GGSCI (sy-PC) 1> DBLOGIN USERID c##ggs@pp1, PASSWORD ggs
Successfully logged into database PP1.

--nonintegrated replicat 需要 checkpointtable
GGSCI (host-172-16-21-61) 5> add checkpointtable c##ggs.ggs_checkpoint

GGSCI (mysql as ) 2> edit param rsscdb_2
输入:
replicat rsscdb_2
userid c##ggs@pp1,password ggs
sourcedefs /u01/ggs12c/dirdef/informixsscdb.def
handlecollisions
ASSUMETARGETDEFS
applynoopupdates
REPERROR DEFAULT,DISCARD
DISCARDFILE ./dirrpt/rsscdb.dsc,PURGE
MAP sscdb.informix.*, TARGET pp1.sscdb.* ;


GGSCI (h70) 3> add replicat rsscdb_2,checkpointtable c##ggs.ggs_checkpoint,exttrail ./dirdat/c2
GGSCI (h70) 12> start rsscdb_2

-- INTEGRATED replicat： DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
-- add replicat rsscdb_2,INTEGRATED,exttrail ./dirdat/c2

GGSCI (mysql as ) 2> edit param rsscdb_2
输入:
replicat rsscdb_2
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
userid c##ggs@pp1,password ggs
sourcedefs /u01/ggs12c/dirdef/informixsscdb.def
handlecollisions
ASSUMETARGETDEFS
applynoopupdates
REPERROR DEFAULT,DISCARD
DISCARDFILE ./dirrpt/rsscdb.dsc,PURGE
MAP sscdb.informix.*, TARGET pp1.sscdb.* ;

GGSCI (h70) 3> add replicat rsscdb_2,INTEGRATED,exttrail ./dirdat/c2
GGSCI (h70) 12> start rsscdb_2

/*******************************************************************************/
八 oracle 12.1 cdb(orclcdb) 复制到 ORACLE 12.1 cdb(cdb2) ogg 12.2
每个CDB 数据库一个PLUGABLE DATABASE pp
/*******************************************************************************/
[oracle@node1 app]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 08-2月 -2017 16:53:24

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 08-2月 -2017 16:15:12
Uptime 0 days 0 hr. 38 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/node1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "cdb2" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "cdb2XDB" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "cdb2pp1" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "orclcdb" has 1 instance(s).
Instance "orclcdb", status READY, has 1 handler(s) for this service...
Service "orclcdbXDB" has 1 instance(s).
Instance "orclcdb", status READY, has 1 handler(s) for this service...
Service "orclcdbpp1" has 1 instance(s).
Instance "orclcdb", status READY, has 1 handler(s) for this service...
Service "pp" has 2 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Instance "orclcdb", status READY, has 1 handler(s) for this service...
The command completed successfully

# vi tnsnames.ora
ORCLCDBPP1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclcdbPP1)
)
)

cdb2PP1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb2PP1)
)
)

ORCLCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclcdb)
)
)

CDB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb2)
)
)

1 为实例 cdb2 安装GGS
[oracle@node1 u01]$ mkdir ggs12c2

2 创建 services
--orclcdb 数据库
SQL> conn / as sysdba

--自动启动所有的pdb(database trigger）
CREATE TRIGGER open_all_pdbs
AFTER STARTUP
ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;
/

SQL> select name,network_name from dba_services;
NAME
----------------------------------------------------------------
NETWORK_NAME
--------------------------------------------------------------------------------
orclcdb
orclcdb

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orclcdb/system01.dbf
/u01/app/oracle/oradata/orclcdb/sysaux01.dbf
/u01/app/oracle/oradata/orclcdb/undotbs01.dbf
/u01/app/oracle/oradata/orclcdb/pdbseed/system01.dbf
/u01/app/oracle/oradata/orclcdb/users01.dbf
/u01/app/oracle/oradata/orclcdb/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/orclcdb/pp/system01.dbf
/u01/app/oracle/oradata/orclcdb/pp/sysaux01.dbf
/u01/app/oracle/oradata/orclcdb/pp/pp_users01.dbf

sql> conn sys/oracle@orclcdb as sysdba
SQL> startup
Pluggable Database opened.

--Commer user
sql> Create user c##username identified by password;

sql> alter session set container=pp;

SQL> select name ,network_name from dba_services;

NAME
----------------------------------------------------------------
NETWORK_NAME
--------------------------------------------------------------------------------
pp
pp

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orclcdb/undotbs01.dbf
/u01/app/oracle/oradata/orclcdb/pp/system01.dbf
/u01/app/oracle/oradata/orclcdb/pp/sysaux01.dbf
/u01/app/oracle/oradata/orclcdb/pp/pp_users01.dbf

--创建
BEGIN
DBMS_SERVICE.CREATE_SERVICE(
service_name => 'orclcdbpp1',
network_name => 'orclcdbpp1');
END;
/

--启动
exec dbms_service.start_service('orclcdbpp1');

--删除
exec dbms_service.delete_service('orclcdbpp1');

--自动启动服务（database trigger）
create trigger tr_startmyservice after startup on database
declare
begin
dbms_service.start_service('orclcdbpp1');
end;
/

SQL> conn sys/oracle@orclcdbpp1 as sysdba
Connected.
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orclcdb/undotbs01.dbf
/u01/app/oracle/oradata/orclcdb/pp/system01.dbf
/u01/app/oracle/oradata/orclcdb/pp/sysaux01.dbf
/u01/app/oracle/oradata/orclcdb/pp/pp_users01.dbf

--cdb2 数据库
SQL> conn / as sysdba
Connected.
SQL> select name,network_name from dba_services;
NAME
----------------------------------------------------------------
NETWORK_NAME
--------------------------------------------------------------------------------
cdb2
cdb2

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb2/system01.dbf
/u01/app/oracle/oradata/cdb2/sysaux01.dbf
/u01/app/oracle/oradata/cdb2/undotbs01.dbf
/u01/app/oracle/oradata/cdb2/pdbseed/system01.dbf
/u01/app/oracle/oradata/cdb2/users01.dbf
/u01/app/oracle/oradata/cdb2/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/cdb2/pp/system01.dbf
/u01/app/oracle/oradata/cdb2/pp/sysaux01.dbf
/u01/app/oracle/oradata/cdb2/pp/pp_users01.dbf

SQL> startup
SQL> CREATE TRIGGER open_all_pdbs
AFTER STARTUP
ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;
/

--Commer user
SQL> Create user c##username identified by password;

sql> alter session set container=pp;
SQL> startup
Pluggable Database opened.
SQL> select name ,network_name from dba_services;

NAME
----------------------------------------------------------------
NETWORK_NAME
--------------------------------------------------------------------------------
pp
pp

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb2/undotbs01.dbf
/u01/app/oracle/oradata/cdb2/pp/system01.dbf
/u01/app/oracle/oradata/cdb2/pp/sysaux01.dbf
/u01/app/oracle/oradata/cdb2/pp/pp_users01.dbf

--创建
BEGIN
DBMS_SERVICE.CREATE_SERVICE(
service_name => 'cdb2pp1',
network_name => 'cdb2pp1');
END;
/

--启动
exec dbms_service.start_service('cdb2pp1');

--删除
exec dbms_service.delete_service('cdb2pp1');

--自动启动服务（database trigger）
create trigger tr_startmyservice after startup on database
declare
begin
dbms_service.start_service('cdb2pp1');
end;
/

SQL> conn sys/oracle@cdb2pp1 as sysdba
Connected.
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb2/undotbs01.dbf
/u01/app/oracle/oradata/cdb2/pp/system01.dbf
/u01/app/oracle/oradata/cdb2/pp/sysaux01.dbf
/u01/app/oracle/oradata/cdb2/pp/pp_users01.dbf

3 创建comm 用户
SQL> conn sys/oracle@orclcdb as sysdba
--Commer user

sql> Create user c##orclcdbggs identified by ggs;
sql> exec dbms_goldengate_auth.grant_admin_privilege('c##orclcdbggs',Container=>'ALL');
SQL> grant dba to c##orclcdbggs container=all;
SQL> connect c##orclcdbggs/ggs@orclcdb

SQL> conn sys/oracle@cdb2 as sysdba
--Commer user
sql> Create user c##cdb2ggs identified by ggs;
sql> exec dbms_goldengate_auth.grant_admin_privilege('c##cdb2ggs',Container=>'ALL');
SQL> grant dba to c##cdb2ggs container=all;
SQL> connect c##cdb2ggs/ggs@cdb2

---orclcdb端复制到CDB2 端
4 orclcdb 端
SQL> conn sys/oracle@orclcdb as sysdba
sql> alter system set RECYCLEBIN =OFF SCOPE=SPFILE;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY,UNIQUE INDEX) COLUMNS;
SQL> alter system switch logfile;
SQL> select supplemental_log_data_min from v$database;
SQL> alter system set enable_goldengate_replication=true;
SQL> ALTER DATABASE FORCE LOGGING;
SQL> ARCHIVE LOG LIST;
SQL> startup force;

SQL> conn sys/oracle@orclcdbpp1 as sysdba

SQL> create user orclcdb identified by oracle;
SQL> grant dba to orclcdb;
SQL> conn orclcdb/oracle@orclcdbpp1
SQL> create table t1 (a int,b int);
SQL> create table t2 (a int,b int);
SQL> create table t3 (a int,b int);

[oracle@node1 ggs12c]$ cd /u01/ggs12c
GGSCI (sy-PC) 1> CREATE SUBDIRS

GGSCI (host-172-16-21-61) ) 2> edit params mgr
PORT 7878
DYNAMICPORTLIST 7845-7911
--AUTOSTART ER *
--AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
LAGREPORTHOURS 1
LAGCRITICALMINUTES 45

5 cdb2 端
SQL> conn sys/oracle@cdb2 as sysdba
sql> alter system set RECYCLEBIN =OFF SCOPE=SPFILE;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY,UNIQUE INDEX) COLUMNS;
SQL> alter system switch logfile;
SQL> select supplemental_log_data_min from v$database;
SQL> alter system set enable_goldengate_replication=true;
SQL> ALTER DATABASE FORCE LOGGING;
SQL> ARCHIVE LOG LIST;
SQL> startup force;

SQL> conn sys/oracle@cdb2pp1 as sysdba

SQL> create user cdb2 identified by oracle;
SQL> grant dba to cdb2;
SQL> conn cdb2/oracle@cdb2pp1
SQL> create table t1 (a int,b int);
SQL> create table t2 (a int,b int);
SQL> create table t3 (a int,b int);

[oracle@node1 ggs12c]$ cd /u01/ggs12c2
GGSCI (sy-PC) 1> CREATE SUBDIRS

GGSCI (host-172-16-21-61) ) 2> edit params mgr
PORT 7890
DYNAMICPORTLIST 7845-7911
--AUTOSTART ER *
--AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
LAGREPORTHOURS 1
LAGCRITICALMINUTES 45

--6 cdb2 端复制到 ORCLCDB端
GGSCI (sy-PC) 1> DBLOGIN USERID c##cdb2ggs@cdb2, PASSWORD ggs
Successfully logged into database CDB$ROOT.

GGSCI (sy-PC) 1> GGSCI (node1) 2> add trandata pp.cdb2.*

GGSCI (host-172-16-21-61) 3> edit param ecdb2_1
extract ecdb2_1
GETTRUNCATES
FETCHOPTIONS FETCHPKUPDATECOLS
userid c##cdb2ggs@cdb2,password ggs
TRANLOGOPTIONS EXCLUDEUSER c##cdb2ggs
SOURCECATALOG pp
exttrail ./dirdat/n1
table cdb2.*;

GGSCI (host-172-16-21-61) 4> add extract ecdb2_1,INTEGRATED TRANLOG,begin now
GGSCI (host-172-16-21-61) 5> add exttrail ./dirdat/n1,extract ecdb2_1,megabytes 5

--注册 Register the Integrated Extract
GGSCI (host-172-16-21-61) 6> REGISTER EXTRACT ecdb2_1 DATABASE CONTAINER (pp)

GGSCI (host-172-16-21-61) 6> edit param pcdb2_1
输入:
extract pcdb2_1
passthru
userid c##cdb2ggs@cdb2,password ggs
rmthost 192.168.6.200,mgrport 7878
rmttrail ./dirdat/n1
table pp.cdb2.*;

GGSCI (host-172-16-21-61) 7> add extract pcdb2_1,exttrailsource ./dirdat/n1
GGSCI (host-172-16-21-61) 8> add rmttrail ./dirdat/n1,extract pcdb2_1,megabytes 5
GGSCI (host-172-16-21-61) 9> start manager

--7 ORCLCDB 端
--oracle12c replicat 需要连接到 PLUG 数据库
GGSCI (sy-PC) 1> DBLOGIN USERID c##orclcdbggs@orclcdbpp1, PASSWORD ggs
Successfully logged into database PP.

GGSCI (host-172-16-21-61) 5> add checkpointtable pp.c##orclcdbggs.ggs_checkpoint

Logon catalog name PP will be used for table specification PP.c##orclcdbggs.ggs_checkpoint.
Successfully created checkpoint table PP.c##orclcdbggs.ggs_checkpoint.

GGSCI (mysql as ) 2> edit param rcdb2_1
输入:
replicat rcdb2_1
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
userid c##orclcdbggs@orclcdbpp1,password ggs
handlecollisions
ASSUMETARGETDEFS
applynoopupdates
REPERROR DEFAULT,DISCARD
DISCARDFILE ./dirrpt/rcdb2.dsc,PURGE
MAP pp.cdb2.*, TARGET pp.orclcdb.* ;

GGSCI (h70) 3> add replicat rcdb2_1,INTEGRATED,exttrail ./dirdat/n1

GGSCI> edit param ./GLOBALS
GGSCHEMA c##orclcdbggs
CHECKPOINTTABLE pp.c##orclcdbggs.ggs_checkpoint

--8 oracle 到 ORACLE 的初始化
1 Configuring a Direct Bulk Load to SQL*Loader (page 15-4)
1）源端 oracle cdb2
GGSCI (host-172-16-21-61) 3> edit param initext

EXTRACT initext
userid c##cdb2ggs@cdb2, PASSWORD ggs
RMTHOST 192.168.6.200, MGRPORT 7878
RMTTASK replicat, GROUP initrep
table pp.cdb2.*;

GGSCI (host-172-16-21-61) 3> ADD EXTRACT initext, SOURCEISTABLE

2）目的端 oracle orclcdb
GGSCI (host-172-16-21-61) 3> edit param initrep

REPLICAT initrep
userid c##orclcdbggs@orclcdbpp1,password ggs
handlecollisions
ASSUMETARGETDEFS
applynoopupdates
REPERROR DEFAULT,DISCARD
DISCARDFILE ./dirrpt/rsscdb.dsc,PURGE
MAP pp.cdb2.* , TARGET pp.orclcdb.* ;

GGSCI (host-172-16-21-61) 3> ADD REPLICAT initrep, SPECIALRUN

3）启动源端 EXTRACT

GGSCI (host-172-16-21-61) 3> start ecdb2_1
GGSCI (host-172-16-21-61) 3> start PCDB2_1

3）启动initext

GGSCI (host-172-16-21-61) 3> start initext

GGSCI (node1 as c##cdb2ggs@cdb2/CDB$ROOT) 134> info extract initext

EXTRACT INITEXT Last Started 2017-02-13 13:42 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table PP.CDB2.T3
2017-02-13 13:42:28 Record 2
Task SOURCEISTABLE

GGSCI (node1 as c##orclcdbggs@orclcdb/PP) 71> info replicat initrep

REPLICAT INITREP Initialized 2017-02-13 13:39 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:03:24 ago)
Log Read Checkpoint Not Available
Task SPECIALRUN

GGSCI (node1 as c##cdb2ggs@cdb2/CDB$ROOT) 18> info extract ecdb2_1

EXTRACT Ecdb2_1 Last Started 2017-02-09 13:12 Status RUNNING
Checkpoint Lag 00:01:15 (updated 00:00:04 ago)
Process ID 30478
Log Read Checkpoint Oracle Integrated Redo Logs
2017-02-09 13:11:36
SCN 0.1910595 (1910595)

GGSCI (node1 as c##orclcdbggs@orclcdb/PP) 146> start rcdb2_1

SQL> insert into t1 values(44444,111);
SQL> commit;

--9 orclcdb 复制到cdb2

GGSCI (sy-PC) 1> DBLOGIN USERID c##orclcdbggs@orclcdb, PASSWORD ggs
Successfully logged into database CDB$ROOT.

GGSCI (sy-PC) 1> GGSCI (node1) 2> add trandata pp.orclcdb.*

GGSCI (host-172-16-21-61) 3> edit param eorcdb_2
extract eorcdb_2
GETTRUNCATES
FETCHOPTIONS FETCHPKUPDATECOLS
userid c##orclcdbggs@orclcdb,password ggs
TRANLOGOPTIONS EXCLUDEUSER c##orclcdbggs
SOURCECATALOG pp
exttrail ./dirdat/w1
table orclcdb.*;

GGSCI (host-172-16-21-61) 4> add extract eorcdb_2,INTEGRATED TRANLOG,begin now
GGSCI (host-172-16-21-61) 5> add exttrail ./dirdat/w1,extract eorcdb_2,megabytes 5

--注册 Register the Integrated Extract
GGSCI (host-172-16-21-61) 6> REGISTER EXTRACT eorcdb_2 DATABASE CONTAINER (pp)

GGSCI (host-172-16-21-61) 6> edit param porcdb_2
输入:
extract porcdb_2
passthru
userid c##orclcdbggs@orclcdb,password ggs
rmthost 192.168.6.200,mgrport 7890
rmttrail ./dirdat/w1
table pp.orclcdb.*;

GGSCI (host-172-16-21-61) 7> add extract porcdb_2,exttrailsource ./dirdat/w1
GGSCI (host-172-16-21-61) 8> add rmttrail ./dirdat/w1,extract porcdb_2,megabytes 5
GGSCI (host-172-16-21-61) 9> start manager
GGSCI (mysql as ) 8> start eorcdb_2
GGSCI (mysql as ) 8> start porcdb_2

--oracle12c replicat 需要连接到 PLUG 数据库
GGSCI (sy-PC) 1> DBLOGIN USERID c##cdb2ggs@cdb2pp1, PASSWORD ggs
Successfully logged into database PP1.
GGSCI (host-172-16-21-61) 5> add checkpointtable pp.c##cdb2ggs.ggs_checkpoint

GGSCI> edit param ./GLOBALS
GGSCHEMA c##cdb2ggs
CHECKPOINTTABLE pp.c##cdb2ggs.ggs_checkpoint

GGSCI (mysql as ) 2> edit param rorcdb_2
输入:
replicat rorcdb_2
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
userid c##cdb2ggs@cdb2pp1,password ggs
handlecollisions
ASSUMETARGETDEFS
applynoopupdates
REPERROR DEFAULT,DISCARD
DISCARDFILE ./dirrpt/rorcdb.dsc,PURGE
MAP pp.orclcdb.*, TARGET pp.cdb2.* ;

GGSCI (h70) 3> add replicat rorcdb_2,INTEGRATED,exttrail ./dirdat/w1
GGSCI (h70) 12> start rorcdb_2

/***************************************************************************************************/
九 oracle orclcdb 到 informix info 的初始化
/***************************************************************************************************/
--1 orclcdb 端复制到 informix 端
GGSCI (sy-PC) 1> DBLOGIN USERID c##orclcdbggs@orclcdb, PASSWORD ggs
Successfully logged into database CDB$ROOT.

GGSCI (sy-PC) 1> GGSCI (node1) 2> add trandata pp.orclcdb.*

GGSCI (host-172-16-21-61) 3> edit param eorcl_1
extract eorcl_1
GETTRUNCATES
FETCHOPTIONS FETCHPKUPDATECOLS
userid c##orclcdbggs@orclcdb,password ggs
TRANLOGOPTIONS EXCLUDEUSER c##orclcdbggs
SOURCECATALOG pp
exttrail ./dirdat/v2
table orclcdb.*;

GGSCI (host-172-16-21-61) 4> add extract eorcl_1,INTEGRATED TRANLOG,begin now
GGSCI (host-172-16-21-61) 5> add exttrail ./dirdat/v2,extract eorcl_1,megabytes 5

--注册 Register the Integrated Extract
GGSCI (host-172-16-21-61) 6> REGISTER EXTRACT eorcl_1 DATABASE CONTAINER (pp)

GGSCI (host-172-16-21-61) 6> edit param porcl_1
输入:
extract porcl_1
passthru
userid c##orclcdbggs@orclcdb,password ggs
rmthost 192.168.6.200,mgrport 7809
rmttrail ./dirdat/v2
table pp.orclcdb.*;

GGSCI (host-172-16-21-61) 7> add extract porcl_1,exttrailsource ./dirdat/v2
GGSCI (host-172-16-21-61) 8> add rmttrail ./dirdat/v2,extract porcl_1,megabytes 5
GGSCI (host-172-16-21-61) 9> start manager

--2 informix 端
GGSCI (mysql as ) 1> dblogin sourcedb info userid informix password informix
GGSCI (mysql as)) 5> ADD CHECKPOINTTABLE info.informix.ggs_checkpoint

GGSCI (mysql as ) 2> edit param rinfo_1
输入:
replicat rinfo_1
sourcedefs /home/informix/ggs/dirdef/orclcdb.def
handlecollisions
assumetargetdefs
GETTRUNCATES
applynoopupdates
REPERROR DEFAULT,DISCARD
TARGETDB info userid informix password informix
Map pp.orclcdb.*,target info.informix.*;

GGSCI (mysql as ) 7> add replicat rinfo_1,exttrail ./dirdat/v2,checkpointtable info.informix.ggs_checkpoint
GGSCI (mysql as ) 8> start manager

dbaccess info -

SQL> create table t1 (a int,b int);
SQL> create table t2 (a int,b int);
SQL> create table t3 (a int,b int);

--odbc.ini
info=IBM INFORMIX ODBC DRIVER

[info]
Driver=/home/informix/gbase/lib/cli/iclis09b.so
Description=IBM INFORMIX ODBC DRIVER
Database=info
LogonID=informix
pwd=informix
Servername=gbaseserver

[informix@node1 gbase]$ ./catalog info

Using specified DSN : info
STEP 1 done...connected to database
Table Name t1
Column: a
Column: b
Table Name t2
Column: a
Column: b
Table Name t3
Column: a
Column: b

3 Configuring a Direct Bulk Load to SQL*Loader (page 15-4)
1）源端 oracle orclcdb
GGSCI (host-172-16-21-61) 3> edit param initext

EXTRACT initext
userid c##orclcdbggs@orclcdb, PASSWORD ggs
RMTHOST 192.168.6.200, MGRPORT 7809
RMTTASK replicat, GROUP initrep
table pp.orclcdb.*;

GGSCI (host-172-16-21-61) 3> ADD EXTRACT initext, SOURCEISTABLE

4 创建 defgen.def文件

1)下面在 ORACLE 端数据库机器上执行

[oracle@host-172-16-21-61 dirprm]$ vi defgeninfo.prm
输入:
defsfile ./dirdef/orclcdb.def purge
userid c##orclcdbggs@orclcdb,password ggs
table pp.orclcdb.*;

cd c:\ggs12c
defgen paramfile ./dirprm/defgeninfo.prm

将 orclcdb.def 上传到 INFORMIX 端

5 目的端 informix
GGSCI (mysql as ) 1> dblogin sourcedb info userid informix password informix

GGSCI (host-172-16-21-61) 3> edit param initrep
输入:
replicat initrep
sourcedefs /home/informix/ggs/dirdef/orclcdb.def
handlecollisions
assumetargetdefs
GETTRUNCATES
applynoopupdates
REPERROR DEFAULT,DISCARD
TARGETDB info userid informix password informix
Map pp.orclcdb.*,target info.informix.*;

GGSCI (host-172-16-21-61) 3> ADD REPLICAT initrep, SPECIALRUN

GGSCI (host-172-16-21-61) 3> start eorcl_1
GGSCI (host-172-16-21-61) 3> start porcl_1

GGSCI (host-172-16-21-61) 3> start initext

GGSCI (node1 as c##cdb2ggs@cdb2/CDB$ROOT) 134> info extract initext

EXTRACT INITEXT Last Started 2017-02-13 13:42 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table PP.CDB2.T3
2017-02-13 13:42:28 Record 2
Task SOURCEISTABLE

GGSCI (node1 as c##orclcdbggs@orclcdb/PP) 71> info replicat initrep

GGSCI (host-172-16-21-61) 3> start rinfo_1

--oracle 插入初始化数据
create or replace procedure gendata
(num in number,start_cn in number ) is
i number;
k number;
begin
i:=start_cn;
k:=1;
i:=start_cn;
loop
insert into t1 values(i,1) ;
insert into t2 values(i,2) ;
insert into t3 values(i,3) ;

i:=i+1;
k:=k+1;
if k=1001 then
k:=1;
commit;
end if;
exit when i>=num+start_cn ;

end loop;
commit;
end;
/

set timing on
truncate table t1;
truncate table t2;
truncate table t3;

exec gendata(1000009,1);
Elapsed: 00:03:31.57

select count(1) from t1;
select count(1) from t2;
select count(1) from t3;

COUNT(1)
----------
1000009

create or replace procedure gendata2
(num in number,start_cn in number ) is
i number;
k number;
begin
i:=start_cn;
k:=1;
i:=start_cn;
loop
insert into t1 values(i,11) ;
insert into t2 values(i,12) ;
insert into t3 values(i,13) ;

i:=i+1;
k:=k+1;
if k=1001 then
k:=1;
commit;
end if;
exit when i>=num+start_cn ;

end loop;
commit;
end;
/

exec gendata2(100000,2000009);

select count(*) from t1;
select count(*) from t2;
select count(8) from t3;
COUNT(8)
----------
1100009

set isolation to {dirty read|committed read}

set isolation to dirty read ;
select count(*) from t3;
select count(*) from t2;
select count(*) from t1;

select count(1) from t1;

insert into t1 values(9,111) ;

create or replace procedure gendata3
(num in number,start_cn in number ) is
i number;
k number;
begin
i:=start_cn;
k:=1;
i:=start_cn;
loop
insert into t1 values(i,31) ;
insert into t2 values(i,32) ;
insert into t3 values(i,33) ;

i:=i+1;
k:=k+1;
if k=1001 then
k:=1;
commit;
end if;
exit when i>=num+start_cn ;

end loop;
commit;
end;
/

exec gendata3(100000,3000009);
exec gendata3(100000,3000009);
COUNT(*)
----------
1300009

SQL> select count(*) from t1;
select count(*) from t2;
select count(*) from t3;

GGSCI (node1 as c##orclcdbggs@orclcdb/CDB$ROOT) 216> info extract initext

EXTRACT INITEXT Last Started 2017-02-14 13:53 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table PP.ORCLCDB.T3
2017-02-14 14:01:09 Record 1300009
Task SOURCEISTABLE

GGSCI (node1 as informix@info/info) 20> info replicat initrep

REPLICAT INITREP Initialized 2017-02-14 13:47 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:14:19 ago)
Log Read Checkpoint Not Available
Task SPECIALRUN

/*******************************************************************************/
十 Configuring a Load from an Input File to SQL*Loader 初始化方法
/*******************************************************************************/

1）源端 oracle orclcdb
GGSCI (host-172-16-21-61) 3> edit param initext2

EXTRACT initext2
userid c##orclcdbggs@orclcdb, PASSWORD ggs
RMTHOST 192.168.6.200, MGRPORT 7890
FORMATASCII, SQLLOADER
RMTFILE ./dirdat/in
table pp.orclcdb.*;

GGSCI (host-172-16-21-61) 3> ADD EXTRACT initext2, SOURCEISTABLE
GGSCI (host-172-16-21-61) 3> start initext2

GGSCI (node1 as c##cdb2ggs@cdb2/CDB$ROOT) 134> info extract initext2

--start initext2 目标端生成 TRAIL 文件：多个表的的数据生成在一个文件里。需要一个一个表的生成？

2) 目的端 cdb2
GGSCI (mysql as ) 1> DBLOGIN USERID c##cdb2ggs@cdb2pp1, PASSWORD ggs
GGSCI (host-172-16-21-61) 5> add checkpointtable pp.c##cdb2ggs.ggs_checkpoint
GGSCI> edit param ./GLOBALS
GGSCHEMA c##cdb2ggs
CHECKPOINTTABLE pp.c##cdb2ggs.ggs_checkpoint

GGSCI (host-172-16-21-61) 3> edit param initrep2
输入:
replicat initrep2
GENLOADFILES sqlldr.tpl
userid c##cdb2ggs@cdb2pp1,password ggs
handlecollisions
assumetargetdefs
applynoopupdates
REPERROR DEFAULT,DISCARD
EXTFILE ./dirdat/in
MAP pp.orclcdb.*, TARGET pp.cdb2.* ;

GGSCI (host-172-16-21-61) 3> ADD REPLICAT initrep2, SPECIALRUN

GGSCI (host-172-16-21-61) 3> start initrep2

GGSCI (node1 as c##cdb2ggs@cdb2/CDB$ROOT) 134> info REPLICAT initrep2

EXTRACT initext2 Last Started 2017-02-13 13:42 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table PP.CDB2.T3
2017-02-13 13:42:28 Record 2
Task SOURCEISTABLE

-- start initrep2 在 ggs 目录下生成 ctl和run 文件。
-- 需要为 run 文件授权 chmod +x *.run
-- 其中的 userid 要注意是否更改为正确的用户名和网络服务名
-- 注意数据文件的位置

--附录：相关文件内容
[oracle@node1 ggs12c2]$ cd /u01/ggs12c2
[oracle@node1 ggs12c2]$ ll *.ctl
-rw-r----- 1 oracle oinstall 260 Feb 14 16:39 T1.ctl
-rw-r----- 1 oracle oinstall 260 Feb 14 16:39 T2.ctl
-rw-r----- 1 oracle oinstall 260 Feb 14 16:39 T3.ctl
[oracle@node1 ggs12c2]$ ll *.run
-rw-r----- 1 oracle oinstall 59 Feb 14 16:39 T1.run
-rw-r----- 1 oracle oinstall 59 Feb 14 16:39 T2.run
-rw-r----- 1 oracle oinstall 59 Feb 14 16:39 T3.run

[oracle@node1 ggs12c2]$ chmod +x *.run
[oracle@node1 ggs12c2]$ export ORACLE_SID=cdb2

[oracle@node1 ggs12c2]$ more T1.run
sqlldr userid=cdb2/oracle@cdb2pp1 control=T1 log=T1 direct=true
[oracle@node1 ggs12c2]$ more T2.run
sqlldr userid=c##cdb2ggs/ggs control=T2 log=T2 direct=true
[oracle@node1 ggs12c2]$ more T3.run
sqlldr userid=c##cdb2ggs/ggs control=T3 log=T3 direct=true

[oracle@node1 ggs12c2]$ more T1.ctl
unrecoverable
load data
infile T1.dat
truncate
into table T1
(
A position(4:53)
nullif (3)='Y'
, B position(55:104)
nullif (54)='Y'
)
[oracle@node1 ggs12c2]$ more T2.ctl
unrecoverable
load data
infile T2.dat
truncate
into table T2
(
A position(4:53)
nullif (3)='Y'
, B position(55:104)
nullif (54)='Y'
)
[oracle@node1 ggs12c2]$ more T3.ctl
unrecoverable
load data
infile T3.dat
truncate
into table T3
(
A position(4:53)
nullif (3)='Y'
, B position(55:104)
nullif (54)='Y'
)

[oracle@node1 ggs12c2]$ more sqlldr.tpl
#
# File Names
#
controlfile ?target.ctl
runfile ?target.run

#
# Run File Template
#
sqlldr userid=?pw control=?target log=?target direct=true
#
# Control File Template
#
unrecoverable
load data
infile ?source.dat
truncate
into table ?target

/*******************************************************************************/
十一Configuring a Direct Bulk Load to SQL*Loader 初始化方法
/*******************************************************************************/

1）源端 oracle orclcdb
GGSCI (host-172-16-21-61) 3> edit param initext3

EXTRACT initext3
userid c##orclcdbggs@orclcdb, PASSWORD ggs
RMTHOST 192.168.6.200, MGRPORT 7890
RMTTASK replicat, GROUP initrep3
table pp.orclcdb.*;

GGSCI (host-172-16-21-61) 3> ADD EXTRACT initext3, SOURCEISTABLE

5 目的端 informix
GGSCI (mysql as ) 1> DBLOGIN USERID c##cdb2ggs@cdb2pp1, PASSWORD ggs

GGSCI (host-172-16-21-61) 3> edit param initrep3
输入:
replicat initrep3
userid c##cdb2ggs@cdb2pp1,password ggs
BULKLOAD
handlecollisions
assumetargetdefs
applynoopupdates
REPERROR DEFAULT,DISCARD
MAP pp.orclcdb.*, TARGET pp.cdb2.* ;

GGSCI (host-172-16-21-61) 3> ADD REPLICAT initrep3, SPECIALRUN

GGSCI (host-172-16-21-61) 3> start initext3

GGSCI (node1 as c##cdb2ggs@cdb2/CDB$ROOT) 134> info extract initext3

GGSCI (node1 as c##cdb2ggs@cdb2/CDB$ROOT) 134> info REPLICAT initrep3

/*******************************************************************************/
十二 oracle 12.1 非CDB( nocdb) 复制到 ORACLE 12.1 CDB(orclcdb)
/*******************************************************************************/

-- 1 非CDB( nocdb)
安装位置 /u01/ggs12cnocdb

SQL> conn sys/oracle@nocdb as sysdba
sql> alter system set RECYCLEBIN =OFF SCOPE=SPFILE;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY,UNIQUE INDEX) COLUMNS;
SQL> alter system switch logfile;
SQL> select supplemental_log_data_min from v$database;
SQL> alter system set enable_goldengate_replication=true;
SQL> ALTER DATABASE FORCE LOGGING;
SQL> ARCHIVE LOG LIST;
SQL> startup force;

SQL> conn sys/oracle@nocdb as sysdba

SQL> create user nocdb identified by oracle;
SQL> grant dba to nocdb;
SQL> conn nocdb/oracle@nocdb
SQL> create table t1 (a int,b int);
SQL> create table t2 (a int,b int);
SQL> create table t3 (a int,b int);

SQL> conn sys/oracle@nocdb as sysdba
sql> Create user nocdbggs identified by ggs;
sql> exec dbms_goldengate_auth.grant_admin_privilege('nocdbggs',Container=>'ALL');
SQL> grant dba to nocdbggs ;
SQL> connect nocdbggs/ggs@nocdb

[oracle@node1 ggs12c]$ cd /u01/ggs12cnocdb
GGSCI (sy-PC) 1> CREATE SUBDIRS

GGSCI (host-172-16-21-61) ) 2> edit params mgr
PORT 7899
DYNAMICPORTLIST 7845-7911
--AUTOSTART ER *
--AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
LAGREPORTHOURS 1
LAGCRITICALMINUTES 45

--2 nocdb 端复制到 ORCLCDB端
GGSCI (sy-PC) 1> DBLOGIN USERID nocdbggs@nocdb, PASSWORD ggs

GGSCI (sy-PC) 1> GGSCI (node1) 2> add trandata nocdb.*

GGSCI (host-172-16-21-61) 3> edit param enocdb_1
extract enocdb_1
GETTRUNCATES
FETCHOPTIONS FETCHPKUPDATECOLS
userid nocdbggs@nocdb,password ggs
TRANLOGOPTIONS EXCLUDEUSER nocdbggs
SOURCECATALOG pp
exttrail ./dirdat/z1
table nocdb.*;

GGSCI (host-172-16-21-61) 4> add extract enocdb_1,TRANLOG,begin now
GGSCI (host-172-16-21-61) 5> add exttrail ./dirdat/z1,extract enocdb_1,megabytes 5

GGSCI (host-172-16-21-61) 6> edit param pnocdb_1
输入:
extract pnocdb_1
passthru
userid nocdbggs@nocdb,password ggs
rmthost 192.168.6.200,mgrport 7878
rmttrail ./dirdat/z1
table nocdb.*;

GGSCI (host-172-16-21-61) 7> add extract pnocdb_1,exttrailsource ./dirdat/z1
GGSCI (host-172-16-21-61) 8> add rmttrail ./dirdat/z1,extract pnocdb_1,megabytes 5
GGSCI (host-172-16-21-61) 9> start manager

--3 ORCLCDB 端
--oracle12c replicat 需要连接到 PLUG 数据库
GGSCI (sy-PC) 1> DBLOGIN USERID c##orclcdbggs@orclcdbpp1, PASSWORD ggs
Successfully logged into database PP.

GGSCI (host-172-16-21-61) 5> add checkpointtable pp.c##orclcdbggs.ggs_checkpoint

Logon catalog name PP will be used for table specification PP.c##orclcdbggs.ggs_checkpoint.
Successfully created checkpoint table PP.c##orclcdbggs.ggs_checkpoint.

GGSCI (mysql as ) 2> edit param rnocdb_1
输入:
replicat rnocdb_1
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
userid c##orclcdbggs@orclcdbpp1,password ggs
handlecollisions
ASSUMETARGETDEFS
applynoopupdates
GETTRUNCATES
REPERROR DEFAULT,DISCARD
DISCARDFILE ./dirrpt/rcdb2.dsc,PURGE
MAP nocdb.*, TARGET pp.orclcdb.* ;

GGSCI (h70) 3> add replicat rnocdb_1,INTEGRATED,exttrail ./dirdat/z1

GGSCI> edit param ./GLOBALS
GGSCHEMA c##orclcdbggs
CHECKPOINTTABLE pp.c##orclcdbggs.ggs_checkpoint

--4 orclcdb 复制到nocdb

GGSCI (sy-PC) 1> DBLOGIN USERID c##orclcdbggs@orclcdb, PASSWORD ggs
Successfully logged into database CDB$ROOT.

GGSCI (sy-PC) 1> GGSCI (node1) 2> add trandata pp.orclcdb.*

GGSCI (host-172-16-21-61) 3> edit param eorcdb_8
extract eorcdb_8
GETTRUNCATES
FETCHOPTIONS FETCHPKUPDATECOLS
userid c##orclcdbggs@orclcdb,password ggs
TRANLOGOPTIONS EXCLUDEUSER c##orclcdbggs
SOURCECATALOG pp
exttrail ./dirdat/x1
table orclcdb.*;

GGSCI (host-172-16-21-61) 4> add extract eorcdb_8,INTEGRATED TRANLOG,begin now
GGSCI (host-172-16-21-61) 5> add exttrail ./dirdat/x1,extract eorcdb_8,megabytes 5

--注册 Register the Integrated Extract
GGSCI (host-172-16-21-61) 6> REGISTER EXTRACT eorcdb_8 DATABASE CONTAINER (pp)

GGSCI (host-172-16-21-61) 6> edit param porcdb_8
输入:
extract porcdb_8
passthru
userid c##orclcdbggs@orclcdb,password ggs
rmthost 192.168.6.200,mgrport 7899
rmttrail ./dirdat/x1
table pp.orclcdb.*;

GGSCI (host-172-16-21-61) 7> add extract porcdb_8,exttrailsource ./dirdat/x1
GGSCI (host-172-16-21-61) 8> add rmttrail ./dirdat/x1,extract porcdb_8,megabytes 5
GGSCI (host-172-16-21-61) 9> start manager
GGSCI (mysql as ) 8> start eorcdb_8
GGSCI (mysql as ) 8> start porcdb_8

--oracle12c replicat 需要连接到 PLUG 数据库
GGSCI (sy-PC) 1> DBLOGIN USERID nocdbggs@nocdb, PASSWORD ggs
GGSCI (host-172-16-21-61) 5> add checkpointtable nocdbggs.ggs_checkpoint

GGSCI> edit param ./GLOBALS
GGSCHEMA nocdbggs
CHECKPOINTTABLE nocdbggs.ggs_checkpoint

GGSCI (mysql as ) 2> edit param rorcdb_8
输入:
replicat rorcdb_8
userid nocdbggs@nocdb,password ggs
handlecollisions
ASSUMETARGETDEFS
applynoopupdates
GETTRUNCATES
REPERROR DEFAULT,DISCARD
DISCARDFILE ./dirrpt/rnocdb.dsc,PURGE
MAP pp.orclcdb.*, TARGET nocdb.* ;

GGSCI (h70) 3> add replicat rorcdb_8,checkpointtable nocdbggs.ggs_checkpoint,exttrail ./dirdat/x1
GGSCI (h70) 12> start rorcdb_8

/*******************************************************************************/
十三 oracle 12.1 nocdb 复制到INFORMIX
-- 1 非CDB( nocdb)
安装位置 /u01/ggs12cnocdb

SQL> conn sys/oracle@nocdb as sysdba
sql> alter system set RECYCLEBIN =OFF SCOPE=SPFILE;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY,UNIQUE INDEX) COLUMNS;
SQL> alter system switch logfile;
SQL> select supplemental_log_data_min from v$database;
SQL> alter system set enable_goldengate_replication=true;
SQL> ALTER DATABASE FORCE LOGGING;
SQL> ARCHIVE LOG LIST;
SQL> startup force;

SQL> conn sys/oracle@nocdb as sysdba

SQL> create user nocdb identified by oracle;
SQL> grant dba to nocdb;
SQL> conn nocdb/oracle@nocdb
SQL> create table t1 (a int,b int);
SQL> create table t2 (a int,b int);
SQL> create table t3 (a int,b int);

SQL> conn sys/oracle@nocdb as sysdba
sql> Create user nocdbggs identified by ggs;
sql> exec dbms_goldengate_auth.grant_admin_privilege('nocdbggs',Container=>'ALL');
SQL> grant dba to nocdbggs ;
SQL> connect nocdbggs/ggs@nocdb

[oracle@node1 ggs12c]$ cd /u01/ggs12cnocdb
GGSCI (sy-PC) 1> CREATE SUBDIRS

GGSCI (host-172-16-21-61) ) 2> edit params mgr
PORT 7899
DYNAMICPORTLIST 7845-7911
--AUTOSTART ER *
--AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
LAGREPORTHOURS 1
LAGCRITICALMINUTES 45

--2 nocdb 端复制到 ORCLCDB端
GGSCI (sy-PC) 1> DBLOGIN USERID nocdbggs@nocdb, PASSWORD ggs

GGSCI (sy-PC) 1> GGSCI (node1) 2> add trandata nocdb.*

GGSCI (host-172-16-21-61) 3> edit param enocdb_7
extract enocdb_7
GETTRUNCATES
FETCHOPTIONS FETCHPKUPDATECOLS
userid nocdbggs@nocdb,password ggs
TRANLOGOPTIONS EXCLUDEUSER nocdbggs
SOURCECATALOG pp
exttrail ./dirdat/z3
table nocdb.*;

GGSCI (host-172-16-21-61) 4> add extract enocdb_7,TRANLOG,begin now
GGSCI (host-172-16-21-61) 5> add exttrail ./dirdat/z3,extract enocdb_7,megabytes 5

GGSCI (host-172-16-21-61) 6> edit param pnocdb_7
输入:
extract pnocdb_7
passthru
userid nocdbggs@nocdb,password ggs
rmthost 192.168.6.200,mgrport 7809
rmttrail ./dirdat/z3
table nocdb.*;

GGSCI (host-172-16-21-61) 7> add extract pnocdb_7,exttrailsource ./dirdat/z3
GGSCI (host-172-16-21-61) 8> add rmttrail ./dirdat/z3,extract pnocdb_7,megabytes 5
GGSCI (host-172-16-21-61) 9> start manager

--3 创建 defgen.def文件

1)下面在 ORACLE 端数据库机器上执行
cd /u01/ggs12cnocdb/dirprm

[oracle@host-172-16-21-61 dirprm]$ vi defgennocdb.prm
输入:
defsfile ./dirdef/nocdb.def purge
userid nocdbggs@nocdb,password ggs
table nocdb.*;

cd /u01/ggs12cnocdb
./defgen paramfile ./dirprm/defgennocdb.prm

将 nocdb.def 上传到 INFORMIX 端

--4 INFORMIX 端
dbaccess info -

SQL> create table t1 (a int,b int);
SQL> create table t2 (a int,b int);
SQL> create table t3 (a int,b int);

--为双向复制创建用户，授权
[informix@node1 dbs]$ dbaccess sscdb -
> GRANT DBA TO rep;

--2） ODBC
info=IBM INFORMIX ODBC DRIVER

[info]
Driver=/home/informix/gbase/lib/cli/iclis09b.so
Description=IBM INFORMIX ODBC DRIVER
Database=info
LogonID=informix
pwd=informix
Servername=gbaseserver

--3）测试odbc
[informix@node1 ~]$ cd gbase/
[informix@node1 gbase]$ ./catalog info
[informix@node1 ggs]$ ggsci

Oracle GoldenGate Command Interpreter for Informix
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401

GGSCI (node1) 3> edit params mgr
port 7809
AUTOSTART REPLICAT r*
AUTORESTART REPLICAT r*, RETRIES 4, WAITMINUTES 4
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3

GGSCI (mysql as ) 1> dblogin sourcedb info userid informix password informix
GGSCI (mysql as)) 5> ADD CHECKPOINTTABLE info.informix.ggs_checkpoint

GGSCI (mysql as ) 2> edit param rinfo_7
输入:
replicat rinfo_7
sourcedefs /home/informix/ggs/dirdef/nocdb.def
handlecollisions
assumetargetdefs
GETTRUNCATES
applynoopupdates
REPERROR DEFAULT,DISCARD
TARGETDB sscdb userid rep password rep
Map nocdb.*,target info.informix.*;

GGSCI (mysql as ) 7> add replicat rinfo_7,exttrail ./dirdat/z3,checkpointtable info.informix.ggs_checkpoint
GGSCI (mysql as ) 8> start manager
GGSCI (mysql as ) 8> start rinfo_7

5 INFORMIX 向 ORACLE12C nocdb 复制
GGSCI (mysql as ) 1>dblogin sourcedb syscdcv1 userid informix password informix

GGSCI (node1 as informix@syscdcv1/syscdcv1) 3> add trandata info.informix.*

GGSCI (host-172-16-21-61) 3> edit param einfo_8
输入:
extract einfo_8
SOURCEDB syscdcv1 userid informix password informix
GETTRUNCATES
FETCHOPTIONS FETCHPKUPDATECOLS
TRANLOGOPTIONS EXCLUDEUSERID 1103
exttrail ./dirdat/z6
table info.informix.*;

GGSCI (node1 as informix@infdrv3/info) 35> add extract einfo_8,VAM,begin now
EXTRACT added..

GGSCI (node1 as informix@infdrv3/info) 36> add exttrail ./dirdat/z6,extract einfo_8,megabytes 5
EXTTRAIL added.

GGSCI (host-172-16-21-61) 6> edit param pinfo_8
输入:
extract pinfo_8
passthru
SOURCEDB syscdcv1 userid informix password informix
rmthost 192.168.6.200,mgrport 7899
rmttrail ./dirdat/z6
table info.informix.*;

GGSCI (node1 as informix@infdrv3/info) 39> add extract pinfo_8,exttrailsource ./dirdat/z6
EXTRACT added.

GGSCI (node1 as informix@infdrv3/info) 40> add rmttrail ./dirdat/z6,extract pinfo_8,megabytes 5
RMTTRAIL added.

6 informix 端准备DEF 文件

su - informix
cd /home/informix/ggs/dirprm
[informix@node1 dirprm]$ vi informixinfo.prm
输入:
defsfile ./dirdef/informixinfo.def purge
SOURCEDB info userid informix password informix
table info.informix.*;

cd /home/informix/ggs
./defgen paramfile ./dirprm/informixinfo.prm
su - root
cd /home/informix/ggs/dirdef/
cp /home/informix/ggs/dirdef/informixinfo.def /u01/ggs12cnocdb/dirdef/
chown -R oracle:oinstall /u01/ggs12cnocdb/dirdef/
chmod -R 777 /u01/ggs12cnocdb/dirdef/
chmod -R 777 /u01/ggs12cnocdb/dirdef/
chmod -R 777 /home/informix/ggs/dirdat

7 oracle12c replicat
GGSCI (sy-PC) 1> DBLOGIN USERID nocdbggs@nocdb, PASSWORD ggs

--nonintegrated replicat 需要 checkpointtable
GGSCI (host-172-16-21-61) 5> add checkpointtable nocdbggs.ggs_checkpoint

GGSCI (mysql as ) 2> edit param rnocdb_8
输入:
replicat rnocdb_8
userid nocdbggs@nocdb,password ggs
sourcedefs /u01/ggs12cnocdb/dirdef/informixinfo.def
handlecollisions
ASSUMETARGETDEFS
applynoopupdates
REPERROR DEFAULT,DISCARD
DISCARDFILE ./dirrpt/rnocdb.dsc,PURGE
MAP info.informix.*, TARGET nocdb.* ;


GGSCI (h70) 3> add replicat rnocdb_8,checkpointtable nocdbggs.ggs_checkpoint,exttrail ./dirdat/z6
GGSCI (h70) 12> start rnocdb_8

-- INTEGRATED replicat： DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
-- add replicat rnocdb_8,INTEGRATED,exttrail ./dirdat/c2

GGSCI (mysql as ) 2> edit param rnocdb_8
输入:
replicat rnocdb_8
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
userid c##ggs@pp1,password ggs
sourcedefs /u01/ggs12c/dirdef/informixsscdb.def
handlecollisions
ASSUMETARGETDEFS
applynoopupdates
REPERROR DEFAULT,DISCARD
DISCARDFILE ./dirrpt/rsscdb.dsc,PURGE
MAP sscdb.informix.*, TARGET pp1.sscdb.* ;

GGSCI (h70) 3> add replicat rnocdb_8,INTEGRATED,exttrail ./dirdat/c2
GGSCI (h70) 12> start rnocdb_8

/*******************************************************************************/
十四 dbfs oracle12.1
/*******************************************************************************/
1 数据库中创建 DBFS 用户和文件系统
1） Creating a DBFS filesystem (in the database)
SQL> CREATE TABLESPACE dbfs_goldengate datafile '/home/oracle/data/dbfs/dbfs_goldengate.dbf' size 1G autoextend on next 1G;

2） Create a user to own the filesystem:

SQL> CREATE USER dbfs_admin IDENTIFIED BY oracle
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE dbfs_goldengate
QUOTA UNLIMITED ON dbfs_goldengate;
GRANT create session, resource, create view, dbfs_role TO dbfs_admin;

3） Create the filesystems:
sqlplus <username>/<password>@<database>
@$ORACLE_HOME/rdbms/admin/dbfs_create_filesystem.sql <tablespace name>
<file system name>

SQL> connect dbfs_admin/oracle@nocdb
@?/rdbms/admin/dbfs_create_filesystem.sql DBFS_GOLDENGATE TESTDB1

注：
dbfs_create_filesystem.sql creates a partitioned file system.

Partitioning is the best performing and scalable way to create a file system in DBFS.
Multiple physical segments are created in the database and files are distributed randomly in these partitions.
However, space cannot be shared between partitions.
So it is possible for one partition to run out of space even when other partitions have space.
This is usually not an issue if file system size is big compared to the size of the individual files.
However if file sizes are a big percentage of the file system size, it can cause ENOSPC error to happen even if the file system is not full.
Another implication of partitioning is that a "rename" operation can require the file to be rewritten. This can be expensive if file is a big file.

dbfs_create_filesystem_advanced.sql can be used to create a non-partitioned file system.

sqlplus <username>/<password>@<database>
@$ORACLE_HOME/rdbms/admin/dbfs_create_filesystem_advanced.sql
<tablespace name> <file system name>
<compress-high | compress-medium | compress-low | nocompress>
<deduplicate | nodeduplicate>
<encrypt | noencrypt>
<partition | non-partition>

sqlplus dbfs2/dbfs2@ora11p
@$ORACLE_HOME/rdbms/admin/dbfs_create_filesystem_advanced.sql
USERS
TESTDBFS2
compress-high
deduplicate
noencrypt
partition

4）Dropping a File System
sqlplus <username>/<password>@<database>
@$ORACLE_HOME/rdbms/admin/dbfs_drop_filesystem.sql <file system name>

5） You can check that the filesystems were created successfully with the following SQL:

SQL> col created for a25
SQL> col store_mount for a30
SQL> SELECT store_mount, store_name, created
FROM table(dbms_dbfs_content.listmounts);

STORE_MOUNT STORE_NAME
------------------------------ --------------------------------
CREATED
-------------------------
TESTDB1 TESTDB1
15-2月 -17 09.41.05.68783
3 上午

2 创建文件系统
1） Mounting the filesystem at the OS level
mkdir -p /home/oracle/goldengate
chown oracle:oinstall /home/oracle/goldengate

3 安装，配置 fuse
1）检查kernel-devel 包
# rpm –q kernel-devel
如果没有安装，挂载安装盘后安装package

2）Install FUSE
[root@node1 ]$ gunzip fuse-3.0.0.tar.gz
[root@node1 ]$ tar -xvf fuse-3.0.0.tar
[root@node1 ]$ cd fuse-3.0.0

3) linux5
./configure -prefix=/usr -with-kernel=/usr/src/kernels/`uname -r`-`uname -p`
make
make install
depmod
modprobe fuse
chmod 666 /dev/fuse
echo "/sbin/modprobe fuse" >> /etc/rc.modules
ldconfig

4) 授权+x，开机可以自动加载 fuse
chmod +x /etc/rc.modules

5）查看fuse 是否加载
[root@node1 ~]# lsmod|grep fuse

6）加载FUSE
[root@node1 ~]# modprobe fuse
WARNING: Deprecated config file /etc/modprobe.conf, all config files belong into /etc/modprobe.d/.
[root@node1 ~]# lsmod|grep fuse
fuse 79892 0

4 dbfs_client Mounting a DBFS file system
1) 需要输入口令MOUNT
[oracle@node1 ~]$ dbfs_client dbfs_admin@node1:1521/nocdb /home/oracle/goldengate

2）避免输入口令,后台启动
[oracle@node1 ~]$ echo "oracle" > /home/oracle/dbfspassword

[oracle@node1 ~]$ nohup dbfs_client dbfs_admin@node1:1521/nocdb /home/oracle/goldengate < /home/oracle/dbfspassword &

--目录信息
[oracle@node1 ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
50G 45G 2.5G 95% /
tmpfs 1.9G 612M 1.3G 32% /dev/shm
/dev/sda1 477M 40M 412M 9% /boot
/dev/mapper/VolGroup-lv_home
45G 16G 27G 37% /home
/dev/sr0 3.7G 3.7G 0 100% /media/cdrom
dbfs-dbfs_admin@node1:1521/nocdb:/
1023M 120K 1023M 1% /home/oracle/goldengate

[oracle@node1 ~]$ nohup dbfs_client dbfs_admin@nocdb /home/oracle/goldengate < /home/oracle/dbfspassword &
[oracle@node1 /]$ df -h
dbfs-dbfs_admin@nocdb:/
1023M 120K 1023M 1% /home/oracle/goldengate

[oracle@node1 /]$ mount | grep dbfs
dbfs-dbfs_admin@nocdb:/ on /home/oracle/goldengate type fuse (rw,nosuid,nodev,max_read=1048576,default_permissions,user=oracle)

5 使用wallet
a Wallet Creation
1) To create a wallet issue the following commands as the "oracle" OS user.
[oracle@node1 /]$ mkdir -p $HOME/oracle/wallet
[oracle@node1 /]$ $ORACLE_HOME/bin/mkstore -wrl $HOME/oracle/wallet -create
PKI-01002: Invalid password:Passwords must have a minimum length of eight characters and contain
alphabetic characters combined with numbers or special characters.

[oracle@node1 /]$ $ORACLE_HOME/bin/mkstore -wrl $HOME/oracle/wallet -create
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter password:
Enter password again: 1111aaaa

2) Add the following lines to the "$ORACLE_HOME/network/admin/sqlnet.ora" file.
# vi $ORACLE_HOME/network/admin/sqlnet.ora
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = $HOME/oracle/wallet) ) )
SQLNET.WALLET_OVERRIDE = TRUE

3) Add the credentials to the wallet for a specific connect string. This could be the existing connect string (DB11G in mycase),
but in this example I've used something a little more specific (DB11G_DBFS_USER).
$ mkstore -wrl $HOME/oracle/wallet -createCredential DB11G_DBFS_USER dbfs_user dbfs_user

$ mkstore -wrl $HOME/oracle/wallet -createCredential nocdb dbfs_admin oracle
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Create credential oracle.security.client.connect_string1

4) Make sure the connect string is present in the "$ORACLE_HOME/network/admin/tnsnames.ora" file.
In this case I just copied my existing connect string and gave it the new name.
NOCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = nocdb)
)
)

dbfs =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = nocdb)
)
)

b The file system can now be mounted using the wallet.
[oracle@node1 ~]$ nohup dbfs_client -o wallet /@nocdb /home/oracle/goldengate &

[oracle@node1 ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
50G 45G 2.5G 95% /
tmpfs 1.9G 612M 1.3G 32% /dev/shm
/dev/sda1 477M 40M 412M 9% /boot
/dev/mapper/VolGroup-lv_home
45G 16G 27G 37% /home
dbfs-@nocdb:/ 1023M 120K 1023M 1% /home/oracle/goldengate

6 Using /etc/fstab
1) To allow mount operations from the "/etc/fstab", issue the following commands as the "root" user.
# groupadd fuse
# usermod -a -G fuse oracle

2) Add the following line into the "/etc/fstab" file.
# vi /etc/fstab
/sbin/mount.dbfs#/@nocdb /home/oracle/goldengate fuse rw,user,noauto 0 0

3)/etc/fuse.conf
# vi /etc/fuse.conf
user_allow_other

4) /sbin/mount.dbfs
--使用口令
# vi /sbin/mount.dbfs
#!/bin/bash
. /home/oracle/.bash_profile
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
nohup $ORACLE_HOME/bin/dbfs_client dbfs_admin@nocdb /home/oracle/goldengate < /home/oracle/dbfspassword &

/-------------------------------------------------------------------------------------------/
问题使用wallet 不通
--使用wallet 不通
# vi /sbin/mount.dbfs
#!/bin/bash
. /home/oracle/.bash_profile
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
nohup $ORACLE_HOME/bin/dbfs_client -o wallet /@nocdb /home/oracle/goldengate &

# vi /etc/fstab
/sbin/mount.dbfs#/@nocdb /home/oracle/goldengate fuse wallet,rw,user,noauto,allow_other 0 0

/-------------------------------------------------------------------------------------------/

# chmod 750 /sbin/mount.dbfs
# chgrp fuse /sbin/mount.dbfs

5) The filesystem should now be mounted using the following command from the "oracle" OS user.
[oracle@node1 ~]$ mount /home/oracle/goldengate
[oracle@node1 ~]$ df -h
dbfs-dbfs_admin@nocdb:/
1023M 160K 1023M 1% /home/oracle/goldengate

6) 开机自动启动
# vi /etc/rc.local
su - oracle -c "lsnrctl start"
su - oracle -c "dbstart"
su - oracle -c "mount /home/oracle/goldengate"

7) As before, the filesystem is unmounted using the following command.
$ fusermount -u /home/oracle/goldengate

7 dbfs_client 命令
1） Directory listing
[oracle@node1 ~]$ dbfs_client -o wallet /@nocdb --command ls -a -l /home/oracle/goldengate
drwxrwxrwx root root 0 Feb 16 13:41 /home/oracle/goldengate/TESTDB1

[oracle@node1 ~]$ dbfs_client dbfs_admin@nocdb --command ls -a -l /home/oracle/goldengate
Password:
drwxrwxrwx root root 0 Feb 16 13:41 /home/oracle/goldengate/TESTDB1

2）Make a directory and get directory listing.
[oracle@node1 ~]$ dbfs_client -o wallet /@nocdb --command mkdir /home/oracle/goldengate/TESTDB1/test_dir

[oracle@node1 /]$ dbfs_client -o wallet /@nocdb --command ls -a -l /home/oracle/goldengate/TESTDB1
drwxr-xr-x root root 0 Feb 15 17:41 /home/oracle/goldengate/TESTDB1/.sfs
drwxr-xr-x oracle oinstall 0 Feb 16 14:52 /home/oracle/goldengate/TESTDB1/test_dir
-rw-r--r-- oracle oinstall 0 Feb 16 13:41 /home/oracle/goldengate/TESTDB1/1.txt
drwxr-xr-x oracle oinstall 0 Feb 16 13:41 /home/oracle/goldengate/TESTDB1/aa
-rw-r--r-- oracle oinstall 0 Feb 16 13:41 /home/oracle/goldengate/TESTDB1/2.txt
drwxr-xr-x oracle oinstall 0 Feb 16 13:41 /home/oracle/goldengate/TESTDB1/bb

3）# Copy files to and from DBFS
[oracle@node1 /]$ dbfs_client -o wallet /@nocdb --command cp /home/oracle/1.ora /home/oracle/goldengate/TESTDB1/1.ora

4) # Remove file
[oracle@node1 /]$ dbfs_client -o wallet /@nocdb --command rm /home/oracle/goldengate/TESTDB1/1.ora
unlinking file /home/oracle/goldengate/TESTDB1/1.ora

[oracle@node1 /]$ dbfs_client -o wallet /@nocdb --command rm -fr /home/oracle/goldengate/TESTDB1/test_dir
[oracle@node1 /]$ dbfs_client -o wallet /@nocdb --command ls -a -l /home/oracle/goldengate/TESTDB1
drwxr-xr-x root root 0 Feb 15 17:41 /home/oracle/goldengate/TESTDB1/.sfs
-rw-r--r-- oracle oinstall 0 Feb 16 13:41 /home/oracle/goldengate/TESTDB1/1.txt
drwxr-xr-x oracle oinstall 0 Feb 16 13:41 /home/oracle/goldengate/TESTDB1/aa
-rw-r--r-- oracle oinstall 0 Feb 16 13:41 /home/oracle/goldengate/TESTDB1/2.txt
drwxr-xr-x oracle oinstall 0 Feb 16 13:41 /home/oracle/goldengate/TESTDB1/bb
/----------------------------------------------------------------------------------------------------------/
--错误处理

1） dbfs_client dbfs_admin@node1:1521/nocdb /home/oracle/goldengate
usage: /u01/app/oracle/product/12.1.0/db_1/bin/dbfs_client <db_user>@<db_server> [options] <mountpoint>
db_user: Name of Database user that owns DBFS content repository filesystem(s)
db_server: A valid connect string for Oracle database server
(for example, hrdb_host:1521/hrservice)
mountpoint: Path to mount Database File System(s)
All the file systems owned by the database user will be seen at the mountpoint.

Fail to load library libfuse.so.
A dynamic linking error occurred: (libfuse.so: cannot open shared object file: No such file or directory)

[root@node1 ~]# locate libfuse.so
/lib64/libfuse.so.2
/lib64/libfuse.so.2.8.3

[root@node1 ~]# ldconfig -p | grep fuse
libfuse3.so.3 (libc6,x86-64) => /usr/lib/libfuse3.so.3
libfuse3.so (libc6,x86-64) => /usr/lib/libfuse3.so
libfuse.so.2 (libc6,x86-64) => /lib64/libfuse.so.2

[oracle@node1 ~]$ ldd $ORACLE_HOME/bin/dbfs_client
linux-vdso.so.1 => (0x00007fffd15f9000)
libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00000037c1a00000)
librt.so.1 => /lib64/librt.so.1 (0x00000037b7600000)
libclntsh.so.12.1 => /u01/app/oracle/product/12.1.0/db_1/lib/libclntsh.so.12.1 (0x00007f7929aa1000)
libclntshcore.so.12.1 => /u01/app/oracle/product/12.1.0/db_1/lib/libclntshcore.so.12.1 (0x00007f792952f000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00000037b7200000)
libm.so.6 => /lib64/libm.so.6 (0x00000037b7a00000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00000037c2200000)
libc.so.6 => /lib64/libc.so.6 (0x00000037b6e00000)
libdl.so.2 => /lib64/libdl.so.2 (0x00000037b6a00000)
/lib64/ld-linux-x86-64.so.2 (0x00000037b6600000)
libmql1.so => /u01/app/oracle/product/12.1.0/db_1/lib/libmql1.so (0x00007f79292b7000)
libipc1.so => /u01/app/oracle/product/12.1.0/db_1/lib/libipc1.so (0x00007f7928f38000)
libnnz12.so => /u01/app/oracle/product/12.1.0/db_1/lib/libnnz12.so (0x00007f792882e000)
libons.so => /u01/app/oracle/product/12.1.0/db_1/lib/libons.so (0x00007f79285e9000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x00000037c7a00000)
libaio.so.1 => /lib64/libaio.so.1 (0x00007f79283e7000)

--解决方法：创建链接 libfuse.so
cd /u01/app/oracle/product/12.1.0/db_1/lib
# ln -s /lib64/libfuse.so.2 libfuse.so
[root@node1 lib]# chown oracle:oinstall libfuse.so

2）[oracle@node1 ~]$ dbfs_client dbfs_admin@node1:1521/nocdb /home/oracle/goldengate
fuse: failed to exec fusermount: Permission denied

[root@node1 lib]# ll /usr/bin/fusermount
lrwxrwxrwx. 1 root root 15 Nov 1 09:44 /usr/bin/fusermount -> /bin/fusermount

[root@node1 dev]# ls -al /dev/fuse
crw-rw-rw- 1 root root 10, 229 Feb 16 09:53 fuse

--解决方法：将 /dev/fuse 该为fuse 组，将oracle 加入fuse 组
[root@node1 dev]# chgrp fuse /dev/fuse

[root@node1 dev]# ls -al /dev/fuse
crw-rw-rw- 1 root fuse 10, 229 Feb 16 09:53 fuse

[root@node1 etc]# id oracle
uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1200(dba)
[root@node1 dev]# usermod -a -G fuse oracle

[root@node1 etc]# id oracle
uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),0(root),1200(dba)

[oracle@node1 sbin]$ rpm -qa | grep fuse
gvfs-fuse-1.4.3-26.el6.x86_64
fuse-2.8.3-5.el6.x86_64
fuse-libs-2.8.3-5.el6.x86_64
/----------------------------------------------------------------------------------------------------------/

5 Unmounting a DBFS
[root@node1 etc]# fusermount -u /home/oracle/goldengate

6 Mounting DBFS Through fstab

7 dbfs_client 命令
dbfs_client - This is the command interface to the filesystem,
allowing for filesystem commands that allow you to use standard UNIX commands to list directory contents (ls),
copy files (cp, rcp) and make sub-directories (mkdir).

fuse - The Filesystem in User Space (FUSE) is a server-side component.
It is copied in with the yum install kernel-devel command and installed as root on the server.

dbfs_client dbfs_admin@nocdb /home/oracle/goldengate < /home/oracle/dbfspassword --command ls -a -l

dbfs_client dbfs_admin@nocdb /home/oracle/goldengate --command ls -l -a

Examples
dbfs_client ETLUser@DBConnectString --command ls -l -a dbfs:/staging_area/directory1
dbfs_client ETLUser@DBConnectString --command cp -R /tmp/1-Jan-2009-dump dbfs:/staging_area
dbfs_client ETLUser@DBConnectString --command rm dbfs:/staging_area/hello.txt
dbfs_client ETLUser@DBConnectString --command mkdir dbfs:/staging_area/directory2

8 dbfs 使用
1) 创建文件系统，MOUNT
sql> conn dbfs_admin/oracle@nocdb
sql> exec dbms_dbfs_sfs.createFilesystem('user1_FS');
sql> exec dbms_dbfs_content.registerStore('user1_FS', 'posix', 'DBMS_DBFS_SFS');
sql> exec dbms_dbfs_content.mountStore('user1_FS', 'user1_mnt');
sql> commit;
/----------------------------------------------------------------------------------/
exec dbms_dbfs_sfs.createfilesystem('cc1');
exec dbms_dbfs_sfs.createfilesystem('cc2');
exec dbms_dbfs_content.registerStore('cc1','posix', 'DBMS_DBFS_SFS');
exec dbms_dbfs_content.registerStore('cc2','posix', 'DBMS_DBFS_SFS');

exec dbms_dbfs_content.mountStore('cc1', 'local');
exec dbms_dbfs_content.mountStore('cc2', 'remote',read_only => true);

SQL> select cc.store_name, tb.table_name, tb.ptable_name
from table(dbms_dbfs_sfs.listTables) tb,
table(dbms_dbfs_sfs.listfilesystems) cc
where cc.schema_name = tb.schema_name
and cc.table_name = tb.table_name
and cc.store_name in ('cc1', 'cc2')
;

exec dbms_dbfs_content.unmountStore('FS1', 'local');
exec dbms_dbfs_content.unmountStore('FS2', 'remote');

exec dbms_dbfs_content.unregisterStore('FS1');
exec dbms_dbfs_content.unregisterStore('FS2');

exec dbms_dbfs_sfs.DROPFILESYSTEM('FS1');
exec dbms_dbfs_sfs.DROPFILESYSTEM('FS2');

sql> exec dbms_dbfs_sfs.createFilesystem('user1_FS');
sql> exec dbms_dbfs_content.registerStore('user1_FS', 'posix', 'DBMS_DBFS_SFS');
sql> exec dbms_dbfs_content.mountStore('user1_FS', 'user1_mnt');
sql> commit;

exec dbms_dbfs_content.unmountStore('user1_FS', 'user1_mnt');
exec dbms_dbfs_content.unregisterStore('user1_FS');
exec dbms_dbfs_sfs.DROPFILESYSTEM('user1_FS');

exec dbms_dbfs_content.unmountStore('TESTDB1', 'TESTDB1');
exec dbms_dbfs_content.unregisterStore('TESTDB1');
exec dbms_dbfs_sfs.DROPFILESYSTEM('TESTDB1');

exec dbms_dbfs_content.unmountStore('fs1', 'fs1');
exec dbms_dbfs_content.unregisterStore('fs1');
exec dbms_dbfs_sfs.DROPFILESYSTEM('fs1');

exec dbms_dbfs_content.unmountStore('fs2', 'fs2');
exec dbms_dbfs_content.unregisterStore('fs2');
exec dbms_dbfs_sfs.DROPFILESYSTEM('fs2');

/----------------------------------------------------------------------------------/

2) 显示文件系统
SQL> select * from table(dbms_dbfs_content.listMounts);

STORE_NAME STORE_ID PROVIDER_NAME
-------------------------------- ---------- --------------------------------
PROVIDER_PKG PROVIDER_ID PROVIDER_VERSION
-------------------------------- ----------- --------------------------------
STORE_FEATURES STORE_GUID
-------------- ----------
STORE_MOUNT
--------------------------------------------------------------------------------
CREATED
---------------------------------------------------------------------------
MOUNT_PROPERTIES(PROPNAME, PROPVALUE, TYPECODE)
--------------------------------------------------------------------------------
TESTDB1 2658889534 sample1
"DBMS_DBFS_SFS" 3350646887 0.5.0
79822999 1120803510
TESTDB1
15-2月 -17 09.41.05.687833 上午
DBMS_DBFS_CONTENT_PROPERTIES_T(DBMS_DBFS_CONTENT_PROPERTY_T('principal', NULL, 9
), DBMS_DBFS_CONTENT_PROPERTY_T('owner', NULL, 9), DBMS_DBFS_CONTENT_PROPERTY_T(
'acl', NULL, 9), DBMS_DBFS_CONTENT_PROPERTY_T('asof', NULL, 187), DBMS_DBFS_CONT
ENT_PROPERTY_T('read_only', '0', 2), DBMS_DBFS_CONTENT_PROPERTY_T('feature_getat
tr_view', '"VSFS$_10"', 9))

user1_FS 4097913233 posix
"DBMS_DBFS_SFS" 3350646887 0.5.0
79822999 167509300
user1_mnt
17-2月 -17 02.31.26.449934 上午
DBMS_DBFS_CONTENT_PROPERTIES_T(DBMS_DBFS_CONTENT_PROPERTY_T('principal', NULL, 9
), DBMS_DBFS_CONTENT_PROPERTY_T('owner', NULL, 9), DBMS_DBFS_CONTENT_PROPERTY_T(
'acl', NULL, 9), DBMS_DBFS_CONTENT_PROPERTY_T('asof', NULL, 187), DBMS_DBFS_CONT
ENT_PROPERTY_T('read_only', '0', 2), DBMS_DBFS_CONTENT_PROPERTY_T('feature_getat
tr_view', '"VSFS$_32778"', 9))

SQL> select * from table(dbms_dbfs_sfs.listTables);

SCHEMA_NAME TABLE_NAME
-------------------------------- --------------------------------
PTABLE_NAME VERSION#
-------------------------------- --------------------------------
CREATED
---------------------------------------------------------------------------
FORMATTED
---------------------------------------------------------------------------
PROPERTIES(PROPNAME, PROPVALUE, TYPECODE)
--------------------------------------------------------------------------------
DBFS_ADMIN TESTDB1
SFS$_FSTP_1 0.5.0
15-2月 -17 09.41.04.218914 上午
15-2月 -17 09.41.04.218914 上午
DBMS_DBFS_CONTENT_PROPERTIES_T(DBMS_DBFS_CONTENT_PROPERTY_T('sfs:props:rootid',
'2', 2))

DBFS TESTDBFS
SFS$_FSTP_8193 0.5.0
16-2月 -17 01.33.10.516541 上午
16-2月 -17 01.33.10.516541 上午
DBMS_DBFS_CONTENT_PROPERTIES_T(DBMS_DBFS_CONTENT_PROPERTY_T('sfs:props:rootid',
'8194', 2))

DBFS_ADMIN SFS$_FST_32769
SFS$_FSTP_32769 0.5.0
17-2月 -17 02.31.14.049744 上午
17-2月 -17 02.31.14.049744 上午
DBMS_DBFS_CONTENT_PROPERTIES_T(DBMS_DBFS_CONTENT_PROPERTY_T('sfs:props:rootid',
'32770', 2))

SQL> select pathname from dbfs_content;

PATHNAME
--------------------------------------------------------------------------------
/TESTDB1
/TESTDB1/info_debug.txt
/TESTDB1/bb
/TESTDB1/aa
/TESTDB1/Desert.jpg
/TESTDB1/Chrysanthemum.jpg
/TESTDB1/2.txt
/TESTDB1/1.txt
/TESTDB1/.sfs/tools
/TESTDB1/.sfs/snapshots
/TESTDB1/.sfs/content
/TESTDB1/.sfs/attributes
/TESTDB1/.sfs/RECYCLE
/TESTDB1/.sfs
/user1_mnt
/user1_mnt/.sfs/tools
/user1_mnt/.sfs/snapshots
/user1_mnt/.sfs/content
/user1_mnt/.sfs/attributes
/user1_mnt/.sfs/RECYCLE
/user1_mnt/.sfs

21 rows selected.

2）从表中读文件系统
SQL> select * from testdb1; --testdb1 为文件系统的名称
SQL> select pathname ,item,pathtype,filedata from testdb1; --filedata 为文件的内容，
通过SQL DEVELOPER 查看以securefile lobs 形式存储

SQL> select * from user_lobs;

TABLE_NAME
--------------------------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
INDEX_NAME
--------------------------------------------------------------------------------
CHUNK PCTVERSION RETENTION FREEPOOLS CACHE LOGGING ENCR COMPRE
---------- ---------- ---------- ---------- ---------- ------- ---- ------
DEDUPLICATION IN_ FORMAT PAR SEC SEG RETENTI RETENTION_VALUE
--------------- --- --------------- --- --- --- ------- ---------------
TESTDB1
FILEDATA
LOB_SFS$_FST_1
DBFS_GOLDENGATE
SYS_IL0000091999C00007$$
8192 NO YES NO NO
NO YES NOT APPLICABLE NOYES YES DEFAULT

SFS$_FST_32769
FILEDATA
LOB_SFS$_FST_32769
DBFS_GOLDENGATE
SYS_IL0000092048C00007$$
8192 NO YES NO NO
NO YES NOT APPLICABLE NOYES YES DEFAULT

/*******************************************************************************/
十五 oracle 12.1 nocdb 复制到 mysql

/*******************************************************************************/
十五 oracle 12.1 nocdb 复制到 sqlserver

/*******************************************************************************/
未实现目标端版本比源端版本高的双向复制
oracle dddd( ogg 11.2) 到 INFORMIX dddd(ogg 12.2)
ORACLE 到 INFORMIX 成功，反之不成功

一版本
oracle 版本 :11.2.0.4.0
goldengate for oracle 版本:11.2.1.0.1

8s 版本:1.6.3.71
goldengate for informix 版本:12.2.0.1.1

ogg 用户名/口令 ggs11/ggs11
数据库需要复制的模式 eeee
ogg 安装位置 /u01/ggs11

/*******************************************************************************/
1 ORACLE 端ORACLE 安装
SQL> create tablespace ggs11 datafile size 100m autoextend on;

SQL> CREATE USER ggs11 IDENTIFIED BY ggs11 default tablespace ggs11;
SQL> GRANT EXECUTE ON UTL_FILE TO ggs11;
SQL> @marker_setup.sql
SQL> @ddl_setup.sql
GRANT CREATE TABLE,CREATE SEQUENCE TO ggs11;

@role_setup.sql

GRANT GGS_GGSUSER_ROLE TO ggs11;

GRANT CREATE SESSION, ALTER SESSION to ggs11;
GRANT RESOURCE,CONNECT to ggs11;
GRANT SELECT ANY DICTIONARY to ggs11;
GRANT FLASHBACK ANY TABLE to ggs11;
GRANT SELECT ANY TABLE to ggs11;
GRANT INSERT ANY TABLE to ggs11;
GRANT UPDATE ANY TABLE to ggs11;
GRANT DELETE ANY TABLE to ggs11;
GRANT CREATE ANY TABLE to ggs11;
GRANT DROP ANY TABLE to ggs11;
GRANT CREATE ANY INDEX to ggs11;
GRANT DROP ANY INDEX to ggs11;
GRANT CREATE ANY VIEW to ggs11;
GRANT DROP ANY VIEW to ggs11;
GRANT CREATE ANY PROCEDURE to ggs11;
GRANT DROP ANY PROCEDURE to ggs11;
GRANT ALTER ANY TABLE to ggs11;
GRANT ALTER ANY PROCEDURE to ggs11;
GRANT EXECUTE ON DBMS_FLASHBACK to ggs11;
GRANT GGS_GGSUSER_ROLE to ggs11;

[oracle@node1 ggs11]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

GGSCI (node1) 1> create subdirs

GGSCI (host-172-16-21-61) ) 2> edit params mgr
输入：
PORT 7860
DYNAMICPORTLIST 7845-7909
--AUTOSTART ER *
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
LAGREPORTHOURS 1
LAGCRITICALMINUTES 45

GGSCI (host-172-16-21-61) 3>edit param ./GLOBALS
输入:
GGSCHEMA ggs11
CHECKPOINTTABLE ggs11.ggs_checkpoint

GGSCI (host-172-16-21-61) 4> DBLOGIN USERID ggs11@orcl, PASSWORD ggs11
GGSCI (host-172-16-21-61) 5> add checkpointtable ggs11.ggs_checkpoint

--执行下面脚本时报下面的错误：
SQL> @role_setup.sql
SQL> GRANT CREATE TABLE,CREATE SEQUENCE TO ggs;
GRANT CREATE TABLE,CREATE SEQUENCE TO OGG
*
ERROR at line 1:
ORA-04098: 触发器 'SYS.GGS_DDL_TRIGGER_BEFORE' 无效且未通过重新验证

--解决（参看网页http://blog.csdn.net/cc_dba/article/details/40082149
3.3 Enabling Oracle GoldenGate in the Database

The database services required to support Oracle GoldenGate capture and apply must be
enabled explicitly for an Oracle 11.2.0.4 database. This is required for all modes of Extract and Replicat.

To enable Oracle GoldenGate, set the following database initialization parameter.
All instances in Oracle RAC must have the same setting.

ENABLE_GOLDENGATE_REPLICATION=true
）

SQL> @ddl_disable.sql

2 数据库
oracle端 :
SQL> create user eeee identified by eeee;
SQL> grant dba to eeee;
SQL> conn eeee/eeee
SQL> create table t1 (a int,b int);
SQL> create table t2 (a int,b int);
SQL> create table t3 (a int,b int);

--informix 端 :
[informix@node1 dirrpt]$ dbaccess eeee -
> create table t1 (a int,b int);
> create table t2 (a int,b int);
> create table t3 (a int,b int);

3 配置ODBC
[informix@mysql dirrpt]$ echo $ODBCINI
/home/informix/odbc.ini

# cd /home/informix
[informix@node1 etc]$ vi odbc.ini

[ODBC Data Sources]
eeee=IBM INFORMIX ODBC DRIVER

[eeee]
Driver=/home/informix/gbase/lib/cli/iclis09b.so
Description=IBM INFORMIX ODBC DRIVER
Database=eeee
LogonID=informix
pwd=informix
Servername=gbaseserver

--测试ODBC
[informix@node1 ~]$ cd /home/informix/gbase/
[informix@node1 gbase]$ ./catalog eeee

4 创建 defgen.def文件
1）因为是异构环境，所以源端要创建DEF文件
su - oracle
cd /u01/ggs11/dirprm
[oracle@node1 dirprm]$ vi defgeneeee.prm
defsfile ./dirdef/eeee.def purge
userid ggs11,password ggs11
table eeee.*;

cd /u01/ggs11
./defgen paramfile ./dirprm/defgeneeee.prm

2）将源端的defgen.def 拷贝到目标端，并授权远程目录 /home/informix/ggs/dirdat 的访问权
[root@node1 ~]# cd /u01/ggs11/dirdef/
[root@node1 dirdef]# cp eeee.def /home/informix/ggs/dirdef/
[root@node1 ggs]# chown informix:informix /home/informix/ggs/dirdef/eeee.def
[root@node1 ggs]# chmod 777 /home/informix/ggs/dirdef/eeee.def
[root@node1 ggs]# chmod 777 /home/informix/ggs/dirdat

5 informix 端
配置 GGS
[informix@node1 ggs]$ ggsci

GGSCI (node1) 1> create subdirs

2）mgr
GGSCI (node1) 2> edit params mgr
输入：
port 7809
AUTOSTART REPLICAT r*
AUTORESTART REPLICAT r*, RETRIES 4, WAITMINUTES 4
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3

3）连接测试
GGSCI (node1) 3> dblogin sourcedb eeee userid informix password informix

4）CHECKPOINTTABLE

GGSCI (node1) 1> dblogin sourcedb eeee userid informix password informix

2016-11-19 23:41:30 INFO OGG-03036 Database character set identified as UTF-8. Locale: zh_CN.

2016-11-19 23:41:30 INFO OGG-03037 Session character set identified as UTF-8.
Successfully logged into database.

GGSCI (node1 as informix@infdrv3/info) 4> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE eeee.informix.ggs_checkpoint

GGSCI (node1 as informix@infdrv3/info) 5> ADD CHECKPOINTTABLE eeee.informix.ggs_checkpoint

Successfully created checkpoint table info.informix.ggs_checkpoint.

6 oracle 复制到 INFORMIX 端
1）Extract 配置( group name must be at most 8 characters).
源数据库端：
GGSCI (node1) 1> DBLOGIN USERID ggs11@orcl, PASSWORD ggs11
Successfully logged into database.

GGSCI (node1) 2> add trandata eeee.*

GGSCI (node1) 3> edit param ex_eeee1
输入：
extract ex_eeee1
GETTRUNCATES
dynamicresolution
NOCOMPRESSDELETES
NOCOMPRESSUPDATES
FETCHOPTIONS FETCHPKUPDATECOLS,USEROWID
userid ggs11@orcl,password ggs11
exttrail ./dirdat/dd
table eeee.*;

GGSCI (node1) 4> add extract ex_eeee1,tranlog,begin now
EXTRACT added.

GGSCI (node1) 5> add exttrail ./dirdat/dd,extract ex_eeee1,megabytes 5
EXTTRAIL added.

2) data pump 配置 dp_eeee1
源数据库端:
GGSCI (f80) 11> edit param dp_eeee1
输入：
extract dp_eeee1
dynamicresolution
rmthost 192.168.6.200,mgrport 7809
USERID ggs11, PASSWORD ggs11
rmttrail /home/informix/ggs/dirdat/dd
table eeee.*;

GGSCI (node1) 8> add extract dp_eeee1,exttrailsource ./dirdat/dd
EXTRACT added.

GGSCI (node1) 9> add rmttrail /home/informix/ggs/dirdat/dd,extract dp_eeee1,megabytes 5

GGSCI (node1) 18> start manager
GGSCI (node1) 20> start ex_eeee1
GGSCI (node1) 21> start dp_eeee1

3) replicat 配置
目标数据库端 (注意数据从 eeee 复制到 eeee)
GGSCI (node1) 3> dblogin sourcedb eeee userid informix password informix

GGSCI (h70) 2> edit param rp_eeee1
输入：
replicat rp_eeee1
TARGETDB eeee userid informix password informix
handlecollisions
ASSUMETARGETDEFS
GETTRUNCATES
applynoopupdates
INSERTUPDATES
INSERTDELETES
REPERROR DEFAULT,DISCARD
sourcedefs /home/informix/ggs/dirdef/eeee.def
DISCARDFILE /home/informix/ggs/dirrpt/rp_eeee1.dsc,PURGE
MAP eeee.*, TARGET eeee.informix.*;

GGSCI (node1 as informix@infdrv3/info) 7> add replicat rp_eeee1,checkpointtable eeee.informix.ggs_checkpoint,exttrail /home/informix/ggs/dirdat/dd
REPLICAT added.

GGSCI (node1 as informix@infdrv3/info) 8> start rp_eeee1

7 informix 复制到 oracle 端

GGSCI (host-172-16-21-61) 3> dblogin sourcedb syscdcv1 userid informix password informix
GGSCI (node1 as informix@infdrv1/d819) 2> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE syscdcv1.informix.ggs_checkpoint
CHECKPOINTTABLE eeee.informix.ggs_checkpoint

GGSCI (node1 as informix@syscdcv1/syscdcv1) 17> ADD CHECKPOINTTABLE syscdcv1.informix.ggs_checkpoint
GGSCI (node1 as informix@syscdcv1/syscdcv1) 3> add trandata eeee.informix.*

/*******************************************************************************/

/*******************************************************************************/
--错误处理
/*******************************************************************************/

1. Cannot load ICU resource bundle ‘ggMessage’, error code 2 – No such file or directory
系统原先有 OGG PATH 目录中有OGG的路径,将其改为新的 OGG 版本路径
[oracle@node1 ggs12]$ echo $PATH
/u01/ggs12/:/u01/app/oracle/product/11.2.0/db_1/bin

2 参数dynamicresolution,ASSUMETARGETDEFS
2017-01-22 13:42:27 WARNING OGG-10173 (ekkbi_1.prm) line 4: Parsing error, [dynamicresolution] is deprecated.

3 2017-01-22 15:36:53 WARNING OGG-02760 ASSUMETARGETDEFS is ignored because trail file /home/informix/ggs/dirdat/b1000000028 contains tab
le definitions.

4 2017-02-01 19:08:21 ERROR OGG-02091 Operation not supported because enable_goldengate_replication is
not set to true.
Action: Set the enable_goldengate_replication system parameter to true by executing the following command:
alter system set enable_goldengate_replication=true;

5 informix 数据库因为 chmod -R 777 /home/informix 而导致不能使用,只能重新安装,安装前备份 sqlhosts.gbaseserver,onconfig.gbaseserver
文件,安装后重新拷贝到新的位置

cp sqlhosts.gbaseserver /home/informix/gbase/etc/sqlhosts.gbaseserver
cp onconfig.gbaseserver /home/informix/gbase/etc/onconfig.gbaseserver

6 创建新的 dbspace
onspaces -c -d ifmis_jsx -p /opt/dbs/ifmis_jsx -o 0 -s 10000000 -k 8k

7 添加新的用户
groupadd rep
useradd -g informix -d /home/rep -s /bin/bash -m rep
passwd rep

此用户添加后 ,不能执行相关的INFORMIX 的命令,需要给目录授权
[informix@node1 home]$ chmod g+rwx informix

8 数据库授权
[informix@node1 dbs]$ dbaccess kkbi -

Database selected.

> create table t1(a int, b int);
create table t2(a int, b int);
create table t3(a int, b int);

> GRANT DBA TO rep;

/*******************************************************************/
odbc 安装下面的方法不对

cd file/
tar -xvf unixODBC-2.3.4.tar
cd unixODBC-2.3.4
./configure --prefix=/usr --sysconfdir=/etc/unixODBC
make
make install
cd ..

1) isql 方法测试
cd /usr/bin
[root@node1 bin]# cd /etc/unixODBC

[informix@node1 bin]$ /usr/bin/isql -v kkbi informix informix

/usr/bin/isql: error while loading shared libraries: libodbc.so.2: cannot open shared object file: No such file or directory

/home/informix/file/unixODBC-2.3.4/DriverManager/.libs/libodbc.so.2

0 0