Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2006-07-11
22
Medium Priority
?
5,660 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 1600 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 1600 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 1600 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 400 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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…

610 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