ITPub博客

首页 > 数据库 > Oracle > oracle时间格式记录异常导致OGG复制进程报错

oracle时间格式记录异常导致OGG复制进程报错

原创 Oracle 作者:sky_dizzy001 时间:2014-02-11 10:18:49 0 删除 编辑
1. GoldenGate进程RSC00022出现ABENDED现象:
REPLICAT    ABENDED     RSC00022    00:00:06      03:52:53

2. 经检查discardfile文件发现问题出在以下update语句报错:
2011-11-10 14:29:17  WARNING OGG-00869  OCI Error ORA-01850: hour must be between 0 and 23 (status = 1850), SQL _SB_YZPZ" SET "PZ_XH" = :a1,"NSRNBM" = :a2,"PZZL_DM" = :a3,"GLWSH_XH" = :a4,"LSNSR_DM" = :a5,"ZSDLFS_DM" = :a6,"SB_RQ" = :a7,"SBFS_D
M" = :a8,"SBSX_DM" = :a9,"RJ_XH" = :a10,"ZZZLJSRY>.
Problem replicating DB_SBZS.T_SCYW to DB_SC.T_SCYW
Mapping problem with compressed key update record (target format)...
*
SB_RQ = 2001-06-11 35:00:00
SBFS_DM = 10
SBSX_DM = 0
RJ_XH = 0
ZZZLJSRY_DM = NULL

3. 如上所示SB_RQ的时间格式有问题SB_RQ = 2001-06-11 35:00:00,导致报错ORA-01850: hour must be between 0 and 23
经检查源表,发现该表PZ_XH = 2004010022368370的记录确实有问题:
SQL> select to_char(PZ_XH),TO_CHAR(SB_RQ,'yyyymmdd hh:mi:ss') from DB_SBZS.T_SCYW
  2   where pz_xh='2007090146478387' or pz_xh='2004010022368370';


TO_CHAR(PZ_XH)                           TO_CHAR(SB_RQ,'YYYYMMDDHH:MI:S
---------------------------------------- ------------------------------
2004010022368370                         00000000 00:00:00
2007090146478387                         20070903 12:00:00

SQL> select SB_RQ from DB_SBZS.T_SCYW where pz_xh='2007090146478387' or pz_xh='2004010022368370';

SB_RQ
-----------
2001-6-11
2007-9-3
     
4.解决思路:源SB_RQ = 2001-06-11 35:00:00的记录更新为正常后,目标端应用进程即可恢复正常
start  replicat RSX00028 skiptransaction ----跳过出错记录
delete   DB_SC.T_SCYW    where  pz_xh='2007090146478387'; ----删除本地记录
insert into    DB_SC.T_SCYW select * from   DB_SBZS.T_SCYW@DB_LINK where  pz_xh='2007090146478387'; ----重新插回记录

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29468144/viewspace-1079332/,如需转载,请注明出处,否则将追究法律责任。

上一篇: OGG-01028错误处理
请登录后发表评论 登录
全部评论

注册时间:2014-02-01

  • 博文量
    27
  • 访问量
    129573