最近,某客户数据库宕机后无法 open , 启动时报错: ORA-600 [kteuproptime-2] 。
客户操作系统为 aix5207 ,数据库版 本为 9.2.0.1 。
Metalink 上面有此报错的相关信息, 9.2.0.1 中, bug 2431450
Symptoms:
Related To:
* Internal Error May Occur (ORA-600)
* Corruption (Physical)
* ORA-600 [kteuPropTime-2]
* ORA-600 [4191]
* System Managed Undo (SMU)
Description
SMU (System Managed Undo) Undo corruption possible on instance crash.
This can result in subsequent ORA-600 errors due to the undo
corruption.
此 bug ,在 9.2.0.4 中 已解决,因此决定升级数据库。
对数据库做完冷备份后,升级到 9.2.0.4, 数 据库依然无法打开,报错信息依旧。另外通过 alert 日志,可以发现, alter database open 时候, redo log 可以打开, instance recovery completed 。如果正常启动,接下来应该是启动 undo 文 件了,但是这个时候就出来 ora600 的错误,结合 metalink 上 面的信息,我们初步判断问题可能是出在 undo 表空间上,按着这个思路,继续尝试解决问题
创建 pfile
SQL> create pfile='/oracle/p1.ora' from spfile;
File created.
修改 pfile 中 undo 的 相关信息
*.undo_management='MANUAL '
*.undo_tablespace='SYSTEM '
以 pfile 启动到 mount 状 态
SQL> startup mount pfile='/oracle/p1.ora'
ORACLE instance started.
Total System Global Area 303531576 bytes
Fixed Size 742968 bytes
Variable Size 285212672 bytes
Database Buffers 16777216 bytes
Redo Buffers 798720 bytes
Database mounted.
SQL>
Offline drop undo 文件后, open 开 数据库
SQL> alter database datafile '/oracle/oradata/oratest/undotbs0.dbf' offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL>
查看 rollback segments 信 息。
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS0 OFFLINE
_SYSSMU2$ UNDOTBS0 OFFLINE
_SYSSMU3$ UNDOTBS0 OFFLINE
_SYSSMU4$ UNDOTBS0 OFFLINE
_SYSSMU5$ UNDOTBS0 OFFLINE
_SYSSMU6$ UNDOTBS0 OFFLINE
_SYSSMU7$ UNDOTBS0 OFFLINE
_SYSSMU8$ UNDOTBS0 OFFLINE
_SYSSMU9$ UNDOTBS0 OFFLINE
_SYSSMU10$ UNDOTBS0 OFFLINE
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11$ UNDOTBS0 NEEDS RECOVERY
_SYSSMU12$ UNDOTBS0 NEEDS RECOVERY
从这里面可以看到, _SYSSMU11$,_SYSSMU12$ 这 两个数据段 need recovery ,我们需要通过隐患参数 _ corrupted_rollback_segments, 丢 弃这两个段
在之前的 pfile 中,增减下列信息
_ corrupted_rollback_segments=(_SYSSMU11$,_SYSSMU12$)
SQL> startup restrict pfile='/oracle/p1.ora';
ORACLE instance started.
Total System Global Area 303531576 bytes
Fixed Size 742968 bytes
Variable Size 285212672 bytes
Database Buffers 16777216 bytes
Redo Buffers 798720 bytes
Database mounted.
Database opened.
Drop 掉旧的 undo 表 空间
SQL> drop tablespace undotbs0 including contents;
Tablespace dropped.
建立新的 undo 表空间
SQL> create undo tablespace undotbs1 datafile '/oracle/oradata/oratest/undotbs1.dbf' size 150M autoextend on;
Tablespace created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
修改 pfile
*.undo_management='AUTO '
*.undo_tablespace='UNDOTBS1’
并删除 _ corrupted_rollback_segments=(_SYSSMU11$,_SYSSMU12$)
相关信息
SQL> startup pfile='/oracle/p1.ora'
ORACLE instance started.
Total System Global Area 303531576 bytes
Fixed Size 742968 bytes
Variable Size 285212672 bytes
Database Buffers 16777216 bytes
Redo Buffers 798720 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile='/oracle/p1.ora';
File created.
SQL>
到此 undo 表空间就恢复正常了,数据库 业务数据也正常了
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 OFFLINE
_SYSSMU2$ UNDOTBS1 OFFLINE
_SYSSMU3$ UNDOTBS1 OFFLINE
_SYSSMU4$ UNDOTBS1 OFFLINE
_SYSSMU5$ UNDOTBS1 OFFLINE
_SYSSMU6$ UNDOTBS1 OFFLINE
_SYSSMU7$ UNDOTBS1 OFFLINE
_SYSSMU8$ UNDOTBS1 OFFLINE
_SYSSMU9$ UNDOTBS1 OFFLINE
_SYSSMU10$ UNDOTBS1 OFFLINE
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11$ UNDOTBS1 ONLINE
12 rows selected.
最后,对数据库进行了一次逻辑全库备份。