１ OGG的状态信息
REPLICAT ABENDED repjj 00:23:32 00:02:00
２ 查看OGG报错信息：
2021-08-19 13:34:21 WARNING OGG-01154 SQL error 1400 mapping USER_A.G_TAB_A to USER_B.G_TAB_A
ORA-01400: cannot insert NULL into ("USER_B"."DEL_G_TAB_A"."S_RESOURCEID")
ORA-06512: at "USER_B.TRI_TAB_B", line 5
ORA-04088: error during execution of trigger 'USER_B.TRI_TAB_B' SQL DELETE FROM "USER_B"."G_TAB_A"
WHERE "RE_ID"='711708'.
2021-08-19 13:34:21 WARNING OGG-01003 Repositioning to rba 93641635 in seqno 104110.
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) [0x7f0731cf971e]]
: [/goldengate/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)
+0x2cc) [0x7f0731cf26bc]]
: [/goldengate/libgglog.so(_MSG_ERR_MAP_TO_TANDEM_FAILED(CSourceContext*,
ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> co
nst&, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, CMessageFactory::
MessageDisposition)+0x53) [0x7f0731cee581]]
: [/goldengate/replicat(take_rep_err_action(short, int, char const*, extr_ptr_def*, __std_rec_hdr*,
char*, file_def*, bool)+
0xdac) [0x521a80]]
: [/goldengate/replicat(process_extract_loop()+0x2388) [0x53ad18]]
: [/goldengate/replicat(main+0x732) [0x54cbd2]]
: [/lib64/libc.so.6(__libc_start_main+0xf5) [0x7f072c0cb3d5]]
: [/goldengate/replicat(__gxx_personality_v0+0x31a) [0x4c0c6a]]
2021-08-19 13:34:21 ERROR OGG-01296 Error mapping from USER_A.G_TAB_A to USER_B.G_TAB_A.
３ 根据生成的discard文件查看执行SQL绑定的变量值：
Current time: 2021-08-19 13:40:33
Discarded record from action ABEND on error 1400
ORA-01400: cannot insert NULL into ("USER_B"."DEL_G_TAB_A"."S_RESOURCEID")
ORA-06512: at "USER_B.TRI_TAB_B", line 5
ORA-04088: error during execution of trigger 'USER_B.TRI_TAB_B' SQL DELETE FROM "USER_B"."G_TAB_A" WHERE "RE_ID"='711708'
Aborting transaction on ./dirdat/yx/ya beginning at seqno 104110 rba 93641635
error at seqno 104110 rba 93641635
Problem replicating USER_A.G_TAB_A to USER_B.G_TAB_A
Mapping problem with delete record (target format)...
*
RE_ID = 711708
L_ID = 1110899
S_ID = 2200000327
RE_FLAG = 1
SOURCE_CHANGE_TIME = 2021-08-19 13:10:49
TARGET_WRITE_TIME = 2021-08-19 13:40:33
C_LINE_RESOURCEID = 358
LINE_RESOURCEID = 711708
C_S_RESOURCEID = 1590
S_RESOURCEID = 6122200000327
*
Process Abending : 2021-08-19 13:40:33
4 查看表结构，G_TAB_A表不能为空的只有RE_ID列，且源端和目标端一致，根据discard文件，此列也是有值的。仔细观察报错信息，发现问题出现在ORA-04088上，是触发器的问题
SYS@source1 >desc USER_A.G_TAB_A
Name Null? Type
--------------- -------- -------------------
RE_ID NOT NULL NUMBER(16)
L_ID NUMBER(16)
S_ID NUMBER(16)
RE_FLAG VARCHAR2(8)
目标端：
SYS@target1 >desc USER_B.G_TAB_A
Name Null? Type
------------------------ -------- --------------------
RE_ID NOT NULL NUMBER(16)
L_ID NUMBER(16)
S_ID NUMBER(16)
RE_FLAG VARCHAR2(8 CHAR)
SOURCE_CHANGE_TIME DATE
TARGET_WRITE_TIME DATE
SOURCE_CHANGE_TIME2 DATE
TARGET_WRITE_TIME2 DATE
C_LINE_RESOURCEID NUMBER(19)
LINE_RESOURCEID NUMBER(19)
C_S_RESOURCEID NUMBER(19)
S_RESOURCEID NUMBER(19)
5 查看对应的触发器，发现状态为开启的，没有禁用，问题应该就发生在这里了。
SYS@target1 >select OWNER,TRIGGER_NAME,TRIGGER_TYPE,STATUS from dba_triggers a
where a.owner in ('USER_B') and trigger_name='TRI_TAB_B' order by status;
OWNER TRIGGER_NAME TRIGGER_TYPE STATUS
------------------------------ ------------------------------ ---------------- --------
USER_B TRI_TAB_B AFTER EACH ROW ENABLED
6 查看触发器的内容,发现主要的作用就是将G_TAB_A表删除的数据插入到del_G_TAB_A表，且G_TAB_A表中的 S_RESOURCEID 为可为空，但 del_G_TAB_A表d S_RESOURCEID 列为不可为空,故导致OGG进程再删除数据时，触发器报错导致的OGG进程异常。
set linesize 300
set pagesize 999
set long 99999
select dbms_metadata.get_ddl('TRIGGER','TRI_TAB_B','USER_B') from dual;
DBMS_METADATA.GET_DDL('TRIGGER','TRI_TAB_B','USER_B')
--------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER "USER_B"."TRI_TAB_B" --触发器名
After Delete On G_TAB_A
For Each Row
Declare
-- LOCAL VARIABLES HERE
Begin
Insert Into del_G_TAB_A
(S_RESOURCEID, line_resourceid, c_line_resourceid, c_S_RESOURCEID,RE_ID, L_ID, S_ID, RE_FLAG,
source_change_time,target_write_time, source_change_time2, target_write_time2, del_time)
Values
(:old.S_RESOURCEID, :old.line_resourceid, :old.c_line_resourceid, :old.c_S_RESOURCEID,:old.RE_ID,:old.L_ID,
:old.S_ID,:old.RE_FLAG, :old.source_change_time, :old.target_write_time,
:old.source_change_time2, :old.target_write_time2, Sysdate);
End TRI_TAB_B;
表结构
SYS@target1 >desc "USER_B"."DEL_G_TAB_A"
Name Null? Type
------------------------ -------- -------------------
S_RESOURCEID NOT NULL NUMBER(19)
LINE_RESOURCEID NOT NULL NUMBER(19)
C_LINE_RESOURCEID NUMBER(19)
C_S_RESOURCEID NUMBER(19)
RE_ID NOT NULL NUMBER(16)
L_ID NUMBER(16)
S_ID NUMBER(16)
RE_FLAG VARCHAR2(8 CHAR)
SOURCE_CHANGE_TIME DATE
TARGET_WRITE_TIME DATE
SOURCE_CHANGE_TIME2 DATE
TARGET_WRITE_TIME2 DATE
DEL_TIME DATE
7 禁用触发器，再次启动进程，OGG进程恢复正常。
使用如下语句生成禁用触发器的语句：
SYS@target1 >SELECT 'alter trigger '||owner||'.'||trigger_name||' disable'||';' from dba_triggers
where owner in ('USER_B') and trigger_name='TRI_TAB_B' and STATUS='ENABLED';
'ALTERTRIGGER'||OWNER||'.'||TRIGGER_NAME||'DISABLE'||';'
------------------------------------------------------------
alter trigger USER_B.TRI_TAB_B disable;
执行禁用触发器的语句：
SYS@target1 >alter trigger USER_B.TRI_TAB_B disable;
Trigger altered.
启动OGG进程：
GGSCI (yxjcptdb3) 7> start repjj
Sending START request to MANAGER ...
REPLICAT repjj starting
OGG进程恢复正常。
GGSCI (yxjcptdb3) 25> info repjj
REPLICAT repjj Last Started 2021-08-19 13:53 Status RUNNING
Checkpoint Lag 00:00:08 (updated 00:00:00 ago)
Log Read Checkpoint File ./dirdat/yx/ya104113
2021-08-19 14:27:03.000192 RBA 136283490