关于oracle的ORA-00607和ORA-00600错误解决方法

转自： http://lym6520.iteye.com/blog/1693710

前些天数据库服务器出现了一下错误：

SQL*Loader-961:  对表 table1 调用一次/加载完成时出错
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kddummy_blkchk], [8], [565129], [18021], [], [], [], []

SQL*Loader-2026:  加载因 SQL 加载程序无法继续而被终止

该错误主要是由于数据库非法关闭造成的。

关于ORA-00600错误描述：

引用

ORA-00600: internal error code, arguments: [string], [string], [string], [string], [string], [string], [string], [string], [string], [string], [string], [string]
Cause: This is the generic internal error number for Oracle program exceptions. It indicates that a process has encountered a low-level, unexpected condition. The first argument is the internal message number. This argument and the database version number are critical in identifying the root cause and the potential impact to your system.
Action: Visit My Oracle Support to access the ORA-00600 Lookup tool (reference Note 600.1) for more information regarding the specific ORA-00600 error encountered. An Incident has been created for this error in the Automatic Diagnostic Repository (ADR). When logging a service request, use the Incident Packaging Service (IPS) from the Support Workbench or the ADR Command Interpreter (ADRCI) to automatically package the relevant trace information (reference My Oracle Support Note 411.1). The following information should also be gathered to help determine the root cause:
- changes leading up to the error

- events or unusual circumstances leading up to the error

- operations attempted prior to the error

- conditions of the operating system and databases at the time of the error Note: The cause of this message may manifest itself as different errors at different times. Be aware of the history of errors that occurred before this internal error.

关于ORA-00607错误描述：

引用

ORA-00607: Internal error occurred while making a change to a data block
Cause: An internal error or memory exception occurred while Oracle was applying redo to a data block.
Action: call Oracle Support

参考了网上的一些资料,通过以下操作解决ORA-00600和ORA-00607错误：

sqlplus /nolog
conn / as sysdba;
#查看undo的表空间管理方式应该是auto
show parameter undo;
#修改undo的表空间管理方式为manual
alter system set undo_management=manual scope=spfile;
shutdown immediate;
startup;
#创建undo临时表空间
create undo tablespace undo2 datafile '/opt/oradata/tgt/undo2.dbf' size 200M;
#修改undo表空间为undo2
alter system set undo_tablespace=undo2 scope=spfile;
#将undo表空间管理模式修改为auto
alter system set undo_management=auto scope=spfile;
shutdown immediate;
startup;

执行完以上操作后为发现ORA-00600错误，说明重建表空间问题解决，接下来将undo表空间改回undotbs1

drop tablespace undotbs1 including contents and datafiles;
create undo tablespace undotbs1 datafile '/opt/oradata/tgt/undotbs1.dbf' size 500M;
alter system set undo_management=manual scope=spfile;
shutdown immediate;
startup;
alter system set undo_tablespace=undotbs1 scope=spfile;
alter system set undo_management=auto scope=spfile;
shutdown immediate;
startup;
#删除刚刚临时重建的undo2表空间
drop tablespace undo2 including contents and datafiles;

大致确定是由于块当中存在逻辑讹误导致的这个错误
{当Oracle进程在读取数据块时会做一系列逻辑检测，当发现块当中存在逻辑讹误就会触发该ORA-00600 [kddummy_blkchk]等内部错误；[kddummy_blkchk]内部函数
的功能大致与[kdBlkCheckError]相仿，它们都有3个参数argument：
ORA-600 [kddummy_blkchk] [file#] [block#] [check code]
ORA-600 [kdBlkCheckError]   [file#] [block#] [check code]
file#即问题块所在datafile的文件号，block#即问题块的块号，check code为发现逻辑讹误时的检测种类代码
}
我们也可以通过file#和block#查找到存在问题的对象：譬如这个case中的file#为121,block#为2275853，检查种类代码为18021:

SELECT tablespace_name,segment_type,owner,segment_name
FROM dba_extents
 WHERE file_id = 8
AND 565129 between block_id AND block_id+blocks-1;

查询结果如：
TABLESPACE_NAME                SEGMENT_TYPE       OWNER     SEGMENT_NAME
------------------------------ ------------------ ------------------------------
DATA01                         TABLE              USER1     TABLE1

该查询结果中的表需要重建，问题解决。

参考资料：
http://blog.sina.com.cn/s/blog_487f7b730100ovoh.html
http://blog.chinaunix.net/uid-23249684-id-3208290.html