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
Solved

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

Posted on 2006-07-11
22
5,365 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
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 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
capture vmstat info and insert it into an oracle table 31 35
add more rows to hierarchy 3 25
update statement in oracle 9 29
pl/sql parameter is null sometimes 2 14
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

856 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