Out of transaction slots

Hi All,

OPS 8.0.6.2 on Sun 2.6 nodes. The other day, the users reported that they were unable to complete transactions, and before we could take a proper look, the database was shutdown aborted, and started up. I am now trying to investigate possible reasons for these errors. Just before the shutdown at 00:15 AM, the alert log reported an error saying "ORA-01595: error freeing extent (8) of rollback segment (2)) ORA-01554: out of transaction slots in transaction tables". Now, we have 20 rollback segments, 10 on each node, on a block size of 2k. So that would mean about approximately a total of
(21*20) transaction slots.

Later, we found that that application logs reported the ORA-01554 almost 2 hours before the alert log entry. Later, the logs had multiple errors saying "ORA???? - Unable to use system rollback segment for non system tables". No one had taken the rollback segments offline. Also, there wasnt any large amount of transactions running as is reflected by the redo log switches. Also, application team says 90% of the transactions are selects. So, for whatever reason, the rollback segments were made unavailable. But I am not able to confirm this. Should Oracle not be creating a trace file when a rollback segment goes unavailable? Also, why was the out of transaction slots error reported in the alert log just before the shutdown, when in fact, the application reported it much earlier? Have not been to get a definite answer yet on my TAR. Any pointers, or clues to look for?

Thanks
Raj

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Rajesh.Rao_at_jpmchase.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).