Undo Tablespace Corruption出现问题是一个较常见的问题，硬件的问题、机器的突然断电、人为的误操做及Oracle的Bug都可能引起回滚段段头及段本身的Corruption。
Platform:Oracle9.2.0.5+Aix5L
During a long import process, i had added a datafile to my undo tablespace (UNDOTBS1). UNDO_MANAGEMENT initialization parameter was AUTO.
Now when the import was going on, I tried dropping the datafile i had just added to the undo tablespace. The drop statement hung so i killed the session. Next i also killed my import session.
Then when i restarted my database but it would abort with saying
ORA-00376: file 30 cannot be read at this time
I then started up my database with UNDO_MANAGEMENT=MANUAL
I created a new undo tablespace (UNDOTBS).
Then i shutdown my database, changed the parameter UNDO_MANAGEMENT=AUTO and UNDO_TABLESPACE=UNDOTBS and was able to start my database successfully.
I also had to drop the tablespace and recreate it for the file for which the ORA-00376 was coming
So now things were fine except for:
I could not drop my older undo tablespace (UNDOTBS1)
I got ORA-00376
Cause:
If you try to drop the undo tablespace that contains active rollback segment then ORA-01548 is the error reported. The undo tablespace need recovery.
SQL > select status from v$datafile where file#=30;
STATUS
----------
RECOVER
1 row selected
The file# 30 is a part of the undo tablespace UNDOTBS1. There was an active transaction in that undo tablespace. The rollback segment is referring to a dead transaction.
SELECT SEGMENT_NAME, STATUS
FROM DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME='UNDOTBS1';
SEGMENT_NAME STATUS
------------------------------ ----------------
_SYSSMU5$ NEEDS RECOVERY
_SYSSMU6$ OFFLINE
_SYSSMU7$ OFFLINE
_SYSSMU8$ OFFLINE
_SYSSMU9$ OFFLINE
_SYSSMU10$ OFFLINE
Offline drop does not work:
alter database datafile 30 offline drop;
database altered
SQL> select status from v$datafile where file#=30;
STATUS
-------
RECOVER
We need to set the undescore parameter in init.ora and then start the database
_OFFLINE_ROLLBACK_SEGMENTS=_SYSSMU5$
startup the database
SQL> SELECT SEGMENT_NAME,STATUS FROM DBA_ROLLBACK_SEGS WHERE TABLESPACE_NAME='UNDOTBS1';
SEGMENT_NAME STATUS
------------------------------ ----------------
_SYSSMU5$ NEEDS RECOVERY
_SYSSMU6$ OFFLINE
_SYSSMU7$ OFFLINE
_SYSSMU8$ OFFLINE
_SYSSMU9$ OFFLINE
_SYSSMU10$ OFFLINE
SQL> drop rollback segment "_SYSSMU5$";
Rollback segment dropped.
SQL > drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
Then,you should Backup database using exp and recreate database。
