Solved

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

Posted on 2006-07-11
22
5,221 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
  • 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now