Oracle Stream Stop Replication without any error

I faces the most strange issue that you will ever seen , Everything was works fine without any problem but suddenly without any error Replication on database stopped Why ?

No one was know , I try everything that you could be imagine , i try to register the archive log manually :

But nothing works, after that i start trace Capture process maybe it will give me hints with following produce:

1. Stop the capture(DBMS_CAPTURE_ADM.STOP_CAPTURE)

2. Enable tracing:

Wait for about 30 minutes an check whether capture is or not progressing..

3. To turn off capture tracing:

 Yes Nothing works , No error , its make me crazy 

Tried checkpoint force, stop/start capture.

tracing the capture process:

exec dbms_capture_adm.set_parameter(‘yourcapturename‘,’trace_level’,’127′);

exec dbms_capture_adm.start_capture(‘yourcapturename‘);
“set trace off after 10-15 mins”:

2. To turn off capture tracing:
exec dbms_capture_adm.set_parameter(‘yourcapturename‘,’trace_level’,null); 

Yea Nothing Works too 

Then I start check if there’s some missing Archivelogs By :

select NAME,SEQUENCE#,STATUS,DELETED from V$ARCHIVED_LOG

Information about archive logs that needed by Oracle Stream :

COLUMN CONSUMER_NAME HEADING ‘Capture|Process|Name’ FORMAT A15
COLUMN SOURCE_DATABASE HEADING ‘Source|Database’ FORMAT A10
COLUMN SEQUENCE# HEADING ‘Sequence|Number’ FORMAT 99999
COLUMN NAME HEADING ‘Required|Archived Redo Log|File Name’ FORMAT A40

SELECT r.CONSUMER_NAME,
r.SOURCE_DATABASE,
r.SEQUENCE#,
r.NAME
FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
WHERE r.CONSUMER_NAME = c.CAPTURE_NAME AND
r.NEXT_SCN >= c.REQUIRED_CHECKPOINT_SCN
order by 3; 

and using this query could help sometimes to give you information TXN about apply process :

select apply_name, xidusn||’.’||xidslt||’.’||xidsqn txn_id, first_scn, first_message_create_time, message_count, spill_creation_time from dba_apply_SPILL_TXN;

Output : (Usually The last one)

After Check The TXN we need to Run the Stream to ignore the required TXN :

1-exec dbms_apply_adm.stop_apply(‘Apply-process-name’);

2-exec dbms_apply_adm.set_parameter(‘Apply-process-name’,’_ignore_transaction’,’TXN-Number’);

3-exec sys.purge_spill_txn(‘Apply-process-name’,’TXN-Number’);

4-exec dbms_apply_adm.start_apply(‘Apply-process-name’);

5-exec dbms_apply_adm.set_parameter(‘Apply-process-name’,’_ignore_transaction’,null);

Capture Site:

1-execute dbms_capture_adm.stop_capture(‘capture-Process-name’);

2-execute dbms_capture_adm.set_parameter(‘capture-Process-name’,’_ignore_transaction’,’TXN-Number’);

3-execute dbms_capture_adm.start_capture(‘capture-Process-name’);

But Nothing works too .

But I remember something Stream got hidden parameter _SGA_Size :

exec dbms_capture_adm.set_parameter(‘Capture-procees-name’,’_SGA_SIZE’,’50’);

exec dbms_capture_adm.start_capture(‘Capture-procees-name’);

The above changes space for named capture process : ‘STRMADMIN_CAPTURE’ from 10M -> 50M 

Thank God Its works again .

Note that i write every steps maybe something will be useful for you . 

Troubleshooting Long-Running Transactions in Oracle Streams (Doc ID 783927.1)

LogMiner out-of-memory (Doc ID 336705.1)