Category Archives: Oracle

system表空间丢失部分文件恢复

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：system表空间丢失部分文件恢复

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有客户因为system表空间有一个数据文件放在其他位置,当时没有正常拷贝出来(备份了oradata路径下面文件,遗漏了一个system文件),尝试启动库报ORA-01157 ORA-01147等错误

[oracle@xifenfei check_db]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 5 21:13:28 2025

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> recover datafile 1;
Media recovery complete. 
SQL> recover datafile 2,3,4,5,6,7,8,9,10;   
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11:
'/u01/app/oracle/product/11.2.0.4/db_1/dbs/path_to_datafile.dbf'

SQL> alter database datafile 11 offline drop;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 11 is offline
ORA-01110: data file 11:
'/u01/app/oracle/product/11.2.0.4/db_1/dbs/path_to_datafile.dbf'

alert日志报错信息

Sun Oct 05 22:35:01 2025
alter database open
Sun Oct 05 22:35:01 2025
Errors in file /data/app/oracle/diag/rdbms/mtxdb1/mtxdb1/trace/mtxdb1_dbw0_5946.trc:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0.4/db_1/dbs/path_to_datafile.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /data/app/oracle/diag/rdbms/mtxdb1/mtxdb1/trace/mtxdb1_ora_11264.trc:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0.4/db_1/dbs/path_to_datafile.dbf'
ORA-1157 signalled during: alter database open...
Sun Oct 05 22:35:25 2025
alter database datafile 11 offline 
ORA-1145 signalled during: alter database datafile 11 offline ...
alter database datafile 11 offline drop
Completed: alter database datafile 11 offline drop
alter database open
Errors in file /data/app/oracle/diag/rdbms/mtxdb1/mtxdb1/trace/mtxdb1_ora_11264.trc:
ORA-01147: SYSTEM tablespace file 11 is offline
ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0.4/db_1/dbs/path_to_datafile.dbf'
ORA-1147 signalled during: alter database open...

由于11号文件是system表空间的一个数据文件,对于这种数据文件丢失无法offline该数据文件,然后open库(也就是说在open库的时候,system表空间的数据文件必须全部online,如果有部分文件offline就会报ORA-01147).对于这样的情况,以前有过类似恢复经历:bbed打开丢失部分system数据文件库,这次的编写了一个m_scn程序实现快速处理

[oracle@xifenfei  tmp]$ cat 1.txt
1@/data/app/oracle/oradata/mtxdb1/system01.dbf
11@/tmp/11.dbf
[oracle@xifenfei  tmp]$ ./m_scn 1.txt

-------------Is processing datafile:/tmp/11.dbf-------------
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.000835728 s, 1.3 GB/s

[oracle@xifenfei tmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 8 11:27:32 2025

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> set numw 16
SQL> col CHECKPOINT_TIME for a40
SQL> set lines 150
SQL> set pages 1000
SQL> SELECT status,
  2  to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time,FUZZY,checkpoint_change#,
  3  count(*) ROW_NUM
  4  FROM v$datafile_header
  5  GROUP BY status, checkpoint_change#, to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss'),fuzzy
  6  ORDER BY status, checkpoint_change#, checkpoint_time;

STATUS  CHECKPOINT_TIME                          FUZ CHECKPOINT_CHANGE#          ROW_NUM
------- ---------------------------------------- --- ------------------ ----------------
OFFLINE 2025-10-02 06:50:06                      NO      17328662858685                1
ONLINE  2025-10-02 06:50:06                      NO      17328662858685               10


SQL> alter database datafile 11 online;

Database altered.

然后重建ctl,并尝试打开库
ctl_re

然后查询11号文件中涉及的对象情况

SQL> select distinct owner,segment_name,segment_type from dba_extents where file_id=11;

OWNER                          SEGMENT_NAME                           SEGMENT_TYPE
------------------------------ -------------------------------------- ------------------
SYS                            SYSTEM                                 ROLLBACK
SYS                            I_COL1                                 INDEX
SYS                            AUD$                                   TABLE

SQL> select owner,segment_name from dba_segments where HEADER_FILE=11;

no rows selected

证明丢失的11号文件（system表空间文件）,涉及的对象较少,而且不涉及核心字典,比如tab$,obj$,col$等非常核心对象,评估理论上应该不涉业务数据丢失,尝试直接expdp导出数据,但是很不幸,报ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018]错误

. . exported "XFF020"."OTHERBILLDETAIL_DEL"              6.405 MB  126048 rows
. . exported "XFF020"."POSSOLDOUT"                       7.784 MB  281413 rows
ORA-31693: Table data object "XFF020"."MATERIELTRAN" failed to load/unload and is being skipped due to error:
ORA-39068: invalid master table data in row with PROCESS_ORDER=159:1000001
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPF$FILE", line 3720
ORA-06512: at line 1
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_DATA [TABLE_DATA:"XFF020"."MATERIELTRAN"] 
UPDATE "SYS"."SYS_EXPORT_FULL_01" SET processing_state = :1, processing_status = :2
    WHERE process_order = :3 AND duplicate = 0
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPW$WORKER", line 7866
ORA-31693: Table data object "XFF020"."MATERIELTRAN" failed to load/unload and is being skipped due to error:
ORA-39068: invalid master table data in row with PROCESS_ORDER=159:1000001
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPF$FILE", line 3720
ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 9721

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xef2fc508     21979  package body SYS.KUPW$WORKER
0xef2fc508      9742  package body SYS.KUPW$WORKER
0xef2fc508      3437  package body SYS.KUPW$WORKER
0xef2fc508     10436  package body SYS.KUPW$WORKER
0xef2fc508      1824  package body SYS.KUPW$WORKER
0xef2feb20         2  anonymous block

ORA-39097: Data Pump job encountered unexpected error -607
ORA-39065: unexpected master process exception in DISPATCH
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []

ORA-31693: Table data object "XFF020"."ANALYSEREPORT" failed to load/unload and is being skipped due to error:
ORA-39068: invalid master table data in row with PROCESS_ORDER=161:1000001
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPF$FILE", line 3720
ORA-06512: at line 1
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_DATA [TABLE_DATA:"XFF020"."ANALYSEREPORT"] 
UPDATE "SYS"."SYS_EXPORT_FULL_01" SET processing_state = :1, processing_status = :2
   WHERE process_order = :3 AND duplicate = 0
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPW$WORKER", line 7866
ORA-31693: Table data object "XFF020"."ANALYSEREPORT" failed to load/unload and is being skipped due to error:
ORA-39068: invalid master table data in row with PROCESS_ORDER=161:1000001
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPF$FILE", line 3720
ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 9721

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xef2fc508     21979  package body SYS.KUPW$WORKER
0xef2fc508      9742  package body SYS.KUPW$WORKER
0xef2fc508      3437  package body SYS.KUPW$WORKER
0xef2fc508     10436  package body SYS.KUPW$WORKER
0xef2fc508      1824  package body SYS.KUPW$WORKER
0xef2feb20         2  anonymous block

ORA-31693: Table data object "XFF020CW"."MATERIELTRAN" failed to load/unload and is being skipped due to error:
ORA-39068: invalid master table data in row with PROCESS_ORDER=160:1000001
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPF$FILE", line 3720
ORA-06512: at line 1
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_DATA [TABLE_DATA:"XFF020CW"."MATERIELTRAN"] 
UPDATE "SYS"."SYS_EXPORT_FULL_01" SET processing_state = :1, processing_status = :2
   WHERE process_order = :3 AND duplicate = 0
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPW$WORKER", line 7866
ORA-31693: Table data object "XFF020CW"."MATERIELTRAN" failed to load/unload and is being skipped due to error:
ORA-39068: invalid master table data in row with PROCESS_ORDER=160:1000001
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPF$FILE", line 3720
ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 9721

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xef2fc508     21979  package body SYS.KUPW$WORKER
0xef2fc508      9742  package body SYS.KUPW$WORKER
0xef2fc508      3437  package body SYS.KUPW$WORKER
0xef2fc508     10436  package body SYS.KUPW$WORKER
0xef2fc508      1824  package body SYS.KUPW$WORKER
0xef2feb20         2  anonymous block

Job "SYS"."SYS_EXPORT_FULL_01" stopped due to fatal error at Wed Oct 8 11:59:29 2025 elapsed 0 00:18:48

对ORA-600 kdBlkCheckError进行分析分析（11表示文件号,3表示block）,是由于导出生成的master表写入在system表空间,而system表空间中的file# 11是人工构造出来的,block 3 是位图分配信息（该信息和实际字典中存储信息不匹配）,所以导致出现该错误,对于这个问题解决方法为expdp写master表不在system表空间即可,通过该操作,顺利导出数据,完成本次恢复任务
expdp_ok

11.2.0.4升级到19c详细操作过程

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：11.2.0.4升级到19c详细操作过程

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

随着硬件生命周期,数据库等保要求等,不少客户需要把数据库从11.2.0.4版本升级到19c.对于这样的客户的核心生产库升级,一般考虑到回退情况,大部分会选择异机进行升级,提供一个完整版测试
在源库(11.2.0.4)中做升级之前检查

--触发器检查（禁用和启用脚本生成）
SELECT 'ALTER TRIGGER ' || owner || '.' || trigger_name || ' DISABLE;' AS disable_commands
FROM dba_triggers 
WHERE trigger_type IN ('BEFORE EVENT', 'AFTER EVENT') 
AND triggering_event LIKE '%DDL%'
AND status = 'ENABLED';


SELECT 'ALTER TRIGGER ' || owner || '.' || trigger_name || ' ENABLE;' AS disable_commands
FROM dba_triggers 
WHERE trigger_type IN ('BEFORE EVENT', 'AFTER EVENT') 
AND triggering_event LIKE '%DDL%'
AND status = 'ENABLED';

--收集字典统计信息
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

--检查时区信息
select * from  v$timezone_file;
select  distinct owner from  dba_tab_columns where 
DATA_TYPE='TIMESTAMP(6) WITH TIME ZONE';

--物化视图刷新检查
SELECT DISTINCT(TRUNC(last_refresh)) FROM dba_snapshot_refresh_times;
SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8; 

--无效index检查
set linesize 400
select owner,index_name from dba_indexes where status in ('INVALID','UNUSABLE') ;
select index_owner,index_name,partition_name,status from dba_ind_partitions where status in ('INVALID','UNUSABLE') ;

--清空审计表和所属表空间检查
truncate table aud$;
SELECT owner,tablespace_name FROM dba_tables WHERE table_name='AUD$';

--清理19c中无法升级组件
alter session set nls_language='American';
SET ECHO ON;
SET SERVEROUTPUT ON;
@olspreupgrade.sql
@emremove.sql
@catnoamd.sql
@catnoexf.sql
@$ORACLE_HOME/apex/apxremov.sql
@?/rdbms/admin/utlprp.sql 32
select owner,object_type from dba_objects where object_name =upper('htmldb_system');
drop package htmldb_system;
drop public synonym htmldb_system;

--检查数据文件不处于备份状态
SELECT * FROM v$backup WHERE status != 'NOT ACTIVE'; 

--清空回收站
PURGE DBA_RECYCLEBIN;

--检查 SYS 及 SYSTEM默认表空间
SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM');

--检查组件状态
set pagesize 500 
set linesize 400
col comp_name for a40 
select substr(comp_name,1,40) comp_name, status, substr (version,1,10) version from dba_registry order by comp_name;

--记录无效对象
create table system.invalid_obj_10g_beforeup tablespace sysaux as select substr(object_name,1,40) object_name,
substr(owner,1,15) owner, object_type from  dba_objects where status='INVALID' order by owner,object_type;

--禁用Block Change Tracking
SELECT filename, status, bytes FROM v$block_change_tracking;
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

--执行 preupgrade 脚本
$ORACLE_HOME/jdk/bin/java -jar /u01/xff/preupgrade.jar FILE TEXT DIR /tmp
@/tmp/preupgrade_fixups.sql

--执行dbupgdiag.sql脚本
alter session set nls_language='American';
SET ECHO ON;
SET SERVEROUTPUT ON;
@dbupgdiag.sql

rman备份还原数据库

--在11.2.0.4库备份操作
backup   filesperset = 5 as compressed backupset  database format
  '/u01/rmanback/full_%T_%U.rman';
sql 'alter system archive log current';
sql 'alter system archive log current';
Backup filesperset = 10 as compressed backupset archivelog all format 
  '/u01/rmanback/arch_%T_%U.rman' not backed up delete input;
backup  format '/u01/rmanback/ctl_%T_%U.rman' current controlfile;

--在19c库还原操作
SQL>startup nomount pfile='/tmp/pfile'
RMAN> restore controlfile from '/u01/rmanback/ctl_20251003_0a459rsp_1_1.rman';
RMAN> alter database mount;
RMAN>  catalog start with '/u01/orabak/';
RMAN> restore database;
RMAN> recover database;   --可以增量追加归档
SQL> alter database open resetlogs upgrade;

正式升级操作（19c环境）

startup pfile='/u01/xff/pfile.upgrade'  mount;
alter database open upgrade;
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
cd $ORACLE_HOME/bin
./dbupgrade

sqlplus / as sysdba
startup
@?/rdbms/admin/utlusts.sql TEXT
@?/rdbms/admin/utlrp.sql 32
@?/rdbms/admin/utlusts.sql TEXT

set pagesize500 
set linesize 400
col comp_name for a40 
select substr(comp_name,1,40) comp_name, status, substr (version,1,10) version from dba_registry order by comp_name;

升级完成后操作

--执行postupgrade_fixups
@/tmp/postupgrade_fixups.sql

--收集字典统计信息
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;


--升级时区
cd $ORACLE_HOME/rdbms/admin
@utltz_countstats.sql
@utltz_countstar.sql
@?/rdbms/admin/utltz_upg_check.sql
@?/rdbms/admin/utltz_upg_apply.sql

主要日志

--升级操作日志
[oracle@oracledb:/u01/app/oracle/product/19c/db/bin]$ ./dbupgrade

Argument list for [/u01/app/oracle/product/19c/db/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in                       c = 0
Do not run in                C = 0
Input Directory              d = 0
Echo OFF                     e = 1
Simulate                     E = 0
Forced cleanup               F = 0
Log Id                       i = 0
Child Process                I = 0
Log Dir                      l = 0
Priority List Name           L = 0
Upgrade Mode active          M = 0
SQL Process Count            n = 0
SQL PDB Process Count        N = 0
Open Mode Normal             o = 0
Start Phase                  p = 0
End Phase                    P = 0
Reverse Order                r = 0
AutoUpgrade Resume           R = 0
Script                       s = 0
Serial Run                   S = 0
RO User Tablespaces          T = 0
Display Phases               y = 0
Debug catcon.pm              z = 0
Debug catctl.pl              Z = 0

catctl.pl VERSION: [19.0.0.0.0]
           STATUS: [Production]
            BUILD: [RDBMS_19.16.0.0.0DBRU_LINUX.X64_220701]


/u01/app/oracle/product/19c/db/rdbms/admin/orahome = [/u01/app/oracle/product/19c/db]
/u01/app/oracle/product/19c/db/bin/orabasehome = [/u01/app/oracle/product/19c/db]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19c/db]

Analyzing file /u01/app/oracle/product/19c/db/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20251003103800]

catcon::set_log_file_base_path: ALL catcon-related output will be written to
  [/tmp/cfgtoollogs/upgrade20251003103800/catupgrd_catcon_15770.lst]

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20251003103800/catupgrd*.log] 
  files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20251003103800/catupgrd_*.lst]
   files for spool files, if any


Number of Cpus        = 4
Database Name         = orcl
DataBase Version      = 11.2.0.4.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to 
  [/u01/xxx/upgrade20251003103801/catupgrd_catcon_15770.lst]

catcon::set_log_file_base_path: catcon: See [/u01/xxx/upgrade20251003103801/catupgrd*.log]
   files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/xxx/upgrade20251003103801/catupgrd_*.lst] 
   files for spool files, if any


Log file directory = [/u01/xxx/upgrade20251003103801]

Parallel SQL Process Count            = 4
Components in [orcl]
    Installed [APS CATALOG CATJAVA CATPROC CONTEXT JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [APEX DV EM MGW ODM OLS RAC WK]
DataBase Version      = 11.2.0.4.0

------------------------------------------------------
Phases [0-107]         Start Time:[2025_10_03 10:38:01]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [orcl] Files:1    Time: 30s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [orcl] Files:5    Time: 18s
Restart  Phase #:2    [orcl] Files:1    Time: 0s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [orcl] Files:19   Time: 9s
Restart  Phase #:4    [orcl] Files:1    Time: 0s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [orcl] Files:7    Time: 7s
*****************   Catproc Start   ****************
Serial   Phase #:6    [orcl] Files:1    Time: 5s
*****************   Catproc Types   ****************
Serial   Phase #:7    [orcl] Files:2    Time: 4s
Restart  Phase #:8    [orcl] Files:1    Time: 0s
****************   Catproc Tables   ****************
Parallel Phase #:9    [orcl] Files:70   Time: 9s
Restart  Phase #:10   [orcl] Files:1    Time: 0s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [orcl] Files:1    Time: 25s
Restart  Phase #:12   [orcl] Files:1    Time: 0s
**************   Catproc Procedures   **************
Parallel Phase #:13   [orcl] Files:95   Time: 2s
Restart  Phase #:14   [orcl] Files:1    Time: 0s
Parallel Phase #:15   [orcl] Files:122  Time: 4s
Restart  Phase #:16   [orcl] Files:1    Time: 0s
Serial   Phase #:17   [orcl] Files:25   Time: 1s
Restart  Phase #:18   [orcl] Files:1    Time: 0s
*****************   Catproc Views   ****************
Parallel Phase #:19   [orcl] Files:32   Time: 6s
Restart  Phase #:20   [orcl] Files:1    Time: 0s
Serial   Phase #:21   [orcl] Files:3    Time: 4s
Restart  Phase #:22   [orcl] Files:1    Time: 1s
Parallel Phase #:23   [orcl] Files:25   Time: 79s
Restart  Phase #:24   [orcl] Files:1    Time: 0s
Parallel Phase #:25   [orcl] Files:12   Time: 49s
Restart  Phase #:26   [orcl] Files:1    Time: 0s
Serial   Phase #:27   [orcl] Files:1    Time: 0s
Serial   Phase #:28   [orcl] Files:4    Time: 1s
Serial   Phase #:29   [orcl] Files:1    Time: 0s
Restart  Phase #:30   [orcl] Files:1    Time: 0s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [orcl] Files:1    Time: 0s
Restart  Phase #:32   [orcl] Files:1    Time: 0s
Serial   Phase #:34   [orcl] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [orcl] Files:297  Time: 9s
Serial   Phase #:36   [orcl] Files:1    Time: 0s
Restart  Phase #:37   [orcl] Files:1    Time: 0s
Serial   Phase #:38   [orcl] Files:10   Time: 2s
Restart  Phase #:39   [orcl] Files:1    Time: 0s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [orcl] Files:3    Time: 21s
Restart  Phase #:41   [orcl] Files:1    Time: 0s
******************   Catproc SQL   *****************
Parallel Phase #:42   [orcl] Files:13   Time: 50s
Restart  Phase #:43   [orcl] Files:1    Time: 1s
Parallel Phase #:44   [orcl] Files:11   Time: 3s
Restart  Phase #:45   [orcl] Files:1    Time: 0s
Parallel Phase #:46   [orcl] Files:3    Time: 0s
Restart  Phase #:47   [orcl] Files:1    Time: 0s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [orcl] Files:1    Time: 5s
Restart  Phase #:49   [orcl] Files:1    Time: 0s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [orcl] Files:1    Time: 8s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [orcl] Files:1    Time: 0s
Restart  Phase #:52   [orcl] Files:1    Time: 0s
**********   Upgrading Java and non-Java   *********
Serial   Phase #:53   [orcl] Files:2    Time: 161s
*****************   Upgrading XDB   ****************
Restart  Phase #:54   [orcl] Files:1    Time: 0s
Serial   Phase #:56   [orcl] Files:3    Time: 9s
Serial   Phase #:57   [orcl] Files:3    Time: 1s
Parallel Phase #:58   [orcl] Files:10   Time: 1s
Parallel Phase #:59   [orcl] Files:25   Time: 2s
Serial   Phase #:60   [orcl] Files:4    Time: 3s
Serial   Phase #:61   [orcl] Files:1    Time: 0s
Serial   Phase #:62   [orcl] Files:32   Time: 1s
Serial   Phase #:63   [orcl] Files:1    Time: 0s
Parallel Phase #:64   [orcl] Files:6    Time: 4s
Serial   Phase #:65   [orcl] Files:2    Time: 8s
Serial   Phase #:66   [orcl] Files:3    Time: 29s
****************   Upgrading ORDIM   ***************
Restart  Phase #:67   [orcl] Files:1    Time: 0s
Serial   Phase #:69   [orcl] Files:1    Time: 0s
Parallel Phase #:70   [orcl] Files:2    Time: 14s
Restart  Phase #:71   [orcl] Files:1    Time: 0s
Parallel Phase #:72   [orcl] Files:2    Time: 0s
Serial   Phase #:73   [orcl] Files:2    Time: 0s
*****************   Upgrading SDO   ****************
Restart  Phase #:74   [orcl] Files:1    Time: 0s
Serial   Phase #:76   [orcl] Files:1    Time: 16s
Serial   Phase #:77   [orcl] Files:2    Time: 0s
Restart  Phase #:78   [orcl] Files:1    Time: 0s
Serial   Phase #:79   [orcl] Files:1    Time: 8s
Restart  Phase #:80   [orcl] Files:1    Time: 0s
Parallel Phase #:81   [orcl] Files:3    Time: 10s
Restart  Phase #:82   [orcl] Files:1    Time: 0s
Serial   Phase #:83   [orcl] Files:1    Time: 2s
Restart  Phase #:84   [orcl] Files:1    Time: 0s
Serial   Phase #:85   [orcl] Files:1    Time: 4s
Restart  Phase #:86   [orcl] Files:1    Time: 0s
Parallel Phase #:87   [orcl] Files:4    Time: 28s
Restart  Phase #:88   [orcl] Files:1    Time: 0s
Serial   Phase #:89   [orcl] Files:1    Time: 0s
Restart  Phase #:90   [orcl] Files:1    Time: 0s
Serial   Phase #:91   [orcl] Files:2    Time: 4s
Restart  Phase #:92   [orcl] Files:1    Time: 0s
Serial   Phase #:93   [orcl] Files:1    Time: 0s
Restart  Phase #:94   [orcl] Files:1    Time: 1s
*******   Upgrading ODM, WK, EXF, RUL, XOQ   *******
Serial   Phase #:95   [orcl] Files:1    Time: 6s
Restart  Phase #:96   [orcl] Files:1    Time: 0s
***********   Final Component scripts    ***********
Serial   Phase #:97   [orcl] Files:1    Time: 1s
*************   Final Upgrade scripts   ************
Serial   Phase #:98   [orcl] Files:1    Time: 22s
*******************   Migration   ******************
Serial   Phase #:99   [orcl] Files:1    Time: 15s
***   End PDB Application Upgrade Pre-Shutdown   ***
Serial   Phase #:100  [orcl] Files:1    Time: 0s
Serial   Phase #:101  [orcl] Files:1    Time: 0s
Serial   Phase #:102  [orcl] Files:1    Time: 35s
*****************   Post Upgrade   *****************
Serial   Phase #:103  [orcl] Files:1    Time: 14s
****************   Summary report   ****************
Serial   Phase #:104  [orcl] Files:1    Time: 0s
***   End PDB Application Upgrade Post-Shutdown   **
Serial   Phase #:105  [orcl] Files:1    Time: 0s
Serial   Phase #:106  [orcl] Files:1    Time: 0s
Serial   Phase #:107  [orcl] Files:1     Time: 50s

------------------------------------------------------
Phases [0-107]         End Time:[2025_10_03 10:51:23]
------------------------------------------------------

Grand Total Time: 802s 

 LOG FILES: (/u01/xxx/upgrade20251003103801/catupgrd*.log)

Upgrade Summary Report Located in:
/u01/xxx/upgrade20251003103801/upg_summary.log

Grand Total Upgrade Time:    [0d:0h:13m:22s]


--组件状态
SQL>@?/rdbms/admin/utlusts.sql TEXT

Oracle Database Release 19 Post-Upgrade Status Tool    10-03-2025 10:53:4
Database Name: ORCL

Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS

Oracle Server                          UPGRADED     19.16.0.0.0  00:05:52
JServer JAVA Virtual Machine           UPGRADED     19.16.0.0.0  00:01:28
Oracle XDK                             UPGRADED     19.16.0.0.0  00:00:16
Oracle Database Java Packages          UPGRADED     19.16.0.0.0  00:00:05
OLAP Analytic Workspace                UPGRADED     19.16.0.0.0  00:00:04
Oracle Text                            UPGRADED     19.16.0.0.0  00:00:18
Oracle Workspace Manager               UPGRADED     19.16.0.0.0  00:00:26
Oracle Real Application Clusters     OPTION OFF     19.16.0.0.0  00:00:00
Oracle XML Database                    UPGRADED     19.16.0.0.0  00:00:58
Oracle Multimedia                      UPGRADED     19.16.0.0.0  00:00:13
Spatial                                UPGRADED     19.16.0.0.0  00:01:11
Oracle OLAP API                        UPGRADED     19.16.0.0.0  00:00:05
Datapatch                                                        00:00:19
Final Actions                                                    00:00:37
Post Upgrade                                                     00:00:12

Total Upgrade Time: 00:11:54

Database time zone version is 14. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.

--编译无效对象
SQL>@?/rdbms/admin/utlrp.sql 32

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2025-10-03 10:53:48
DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2025-10-03 10:55:35
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0

--编译之后组件状态正常
SQL>@?/rdbms/admin/utlusts.sql TEXT
Oracle Database Release 19 Post-Upgrade Status Tool    10-03-2025 10:55:3
Database Name: ORCL

Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS

Oracle Server                             VALID     19.16.0.0.0  00:05:52
JServer JAVA Virtual Machine              VALID     19.16.0.0.0  00:01:28
Oracle XDK                                VALID     19.16.0.0.0  00:00:16
Oracle Database Java Packages             VALID     19.16.0.0.0  00:00:05
OLAP Analytic Workspace                   VALID     19.16.0.0.0  00:00:04
Oracle Text                               VALID     19.16.0.0.0  00:00:18
Oracle Workspace Manager                  VALID     19.16.0.0.0  00:00:26
Oracle Real Application Clusters     OPTION OFF     19.16.0.0.0  00:00:00
Oracle XML Database                       VALID     19.16.0.0.0  00:00:58
Oracle Multimedia                         VALID     19.16.0.0.0  00:00:13
Spatial                                   VALID     19.16.0.0.0  00:01:11
Oracle OLAP API                           VALID     19.16.0.0.0  00:00:05
Datapatch                                                        00:00:19
Final Actions                                                    00:00:37
Post Upgrade                                                     00:00:12
Post Compile                                                     00:01:47

Total Upgrade Time: 00:13:41

Database time zone version is 14. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.

--时区升级之后结果
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
  2  FROM DATABASE_PROPERTIES
  3  WHERE PROPERTY_NAME LIKE 'DST_%'
  4  ORDER BY PROPERTY_NAME;

PROPERTY_NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
32

DST_SECONDARY_TT_VERSION
0

DST_UPGRADE_STATE
NONE


3 rows selected.

具体参考:Oracle 19c – 手动升级到 Non-CDB Oracle Database 19c 的完整核对清单 (Doc ID 2577572.1)

一次幸运的ORA-07445 kdxlin故障恢复

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：一次幸运的ORA-07445 kdxlin故障恢复

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一个oracle 19.3版本数据库异常关机之后直接启动数据库报ORA-07445 [kdxlin()+4824]相关错误

alter database open
2025-09-27T13:45:25.877298+08:00
Ping without log force is disabled:
  instance mounted in exclusive mode.
2025-09-27T13:45:25.924174+08:00
Beginning crash recovery of 1 threads
 parallel recovery started with 11 processes
 Thread 1: Recovery starting at checkpoint rba (logseq 2188 block 62125), scn 0
2025-09-27T13:45:26.049176+08:00
Started redo scan
2025-09-27T13:45:26.174177+08:00
Completed redo scan
 read 1070 KB redo, 536 data blocks need recovery
2025-09-27T13:45:26.465482+08:00
Started redo application at
 Thread 1: logseq 2188, block 62125, offset 0
2025-09-27T13:45:26.481107+08:00
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2188 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\Nxff\REDO01.LOG
2025-09-27T13:45:26.496732+08:00
Completed redo application of 0.58MB
2025-09-27T13:45:26.699860+08:00
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xC] [PC:0x7FF77DBBF2F8, kdxlin()+4824]
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_p003_4996.trc  (incident=131272):
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4824][ACCESS_VIOLATION][ADDR:0xC][PC:0x7FF77DBBF2F8][UNABLE_TO_READ][]
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\incident\incdir_131272\xff_p003_4996_i131272.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2025-09-27T13:45:30.776913+08:00
Slave encountered ORA-10388 exception during crash recovery
2025-09-27T13:45:30.776913+08:00
Slave encountered ORA-10388 exception during crash recovery
2025-09-27T13:45:30.776913+08:00
Slave encountered ORA-10388 exception during crash recovery
2025-09-27T13:45:30.776913+08:00
Slave encountered ORA-10388 exception during crash recovery
2025-09-27T13:45:30.776913+08:00
Slave encountered ORA-10388 exception during crash recovery
2025-09-27T13:45:30.792538+08:00
Slave encountered ORA-10388 exception during crash recovery
2025-09-27T13:45:30.792538+08:00
Slave encountered ORA-10388 exception during crash recovery
2025-09-27T13:45:31.792552+08:00
Slave encountered ORA-10388 exception during crash recovery
2025-09-27T13:45:32.802610+08:00
Slave encountered ORA-10388 exception during crash recovery
2025-09-27T13:45:33.818250+08:00
Slave encountered ORA-10388 exception during crash recovery
2025-09-27T13:45:33.833875+08:00
Aborting crash recovery due to slave death, attempting serial crash recovery
2025-09-27T13:45:33.833875+08:00
Beginning crash recovery of 1 threads
 Thread 1: Recovery starting at checkpoint rba (logseq 2188 block 62125), scn 0
2025-09-27T13:45:33.865126+08:00
Started redo scan
2025-09-27T13:45:33.912001+08:00
Completed redo scan
 read 1070 KB redo, 536 data blocks need recovery
2025-09-27T13:45:33.912001+08:00
Started redo application at
 Thread 1: logseq 2188, block 62125, offset 0
2025-09-27T13:45:33.927627+08:00
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2188 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\Nxff\REDO01.LOG
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xC] [PC:0x7FF77DBBF2F8, kdxlin()+4824]
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_ora_4880.trc  (incident=131240):
ORA-07445: ??????: ???? [kdxlin()+4824] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x7FF77DBBF2F8] [UNABLE_TO_READ] []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\incident\incdir_131240\xff_ora_4880_i131240.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2025-09-27T13:45:36.386177+08:00
Dumping diagnostic data in directory=[cdmp_20250927134536],requested by(instance=1,osid=4880),summary=[incident=131240]
2025-09-27T13:45:39.745686+08:00
PMON (ospid: ): terminating the instance due to ORA error 
2025-09-27T13:45:39.745686+08:00
Cause - 'Instance is being terminated due to fatal process death (pid: 33, ospid: 4880, )'
2025-09-27T13:45:39.745686+08:00
System state dump requested by (instance=1, osid=4652 (PMON)), summary=[abnormal instance termination].
System State dumped to trace file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_diag_4696.trc
2025-09-27T13:45:44.013452+08:00
Instance terminated by PMON, pid = 4652

这次运气爆棚,直接尝试recover database,然后open库成功,完成数据库恢复任务
QQ20250927-154901

检查数据库没有发现其他问题,完成本次恢复业务

ORA-704 ORA-604 ORA-1426故障分析处理

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：ORA-704 ORA-604 ORA-1426故障分析处理

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

服务器异常断电,通过分析alert日志发现现场的一些操作,数据库启动最初报ORA-00322 ORA-00312错

Tue Sep 23 20:06:52 2025
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_m000_6056.trc:
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG'
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_m000_6056.trc:
ORA-00322: log 2 of thread 1 is not current copy
ORA-00312: online log 2 thread 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG'
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_m000_6056.trc:
ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG'

通过隐含参数强制拉库报ORA-00704 ORA-00604 ORA-01426错误

Tue Sep 23 23:53:52 2025
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 444541390
Resetting resetlogs activation ID 1705450279 (0x65a71b27)
Online log D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG: Thread 1 Group 3 was previously cleared
Tue Sep 23 23:53:53 2025
Setting recovery target incarnation to 6
Tue Sep 23 23:53:53 2025
Assigning activation ID 1740400222 (0x67bc665e)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Sep 23 23:53:53 2025
SMON: enabling cache recovery
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_7792.trc:
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01426: 数字溢出
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_7792.trc:
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01426: 数字溢出
Error 704 happened during db open, shutting down database
USER (ospid: 7792): terminating the instance due to error 704
Instance terminated by USER, pid = 7792
ORA-1092 signalled during: alter database open resetlogs...

我接手故障之后尝试启动库,依旧报ORA-01092 ORA-00704 ORA-00604 ORA-01426错误无法启动库

C:\Users\XFF>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 9月 25 22:50:21 2025

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


连接到:
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> recover database;
完成介质恢复。
SQL> alter database open ;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01426: numeric overflow
进程 ID: 18152
会话 ID: 14 序列号: 1

这个报错是number数据溢出,那就可能是由于number类型的数据值不对,通过对启动过程跟踪

PARSING IN CURSOR #5 len=74 dep=1 uid=0 oct=3 lid=0 tim=1677733680660 hv=3309402135 
    ad='7fffeef07300' sqlid='5n1fs4m2n2y0r'
select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ where obj#=:1
END OF STMT
BINDS #5:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=1c1d70b8  bln=22  avl=03  flg=05
  value=425
EXEC #5:c=0,e=69,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=299250003,tim=1677733680725
WAIT #5: nam='db file sequential read' ela= 152 file#=1 block#=386 blocks=1 obj#=42 tim=1677733680892
FETCH #5:c=0,e=178,p=1,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=299250003,tim=1677733680913
FETCH #5:c=0,e=3,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=299250003,tim=1677733680931
FETCH #5:c=0,e=2,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=299250003,tim=1677733680943
CLOSE #5:c=0,e=1,dep=1,type=3,tim=1677733680957

*** 2025-09-25 21:29:42.634
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=10, mask=0x0)
----- Error Stack Dump -----
ORA-01426: 数字溢出
----- Current SQL Statement for this session (sql_id=bkdusjx00dsmc) -----
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags, i.property,i.pctfree$,i.initrans,
i.maxtrans,i.blevel,i.leafcnt,i.distkey, i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,
i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,
i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0), nvl(i.spare1,i.intcols),i.spare4,
spare2,spare6, decode(i.pctthres$,null,null, mod(trunc(i.pctthres$/256),256)) 
from ind$ i, (select enabled, min(cols) unicols, min(to_number(bitand(defer,1))) 
deferrable#, min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled)
 c where i.obj#=c.enabled(+) and i.bo#=:1 order by i.obj#

通过上述可以是在执行上述sql的时候,遭遇异常,进一步查看trace,发现在该block中有异常信息

Block header dump:  0x004000a5
 Object id on Block? Y
 seg/obj: 0x2  csc: 0x00.10daa102  itc: 2  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0006.01b.00000009  0x00c001b2.0003.09  C---    0  scn 0x0000.000021b5
0x02   0x000a.010.0003827e  0x00c00349.35ea.2f  --U-    2  fsc 0x0000.10daa11d
bdba: 0x004000a5

这个部分可以确认,异常对象是dataobj#为2(c_obj#的cluster),rdba为0x004000a5(file 1 block 165),对应的block dump中有以下信息异常

tab 3, row 4, @0x19a6
tl: 366 fb: -CH-FL-- lb: 0x0  cc: 28 cki: 4
col  0: [ 3]  c2 05 1b
col  1: [ 3]  c2 1d 1b
col  2: [44]
 80 02 c1 02 03 c2 1d 59 01 80 02 c1 03 02 c1 0b 02 c1 03 03 c2 03 38 ff 02
 c1 02 03 c2 15 33 01 80 02 c1 02 03 c2 02 3d 04 c3 04 62
col  3: [35]
 02 c1 02 02 c1 02 03 c2 17 10 07 78 7c 07 1b 0f 04 24 04 c3 04 62 23 04 c3
 04 62 23 02 c1 03 ff ff ff 02
col  4: [193]
 03 6c 00 1c 04 03 c2 05 1a 03 c2 05 1a 01 80 02 c1 02 03 c2 1d 51 01 80 02
 c1 03 02 c1 0b 02 c1 03 03 c2 03 38 ff 02 c1 02 03 c2 15 33 01 80 02 c1 02
 03 c2 02 3d 04 c3 04 5c 3d 02 c1 02 02 c1 02 03 c2 17 13 07 78 7c 07 1b 0f
 04 24 04 c3 04 62 23 04 c3 04 62 23 02 c1 03 ff ff ff 02 c1 03 6c 00 1c 02
 03 c2 05 16 03 c2 05 16 01 80 02 c1 02 03 c2 1d 11 01 80 02 c1 03 02 c1 0b
 02 c1 03 03 c2 03 38 ff 02 c1 04 02 c1 03 02 c1 02 02 c1 02 02 c1 0f 03 c2
 19 3b 02 c1 02 02 c1 02 03 c2 19 3b 07 78 7c 07 1b 17 04 23 03 c2 19 3b 03
 c2 19 3b 02 c1 03 ff ff ff 02 c1 03 6c 00 1c 01 03 c2
col  5: [ 5]  14 03 c2 05 14
col  6: [ 1]  80
col  7: [ 2]  c1 02
col  8: [ 3]  c2 1a 35
col  9: [ 1]  80
col 10: [ 2]  c1 03
col 11: [ 2]  c1 0b
col 12: [ 2]  c1 03
col 13: [ 3]  c2 03 38
col 14: *NULL*
col 15: [ 2]  c1 02
col 16: [ 3]  c2 15 33
col 17: [ 2]  c1 02
col 18: [ 2]  c1 02
col 19: [ 2]  c1 05
col 20: [ 3]  c2 08 4c
col 21: [ 2]  c1 02
col 22: [ 2]  c1 02
col 23: [ 2]  c1 05
col 24: [ 7]  78 6e 03 1e 0b 12 32
col 25: [ 3]  c2 08 4c
col 26: [ 3]  c2 08 4c
col 27: [ 2]  c1 03

这个里面显示是c_obj#这个簇中的第三个表第四行记录,通过查询正常库,确认第三个表是什么对象

SQL> select obj# from tab$ where dataobj#=2 and tab#=3;

      OBJ#
----------
        19

SQL> select name from obj$ where obj#=19;

NAME
------------------------------
IND$

通过上述可以去人tab 3为ind$对象,进一步分析ind$的表结构

SQL> desc ind$
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 OBJ#                                      NOT NULL NUMBER
 DATAOBJ#                                           NUMBER
 TS#                                       NOT NULL NUMBER
 FILE#                                     NOT NULL NUMBER
 BLOCK#                                    NOT NULL NUMBER
 BO#                                       NOT NULL NUMBER
 INDMETHOD#                                NOT NULL NUMBER
 COLS                                      NOT NULL NUMBER
 PCTFREE$                                  NOT NULL NUMBER
 INITRANS                                  NOT NULL NUMBER
 MAXTRANS                                  NOT NULL NUMBER
 PCTTHRES$                                          NUMBER
 TYPE#                                     NOT NULL NUMBER
 FLAGS                                     NOT NULL NUMBER
 PROPERTY                                  NOT NULL NUMBER
 BLEVEL                                             NUMBER
 LEAFCNT                                            NUMBER
 DISTKEY                                            NUMBER
 LBLKKEY                                            NUMBER
 DBLKKEY                                            NUMBER
 CLUFAC                                             NUMBER
 ANALYZETIME                                        DATE
 SAMPLESIZE                                         NUMBER
 ROWCNT                                             NUMBER
 INTCOLS                                   NOT NULL NUMBER
 DEGREE                                             NUMBER
 INSTANCES                                          NUMBER
 TRUNCCNT                                           NUMBER
 SPARE1                                             NUMBER
 SPARE2                                             NUMBER
 SPARE3                                             NUMBER
 SPARE4                                             VARCHAR2(1000)
 SPARE5                                             VARCHAR2(1000)
 SPARE6                                             DATE

对dump出来的block记录进行转换为实际值

SQL> select utl_raw.cast_to_number('c2051b') value from dual;

     VALUE
----------
       426

SQL> select utl_raw.cast_to_number('c21d1b') value from dual;

     VALUE
----------
      2826

SQL>  select utl_raw.cast_to_number('02c10202c10203c2171007787c071b0f042404c304622304c304622302c103ffffff02')
  2  value from dual;
 select utl_raw.cast_to_number('02c10202c10203c2171007787c071b0f042404c304622304c304622302c103ffffff02')
        *
第 1 行出现错误:
ORA-06502: PL/SQL: 数字或值错误
ORA-06512: 在 "SYS.UTL_RAW", line 388

通过上述分析证明,在ind$表的obj#为426行的记录的第三列无法转换为正常number记录,证明该值异常,从而导致数据库在执行sql_id=bkdusjx00dsmc这个sql的时候报错,从而无法open库,dbv对system文件进行检测发现有一些逻辑层面损坏

C:\Users\XFF>dbv file=H:\BaiduNetdisk\orcl\SYSTEM01.DBF

DBVERIFY: Release 11.2.0.4.0 - Production on 星期四 9月 25 19:17:17 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - 开始验证: FILE = H:\BAIDUNETDISK\ORCL\SYSTEM01.DBF
Block Checking: DBA = 4194469, Block Type = KTB-managed data block
data header at 0x5c2425c
kdbchk: row does not end within block
        table=1  slot=0
        len=923  offset=7757  dtl=8096 aclen=923
页 165 失败, 校验代码为 6103
itl[2] has higher commit scn(0x0000.000ac920) than block scn (0x0000.000a4a37)
页 21478 失败, 校验代码为 6056
Block Checking: DBA = 4263330, Block Type = KTB-managed data block
**** row 100: row length 65627 past end of block
**** row 100: row skipped so other stats may be wrong
**** row 102: key out of order
**** row 102: lock value 4 is larger than maximum itl 2
**** row 102: bad flag value 193
**** row 105: key out of order
**** row 105: lock value 4 is larger than maximum itl 2
**** row 105: bad flag value 193
**** row 106: row length 65537 past end of block
**** row 106: row skipped so other stats may be wrong
**** row 108: row length 65627 past end of block
**** row 108: row skipped so other stats may be wrong
**** row 109: key out of order
**** row 109: lock value 4 is larger than maximum itl 2
**** row 109: bad flag value 193
**** row 110: key out of order
**** row 110: lock value 239 is larger than maximum itl 2
**** row 112: row length 65627 past end of block
**** row 112: row skipped so other stats may be wrong
**** row 113: key out of order
**** row 113: lock value 4 is larger than maximum itl 2
**** row 113: bad flag value 193
**** row 114: row length 65537 past end of block
**** row 114: row skipped so other stats may be wrong
**** row 116: key out of order
**** row 116: lock value 4 is larger than maximum itl 2
**** row 116: bad flag value 193
**** key (begin=0x1a14, len=17) overlaps with another
        begin = 0x1a1c len = 11
---- end index block validation
页 69026 失败, 校验代码为 6401
Block Checking: DBA = 4263331, Block Type = KTB-managed data block
**** row 121: row offset 3123 out of valid range
**** row 122: row offset 3106 out of valid range
**** row 123: lock value 214 is larger than maximum itl 2
**** row 123: bad flag value 128
**** row 124: row length 65537 past end of block
**** row 124: row skipped so other stats may be wrong
**** row 125: key out of order
**** row 129: row offset 2979 out of valid range
**** row 130: row offset 2962 out of valid range
**** row 131: row offset 3225 out of valid range
**** row 135: committed with rsl and/or ras flag
**** row 136: key out of order
**** row 136: lock value 100 is larger than maximum itl 2
**** row 137: lock value 193 is larger than maximum itl 2
**** row 141: row length 65537 past end of block
**** row 141: row skipped so other stats may be wrong
**** row 142: rsl is 0 with ras flag
**** row 143: key out of order
**** row 143: lock value 195 is larger than maximum itl 2
**** row 147: key out of order
**** row 149: key out of order
**** row 154: key out of order
**** row 154: lock value 72 is larger than maximum itl 2
**** row 155: key out of order
**** row 155: lock value 73 is larger than maximum itl 2
**** row 157: key out of order
**** row 157: lock value 195 is larger than maximum itl 2
**** row 159: row offset 8168 out of valid range
**** row 161: row offset 8138 out of valid range
**** row 163: row length 65943 past end of block
**** row 163: row skipped so other stats may be wrong
**** actual free space = 2756 < kdxcoavs = 3155
**** key (begin=0x11fa, len=17) overlaps with another
        begin = 0x1209 len = 9
---- end index block validation
页 69027 失败, 校验代码为 6401
itl[1] has higher commit scn(0x0000.000c021f) than block scn (0x0000.0009a123)
页 72609 失败, 校验代码为 6056
Block Checking: DBA = 4273153, Block Type = KTB-managed data block
data header at 0x5ac525c
kdbchk: row locked by non-existent transaction
        table=0   slot=93
        lockid=3   ktbbhitc=2
页 78849 失败, 校验代码为 6101
Block Checking: DBA = 4273154, Block Type = KTB-managed data block
data header at 0x5ac725c
kdbchk: bad row offset slot 113 offs 473 fseo 474 dtl 8168 bhs 72
页 78850 失败, 校验代码为 6135
Block Checking: DBA = 4273155, Block Type = KTB-managed data block
data header at 0x5ac925c
kdbchk: bad row offset slot 17 offs 342 fseo 394 dtl 8168 bhs 72
页 78851 失败, 校验代码为 6135
Block Checking: DBA = 4273185, Block Type = KTB-managed data block
data header at 0x5b0525c
kdbchk: row locked by non-existent transaction
        table=0   slot=14
        lockid=4   ktbbhitc=2
页 78881 失败, 校验代码为 6101
Block Checking: DBA = 4273189, Block Type = KTB-managed data block
data header at 0x5b0d25c
kdbchk: row locked by non-existent transaction
        table=0   slot=0
        lockid=2   ktbbhitc=2
页 78885 失败, 校验代码为 6101
Block Checking: DBA = 4273192, Block Type = KTB-managed data block
data header at 0x5b1325c
kdbchk: bad row offset slot 62 offs 569 fseo 1122 dtl 8168 bhs 72
页 78888 失败, 校验代码为 6135
Block Checking: DBA = 4282166, Block Type = KTB-managed data block
**** row 0: row offset 1457 out of valid range
**** row 1: row offset 1440 out of valid range
**** row 2: row offset 1423 out of valid range
**** row 3: row offset 1406 out of valid range
**** row 4: row offset 1389 out of valid range
**** row 5: row offset 1372 out of valid range
**** row 6: row offset 1355 out of valid range
**** row 7: row offset 1338 out of valid range
**** row 25: row offset 3055 out of valid range
**** row 26: row offset 3038 out of valid range
**** row 27: row offset 3021 out of valid range
**** row 28: row offset 3004 out of valid range
**** row 29: row offset 2987 out of valid range
**** row 30: row offset 2970 out of valid range
**** row 31: row offset 2953 out of valid range
**** row 32: row offset 2936 out of valid range
**** row 33: row offset 2919 out of valid range
**** row 34: row offset 2902 out of valid range
**** row 35: row offset 2885 out of valid range
**** row 36: row offset 2868 out of valid range
**** row 37: row offset 2851 out of valid range
**** row 38: row offset 2834 out of valid range
**** row 39: row offset 1321 out of valid range
**** row 40: row offset 2817 out of valid range
**** row 41: row offset 2800 out of valid range
**** row 42: row offset 2783 out of valid range
**** row 43: row offset 2766 out of valid range
**** row 44: row offset 2749 out of valid range
**** row 45: row offset 2732 out of valid range
**** row 46: row offset 2715 out of valid range
**** row 47: row offset 2698 out of valid range
**** row 48: row offset 2681 out of valid range
**** row 49: row offset 2664 out of valid range
**** row 50: row offset 2647 out of valid range
**** row 51: row offset 2630 out of valid range
**** row 52: row offset 2613 out of valid range
**** row 53: row offset 2596 out of valid range
**** row 54: row offset 2579 out of valid range
**** row 55: row offset 2562 out of valid range
**** row 56: row offset 2545 out of valid range
**** row 57: row offset 2528 out of valid range
**** row 58: row offset 2511 out of valid range
**** row 59: row offset 2494 out of valid range
**** row 60: row offset 2477 out of valid range
**** row 61: row offset 2460 out of valid range
**** row 62: row offset 2443 out of valid range
**** row 63: row offset 2426 out of valid range
**** row 64: row offset 2409 out of valid range
**** row 65: row offset 2392 out of valid range
**** row 66: row offset 2375 out of valid range
**** row 67: row offset 2358 out of valid range
**** row 68: row offset 2341 out of valid range
**** row 69: row offset 2324 out of valid range
**** row 70: row offset 2307 out of valid range
**** row 71: row offset 2290 out of valid range
**** row 72: row offset 2273 out of valid range
**** row 73: row offset 2256 out of valid range
**** row 74: row offset 2239 out of valid range
**** row 75: row offset 2222 out of valid range
**** row 76: row offset 2205 out of valid range
**** row 77: row offset 2188 out of valid range
**** row 78: row offset 2171 out of valid range
**** row 79: row offset 2154 out of valid range
**** row 80: row offset 2137 out of valid range
**** row 81: row offset 2120 out of valid range
**** row 82: row offset 2103 out of valid range
**** row 83: row offset 2086 out of valid range
**** row 84: row offset 2069 out of valid range
**** row 85: row offset 2052 out of valid range
**** row 86: row offset 2035 out of valid range
**** row 87: row offset 2018 out of valid range
**** row 88: row offset 2001 out of valid range
**** row 89: row offset 1984 out of valid range
**** row 90: row offset 1967 out of valid range
**** row 91: row offset 1950 out of valid range
**** row 92: row offset 1933 out of valid range
**** row 93: row offset 1916 out of valid range
**** row 94: row offset 1899 out of valid range
**** row 95: row offset 1882 out of valid range
**** row 96: row offset 1865 out of valid range
**** row 97: row offset 1848 out of valid range
**** row 98: row offset 1831 out of valid range
**** row 99: row offset 1814 out of valid range
**** row 100: row offset 1797 out of valid range
**** row 101: row offset 1780 out of valid range
**** row 102: row offset 1763 out of valid range
**** row 103: row offset 1746 out of valid range
**** row 104: row offset 1729 out of valid range
**** row 105: row offset 1712 out of valid range
**** row 106: row offset 1695 out of valid range
**** row 107: row offset 1678 out of valid range
**** row 108: row offset 1661 out of valid range
**** row 109: row offset 1644 out of valid range
**** row 110: row offset 1627 out of valid range
**** row 111: row offset 1610 out of valid range
**** row 112: row offset 1593 out of valid range
**** row 113: row offset 1576 out of valid range
**** row 114: row offset 1559 out of valid range
**** row 115: row offset 1542 out of valid range
**** row 116: row offset 1525 out of valid range
**** row 117: row offset 1508 out of valid range
**** row 118: row offset 1491 out of valid range
**** row 119: row offset 1474 out of valid range
**** actual rows locked by itl 2  = 95 != # in trans. header = 198
**** actual rows marked deleted = 95 != kdxlende = 123
---- end index block validation
页 87862 失败, 校验代码为 6401
Block Checking: DBA = 4282170, Block Type = KTB-managed data block
**** kdxcofbo = 434 != 590
---- end index block validation
页 87866 失败, 校验代码为 6401
Block Checking: DBA = 4301433, Block Type = KTB-managed data block
data header at 0x5bb525c
kdbchk: avsp(899) > tosp(867)
页 107129 失败, 校验代码为 6128


DBVERIFY - 验证完成

检查的页总数: 956160
处理的页总数 (数据): 919530
失败的页总数 (数据): 10
处理的页总数 (索引): 13114
失败的页总数 (索引): 4
处理的页总数 (其他): 3222
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 20294
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 443681161 (0.443681161)

基于上述情况,现在基本上可以确定是由于datafile 1 block 165中的ind$记录的第四行出现损坏,导致数据库无法正常查询ind$记录,从而使得数据库无法open.已经定位到该问题了,处理起来相对比较简单,使用bbed对其进行修复,让数据库可以正常查询ind$表记录,从而正常open库

SQL> recover database;
完成介质恢复。
SQL> shutdown immediate;
ORA-01109: ??????


已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount pfile='d:/pfile.txt'
ORACLE 例程已经启动。

Total System Global Area 4275781632 bytes
Fixed Size                  2182592 bytes
Variable Size             822084160 bytes
Database Buffers         3439329280 bytes
Redo Buffers               12185600 bytes
数据库装载完毕。
SQL> alter database open;

数据库已更改。

然后到粗数据,完成本次恢复任务.

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

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：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的回滚段处理,打开数据库