ORA-600 4194引起SMON encountered 100 out of maximum 100 non-fatal internal errors故障

Posted on 2025 年 09 月 23 日 by 惜分飞

标题：ORA-600 4194引起SMON encountered 100 out of maximum 100 non-fatal internal errors故障

客户数据库11.2.0.3版本,由于机房停电导致数据库启动之后一段时间自动crash

Sat Sep 20 20:31:14 2025
QMNC started with pid=39, OS id=10637 
Completed: ALTER DATABASE OPEN
Starting background process CJQ0
Sat Sep 20 20:31:14 2025
CJQ0 started with pid=44, OS id=10654 
Setting Resource Manager plan SCHEDULER[0x318E]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Starting background process VKRM
Sat Sep 20 20:31:17 2025
VKRM started with pid=40, OS id=10680 
Sat Sep 20 20:38:01 2025
Starting background process SMCO
Sat Sep 20 20:38:01 2025
SMCO started with pid=38, OS id=10955 
Sat Sep 20 20:56:54 2025
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_11564.trc (incident=148368):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
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 1082886, block 29263 to scn 74449804596
Recovery of Online Redo Log: Thread 1 Group 14 Seq 1082886 Reading mem 0
  Mem# 0: /oracledb/oradata/orcl/redo14.log
Block recovery stopped at EOT rba 1082886.29264.16
Block recovery completed at rba 1082886.29264.16, scn 17.1435360559
Block recovery from logseq 1082886, block 29263 to scn 74449804590
Recovery of Online Redo Log: Thread 1 Group 14 Seq 1082886 Reading mem 0
  Mem# 0: /oracledb/oradata/orcl/redo14.log
Block recovery completed at rba 1082886.29264.16, scn 17.1435360559
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_11564.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Sat Sep 20 20:56:58 2025
Sweep [inc][148368]: completed
Sweep [inc2][148368]: completed
Sat Sep 20 21:00:20 2025
Exception[type:SIGSEGV,Address not mapped to object][ADDR:0xBC44AC1][PC:0x932F8EA,kgegpa()+40][flags:0x0,count:1]
Exception[type:SIGSEGV,Address not mapped to object][ADDR:0xBC44AC1][PC:0x932DEF3,kgebse()+771][flags:0x2,count:2]
Exception[type:SIGSEGV,Address not mapped to object][ADDR:0xBC44AC1][PC:0x932DEF3,kgebse()+771][flags:0x2,count:2]
Sat Sep 20 21:00:21 2025
Block recovery from logseq 1082886, block 29263 to scn 74449804596
Recovery of Online Redo Log: Thread 1 Group 14 Seq 1082886 Reading mem 0
  Mem# 0: /oracledb/oradata/orcl/redo14.log
……………………
Sat Sep 20 21:05:00 2025
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_10516.trc(incident=148296):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
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 1082886, block 32045 to scn 74449805729
Recovery of Online Redo Log: Thread 1 Group 14 Seq 1082886 Reading mem 0
  Mem# 0: /oracledb/oradata/orcl/redo14.log
Block recovery completed at rba 1082886.32056.16, scn 17.1435361698
Block recovery from logseq 1082886, block 32045 to scn 74449806046
Recovery of Online Redo Log: Thread 1 Group 14 Seq 1082886 Reading mem 0
  Mem# 0: /oracledb/oradata/orcl/redo14.log
Block recovery completed at rba 1082886.32321.16, scn 17.1435362015
Non-fatal internal error happenned while SMON was doing flushing of monitored table stats.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
*******
Block recovery completed at rba 1082898.52054.16, scn 17.1444838013
Non-fatal internal error happenned while SMON was doing flushing of monitored table stats.
SMON encountered 99 out of maximum 100 non-fatal internal errors.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_10516.trc(incident=164458):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
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 1082898, block 52038 to scn 74459282045
Recovery of Online Redo Log: Thread 1 Group 8 Seq 1082898 Reading mem 0
  Mem# 0: /oracledb/oradata/orcl/redo08.log
Block recovery completed at rba 1082898.52054.16, scn 17.1444838014
Block recovery from logseq 1082898, block 52038 to scn 74459282088
Recovery of Online Redo Log: Thread 1 Group 8 Seq 1082898 Reading mem 0
  Mem# 0: /oracledb/oradata/orcl/redo08.log
Block recovery completed at rba 1082898.52104.16, scn 17.1444838057
Non-fatal internal error happenned while SMON was doing flushing of monitored table stats.
SMON encountered 100 out of maximum 100 non-fatal internal errors.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_10516.trc  (incident=164459):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
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 1082898, block 52038 to scn 74459282045
Recovery of Online Redo Log: Thread 1 Group 8 Seq 1082898 Reading mem 0
  Mem# 0: /oracledb/oradata/orcl/redo08.log
Block recovery completed at rba 1082898.52054.16, scn 17.1444838014
Block recovery from logseq 1082898, block 52038 to scn 74459282101
Recovery of Online Redo Log: Thread 1 Group 8 Seq 1082898 Reading mem 0
  Mem# 0: /oracledb/oradata/orcl/redo08.log
Block recovery completed at rba 1082898.52130.16, scn 17.1444838070
Non-fatal internal error happenned while SMON was doing flushing of monitored table stats.
SMON exceeded the maximum limit of 100 internal error(s).
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_10516.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
SMON (ospid: 10516): terminating the instance due to error 474
Mon Sep 22 04:05:28 2025
System state dump requested by(instance=1,osid=10516 (SMON)),summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_diag_10463.trc
Instance terminated by SMON, pid = 10516

错误原因比较明显是由于“Non-fatal internal error happenned while SMON was doing flushing of monitored table stats.”smon进程对MON_MODS$/MON_MODS_ALL$操作异常触发ORA-600 4194错误使得该操作失败,默认情况smon尝试100次(_smon_internal_errlimit该参数控制)依旧没有成功,会强制终止smon进程,从而导致实例crash.然后尝试重启数据库无法启动成功

Mon Sep 22 09:00:03 2025
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 32 processes
Started redo scan
Completed redo scan
 read 1360 KB redo, 405 data blocks need recovery
Started redo application at
 Thread 1: logseq 1082898, block 49410
Recovery of Online Redo Log: Thread 1 Group 8 Seq 1082898 Reading mem 0
  Mem# 0: /oracledb/oradata/orcl/redo08.log
Completed redo application of 0.60MB
Completed crash recovery at
 Thread 1: logseq 1082898, block 52130, scn 74459302102
 405 data blocks read, 405 data blocks written, 1360 redo k-bytes read
Thread 1 advanced to log sequence 1082899 (thread open)
Thread 1 opened at log sequence 1082899
  Current log# 9 seq# 1082899 mem# 0: /oracledb/oradata/orcl/redo09.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_78465.trc  (incident=164779):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Block recovery from logseq 1082899, block 3 to scn 74459302111
Recovery of Online Redo Log: Thread 1 Group 9 Seq 1082899 Reading mem 0
  Mem# 0: /oracledb/oradata/orcl/redo09.log
Block recovery stopped at EOT rba 1082899.5.16
Block recovery completed at rba 1082899.5.16, scn 17.1444858077
Block recovery from logseq 1082899, block 3 to scn 74459302108
Recovery of Online Redo Log: Thread 1 Group 9 Seq 1082899 Reading mem 0
  Mem# 0: /oracledb/oradata/orcl/redo09.log
Block recovery completed at rba 1082899.5.16, scn 17.1444858077
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_78465.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_78465.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 78465): terminating the instance due to error 600
Instance terminated by USER, pid = 78465
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (78465) as a result of ORA-1092
Mon Sep 22 09:00:08 2025
ORA-1092 : opitsk aborting process

客户再次尝试几次之后,最后直接无法正常mount库

Mon Sep 22 19:14:14 2025
ALTER DATABASE   MOUNT
USER (ospid: 11679): terminating the instance
System state dump requested by (instance=1, osid=11679), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_diag_11615.trc
Dumping diagnostic data in directory=[cdmp_20250922191419],requested by(instance=1,osid=11679)
   ,summary=[abnormal instance termination].
Instance terminated by USER, pid = 11679

这个故障处理起来相对比较简单:
1)根据当前损坏的的ctl以及操作系统的控制数据文件,redo以及alert日志中数据库字符集信息,构造重建控制文件语句,对该库进行rectl
2)由于ORA-600 4194错误,明显指向undo异常,通过对异常undo的回滚段处理,打开数据库

服务器断电一起的一例ORA-01207故障处理

Posted on 2025 年 09 月 06 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：服务器断电一起的一例ORA-01207故障处理

有一个客户反馈服务器异常断电,导致数据库无法正常启动(启动报ORA-01207: file is more recent than control file – old control file 错误)

SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area 5.4784E+10 bytes
Fixed Size                  2188768 bytes
Variable Size            4.2010E+10 bytes
Database Buffers         1.2751E+10 bytes
Redo Buffers               20566016 bytes
数据库装载完毕。
SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 6 failed verification check
ORA-01110: data file 6: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\HSHIS1'
ORA-01207: file is more recent than control file - old control file

尝试拷贝alert日志到本地进行分析,发现无法正常拷贝

怀疑文件系统或者底层磁盘有损坏,进一步检查系统日志,确认发现大量ntfs层面异常提示（而且多个盘都有）

对于这样的情况,先对数据文件进行了备份,运气还不错,文件全部备份成功,并通过Oracle Database Recovery Check 脚本进行检查当前故障库状态,确认很多文件的checkpoint点相差很远

对于这样的一个非归档数据库,肯定无法正常打开,只能通过屏蔽一致性尝试强制打开库,结果在打开过程中出现ORA-600 4194错误

SQL> alter database open ;
alter database open 
*
第 1 行出现错误:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [],
[], [], [], []
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [],
[], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [],
[], [], [], []
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [],
[], [], [], []
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [],
[], [], [], []
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [],
[], [], [], []
进程 ID: 7820
会话 ID: 177 序列号: 147

解决异常undo,数据库正常open成功,但是在数据库导出数据过程中报ORA-600 2662和ORA-600 2663错误(这种情况一般比较少见,大部分ORA-600 2662/2663都是在open过程中,这种库已经open成功,后续查询报该错误的比较少见,但是原理上有可能[启动的时候没有读到这些block,后面放到这些表的block scn大于文件头scn,所以报该错误])

Fri Sep 05 01:04:12 2025
QMNC started with pid=54, OS id=7708 
Completed: alter database open
Fri Sep 05 01:04:13 2025
Errors in file d:\app\administrator\diag\rdbms\his\his\trace\his_m001_8624.trc  (incident=10058):
ORA-00600: internal error code, arguments: [2662], [2], [1384717872], [2], [1385084764], [41943042],
Incident details in: d:\app\administrator\diag\rdbms\his\his\incident\incdir_10058\his_m001_8624_i10058.trc
Fri Sep 05 01:04:13 2025
Starting background process CJQ0
Fri Sep 05 01:04:13 2025
CJQ0 started with pid=62, OS id=6852 
Fri Sep 05 01:04:13 2025
db_recovery_file_dest_size of 3912 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Errors in file d:\app\administrator\diag\rdbms\his\his\trace\his_ora_9220.trc  (incident=10084):
ORA-00600: 内部错误代码, 参数: [2662], [2], [1384717989], [2], [1385446402], [37802453], [], [], [],
Fri Sep 05 01:04:18 2025
Errors in file d:\app\administrator\diag\rdbms\his\his\trace\his_j017_11248.trc  (incident=10059):
ORA-00600: internal error code, arguments: [2662], [2], [1384717990], [2], [1385446402], [37802453],
Errors in file d:\app\administrator\diag\rdbms\his\his\trace\his_j017_11248.trc:
ORA-00600: internal error code, arguments: [2662], [2], [1384717990], [2], [1385446402], [37802453],
ORA-06512: at "HOSPITAL.RPT_CASE", line 5684
ORA-06512: at line 3
Errors in file d:\app\administrator\diag\rdbms\his\his\trace\his_ora_9220.trc  (incident=10085):
ORA-00600: 内部错误代码, 参数: [2662], [2], [1384717990], [2], [1385446402], [37802453], [], [], [],

这个问题相对比较简单,通过Patch SCN小工具修改正确的Oracle SCN即可

ORA-600 kcratr_nab_less_than_odr和ORA-600 4194故障处理

Posted on 2025 年 08 月 08 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-600 kcratr_nab_less_than_odr和ORA-600 4194故障处理

客户一个11.2.0.1的库由于断电导致启动的时候报ORA-600 kcratr_nab_less_than_odr错误

Wed Aug 06 15:55:07 2025
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 27 processes
Started redo scan
Completed redo scan
 read 11787 KB redo, 4129 data blocks need recovery
Errors in file d:\app\xff\diag\rdbms\primary\orcl\trace\orcl_ora_3520.trc  (incident=2624321):
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [111988], [42537], [43285], [], [], [], [], [], [], []
Incident details in: d:\app\xff\diag\rdbms\primary\orcl\incident\incdir_2624321\orcl_ora_3520_i2624321.trc
Aborting crash recovery due to error 600
Errors in file d:\app\xff\diag\rdbms\primary\orcl\trace\orcl_ora_3520.trc:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [111988], [42537], [43285], [], [], [], [], [], [], []
Errors in file d:\app\xff\diag\rdbms\primary\orcl\trace\orcl_ora_3520.trc:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [111988], [42537], [43285], [], [], [], [], [], [], []
ORA-600 signalled during: ALTER DATABASE OPEN...

类似的ORA-600 kcratr_nab_less_than_odr故障处理不少,但是应用redo完成之后,再open报错的不多:
12c启动报kcratr_nab_less_than_odr
又一例ORA-600 kcratr_nab_less_than_odr
ORA-600 kcratr_nab_less_than_odr故障解决
 差点被误操作的ORA-600 kcratr_nab_less_than_odr故障
 11.2.0.4库中遇到ORA-600 kcratr_nab_less_than_odr报错
但是这个客户比较不幸,经过一系列尝试打开数据库,但是报ORA-600 4194错误

Wed Aug 06 17:09:33 2025
ALTER DATABASE RECOVER  database until cancel using backup controlfile  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 28 slaves
ORA-279 signalled during: ALTER DATABASE RECOVER  database until cancel using backup controlfile  ...
Wed Aug 06 17:10:17 2025
ALTER DATABASE RECOVER    LOGFILE 'D:\app\xff\oradata\orcl\REDO05.LOG'  
Media Recovery Log D:\app\xff\oradata\orcl\REDO05.LOG
Wed Aug 06 17:10:17 2025
Incomplete recovery applied all redo ever generated.
Recovery completed through change 2118813100 time 08/06/2025 15:02:15
Completed: ALTER DATABASE RECOVER    LOGFILE 'D:\app\xff\oradata\orcl\REDO05.LOG'  
Wed Aug 06 17:14:05 2025
alter database open resetlogs
Archived Log entry 188143 added for thread 1 sequence 111985 ID 0x634438e6 dest 1:
Archived Log entry 188144 added for thread 1 sequence 111986 ID 0x634438e6 dest 1:
Archived Log entry 188145 added for thread 1 sequence 111987 ID 0x634438e6 dest 1:
Archived Log entry 188146 added for thread 1 sequence 111984 ID 0x634438e6 dest 1:
Archived Log entry 188147 added for thread 1 sequence 111988 ID 0x634438e6 dest 1:
RESETLOGS after complete recovery through change 2118813100
Resetting resetlogs activation ID 1665415398 (0x634438e6)
Wed Aug 06 17:15:03 2025
Archived Log entry 188148 added for thread 1 sequence 1 ID 0x677cfac1 dest 1:
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
No Resource Manager plan active
Errors in file d:\app\xff\diag\rdbms\primary\orcl\trace\orcl_smon_2428.trc  (incident=3264274):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\app\xff\diag\rdbms\primary\orcl\incident\incdir_3264274\orcl_smon_2428_i3264274.trc
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Aug 06 17:15:06 2025
QMNC started with pid=34, OS id=3052 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open resetlogs

后续由于该undo异常报ORA-01595和ORA-600 4194等错误导致数据库crash

Wed Aug 06 17:15:49 2025
Doing block recovery for file 3 block 888
Resuming block recovery (PMON) for file 3 block 888
Block recovery from logseq 2, block 316 to scn 2118813732
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: D:\APP\xff\ORADATA\ORCL\REDO02.LOG
Block recovery stopped at EOT rba 2.447.16
Block recovery completed at rba 2.447.16, scn 0.2118813732
Doing block recovery for file 3 block 1960
Resuming block recovery (PMON) for file 3 block 1960
Block recovery from logseq 2, block 316 to scn 2118813622
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: D:\APP\xff\ORADATA\ORCL\REDO02.LOG
Block recovery completed at rba 2.317.16, scn 0.2118813626
Errors in file d:\app\xff\diag\rdbms\primary\orcl\trace\orcl_smon_2428.trc:
ORA-01595: error freeing extent (2) of rollback segment (27))
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

接手这个库之后,处理工作相对比较简单,直接对异常undo进行重建即可

Thu Aug 07 09:35:08 2025
create undo tablespace undotbs2 datafile 'D:\APP\xff\ORADATA\ORCL\undotbs02.dbf' size 128M autoextend on
Completed: create undo tablespace undotbs2 datafile 'D:\APP\xff\ORADATA\ORCL\undotbs02.dbf' size 128M autoextend on
Thu Aug 07 09:37:53 2025
drop tablespace UNDOTBS1 including contents and datafiles
Deleted file D:\APP\xff\ORADATA\ORCL\UNDOTBS01.DBF
Completed: drop tablespace UNDOTBS1 including contents and datafiles

然后数据库报大量的ORA-600 kdsgrp1错误

Thu Aug 07 10:03:53 2025
Errors in file d:\app\xff\diag\rdbms\primary\orcl\trace\orcl_ora_6156.trc  (incident=5121829):
ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
Thu Aug 07 10:04:00 2025
Errors in file d:\app\xff\diag\rdbms\primary\orcl\trace\orcl_ora_4564.trc  (incident=5121501):
ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
Thu Aug 07 10:04:06 2025
Errors in file d:\app\xff\diag\rdbms\primary\orcl\trace\orcl_ora_9160.trc  (incident=5121837):
ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
Thu Aug 07 10:04:16 2025
Errors in file d:\app\xff\diag\rdbms\primary\orcl\trace\orcl_ora_5868.trc  (incident=5121821):
ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
Thu Aug 07 10:04:28 2025
Errors in file d:\app\xff\diag\rdbms\primary\orcl\trace\orcl_ora_4448.trc  (incident=5122077):
ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []

* kdsgrp1-1: *************************************************
            row 0x013a9ae9.0 continuation at
            0x013a9ae9.0 file# 4 block# 3840745 slot 0 not found
KDSTABN_GET: 0 ..... ntab: 1
curSlot: 0 ..... nrows: 2
kdsgrp - dump CR block dba=0x013a9ae9
Block header dump:  0x013a9ae9
 Object id on Block? Y
 seg/obj: 0x31dd9  csc: 0x00.7dc0f246  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x13a9082 ver: 0x01 opc: 0
     inc: 2  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

通过分析是由于index和表记录不匹配导致,对index进行重新rebuild完成本次修复任务.

差点被误操作的ORA-600 kcratr_nab_less_than_odr故障

Posted on 2025 年 03 月 19 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：差点被误操作的ORA-600 kcratr_nab_less_than_odr故障

晚上接到一个客户电话,数据库无法启动,咨询我的Oracle Recovery Tools工具的授权问题,我远程登录客户的环境进行查看故障,发现客户进行了一下操作导致最后open报ORA-01152错误

C:\Users\Administrator>sqlplus sys/sys as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 18 22:43:24 2025

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup mount
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2176168 bytes
Variable Size             973081432 bytes
Database Buffers          620756992 bytes
Redo Buffers                7397376 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[27890], [1589], [1649], [], [], [], [], [], [], []

SQL> recover database;
ORA-00283: 恢复会话因错误而取消
ORA-00264: 不要求恢复


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[27890], [1589], [1649], [], [], [], [], [], [], []


SQL> recover database until cancel;
ORA-10879: error signaled in parallel recovery slave
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01152: 文件 1 没有从过旧的备份中还原
ORA-01110: 数据文件 1: 'D:\APP\ADMINISTRATOR\ORADATA\orcl\SYSTEM01.DBF'

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\APP\ADMINISTRATOR\ORADATA\orcl\SYSTEM01.DBF'


SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;
ORA-00279: change 17384974762395 generated at 03/18/2025 18:30:34 needed for
thread 1
ORA-00289: suggestion :
D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\orcl\ARCHIVELOG\2025_03_19\O1_MF_1_27
890_%U_.ARC
ORA-00280: change 17384974762395 for thread 1 is in sequence #27890


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D\APP\ADMINISTRATOR\ORADATA\orcl\RED001.LOG
ORA-00308: 无法打开归档日志 'D\APP\ADMINISTRATOR\ORADATA\orcl\RED001.LOG'
ORA-27041: 无法打开文件
OSD-04002: 无法打开文件
O/S-Error: (OS 3) 系统找不到指定的路径。


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:\app\Administrator\oradata\orcl\RED001.LOG
ORA-00308: 无法打开归档日志 'D:\app\Administrator\oradata\orcl\RED001.LOG'
ORA-27041: 无法打开文件
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。

最初数据库启动报ORA-600 kcratr_nab_less_than_odr错误(这个是一个非常典型的错误,早期写过处理方法:ORA-600 kcratr_nab_less_than_odr故障解决)，客户处理故障思路不太清晰,使用了recover database until cancel和alter database open resetlogs等不当操作,导致数据库没有open成功.然后希望使用我的Oracle Recovery Tools小工具进行修复,但是根据我的判断,这个故障还用不上该工具,直接可以open库.对其进行ctl重建并open库

SQL> alter database backup controlfile to trace as 'd:/ctl.txt';

Database altered.

SQL> create pfile='d:/pfile.txt' from spfile;

File created.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2176168 bytes
Variable Size             973081432 bytes
Database Buffers          620756992 bytes
Redo Buffers                7397376 bytes
SQL> @d:/xifenfei/rectl.sql

Control file created.

SQL>
SQL>
SQL> recover database;
Media recovery complete.

SQL> alter database open;

Database altered.

在open之后,数据库报ORA-600 4194错误

Wed Mar 19 01:51:12 2025
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 23 processes
Started redo scan
Completed redo scan
 read 793 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 27890, block 2, scn 17384974741793
Recovery of Online Redo Log: Thread 1 Group 4 Seq 27890 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\orcl\REDO04.LOG
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 27890, block 1589, scn 17384974762395
 0 data blocks read, 0 data blocks written, 793 redo k-bytes read
Wed Mar 19 01:51:14 2025
Thread 1 advanced to log sequence 27891 (thread open)
Thread 1 opened at log sequence 27891
  Current log# 2 seq# 27891 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\orcl\REDO02.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Mar 19 01:51:14 2025
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'TEMP1' #11 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
 
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
 
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
           Empty temporary tablespace: TEMP1
*********************************************************************
Database Characterset is ZHS16GBK
No Resource Manager plan active
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_smon_4084.trc  (incident=158542):
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_158542\orcl_smon_4084_i158542.trc
Wed Mar 19 01:51:26 2025
Trace dumping is performing id=[cdmp_20250319015126]
Wed Mar 19 01:51:31 2025
Sweep [inc][158542]: completed
Sweep [inc2][158542]: completed
Wed Mar 19 01:51:35 2025
Doing block recovery for file 3 block 1415
Resuming block recovery (PMON) for file 3 block 1415
Block recovery from logseq 27891, block 86 to scn 17384974782720
Recovery of Online Redo Log: Thread 1 Group 2 Seq 27891 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\orcl\REDO02.LOG
Block recovery stopped at EOT rba 27891.87.16
Block recovery completed at rba 27891.87.16, scn 4047.3242135803
Doing block recovery for file 3 block 264
Resuming block recovery (PMON) for file 3 block 264
Block recovery from logseq 27891, block 86 to scn 17384974782714
Recovery of Online Redo Log: Thread 1 Group 2 Seq 27891 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\orcl\REDO02.LOG
Block recovery completed at rba 27891.87.16, scn 4047.3242135803
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_smon_4084.trc:
ORA-01595: 释放区 (2) 回退段 (12) 时出错
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
Wed Mar 19 01:51:36 2025
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_5680.trc  (incident=158590):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_158590\orcl_ora_5680_i158590.trc

数据库open成功,但是后台报ORA-01595/ORA-600 4194错误,这个问题比较常见,直接处理异常undo即可恢复.

ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME

Posted on 2024 年 09 月 24 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME

一个10g的库应用访问报ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME

通过分析alert日志,确认是数据库启动报ORA-600 4194错误

Mon Sep 23 16:12:42 2024
SMON: enabling cache recovery
Mon Sep 23 16:12:43 2024
Successfully onlined Undo Tablespace 1.
Mon Sep 23 16:12:43 2024
SMON: enabling tx recovery
Mon Sep 23 16:12:43 2024
Database Characterset is ZHS16GBK
Mon Sep 23 16:12:43 2024
Errors in file d:\oracle\product\10.2.0\admin\xifenfei\udump\xifenfei_ora_7832.trc:
ORA-00600: 内部错误代码, 参数: [4194], [66], [50], [], [], [], [], []

DEBUG: Replaying xcb 0xae312888, pmd 0x9058f4d4 for failed op 8
Doing block recovery for file 2 block 5547
No block recovery was needed
Mon Sep 23 16:13:31 2024
Errors in file d:\oracle\product\10.2.0\admin\xifenfei\udump\xifenfei_ora_7832.trc:
ORA-00600: 内部错误代码, 参数: [4194], [66], [50], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [66], [50], [], [], [], [], []

Mon Sep 23 16:13:32 2024
DEBUG: Replaying xcb 0xae312888, pmd 0x9058f4d4 for failed op 8
Mon Sep 23 16:13:32 2024
Errors in file d:\oracle\product\10.2.0\admin\xifenfei\udump\xifenfei_ora_7832.trc:
ORA-00600: 内部错误代码, 参数: [4194], [66], [50], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [66], [50], [], [], [], [], []

Doing block recovery for file 2 block 5547
No block recovery was needed
Mon Sep 23 16:13:33 2024
Errors in file d:\oracle\product\10.2.0\admin\xifenfei\udump\xifenfei_ora_7832.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4194], [66], [50], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [66], [50], [], [], [], [], []

Mon Sep 23 16:14:18 2024
Errors in file d:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_smon_5880.trc:
ORA-00600: internal error code, arguments: [4194], [66], [50], [], [], [], [], []

Mon Sep 23 16:14:19 2024
DEBUG: Replaying xcb 0xae312888, pmd 0x9058f4d4 for failed op 8
Mon Sep 23 16:14:19 2024
Non-fatal internal error happenned while SMON was doing shrinking of rollback segments.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
Mon Sep 23 16:14:19 2024
Doing block recovery for file 2 block 5547
No block recovery was needed
Mon Sep 23 16:15:06 2024
Errors in file d:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_pmon_6952.trc:
ORA-00600: internal error code, arguments: [4194], [66], [50], [], [], [], [], []

Mon Sep 23 16:15:06 2024
Errors in file d:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_pmon_6952.trc:
ORA-00600: internal error code, arguments: [4194], [66], [50], [], [], [], [], []

Mon Sep 23 16:15:06 2024
PMON: terminating instance due to error 472
Mon Sep 23 16:15:07 2024
Errors in file d:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_psp0_2104.trc:
ORA-00472: PMON  process terminated with error

Mon Sep 23 16:15:07 2024
Errors in file d:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_lgwr_3200.trc:
ORA-00472: PMON  process terminated with error

Mon Sep 23 16:15:07 2024
Errors in file d:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_dbw1_448.trc:
ORA-00472: PMON  process terminated with error

Mon Sep 23 16:15:07 2024
Errors in file d:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_dbw0_7436.trc:
ORA-00472: PMON  process terminated with error

Mon Sep 23 16:15:07 2024
Errors in file d:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_mman_1704.trc:
ORA-00472: PMON  process terminated with error

Mon Sep 23 16:15:07 2024
Errors in file d:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_dbw2_5072.trc:
ORA-00472: PMON  process terminated with error

Mon Sep 23 16:15:07 2024
Errors in file d:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_ckpt_6628.trc:
ORA-00472: PMON  process terminated with error

Mon Sep 23 16:15:07 2024
Errors in file d:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_reco_7924.trc:
ORA-00472: PMON  process terminated with error

Mon Sep 23 16:15:07 2024
Errors in file d:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_smon_5880.trc:
ORA-00472: PMON  process terminated with error

Instance terminated by PMON, pid = 6952

这个比较简单一般就是undo异常，对undo设置为人工管理，然后重建undo完成本次恢复任务