Monday, June 22, 2009

How to replace undo tablespace in an Oracle database


Task: replace UNDO tablespace in an Oracle database
Steps:
1. See the UNDO settings in the database:
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- --------------------
undo_management string AUTO
undo_retention integer 10800
undo_tablespace string UNDO
2. Determine the size of the UNDO datafiles:
SQL> select bytes, name from v$datafile where upper(name) like '%UNDO%';

BYTES NAME
-------------------- -----------------------------------------------
1433075712 /fs/u02/oradata/testdb/undo_01.dbf
3. Create a new UNDO tablespace according to the requirement:

CREATE UNDO TABLESPACE UNDOTBS1 datafile '/fs/u02/oradata/testdb/undotbs01.dbf' size 2000m;

Tablespace created.
4. Edit the init.ora file and change the parameter "undo_tablespace=UNDOTBS1" and "undo_management=MANUAL". Note that a pfile may need to be created first by using "create pfile=… from spfile=…". "undo_management=MANUAL" ensures the old rollback segments can be taken offline and avoids editing the pfile and restarting the instance again.
5. Shut down the databse cleanly by performing a shutdown immediate.

SQL> shutdown immediate

6. Startup the database (specify the pfile if needed.)
SQL> startup
7. Confirm the new tablespace is in use:
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
8. 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
------ ------------------------------ ------------------------------ ----------------
SYS SYSTEM SYSTEM ONLINE
PUBLIC _SYSSMU1$ UNDO OFFLINE
PUBLIC _SYSSMU2$ UNDO OFFLINE
PUBLIC _SYSSMU3$ UNDO OFFLINE
PUBLIC _SYSSMU4$ UNDO OFFLINE
PUBLIC _SYSSMU5$ UNDO OFFLINE
PUBLIC _SYSSMU6$ UNDO OFFLINE
PUBLIC _SYSSMU7$ UNDO OFFLINE
PUBLIC _SYSSMU8$ UNDO OFFLINE
PUBLIC _SYSSMU9$ UNDO OFFLINE
PUBLIC _SYSSMU10$ UNDO OFFLINE
PUBLIC _SYSSMU11$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU12$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU13$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU14$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU15$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU16$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU17$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU18$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU19$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU20$ UNDOTBS1 OFFLINE
21 rows selected.
If the old segments are online, then they must be taken offline as follows:
SQL>alter rollback segment "_SYSSMU3$" offline;

9. Drop the old undo tablespace and remove from the OS directory:
SQL> drop tablespace UNDO;
Tablespace dropped.

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$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU12$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU13$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU14$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU15$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU16$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU17$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU18$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU19$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU20$ UNDOTBS1 OFFLINE
11 rows selected.
SQL> shutdown immediate
10. Modify the init.ora file to change undo_management to AUTO and restart the database. spfile may need to be created. :
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$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU12$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU13$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU14$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU15$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU16$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU17$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU18$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU19$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU20$ UNDOTBS1 ONLINE
11 rows selected.


More Oracle DBA tips, please visit Oracle DBA Tips  

No comments:

Post a Comment