DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Export Error

  1. #1
    Join Date
    Jun 2003
    Posts
    108

    Export Error

    Hi DBAs

    I got the following error , when i exported my production DB.

    "Export terminated unsuccessfully"

    ORA-01555: Snapshot too old: Rollback segment number 6 with name "RB5" too small.

    What could be the error.

    Pls let me know to rectify this.

    Thanx.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Are you exporting in consistent mode?

    Simple answer is that there is enough activity on the db while you are exporting it for the database to be unable to locate the SCN for the export query consistency point in the rollback, and therefore can't validate that the data it is exporting is consistent. Bigger rollback segments would be nice.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Feb 2001
    Posts
    295
    Bigger rollback segments would be nice.
    Exports in off-hours would be even better.

    With CONSISTENT=N export option backup should finish well, too... However, I never had the guts to use something like that.
    An ounce of action is worth a ton of theory.
    —Friedrich Engels

  4. #4
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    374
    sri
    You have ORA-01555: Snapshot too old: Rollback segment number 6 with name "RB5" too small. while exporting....

    What you first need to do is query dba_rollback_segs and check the size of your rollback segments.
    Drop and recreate rollback segment RB5 with a larger size and rerun your export.

    HTH
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  5. #5
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    hi

    oracle needs the full image of the database
    from the export's start to end.
    if the data block has been changed after your export
    has started it goes to rollback segment to get the
    previous image.
    if it fails to get for any of the block you get this error.


    droping and recreating "rb5" alone will not solve the problem.

    you will have to create all the rollback segments with high
    minextents.(this is the oracle recommentation)

    if yours is a transaction intensive then:

    you can try adding more rollback segments.

    you can set transactions_per_rollback_segment to high.

    how much time your export takes to complete.

    what is number of transactions that can execute
    during the exporting period.

    your
    (count of rollback segments) * transactions_per_rollback_segment
    should be greater than the number of transactions executed
    during that exporting period.


    -Raja

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by rajabalachandra
    hi

    oracle needs the full image of the database
    from the export's start to end.
    errr no the whole database please!

    it needs the before image when it starts to export a table... TABLE!!! NOT THE WHOLE DATABASE!



    you can set transactions_per_rollback_segment to high.
    that would make things worse!

  7. #7
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    yes; it does not need the full database image.


    the table that is being exported currently
    should be read consistent.

    once the table is exported then this image
    can be destroyed.

    oracle exports tables in the order of
    userid, table name.


    the frequently modified tables can be
    named in such a way that it gets
    exported first. (say starts with a, b, c..)
    (you can create some relevant synonyms for tables).
    this is also a way you can eliminate this problem.




    pando,

    how do you say that

    setting "transactions_per_rollback_segment" to high

    will worse the situation.


    do you mean

    one transaction will overwrite another transaction's
    undo data if they are sharing the same rollback segment.

    can i get the reason.


    thank you!


    -Raja

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you increase number of transactions per RBS you are increasing the chance of overwriting RBS!

  9. #9
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    hi pando,

    i want to ask 2 questions.


    1.Is the export done as a single statement
    or multiple statements?

    2.If done as multiple statements,
    does the export need transaction level read consistency
    or statement level read consistency?


    thank you.

    -Raja

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