ORA-00603，ORA-01595，ORA-00600非正常关机导致的UNDO损坏

如果非必要一般不会重启服务器，如果重启服务器，必须先要正确关闭oracle，然后在重启。

人无完人，总有人会犯错误，其中一台线上oracle维护者，失误，直接将oracle服务器重启了，重启之后，直接造成了，数据库提示ora-00603: ORACLE server session terminated by fatal error,

用sqlplus 登录oracle;

su - oracle

alter system set events '10046 trace name context off';

alter system set timed_statistics=false;

然后重启oracle，最后发现还是报错，

去了oracle 的日志目录并查找trace文件

cd /data0/oracle/admin/dzinfoiims/bdump/

cat dzinfoiims_smon_6401.trc

SMON: following errors trapped and ignored:

ORA-01595: error freeing extent (4) of rollback segment (1))

ORA-00607: Internal error occurred while making a change to a data block

ORA-00600: internal error code, arguments: [4194], [62], [32], [], [], [], [], []

最后几行显示 ora-01595，按照提示因为不正确关闭oracle导致回滚段失败，基本上可以断定undo表空间损坏，但是可以通过重建undo文件。

1）生成pfile

SQL> create pfile from spfile

修改pfile参数：

#*.undo_management='AUTO'

*.undo_management='MANUAL'

_allow_resetlogs_corruption=true

2）以pfile启动数据库

SQL> startup mount pfile='/data0/oracle/product/10.2.0/db_1/dbs/initdzinfoiims.ora';

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 2085872 bytes

Variable Size 373296144 bytes

Database Buffers 230686720 bytes

Redo Buffers 6299648 bytes

Database mounted.

SQL> show parameter undo

NAME TYPE

------------------------------------ ----------------------

VALUE

------------------------------

undo_management string

MANUAL

undo_retention integer

900

undo_tablespace string

UNDOTBS1

SQL> alter database open;

Database altered.

3）新建undo表空间undotbs2

SQL> create undo tablespace undotbs2 datafile '/data0/oracle/oradata/dzinfoiims/undotbs02.dbf' size 1G;

table space created.

SQL> drop tablespace undotbs1;

The deleted table space .

SQL> alter tablespace undotbs2 rename to undotbs1;

table space was alted.

4）以spfile重启数据库

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 5016387584 bytes

Fixed Size 2027640 bytes

Variable Size 671092616 bytes

Database Buffers 4328521728 bytes

Redo Buffers 14745600 bytes

Database mounted.

本以为至此结束，但是有两个表查询，竟然提示ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], []

这是oracle的一个bug

可以通过alter日志，查看是那个表有问题

可以在dba状态下

execute dbms_stats.delete_table_stats('iims','bbs_message_map');

PL/SQL procedure successfully completed.

接下该表，就select就没事了。