Solved

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

Posted on 2008-06-20
25
2,681 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
[X]
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
  • 11
  • 8
  • 6
25 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 21831130
Set
CONSISTANT=N
and try again.
0
 

Author Comment

by:villavej
ID: 21831193
where do I set that? and why do I need to do that. what is that for?
0
 
LVL 48

Expert Comment

by:schwertner
ID: 21831349
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:villavej
ID: 21831472
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 48

Expert Comment

by:schwertner
ID: 21831632
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
ID: 21831682
"CONSISTANT manages this problem but is loading the Rollback Segments"
Will this resolve both "snapshot too old" and "Rollbakc Segment too small"?
0
 
LVL 48

Expert Comment

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

Author Comment

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

Author Comment

by:villavej
ID: 21833640
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 48

Expert Comment

by:schwertner
ID: 21836976
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
ID: 21837729
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
ID: 21845060
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
 
LVL 7

Expert Comment

by:Dauhee
ID: 21845313
how big is your export and what is your redo sized at
0
 
LVL 48

Expert Comment

by:schwertner
ID: 21845318
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
ID: 21845369
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 48

Expert Comment

by:schwertner
ID: 21845412
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
ID: 21845478
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
ID: 21845547
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
ID: 21845561
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
ID: 21846113
Dauhee:

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

Expert Comment

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

Author Comment

by:villavej
ID: 21846301
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 48

Accepted Solution

by:
schwertner earned 63 total points
ID: 21846650
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
ID: 21847895
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
ID: 31469144
Thanks, schwertner:
Thanks, Dauhee:
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

623 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