Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Conseq. of UNDO_SUPPRESS_ERRORS=TRUE when UNDO_MANAGEMENT=MANUAL

Re: Conseq. of UNDO_SUPPRESS_ERRORS=TRUE when UNDO_MANAGEMENT=MANUAL

From: Joel Garry <joel-garry_at_home.com>
Date: 23 Dec 2004 13:22:55 -0800
Message-ID: <1103836975.793452.249010@c13g2000cwb.googlegroups.com>

Spendius wrote:
> We've seen lots of ORA-01555 appearing in our alert.log file,
> with the exact phrasing:
> > ORA-01555 caused by SQL statement below (SCN: 0x0002.614e9d7c)
> > [SQL stmt follows]
>
> What does it mean ? Did the user's sessions raise an error ? We
> doubt because we NEVER had any such feedback from them...
> All of the statements were SELECTs (this error also happened for
> a SELECT SYSDATE FROM DUAL !).
>
> Someone can tell me if anything's wrong ?
>
> Thanks, and regards...
> Spendius

01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"

// *Cause: rollback records needed by a reader for consistent read are
//         overwritten by other writers
// *Action: Use larger rollback segments

That's a bit misleading, as there are several other things which can cause it, varying by version. Search metalink if you are using flashback.

Remember, this is a consistency error, so it means someone was trying to read something that requires reconstruction of how the database looked when _another_ transaction started. So a simple select can blow up if something in the select's transaction requires something in another transaction that is long running and has had it's rbs overwritten for whatever reason.

So things to consider and research: delayed block cleanout; use of optimal in your rollbacks (I say, Don't!); size and volatility of buffer cache (if the block needed for reconstruction is there, mightn't need rbs); and look at long running transactions. Metalink and cdos both have good explanations, and so do some books. http://metalink.oracle.com/metalink/plsql/ml2_documents.showFrameDocument?p_database_id=NOT&p_id=40689.1

Whether or not people will complain is based mostly on whether the users think anything will come of complaining and whether the application is coded to handle such things nicely so the users don't see anything wrong (I think, it's been a long time since I've done such coding).

See the manual about UNDO_SUPPRESS_ERRORS - it is only for automatic undo mode. So why aren't you using automatic undo mode, anyways? Is this some sort of strange upgrade?

jg

--
@home.com is bogus.
"We want to increase Internet penetration." - Pawan Duggal
http://seattlepi.nwsource.com/business/aptech_story.asp?category=1700&slug=India%20eBay%20Sex
Received on Thu Dec 23 2004 - 15:22:55 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US