Friday, October 21, 2011

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)

1 comment:

  1. Thank you for this post. Increasing SGA resolved out issue and helped avoid data loss.

    ReplyDelete