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

Last updated on FEBRUARY 04, 2022

Applies to:

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 :

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
NAME.REGION.EXAMPLE.COM
FALSE

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.