oracle-goldgate

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