Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: EXP Issue

Re: EXP Issue

From: joel garry <joel-garry_at_home.com>
Date: 18 Sep 2006 13:35:51 -0700
Message-ID: <1158611751.503503.161530@m73g2000cwd.googlegroups.com>

al1 wrote:
> I'm receiving an:
>
> Oracle 9i
>
> 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

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/
Received on Mon Sep 18 2006 - 15:35:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US