ORA-01555

[RottPaws]

I've been searching and reading about this error for 2 days, but haven't been able to resolve my problem.

I'm getting the following error:
ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$" too small

My rollback and undo settings are below

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
max_rollback_segments                integer     37
rollback_segments                    string
transactions_per_rollback_segment    integer     5

undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1

The error has persisted through the database being shutdown and restarted and even through the server being rebooted.

There are no other users in the database and no other transactions being processed.

I'm stumped.

------------------------------------

I've got a new database that is going to be a sort of data warehouse. I've got a script that has been loading data into it for a couple of weeks without problems.

Then a couple of days ago I tried to run a SELECT query while an INSERT query was running (I didn't realize it was running at the time) and it crashed.

I've got 3 tables. COMMENT_WORDS, COMMENT_WORDS_TEMP, and COMMENT_DICTIONARY.

My script generates a flat file and then uses SQLLDR to dump the data into COMMENT_WORDS_TEMP. Next a query runs that identifies any unique words in that table that are not in the COMMENT_DICTIONARY table and then insert them. Finally, a query is run to insert the data from COMMENT_WORDS_TEMP into COMMENT_WORDS using the WORD_ID from COMMENT_DICTIONARY instead of the WORD in the TEMP table.

The COMMENT_WORDS table has over 200 million rows. I get the 01555 error when I try to run a SELECT COUNT(*) query on it. However, I was able to run a SELECT * FROM COMMENT_WORDS query.

The COMMENT_WORDS_TEMP table has 539519 records. If I try to run a query that joins COMMENT_WORDS to COMMENT_WORDS_TEMP I get the 01555 error then too.

But when I try to run the query to insert rows from TEMP into COMMENT_WORDS, I get the following error:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [], []

Anybody have any ideas???

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[SantaMufasa]

RottPaws,

Your "10800" value for undo_retention theoretically means 3 hours "innoculation" against ORA-01555. I use the term "theoretical" because of this scenario (that pokes a hole in UNDO_RETENTION's ability to prevent ORA-01555):

1) At 12:01, User A performs some sort of UPDATE, INSERT, or DELETE on one of your tables, then, without commiting or rolling back, she decides to go out for a long lunch. The "clock" starts ticking on her 10,800 seconds at 12:01.

2) At 14:56 (10,500 seconds following User A's DML statement), You start your "SELECT COUNT(*)..." query that depends, in part, on consistent data from User A's transaction.

3) At 14:57, User A either rollsback or commits her change.

4) At 15:01, Oracle releases User A's rollback entries (that You depend upon).

5) At 15:04, User C generates changes that overwrite User A's original rollback data.

6) At 15:10, You reach the point in your query where you need rollback data (that was consistent when your transaction began) that is now gone forever from the rollback segment.

Voila, ORA-01555 !

Now obviously, I do not know if this specific scenario existed in your case, but I do know (because or your receiving ORA-01555) that some adverse scenario occurred that resulted in the error.

To prevent your receiving ORA-01555, you could possibly experiment with increasing undo_retention to allow for many more hours of retention, but frankly, there still exists the possibility of ORA-01555 no matter what the size of your undo_retention.

Because of this the weaknesses that still remain in UNDO tablespace automatic rollback segments, I choose to use traditional rollback segments and deal with "snapshot too old..." prevention via other means.

To read more about why this problem happens and how I, and others, deal with preventing this error, you can refer to either faq759-4436 or (the full discussion on the matter) at thread186-628107.

Let us know your findings or results,

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 21:25 (12Nov04) UTC (aka "GMT" and "Zulu"),
@ 14:25 (12Nov04) Mountain Time

 

[RottPaws]

Thanks for the response, Dave. Unfortunately, it doesn't help me. During my 2-day ORA-01555 reading marathon, I've read a ton of stuff from "Ask Tom" and the saga thread you mentioned. I have not read the FAQ, but a quick scan of it looks like a rehash of the explanation (a straigt-forward, easy to understand one, by the way) you gave in the aforementioned thread.

The 3 hour undo retention setting has been in place since the database was created.....since before the problem started. Each iteration of the script that was doing my data load was taking between 1 1/2 and 2 1/2 hours, of which, 30-45 minutes was used pulling data out of another database and formatting it into the flat files prior to loading the data into this database.

I have been the sole user of this database, and that has been primarily done by my script.

This problem came up when I was trying to run a select query during one of the loads. I mistakenly thought the script was in that 30-45 minute part of the query that didn't do anything in this database, but that apparently was not the case.

Now I need to know how to get his error to clear. The error is still there even if I restart the database or totally reboot the server. It's almost as though the rollback segment needs to be flushed or something, but I don't know if that's possible or how to do it.

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[SantaMufasa]

RottPaws,

If it is your belief that there is something in one of the existing rollback segments that is preventing you from progressing, then if I were in your shoes, I would offline and drop the rollback segments. Since I do not us automatic rollback segments in an UNDO tablespace, I am not familiar with any idiosyncracies of dropping UNDO rollback segments.

With manual (traditional) rollback segments I would just say:

alter rollback segment rbs1 offline;
drop rollback segment rbs1;

I would then re-create rollback segment rbs1, but with automatic rollback segments, Oracle would probably recreate them for you.

My thoughts here are just to help you get moving in the right direction. I am sorry that I do not have any more definitve suggestions under your circumstances.

Please let us know how your adventure turns out.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 04:45 (13Nov04) UTC (aka "GMT" and "Zulu"),
@ 21:45 (12Nov04) Mountain Time

 

[RottPaws]

I am not able to alter or drop the rollback segments because they are system objects.

I know you said you don't use automatic, but theorectically, does it sound reasonable that if I change it to MANUAL and then back to AUTO (restarting the db, of course), it would have the same effect?

If so, I would have to make that change in the initSID.ora file, right?

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[SantaMufasa]

Rott Paws,

Yes, your assessments are correct.

I actually did what you are suggesting. I had a database which was a default database creation resulting from a 9.2.0.1 installation (patched to 9.2.0.5). When I determined that the Automatic Rollback Segments (in UNDO tablespace) were not yielding the benefits I wanted to see, I decided to revert to manual rollback segments.

To do so, I changed my init<SID>.ora settings to reflect manual rollback segments, and bounced the database. At that point, I dropped the "UNDOTBS1" tablespace, including contents, which got rid of all evidence of the "new-style" rollbacks. Although you may wish to go back to an UNDO tablespace with automatic rollbacks, I chose to create a traditional tablespace with traditional rollback segments, which have been working very nicely for me ever since.

Let us know how this plot develops and turns out.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 22:43 (14Nov04) UTC (aka "GMT" and "Zulu"),
@ 15:43 (14Nov04) Mountain Time