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

最新推荐文章于 2023-12-22 09:07:54 发布

转载最新推荐文章于 2023-12-22 09:07:54 发布 · 336 阅读

本文讨论了GoldenGate进程出现ABENDED现象，并详细分析了导致问题的update语句中时间格式错误。通过检查源表发现特定记录的SB_RQ时间格式不正确，导致Oracle错误ORA-01850。解决思路包括修正源表数据，跳过出错记录，删除本地记录并重新插入正确的数据。

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-1076728/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29468144/viewspace-1076728/