Oracle GoldenGate 系列：Replicat 进程遇 OCI Error ORA-14402 错误解决办法

生产环境发票管理库到总局主数据库 Replicat 进程因报如下错误 Abended：

2013-04-25 07:59:50  WARNING OGG-00869  OCI Error ORA-14402: updating partition keycolumn would cause a partition change (status = 14402). UPDATE"HX_FP"."FP_PZHDXX" SET "SWJG_DM" =:a1,"XGRQ" = :a2,

"SJGSDQ" = :a3 WHERE"HDQCUUID" = :b0.

2013-04-25 07:59:50  WARNING OGG-01004  Aborted grouped transaction on'HX_FP.FP_PZHDXX', Database error 14402 (OCI Error ORA-14402: updatingpartition key column would cause a partition change (statu

s = 14402). UPDATE"HX_FP"."FP_PZHDXX" SET "SWJG_DM" = :a1,"XGRQ"= :a2,"SJGSDQ" = :a3 WHERE "HDQCUUID" = :b0).

2013-04-25 07:59:50  WARNING OGG-01003  Repositioning to rba 355778 in seqno 83.

2013-04-25 07:59:50  WARNING OGG-01154  SQL error 14402 mapping HX_FP.FP_PZHDXX toHX_FP.FP_PZHDXX OCI Error ORA-14402: updating partition key column would causea partition change (status = 14402). U

PDATE"HX_FP"."FP_PZHDXX" SET "SWJG_DM" =:a1,"XGRQ" = :a2,"SJGSDQ" = :a3 WHERE "HDQCUUID"= :b0.

2013-04-25 07:59:50  WARNING OGG-01003  Repositioning to rba 355778 in seqno 83.

Source Context :

 SourceModule            :[er.errors]

 SourceID                :[/scratch/aime1/adestore/views/aime1_staxj16/oggcore/OpenSys/src/app/er/errors.cpp]

 SourceFunction          :[take_rep_err_action(short, int32_t, const char *, extr_ptr_def *,std_rec_hdr_def *, char *, file_def *, bool)]

  SourceLine              : [623]

2013-04-25 07:59:50  ERROR  OGG-01296  Error mapping fromHX_FP.FP_PZHDXX to HX_FP.FP_PZHDXX.

 

WARNNING OGG-00869根据官方的 Error Reference 中的描述，专指OGG遇到了特定的数据库错误，可以忽略。

OGG-00869: {0}

Cause: The specified database error occurred, but can be ignored.

Action: Contact Oracle Support only if a problem persists.

 

但在本例中，replicat 进程遇到的数据库错误为OCI ErrorORA-14402: updating partition key column would cause a partition change，显然无法忽略。ORA-14402 错误一般是由于 update 操作更改了分区表的分区键的值触使该行迁移到其他的分区中。而表的 row movement 默认情况下处于禁用状态，从而导致报该错误。

[oracle@prod ~]$ oerr ora 14402

14402, 00000, "updating partition keycolumn would cause a partition change"

// *Cause: An UPDATE statement attempted to change the value of a partition

//         key column causing migration of the row to another partition

// *Action: Do not attempt to update apartition key column or make sure that

//         the new partition key is within the range containing the old

//         partition key.

 

Replicat 进程报错时正在修改的记录为：

Logdump 7 >pos 355778

Reading forward from RBA 355778

Logdump 8 >n

___________________________________________________________________

Hdr-Ind    :     E (x45)     Partition  :    .  (x04) 

UndoFlag   :     . (x00)     BeforeAfter:     A (x41) 

RecLength  :    91 (x005b)   IO Time    : 2013/04/24 20:33:00.010.627  

IOType     :    15 (x0f)     OrigNode   :  255  (xff)

TransInd   :     . (x00)     FormatType :     R (x52)

SyskeyLen  :     0 (x00)     Incomplete :     . (x00)

AuditRBA   :        950       AuditPos   : 915048500

Continued  :     N (x00)     RecCount   :    1  (x01)

 

2013/04/24 20:33:00.010.627 FieldComp            Len    91 RBA 355778

Name: HX_FP.FP_PZHDXX

After  Image:                                            Partition 4   G  b  

 0000 0022 0000 4232 3742 3133 35354146 3646 3430 | ..."..B27B1355AF6F40 

 3945 3839 3145 3142 4238 4144 36383837 4438 000c | 9E891E1BB8AD6887D8.. 

 000d 0000 3135 3030 3931 3030 30303000 1000 1500 | ....15009100000..... 

 0032 3031 332d 3034 2d32 343a 32303a33 333a 3030 | .2013-04-24:20:33:00 

 0011 0007 0000 3135 3030 39                       | ......15009 

Column     0 (x0000), Len    34 (x0022) 

Column    12 (x000c), Len    13 (x000d) 

Column    16 (x0010), Len    21 (x0015) 

Column    17 (x0011), Len     7 (x0007)

 

执行的 update 语句为：

UPDATE HX_FP.FP_PZHDXX SET SWJG_DM = ‘15009100000’,XGRQ= ‘2013-04-24:20:33:00’,SJGSDQ= ‘15009’ WHERE HDQCUUID = ‘B27B1355AF6F409E891E1BB8AD6887D8’

 

其中SJGSDQ 正是HX_FP.FP_PZHDXX 表的分区键。

针对这种修改分区表分区键的操作导致的 replicat 进程挂起，metalink 上给出的建议为在应用设计时尽量避免这种操作，启用该表的 row movement便可临时解决这一问题。

SQL> alter table HX_FP.FP_PZHDXX enablerow movement;

Table altered.

GGSCI (bjsczjdbzsj01) 1> info all

Program    Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER    RUNNING                                          

JAGENT     STOPPED                                          

EXTRACT    RUNNING     EZJTS_TS    00:00:00      00:00:02   

EXTRACT    RUNNING     PZJTS_TS    00:00:00      00:00:03   

REPLICAT   ABENDED     RFP_ZJ3     00:00:00      12:21:37   

REPLICAT   RUNNING     RFX_ZJ3     00:00:00      00:00:07   

REPLICAT   RUNNING     RGZ_ZJ5     00:00:00      00:00:02   

REPLICAT   RUNNING     RNSTS_ZJ    00:00:00      00:00:03   

REPLICAT   RUNNING     RNS_ZJ2     00:00:00      00:00:01   

REPLICAT   RUNNING     RSB_ZJ4     00:00:00      00:00:04   

GGSCI (bjsczjdbzsj01) 3> start RFP_ZJ3

Sending START request to MANAGER ...

REPLICAT RFP_ZJ3 starting

GGSCI (bjsczjdbzsj01) 4> info all

Program    Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER    RUNNING                                          

JAGENT     STOPPED                                           

EXTRACT    RUNNING     EZJTS_TS    00:00:00      00:00:05   

EXTRACT    RUNNING     PZJTS_TS    00:00:00      00:00:06   

REPLICAT   RUNNING     RFP_ZJ3     00:00:00      00:00:00   

REPLICAT   RUNNING     RFX_ZJ3     00:00:00      00:00:00    

REPLICAT   RUNNING     RGZ_ZJ5     00:00:00      00:00:03   

REPLICAT   RUNNING     RNSTS_ZJ    00:00:00      00:00:06   

REPLICAT   RUNNING     RNS_ZJ2     00:00:00      00:00:09   

REPLICAT   RUNNING     RSB_ZJ4     00:00:00      00:00:07

转载请注明作者出处及原文链接:

http://blog.csdn.net/xiangsir/article/details/8851677