1 源端创建表：

create table oggtest.test_id(id number,name varchar2(16));

并添加附加日志：

add trandata oggtest.test_id

2 目标端创建表：

create table oggtest.test_id(id number,name varchar2(16),id2 number);

创建唯一索引，并将表的列置为不为空

create unique index oggtest.idx_test_id on oggtest.test_id(ID2);

alter table oggtest.test_id modify ID2 number not null;

目标端表如下：

SYS@orcl>desc oggtest.test_id

Name Null? Type

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

ID NUMBER

NAME VARCHAR2(16)

ID2 NOT NULL NUMBER

目标端配置的OGG参数如下：

map oggtest.test_id,target oggtest.test_id;

3 插入数据，进行验证

insert into oggtest.test_id values (1,'xue');

commit;

4 查看OGG的状态及报错信息

查看OGG进程的状态，发现为abend

MANAGER RUNNING

REPLICAT ABENDED REPTB1 00:00:05 00:00:07

查看报错信息，为不能插入空置到非空列。但由于ID2为目标端自定义的一列，故决定使用序列，为插入的数据赋值。

2022-12-30 15:57:13 WARNING OGG-01154 SQL error 1400 mapping OGGTEST.TEST_ID to OGGTEST.TEST_ID

OCI Error ORA-01400: cannot insert NULL into ("OGGTEST"."TEST_ID"."ID2") (

status = 1400). INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "OGGTEST"."TEST_ID" ("ID","NAME")

VALUES (:a0,:a1).

2022-12-30 15:57:13 WARNING OGG-01003 Repositioning to rba 1724 in seqno 20.

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) [0x2b6a770a061e]]

: [/app/oracle/goldengate/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x2ec) [0x2b6a7709915c]]

: [/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) [0x2b6a7709512b]]

: [/app/oracle/goldengate/replicat(take_rep_err_action(short, int, char const*, extr_ptr_def*, __std_rec_hdr*, char*, file_def*, bool)+0xaa7) [0x5

24c31]]

: [/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]]

2022-12-30 15:57:13 ERROR OGG-01296 Error mapping from OGGTEST.TEST_ID to OGGTEST.TEST_ID.

5 解决方法

5.1 创建序列

create sequence oggtest.SEQ_TEST_ID

minvalue 1

maxvalue 9999999999

start with 1

increment by 1

cache 20;

5.2 修改目标端OGG的参数，主要使用sqlexec功能

map oggtest.test_id,target oggtest.test_id,SQLEXEC (ID lookup,QUERY 'select oggtest.SEQ_TEST_ID.nextval cname from dual'),COLMAP (USEDEFAULTS, ID2 = @GETVAL (lookup.cname) );

5.3 启动OGG进程，OGG进程恢复正常

GGSCI (oracle10g1) 27> start reptb1

Sending START request to MANAGER ...

REPLICAT REPTB1 starting

GGSCI (oracle10g1) 28> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REPTB1 00:00:00 00:00:02

5.4 验证数据，发现数据按照期望值进行了入库

SYS@orcl>select * from oggtest.test_id;

ID NAME ID2

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

1 xue 2

goldengate的sqlexec测试