【IT168 技术文档】

    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。