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