EXP Issue
al1
EXP-00056: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number 2 with name
"_SYSSMU2$" too small
The exp continues after this message and at the end of the 3 hour exp I
get the following message:
"Export terminated successfully with warnings."
Will this exp be ok or is it corrupt based on the 1555 error?
I read some where that setting the UNDO_RETENTION period higher solves
this issue, it's currently set to 600, should I up it?
The UNDOTBS1 is sized at 22G and when I view the usage during the exp
it does not appear to be filling up.
The exp cmd settings are :
BUFFER=128000
FULL=Y
COMPRESS=N
STATISTICS=NONE
FILESIZE=2G
Thx in Advance
al1
Oracle 9i
Vladimir M. Zakharychev
Yes, it will be corrupt - the error prevented the affected table to be
fully fetched, so the export misses some data.
Yes, you should increase the UNDO_RETENTION so that it at least exceeds
the time it takes to export the largest table. You may want to check
V$UNDOSTAT.MAXQUERYLEN, which should hint you how long the longest
query was running in the past week. Undo retention should be at least
that long.
Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
joel garry
In addition to what Vladimir said, you might consider reviewing the
concepts manual about read concurrency. The undo is used so that
oracle can make exp's view of the database (or table, depending on exp
options) consistent to a point in time. If there are other users
updating the db, oracle has to keep everyone's view of the db/table
consistent to when their transaction started. What can happen is
someone can log off the database incorrectly (in some apps, Xing a
window might not properly log them off) or not even log off, so when
oracle goes to make the exp transaction consistent - oops, the undo has
gone!
Some things you might think about are:
Kicking people off the db before starting the exp.
Use a larger buffer if you can - see the BUFFER parameter in the
utilities manual to see if you can.
See the exp CONSISTENT parameter. If you can't kick everyone off the
system before the export, you may want to force a very large undo
usage. Depending on whether you care about referential integrity, of
course. Which depends on what you want to do with the export.
On some of my systems, it makes more sense to have schema exports along
with a full row=N export.
DIRECT=Y with a large RECORDLENGTH can be much faster.
COMPRESS is so often misunderstood.
And always remember, logical backups like exp are useful, physical
backups are mandatory!
jg
--
@home.com is bogus.
http://www.werc.usgs.gov/sattrack/