ogg複製程式報ORA-01438錯誤處理

1 OGG程式異常終止

REPLICAT    ABENDED     reptest    00:00:00      00:08:22 

GGSCI (host1) 13> info reptest

REPLICAT   reptest  Last Started 2023-02-22 16:08   Status ABENDED

Checkpoint Lag       00:00:00 (updated 00:08:45 ago)

Log Read Checkpoint  File ./dirdat/dn/dn000046

                     2023-02-22 15:42:53.999701  RBA 32786889

2 檢視報錯資訊

2023-02-22 16:08:29  WARNING OGG-01154  SQL error 1438 mapping usera.TBALE_A to userb.TBALE_A OCI 

Error ORA-01438: value larger than specified precision allowed for this column (status = 1438). 

INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "userb"."TBALE_A" ..............

2023-02-22 16:08:29  WARNING OGG-01003  Repositioning to rba 32786889 in seqno 46.

Source Context :

  SourceModule            : [er.errors]

  SourceID                : [/scratch/pradshar/view_storage/pradshar_pse_15852019/oggcore/OpenSys/src/app/er/errors.cpp]

  SourceFunction          : [take_rep_err_action]

  SourceLine              : [623]

  ThreadBacktrace         : [8] elements

                          : [/goldengate/libgglog.so(CMessageContext::AddThreadContext()+0x1e) [0x7fc489de571e]]

                          : [/goldengate/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x2cc) [0x7fc489dde6bc]]

                          : [/goldengate/libgglog.so(_MSG_ERR_MAP_TO_TANDEM_FAILED(CSourceContext*, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)

1> const&, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, CMessageFactory::MessageDisposition)+0x53) [0x7fc489dda581]]

                          : [/goldengate/replicat(take_rep_err_action(short, int, char const*, extr_ptr_def*, __std_rec_hdr*, char*, file_def*, b

ool)+0xdac) [0x521a80]]

                          : [/goldengate/replicat(process_extract_loop()+0x2388) [0x53ad18]]

                          : [/goldengate/replicat(main+0x732) [0x54cbd2]]

                          : [/lib64/libc.so.6(__libc_start_main+0xf5) [0x7fc4841b73d5]]

                          : [/goldengate/replicat(__gxx_personality_v0+0x31a) [0x4c0c6a]]

2023-02-22 16:08:29  ERROR   OGG-01296  Error mapping from usera.TBALE_A to userb.TBALE_A.

3 檢視Oracle對報錯資訊的解釋，根據如下資訊，認為是表的某個列，資料列精度不匹配，需要進行調整，即往大里調整

[oracle@host1 dirrpt]$ oerr ora 1438

01438, 00000, "value larger than specified precision allowed for this column"

// *Cause: When inserting or updating records, a numeric value was entered 

//         that exceeded the precision defined for the column.

// *Action: Enter a value that complies with the numeric column's precision,

//          or use the MODIFY option with the ALTER TABLE command to expand

//          the precision.

4 由於以上報錯資訊，沒有說明哪個列的資料精度有問題，則需要對比源端和目標端的表結構，將number型別的進行比對

5 檢視OGG源端和目標端的對映關係，發現column_A的長度為20，比表目前設定的值大1

map usera.TBALE_A target userb.TBALE_A, KEYCOLS (COLUMN_B), colmap (usedefaults, target_write_time = @datenow(), 

source_change_time = @GETENV ("GGHEADER", "COMMITTIMESTAMP"), CONTAINER = 959, C_COLUMN_A = 1019, COLUMN_A = @STRCAT("612", column2), 

COLUMN_B = @STRCAT("612", column1));

6 使用discard進行資料分析，確定column_A列值比設定的小

7 修改表結果，並重啟程式，ogg程式恢復正常

 SYS@instance1 > alter table userb.TBALE_A modify COLUMN_A NUMBER(20);

Table altered.

GGSCI (host1) 19> start reptest

Sending START request to MANAGER ...

REPLICAT reptest starting

GGSCI (host1) 20> info reptest

REPLICAT   reptest  Last Started 2023-02-22 16:30   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:02 ago)

Log Read Checkpoint  File ./dirdat/dn/dn000046

                     2023-02-22 16:29:59.000150  RBA 32790448