Solved

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

Posted on 2008-06-20
25
2,581 Views
Last Modified: 2013-12-19
Export terminated successfully with warnings.
EXP-00056: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number # with name "" too small

Despite the warning above,  did the export proceed successfully?  We are not using UNDO tablespace for this 9i database.  
0
Comment
Question by:villavej
  • 11
  • 8
  • 6
25 Comments
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
Set
CONSISTANT=N
and try again.
0
 

Author Comment

by:villavej
Comment Utility
where do I set that? and why do I need to do that. what is that for?
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
You can   set it in the parametr file or in command line invoking the export.
It asures that the Import is consistent, i.e. if somebody has changed the exported data before the export
ends, then the changed data will be put in the export either.
BUT this generates high load to the Rollback segments.
The other way is to estimate the time of the Export.
If it takes not long and the business rules allow
to turn off the business activity then you can switch the
database to RESTRICTED mode and to do the Export.
So the application will not be allowed to do changes.
After you do the Export then turn the DB to NORMAL mode.
0
 

Author Comment

by:villavej
Comment Utility
We cannot restrict the database.  If consistent is set, are we able to avoid that error from displaying? Is the table still getting exported despite teh error?
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
What is the target of the Export?
Possibly setting
CONSISTANT=N
you will get rid of the error mentioned in your
initial posting.
But the Export will not be consistent and can not be used
as whole backup set.

I will explain you what happens:
1. You have two (or more) tables A and B that are corelated via referential constraint.
2, Export has exported table A.
3. Table B is not yet exported
4. Table A is changed in regard of rows of table B
5. Export exports table B
6. At this point of time Table A and table B are not consistent to each other
   (A is old version B is new version)

CONSISTANT manages this problem but is loading the Rollback Segments.
0
 

Author Comment

by:villavej
Comment Utility
"CONSISTANT manages this problem but is loading the Rollback Segments"
Will this resolve both "snapshot too old" and "Rollbakc Segment too small"?
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
It will resolve.
Give a try.
Doing Export can not harm your Data base
0
 

Author Comment

by:villavej
Comment Utility
what is the default setting for consistent parameter?  What it should be set to in order to resolve this?
0
 

Author Comment

by:villavej
Comment Utility
we didn't have consistent parameter defined in our export parameter file.  That means, it's using teh default, consistent=n.  why did the errror still appear?
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
Give a try with
CONSISTANT=N
to check what happens.

If you succeed this means that I am correct.
If not - we will search the error somewhere else.
0
 
LVL 7

Expert Comment

by:Dauhee
Comment Utility
ahem - what is your redo sized at. Undo would be preferential really :)

You could also create a really big redo ts + datafile (or just another one with the same size as existing one) and set an export user to use that - it will have uncontended access to redo which could make a big difference - if you are exporting on a regular basis (which you should) then you won't have to go stopping production/user access

Out of curiosity, how big is the table that fails on the export?
0
 

Author Comment

by:villavej
Comment Utility
schwertner:

Are you saying that, the default behavior  is consistent=y even though the default value set by Oracle is consistent=n?  If I set to consistent=y so that the whole backup set is consistent,  would that still appear when we move to UNDO tablespace? We're doing full export by the way aside from cold backup (full=y)

Dauhee:

We're using Rollback Segments but we're moving to UNDO tablespace for this application alone. Then we'll set to consistent=y.  If I set to consistent=y with the UNDO tablespace,  would that still appear when we move to UNDO tablespace? We're doing full export by the way aside from cold backup (full=y)


Please advise
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 7

Expert Comment

by:Dauhee
Comment Utility
how big is your export and what is your redo sized at
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
Cold backup CAN NOT BE DONE with Export utility.
Only FULL export can be done! But this is not backup.
This is only a snapshot. Backup has extended functionality.

If you set CONSISTANT=y this will explore your redo log
and will make many entries there.

If you set CONSISTANT=n there is no reason for the usage
of the redo log at least for the export.
0
 

Author Comment

by:villavej
Comment Utility
schwertner:,

After we do the cold backup, we do full export (whole database) to have an alternative avaiability because there n instance that teh cold backup was incomplete due to disk space and  we don't have any other options but to do the import from teh export file.  As you have said before,  if consistent=n,  any updates during the export will make the export files inconsistent, and therefore, cannot be used as a whole backup set.  We're planning to go for UNDO tablespace then make consistent=y, just to make sure that any updates will be rolled back during teh export.  What do you think?
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
If you really use the export as sort of backup then you have to use
CONSISTANT=y
If you can not set RESTRICTED mode of the database you have
to manage this problem using bigger UNDO segment.
0
 

Author Comment

by:villavej
Comment Utility
Dauhee,

Our redo logs is 9 sets of 10MB each.  I know this is small but what is an adviseable size of redo for an export of a table having 5M rows.  The full export  is around 20 GB  (2GB per file;full export is slashed into  10 export files)
0
 
LVL 7

Expert Comment

by:Dauhee
Comment Utility
If you could spare a couple of gigs that would be great - at least 5. There are specific methods to determine what size they should be but would take a bit of back and forwards and you are changing to undo anyhows

Also if you allow it to auto extend that would be great
0
 
LVL 7

Expert Comment

by:Dauhee
Comment Utility
oh ideally have the export using its own RB as that will reduce contention and help further if ongoing changes are occurring!
0
 

Author Comment

by:villavej
Comment Utility
Dauhee:

What's RB? Is it rollback segment?
0
 
LVL 7

Expert Comment

by:Dauhee
Comment Utility
Hi villavej apologies - yep a separate rollback tablespace with rolback segments int it :)
0
 

Author Comment

by:villavej
Comment Utility
How would you tell export to use a specific rollbakc tablespace?Right now, we're using rollback segment.  This week we will be converting to UNDO tablespace.  Can we have more than 1 UNDO tablespaces?
0
 
LVL 47

Accepted Solution

by:
schwertner earned 63 total points
Comment Utility
No, UNDO tablespace is fully automated and you will have no problems.
Why you hide the fact that you use clasical   rollback segments
up to now?
Oracle 9i comes by default UNDO segments and there are no troubles
you complain.
0
 
LVL 7

Assisted Solution

by:Dauhee
Dauhee earned 62 total points
Comment Utility
yep if you're changing to undo there is no point in looking at redo.

yep can have more than 1 undo
0
 

Author Closing Comment

by:villavej
Comment Utility
Thanks, schwertner:
Thanks, Dauhee:
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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

772 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

16 Experts available now in Live!

Get 1:1 Help Now