Oracle 11g Streams: Capture state is showing 'WAITING FOR A SUBSCRIBER TO BE ADDED'

Problem: Capture state is showing  'WAITING FOR A SUBSCRIBER TO BE ADDED' 

select capture_name, state from gv$streams_capture;


You might also encounter sometimes the error ORA-26851 for the propagation process as well:

select propagation_name, error_message from dba_propagation;


Changes

The source or destination database was bounced.

Cause

This problem occurs when the Apply is restarted and the Capture re-connects to the new apply. However, the Capture then restarts because it detects a change in the queue subscribers. When the new Capture tries to connect to the Apply, it gets an error that the apply is already attached to another capture. This is because of unpublished bug 9115882.

Solution

Unpublished bug 9115882 has been fixed on RDBMS release 12.1 and patchset 11.2.0.2.

One possible workaround if you do not have the fix in place is to revert the Capture/Apply processes to the 10g behavior by disabling Combined-Capture-Apply (CCA) as follows:

$ sqlplus /nolog
conn / as sysdba

-- Stop current Propagation and Capture with Force => True, so we clean and pending flags:
exec dbms_propagation_adm.stop_propagation('<propagation_name>', true);
exec dbms_capture_adm.stop_capture('<capture_name>', true);

-- Disable CCA:
exec dbms_capture_adm.set_parameter('<capture_name>', '_DIRECT_APPLY', 'N');

-- Re-enable Propagation and Capture processes normally:
exec dbms_propagation_adm.start_propagation('<propagation_name>');
exec dbms_capture_adm.start_capture('<capture_name>');