Link to home
Start Free TrialLog in
Avatar of luyan
luyan

asked on

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

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!
Avatar of Acton Wang
Acton Wang
Flag of United States of America image

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.

You probably do not have enough roll back segments. Add additional rollback segments and try your export shouldn't give this error then
This could also happen if you have other active connections to your DB. You could also try it with the consistent = N parameter
Avatar of luyan
luyan

ASKER

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!
Avatar of luyan

ASKER

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

Thanks!
SOLUTION
Avatar of sathyagiri
sathyagiri
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of luyan

ASKER

How about increasing undo_retention to 2 hours or more? Does it need to be done at downtime?

Thanks!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
yes, just issue:

alter system undo_retention=<xx>

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
3G is a lot of rollback segment space. try undo_retention first.
Determine the UNDO_RETENTION required based on the above post then use the alter system command to change it.
also make sure you do export while not many other database activities.
Agree try increasing UNDO_RETENTION first.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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


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.
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.
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.
>> 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.