Streams | ORA-24082 while disabling a propagation job

The reason for the ORA-24082 error is that a message has been enqueued  onto the AQ$_PROP_NOTIFY_<N> queue to notify the job_queue_process associated with  the propagation schedule to stop propagating messages and the
job_queue_process has failed to acknowledge that message within a  reasonable amount of time.

dbms_aqadm.disable_propagation_schedule(
queue_name => ‘STRMADMIN. SCOTT_PROPAGATION_Q’, destination => ‘${DEST_DB}’, destination_queue => ‘STRMADMIN. SCOTT_PROPAGATION_Q’);

stopping SCOTT_PROPAGATION
BEGIN
*
ERROR at line 1:
ORA-24082: propagation may still be happening for the schedule for QUEUE
STRMADMIN.SCOTT_C_Q and DESTINATION STREAMS
ORA-06512: at line 11

Use stop_propagation with Force option

select PROPAGATION_NAME,status from dba_propagation where propagation_name=’SCOTT’;

PROPAGATION_NAME                   STATUS
————————————————————————————
SCOTT_P                            ENABLED

SQL> execute dbms_propagation_adm.stop_propagation(propagation_name=>’SCOTT_P’,force=>TRUE);
PL/SQL procedure successfully completed.

select PROPAGATION_NAME,status from dba_propagation where propagation_name=’SCOTT’;

PROPAGATION_NAME                   STATUS
———————————————————————————–
SCOTT_P                            ABORTED

If you have number of propagation processes within database to stop then try bash script below

#!/bin/bash
# This script will connect to each Database defined at array “SIDs”
# Disable Capture process
# Disable Propogation process
#
# If you want to run SQL commands at ALL instances active on the Box please use line below
# typeset -a SIDS=($(ps -ef | grep pmon | grep -v grep | awk -F_ ‘{print $NF}’))
#
# Connect to specific database defined at SIDS=(test1 test2 test3)
typeset -a SIDS=(ORCL)

for x in $(seq 0 $((${#SIDS[*]}-1)))
do
export ORACLE_SID=${SIDS[x]}
export ORACLE_HOME=$(awk -F: ‘/’^$ORACLE_SID’/ {print $2}’ /etc/oratab)
typeset -a UIDS=($(
$ORACLE_HOME/bin/sqlplus -s strmadmin/strmadmin <<EOF | grep -v “SQL>” | grep “M_A_R_K” |awk ‘{print $1}’
set feedback off
set pages 0
set hea off
select capture_name, ‘M_A_R_K’  from sys.streams$_capture_process where status= 1;
order by 1;
quit
EOF
))
for ID in ${UIDS[*]}
do
#
# DEST_DB — Set this variable to Target Database
# If your destination_queue name is different then please change value accordingly
#
$ORACLE_HOME/bin/sqlplus  -s strmadmin/strmadmin <<EOF
set pages 0;
set feedback off;
set linesize 200;
Begin
dbms_aqadm.disable_propagation_schedule(
queue_name => ‘STRMADMIN.${ID}_Q’,
destination => ‘${DEST_DB}’,
destination_queue => ‘STRMADMIN.${ID}_Q’);
EXCEPTION
WHEN OTHERS THEN
IF sqlcode = -24082 THEN
execute dbms_propagation_adm.stop_propagation(propagation_name=>’${ID}’,force=>TRUE);
END IF;
IF sqlcode = -24064  THEN NULL;
ELSE RAISE;
END IF;
END;
exit;
EOF
echo “Proceed/Cancel (P/C)?”
read A
[[ $A != “p” && $A != “P” ]] && exit
#    “break” will skip the remaining users for this SID and continue with the next SID
#    To skip everything (the remaining users for this SID as well as the remaining SIDs)replace “break” with “exit”.
done
done