Database SOS – 第17页 – Professional Database Recovery Support@+8617813235971

服务器异常断电之后，开机启动数据库启动成功，但是报ORA-00353 ORA-00354以及ORA-600 kdsgrp1错误

Sun Mar 31 01:19:51 2024
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Mar 31 01:19:51 2024
SMON: enabling cache recovery
[4528] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:64250 end:64859 diff:609 (6 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sun Mar 31 01:19:57 2024
QMNC started with pid=40, OS id=4912 
Sun Mar 31 01:20:03 2024
Completed: alter database open
Sun Mar 31 01:20:04 2024
Starting background process CJQ0
Sun Mar 31 01:20:04 2024
CJQ0 started with pid=20, OS id=5104 
Setting Resource Manager plan SCHEDULER[0x32DE]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sun Mar 31 01:20:07 2024
Starting background process VKRM
Sun Mar 31 01:20:07 2024
VKRM started with pid=48, OS id=4160 
Sun Mar 31 01:20:32 2024
Incomplete read from log member 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'. Trying next member.
Incomplete read from log member 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'. Trying next member.
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_j000_4180.trc(incident=132393):
ORA-00353: 日志损坏接近块 13792 更改 33686946 时间 03/31/2024 00:17:54
ORA-00334: 归档日志: 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'
Incomplete read from log member 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'. Trying next member.
Incomplete read from log member 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'. Trying next member.
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_j000_4180.trc(incident=132394):
ORA-00353: 日志损坏接近块 13792 更改 33686946 时间 03/31/2024 00:17:54
ORA-00334: 归档日志: 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'
ORA-00353: 日志损坏接近块 13792 更改 33686946 时间 03/31/2024 00:17:54
ORA-00334: 归档日志: 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xifenfei\xifenfei\incident\incdir_132393\xifenfei_j000_4180_i132393.trc:
ORA-00354: 损坏重做日志块标头
ORA-00353: 日志损坏接近块 13792 更改 33686946 时间 03/31/2024 00:17:54
ORA-00334: 归档日志: 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'
ORA-00353: 日志损坏接近块 13792 更改 33686946 时间 03/31/2024 00:17:54
ORA-00334: 归档日志: 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'
Sun Mar 31 01:20:35 2024
Sweep [inc][132394]: completed
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_j000_4180.trc:
ORA-00354: 损坏重做日志块标头
ORA-00353: 日志损坏接近块 13792 更改 33686946 时间 03/31/2024 00:17:54
ORA-00334: 归档日志: 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_j000_4180.trc(incident=132395):
ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
Incomplete read from log member 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'. Trying next member.
Incomplete read from log member 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'. Trying next member.
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

再次重启数据库报ORA-600 2131错误,数据库无法正常mount

Sun Mar 31 08:59:20 2024
alter database mount exclusive
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_3232.trc  (incident=144175):
ORA-00600: ??????, ??: [2131], [9], [8], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORA-600 signalled during: alter database mount exclusive...

因为这个库有ctl备份,通过rman还原ctl备份,然后尝试recover库,结果报ORA-00310 ORA-00334（由于需要的redo无法正常应用导致）

对于这类情况,只能通过屏蔽一致性强制打开库

数据报ORA-600 2662错误,此类错误比较简单,使用patch scn工具一键搞定(ORA-600 2662快速恢复之Patch scn工具),数据库open成功,导出数据完整恢复
对于正常open的库,出现此类问题属于反常现象,通过分析系统事件确定是由于ntfs文件系统本身有问题导致

ORA-600 ktsiseginfo1故障

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

标题：ORA-600 ktsiseginfo1故障

oracle 9i的库在运行途中突然报ORA-600 kcbnew_3错误

Sun Mar 31 14:25:11 2024
Undo Segment 69 Onlined
Sun Mar 31 14:25:11 2024
Created Undo Segment _SYSSMU69$
Sun Mar 31 14:25:11 2024
Created Undo Segment _SYSSMU70$
Undo Segment 70 Onlined
Sun Mar 31 14:28:41 2024
Starting control autobackup
Control autobackup written to SBT_TAPE device
	comment 'API Version 2.0,MMS Version 1.2.0.0',
	media 'A90063L4'
	handle 'c-1757743655-20240331-01'
Sun Mar 31 14:31:07 2024
Starting control autobackup
Control autobackup written to SBT_TAPE device
	comment 'API Version 2.0,MMS Version 1.2.0.0',
	media 'A90063L4'
	handle 'c-1757743655-20240331-02'
Sun Mar 31 14:34:31 2024
SMON offlining US=70
Sun Mar 31 14:34:31 2024
Created Undo Segment _SYSSMU73$
Undo Segment 73 Onlined
Sun Mar 31 14:34:31 2024
Created Undo Segment _SYSSMU74$
Undo Segment 74 Onlined
Sun Mar 31 14:34:31 2024
Created Undo Segment _SYSSMU81$
Undo Segment 81 Onlined
Sun Mar 31 14:37:36 2024
Errors in file /u2/oradb/admin/xifenfei/udump/xifenfei_ora_3741.trc:
ORA-00600: 内部错误代码，参数: [kcbnew_3], [156], [], [], [], [], [], []
Sun Mar 31 14:39:22 2024
Errors in file /u2/oradb/admin/xifenfei/bdump/xifenfei_smon_6890.trc:
ORA-01595: error freeing extent (6) of rollback segment (70))
ORA-00600: internal error code, arguments: [kcbnew_3], [156], [], [], [], [], [], []
Sun Mar 31 14:45:05 2024
Errors in file /u2/oradb/admin/xifenfei/udump/xifenfei_ora_4046.trc:
ORA-00600: 内部错误代码，参数: [kcbnew_3], [156], [], [], [], [], [], []
Sun Mar 31 14:47:47 2024
Failure to extend rollback segment 73 because of 3113 condition
FULL status of rollback segment 73 set.
Sun Mar 31 14:47:47 2024
FULL status of rollback segment 73 cleared.
Sun Mar 31 14:48:03 2024
Errors in file /u2/oradb/admin/xifenfei/udump/xifenfei_ora_4074.trc:
ORA-00600: 内部错误代码，参数: [kcbnew_3], [156], [], [], [], [], [], []
Sun Mar 31 14:48:35 2024
Errors in file /u2/oradb/admin/xifenfei/bdump/xifenfei_qmn0_4044.trc:
ORA-00600: internal error code, arguments: [kcbnew_3], [156], [], [], [], [], [], []
Sun Mar 31 14:50:01 2024
Failure to extend rollback segment 73 because of 3113 condition
FULL status of rollback segment 73 set.
Sun Mar 31 14:50:02 2024
FULL status of rollback segment 73 cleared.
Sun Mar 31 14:50:41 2024
Restarting dead background process QMN0
QMN0 started with pid=53
Sun Mar 31 14:51:09 2024
Failure to extend rollback segment 73 because of 3113 condition
FULL status of rollback segment 73 set.
Sun Mar 31 14:51:09 2024
FULL status of rollback segment 73 cleared.
Sun Mar 31 14:54:36 2024
Failure to extend rollback segment 73 because of 3113 condition
FULL status of rollback segment 73 set.
Sun Mar 31 14:54:36 2024
FULL status of rollback segment 73 cleared.
Sun Mar 31 14:54:56 2024
Errors in file /u2/oradb/admin/xifenfei/bdump/xifenfei_smon_6890.trc:
ORA-01595: error freeing extent (6) of rollback segment (70))
ORA-00600: internal error code, arguments: [kcbnew_3], [156], [], [], [], [], [], []

从上述信息看,由于ora-600 kcbnew_3错误导致70/73号回滚段异常,smon无法正常对其进行扩展,关于ora-600 kcbnew_3报错描述

客户尝试关闭数据库重启,数据库报ora-600 ktsiseginfo1错误，无法正常启动

Sun Mar 31 15:13:03 2024
Shutting down instance: further logons disabled
Sun Mar 31 15:15:59 2024
Shutting down instance (immediate)
Sun Mar 31 15:15:59 2024
Failure to extend rollback segment 73 because of 1089 condition
Sun Mar 31 15:15:59 2024
FULL status of rollback segment 73 set.
Sun Mar 31 15:15:59 2024
Failure to extend rollback segment 73 because of 1089 condition
FULL status of rollback segment 73 set.
Sun Mar 31 15:15:59 2024
Failure to extend rollback segment 73 because of 1089 condition
Sun Mar 31 15:15:59 2024
Failure to extend rollback segment 73 because of 1089 condition
Sun Mar 31 15:15:59 2024
Failure to extend rollback segment 73 because of 1089 condition
Sun Mar 31 15:15:59 2024
Failure to extend rollback segment 73 because of 1089 condition
Sun Mar 31 15:15:59 2024
FULL status of rollback segment 73 cleared.
Sun Mar 31 15:15:59 2024
Failure to extend rollback segment 73 because of 1089 condition
Sun Mar 31 15:15:59 2024
FULL status of rollback segment 73 set.
Sun Mar 31 15:15:59 2024
Failure to extend rollback segment 73 because of 1089 condition
Sun Mar 31 15:15:59 2024
FULL status of rollback segment 73 cleared.
Sun Mar 31 15:15:59 2024
FULL status of rollback segment 73 cleared.
Sun Mar 31 15:15:59 2024
License high water mark = 161
Sun Mar 31 15:21:04 2024
Active call for process 2878 user 'oracle' program 'oracle@ttperp (TNS V1-V3)'
SHUTDOWN: waiting for active calls to complete.
Sun Mar 31 15:21:07 2024
Failure to extend rollback segment 73 because of 1089 condition
FULL status of rollback segment 73 set.
Sun Mar 31 15:21:07 2024
SMON: FULL status of rollback segment 73 cleared.
Sun Mar 31 15:21:08 2024
ALTER DATABASE CLOSE NORMAL
SMON: disabling tx recovery
SMON: disabling cache recovery
Sun Mar 31 15:21:16 2024
Shutting down archive processes
Archiving is disabled
Sun Mar 31 15:21:16 2024
ARCH shutting down
Sun Mar 31 15:21:16 2024
ARCH shutting down
Sun Mar 31 15:21:16 2024
ARC1: Archival stopped
Sun Mar 31 15:21:16 2024
ARC0: Archival stopped
Sun Mar 31 15:21:17 2024
Thread 1 closed at log sequence 313828
Successful close of redo thread 1.
Sun Mar 31 15:21:18 2024
Completed: ALTER DATABASE CLOSE NORMAL
Sun Mar 31 15:21:18 2024
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Sun Mar 31 15:23:10 2024
Starting ORACLE instance (normal)
Sun Mar 31 15:23:10 2024
Running with 1 strand for Non-Enterprise Edition
Sun Mar 31 15:23:10 2024
WARNING: EINVAL creating segment of size 0x0000000065400000
fix shm parameters in /etc/system or equivalent
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Running with 1 strand for Non-Enterprise Edition
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.4.0.
System parameters with non-default values:
  processes                = 1000
  timed_statistics         = TRUE
  shared_pool_size         = 218103808
  sga_max_size             = 1679366944
  large_pool_size          = 67108864
  java_pool_size           = 33554432
  control_files            = /u2/oradb/oradata/xifenfei/control01.ctl
  db_block_size            = 8192
  db_cache_size            = 838860800
  compatible               = 9.2.0.0.0
  log_archive_start        = TRUE
  log_archive_dest         = /u6/archive_u6
  log_archive_format       = %s_%t.dbf
  db_file_multiblock_read_count= 16
  fast_start_mttr_target   = 0
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS7
  undo_retention           = 10800
  remote_login_passwordfile= EXCLUSIVE
  db_domain                = 
  instance_name            = xifenfei
  utl_file_dir             = /u1/usr/oracle/utl
  job_queue_processes      = 10
  hash_join_enabled        = TRUE
  background_dump_dest     = /u2/oradb/admin/xifenfei/bdump
  user_dump_dest           = /u2/oradb/admin/xifenfei/udump
  core_dump_dest           = /u2/oradb/admin/xifenfei/cdump
  sort_area_size           = 104857600
  db_name                  = xifenfei
  open_cursors             = 300
  star_transformation_enabled= FALSE
  query_rewrite_enabled    = FALSE
  pga_aggregate_target     = 209715200
  aq_tm_processes          = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Sun Mar 31 15:23:11 2024
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=10
ARC0: Archival started
ARC1 started with pid=11
Sun Mar 31 15:23:11 2024
ARCH: STARTING ARCH PROCESSES COMPLETE
Oracle Data Guard is not available in this edition of Oracle.
Sun Mar 31 15:23:11 2024
ARC1: Archival started
Sun Mar 31 15:23:11 2024
ARC1: Thread not mounted
Sun Mar 31 15:23:11 2024
ARC0: Thread not mounted
Sun Mar 31 15:23:11 2024
ALTER DATABASE   MOUNT
Sun Mar 31 15:23:15 2024
Successful mount of redo thread 1, with mount id 2392685535.
Sun Mar 31 15:23:15 2024
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE   MOUNT
Sun Mar 31 15:23:15 2024
ALTER DATABASE OPEN
Sun Mar 31 15:23:15 2024
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 opened at log sequence 313828
  Current log# 5 seq# 313828 mem# 0: /u3/oracle/xifenfei/redolog/redo05a.log
  Current log# 5 seq# 313828 mem# 1: /u4/oracle/xifenfei/redolog/redo05b.log
Successful open of redo thread 1.
Sun Mar 31 15:23:17 2024
SMON: enabling cache recovery
Sun Mar 31 15:23:17 2024
Undo Segment 69 Onlined
Undo Segment 70 Onlined
Undo Segment 71 Onlined
Undo Segment 72 Onlined
Sun Mar 31 15:23:21 2024
Errors in file /u2/oradb/admin/xifenfei/udump/xifenfei_ora_4384.trc:
ORA-00600: 内部错误代码，参数: [ktsiseginfo1], [20], [65], [73], [], [], [], []
Sun Mar 31 15:23:22 2024
Errors in file /u2/oradb/admin/xifenfei/udump/xifenfei_ora_4384.trc:
ORA-00600: 内部错误代码，参数: [ktsiseginfo1], [20], [65], [73], [], [], [], []
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 4384
ORA-1092 signalled during: ALTER DATABASE OPEN...

启动的时候由于回滚段异常导致该问题,临时解决问题可以通过规避掉异常回滚段open库,但是这个问题的本质是由于seg$和undo$中记录不匹配导致,建议进一步通过hcheck进行检查分析,并进行后续处理,参考mos相关描述:

ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因

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

标题：ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因

最近接触过几个ORA-600 16703的恢复case,报错日志类似为：

SMON: enabling cache recovery
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2071.trc  (incident=7357):
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_7357/orcl_ora_2071_i7357.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2071.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2071.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
USER (ospid: 2071): terminating the instance due to error 704
Instance terminated by USER, pid = 2071
ORA-1092 signalled during: ALTER DATABASE OPEN...

查看客户的日志,发现客户的库被人使用bbed按照网上的思路进行过恢复（使用block替换的方式尝试open库）,结果报出来ORA-00600: internal error code, arguments: [16703], [1403], [4]之类的错误

Wed Mar 20 22:00:39 2024
SMON: enabling cache recovery
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_1840752.trc  (incident=62557):
ORA-00600: internal error code, arguments: [16703], [1403], [4], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_62557/orcl_ora_1840752_i62557.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_1840752.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [4], [], [], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_1840752.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [4], [], [], [], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
USER (ospid: 1840752): terminating the instance due to error 704
Instance terminated by USER, pid = 1840752
ORA-1092 signalled during: alter database open...

其实ORA-600 16703 1403 N的错误,表示数据库在检索tab$表的时候,N为发现异常的obj#的值,这里的报错表示数据库在启动过程中无法检索到tab$中的tab$对象,应该是恢复过程中缺少该记录导致,解决问题的思路就是把该记录回写到tab$表中即可
以前写过类似文章:ORA-00600: internal error code, arguments: [16703], [1403], [4] 故障处理

最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视

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

标题：最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视

最近由于安装介质问题,导致tab$被清空的case又多了起来,最近遇到几个客户咨询此类问题

以前写过一些类似的文档,参考:
tab$恢复错误汇总
ORA-600 kzrini:!uprofile处理
10g数据库遭遇ORA-600 16703
12C数据库遭遇ORA-600 16703
ORA-00600: internal error code, arguments: [16703], [1403], [32]
ORA-600 16703直接把orachk备份表插入到tab$恢复
ORA-600 16703故障解析—tab$表被清空
tab$异常被处理之后报ORA-600 13304故障处理

aix平台tab$被删除可能出现ORA-600 [16703], [1403], [28]错误
 ORA-00600: internal error code, arguments: [16703], [1403], [4] 故障处理
 ORA-600 16703故障,客户找人恢复数据库,数据库被进一步恶意破坏—ORA-00704 ORA-00922
警告：互联网中有oracle介质被注入恶意程序导致—ORA-600 16703

ORA-600 2662快速恢复之Patch scn工具

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

标题：ORA-600 2662快速恢复之Patch scn工具

有客户数据库启动报ORA-600 2662错误

SQL> recover database;
完成介质恢复。
SQL> alter database open ;
alter database open 
*
第 1 行出现错误:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [0], [272845978], [0], [272853062], [12583040]
ORA-00600: internal error code, arguments: [2662], [0], [272845977], [0], [272853062], [12583040]
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [272845973], [0], [272853062], [12583040]
进程 ID: 3528
会话 ID: 33 序列号: 1

通过自研开发的patch scn工具,修改数据库scn值

然后open数据库成功

对于这类故障,patch scn工具是最快速的解决方案

TNS-12518: TNS:listener could not hand off client connection

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

标题：TNS-12518: TNS:listener could not hand off client connection

客户反馈业务经常性出现数据库连接异常,通过工具访问ORACLE进程报ORA-12170

通过分析发现lsnrctl status几乎hang住，tnsping延迟特别大

进一步分析监听日志发现TNS-12518: TNS:listener could not hand off client connection错误

12-MAR-2024 15:34:50 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)
(HOST=__jdbc__)(USER=Administrator))(SERVICE_NAME=ilas)) * (ADDRESS=(PROTOCOL=tcp)
(HOST=ip)(PORT=52854)) * establish * ilas * 12518
TNS-12518: TNS:listener could not hand off client connection
 TNS-12547: TNS:lost contact
  TNS-12560: TNS:protocol adapter error
   TNS-00517: Lost contact
    Linux Error: 32: Broken pipe

根据经验和Troubleshooting Guide for TNS-12518 TNS listener could not hand off client connection描述,检查监听文件配置

[oracle@xff admin]$ cat listener.ora
# listener.ora Network Configuration File:/home/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = /home/u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:/home/u01/app/oracle/product/11.2.0/dbhome_1/bin/oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = IP)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /home/u01/app/oracle

根据经验和客户的业务进行分析,确认他们不会使用 external procedures方式访问数据库,直接修改监听配置

[oracle@xff admin]$ cat listener.ora
# listener.ora Network Configuration File:/home/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = xff)
      (ORACLE_HOME = /home/u01/app/oracle/product/11.2.0/dbhome_1)
      (GLOBAL_DBNAME = xff)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = IP)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /home/u01/app/oracle

然后reload配置,再使用lsnrctl status查看结果秒出,tnsping也非常快

让客户测试应用也恢复正常,一切ok,问题在最小修改的情况下解决,和最初供应商建议的重装系统,双机,数据库等解决方案大大简化

ora.storage无法启动报ORA-12514故障处理

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

标题：ora.storage无法启动报ORA-12514故障处理

19.11集群，节点2人工重启之后，crs启动异常

[grid@xff2 ~]$ crsctl status res -t -init
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  ONLINE       xff2                    STABLE
ora.cluster_interconnect.haip
      1        ONLINE  ONLINE       xff2                    STABLE
ora.crf
      1        ONLINE  ONLINE       xff2                    STABLE
ora.crsd
      1        ONLINE  OFFLINE                               STABLE
ora.cssd
      1        ONLINE  ONLINE       xff2                    STABLE
ora.cssdmonitor
      1        ONLINE  ONLINE       xff2                    STABLE
ora.ctssd
      1        ONLINE  ONLINE       xff2                    OBSERVER,STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.drivers.acfs
      1        ONLINE  ONLINE       xff2                    STABLE
ora.evmd
      1        ONLINE  ONLINE       xff2                    STABLE
ora.gipcd
      1        ONLINE  ONLINE       xff2                    STABLE
ora.gpnpd
      1        ONLINE  ONLINE       xff2                    STABLE
ora.mdnsd
      1        ONLINE  ONLINE       xff2                    STABLE
ora.storage
      1        ONLINE OFFLINE                                STABLE
--------------------------------------------------------------------------------

crs的alert日志显示

2024-03-05 12:46:26.021 [CLSECHO(3653)]ACFS-9327: Verifying ADVM/ACFS devices.
2024-03-05 12:46:26.040 [CLSECHO(3661)]ACFS-9156: Detecting control device '/dev/asm/.asm_ctl_spec'.
2024-03-05 12:46:26.065 [CLSECHO(3673)]ACFS-9156: Detecting control device '/dev/ofsctl'.
2024-03-05 12:46:26.357 [CLSECHO(3703)]ACFS-9294: updating file /etc/sysconfig/oracledrivers.conf
2024-03-05 12:46:26.376 [CLSECHO(3711)]ACFS-9322: completed
2024-03-05 12:46:27.764 [CSSDMONITOR(3855)]CRS-8500: Oracle Clusterware CSSDMONITOR process is starting with operating system process ID 3855
2024-03-05 12:46:27.839 [OSYSMOND(3857)]CRS-8500: Oracle Clusterware OSYSMOND process is starting with operating system process ID 3857
2024-03-05 12:46:28.129 [CSSDAGENT(3890)]CRS-8500: Oracle Clusterware CSSDAGENT process is starting with operating system process ID 3890
2024-03-05 12:46:29.125 [OCSSD(3910)]CRS-8500: Oracle Clusterware OCSSD process is starting with operating system process ID 3910
2024-03-05 12:46:30.187 [OCSSD(3910)]CRS-1713: CSSD daemon is started in hub mode
2024-03-05 12:46:31.428 [OCSSD(3910)]CRS-1707: Lease acquisition for node xff2 number 2 completed
2024-03-05 12:46:32.630 [OCSSD(3910)]CRS-1621: The IPMI configuration data for this node stored in the Oracle registry is incomplete; details at (:CSSNK00002:) in /u01/app/grid/diag/crs/xff2/crs/trace/ocssd.trc
2024-03-05 12:46:32.630 [OCSSD(3910)]CRS-1617: The information required to do node kill for node xff2 is incomplete; details at (:CSSNM00004:) in /u01/app/grid/diag/crs/xff2/crs/trace/ocssd.trc
2024-03-05 12:46:32.638 [OCSSD(3910)]CRS-1605: CSSD voting file is online: /dev/sda1; details in /u01/app/grid/diag/crs/xff2/crs/trace/ocssd.trc.
2024-03-05 12:46:33.546 [OCSSD(3910)]CRS-1601: CSSD Reconfiguration complete. Active nodes are xff1 xff2 .
2024-03-05 12:46:35.405 [OCSSD(3910)]CRS-1720: Cluster Synchronization Services daemon (CSSD) is ready for operation.
2024-03-05 12:46:35.533 [OCTSSD(4138)]CRS-8500: Oracle Clusterware OCTSSD process is starting with operating system process ID 4138
2024-03-05 12:46:36.339 [OCTSSD(4138)]CRS-2403: The Cluster Time Synchronization Service on host xff2 is in observer mode.
2024-03-05 12:46:37.601 [OCTSSD(4138)]CRS-2407: The new Cluster Time Synchronization Service reference node is host xff1.
2024-03-05 12:46:37.601 [OCTSSD(4138)]CRS-2401: The Cluster Time Synchronization Service started on host xff2.
2024-03-05 12:46:54.181 [ORAROOTAGENT(2427)]CRS-5019: All OCR locations are on ASM disk groups [SYSTEMDG], and none of these disk groups are mounted. Details are at "(:CLSN00140:)" in "/u01/app/grid/diag/crs/xff2/crs/trace/ohasd_orarootagent_root.trc".
2024-03-05 12:47:15.209 [OLOGGERD(4553)]CRS-8500: Oracle Clusterware OLOGGERD process is starting with operating system process ID 4553
2024-03-05 12:52:04.581 [CRSCTL(8313)]CRS-1013: The OCR location in an ASM disk group is inaccessible. Details in /u01/app/grid/diag/crs/xff2/crs/trace/crsctl_8313.trc.
2024-03-05 12:56:44.519 [ORAROOTAGENT(2427)]CRS-5818: Aborted command 'start' for resource 'ora.storage'. Details at (:CRSAGF00113:) {0:5:3} in /u01/app/grid/diag/crs/xff2/crs/trace/ohasd_orarootagent_root.trc.
2024-03-05 12:56:44.608 [OHASD(2217)]CRS-2757: Command 'Start' timed out waiting for response from the resource 'ora.storage'. Details at (:CRSPE00221:) {0:5:3} in /u01/app/grid/diag/crs/xff2/crs/trace/ohasd.trc.
2024-03-05 12:56:44.606 [ORAROOTAGENT(2427)]CRS-5017: The resource action "ora.storage start" encountered the following error:
2024-03-05 12:56:44.606+agent's abort action pending. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/xff2/crs/trace/ohasd_orarootagent_root.trc".
2024-03-05 12:57:58.464 [CRSD(11801)]CRS-8500: Oracle Clusterware CRSD process is starting with operating system process ID 11801
2024-03-05 12:58:12.059 [CRSD(11801)]CRS-1013: The OCR location in an ASM disk group is inaccessible. Details in /u01/app/grid/diag/crs/xff2/crs/trace/crsd.trc.

ohasd_orarootagent_root 日志

2024-03-05 12:52:00.769 :  OCRRAW:4255452928: kgfnConnect3: Got a Connection Error when connecting to ASM.

2024-03-05 12:52:00.771 :  OCRRAW:4255452928: kgfnConnect2: failed to connect

2024-03-05 12:52:00.771 :  OCRRAW:4255452928: kgfnConnect2Retry: failed to connect connect after 1 attempts, 124s elapsed

2024-03-05 12:52:00.771 :  OCRRAW:4255452928: kgfo_kge2slos error stack at kgfoAl06: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor


2024-03-05 12:52:00.771 :  OCRRAW:4255452928: -- trace dump on error exit --

2024-03-05 12:52:00.771 :  OCRRAW:4255452928: Error [kgfoAl06] in [kgfokge] at kgfo.c:2176

2024-03-05 12:52:00.771 :  OCRRAW:4255452928: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-12514: TNS:listener does not currently know of service requested

2024-03-05 12:52:00.771 :  OCRRAW:4255452928: Category: 7

"/u01/app/grid/diag/crs/xff2/crs/trace/crsctl_8313.trc" 208L, 11809C

2024-03-05 12:52:03.543 :  OCRRAW:4255452928: 9379 Error 4 opening dom root in 0xf9afdb79c0

2024-03-05 12:52:03.551 :  OCRRAW:4255452928: kgfnConnect2: kgfnGetBeqData failed

2024-03-05 12:52:03.577 :  OCRRAW:4255452928: kgfnConnect2Int: cstr=(DESCRIPTION=(TCP_USER_TIMEOUT=1)(CONNECT_TIMEOUT=60)(EXPIRE_TIME=1)(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=tcp)(HOST=节点1私网IP)(PORT=1525)))(CONNECT_DATA=(SERVICE_NAME=+ASM)))

2024-03-05 12:52:03.578 :  OCRRAW:4255452928: kgfnConnect2Int: ServerAttach

2024-03-05 12:52:04.579 :  OCRRAW:4255452928: kgfnServerAttachConnErrors: Encountered service based error 12514

2024-03-05 12:52:04.579 :  OCRRAW:4255452928: kgfnRecordErr 12514 OCI error:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor


2024-03-05 12:52:04.579 :  OCRRAW:4255452928: kgfnConnect3: Got a Connection Error when connecting to ASM.

2024-03-05 12:52:04.581 :  OCRRAW:4255452928: kgfnConnect2: failed to connect

2024-03-05 12:52:04.581 :  OCRRAW:4255452928: kgfnConnect2Retry: failed to connect connect after 1 attempts, 122s elapsed

2024-03-05 12:52:04.581 :  OCRRAW:4255452928: kgfo_kge2slos error stack at kgfoAl06: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor


2024-03-05 12:52:04.581 :  OCRRAW:4255452928: -- trace dump on error exit --

2024-03-05 12:52:04.581 :  OCRRAW:4255452928: Error [kgfoAl06] in [kgfokge] at kgfo.c:3180

2024-03-05 12:52:04.581 :  OCRRAW:4255452928: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-12514: TNS:listener does not currently know of service requested

2024-03-05 12:52:04.581 :  OCRRAW:4255452928: Category: 7

2024-03-05 12:52:04.581 :  OCRRAW:4255452928: DepInfo: 12514

2024-03-05 12:52:04.581 :  OCRRAW:4255452928: ADR is not properly configured

2024-03-05 12:52:04.581 :  OCRRAW:4255452928: -- trace dump end --

  OCRASM:4255452928: SLOS : SLOS: cat=7, opn=kgfoAl06, dep=12514, loc=kgfokge

2024-03-05 12:52:04.581 :  OCRASM:4255452928: ASM Error Stack : ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

2024-03-05 12:52:04.581 :  OCRASM:4255452928: proprasmo: kgfoCheckMount returned [7]
2024-03-05 12:52:04.581 :  OCRASM:4255452928: proprasmo: The ASM instance is down
2024-03-05 12:52:04.635 :  OCRRAW:4255452928: proprioo: Failed to open [+SYSTEMDG/xff-cluster/OCRFILE/registry.255.1072903025]. Returned proprasmo() with [26]. Marking location as UNAVAILABLE.
2024-03-05 12:52:04.635 :  OCRRAW:4255452928: proprioo: No OCR/OLR devices are usable
  OCRUTL:4255452928: u_fill_errorbuf: Error Info : [Insufficient quorum to open OCR devices]
 default:4255452928: u_set_gbl_comp_error: comptype '107' : error '0'
2024-03-05 12:52:04.635 :  OCRRAW:4255452928: proprinit: Could not open raw device
2024-03-05 12:52:04.635 : default:4255452928: a_init:7!: Backend init unsuccessful : [26]
2024-03-05 12:52:04.637 : default:4255452928: clsvactversion:4: Retrieving Active Version from local storage.

通过这里,初步判断是由于节点2访问(DESCRIPTION=(TCP_USER_TIMEOUT=1)(CONNECT_TIMEOUT=60)(EXPIRE_TIME=1)(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=tcp)(HOST=节点1私网IP)(PORT=1525)))(CONNECT_DATA=(SERVICE_NAME=+ASM)))异常导致,查看节点1的该监听状态

[grid@xff1 ~]$ lsnrctl status ASMNET1LSNR_ASM

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 05-MAR-2024 13:04:51

Copyright (c) 1991, 2021, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM)))
STATUS of the LISTENER
------------------------
Alias                     ASMNET1LSNR_ASM
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                20-MAY-2021 23:53:50
Uptime                    25 days 8 hr. 15 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19c/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/xff1/asmnet1lsnr_asm/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=ASMNET1LSNR_ASM)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=节点1私网IP)(PORT=1525)))
The listener supports no services
The command completed successfully

发现该监听没有注册服务进去,检查相关listener参数配置

[grid@xff1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 5 13:26:29 2024
Version 19.11.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0

SQL> show parameter listener;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
forward_listener                     string
listener_networks                    string
local_listener                       string      
remote_listener                      string

初步判断是由于节点1的ASMNET1LSNR_ASM监听状态异常,很可能是由于asm实例的listener参数异常导致,比较稳妥的解决方案是重启节点1,让其重新生成listener相关参数，实现动态注册,临时解决方法,

[grid@xff1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 5 13:05:11 2024
Version 19.11.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0

SQL> ALTER SYSTEM SET local_listener ='(ADDRESS=(PROTOCOL=TCP)(HOST=节点1私网IP)(PORT=1525))' sid='+ASM1' SCOPE=MEMORY;

System altered.



[grid@xff1 ~]$ lsnrctl status ASMNET1LSNR_ASM

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 05-MAR-2024 13:05:21

Copyright (c) 1991, 2021, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM)))
STATUS of the LISTENER
------------------------
Alias                     ASMNET1LSNR_ASM
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                20-MAY-2021 23:53:50
Uptime                    25 days 8 hr. 15 min. 45 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19c/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/xff1/asmnet1lsnr_asm/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=ASMNET1LSNR_ASM)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=节点1私网IP)(PORT=1525)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_FRA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_SYSTEMDG" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@xff1 ~]$

设置节点1的asm实例的local_listener 参数之后,集群启动成功

[grid@xff2 ~]$ crsctl status res -t -init
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  ONLINE       xff2                    STABLE
ora.cluster_interconnect.haip
      1        ONLINE  ONLINE       xff2                    STABLE
ora.crf
      1        ONLINE  ONLINE       xff2                    STABLE
ora.crsd
      1        ONLINE  ONLINE       xff2                    STABLE
ora.cssd
      1        ONLINE  ONLINE       xff2                    STABLE
ora.cssdmonitor
      1        ONLINE  ONLINE       xff2                    STABLE
ora.ctssd
      1        ONLINE  ONLINE       xff2                    OBSERVER,STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.drivers.acfs
      1        ONLINE  ONLINE       xff2                    STABLE
ora.evmd
      1        ONLINE  ONLINE       xff2                    STABLE
ora.gipcd
      1        ONLINE  ONLINE       xff2                    STABLE
ora.gpnpd
      1        ONLINE  ONLINE       xff2                    STABLE
ora.mdnsd
      1        ONLINE  ONLINE       xff2                    STABLE
ora.storage
      1        ONLINE  ONLINE       xff2                    STABLE
--------------------------------------------------------------------------------

断电引起文件scn异常数据库恢复

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

标题：断电引起文件scn异常数据库恢复

由于异常断电，数据库最初启动报错

Fri Mar 01 08:41:17 2024
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 1865809648
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Fri Mar 01 08:41:24 2024
ALTER DATABASE OPEN
Errors in file /home/oracle/app/diag/rdbms/XFF/XFF/trace/XFF_ora_25243.trc:
ORA-01113: file 13 needs media recovery
ORA-01110: data file 13: '/data2/oracle/oradata/data/data00.dbf'
ORA-1113 signalled during: ALTER DATABASE OPEN...

经过应用厂商一系列操作，主要是如下操作

Fri Mar 01 11:10:56 2024
ALTER DATABASE RECOVER datafile 13
Media Recovery Start
Serial Media Recovery started
WARNING! Recovering data file 13 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
ORA-279 signalled during: ALTER DATABASE RECOVER datafile 13 ...
Fri Mar 01 11:11:09 2024
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /home/oracle/app/flash_recovery_area/XFF/archivelog/2024_03_01/o1_mf_1_153563_%u_.arc
Errors with log /home/oracle/app/flash_recovery_area/XFF/archivelog/2024_03_01/o1_mf_1_153563_%u_.arc
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /home/oracle/app/flash_recovery_area/XFF/archivelog/2024_03_01/o1_mf_1_153563_%u_.arc
Errors with log /home/oracle/app/flash_recovery_area/XFF/archivelog/2024_03_01/o1_mf_1_153563_%u_.arc
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
ALTER DATABASE RECOVER CANCEL
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL
Fri Mar 01 11:16:50 2024
db_recovery_file_dest_size of 10240 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Fri Mar 01 11:51:56 2024
Starting ORACLE instance (normal)
Fri Mar 01 12:11:35 2024
alter database datafile 13 offline
ORA-1145 signalled during: alter database datafile 13 offline...
Fri Mar 01 12:12:29 2024
alter database recover cancel
ORA-1112 signalled during: alter database recover cancel...
Fri Mar 01 12:13:24 2024
ALTER DATABASE RECOVER database until cancel
Media Recovery Start
started logmerger process
Fri Mar 01 12:13:24 2024
WARNING! Recovering data file 13 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 14 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 15 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 16 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 17 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 18 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 19 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 20 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 21 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 22 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
Parallel Media Recovery started with 48 slaves
ORA-279 signalled during: ALTER DATABASE RECOVER database until cancel ...
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /home/oracle/app/flash_recovery_area/XFF/archivelog/2024_03_01/o1_mf_1_153563_%u_.arc
Errors with log /home/oracle/app/flash_recovery_area/XFF/archivelog/2024_03_01/o1_mf_1_153563_%u_.arc
Errors in file /home/oracle/app/diag/rdbms/XFF/XFF/trace/XFF_pr00_38097.trc:
ORA-00308:cannot open archived log '/home/app/flash_recovery_area/XFF/archivelog/2024_03_01/o1_mf_1_153563_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /home/oracle/app/flash_recovery_area/XFF/archivelog/2024_03_01/o1_mf_1_153563_%u_.arc
Errors with log /home/oracle/app/flash_recovery_area/XFF/archivelog/2024_03_01/o1_mf_1_153563_%u_.arc
Errors in file /home/oracle/app/diag/rdbms/XFF/XFF/trace/XFF_pr00_38097.trc:
ORA-00308:cannot open archived log '/home/app/flash_recovery_area/XFF/archivelog/2024_03_01/o1_mf_1_153563_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
ALTER DATABASE RECOVER CANCEL
Signalling error 1152 for datafile 1!
Errors in file /home/oracle/app/diag/rdbms/XFF/XFF/trace/XFF_pr00_38097.trc:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/data1/oracle/oradata/XFF/system01.dbf'
Slave exiting with ORA-1547 exception
Errors in file /home/oracle/app/diag/rdbms/XFF/XFF/trace/XFF_pr00_38097.trc:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/data1/oracle/oradata/XFF/system01.dbf'
ORA-10879 signalled during: ALTER DATABASE RECOVER CANCEL ...
Fri Mar 01 13:23:05 2024
ALTER DATABASE DATAFILE '/data2/oracle/oradata/data/data00.dbf' OFFLINE DROP
Completed: ALTER DATABASE DATAFILE '/data2/oracle/oradata/data/data00.dbf' OFFLINE DROP

接手现场之后,尝试单个文件recover操作

SQL> recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> recover datafile 2;
Media recovery complete.
SQL> recover datafile 3;
Media recovery complete.
SQL> recover datafile 4;
Media recovery complete.
SQL> recover datafile 5;
Media recovery complete.
SQL> recover datafile 6,7,8,9,10;
Media recovery complete.
SQL> recover datafile 11;
Media recovery complete.
SQL> recover datafile 12;
Media recovery complete.
SQL> recover datafile 13;
ORA-00279: change 1474236715 generated at 02/29/2024 17:13:00 needed for thread 1
ORA-00289: suggestion : /home/oracle/app/program/11g/dbs/arch1_153563_1136037378.dbf
ORA-00280: change 1474236715 for thread 1 is in sequence #153563


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/home/oracle/app/program/11g/dbs/arch1_153563_1136037378.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> recover datafile 14;
ORA-00279: change 1474236715 generated at 02/29/2024 17:13:00 needed for thread 1
ORA-00289: suggestion : /home/oracle/app/program/11g/dbs/arch1_153563_1136037378.dbf
ORA-00280: change 1474236715 for thread 1 is in sequence #153563


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/home/oracle/app/program/11g/dbs/arch1_153563_1136037378.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

基于这样的情况,通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)检查发现/data2挂载点所有数据文件异常,由于以前的操作日志已经被清空无法判断原因,初步怀疑和这个挂载点本身有关系


这种情况直接使用bbed修改文件头,然后open库,再逻辑导出数据,完成本次数据恢复工作,参考类似文档
bbed 修改datafile header
使用bbed让rac中的sysaux数据文件online
当然这类故障也可以通过自研的Oracle Recovery Tools工具进行修复处理,类似文档:
Oracle Recovery Tools解决ORA-00279 ORA-00289 ORA-00280故障

ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance

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

标题：ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance

客户反馈,rac有一个节点无法open(可以mount),在open过程报如下错误

alert日志内容中报错主要为:
ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance

Thu Feb 29 17:46:15 2024
Successful mount of redo thread 1, with mount id 354054158
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
Lost write protection disabled
Completed: ALTER DATABASE MOUNT /* db agent *//* {1:32636:2} */
ALTER DATABASE OPEN /* db agent *//* {1:32636:2} */
Picked broadcast on commit scheme to generate SCNs
ARCH: STARTING ARCH PROCESSES
Thu Feb 29 17:46:16 2024
ARC0 started with pid=39, OS id=15401176 
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thu Feb 29 17:46:18 2024
ARC1 started with pid=41, OS id=11993228 
Thu Feb 29 17:46:18 2024
ARC2 started with pid=42, OS id=15007986 
Thu Feb 29 17:46:18 2024
ARC3 started with pid=43, OS id=12779724 
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
WARNING: The 'LOG_ARCHIVE_CONFIG' init.ora parameter settings
are inconsistent with another started instance.  This may be
caused by the 'DB_UNIQUE_NAME' init.ora parameter being specified
differently on one or more of the other RAC instances; the
DB_UNIQUE_NAME parameter value MUST be identical for all 
instances of the database.
Errors in file /oracle/oracle/diag/rdbms/xff/xff1/trace/xff1_lgwr_12976288.trc:
ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
LGWR (ospid: 12976288): terminating the instance due to error 16188
Thu Feb 29 17:46:18 2024
System state dump requested by(instance=1, osid=12976288 (LGWR)),summary=[abnormal instance termination].
System State dumped to trace file /oracle/oracle/diag/rdbms/xff/xff1/trace/xff1_diag_13041806.trc
Thu Feb 29 17:46:18 2024
ORA-1092 : opitsk aborting process
Thu Feb 29 17:46:19 2024
License high water mark = 1
Instance terminated by LGWR, pid = 12976288
USER (ospid: 15532254): terminating the instance
Instance terminated by USER, pid = 15532254

检查LOG_ARCHIVE_CONFIG和DB_UNIQUE_NAME参数配置

SQL> select inst_id,value,name,length(value) from gv$parameter where name in ('log_archive_config','db_unique_name');

   INST_ID VALUE                          NAME                                  LENGTH(VALUE)
---------- ------------------------------ ------------------------------------- -------------
         2                                log_archive_config
         2 xff                            db_unique_name                                    8
         1                                log_archive_config
         1 xff                            db_unique_name                                    8

设置尝试log_archive_config配置为”和reset 均数据库无法正常启动

SQL> alter system set log_archive_config='' sid='*';

系统已更改。

SQL> alter system reset log_archive_config scope=both sid='*';

系统已更改。

设置log_archive_config=NODG_CONFIG数据库启动成功

SQL> alter system set log_archive_config=NODG_CONFIG scope=both sid='*';

系统已更改。

SQL> alter database open;

数据库已更改。

SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options 断开

.[hudsonL@cock.li].mkp勒索加密数据库完美恢复

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

标题：.[hudsonL@cock.li].mkp勒索加密数据库完美恢复

有朋友oracle数据库所在机器被加密,扩展名为：.[hudsonL@cock.li].mkp,数据文件类似：

通过专业工具分析,确认这次运气非常好,每个文件就加密破坏前面31个block

通过研发的Oracle数据文件勒索恢复工具进行恢复

顺利数据库并且导出数据

mkp勒索病毒预防建议：
1. 教育和培训：提高用户的网络安全意识非常重要。通过定期的网络安全培训和教育，向用户传达有关勒索病毒及其传播方式的知识，让他们能够警惕潜在的威胁，并学会如何正确应对可疑的电子邮件、链接和附件。
2. 更新和维护：及时更新操作系统、应用程序和安全软件，以修补已知的漏洞，并确保系统能够及时获取最新的安全补丁。此外，定期进行系统维护和检查，确保系统的安全配置和设置。
3. 备份数据：定期备份重要的数据和文件，并将备份存储在安全的离线或云存储中。确保备份是完整的、可靠的，并且能够及时恢复，以便在发生勒索病毒感染或其他数据丢失事件时能够快速恢复数据。
4. 网络安全工具：使用可信赖的网络安全工具，包括防病毒软件、防火墙、入侵检测系统等，以提高系统的安全性和防护能力。定期对系统进行全面的安全扫描和检测，及时发现并清除潜在的威胁。
5. 访问控制：实施严格的访问控制措施，限制用户对系统和文件的访问权限，避免使用管理员权限进行日常操作，以减少恶意软件感染的风险。此外，定期审查和更新访问控制策略，确保系统安全性得到有效维护。
6. 应急响应计划：制定和实施应急响应计划，明确团队成员的责任和任务，建立应对勒索病毒和其他安全事件的应急响应流程，以最大程度地减少损失并快速恢复业务正常运营。

数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)