Translate this Page

 

Sometimes at the first installation, we don’t care about the size of UNDO Tablespace. We just create it with maximum extensible unlimited size. So there’s one big data file with a big I/O since write-read process only on that file. This article I wrote for give you a solution how to replace an UNDO Tablespace with a big datafile with a new UNDO Tablespace with several tiny datafiles.

  1. Check the UNDO setting in the database :
  2. SQL> show parameter undo
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- --------------------
    undo_management                      string      AUTO
    undo_retention                       integer     10800
    undo_tablespace                      string      UNDOTBS1

  3. Create new UNDO tablespace
  4. SQL> create undo tablespace undotbs2 datafile
    '/oracle/oradata/undotbs2_01.dbf' size 100M autoextend on next 8M maxsize 2048M,
    '/oracle/oradata/undotbs2_02.dbf' size 100M autoextend on next 8M maxsize 2048M,
    '/oracle/oradata/undotbs2_03.dbf' size 100M autoextend on next 8M maxsize 2048M,
    '/oracle/oradata/undotbs2_04.dbf' size 100M autoextend on next 8M maxsize 2048M,
    '/oracle/oradata/undotbs2_05.dbf' size 100M autoextend on next 8M maxsize 2048M,
    '/oracle/oradata/undotbs2_06.dbf' size 100M autoextend on next 8M maxsize 2048M,
    '/oracle/oradata/undotbs2_07.dbf' size 100M autoextend on next 8M maxsize 2048M
    extent management local;
  5. Shutdown the Database (don’t used abort!!)
  6. SQL> shutdown immediate
  7. Edit parameter file, initSID.ora, and change the parameter “undo_tablespace” and “undo_management”
  8. undo_tablespace=UNDOTBS2
    undo_management=MANUAL

    NOTE: if you’re using spfile, then you have to create pfile first before edit it.”Create pfile from spfile”.

  9. Startup database
  10. SQL> startup pfile=initSID.ora
  11. Assure the UNDO parameters already changed
  12. SQL> show parameter undo
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- --------------------
    undo_management                      string      MANUAL
    undo_retention                       integer     10800
    undo_tablespace                      string      UNDOTBS2
  13. Assure all undo segments in the old UNDO Tablespace already offline
  14. SQL>select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3; 
    
    OWNER  SEGMENT_NAME                   TABLESPACE_NAME                STATUS
    ------ ------------------------------ ------------------------------ ----------------
    SYS    SYSTEM                         SYSTEM                         ONLINE
    PUBLIC _SYSSMU1$                      UNDOTBS1                       OFFLINE
    PUBLIC _SYSSMU2$                      UNDOTBS1                       OFFLINE
    PUBLIC _SYSSMU3$                      UNDOTBS1                       OFFLINE
    PUBLIC _SYSSMU4$                      UNDOTBS1                       OFFLINE
    PUBLIC _SYSSMU5$                      UNDOTBS1                       OFFLINE
    PUBLIC _SYSSMU6$                      UNDOTBS1                       OFFLINE
    PUBLIC _SYSSMU7$                      UNDOTBS1                       OFFLINE
    PUBLIC _SYSSMU8$                      UNDOTBS1                       OFFLINE
    PUBLIC _SYSSMU9$                      UNDOTBS1                       OFFLINE
    PUBLIC _SYSSMU10$                     UNDOTBS1                       OFFLINE
    PUBLIC _SYSSMU11$                     UNDOTBS2                       OFFLINE
    PUBLIC _SYSSMU12$                     UNDOTBS2                       OFFLINE
    PUBLIC _SYSSMU13$                     UNDOTBS2                       OFFLINE
    PUBLIC _SYSSMU14$                     UNDOTBS2                       OFFLINE
    PUBLIC _SYSSMU15$                     UNDOTBS2                       OFFLINE
    PUBLIC _SYSSMU16$                     UNDOTBS2                       OFFLINE
    PUBLIC _SYSSMU17$                     UNDOTBS2                       OFFLINE
    PUBLIC _SYSSMU18$                     UNDOTBS2                       OFFLINE
    PUBLIC _SYSSMU19$                     UNDOTBS2                       OFFLINE
    PUBLIC _SYSSMU20$                     UNDOTBS2                       OFFLINE
    21 rows selected.

    NOTE: undo segments in the new UNDO Tablespace could be “offline” also.

  15. If the undo segment of old UNDO Tablespace still “online”, make it offline
  16. SQL>alter rollback segment "_SYSSMU3$" offline;
  17. If all undo segments of old UNDO Tablespace already “offline”, we can drop the old UNDO Tablespace
  18. SQL> DROP tablespace UNDOTBS1 including contents and datafiles;
  19. Check current undo segments
    SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;
    OWNER  SEGMENT_NAME                   TABLESPACE_NAME                STATUS
    ------ ------------------------------ ------------------------------ ----------------
    SYS    SYSTEM                         SYSTEM                         ONLINE
    PUBLIC _SYSSMU11$                     UNDOTBS2                       OFFLINE
    PUBLIC _SYSSMU12$                     UNDOTBS2                       OFFLINE
    PUBLIC _SYSSMU13$                     UNDOTBS2                       OFFLINE
    PUBLIC _SYSSMU14$                     UNDOTBS2                       OFFLINE
    PUBLIC _SYSSMU15$                     UNDOTBS2                       OFFLINE
    PUBLIC _SYSSMU16$                     UNDOTBS2                       OFFLINE
    PUBLIC _SYSSMU17$                     UNDOTBS2                       OFFLINE
    PUBLIC _SYSSMU18$                     UNDOTBS2                       OFFLINE
    PUBLIC _SYSSMU19$                     UNDOTBS2                       OFFLINE
    PUBLIC _SYSSMU20$                     UNDOTBS2                       OFFLINE
    11 rows selected.
  20. Shutdown the database
  21. SQL> shutdown immediate
  22. Modify the parameter file, initSID.ora
  23. undo_management=AUTO

    NOTE: after edit the pfile, re-create the spfile: “create spfile from pfile”.

  24. Startup the database
  25.  SQL> startup
  26. Check the undo segments of new UNDO Tablespace
  27. SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;
    OWNER  SEGMENT_NAME                   TABLESPACE_NAME                STATUS
    ------ ------------------------------ ------------------------------ ----------------
    SYS    SYSTEM                         SYSTEM                         ONLINE
    PUBLIC _SYSSMU11$                     UNDOTBS2                       ONLINE
    PUBLIC _SYSSMU12$                     UNDOTBS2                       ONLINE
    PUBLIC _SYSSMU13$                     UNDOTBS2                       ONLINE
    PUBLIC _SYSSMU14$                     UNDOTBS2                       ONLINE
    PUBLIC _SYSSMU15$                     UNDOTBS2                       ONLINE
    PUBLIC _SYSSMU16$                     UNDOTBS2                       ONLINE
    PUBLIC _SYSSMU17$                     UNDOTBS2                       ONLINE
    PUBLIC _SYSSMU18$                     UNDOTBS2                       ONLINE
    PUBLIC _SYSSMU19$                     UNDOTBS2                       ONLINE
    PUBLIC _SYSSMU20$                     UNDOTBS2                       ONLINE
    11 rows selected.

    Now you have already replaced your old UNDO Tablespace with a new one. Congratulation!!



 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
© 2012 LevICORP's webnote Suffusion theme by Sayontan Sinha