ITPub博客

首页 > 应用开发 > IT综合 > Undo Tablespace Corruption处理

Undo Tablespace Corruption处理

原创 IT综合 作者:qiuyb 时间:2006-03-22 16:00:07 0 删除 编辑
Undo Tablespace Corruption出现问题是一个较常见的问题,硬件的问题、机器的突然断电、人为的误操做及Oracle的Bug都可能引起回滚段段头及段本身的Corruption。[@more@]

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。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/717880/viewspace-822116/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Transaction Table
下一篇: Oracle's x$ Tables
请登录后发表评论 登录
全部评论
  • 博文量
    75
  • 访问量
    650229