ggsci: error while loading shared libraries

在部署goldengate过程中发现如下错误:
[oracle@localhost OGG]$ ggsci
ggsci: error while loading shared libraries: /opt/oracle/product/10.2.0/db_1/lib/libclntsh.so.10.1: cannot restore segment prot after reloc: Permission denied

查找资料,没有专门说ogg安装这个错误,是SELinux启用导致,但是有很多其他程序执行过程中报类此错误是有此导致,那么我抱着尝试的态度实验看看:
1、查看SELinux是否被关闭
[oracle@localhost tmp]$ more /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing – SELinux security policy is enforced.
# permissive – SELinux prints warnings instead of enforcing.
# disabled – SELinux is fully disabled.
SELINUX=disabled
# SELINUXTYPE= type of policy in use. Possible values are:
# targeted – Only targeted network daemons are protected.
# strict – Full SELinux protection.
SELINUXTYPE=targeted
说明已经关闭。那就奇怪了,既然已经闭关了那为什么还不行?于是我怀疑,是不是有人只是修改了SELINUX=disabled,没有重启系统或者使用命令使其生效导致。

2、查看SELINUX修改是否生效
[root@localhost ~]# getenforce
Enforcing
果然修改没有生效

3、使SELINUX生效
[root@localhost ~]# setenforce 0
再次查询,现在已经生效
[root@localhost ~]# getenforce
Permissive

4、然后启动ggsci
[oracle@localhost ~]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.1 OGGCORE_11.1.1.1.1_PLATFORMS_110729.1700
Linux, x64, 64bit (optimized), Oracle 10g on Jul 29 2011 19:43:29

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

Total insert collisions (ogg)

1、错误现象
Replicating from ECP.TAB_UUM_PACKAGE to RWGL.TAB_UUM_USER:
*** Total statistics since 2011-08-05 10:34:10 ***

Total inserts                               20.00
Total updates                                0.00
Total deletes                                0.00
Total discards                               0.00
Total operations                            20.00
Total insert collisions                     20.00

2、错误原因
RWGL.TAB_UUM_USER表上有insert触发器,导致失败。因为触发器使得插入操作为插入和触发器中的操作绑定为了一个整体,现在因为触发器失败,导致插入失败,而且还会丢失该条插入记录,需要查找出该条记录比较困难。

3、解决方案
采用自治事件结合异常捕获
自治事件使得触发器和插入操作相互分离,异常捕获记录触发器失败的原因,插入到日志表中,通过该表,可以查询查失败的记录,然后人工干预,触发器实例:

create or replace trigger ogg_t
  before insert on t_1
  for each row
declare
   tid NUMBER;
   err VARCHAR2(100);
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  SELECT t.id2 INTO tid FROM t_2 t WHERE NAME=:new.Name;
  INSERT INTO t_3 VALUES(tid,:new.name);
  COMMIT;
EXCEPTION
       WHEN TOO_MANY_ROWS THEN
         INSERT INTO t_error VALUES(:new.id,'TOO_MANY_ROWS');
         COMMIT;
        WHEN NO_DATA_FOUND THEN
           INSERT INTO t_error VALUES(:new.id,'NO_DATA_FOUND');
           COMMIT;
         WHEN OTHERS THEN
           err:=SUBSTR(SQLERRM(SQLCODE),1,100);
           INSERT INTO t_error VALUES(:new.id,err);
           COMMIT;
end ogg_t;

1)PRAGMA AUTONOMOUS_TRANSACTION;
自治事务,就是说触发器不管是成功,还是失败,数据库同步程序都能够同步成功数据到目标端

2)COMMIT;
因为采用了自治事件,所以begin end中的操作是独立与数据库中数据,需要单独提交

3)EXCEPTION
添加异常处理

4)INSERT INTO t_error VALUES(:new.id,’TOO_MANY_ROWS’);(类此语句,注意commit)
建立一张错误日志表(根据具体情况决定),如果触发器失败,把错误记录到该表中,以后出现问题查找很方便(要求:通过该表能够查询到那条语句的触发器执行失败。失败原因,失败时间,额外列(用于确定对应记录))

Goldengate常见错误

ERROR OGG-01031 There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. (Reply received is Unable to open file “/opt/OGG/dirdat/AIR/EXTTRAIL/U9000005” (error 11, Resource temporarily unavailable)).
重新启动一次

WARNING OGG-00769 mysql_refresh() failed, falling back to default key. SQL error (1227). Access denied; you need the RELOAD privilege for this operation.
mysql用户权限问题

ERROR OGG-01033 There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. (Remote file used is /opt/OGG/dirdat/rl000003, reply received is Unable to lock file “/opt/OGG/dirdat/rl000003” (error 13, Permission denied). Lock currently held by process id (PID) 14409)
原因:网络或者目标段路径不正常,访问到目标端目录失败导致
在目标端kill -9 14409
或者等待2小时,自动系统自动重启目标端进程

ERROR OGG-01033 Oracle GoldenGate Capture for Oracle, p-xz.prm: There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. (Remote file used is /opt/OGG/dirdat/XunZhi/EXTFILE/U1000000, reply received is Could not create /opt/OGG/dirdat/XunZhi/EXTFILE/U1000000).
检查远程的目录是否和datapump中的远程目录是否一致

Goldengate 配置oracle to mysql

以前oracle to mysql试验总是有乱码问题不能解决,后来和同事交流中,他说oracle使用ogg的11g版本,mysql使用ogg的10g版本可以解决乱码问题,开始我还是怀疑,因为想新的版本都不行,难道老版本就可以解决这个问题吗?抱着试试看的态度,做了个试验,尽然成功了,把试验的相关情况记录下来
mysql参数配置(cat /etc/my.cnf):
[client]
default-character-set = gbk
[mysqld]
lower_case_table_names=1–表名不区分大小写(省的在repl进程中因为大小写的问题导致不能捕获数据)
character-set-server = gbk
表/列编码:均为gbk
Note:
1、因为mysql是target端,所以对于log-bin/binlog_format参数无要求
2、通过以上设置确保mysql的所有相关编码均为gbk

系统编码配置(cat /etc/sysconfig/i18n):
source:
LANG=”zh_CN.GBK”
SUPPORTED=”zh_CN.GBK:zh_CN:zh”
SYSFONT=”latarcyrheb-sun16″
target:
LANG=”zh_CN.GBK”
SYSFONT=”latarcyrheb-sun16″
Note:是的系统编码和mysql编码相同

OGG配置过程:
source端:

add extract ext-all,tranlog,begin now
ADD EXTTRAIL /opt/OGG/dirdat/extract/AL, EXTRACT ext-all
edit params ext-all

extract ext-all
SETENV (NLS_LANG =AMERICAN_AMERICA.ZHS16GBK)
userid is1ogg,password passw0rd
exttrail /opt/OGG/dirdat/extract/AL
discardfile /opt/OGG/discard/ext-all.txt, append, megabytes 100 
DDL INCLUDE MAPPED OPTYPE alter OBJNAME ECP.TAB*
TABLE ecp.*;


add EXTRACT p-air, EXTTRAILSOURCE /opt/OGG/dirdat/extract/AL, BEGIN now
add rmttrail /opt/OGG/dirdat/rl extract p-air
edit params p-air

extract p-air
SETENV (NLS_LANG =AMERICAN_AMERICA.ZHS16GBK)
userid is1ogg,password passw0rd
RMTHOST 192.168.1.4,MGRPORT 7809,TCPBUFSIZE 100000,TCPFLUSHBYTES 300000
rmttrail /opt/OGG/dirdat/rl
discardfile /opt/OGG/discard/p-air.txt, append, megabytes 100 
TABLE ecp.*;

target端:

add replicat repl, exttrail /opt/OGG/dirdat/rl,nodbcheckpoint
edit params repl

replicat repl
DBOPTIONS HOST 127.0.0.1, CONNECTIONPORT 3306
TARGETDB ecp,userid root,password xifenfei
assumetargetdefs
reperror default,discard
discardfile /tmp/mysql.dsc,append,megabytes 100
MAP ECP.TAB_UUM_DEPT, TARGET ecp.tab_uum_dept;
MAP ECP.TAB_UUM_DEPT_LEADER, TARGET ecp.tab_uum_dept_leader;

异构数据库初始化大字段处理

一、source端

SOURCEISTABLE
SOURCEDB oracle
RMTHOST 127.0.0.1, MGRPORT 7820
RMTFILE D:\ogg\oracle\dirdat\i1
table dbo.t_v;
table dbo.t_t;

二、target端

SPECIALRUN
END RUNTIME
SETENV (NLS_LANG =AMERICAN_AMERICA.ZHS16GBK)  
userid ogg,password xifenfei   
EXTFILE D:\ogg\oracle\dirdat\i1
SOURCEDEFS D:\ogg\oracle\dirdef\t_v.def
discardfile D:\ogg\oracle\dirtmp\repsz.dsc,append,megabytes 100 
MAP "dbo.t_v", target mssql.t_v;
MAP "dbo.t_t", target mssql.t_t
, colmap ( id = id , text_t = @binary(t_text));

三、执行
extract paramfile dirprm\einit1.prm reportfile dirrpt\einit1.rpt
replicat paramfile dirprm\rinit1.prm reportfile dirrpt\rinit1.rpt

四、试验说明
如果大字段数据库过长,如这里的text字段类型过长是,在l1中有数据,但是target端的数据库中无对应数据,初步分析原因可能有:
1、defgen定义文件中确定文件长度导致
2、target端编码和clob列相关限制有关(可能性不大)
3、goldengate软件内在机制导致,还需要彻底的阅读官网文档
4、现在好像到target端的最终长度为4000byte(根据数据库编码不同区分汉字、字母、数字),在本测试中,如果source端使用ntext,target只能接收1000个汉字,如果是text类型,可以接受1333个汉字。
5、试验环境说明:sql server 2005 to oracle 11g,source端表(id int primary key auto,t_text text),target端(id number primary key,text_t clob)

—出现异常原因已经找到(2011年2月22日23:27:51)
When the size of a large object exceeds 4K, Oracle GoldenGate stores the data in segments within the Oracle GoldenGate trail. The first 4K is stored in the base segment, and the rest is stored in a series of 2K segments. Oracle GoldenGate does not support the filtering, column mapping, or manipulation of large objects of this size. Full Oracle GoldenGate functionality can be used for objects that are 4K or smaller.
大致意思就是当内容超过4k时,就不能使用过滤、列映射、或者操作大字段,当内容等于或者小于4k时,所有的goldengate函数都可以使用。上面问题是当t_text内容大于4k时,不能进行大对象操作。
疑问:那当有数据超过4k该怎么处理

—试验测试(2011年2月23日23:34:13)
初始化后,同步数据库过程中,异构数据库不同列类型(如:mssql中的text到oracle中的clob),也必须使用sourcedefs,使用适当的函数对列类型进行转换(如:colmap ( id = id , t_lob = @binary(t_lob))),对于大于4k的数据,还是和初始化一样不能被处理。该问题等待寻找解决方案