Export Error

[sri_dba]

Hi DBAs

I got the following error , when i exported my production DB.

"Export terminated unsuccessfully"

ORA-01555: Snapshot too old: Rollback segment number 6 with name "RB5" too small.

What could be the error.

Pls let me know to rectify this.

Thanx.

[slimdave]

Are you exporting in consistent mode?

Simple answer is that there is enough activity on the db while you are exporting it for the database to be unable to locate the SCN for the export query consistency point in the rollback, and therefore can't validate that the data it is exporting is consistent. Bigger rollback segments would be nice.

[adrianomp]

Bigger rollback segments would be nice.

Exports in off-hours would be even better.

With CONSISTENT=N export option backup should finish well, too... However, I never had the guts to use something like that.

[Ablakios]

sri
You have ORA-01555: Snapshot too old: Rollback segment number 6 with name "RB5" too small. while exporting....

What you first need to do is query dba_rollback_segs and check the size of your rollback segments.
Drop and recreate rollback segment RB5 with a larger size and rerun your export.

HTH

[rajabalachandra]

hi

oracle needs the full image of the database
from the export's start to end.
if the data block has been changed after your export
has started it goes to rollback segment to get the
previous image.
if it fails to get for any of the block you get this error.


droping and recreating "rb5" alone will not solve the problem.

you will have to create all the rollback segments with high
minextents.(this is the oracle recommentation)

if yours is a transaction intensive then:

you can try adding more rollback segments.

you can set transactions_per_rollback_segment to high.

how much time your export takes to complete.

what is number of transactions that can execute
during the exporting period.

your
(count of rollback segments) * transactions_per_rollback_segment
should be greater than the number of transactions executed
during that exporting period.


-Raja

[pando]

Originally posted by rajabalachandra
hi

oracle needs the full image of the database
from the export's start to end.

errr no the whole database please!

it needs the before image when it starts to export a table... TABLE!!! NOT THE WHOLE DATABASE!

you can set transactions_per_rollback_segment to high.

that would make things worse!

[rajabalachandra]

yes; it does not need the full database image.


the table that is being exported currently
should be read consistent.

once the table is exported then this image
can be destroyed.

oracle exports tables in the order of
userid, table name.


the frequently modified tables can be
named in such a way that it gets
exported first. (say starts with a, b, c..)
(you can create some relevant synonyms for tables).
this is also a way you can eliminate this problem.




pando,

how do you say that

setting "transactions_per_rollback_segment" to high

will worse the situation.


do you mean

one transaction will overwrite another transaction's
undo data if they are sharing the same rollback segment.

can i get the reason.


thank you!


-Raja

[pando]

you increase number of transactions per RBS you are increasing the chance of overwriting RBS!

[rajabalachandra]

hi pando,

i want to ask 2 questions.


1.Is the export done as a single statement
or multiple statements?

2.If done as multiple statements,
does the export need transaction level read consistency
or statement level read consistency?


thank you.

-Raja