ORA-01555 - best way to manage rollback segments? 1

[MCubitt]

Hi there,

I get this message:
Code: ORA-01555
Text: snapshot too old: rollback segment number 11 with name "_SYSSMU11$" too small

And from what I can gather, I need to add or make larger the number of rollback segments.

Our rollback segments are in SYSTEM tablespace, which is 1Gb and 92% full.

I can increase the size (not a problem) but what exactly should I do?

Do I increase the size of SYSTEM to, say, 1.5Gb and then how do I increase the size of the rollback segments? Or should I add more?

Thank you,






There's no need for sarcastic replies, we've not all been this sad for that long!

 

[sem]

In fact you should move your RBS out of SYSTEM tablespace

Regards, Dima

http://seminihin.narod.ru

 

[MCubitt]

Really? This system was installed and configured by an external supplier and so I will havbe to ask them I guess (though I am taking on DBA role, there may be reasons for their method).

So how can I expand the rollback segment?!



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[alexhu]

MCubitt - you may like to ask them why, as you're using 9i, that UNDO is not configured instead of rollback

Alex

 

[sem]

The only encouraged (and mandatory!) rollback segment in SYSTEM tablespace is SYSTEM, which is an essential part of Oracle database. All RBS's used by applications should be created in other ablespace(s). I may suggest you to create a new undo tablespace with AUTOEXTEND and ALTER SYSTEM SET undo_tablespace = <new undo tablespace> scope spfile.

Regards, Dima

http://seminihin.narod.ru

 

[MCubitt]

Alex,

Oh gosh, maybe it is!

We have UNDO table space and REDO logs, oh isn't it confusing?

TABLESPACE_NAME Tot Size (MB) Used (MB) % Used % Free
------------------------------ ------------- ---------- ---------- ----------
IFSAPP_ARCHIVE_DATA 100 1 1 99
IFSAPP_ARCHIVE_INDEX 100 1 1 99
IFSAPP_DATA 11000 10246 93 7
IFSAPP_INDEX 5000 2558 51 49
IFSAPP_REPORT_DATA 1000 209 21 79
IFSAPP_REPORT_INDEX 1000 11 1 99
OEM_REPOSITORY 92 87 95 5
SYSTEM 1000 925 92 8
UNDOTBS 2400 69 3 97
USERS 20 0 0 100


I have noticed that since the user is running the job which causes the error (takes 1 hour) the UNDO table space is being used...

At 10.02am it was 4.29% used and it reached 6% but has now dropped again (could be red herring).






There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

sem/Alex

In that case, it could be that UNDO is being used instead?

From the alert log,
Thread 1 advanced to log sequence 4706
Current log# 5 seq# 4706 mem# 0: /redolog1/IFSL/redo05.log
Current log# 5 seq# 4706 mem# 1: /redolog2/IFSL/redo05a.log
Mon Jun 14 08:37:35 2004
Created Undo Segment _SYSSMU11$

and _SYSSMU11$ was mentioned in the error.

So is it these beauties I need to increase?



There are 5 redo log groups, redo01 and redo01a through redo05 and redo05a.

Each is 51,200 Kbs.


How does one increase the size of these.. if that's where I should be going?

Thanks


There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SantaMufasa]

MCubitt,

Keep in mind that "external suppliers" are
a) humans [sometimes of lower intelligence or untrained in nuances of "Best DBA Practices"] and b) "Developers" (rarely DBAs).

As Dima suggests, IN NO CASE SHOULD ROLLBACK SEGMENTS EVER BE IN THE SYSTEM TABLESPACE !!!! The purpose of have a SYSTEM tablespace is to avoid/reduce/eliminate fragmentation against the most heavily hit tablespace (since it houses the data dictionary). Rollback Segments, by definition are database objects most prone to (causing) fragmentation.

If your "external supplier" agrees, they are admitting that they "did it wrong". So you probably will get some sort of marketing mumbo-jumbo double-talk to try and justify their design decision. You certainly will not get a technically astute rationale.

Since you probably will not get any co-operation from the supplier, you will need to remedy the situation yourself. If you are not the DBA, or if you are the DBA but not trained in how to create UNDO or Rollback-Segment infrastructure, then you should get help from someone who is experienced in this. If we (Tek-Tipsters) are your best/only alternative, then let us know and we can suggest methodology/syntax to achieve an improved situation.

Luckily, your correcting this problem (i.e., moving rollback out of the SYSTEM tablespace) does not need to impact your application.

Then, to the topic of how to resolve your problem with "ORA-01555: snapshot too old...", I highly recommend you read FAQ: "ORA-01555: Snapshot too old: rollback...too small". Why do I get it? How can I stop it? faq759-4436.

We look forward to your response,

Mufasa
(aka Dave of Sandy, Utah, USA @ 12:08 (16Jun04) UTC (aka "GMT" and "Zulu"), 05:08 (16Jun04) Mountain Time)

 

[sem]

Try to increase UNDO_RETENTION parameter.

Regards, Dima

http://seminihin.narod.ru

 

[DeepDiverMom]

Dima (Sem),

For those of us that are only trained in the pre-9i use of Rollback Segments, prior to UNDO segments, could you (or anyone else) please recommend a web-link that provides a good tutorial/explanation of the behaviors of UNDO and how they compare and contrast with traditional rollback segments?

Thanks,

Becca

 

[vivekm]

You will need to increase the size of your undo tablespace. Find out which datafile UNDOTBS tablespace is using:

select file_name from dba_data_files
where tablespace_name = 'UNDOTBS';

Then resize that datfile:
ALTER DATABASE
DATAFILE '<name of file>' RESIZE 3000M;

But Undo tablespace increases and shrinks with time. You may find that your undo_retention parameter may be set to an inappropriate value ( this value is in no of seconds). The system retains undo for at least this time specified. Your error indicates that you may need to increase this parameter. Please provide value of this parameter by typing:
select name, value from v$parameter
where name = 'undo_retention';

Regards,
Vivek

 

[MCubitt]

Santa,

I think it was my misunderstanding. It appears this is the system RBS, not the applications. Redo logs have been created elsewhere. Thanks for the explanation, however.



Sem,

Thank you. Using Enterprise Manager I cannot find that parameter.

Is this:
ALTER SYSTEM SET UNDO_RETENTION = {value} ?

Surely, from the error, it is not the retention but the size of the storage that is the problem? Do I not need to increase the physical space?












There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

vivekm,

The current retention is the default 900 seconds.

The job took over 1 hour to "save" data and then produced the ORA error.

If I INCREASE the value to, say, 2,400 seconds, I assume I need to increase the undo tablespace?




There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

The Undo table space is 2.4Gb and only 2.37% used. Surely this cannot be the problem. As teh user was saving the data, I reviewed the Undo table space and it topped 6% used!

I am sure it is teh redo logs that is the problem, not undo space. Or am I talking out of my bottom?




There's no need for sarcastic replies, we've not all been this sad for that long!

 

[sem]

Becca,
In fact I'm also no formally training, neither in 9i nor in 7-8, but quick "googling" provides a number of links, e.g.

http://www.siue.edu/~dbock/cmis565/ch10-undo_data.htm

Regards, Dima

http://seminihin.narod.ru

 

[MCubitt]

Guys,

Can you clarify if this is redo or undo problem and how you can tell? Also, how to I increase the one with the problem?

I am assuming it is redo because they are so small and undo has ample.

Thanks



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[carp]

It has nothing to do with REDO. Redo logs are part of the instance recovery mechanism. Rollback/Undo is part of the read-consistent image/transaction-cancelling mechanism.
Also note that your redo logs are not in any tablespace; they're files on the O/S.

ORA-1555 is normally associated with rollback/undo problems.

 

[MCubitt]

Oh gee, why couldn't we have stuck with Db2!

So what do I need to do in order to stop this error message and allow the user to complete the process?




There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

I'll up the undo retention to 2,400 and see how it goes tomorrow.


There's no need for sarcastic replies, we've not all been this sad for that long!

 

[carp]

Rather than reinvent the wheel, let me direct you to an exhaustive, enlightening, and sometimes entertaining thread on this subject:

thread186-628107

Pay particular attention to SantaMufasa's solution.