Exp-00008 with ORA-01555 Using Automatic Undo Management
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Exp-00008 with ORA-01555 Using Automatic Undo Management

  1. #1
    Join Date
    Nov 2000
    Posts
    162

    Question Exp-00008 with ORA-01555 Using Automatic Undo Management

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you undo retention is not set big enough for the export

    and exports for backups, oh dear

  3. #3
    Join Date
    Nov 2001
    Posts
    335
    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!

  4. #4
    Join Date
    Nov 2001
    Posts
    118
    I've come across this when the redo logfiles weren't large enough.

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    no you havent

  6. #6
    Join Date
    Nov 2000
    Posts
    162
    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!

  7. #7
    Join Date
    Nov 2000
    Posts
    162
    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

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    your undo retention needs to be longer than your longest running query

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    and that export command will generate a backup that is useless to you

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    exp / owner=dw eot scat state goupers oper app
    Exp one user at a time.
    Or run multiple exp scripts for each user.

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width