Undo Management
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Undo Management

  1. #1
    Join Date
    Feb 2006
    Posts
    162

    Undo Management

    I've not set the parameter UNDO_MANAGEMENT in init.ora file,so it takes the default value of MANUAL,

    SQL> show parameter undo_management;

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    undo_management string MANUAL



    When i queries to know the status of undo segments, it shows all are in OFFLINE,


    SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

    SEGMENT_NAME TABLESPACE_NAME STATUS
    ------------------------------ ------------------------------ ----------------
    SYSTEM SYSTEM ONLINE
    _SYSSMU1$ UNDOTBS1 OFFLINE
    _SYSSMU2$ UNDOTBS1 OFFLINE
    _SYSSMU3$ UNDOTBS1 OFFLINE
    _SYSSMU4$ UNDOTBS1 OFFLINE
    _SYSSMU5$ UNDOTBS1 OFFLINE
    _SYSSMU6$ UNDOTBS1 OFFLINE
    _SYSSMU7$ UNDOTBS1 OFFLINE
    _SYSSMU8$ UNDOTBS1 OFFLINE
    _SYSSMU9$ UNDOTBS1 OFFLINE
    _SYSSMU10$ UNDOTBS1 OFFLINE


    Then i tried,to set UNDO_TABLESPACE,so that all undo segments can be brought ONLINE,but it shows the following the error,

    SQL> alter system set undo_tablespace=UNDOTBS1
    2 /
    alter system set undo_tablespace=UNDOTBS1
    *
    ERROR at line 1:
    ORA-02097: parameter cannot be modified because specified value is invalid
    ORA-30014: operation only supported in Automatic Undo Management mode


    Can anyone explain me,

    1) what could be the reason for all the segments to be in OFFLINE,eventhough the undo tablespace is in ONLINE?

    2) What i've to do to make all undo segments ONLINE?

    3) Since UNDO_MANAGEMENT parameter is static,how can i change its value dynamically?

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    1) They are offline because you've not started automatic undo management!

    2) Start automatic undo management and specify the correct undo tablespace.

    3) It's a static parameter. It can't be changed dynamically. If it could, it would be called a dynamic parameter!

    Set the parameter in the init.ora file or the spfile and restart the database, like:

    Code:
    CREATE UNDO TABLESPACE UNDOTBS1
      DATAFILE '/my/path/to/file/undo0101.dbf'
      SIZE 100M REUSE AUTOEXTEND ON;
    
    alter system set undo_management=AUTO scope=SPFILE;
    alter system set undo_tablespace=UNDOTBS1 scope=SPFILE;
    alter system set undo_retention=900 scope=SPFILE;
    shutdown immediate;
    startup;
    Read this:

    http://www.oracle-base.com/articles/...Management.php

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

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