Solved

ORA-01555: snapshot too old: rollback segment number 3 with name "_SYSSMU3$" too small

Posted on 2006-07-11
22
5,493 Views
Last Modified: 2008-09-30
I got following error.

EXP-00008: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number 3 with name "_SYSSMU3$" too small
EXP-00000: Export terminated unsuccessfully

My current undo_retention is 2500 sec, and undo tablespace is 3G.
Question:
- Do I need to change undo_retention or undo tablespace to fix the problem?  
- How big should be?
- Does undo_retention need to be changed at downtime?

Thanks!
0
Comment
Question by:luyan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 4
  • +3
22 Comments
 
LVL 19

Expert Comment

by:actonwang
ID: 17087255
make sure you set:

undo_management=AUTO


also  are you running long query for over 40 minutes? you might set longer undo_retention and also make sure not many transactions running in the same time.

0
 
LVL 19

Expert Comment

by:actonwang
ID: 17087263
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17087266
You probably do not have enough roll back segments. Add additional rollback segments and try your export shouldn't give this error then
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:sathyagiri
ID: 17087278
This could also happen if you have other active connections to your DB. You could also try it with the consistent = N parameter
0
 

Author Comment

by:luyan
ID: 17087525
Database was doing full export, and met this issue.
in my system:
  undo_management  = AUTO
  undo_retention        = 2500
  undo_tablespace  -- size 4000MB and used 5%
  consistent = y
 
Any suggestions on configuration to fix the issue?

Thanks!
0
 

Author Comment

by:luyan
ID: 17087590
ORA-01555: snapshot too old  --  happened everytime when system was doing full export.

Thanks!
0
 
LVL 14

Assisted Solution

by:sathyagiri
sathyagiri earned 400 total points
ID: 17087597
I guess your problem should get resolved if you add additional roll back segments.
0
 

Author Comment

by:luyan
ID: 17087619
How about increasing undo_retention to 2 hours or more? Does it need to be done at downtime?

Thanks!
0
 
LVL 14

Assisted Solution

by:sathyagiri
sathyagiri earned 400 total points
ID: 17087626
ALTER TABLESPACE <UNDOTABLESPACE_NAM> ADD DATAFILE '<DATAFILE PATH>  AUTOEXTEND ON NEXT 1M
         MAXSIZE UNLIMITED;

Or you can resize the existing data file using
ALTER DATABASE resize DATAFILE
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17087634
yes, just issue:

alter system undo_retention=<xx>

0
 
LVL 14

Accepted Solution

by:
sathyagiri earned 400 total points
ID: 17087638
Here's an article to optimize your UNDO parameters
http://www.akadia.com/services/ora_optimize_undo.html
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17087641
3G is a lot of rollback segment space. try undo_retention first.
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17087643
Determine the UNDO_RETENTION required based on the above post then use the alter system command to change it.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17087646
also make sure you do export while not many other database activities.
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17087657
Agree try increasing UNDO_RETENTION first.
0
 
LVL 19

Assisted Solution

by:actonwang
actonwang earned 100 total points
ID: 17087667
again, refer to this link:

http://asktom.oracle.com/pls/ask/f?p=4950:8:15098632210842779324::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:275215756923

for more info on undo segment and reason for this error. for auto undospace, tasks are more easier with undo_rentention.
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17087922
If UNDO_RETENTION is more, then more number of transactions blocks has to be kept in UNDO blocks. This will increase the demand for rollback segment space.

The options for avoinding ORA-01555 error is

1) Increase UBDO tablespace
2) Reduce UNDO_RETENTION
0
 
LVL 7

Expert Comment

by:vishal68
ID: 17088195
You are using consistent=y. Do you really need it. This parameter will ensure that all the tables exported will be consistent as of the time of start of export. You will need to set UNDO_RETENTION parameter equal or greater than the time needed for the full export to complete. Also you will need to ensure that the UNDO tablespace is large enough to be able to support full export.

A better strategy might be to use consistent=n This will require much lesser UNDO space.

HTH
Vishal


0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17088477
When there are many transactions happening in the database, setting CONSISTENT=Y during export is needed to ensure data consistency.

A better approach is to take the export when there are less activities on the server. Batch jobs which updates large amount of data is one reason for rollback segment usage.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 17088641
I am surprising.
Export writes in an sequential file the contents of the DB.
May be it do not flushes the undo segments because of consistent reads
principle of Oracle.

I will recommend to find a maintanence window in your shop
and to shutdown the DB.
After that open it in RESTRICTED MODE and do teh export.
Now you can open the DB in ordinary mode and allow
normal processing.

Another solution is not to make full export.
Try to export only the nonsystem schemas.

Also possible cause could be a bug.
Try to upgrade to the latest version and do not forget to upgrade the catalog
in the post upgrade task list.
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17088674
I had noticed ORA-01555 during export and this happened for large tables since the time taken for export is more here. During this time other transactions can update the table. CONSISTENT=Y will not solve the issue since "read consistency" is implemented with Oracle.
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17088684
>> Typo error
It should be
CONSISTENT=N will not solve the issue since "read consistency" is implemented with Oracle.
CONSISTENT=Y will ensure consistency from the start of the export command.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question