某次Undo 表空间损坏的修复

最近，某客户数据库宕机后无法 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.

最后，对数据库进行了一次逻辑全库备份。