export needs bigger RBS
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: export needs bigger RBS

  1. #1
    Join Date
    Nov 2000
    Posts
    89
    Oracle People:

    My RBSs are sized perfectly for my database. The TOAD tool even says that they are a bit big. The problem is that they can't handle the export of certain large tables:

    *****************************************************************
    exporting table NODE_VALUE
    EXP-00056: ORACLE error 1555 encountered
    ORA-01555: snapshot too old: rollback segment number 9 with name "RBS8" too small
    *******************************************************************

    I don't want to change my RBSs but I need to export. Is there a way to assign a special RBS to an export ??? Do I need to increase the size of all my RBSs ???

    Thanks,

    Roger

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    Hey Roger; Been there, done that.
    I would create an export parfile and be sure to add
    BUFFER=
    COMMIT=Y

    I usually set the BUFFER between 1000000 and 4000000, this is measured in bytes so 1 million is less than a meg ect...
    This way you will commit every 1-4 megs rather than trying to import the entire table and then commit, which is the Oracle default.

    Good Luck

    MH
    I remember when this place was cool.

  3. #3
    Join Date
    Aug 2001
    Posts
    390
    Take your export when the database is not busy. Don't put CONSISTENT=Y to your export command.


  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by Mr.Hanky
    Hey Roger; Been there, done that.
    I would create an export parfile and be sure to add
    BUFFER=
    COMMIT=Y
    There is no INSERTS/UPDATES/DELETES performed during the export, so there is also no COMMIT= parameter available in EXPort! I belive you've confused this with IMPort.
    Originally posted by rogerF
    Is there a way to assign a special RBS to an export?
    No, there isn't. And it wouldn't make sence. Export doesn't need rollback segments for writing undo information into them (as it doesn't change any data, anyway), but to constructs the read consistent view of table's data as it was when the export of that table begun. If during the proces of exporting other sessions are changing the contents of that table, export must find the values as they were at the begining of the table's export (Time 0). It can find this information only from the rollback segments into which other processes have wriiten their undo information about their changes. So it is not one RBS, there might be many of them that would help exp process to reconstruct the image of the data at Time 0. If any of that information in one of the rollback segment is allready overwritten, you'll get ORA-1555.

    So you either follow mike73 advice (export when there is not much changes performed on those tables and don't use CONSISTENT=Y if it is not necessary), or make your rollback segments bigger (and/or make more of them). Your RB segments might be sized OK for your transactions, but they obviously are not sized OK for long running queries like EXP.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jan 2001
    Posts
    3,131
    Sorry my bad, this is the second time I've done this. It will help with import though.

    MH
    I remember when this place was cool.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by Mr.Hanky
    Sorry my bad, this is the second time I've done this. It will help with import though.

    MH
    We'll credit you with a correct answer to the wrong question
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Nov 2000
    Posts
    89
    THANKS !

    I just got rid of the consistant=y, and I successfully got the table on export.

    . . exporting table NODE_VALUE 1840118 rows exported

    -Roger

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I hope you're not using export for backup purposes. If you are, you have just reduced the limited effectiveness of exp.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Nov 2000
    Posts
    89
    Marist89:

    I'll walk into the trap :-) and answer the question ... Yes, we do use them as a backup. The good news is that we are moving over to RMAN for this DB. I usually run HOTBACKUPS and have EXPORTS (on other DBs) to have the flexibility to quickly recover a users table if they make a stupid mistake. Obvoisly this database is getting too big for the export option.

    Oracle actually recommended to me (during a course) to do the EXPORTS - as long as they didn't take too long or disturb the users.
    This worked well for our small DBs.

    So - can you expand on why ?

    Thanks,
    Roger

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Well, I should have said I hope you're not using export as the only method for purposes of backup. If you are, you have just reduced the limited effectiveness of exp.

    Using export as the only method of backup is a big no-no, IMHO. It limits your options for a recovery in so many scenarios. By using CONSISTANT=N, you are limiting yourself even more because your RI will most likely be out of whack in an active database.

    Since you're using hot backups as the primary backup method (you've tested basic recovery scenarios, right?) and use the exp as a supplemental backup, you are covered for the overwhelming majority of failure scenarios.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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