Thursday, August 19, 2010

How to drop and recreate UNDO Tablespace in Oracle 9i/10g/11g

How to drop and recreate UNDO Tablespace in Oracle 9i/10g/11g?

Solution:

1. Determine the size of your undo tablespace

SQL> select sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='UNDOTBS1';

SUM(BYTES)/1024/1024/1024
-------------------------
                12.09375
               
2. Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.
SQL> create undo tablespace UNDOTBS2 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\UNDOTBS02.DBF' size 400M;

    Tablespace created.
   
3. Edit your init.ora file and change the parameter "undo_tablespace=UNDOTBS2" so it points to the newly created tablespace.
    Change undo_tablespace=UNDOTBS2
    Change undo_management=MANUAL

    Setting undo_management now ensures the old rollback segments can be taken offline and avoids editing the pfile and restarting the instance again in Step 7.
   
4. Arrange a time when the database can be shutdown cleanly and perform a shutdown immediate.   
5. Startup the database
6. Confirm the new tablespace is in use:

    SQL> show parameter undo_tablespace

    NAME                 TYPE              VALUE
    -------------        -----------       ------------
    undo_tablespace      string            UNDOTBS2
   
7. Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline. The segments in the new tablespace may also show offline.
    SQL>select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;

    OWNER         SEGMENT_NAME         TABLESPACE_NAME         STATUS
    ------              --------------------------- ------------------------------ -----------
    PUBLIC         _SYSSMU3$                      UNDOTBS1                         OFFLINE
    PUBLIC         _SYSSMU2$                      UNDOTBS1                         OFFLINE
    PUBLIC         _SYSSMU19$                     UNDOTBS2                         OFFLINE

    ....etc.

    If the old segments are online, then they must be taken offline:

    SQL>alter rollback segment "_SYSSMU3$" offline;
    SQL>alter rollback segment "_SYSSMU2$" offline;

    This should be executed for all online rollback segments in the old tablespace.

8. Provided all the segments in the old undo tablespace are offline, you can now drop the old undo tablespace:
    SQL>drop tablespace UNDOTBS1 including contents and datafiles;

    Tablespace dropped.

9. Edit your init.ora file do the following changes.

    undo_management='AUTO'
    undo_tablespace='UNDOTBS2'
   
10. Shutdown the database (shutdown immediate) and restart it.

11. Monitor the alert log simultaneously during all steps.

Reference Metalink Note: [ID 431652.1]

7 comments:

Anonymous said...

Good reference Tank you!

ram mohan said...

Oracle 10g/11g Training By Subject matter Experts

Medha Information research Offering In House, Online and Corporate training on Oracle 10g/11
Course Highlights
Fully handson Sessions.
Realtime examples
Interview/project support
Indepth and exclusive coverage on each topic
For More details,
Contact us at +91 9945207350 or write us at info@medhainfo.com

Website: WWW.MEDHAINFO.COM

Yogesh Nikhade said...

THANKS...
I have a question, If default temporary TBS1 is switched to another temp TBS2....segments of (previous) undo TBS1 becomes offline...But what happen when undo tbs2 become full..It gets extended or segment of undo tbs1 becomes online and used again........plz help....

Yogesh Nikhade said...

artlyesTHANKS...
I have a question, If default temporary TBS1 is switched to another temp TBS2....segments of (previous) undo TBS1 becomes offline...But what happen when undo tbs2 become full..It gets extended or segment of undo tbs1 becomes online and used again........plz help....

Yogesh Nikhade said...

THANKS...
I have a question, If default temporary TBS1 is switched to another temp TBS2....segments of (previous) undo TBS1 becomes offline...But what happen when undo tbs2 become full..It gets extended or segment of undo tbs1 becomes online and used again........plz help....

Yogesh Nikhade said...

THANKS...
I have a question, If default temporary TBS1 is switched to another temp TBS2....segments of (previous) undo TBS1 becomes offline...But what happen when undo tbs2 become full..It gets extended or segment of undo tbs1 becomes online and used again........plz help....

Yogesh Nikhade said...

THANKS...
I have a question, If default temporary TBS1 is switched to another temp TBS2....segments of (previous) undo TBS1 becomes offline...But what happen when undo tbs2 become full..It gets extended or segment of undo tbs1 becomes online and used again........plz help....

Autoconfig post 12C upgrade from 11g failed with "ORA-01804: failure to initialize timezone information"

Autoconfig post 12C upgrade from 11g failed with "ORA-01804: failure to initialize timezone information" Error DB Autoconfig ...