Friday, April 30, 2010

Oracle UNDO DBF issues

Oracle 10.2 is installed on Windows XP SP3.

Useful references:

1. Switch Undo Tablespace
2. Fun with UNDO tablespaces
3. ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping tablespace
4. Oracle Init.Ora & SPFile
5. Automatic UNDO Management in Oracle9i

Problem description:
UNDO dbf file was f**ed up, it have to be removed and replaced.

Create temp tablespace and switch UNDO to it:
sqlplus> create undo tablespace UNDO_RBS1 datafile 'path_to_new_undo_dbf' size 500M AUTOEXTEND ON MAXSIZE 3900M;
sqlplus> alter system set undo_tablespace=UNDO_RBS1;

sqlplus> select status from v$rollstat;
STATUS
---------------
ONLINE
PENDING OFFLINE
PENDING OFFLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
You can't drop or offline if status show offline pending.
Restart DB:
sqlplus> shutdown immediate;
sqlplus> startup;
And now try to drop undo tablespace:
sqlplus>alter tablespace UNDOTBS1 offline;
sqlplus>drop tablespace UNDOTBS1 including contents and datafiles;
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping
tablespace
Cause of The Problem
An attempt was made to drop a tablespace that contains active rollback segments.

Solution of The Problem
In order to get rid of this error you have to follow following steps.

1)Create pfile if you started with database with spfile.
sqlplus> create PFILE from SPFILE;
You can use the next command to get path to spfile (in my case pfile was located at "D:\oracle\product\10.2.0\admin\A7\pfile\init.ora.1252010112825" ):
sqlplus> show parameter spfile

2)Edit pfile and set undo management to manual.
undo_management = manual

3)Put the entries of the undo segments in the pfile by using the following statement in the pfile:
_CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,.....)
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=MANUAL
undo_tablespace=UNDOTBS1
_CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU1$)

4)Mount the database using new pfile:
sqlplus> shutdown immediate;
sqlplus> startup mount pfile='fullpath to pfile'

5)Drop the undo segments,
sqlplus> drop rollback segment "_SYSSMU1$";

6) Drop Old undo tablespace.
sqlplus> drop tablespace UNDOTBS1 including contents and datafiles;

7) Add the new undo tablespace:
sqlplus> create undo tablespace UNDOTBS1 datafile 'path to new undo table space' size 500M AUTOEXTEND ON MAXSIZE 32000M;
sqlplus> alter system set undo_tablespace=UNDOTBS1;

8) Remove temp undo table space:
sqlplus> alter tablespace UNDO_RBS1 offline;
sqlplus> drop tablespace UNDO_RBS1 including contents and datafiles;
sqlplus> shutdown immediate;

9) Change the pfile parameters back to Undo_management = AUTO and modify the parameter Undo_tablespace=new_undo_tablespace_name and remove the _corrupted_rollback_segments parameter:
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_tablespace=UNDOTBS1
#_CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU1$)

10) Startup the Database:
sqlplus> startup

NOTE: In step #3 I've tried to use _OFFLINE_ROLLBACK_SEGMENT = (_syssmu1$). But it didn't work.

No comments:

Post a Comment