前 言
本文是今年年初一朋友遇到的问题，事后记录形成的文档，适合初学者通过搜索引擎、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 网站也提供了一个专门搜索 ORA600 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)
————————————————————————————
微信公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————
