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

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.  
villavejAsked:
Who is Participating?
 
schwertnerConnect With a Mentor Commented:
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
 
schwertnerCommented:
Set
CONSISTANT=N
and try again.
0
 
villavejAuthor Commented:
where do I set that? and why do I need to do that. what is that for?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
schwertnerCommented:
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
 
villavejAuthor Commented:
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
 
schwertnerCommented:
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
 
villavejAuthor Commented:
"CONSISTANT manages this problem but is loading the Rollback Segments"
Will this resolve both "snapshot too old" and "Rollbakc Segment too small"?
0
 
schwertnerCommented:
It will resolve.
Give a try.
Doing Export can not harm your Data base
0
 
villavejAuthor Commented:
what is the default setting for consistent parameter?  What it should be set to in order to resolve this?
0
 
villavejAuthor Commented:
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
 
schwertnerCommented:
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
 
DauheeCommented:
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
 
villavejAuthor Commented:
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
 
DauheeCommented:
how big is your export and what is your redo sized at
0
 
schwertnerCommented:
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
 
villavejAuthor Commented:
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
 
schwertnerCommented:
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
 
villavejAuthor Commented:
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
 
DauheeCommented:
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
 
DauheeCommented:
oh ideally have the export using its own RB as that will reduce contention and help further if ongoing changes are occurring!
0
 
villavejAuthor Commented:
Dauhee:

What's RB? Is it rollback segment?
0
 
DauheeCommented:
Hi villavej apologies - yep a separate rollback tablespace with rolback segments int it :)
0
 
villavejAuthor Commented:
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
 
DauheeConnect With a Mentor Commented:
yep if you're changing to undo there is no point in looking at redo.

yep can have more than 1 undo
0
 
villavejAuthor Commented:
Thanks, schwertner:
Thanks, Dauhee:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.