Exp-00008 with ORA-01555 Using Automatic Undo Management

[Unna]

Dear all,

We have a data warehouse in Oracle 9i database in HP-Unix, it has a 9 GB undo tablespace with automatic undo management and undo_retention=1979345.

When we do export we got these errors:
EXP-00008: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number 4 with name "_SYSSMU4$" too small
EXP-00000: Export terminated unsuccessfully

Since this tablespace is under automatic undo management, any change to it is not allowed. This export is our nightly backup. Is there a way that we can go around to fix this ORA-15555 error? I believe our UNDO tablespace size is big enough and base on formula Oracle recommended, 1979345 is the optimal value for undo_retention.

Any help is deeply appreciated.


Thanks,
Unna

[davey23uk]

you undo retention is not set big enough for the export

and exports for backups, oh dear

[BV1963]

do you use consistent=yes? How long does export take?

[Peer Jones]

I've come across this when the redo logfiles weren't large enough.

[davey23uk]

no you havent

[Unna]

Here is my export:
exp / owner=dw eot scat state goupers oper app file=exp.dmp log=exp.log compress=y consistent=n direct=y

The dump failled after 8 hours export with error ORA-1555.

If my undo_retention is not big enough, how big should I set it to? Is there a formula? Based on oracle's, I used Actual Undo Size/(Undo Blocks per Second*DB Block Size) to come up with the current undo_retention=1979345.



Thanks all!

[Unna]

Since our undo tablespace is 9GB in size, the system generated rollback segments only have total size of 600M, is there a way to increase these segments size in AUTO Undo Management tablespace?

Segment Tablesp Ini (K) Nxt (K) Max Status RSize(K) Extents
------------------ -------------- -------- ------- -------- ---------- ------- -----------
SYSTEM SYSTEM 64 64 1017 ONLINE 496 8
_SYSSMU1$ RBS 128 32765 ONLINE 12,400 14
_SYSSMU19$ RBS 128 32765 ONLINE 10,352 12
_SYSSMU2$ RBS 128 32765 ONLINE 42,096 36
_SYSSMU3$ RBS 128 32765 ONLINE 90,224 69
_SYSSMU4$ RBS 128 32765 ONLINE 114,800 72
_SYSSMU5$ RBS 128 32765 ONLINE 122,864 86
_SYSSMU6$ RBS 128 32765 ONLINE 16,496 18
_SYSSMU7$ RBS 128 32765 ONLINE 163,952 78
_SYSSMU8$ RBS 128 32765 ONLINE 14,448 9
_SYSSMU9$ RBS 128 32765 ONLINE 24,752 27
_SYSSMU20$ RBS 128 32765 OFFLINE

[davey23uk]

your undo retention needs to be longer than your longest running query

[davey23uk]

and that export command will generate a backup that is useless to you

[tamilselvan]

exp / owner=dw eot scat state goupers oper app

Exp one user at a time.
Or run multiple exp scripts for each user.

Tamil