Exp-00008 with ORA-01555 Using Automatic Undo Management
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.
you undo retention is not set big enough for the export
and exports for backups, oh dear
do you use consistent=yes? How long does export take?
One, who thinks that the other one who thinks that know and does not know, does not know either!
I've come across this when the redo logfiles weren't large enough.
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.
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
your undo retention needs to be longer than your longest running query
and that export command will generate a backup that is useless to you
Exp one user at a time.
exp / owner=dw eot scat state goupers oper app
Or run multiple exp scripts for each user.
Click Here to Expand Forum to Full Width