Thanks Tim for your response to this. I agree with you. For now, I have
come up with this theory :
I feel that the "Unable to use system rollback tablespace" errors were a
result of the "Out of transaction slots" message. Transactions were
assigned as they came in to different transaction slots in the rollback
segments. Suppose, we had 21 transaction slots in each of the 20 rollback
segments. This were utilized one by one by different transactions, and
never released. So, the transactions never commited or rolled back, and
they kept coming in. I think, as all the transaction slots in a rollback
segment were utilized, that rollback segment was marked as not available
for any more transactions. So, one by one, the rollback segments started
going unavailable. No errors were reported anywhere in the logs when this
was happening, because there were transaction slots available in other
rollback segments. But finally when the last transaction slot in the last
available rollback segment was utilized, the application log reported the
"Out of transaction slots" in the error log to the next incoming
transaction. This would also mark all the rollback segments as not
available for transaction, whereby Oracle would then try to make use of the
system rollback segment. Hence, all subsequent errors were for "Unable to
use system rollback segment for non system tables".
This answers my questions, why did the "Out of transaction slots" error
happen just once, whereas the "Unable to use system rollback" errors got
reported for every subsequent transaction? Also, why was the "Out of
transaction slots" reported first?
Does it make sense? Anyways, I plan to conduct a test tomorrow where I keep
just one rollback segment online, start more than 20 transactions, dont
commit them, and then check the errors that should hopefully be reported
after the 21st session. I wonder what the status of the rollback segment
would be?
Thanks
Raj
"Tim Gorman"
<Tim@SageLogi To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
x.com> cc:
Sent by: Subject: Re: Out of transaction slots
root@fatcity.
com
October 03,
2002 07:01 PM
Please
respond to
ORACLE-L
comments inline...
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, October 03, 2002 4:34 PM
> 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.
The total number of transaction slots is not relevant; only the number of
slots per RBS. A new transaction is first assigned to an RBS; the
algorithm which chooses is strictly LRU -- the number of available slots in
the transaction table doesn't enter into it (though it easily could)...
>
> 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.
The percentage mix of SELECTs vs DML is kind of irrelevant, especially when
dealing with small transaction tables in 2k database blocks (i.e. 21
entries). Even if only 10% of all SQL statements are DML, how long would
it
take to generate 420 (i.e. 21 tt slots times 10 RBS) of them? An hour? A
day? If the transactions are not committing promptly and properly, then
the
percentage mix only accelerates or decelerates the rapidity of getting
ORA-01554; it's going to happen regardless...
...I would bet that a change has happened to the application recently which
is somehow preventing prompt commits of transactions from occurring, and
that they are just piling up. Do you have good change-management on
application code changes in place?
> 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?
I'm not certain, but you may only be assuming that the RBS actually went
OFFLINE, just because the error (I'm guessing that it was ORA-01552?) is
commonly associated with unavailable non-SYSTEM rollback segments. Unless
you actually saw the status OFFLINE somewhere, it may not be useful to
assume that to be the case...
>
> Thanks
> Raj
Wow! Crazy situation! Best of luck -- sorry not to offer any real help...
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Gorman
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
