ORA-00600: [4194] 错误解决办法

JiekeXu之路

发布于 2024-03-02 10:02:38

1560

发布于 2024-03-02 10:02:38

文章被收录于专栏：JiekeXu之路JiekeXu之路

作者 | JiekeXu

来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）

如需转载请联系授权 | (个人微信 ID：JiekeXu_DBA)

大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看ORA-00600: [4194] 错误解决办法，欢迎点击上方蓝字“JiekeXu DBA之路”关注我的公众号，标星或置顶，更多干货第一时间到达!

前言

上一篇文章还停留在腊月二十六，现在正月十五也已经过去了，这个年算是过去了，这二十多天里看到很多大佬都在不停的更新文章，卷的铺天盖地，我就只能假装看不见，算是躺平了，什么也没有干，静静地等待这个年过完。本文是今年年初一位朋友遇到的数据库的小问题，事后指导其记录形成的文档，算是小白记录问题处理过程，也是本公众号的第一篇投稿（还有投稿的读者朋友可以找我私聊），适合初学者通过搜索引擎、MOS 来解决 Oracle 数据库遇到的小问题并按照一定的格式来记录问题处理过程，具有一定的参考性，废话不多说，进入正文。

环境信息

生产环境是 Oracle11g 11204 版本的单机数据库，没有备库，部分数据有物理备份；数据库本身没有任何补丁。

[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 11 11:02:36 2024
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from dba_registry_history;
ACTION_TIME               ACTION   NAMESPAC VERSION          ID BUNDL COMMENTS
---------------------------- -------- -------- ---------- ---------- ----- -------------
11-DEC-18 05.44.22.925930 PM   APPLY    SERVER   11.2.0.4            0 PSU   Patchset 11.2.0.2.0

问题现象

一项目组的外地项目的生产环境，上个月的某个周五凌晨几台物理机宕机，当机器正常启动后，厂商反馈数据库有异常，做了很多修复依旧不行（不知道做了哪些修复还不行），于是乎他则接手，开通远程查看数据库发现，数据库可以正常启动，但是无法执行任何查询命令，会立马宕机，如下所示：

[oracle@localhost ~]$ sqlplus / as sysdba
SQL*plus: Release 11.2.0.4.0 Production on Fri Jan 5 12:37:03 2024Copyright (c) 1982，2013， oracle. A11 rights reserved.Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total system Gobal Area  7532736512    bytes
Fixed Size                 2267912    bytes
Variable size            2097153272    bytes
Database Buffers        5419040768    bytes
Redo Buffers              14274560    bytes  
Database mounted.
Database opened.
SQL> show parameter name;
ORA-03135: connection 1ost contact
Process ID: 6416
Session ID: 283  serial number: 5

很奇怪，可以正常 open 数据库，但是不能进行下一步的查询操作，于是只能去查看 alert 日志，发现有大批量的 ORA-00600 报错：

opiodr aborting process unknown ospid (4436) as a result of ORA-603
Block recovery from logseq 1, block 65 to scn 33902276338
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /datafile/orcl/redo01.log
Block recovery completed at rba 1.68.16, scn 7.3837505268
Block recovery from logseq 1, block 65 to scn 33902276377
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /datafile/orcl/redo01.log
Block recovery completed at rba 1.110.16, scn 7.3837505312
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_4472.trc  (incident=336185):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_336185/orcl_m000_4472_i336185.trc
Dumping diagnostic data in directory=[cdmp_20240105103802], requested by (instance=1, osid=4436), summary=[incident=336163].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_4472.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Fri Jan 05 10:38:46 2024
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_4504.trc  (incident=336200):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_336200/orcl_m000_4504_i336200.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Block recovery from logseq 1, block 65 to scn 33902276338
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /datafile/orcl/redo01.log
Block recovery completed at rba 1.68.16, scn 7.3837505268
Block recovery from logseq 1, block 65 to scn 33902276575
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /datafile/orcl/redo01.log
Block recovery completed at rba 1.129.16, scn 7.3837505504
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_4504.trc  (incident=336201):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_336201/orcl_m000_4504_i336201.trc
Fri Jan 05 10:38:47 2024
Dumping diagnostic data in directory=[cdmp_20240105103847], requested by (instance=1, osid=4504 (M000)), summary=[incident=336200].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_4504.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Dumping diagnostic data in directory=[cdmp_20240105103848], requested by (instance=1, osid=4504 (M000)), summary=[incident=336201].
Fri Jan 05 10:39:01 2024

对于 ora-00600 错误，我们只能借鉴 MOS 去搜索相关报错了，通过在 MOS 中搜索 ORA-00600 [4194] 来获取相同或相似知识，如下图第二篇 Doc ID 1428786.1 就是我们本次借鉴的文章。

强大的 MOS 网站也提供了一个专门搜索 ORA-600 700 7445 的工具链接,可可以直接在此页面搜索相关错误代码，查找 bug 等等。[ORA-600/ORA-7445/ORA-700 Error Look-up Tool (Doc ID 153788.1)]

问题原因

在MOS上找到了一篇文章 (Doc ID 1428786.1)对这个问题有具体的分析与处理过程。

在数据库崩溃之前，alert.log 中出现了以下错误。ora - 00600:内部错误代码,参数:[4194 ], [#], [#], [], [], [], [], [] 错误表明在重做记录和回滚(撤消)记录之间检测到不匹配，这个问题通常发生在断电或硬件故障导致数据库崩溃的情况下。这不正是符合我们上周五凌晨断电的问题吗? 那么一起来看看处理办法吧。

处理过程

根据(Step by step to resolve ORA-600 4194 4193 4197 on database crash (Doc ID 1428786.1)文档中提供的方法尝试恢复数据库。

1、创建pfile（nomount）

SQL> create pfile='/u01/pfile.ora' from spfile;
create pfile='/u01/pfile.ora!from spfile
*
ERROR at Tine 1:
ORA-07391: sftopn: fopen error, unable to opentext file.
u01/app/oracle/product/11.2.0dbhome_1/dbs/spfileorc1.ora
ERROR at Tine 1:ORA-07391: sftopn: fopen error, unable to opentext file.
SQL> show parameter spfile:
NAME           TYPE            VALUE
-----------   -------------   -------------------------------------------------------------------
spfile           string              /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorc1.ora
[oracle@localhost dbs]$ strings spfileorcl.ora > initorcl.ora

这里也没法直接在 nomount 下创建 pfile，索性直接通过 strings 命令将 spfile 内容写入到 pfile 里，然后检查 initorcl.ora 参数内容是否出现换行、空格等错误格式加以修改。

2、修改 pfile

添加以下参数：

undo_management=manual
event='10513 trace name context forever, level 2'

3、使用 restrict 模式启动

关闭数据库并用 pfile 启动：编者注：如果 pfile 在默认的 dbs 目录下，启动时也可不用指定路径就能访问。

SQL> startup restrict pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'
ORACLE instance started.
Total System Global Area 5010685952 bytes
Fixed Size                  2261848 bytes
Variable Size             989858984 bytes
Database Buffers         4009754624 bytes
Redo Buffers                8810496 bytes
ORA-00205: error in identifying control file, check alert log for more info

如上错误，是由于 strings 时控制文件换行了导致的错误而没有察觉。

vi initorcl.ora

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'(发现控制文件换行了）

修改完重新启动.

SQL> startup restrict pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'
ORACLE instance started.
Total System Global Area 5010685952 bytes
Fixed Size                  2261848 bytes
Variable Size             989858984 bytes
Database Buffers         4009754624 bytes
Redo Buffers                8810496 bytes
Database mounted.
Database opened.
SQL>

查看回滚段：

SQL>  select tablespace_name,status,segment_name from dba_rollback_segs where status !='OFFLINE';

TABLESPACE_NAME      STATUS                           SEGMENT_NAME
------------------ ------------------------- ------------------------------------------------------------
SYSTEM               ONLINE                           SYSTEM
...... 等等

这一点非常重要 - 我们希望所有撤消段都处于离线状态 - SYSTEM 将始终在线。如果有任何 "部分可用 "或 “需要恢复”，需要另当别论。如果全部脱机，则继续下一步。

4、创建新的 undo 表空间

SQL> select tablespace_name,file_name,bytes/1024/1024 mb ,autoextensible from dba_data_files order by tablespace_name;

TABLESPACE_NAME      FILE_NAME                                                            MB AUTOEX
-------------------- -------------------------------------------------------- ---------- ----
SYSAUX               /u01/app/oracle/oradata/orcl/sysaux01.dbf                490 YES
SYSTEM               /u01/app/oracle/oradata/orcl/system01.dbf                740 YES
UNDOTBS1             /u01/app/oracle/oradata/orcl/undotbs01.dbf                30 YES
USERS                /u01/app/oracle/oradata/orcl/users01.dbf                    5 YES

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 30G; 

Tablespace created.

Using your Original spfile：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

这里如果有默认的 pfile 存在于 dbs 目录下，我们需要将其 mv 重命名，防止使用 pfile 启动。

[oracle@localhost ~]$ mv /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora_bak20240111
[oracle@localhost ~]$ sqlplus / as sysdba

SQL> startup nomount      
ORACLE instance started.
Total System Global Area 5010685952 bytes
Fixed Size                  2261848 bytes
Variable Size             989858984 bytes
Database Buffers         4009754624 bytes
Redo Buffers                8810496 bytes

SQL> alter system set undo_tablespace=undotbs2 scope=spfile;
File created.

System altered.
SQL> 
SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

5、使用 spfile 重新启动数据库

SQL> shutdown immediate;
Ora-01507 Database not mounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total system Gobal Area  7532736512    bytes
Fixed Size                 2267912    bytes
Variable size            2097153272    bytes
Database Buffers        5419040768    bytes
Redo Buffers              14274560    bytes  
Database mounted.
Database opened.
SQL> 
set line  240 
col HOST_NAME for a30 
select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from gv$instance; 
SQL> 
INSTANCE_NAME    HOST_NAME                      VERSION           STARTUP_TIME        STATUS
---------------- ------------------------------ ----------------- ------------------- ------------
ORCL             localhost                      11.2.0.4.0        2024-01-05 12:38:48 OPEN

SQL> select sum(bytes)/1024/1024/1024 Gb from dba_segments; 

        GB
----------
30.7781982

SQL> select inst_id,count(*),status  from gv$session where type<>'BACKGROUND' group by inst_id,status order by 1; 

   INST_ID   COUNT(*) STATUS
---------- ---------- --------
         1          2 ACTIVE
         1        108 INACTIVE

数据库正常启动,也可正常查询，alert 日志再无 ORA-00600 错误,算是业务恢复正常。

参考文章

Doc ID 1428786.1 https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=447276702637698&id=1428786.1&_afrWindowMode=0&_adf.ctrl-state=1co1wx0pfd_4 

Step by step to resolve ORA-600 4194 4193 4197 on database crash (Doc ID 1428786.1)

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2024-02-26，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库