Oracle Point, Oracle Life.

Due to limitation of disk capacity, I was asked to resize one of our databases. Our investigation shows that the size of undo tablespace has been set to unnecessarily large, say 8G. Our junior DBA was trying to switch to newly created undo tablespace and have placed the datafile for default undo tablespace offline. After doing that, he got error code Ora-01157 and Ora-01110 when he tried to drop the prior default undo tablespace. To figure out the problem and perform the switch, we followed the following steps to have it done.

Step 1: Query to discover how many rollback segments were corrupted.

SQL> select segment_name, status, tablespace_name from    dba_rollback_segs where status=’NEEDS RECOVERY’;

SEGMENT_NAME    STATUS                  TABLESPACE_NAME
—————————— —————————— ——————————-
_SYSSMU1$          NEEDS RECOVERY UNDOTBS1
_SYSSMU2$          NEEDS RECOVERY UNDOTBS1
_SYSSMU3$          NEEDS RECOVERY UNDOTBS1
_SYSSMU4$          NEEDS RECOVERY UNDOTBS1
_SYSSMU5$          NEEDS RECOVERY UNDOTBS1
_SYSSMU6$          NEEDS RECOVERY UNDOTBS1
_SYSSMU7$          NEEDS RECOVERY UNDOTBS1
_SYSSMU8$          NEEDS RECOVERY UNDOTBS1
_SYSSMU9$          NEEDS RECOVERY UNDOTBS1
_SYSSMU10$          NEEDS RECOVERY UNDOTBS1

Step 2: Add the following line to pfile

_corrupted_rollback_segments =('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU21$')

Step 3: Shutdown database clearly 

$ sqlplus “/ as sysdba”

SQL> connect sys@dbname as sysdba

SQL> shutdown immediate

Step 4: Start database with newly updated pfile

SQL> startup pfile=/oracle/db10g/dbs/initDBNAME.ora

Step 5: Drop all of the corrupted rollback segments

SQL> drop rollback segment “_SYSSMU1”;

……………..

SQL> drop rollback segment “_SYSSMU10”;

Step 6: Now drop bad undo tablespace

SQL> drop tablespace UNDOTBS1;

Done!

Once finishing above steps, we went back to OEM console and confirmed that bad undo tablespace has been removed. The new undo tablespace is the one our Junior DBA created.

Popularity: 46% [?]