topic ORA-01555: snapshot too old in Operating System - HP-UX

ORA-01555: snapshot too old

kohli — Mon, 29 Sep 2003 19:51:02 GMT

Hi all,

I know that this problem is all too common but I really need some help on this. I'm new with Databases so please bear with me. I feel that the common solution given to this problem doesn't always work. I have extended my rollback by 3 folds but still doesn't work. The rollback segments didn't even grow. Exporting as SYSTEM gave no problems. Can it be due to the insufficient rollback segments?
I have seven rollback segments and with approx 160 DB users at a given time. Can adding more rollback segments help?

Re: ORA-01555: snapshot too old

twang — Mon, 29 Sep 2003 22:45:27 GMT

"ORA 1555 snapshot too old" can be caused by "rollback segment too small", it is because rollback records needed by a reader for consistent read are overwritten by other writers, the operation that failed the information it needs to construct a read consistent view of the data is no longer available. The main way to avoid this is to use larger rollback segments across the system. This gives you a longer period of grace before 'old' rollback infomation gets overwritten. It is typically no use just enlarging a single rollback segment.
For long running reports, try to run these when the database is lightly loaded so that the chance of a rollback segment wrapping is reduced (as there are fewer transactions in progress and hence less rollback information being generated). Try using an ORDER BY clause that forces a sort in the query - this should produce the row-source up front so undo is only needed while this ordered row-source is produced.

Re: ORA-01555: snapshot too old

kohli — Mon, 29 Sep 2003 22:52:01 GMT

My problem is when I do a DB export.
I did enlarge all rollback segment Initial and Next extent size but still have the same problem. How do we determine how many rollback segments is sufficient for our system?

Re: ORA-01555: snapshot too old

twang — Mon, 29 Sep 2003 23:30:16 GMT

What version of oracle are you using? What is the exact error message?

Re: ORA-01555: snapshot too old

Yogeeraj_1 — Tue, 30 Sep 2003 01:55:54 GMT

hi,
*
I think support note <40689.1> covers this topic very well
*
see attachment.
*
regards
Yogeeraj

Re: ORA-01555: snapshot too old

Yogeeraj_1 — Tue, 30 Sep 2003 02:00:46 GMT

hi,
*
sorry forgot the attachment.
Note that the only CAUSE of a 1555 is improperly sized rollback segments.
*
Note that in 9i, this might be a little easier to deal with as you can set an undo
rentention period.
*
regards
Yogeeraj

Re: ORA-01555: snapshot too old

Graham Cameron_1 — Tue, 30 Sep 2003 02:36:29 GMT

Your problem is that you are trying to export whilst your database is in use. ie transactions are updating the database whilst the export is in progress.
Oracle attempts to give your export a consistent view of the data, but runs out of rollback to do this - there must be a lot of updating going on.

The safest route is to export the database whilst no-none is using it. There are 2 ways:

1. Politely ask all users to log off for the duration.

2. Shutdown immediate, then startup restrict the database; run your export (as privileged user); shutdown, startup normal the database.

-- Graham

Re: ORA-01555: snapshot too old

kohli — Tue, 30 Sep 2003 06:16:16 GMT

I'm using Oracle 8.1.7.4 on HP UX.
This is a system running 24x7 so shutting down is a no-no. Asking users to log out is not an option in this environment. I know that creating more rollback segments helps...but does a larger minimum extent help?

Re: ORA-01555: snapshot too old

Dave Walley — Tue, 30 Sep 2003 08:26:45 GMT

Hi.

When you did the export did you say CONSISTENT=Y because if you did the export will ensure that the export copies all tables as they were when the first table is exported. This will mean extra rollback is used. Try setting consistent=N.

Dave

Re: ORA-01555: snapshot too old

Graham Cameron_1 — Tue, 30 Sep 2003 08:37:31 GMT

If shutting down or asking users to log out is a no-no, one has to wonder in that case what you are trying to achieve with your export.

You should be aware that export of a database against which transactions are taking place is not in any way a secure backup.

However, if export is what you really want, then you need to increase the size of your rollback segments, or add some larger ones.
Note that extending the tablespace which contains the rollback segments is not enough.

You can query your existing with

select SEGMENT_NAME, TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, status
from dba_rollback_segs
/

To add new ones, you want something like:

CREATE ROLLBACK SEGMENT
TABLESPACE
STORAGE (INITIAL 100m NEXT 100m
MAXEXTENTS unlimited)
/

Then you need to bring it online with
ALTER ROLLBACK SEGMENT ONLINE
/

You should also add it to your init.ora so that is available after database restart.

Hope this helps. It's a big subject for someone new to databases.
You should really read the oracle documentation.
If you don't have the CD you can download it from
http://otn.oracle.com/documentation/oracle8i.html
(you may have to register first).

-- Graham