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!
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!
read this and it is a very good explanation on this error:
http://asktom.oracle.com/pls/ask/f?p=4950:8:15098632210842779324::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:275215756923
http://asktom.oracle.com/pls/ask/f?p=4950:8:15098632210842779324::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:275215756923
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
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!
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!
ASKER
ORA-01555: snapshot too old -- happened everytime when system was doing full export.
Thanks!
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How about increasing undo_retention to 2 hours or more? Does it need to be done at downtime?
Thanks!
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
yes, just issue:
alter system undo_retention=<xx>
alter system undo_retention=<xx>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
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.
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.
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.
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.