Combined Capture and Apply (CCA) : Capture aborts : ORA-1422 after schedule_propagation (Doc ID 1059029.1)

Last updated on MAY 11, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.3 [Release 11.1 to 11.2]
Information in this document applies to any platform.

Symptoms

An attempt has been made to unschedule / schedule propagation connected with
a Capture process running in CCA Mode : Combined Capture and Apply.

It is likely that the unschedule operation encountered the error :
ORA-24042: no propagation schedule exists for QUEUE <schema.source_queue>
and DESTINATION <dblink>

The Capture process then fails with the following errors after it has been stopped and
restarted :

ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows

If we examine dba_queue_schedules might see :

select schema, qname, destination from dba_queue_schedules where message_delivery_mode='BUFFERED';



SCHEMA QNAME
------------------------------ ------------------------------
DESTINATION
--------------------------------------------------------------------------------
STRMADMIN STREAMS_CAP_QUEUE
TARMM2.US.ORACLE.COM

STRMADMIN STREAMS_CAP_QUEUE
"STRMADMIN"."STREAMS_APP_QUEUE"@TARMM2.US.ORACLE.COM

Note :
destination = 'db link ' - a destination of this form is associated with queue_to_queue = FALSE;
destination = 'schema.queue@dblink - a destination of this form is associated with queue_to_queue = TRUE


i.e we effectively have 2 equivalent entries for the same source / destination
queue combination. In order to determine which one is expected , we need to check what the queue_to_queue parameter is in dba_propagation.

select propagation_name, source_queue_name, destination_queue_owner, destination_queue_name, destination_dblink, queue_to_queue from dba_propagation;


PROPAGATION_NAME SOURCE_QUEUE_NAME
------------------------------ ------------------------------
DESTINATION_QUEUE_OWNER DESTINATION_QUEUE_NAME
------------------------------ ------------------------------
DESTINATION_DBLINK
--------------------------------------------------------------------------------
QUEUE
-----
STRMADMIN_PROPAGATE STREAMS_CAP_QUEUE
STRMADMIN STREAMS_APP_QUEUE
TARMM2.US.ORACLE.COM
FALSE

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