ORA-600 krhpfh_03-1208

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

最近一个客户咨询一个问题,他正常的drop tbs,结果触发ORA-600 krhpfh_03-1208 错误,导致数据库crash

Wed Mar 26 14:33:20 2025
Thread 1 cannot allocate new log, sequence 478485
Checkpoint not complete
  Current log# 2 seq# 478484 mem# 0: /apps/data/oracle/orcl/redo02.log
Thread 1 advanced to log sequence 478485 (LGWR switch)
  Current log# 3 seq# 478485 mem# 0: /apps/data/oracle/orcl/redo03.log
Wed Mar 26 14:35:06 2025
Wed Mar 26 14:35:06 2025
drop tablespace XFF_MON_2016 including contents and datafiles cascade constraint
Read of datafile '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf' (fno 17) header failed with ORA-01208
Rereading datafile 17 header failed with ORA-01208
Errors in file /apps/svr/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_188213.trc  (incident=7677):
ORA-00600: 内部错误代码, 参数: [krhpfh_03-1208],[fno =],[17],[fecpc =],[454709],[fhcpc =],[402935],[],[],[],[],[]
ORA-01110: 数据文件 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-01122: 数据库文件 17 验证失败
ORA-01110: 数据文件 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-01208: 数据文件是旧的版本 - 不能访问当前版本
Incident details in: /apps/svr/oracle/diag/rdbms/orcl/orcl/incident/incdir_7677/orcl_ora_188213_i7677.trc
Wed Mar 26 14:35:07 2025
Trace dumping is performing id=[cdmp_20250326143507]
ORA-600 signalled during: drop tablespace XFF_MON_2016 including contents and datafiles cascade constraint...
Errors in file /apps/svr/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_188213.trc  (incident=7678):
ORA-00600: 内部错误代码, 参数: [krhpfh_03-1208],[fno =],[17],[fecpc =],[454709],[fhcpc =],[402935],[],[],[],[],[]
ORA-01110: 数据文件 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-00600: 内部错误代码, 参数: [krhpfh_03-1208],[fno =],[17],[fecpc =],[454709],[fhcpc =],[402935],[],[],[],[],[]
ORA-01110: 数据文件 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-01122: 数据库文件 17 验证失败
ORA-01110: 数据文件 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-01208: 数据文件是旧的版本 - 不能访问当前版本
Incident details in: /apps/svr/oracle/diag/rdbms/orcl/orcl/incident/incdir_7678/orcl_ora_188213_i7678.trc
Wed Mar 26 14:35:08 2025
Sweep [inc][7678]: completed
Sweep [inc][7677]: completed
Sweep [inc2][7677]: completed
Wed Mar 26 14:35:09 2025
Thread 1 cannot allocate new log, sequence 478486
Checkpoint not complete
  Current log# 3 seq# 478485 mem# 0: /apps/data/oracle/orcl/redo03.log
Errors in file /apps/svr/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_188213.trc  (incident=7679):
ORA-00600: 内部错误代码, 参数: [krhpfh_03-1208],[fno =],[17],[fecpc =],[454709],[fhcpc =],[402935],[],[],[],[],[]
ORA-01110: 数据文件 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-00600: 内部错误代码, 参数: [krhpfh_03-1208],[fno =],[17],[fecpc =],[454709],[fhcpc =],[402935],[],[],[],[],[]
ORA-01110: 数据文件 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-00600: 内部错误代码, 参数: [krhpfh_03-1208],[fno =],[17],[fecpc =],[454709],[fhcpc =],[402935],[],[],[],[],[]
ORA-01110: 数据文件 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-01122: 数据库文件 17 验证失败
ORA-01110: 数据文件 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-01208: 数据文件是旧的版本 - 不能访问当前版本
Incident details in: /apps/svr/oracle/diag/rdbms/orcl/orcl/incident/incdir_7679/orcl_ora_188213_i7679.trc
Trace dumping is performing id=[cdmp_20250326143511]
Errors in file /apps/svr/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_188213.trc:
ORA-00600: 内部错误代码, 参数: [krhpfh_03-1208],[fno =],[17],[fecpc =],[454709],[fhcpc =],[402935],[],[],[],[],[]
ORA-01110: 数据文件 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-00600: 内部错误代码, 参数: [krhpfh_03-1208],[fno =],[17],[fecpc =],[454709],[fhcpc =],[402935],[],[],[],[],[]
ORA-01110: 数据文件 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-00600: 内部错误代码, 参数: [krhpfh_03-1208],[fno =],[17],[fecpc =],[454709],[fhcpc =],[402935],[],[],[],[],[]
ORA-01110: 数据文件 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-01122: 数据库文件 17 验证失败
ORA-01110: 数据文件 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-01208: 数据文件是旧的版本 - 不能访问当前版本
Thread 1 advanced to log sequence 478486 (LGWR switch)
  Current log# 1 seq# 478486 mem# 0: /apps/data/oracle/orcl/redo01.log
Wed Mar 26 14:35:13 2025
Errors in file /apps/svr/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_188213.trc  (incident=15551):
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [krhpfh_03-1208],[fno =],[17],[fecpc =],[454709],[fhcpc =],[402935]
ORA-01110: data file 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-00600: internal error code, arguments: [krhpfh_03-1208],[fno =],[17],[fecpc =],[454709],[fhcpc =],[402935]
ORA-01110: data file 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-00600: internal error code, arguments: [krhpfh_03-1208],[fno =],[17],[fecpc =],[454709],[fhcpc =],[402935]
ORA-01110: data file 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-01122: database file 17 failed verification check
ORA-01110: data file 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-01208: data file is an old version - not accessing current version
Incident details in: /apps/svr/oracle/diag/rdbms/orcl/orcl/incident/incdir_15551/orcl_ora_188213_i15551.trc
Errors in file /apps/svr/oracle/diag/rdbms/orcl/orcl/incident/incdir_15551/orcl_ora_188213_i15551.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [krhpfh_03-1208],[fno =],[17],[fecpc =],[454709],[fhcpc =],[402935]
ORA-01110: data file 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-00600: internal error code, arguments: [krhpfh_03-1208],[fno =],[17],[fecpc =],[454709],[fhcpc =],[402935]
ORA-01110: data file 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-00600: internal error code, arguments: [krhpfh_03-1208],[fno =],[17],[fecpc =],[454709],[fhcpc =],[402935]
ORA-01110: data file 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-01122: database file 17 failed verification check
ORA-01110: data file 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-01208: data file is an old version - not accessing current version
Trace dumping is performing id=[cdmp_20250326143514]
Wed Mar 26 14:35:15 2025
Errors in file /apps/svr/oracle/diag/rdbms/orcl/orcl/trace/orcl_pmon_139367.trc  (incident=7224):
ORA-00600: internal error code, arguments: [krhpfh_03-1208],[fno =],[17],[fecpc =],[454709],[fhcpc =],[402935]
ORA-01110: data file 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
Incident details in: /apps/svr/oracle/diag/rdbms/orcl/orcl/incident/incdir_7224/orcl_pmon_139367_i7224.trc
Errors in file /apps/svr/oracle/diag/rdbms/orcl/orcl/trace/orcl_pmon_139367.trc:
ORA-00600: internal error code, arguments: [krhpfh_03-1208],[fno =],[17],[fecpc =],[454709],[fhcpc =],[402935]
ORA-01110: data file 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
Wed Mar 26 14:35:19 2025
drop tablespace XFF_MON_2016 including contents and datafiles cascade constraint
Wed Mar 26 14:35:20 2025
DBW0 (ospid: 139390): terminating the instance due to error 472
Instance terminated by DBW0, pid = 139390

这个报错信息看,但是发起drop tbs之后,数据库应该是检查file 17号文件的状态,发现这个版本状态过旧(ORA-01208: 数据文件是旧的版本),由于某种原因报出来了krhpfh_03-1208,导致数据库crash了,然后他尝试启动数据库报ORA-01113: file 17 needs media recovery

Wed Mar 26 17:11:00 2025
Starting ORACLE instance (normal)
Wed Mar 26 17:11:17 2025
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Wed Mar 26 17:11:28 2025
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =168
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options.
Using parameter settings in server-side spfile /apps/svr/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora
System parameters with non-default values:
  processes                = 1000
  sga_target               = 0
  memory_target            = 66048M
  memory_max_target        = 66048M
  control_files            = "/apps/data/oracle/orcl/control01.ctl"
  control_files            = "/apps/svr/oracle/flash_recovery_area/orcl/control02.ctl"
  db_block_size            = 8192
  compatible               = "11.2.0.0.0"
  db_recovery_file_dest    = "/apps/svr/oracle/flash_recovery_area"
  db_recovery_file_dest_size= 3882M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
  audit_file_dest          = "/apps/svr/oracle/admin/orcl/adump"
  audit_trail              = "DB"
  db_name                  = "orcl"
  open_cursors             = 300
  pga_aggregate_target     = 0
  diagnostic_dest          = "/apps/svr/oracle"
Wed Mar 26 17:11:29 2025
PMON started with pid=2, OS id=28315 
Wed Mar 26 17:11:29 2025
VKTM started with pid=3, OS id=28317 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Wed Mar 26 17:11:29 2025
GEN0 started with pid=4, OS id=28324 
Wed Mar 26 17:11:29 2025
DIAG started with pid=5, OS id=28326 
Wed Mar 26 17:11:29 2025
DBRM started with pid=6, OS id=28328 
Wed Mar 26 17:11:29 2025
PSP0 started with pid=7, OS id=28330 
Wed Mar 26 17:11:29 2025
DIA0 started with pid=9, OS id=28334 
Wed Mar 26 17:11:29 2025
MMAN started with pid=8, OS id=28336 
Wed Mar 26 17:11:29 2025
DBW0 started with pid=10, OS id=28338 
Wed Mar 26 17:11:29 2025
DBW1 started with pid=11, OS id=28340 
Wed Mar 26 17:11:29 2025
DBW2 started with pid=12, OS id=28342 
Wed Mar 26 17:11:29 2025
DBW3 started with pid=13, OS id=28344 
Wed Mar 26 17:11:29 2025
LGWR started with pid=14, OS id=28346 
Wed Mar 26 17:11:29 2025
CKPT started with pid=15, OS id=28348 
Wed Mar 26 17:11:29 2025
SMON started with pid=16, OS id=28350 
Wed Mar 26 17:11:29 2025
RECO started with pid=17, OS id=28352 
Wed Mar 26 17:11:29 2025
MMON started with pid=18, OS id=28354 
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Wed Mar 26 17:11:29 2025
MMNL started with pid=19, OS id=28356 
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /apps/svr/oracle
Wed Mar 26 17:11:29 2025
ALTER DATABASE   MOUNT
Wed Mar 26 17:11:32 2025
Sweep [inc][7679]: completed
Sweep [inc][7224]: completed
Sweep [inc][15551]: completed
Sweep [inc2][7679]: completed
Sweep [inc2][7678]: completed
Sweep [inc2][7224]: completed
Sweep [inc2][15551]: completed
Successful mount of redo thread 1, with mount id 1724539585
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Wed Mar 26 17:11:34 2025
ALTER DATABASE OPEN
Errors in file /apps/svr/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_28406.trc:
ORA-01113: file 17 needs media recovery
ORA-01110: data file 17: '/apps/data/oracle/XFF_MON/XFF_MON_2016.dbf'
ORA-1113 signalled during: ALTER DATABASE OPEN...

由于现场已经破坏,无法分析当时库的情况和17号文件的具体情况做进一步判断,只能通过日志记录下这个类型的错误.
在oracle中关于ORA-600 krhpfh_03的bug也比较多

ORA-39773: parse of metadata stream failed故障处理

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

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

标题：ORA-39773: parse of metadata stream failed故障处理

客户expdp导出数据,在写入生成SYS_EXPORT_SCHEMA表所在的users表空间不足,导致expdp报部分异常

Export: Release 11.2.0.4.0 - Production on Thu Feb 27 15:12:36 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "XFF"."SYS_EXPORT_SCHEMA_95":XFF/**** directory=DUMP dumpfile=20250227.dmp logfile=20250227.log schemas=XFF
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table XFF.SYS_EXPORT_SCHEMA_95 by 128 in tablespace USERS
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table XFF.SYS_EXPORT_SCHEMA_95 by 128 in tablespace USERS
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table XFF.SYS_EXPORT_SCHEMA_95 by 128 in tablespace USERS
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table XFF.SYS_EXPORT_SCHEMA_95 by 128 in tablespace USERS
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table XFF.SYS_EXPORT_SCHEMA_95 by 128 in tablespace USERS
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table XFF.SYS_EXPORT_SCHEMA_95 by 128 in tablespace USERS
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table XFF.SYS_EXPORT_SCHEMA_95 by 128 in tablespace USERS
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table XFF.SYS_EXPORT_SCHEMA_95 by 128 in tablespace USERS
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table XFF.SYS_EXPORT_SCHEMA_95 by 128 in tablespace USERS
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table XFF.SYS_EXPORT_SCHEMA_95 by 128 in tablespace USERS
Total estimation using BLOCKS method: 481.9 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
. . exported "XFF"."PUB_WORKINGTASKLOG" 88.09 GB 112681 rows
. . exported "XFF"."SM_BUSILOG_DEFAULT" 51.94 GB 3149092 rows
. . exported "XFF"."FFW_DISTRIBUTESUBTASK" 46.47 GB 552214 rows
. . exported "XFF"."GL_DETAIL" 11.32 GB 16214805 rows
…………
. . exported "XFF"."ZDP_10000000RR8NKX" 0 KB 0 rows
. . exported "XFF"."ZDP_10000000RTB1GI" 0 KB 0 rows
. . exported "XFF"."ZDP_10000000RTB1GK" 0 KB 0 rows
Master table "XFF"."SYS_EXPORT_SCHEMA_95" successfully loaded/unloaded
******************************************************************************
Dump file set for XFF.SYS_EXPORT_SCHEMA_95 is:
/rman_bak/dump/20200227.dmp
Job "XFF"."SYS_EXPORT_SCHEMA_95" completed with 10 error(s) at Thu Feb 27 19:32:22 2025 elapsed 0 04:16:28

尝试把该dmp导入数据库,报ORA-31694/ORA-02354/ORA-39773错误

Import: Release 11.2.0.4.0 - Production on 星期日 3月 16 02:38:14 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;; 
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31694: 加载/卸载主表 "XFF"."SYS_IMPORT_FULL_01" 失败
ORA-02354: 导出/导入数据时出错
ORA-39773: parse of metadata stream failed

客户需要恢复其中的GL_DETAIL表数据,通过dul实现expdp dump文件转换sqlldr格式方法进行恢复,实现数据完美恢复

对于dmp(exp/expdp)文件,我们可以实现比较完美的恢复,最后限度抢救数据.如果你有oracle expdp/exp dmp被加密或者破坏,无法正常导入数据库,可以联系我们对其进行恢复处理:提供(ORACLE数据库恢复技术支持)：
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com

差点被误操作的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即可恢复.

win平台19c 打patch遭遇2个小问题汇总

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

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

标题：win平台19c 打patch遭遇2个小问题汇总

在给19c的库打ru patch的过程中遇到两个错误,进行记录,以供以后遇到类似错误参考:
UtilSession 失败: oracle/cluster/install/InstallException

C:\Users\Administrator>F:\updatecode\WINDOWS.X64_193000_db_home\opatch\opatch apply F:\oracle_patch\37486199
Oracle 临时补丁程序安装程序版本 12.2.0.1.45
版权所有 (c) 2025, Oracle Corporation。保留所有权利。


Oracle 主目录       ：F:\updatecode\WINDOWS.X64_193000_db_home
主产品清单：C:\Program Files\Oracle\Inventory
   来自           ：
OPatch 版本    ：12.2.0.1.45
OUI 版本       ：12.2.0.7.0
日志文件位置：F:\updatecode\WINDOWS.X64_193000_db_home\cfgtoollogs\opatch\opatch2025-03-17_18-19-56下午_1.log

Verifying environment and performing prerequisite checks...
UtilSession 失败: oracle/cluster/install/InstallException
Log file location: F:\updatecode\WINDOWS.X64_193000_db_home\cfgtoollogs\opatch\opatch2025-03-17_18-19-56下午_1.log

OPatch failed with error code = 73

对应的日志错误部分

[2025-3-17 18:19:57] [INFO]   CAS Dynamic Loading :
[2025-3-17 18:19:57] [INFO]   CUP_LOG: Trying to load HomeOperations object
[2025-3-17 18:19:57] [INFO]   CUP_LOG: HomeOperations object created. CUP1.0 is enabled
[2025-3-17 18:19:57] [INFO]   OPatch invoked as follows: 'apply F:\oracle_patch\37486199 '
[2025-3-17 18:19:57] [INFO]   Runtime args: [-DOPatch.ORACLE_HOME=F:\updatecode\WINDOWS.X64_193000_db_home, -DOPatch.DEBUG=false,
                              -DOPatch.RUNNING_DIR=F:\updatecode\WINDOWS.X64_193000_db_home\OPatch, -DOPatch.MW_HOME=, 
                              -DOPatch.WL_HOME=, -DOPatch.COMMON_COMPONENTS_HOME=, -DOPatch.OUI_LOCATION=, -DOPatch.FMW_COMPONENT_HOME=,
                               -DOPatch.WEBLOGIC_CLASSPATH=, -DOPatch.OPATCH_CLASSPATH=]
[2025-3-17 18:19:57] [INFO]   Heap in use : 120 MB
                              Total memory: 1917 MB
                              Free memory : 1796 MB
                              Max memory  : 27305 MB
[2025-3-17 18:19:57] [INFO]   Oracle 主目录       : F:\updatecode\WINDOWS.X64_193000_db_home
                              主产品清单: C:\Program Files\Oracle\Inventory
                                 从           : 
                              OPatch 版本    : 12.2.0.1.45
                              OUI 版本       : 12.2.0.7.0
                              OUI 位置      : F:\updatecode\WINDOWS.X64_193000_db_home\oui
                              日志文件位置 : F:\updatecode\WINDOWS.X64_193000_db_home\cfgtoollogs\opatch\opatch2025-03-17_18-19-56下午_1.log
[2025-3-17 18:19:57] [INFO]   Patch history file: F:\updatecode\WINDOWS.X64_193000_db_home\cfgtoollogs\opatch\opatch_history.txt
[2025-3-17 18:19:59] [INFO]   [OPSR-TIME] Loading raw inventory
[2025-3-17 18:20:00] [INFO]   [OPSR-MEMORY] Loaded all components from inventory. Heap memory in use: 150 (MB)
[2025-3-17 18:20:00] [INFO]   [OPSR-MEMORY] Loaded all one offs from inventory. Heap memory in use: 160 (MB)
[2025-3-17 18:20:00] [INFO]   [OPSR-TIME] Raw inventory loaded successfully
[2025-3-17 18:20:00] [INFO]   NApply::no CAS enabled, OPatch runs with legacy process.
[2025-3-17 18:20:00] [INFO]   Verifying environment and performing prerequisite checks...
[2025-3-17 18:20:00] [INFO]   [OPSR-TIME] Running prerequisite checks
[2025-3-17 18:20:00] [INFO]   opatch-external.jar is in F:\updatecode\WINDOWS.X64_193000_db_home\OPatch\jlib\opatch-external.jar
[2025-3-17 18:20:00] [SEVERE] OUI-67073:UtilSession 失败: oracle/cluster/install/InstallException
[2025-3-17 18:20:00] [INFO]   Finishing UtilSession at Mon Mar 17 18:20:00 CST 2025
[2025-3-17 18:20:00] [INFO]   堆栈说明: java.lang.RuntimeException: oracle/cluster/install/InstallException
                              	at java.lang.Class.getDeclaredConstructors0(Native Method)
                              	at java.lang.Class.privateGetDeclaredConstructors(Class.java:2671)
                              	at java.lang.Class.getConstructor0(Class.java:3075)
                              	at java.lang.Class.getConstructor(Class.java:1825)
                              	at oracle.opatch.OPatchExternalFactory.getRac(OPatchExternalFactory.java:158)
                              	at oracle.opatch.napplyhelper.EnvValidation.validateConnectStringNodes(EnvValidation.java:104)
                              	at oracle.opatch.napplyhelper.EnvValidation.checkConnectString(EnvValidation.java:92)
                              	at oracle.opatch.napplyhelper.EnvValidation.validate(EnvValidation.java:64)
                              	at oracle.opatch.opatchutil.NApply.legacy_process(NApply.java:530)
                              	at oracle.opatch.opatchutil.NApply.legacy_process(NApply.java:374)
                              	at oracle.opatch.opatchutil.NApply.process(NApply.java:354)
                              	at oracle.opatch.opatchutil.OUSession.napply(OUSession.java:1143)
                              	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                              	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
                              	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
                              	at java.lang.reflect.Method.invoke(Method.java:498)
                              	at oracle.opatch.UtilSession.process(UtilSession.java:355)
                              	at oracle.opatch.OPatchSession.process(OPatchSession.java:2640)
                              	at oracle.opatch.OPatch.process(OPatch.java:888)
                              	at oracle.opatch.OPatch.main(OPatch.java:945)
                              Caused by: java.lang.NoClassDefFoundError: oracle/cluster/install/InstallException
                              	... 20 more
                              Caused by: java.lang.ClassNotFoundException: oracle.cluster.install.InstallException
                              	at java.net.URLClassLoader.findClass(URLClassLoader.java:387)
                              	at java.lang.ClassLoader.loadClass(ClassLoader.java:418)
                              	at java.lang.ClassLoader.loadClass(ClassLoader.java:351)
                              	... 20 more

通过mos给出来的文档:Windows:opatch file with error: [SEVERE] OUI-67073:UtilSession failed: oracle/cluster/install/InstallException (Doc ID 3020534.1),可能是由于%ORACLE_HOME%\oui\jlib\srvm.jar 文件异常导致该问题,查看打patch机器,发现该文件丢失[丢失原因未知],从37486199的patch文件中拷贝该文件到数据库对应目录,后续没有再报该错误

然后提示Prerequisite check “CheckActiveFilesAndExecutables” failed.错误
注意参考:win平台 UtilSession 失败: Prerequisite check “CheckActiveFilesAndExecutables” failed. 处理没有解决问题(因为文件本身没有被占用)

F:\oracle_patch\37486199>F:\updatecode\WINDOWS.X64_193000_db_home\opatch\opatch apply
Oracle 临时补丁程序安装程序版本 12.2.0.1.45
版权所有 (c) 2025, Oracle Corporation。保留所有权利。


Oracle 主目录       ：F:\updatecode\WINDOWS.X64_193000_db_home
主产品清单：C:\Program Files\Oracle\Inventory
   来自           ：
OPatch 版本    ：12.2.0.1.45
OUI 版本       ：12.2.0.7.0
日志文件位置：F:\updatecode\WINDOWS.X64_193000_db_home\cfgtoollogs\opatch\opatch2025-03-17_18-34-40下午_1.log

Verifying environment and performing prerequisite checks...
Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:

Following active files/executables/libs are used by ORACLE_HOME :F:\updatecode\WINDOWS.X64_193000_db_home
F:\updatecode\WINDOWS.X64_193000_db_home\bin\oravssmsgus.dll
F:\updatecode\WINDOWS.X64_193000_db_home\bin\ORAEVRUS19.dll


UtilSession 失败: Prerequisite check "CheckActiveFilesAndExecutables" failed.
Log file location: F:\updatecode\WINDOWS.X64_193000_db_home\cfgtoollogs\opatch\opatch2025-03-17_18-34-40下午_1.log

OPatch failed with error code = 73

通过命令分析确认oravssmsgus.ddl和ORAEVRUS19.dll动态库没有被其他程序占用

F:\oracle_patch\37486199>tasklist /M ora*
信息: 没有运行的任务匹配指定标准。

F:\oracle_patch\37486199>tasklist /M ORA*
信息: 没有运行的任务匹配指定标准。

对于这种情况,根据mos文档:Database Release Update Bundle Windows Patch (XXX) Error”UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed.” (Doc ID 3046640.1)建议,把对一个文件重命名

F:\updatecode\WINDOWS.X64_193000_db_home\bin>dir *bak.dll
 驱动器 F 中的卷是 安全区
 卷的序列号是 4407-E854

 F:\updatecode\WINDOWS.X64_193000_db_home\bin 的目录

2022-07-28  17:35             4,096 ORAEVRUS19-bak.dll
2022-07-28  17:35           100,352 oravssmsgus-bak.dll
               2 个文件        104,448 字节
               0 个目录 680,382,025,728 可用字节

后续打patch操作一切正常,没有再出现其他问题.

表dml操作权限授权给public,导致只读用户失效

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

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

标题：表dml操作权限授权给public,导致只读用户失效

最近一个客户和我反馈,他们创建了一个只读用户(之时给了create session和select表权限),但是其中有部分表可以执行dml操作,我登录系统进行确认

SQL> SELECT PRIVILEGE, ADMIN_OPTION
  2    FROM DBA_SYS_PRIVS
  3   WHERE GRANTEE =  'ALL_READONLY'
  4  UNION
  5  SELECT PRIVILEGE, ADMIN_OPTION
  6    FROM ROLE_SYS_PRIVS
  7   WHERE ROLE IN
  8         (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE =  'ALL_READONLY')
  9  UNION
 10  SELECT PRIVILEGE, ADMIN_OPTION
 11    FROM ROLE_SYS_PRIVS
 12   WHERE ROLE IN (SELECT GRANTED_ROLE
 13                    FROM ROLE_ROLE_PRIVS
 14                   WHERE ROLE IN (SELECT GRANTED_ROLE
 15                                    FROM DBA_ROLE_PRIVS
 16                                   WHERE GRANTEE = 'ALL_READONLY'));

PRIVILEGE                                ADM
---------------------------------------- ---
CREATE SESSION                           NO

尝试对一个表做dml操作,确实可以对u1.t1表进行dml操作

SQL> conn all_readonly/PASSWORD
Connected.
SQL> update U1.T1 set SNAME='111_test' where sid='www.xifenfei.com';

1 row updated.

SQL> rollback;

Rollback complete.

查看这个表的相关授权,关于all_readonly(只读用户)的授权,也确实只是授权了查询权限

SQL>  SELECT GRANTEE,PRIVILEGE,OWNER,TABLE_NAME  FROM dba_TAB_PRIVS WHERE TABLE_NAME ='T1' and GRANTEE='ALL_READONLY'

GRANTEE              PRIVILEGE                                OWNER                TABLE_NAME
-------------------- ---------------------------------------- -------------------- --------------------
ALL_READONLY         SELECT                                   U1                    T1

既然t1这个表可以被dml操作,那是这个表是否还有其他授权,进一步查询该表授权(不限于ALL_REAONLY用户)

SQL> SELECT GRANTEE,PRIVILEGE,OWNER,TABLE_NAME  FROM dba_TAB_PRIVS WHERE TABLE_NAME ='T1';

GRANTEE              PRIVILEGE                                OWNER                TABLE_NAME
-------------------- ---------------------------------------- -------------------- --------------------
PUBLIC               ALTER                                    U1                    T1
PUBLIC               DELETE                                   U1                    T1
PUBLIC               INDEX                                    U1                    T1
PUBLIC               INSERT                                   U1                    T1
PUBLIC               SELECT                                   U1                    T1
PUBLIC               UPDATE                                   U1                    T1
PUBLIC               REFERENCES                               U1                    T1
PUBLIC               ON COMMIT REFRESH                        U1                    T1
PUBLIC               QUERY REWRITE                            U1                    T1
PUBLIC               DEBUG                                    U1                    T1
PUBLIC               FLASHBACK                                U1                    T1
ALL_READONLY         SELECT                                   U1                    T1

14 rows selected.

这下明确了,由于授权了u1.t1表的(insert,delete,update等)权限给public,导致其他用户也可以对这些表进行授权给public的所有操作.
不管任何原因,都不建议授权表/对象的操作给public,这样会导致登录该数据库的所有用户都具有这个权限,风险不可控