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 :
ALTER DATABASE REGISTER LOGICAL LOGFILE
‘Archivelog-name’ FOR ‘Capture-name’;

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:

sqlplus /nolog
connect / as sysdba
alter system set events ‘26700 trace name context forever, level 6’;
alter system set events ‘1349 trace name context forever , level 1024’;
exit

sqlplus strmadmin/passwd (streams admin)

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

Wait for about 30 minutes an check whether capture is or not progressing..
3. To turn off capture tracing:

sqlplus strmadmin/passwd (streams admin)

Stop the capture(DBMS_CAPTURE_ADM.STOP_CAPTURE)
exec dbms_capture_adm.set_parameter(‘yourcapturename’,’trace_level’,null);
exit

sqlplus /nolog
connect / as sysdba

alter system set events ‘26700 trace name context off’;
alter system set events ‘1349 trace name context off’;
exit

 Yes Nothing works , No error , its make me crazy 
Tried checkpoint force, stop/start capture.
tracing the capture process:

1. Stop the capture(DBMS_CAPTURE_ADM.STOP_CAPTURE)

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)
APPLY_FROM_MARKA               9.27.637276                         5600212722474 17-OCT-11            25      18-OCT-11                                 
APPLY_FROM_MARKA               4.23.369085                         5600212761449 17-OCT-11             1       18-OCT-11                                 
APPLY_FROM_MARKA               56.17.156892                        5600242177417 18-OCT-11             1       18-OCT-11                                 
APPLY_FROM_MARKA               4.40.369226                          5600243099471 18-OCT-11             1       18-OCT-11                                 
APPLY_FROM_MARKA               24.21.419884                         5600247380679 18-OCT-11            15      19-OCT-11                                 
APPLY_FROM_MARKA               14.20.166840                         5600278453686 19-OCT-11             1       19-OCT-11                                 
                           
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 :
Capture needs to be configured to have more space.
The default amount of space for capture / log miner activity is 10
Steps :

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 . 
Some metalink note will be useful too :
Troubleshooting Long-Running Transactions in Oracle Streams (Doc ID 783927.1)
LogMiner out-of-memory (Doc ID 336705.1)

One thought on “Oracle Stream Stop Replication without any error

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.