9i export issue with RBS
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: 9i export issue with RBS

  1. #1
    Join Date
    Jan 2001
    Posts
    66

    9i export issue with RBS

    I am a little confused. I have recently upgraded to 9i(9201) from 8i. The value that I assigned to UNDO_MANAGEMENT=AUTO. When researching 9i db I expected that the UNDOTBS1 tablespace replaces RBS's functional purpose.
    When I attempt to run an export job that I initially built for 8i the
    export starts normal. by the time I get to exporting the 6th table, I receive errors. Output is below, with my question following the output:
    About to export the entire database ...
    . exporting tablespace definitions
    . exporting profiles
    . exporting user definitions
    . exporting roles
    . exporting resource costs
    . exporting rollback segment definitions
    . exporting database links
    . exporting sequence numbers
    . exporting directory aliases
    . exporting context namespaces
    . exporting foreign function library names
    . exporting PUBLIC type synonyms
    . exporting private type synonyms
    . exporting object type definitions
    . exporting system procedural objects and actions
    . exporting pre-schema procedural objects and actions
    . exporting cluster definitions
    . about to export SYSTEM's tables via Conventional Path ...
    . . exporting table AQ$_INTERNET_AGENTS 0 rows exported
    . . exporting table AQ$_INTERNET_AGENT_PRIVS 0 rows exported
    EXP-00091: Exporting questionable statistics.
    . . exporting table AQ$_QUEUE_UPGRADE_TMP 4 rows exported
    . . exporting table DEF$_AQCALL 0 rows exported
    EXP-00091: Exporting questionable statistics.
    . . exporting table DEF$_AQERROR 0 rows exported
    EXP-00008: ORACLE error 1555 encountered
    ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small
    EXP-00056: ORACLE error 1403 encountered
    ORA-01403: no data found
    . . exporting table DEF$_CALLDEST
    EXP-00008: ORACLE error 1555 encountered
    ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small
    . . exporting table DEF$_DEFAULTDEST
    EXP-00008: ORACLE error 1555 encountered
    ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small
    . . exporting table DEF$_DESTINATION
    EXP-00008: ORACLE error 1555 encountered

    My question is... Why am I receiving rollback errors if I have the UNDO_MANAGEMENT=AUTO? I thought the RBS was irrelevent in this situation. What am I missing? My export command follows:
    exp userid=/ buffer=1024000 file=${fileName} log=${logName} full=Y consistent=Y

    Any thoughts would be appreciated.

  2. #2
    Join Date
    Jul 2003
    Posts
    323
    db size ?
    undo_retention value..?

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    Instead of managing rollback, you are telling Oracle to do it with UNDO_MANAGEMENT=AUTO. You should try increasing the size of you undo tablespace.

    Did you look at the alert log?

  4. #4
    Join Date
    Jan 2001
    Posts
    3,131
    If you use CONSISTENT=y and the volume of updates is large, the rollback segment usage will be large. In addition, the export of each table will be slower because the rollback segment must be scanned for uncommitted transactions.

    Keep in mind the following points about using CONSISTENT=y:

    CONSISTENT=y is unsupported for exports that are performed when you are connected as user SYS or you are using AS SYSDBA, or both.
    Export of certain metadata may require the use of the SYS schema within recursive SQL. In such situations, the use of CONSISTENT=y will be ignored. Oracle Corporation recommends that you avoid making metadata changes during an export process in which CONSISTENT=y is selected.
    To minimize the time and space required for such exports, you should export tables that need to remain consistent separately from those that do not.

    For example, export the emp and dept tables together in a consistent export, and then export the remainder of the database in a second pass.

    A "snapshot too old" error occurs when rollback space is used up, and space taken up by committed transactions is reused for new transactions. Reusing space in the rollback segment allows database integrity to be preserved with minimum space requirements, but it imposes a limit on the amount of time that a read-consistent image can be preserved.

    If a committed transaction has been overwritten and the information is needed for a read-consistent view of the database, a "snapshot too old" error results.

    To avoid this error, you should minimize the time taken by a read-consistent export. (Do this by restricting the number of objects exported and, if possible, by reducing the database transaction rate.) Also, make the rollback segment as large as possible.



    Yet another reason to SHOOT THE DEVELOPERS!!, how dare they do any work while you are exporting.

    MH
    I remember when this place was cool.

  5. #5
    Join Date
    Jan 2001
    Posts
    3,131
    Since you can not specify a RBS to use for an export, perhaps asigning one to the users is possible. Otherwise loose the consistent=y.

    Ps, Shoot the damn devlopers just as a precaution!!

    MH
    I remember when this place was cool.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    What is the value you set for undo_retention ?

    Tamil

  7. #7
    Join Date
    Oct 2002
    Posts
    807
    Originally posted by tamilselvan
    What is the value you set for undo_retention ?

    Tamil
    Just curious - but why do you care about undo_retention here? That's essentilly just a "suggestion". It won't cause the undo to blow.

  8. #8
    Join Date
    Jul 2003
    Posts
    323
    From what I understand: 9i
    Rbs sizing:
    -Determine the length of the longest running transaction & qry !
    -Ensure you have sufficient rolllback permanently (not that it can GROW but rather that it is permanently allocated) so that you do not wrap in the perdiod of time that is larger then the above two numbers.
    -In 9i, this is simplified as you may use an UNDO tablespace instead of rollback segments. Here you configure a retention period (how long do you want to keep undo) and it sizes itself.
    -So size UNDO based on stats. in V$UNDOSTATS & set an adequate retention period !! - Most of our 1555's went away with this approach !!

  9. #9
    Join Date
    Oct 2002
    Posts
    807
    Originally posted by cruser3
    -In 9i, this is simplified as you may use an UNDO tablespace instead of rollback segments. Here you configure a retention period (how long do you want to keep undo) and it sizes itself.
    -So size UNDO based on stats. in V$UNDOSTATS & set an adequate retention period !! - Most of our 1555's went away with this approach !!
    If you constrain the undo with a small max size it will blow - no matter what. undo_retention will do nothing for you. It is only a "suggestion". It is not hard n fast. If the trasaction needs additional space in your undo (used by an older txn), it will use it, even though your undo_retention was set to a high value. And then will get constrained by the max size eventually.

    Tom explains it all here
    http://asktom.oracle.com/pls/ask/f?p...7705505116425,

  10. #10
    Join Date
    Jul 2003
    Posts
    323
    Agreed - but the poser here seems to have a specific prob. i.e during a consistent=y exp.
    Which is why I enquired about undo_retention - the key to solving the problem is sizing the UNDO. Redo can be generated in so many ways but generally solving 1555's on 9i-> is increasing RBS/undo_retention but your link should help him solve this - not a big deal !!



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