Sql Apply Stops With ORA-1422 When Inserting Duplicate Rows in Table With Lob (Doc ID 2005846.1)

Last updated on MAY 12, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.

Symptoms

On logical standby database, sql apply stops with the following in the alert.log:

Fri Apr 03 19:52:15 2015
LOGSTDBY Apply process AS05 started with server id=5 pid=40 OS id=1773
LOGSTDBY: SQL Apply about to stop with ORA-1422
LOGSTDBY: SQL Apply finished logging error information
LOGSTDBY Apply process AS01 server id=1 pid=36 OS id=1757 stopped
Errors in file ...ls1124_as01_1757.trc:
ORA-01422: exact fetch returns more than requested number of rows
Errors in file ...ls1124_lsp0_1540.trc:
ORA-26808: Apply process AS01 died unexpectedly.
ORA-01422: exact fetch returns more than requested number of rows
LOGSTDBY Apply process AS03 server id=3 pid=38 OS id=1765 stopped
LOGSTDBY Apply process AS04 server id=4 pid=39 OS id=1769 stopped
LOGSTDBY Apply process AS05 server id=5 pid=40 OS id=1773 stopped
LOGSTDBY Apply process AS02 server id=2 pid=37 OS id=1761 stopped
LOGSTDBY Analyzer process AS00 server id=0 pid=35 OS id=1753 stopped
LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=33 OS id=1745 sid=144 stopped
LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=34 OS id=1749 sid=19 stopped
LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=32 OS id=1741 sid=18 stopped

Following select-statement on dba_logstdby_events shows that ORA-1422 happened in SCOTT.TEST1:

SQL> select event_timestamp,event,status from dba_logstdby_events order by event_timestamp;
EVENT_TIMESTAMP
-----------------------------------------------------------------
EVENT
-----------------------------------------------------------------
STATUS
-----------------------------------------------------------------
:
15-04-03 19:52:15.821348
update "SCOTT"."TEST1"
  set
    "COL2" = 'BBB'
  where
    "COL1" = 1 and

ORA-01422: exact fetch returns more than requested number of rows:

SCOTT.TEST1 has lob column, and doesn't have unique key.

SQL> desc SCOTT.TEST1
 Name  Null?  Type
 ----- ------ -------
 COL1         NUMBER
 COL2         CLOB

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms