1 建立測試表，並插入資料：

OGGTEST@orcl>create table test_number (id number(8));

Table created.

insert into test_number values(0);

insert into test_number values(1);

insert into test_number values(10);

insert into test_number values(100);

insert into test_number values(1000);

insert into test_number values(10000);

insert into test_number values(100000);

insert into test_number values(1000000);

insert into test_number values(10000000);

insert into test_number values(-1);

insert into test_number values(-10);

insert into test_number values(-100);

insert into test_number values(-1000);

insert into test_number values(-10000);

insert into test_number values(-100000);

insert into test_number values(-1000000);

insert into test_number values(-10000000);

insert into test_number values(-100000000);

commit;

目標端檢視資料是否同步，發現已經同步：

目標端：

OGGTEST@orcl>/

ID

----------

0

1

10

100

1000

10000

100000

1000000

10000000

-1

-10

-100

-1000

-10000

-100000

-1000000

-10000000

測試超出範圍的資料，發現報錯：

OGGTEST@orcl>insert into test_number values(100000000);

insert into test_number values(100000000)

*

ERROR at line 1:

ORA-01438: value larger than specified precision allowed for this column

OGGTEST@orcl>insert into test_number values(-100000000);

insert into test_number values(-100000000)

*

ERROR at line 1:

ORA-01438: value larger than specified precision allowed for this column

2 根據如上試驗，可以確定，相同的列，插入負值，0，正值都是沒問題的。

以下測試列聯合。目標端增加一列資料，用於測試連線值。

目標端：

OGGTEST@orcl>alter table test_number add id_numbertest number(11);

Table altered.

OGGTEST@orcl>desc test_number;

Name Null? Type

----------------------------------------- -------- ----------------------------

ID NUMBER(8)

ID_NUMBERTEST NUMBER(11)

修改OGG引數如下：

MAP oggtest.test_number, TARGET oggtest.test_number, COLMAP (USEDEFAULTS, ID_NUMBERTEST=@STRCAT("612",ID));

源端：

insert into test_number values(11);

insert into test_number values(110);

insert into test_number values(1100);

insert into test_number values(11000);

insert into test_number values(110000);

insert into test_number values(1100000);

insert into test_number values(11000000);

commit;

目標端資料驗證：

OGGTEST@orcl>/

ID ID_NUMBERTEST

---------- ---------------

0

1

10

100

1000

10000

100000

1000000

10000000

-1

-10

-100

-1000

-10000

-100000

-1000000

-10000000

11 61211

110 612110

1100 6121100

11000 61211000

110000 612110000

1100000 6121100000

11000000 61211000000

24 rows selected.

3 測試負值，檢視OGG的資訊

源端測試負值：

insert into test_number values(-11);

OGGTEST@orcl>insert into test_number values(-11);

1 row created.

OGGTEST@orcl>commit;

Commit complete.

目標端發現OGG報錯：

GGSCI (oracle10g1) 71> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT ABENDED REPTB1 00:00:06 00:00:13

檢視報錯資訊：

MAP resolved (entry oggtest.test_number):

MAP "OGGTEST"."TEST_NUMBER", TARGET oggtest.test_number, COLMAP (USEDEFAULTS,

ID_NUMBERTEST=@STRCAT("612",ID));

2021-09-24 16:34:02 WARNING OGG-00869 No unique key is defined for table 'TEST_NUMBER'.

All viable columns will be used to represent the key, but may not guarante

e uniqueness. KEYCOLS may be used to define the key.

Using the following default columns with matching names:

ID=ID, ID_NUMBERTEST=ID_NUMBERTEST

Using the following key columns for target table OGGTEST.TEST_NUMBER: ID, ID_NUMBERTEST.

2021-09-24 16:36:17 WARNING OGG-01431 Aborted grouped transaction on 'OGGTEST.TEST_NUMBER',

Mapping error.

2021-09-24 16:36:17 WARNING OGG-01003 Repositioning to rba 5350 in seqno 17.

2021-09-24 16:36:17 WARNING OGG-01151 Error mapping from OGGTEST.TEST_NUMBER to

OGGTEST.TEST_NUMBER.

2021-09-24 16:36:17 WARNING OGG-01003 Repositioning to rba 5350 in seqno 17.

Source Context :

SourceModule : [er.errors]

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

SourceFunction : [take_rep_err_action]

SourceLine : [632]

ThreadBacktrace : [12] elements

: [/app/oracle/goldengate/libgglog.so(CMessageContext::AddThreadContext()+0x1e) [0x2afaa1

92e61e]]

: [/app/oracle/goldengate/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*,

unsigned int, ...)+0x2ec) [0x2afaa192715c]]

: [/app/oracle/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)

[0x2afaa192312b]]

: [/app/oracle/goldengate/replicat(take_rep_err_action(short, int, char const*, extr_ptr_def*,

__std_rec_hdr*, char*, file_def*, bool)+0xaa7) [0x524c31]]

: [/app/oracle/goldengate/replicat(process_extract_loop()+0x274c) [0x53e78c]]

: [/app/oracle/goldengate/replicat(replicat_main(int, char**)+0x741) [0x551101]]

: [/app/oracle/goldengate/replicat(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x4f)

[0x5ef7ff]]

: [/app/oracle/goldengate/replicat(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::

MultiThreading::Thread::ThreadArgs*)+0x104) [

0x5efa54]]

: [/app/oracle/goldengate/replicat(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0x8b)

[0x5efb5b]]

: [/app/oracle/goldengate/replicat(main+0x2c) [0x5502ac]]

: [/lib64/libc.so.6(__libc_start_main+0xf4) [0x3c9561d994]]

: [/app/oracle/goldengate/replicat(__gxx_personality_v0+0x31a) [0x4c266a]]

2021-09-24 16:36:17 ERROR OGG-01296 Error mapping from OGGTEST.TEST_NUMBER to

OGGTEST.TEST_NUMBER.

GGSCI (oracle10g1) 75> info reptb1

REPLICAT REPTB1 Last Started 2021-09-24 16:32 Status ABENDED

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

Log Read Checkpoint File ./dirdat/pt000017

2021-09-24 16:36:11.000484 RBA 5350

使用logdump檢視資料：

Logdump 815 >open ./dirdat/pt000017

Current LogTrail is /app/oracle/goldengate/dirdat/pt000017

Logdump 816 >pos 5350

Reading forward from RBA 5350

Logdump 817 >n

___________________________________________________________________

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

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

RecLength : 14 (x000e) IO Time : 2021/09/24 16:36:11.000.484

IOType : 5 (x05) OrigNode : 255 (xff)

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

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

AuditRBA : 61 AuditPos : 10841720

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

2021/09/24 16:36:11.000.484 Insert Len 14 RBA 5350

Name: OGGTEST.TEST_NUMBER

After Image: Partition 4 G s

0000 000a 0000 ffff ffff ffff fff5 | ..............

Column 0 (x0000), Len 10 (x000a)

0000 ffff ffff ffff fff5 | ..........

-此值轉換為十進位制為-11，既然這個值是正常的，那麼只有ID_NUMBERTEST值有問題的

GGS tokens:

TokenID x52 'R' ORAROWID Info x00 Length 20

4141 414d 7138 4141 4541 4141 4142 4741 4159 0001 | AAAMq8AAEAAAABGAAY..

TokenID x4c 'L' LOGCSN Info x00 Length 7

3935 3136 3135 37 | 9516157

TokenID x36 '6' TRANID Info x00 Length 8

332e 3132 2e35 3635 | 3.12.565

在目標端測試相關資料：

OGGTEST@orcl>select concat('612','-11') from dual;

CONCAT

------

612-11

OGGTEST@orcl>insert into test_number values (111,612-11);

1 row created.

OGGTEST@orcl>commit;

Commit complete.

OGGTEST@orcl>insert into test_number values (111,'612-11');

insert into test_number values (111,'612-11')

*

ERROR at line 1:

ORA-01722: invalid number

修改OGG引數：

MAP oggtest.test_number, TARGET oggtest.test_number,

COLMAP (USEDEFAULTS, ID_NUMBERTEST=@STRCAT("612",ID));

修改後

MAP oggtest.test_number, TARGET oggtest.test_number,

COLMAP (USEDEFAULTS, ID_NUMBERTEST=@EVAL( ID < 0 ,"612",@STRCAT("612",ID)));

測試，發現問題解決，負值的問題已經解決。

OGGTEST@orcl>select * from test_number;

ID ID_NUMBERTEST

---------- ---------------

0

1

10

100

1000

10000

100000

1000000

10000000

-1

-10

-100

-1000

-10000

-100000

-1000000

-10000000

11 61211

110 612110

1100 6121100

11000 61211000

110000 612110000

1100000 6121100000

11000000 61211000000

-11 612

111 601

26 rows selected.

OGG-01431 OGG-01003 OGG-01151 OGG-01003錯誤處理

相關文章