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)
Technology Geek 