Oracle备份恢复 – 第2页

服务器异常断电之后，开机启动数据库启动成功，但是报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-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工具是最快速的解决方案

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-600 kclchkblk_4和2662故障

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

标题：又一例:ORA-600 kclchkblk_4和2662故障

有客户恢复请求:由于未知原因导致aix环境的rac两台主机同时重启之后数据库无法正常启动,初步判断是由于写丢失导致故障（ORA-00742 ORA-00353）

Wed Feb 21 09:23:06 2024
ALTER DATABASE OPEN
This instance was first to open
Abort recovery for domain 0
Errors in file /oracle/db/diag/rdbms/xff/xff1/trace/xff1_ora_5767246.trc:
ORA-01113: file 32 needs media recovery
ORA-01110: data file 32: '+DATA/xff/datafile/x5sys_cs.dbf'
ORA-1113 signalled during: ALTER DATABASE OPEN...
Wed Feb 21 09:23:27 2024
ALTER DATABASE RECOVER  datafile '+DATA/xff/datafile/x5sys_cs.dbf'  
Media Recovery Start
Serial Media Recovery started
WARNING! Recovering data file 32 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
Recovery of Online Redo Log: Thread 2 Group 14 Seq 48490 Reading mem 0
  Mem# 0: +DATA/xff/onlinelog/group_14.313.1060528521
Recovery of Online Redo Log: Thread 1 Group 7 Seq 64195 Reading mem 0
  Mem# 0: +DATA/xff/onlinelog/group_7.306.1060527979
Wed Feb 21 09:24:10 2024
Errors in file /oracle/db/diag/rdbms/xff/xff1/trace/xff1_ora_5767246.trc:
ORA-00742: Log read detects lost write in thread %d sequence %d block %d
ORA-00334: archived log: '+DATA/xff/onlinelog/group_14.313.1060528521'
Errors in file /oracle/db/diag/rdbms/xff/xff1/trace/xff1_ora_5767246.trc  (incident=336478):
ORA-00353: log corruption near block 139727 change 26346459680 time 02/20/2024 20:13:50
ORA-00312: online log 14 thread 2: '+DATA/xff/onlinelog/group_14.313.1060528521'

尝试屏蔽一致性强制拉库后数据库报ORA-600 kclchkblk_4
参考:ora-600 2662和ora-600 kclchkblk_4恢复、redo异常 ORA-600 kclchkblk_4 故障恢复

Wed Feb 21 09:55:26 2024
SMON: enabling cache recovery
Wed Feb 21 09:55:26 2024
Redo thread 2 internally disabled at seq 5 (CKPT)
Archived Log entry 112707 added for thread 2 sequence 4 ID 0xffffffffe144183b dest 1:
ARC0: Archiving disabled thread 2 sequence 5
Archived Log entry 112708 added for thread 2 sequence 5 ID 0xffffffffe144183b dest 1:
Wed Feb 21 09:55:28 2024
Errors in file /oracle/db/diag/rdbms/xff/xff1/trace/xff1_ora_6423264.trc  (incident=360479):
ORA-00600: internal error code, arguments: [kclchkblk_4], [6], [576721660], [6], [576702892]
Incident details in: /oracle/db/diag/rdbms/xff/xff1/incident/incdir_360479/xff1_ora_6423264_i360479.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thread 1 advanced to log sequence 3 (LGWR switch)
  Current log# 7 seq# 3 mem# 0: +DATA/xff/onlinelog/group_7.306.1161510375
Archived Log entry 112709 added for thread 1 sequence 2 ID 0xffffffffe144183b dest 1:
Wed Feb 21 09:55:31 2024
Errors in file /oracle/db/diag/rdbms/xff/xff1/trace/xff1_ora_6423264.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [kclchkblk_4], [6], [576721660], [6], [576702892]
Errors in file /oracle/db/diag/rdbms/xff/xff1/trace/xff1_ora_6423264.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [kclchkblk_4], [6], [576721660], [6], [576702892]

后续处理中出现和这个错误类似的ORA-600 2662错误

Wed Feb 21 15:37:35 2024
SMON: enabling cache recovery
Errors in file /oracle/db/diag/rdbms/xff/xff1/trace/xff1_ora_6357664.trc  (incident=432423):
ORA-00600: internal error code, arguments: [2662], [6], [576742938], [6], [576834283], [12583104]
Incident details in: /oracle/db/diag/rdbms/xff/xff1/incident/incdir_432423/xff1_ora_6357664_i432423.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 /oracle/db/diag/rdbms/xff/xff1/trace/xff1_ora_6357664.trc:
ORA-00600: internal error code, arguments: [2662], [6], [576742938], [6], [576834283], [12583104], [], [], [], [], [], []
Errors in file /oracle/db/diag/rdbms/xff/xff1/trace/xff1_ora_6357664.trc:
ORA-00600: internal error code, arguments: [2662], [6], [576742938], [6], [576834283], [12583104], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 6357664): terminating the instance due to error 600
Instance terminated by USER, pid = 6357664

通过对oracle scn进行修改,数据库open成功

SQL> recover database;
Media recovery complete.
SQL> 
SQL> 
SQL> 
SQL> oradebug setmypid
Statement processed.
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [700000000019B70, 700000000019BA0) = 00000000 00000000 00000000 00000000 
SQL> oradebug poke 
BEFORE: [700000000019B70, 700000000019B78) = 00000000 00000000
AFTER:  [700000000019B70, 700000000019B78) = 00000006 22710D2B
SQL> oradebug DUMPvar SGA kcsgscn_ 
kcslf kcsgscn_ [700000000019B70, 700000000019BA0) = 00000006 22710D2B 00000000 00000000 
SQL> alter database open;

Database altered.

后续检查发现obj$中的index异常（ORA-08102: index key not found, obj# 39）
类似文章:通过bbed修改obj$中dataobj$重现I_OBJ4索引报ORA-08102错误

SQL> truncate CLUSTER "SYS"."SMON_SCN_TO_TIME_AUX";

truncate CLUSTER "SYS"."SMON_SCN_TO_TIME_AUX"
                       *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 39, file 1, block 967206 (2)

SQL> SQL> select object_name,object_type from dba_objects where object_id=39;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
I_OBJ4                         INDEX

对于此类问题使用非常规方法把obj$字典表进行重建（需要注意undo需要为自动管理方式,temp不能为空）,参考:
bootstrap$核心index(I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等)异常恢复—ORA-00701错误解决

Oracle误删除数据文件恢复

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

标题：Oracle误删除数据文件恢复

有客户通过sftp误删除oracle数据文件,咨询我们是否可以恢复,通过远程上去检查,发现运气不错,数据库还没有crash,通过句柄找到被删除文件

oracle@cwgstestdb[testwctdb]/proc/20611/fd$ls -ltr
total 0
lr-x------ 1 oracle oinstall 64 Feb 20 14:03 9 -> /oracle/db19c/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 8 -> /oracle/db19c/dbs/lkTESTWCTDB
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 7 -> /oracle/db19c/dbs/hc_testwctdb.dat
lr-x------ 1 oracle oinstall 64 Feb 20 14:03 6 -> /var/lib/sss/mc/passwd
lr-x------ 1 oracle oinstall 64 Feb 20 14:03 5 -> /proc/20611/fd
lr-x------ 1 oracle oinstall 64 Feb 20 14:03 4 -> /oracle/db19c/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 305 -> /oradata/ftms_zx_test01_data8.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 304 -> /oradata/ftms_zx_test01_data7.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 303 -> /oradata/ftms_zx_test01_data6.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 302 -> '/oradata/ftms_zx_test01_data5.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 301 -> '/oradata/ftms_zx_test01_data4.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 300 -> '/oradata/ftms_zx_test01_data3.dbf (deleted)'
lr-x------ 1 oracle oinstall 64 Feb 20 14:03 3 -> /dev/null
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 299 -> '/oradata/ftms_zx_test01_data2.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 298 -> '/oradata/ftms_zx_test01_data1.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 297 -> '/oradata/ftms_zx_test01_data.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 296 -> /oradata/ftms_zx_test_data.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 295 -> '/oradata/TESTWCTDB/sd.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 294 -> /oradata/TESTWCTDB/ftms_cs3_jiamiceshi
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 293 -> /langchao/dumpdata/FTMS_CS_TDE.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 292 -> /oradata/ftms_zx_test01.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 291 -> /langchao/dumpdata/FTMS_CS_DATA4.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 290 -> '/oradata/ftms_zx_data5.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 289 -> /langchao/dumpdata/FTMS_CS_DATA3.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 288 -> /langchao/dumpdata/FTMS_CS_DATA2.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 287 -> /langchao/dumpdata/FTMS_JD_DATA2.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 286 -> '/oradata/LCBIPECDS _TEMP_DAT.DBF'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 285 -> '/oradata/rTB_MBFE_TEMP (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 284 -> '/oradata/TESTWCTDB/temp01.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 283 -> '/oradata/ftms_credit_data5.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 282 -> /oradata/ftmshtdata.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 281 -> '/oradata/dump_data/FTMS_CSBF_DATA.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 280 -> /langchao/dumpdata/FTMS_NEWBL2_DATA.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 279 -> /langchao/dumpdata/FTMS_CS_DATA.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 278 -> /oradata/LCBIPECDS_DAT.DBF
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 277 -> /oradata/rTB_MBFE
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 276 -> /oradata/udpcount_02.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 275 -> /oradata/udpcount_01.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 274 -> '/oradata/ftms_credit_data_6.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 273 -> /langchao/dumpdata/FTMS_JD_DATA.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 272 -> '/oradata/ftms_old.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 271 -> '/oradata/ftms_credit_data2.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 270 -> /langchao/dumpdata/PJDIP_DATA.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 269 -> '/oradata/ftms_credit_data.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 268 -> /langchao/dumpdata/FTMS_NEWBL_DATA.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 267 -> '/oradata/ftms_zx_data4.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 266 -> /langchao/dumpdata/QIANZHANG_DATA.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 265 -> '/oradata/ftms_zx_data3.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 264 -> '/oradata/ftms_zx_data2.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 263 -> '/oradata/ftms_zx_data.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 262 -> /langchao/dumpdata/FTMSDIP_DATA.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 261 -> /oradata/TESTWCTDB/users01.dbf
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 260 -> '/oradata/TESTWCTDB/undotbs01.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 259 -> '/oradata/TESTWCTDB/sysaux01.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 258 -> '/oradata/TESTWCTDB/system01.dbf (deleted)'
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 257 -> /oradata/TESTWCTDB/control02.ctl
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 256 -> /oradata/TESTWCTDB/control01.ctl
l-wx------ 1 oracle oinstall 64 Feb 20 14:03 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 Feb 20 14:03 10 -> 'socket:[823411]'
l-wx------ 1 oracle oinstall 64 Feb 20 14:03 1 -> /dev/null
lr-x------ 1 oracle oinstall 64 Feb 20 14:03 0 -> /dev/null

查询数据文件大小（被删除的文件文件大小通过v$datafile查询为0）

SQL> select name,bytes/1024/1024/1024 from v$datafile;

NAME                                                                             BYTES/1024/1024/1024
-------------------------------------------------------------------------------- --------------------
/oradata/TESTWCTDB/system01.dbf                                                                     0
/oradata/TESTWCTDB/sysaux01.dbf                                                                     0
/oradata/TESTWCTDB/undotbs01.dbf                                                                    0
/oradata/TESTWCTDB/users01.dbf                                                             .004882813
/langchao/dumpdata/FTMSDIP_DATA.dbf                                                                 3
/oradata/ftms_zx_data.dbf                                                                           0
/oradata/ftms_zx_data2.dbf                                                                          0
/oradata/ftms_zx_data3.dbf                                                                          0
/langchao/dumpdata/QIANZHANG_DATA.dbf                                                               5
/oradata/ftms_zx_data4.dbf                                                                          0
/langchao/dumpdata/FTMS_NEWBL_DATA.dbf                                                             30
/oradata/ftms_credit_data.dbf                                                                       0
/langchao/dumpdata/PJDIP_DATA.dbf                                                                  20
/oradata/ftms_credit_data2.dbf                                                                      0
/oradata/ftms_old.dbf                                                                               0
/langchao/dumpdata/FTMS_JD_DATA.dbf                                                                15
/oradata/ftms_credit_data_6.dbf                                                                     0
/oradata/udpcount_01.dbf                                                                            5
/oradata/udpcount_02.dbf                                                                            5
/oradata/rTB_MBFE                                                                              .03125
/oradata/LCBIPECDS_DAT.DBF                                                                         .5
/langchao/dumpdata/FTMS_CS_DATA.dbf                                                                30
/langchao/dumpdata/FTMS_NEWBL2_DATA.dbf                                                            30
/oradata/dump_data/FTMS_CSBF_DATA.dbf                                                               0
/oradata/ftmshtdata.dbf                                                                    .087890625
/oradata/ftms_credit_data5.dbf                                                                      0
/langchao/dumpdata/FTMS_JD_DATA2.dbf                                                                3
/langchao/dumpdata/FTMS_CS_DATA2.dbf                                                       31.9999847
/langchao/dumpdata/FTMS_CS_DATA3.dbf                                                               10
/oradata/ftms_zx_data5.dbf                                                                          0
/langchao/dumpdata/FTMS_CS_DATA4.dbf                                                        12.109375
/oradata/ftms_zx_test01.dbf                                                                19.0527344
/langchao/dumpdata/FTMS_CS_TDE.dbf                                                                  1
/oradata/TESTWCTDB/ftms_cs3_jiamiceshi                                                     .029296875
/oradata/TESTWCTDB/sd.dbf                                                                           0
/oradata/ftms_zx_test_data.dbf                                                             .009765625
/oradata/ftms_zx_test01_data.dbf                                                                    0
/oradata/ftms_zx_test01_data1.dbf                                                                   0
/oradata/ftms_zx_test01_data2.dbf                                                                   0
/oradata/ftms_zx_test01_data3.dbf                                                                   0
/oradata/ftms_zx_test01_data4.dbf                                                                   0
/oradata/ftms_zx_test01_data5.dbf                                                                   0
/oradata/ftms_zx_test01_data6.dbf                                                          12.5976563
/oradata/ftms_zx_test01_data7.dbf                                                          9.08203125
/oradata/ftms_zx_test01_data8.dbf                                                                6.25

45 rows selected.

把数据文件拷贝回来

cp /proc/20611/fd/302   /langchao/orabak/
cp /proc/20611/fd/301   /langchao/orabak/
cp /proc/20611/fd/300   /langchao/orabak/
cp /proc/20611/fd/299   /langchao/orabak/
cp /proc/20611/fd/298   /langchao/orabak/
cp /proc/20611/fd/297   /langchao/orabak/
cp /proc/20611/fd/295   /langchao/orabak/
cp /proc/20611/fd/290   /langchao/orabak/
cp /proc/20611/fd/285   /langchao/orabak/
cp /proc/20611/fd/284   /langchao/orabak/
cp /proc/20611/fd/283   /langchao/orabak/
cp /proc/20611/fd/281   /langchao/orabak/
cp /proc/20611/fd/274   /langchao/orabak/
cp /proc/20611/fd/272   /langchao/orabak/
cp /proc/20611/fd/271   /langchao/orabak/
cp /proc/20611/fd/269   /langchao/orabak/
cp /proc/20611/fd/267   /langchao/orabak/
cp /proc/20611/fd/265   /langchao/orabak/
cp /proc/20611/fd/264   /langchao/orabak/
cp /proc/20611/fd/263   /langchao/orabak/
cp /proc/20611/fd/260   /langchao/orabak/
cp /proc/20611/fd/259   /langchao/orabak/
cp /proc/20611/fd/258   /langchao/orabak/

由于涉及system表空间数据文件被删除,无法在open情况下直接操作,直接关闭数据库,启动到mount状态,重命名数据文件路径,recover数据文件,open库,恢复完成
参考以前类似恢复:
Solaris rm datafile recovery—利用句柄误删除数据文件恢复
如果数据库已经关闭,需要考虑以下类似恢复方式:
dbca删除库和rm删库恢复
 记录一次rm -rf 删除数据文件异常恢复

ORA-01595/ORA-600 4194处理

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

标题：ORA-01595/ORA-600 4194处理

又一个客户由于服务器断电，导致oracle无法正常启动报ORA-600 4194错误

Sat Jan 20 13:45:22 2024
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 32 processes
Started redo scan
Completed redo scan
 read 31 KB redo, 5 data blocks need recovery
Started redo application at
 Thread 1: logseq 9, block 3
Recovery of Online Redo Log: Thread 1 Group 3 Seq 9 Reading mem 0
  Mem# 0: D:\ORACLE\ORADATA\xff\REDO03.LOG
Completed redo application of 0.02MB
Completed crash recovery at
 Thread 1: logseq 9, block 66, scn 81924188
 5 data blocks read, 5 data blocks written, 31 redo k-bytes read
Thread 1 advanced to log sequence 10 (thread open)
Thread 1 opened at log sequence 10
  Current log# 1 seq# 10 mem# 0: D:\ORACLE\ORADATA\xff\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
Errors in file d:\oracle\diag\rdbms\xff\xff\trace\xff_smon_5164.trc  (incident=384222):
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\oracle\diag\rdbms\xff\xff\incident\incdir_384222\xff_smon_5164_i384222.trc
Errors in file d:\oracle\diag\rdbms\xff\xff\trace\xff_ora_6832.trc  (incident=384270):
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\oracle\diag\rdbms\xff\xff\incident\incdir_384270\xff_ora_6832_i384270.trc
Doing block recovery for file 3 block 207
Resuming block recovery (PMON) for file 3 block 207
Block recovery from logseq 10, block 64 to scn 81924393
Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0
  Mem# 0: D:\ORACLE\ORADATA\xff\REDO01.LOG
Block recovery stopped at EOT rba 10.66.16
Block recovery completed at rba 10.66.16, scn 0.81924391
Doing block recovery for file 3 block 160
Resuming block recovery (PMON) for file 3 block 160
Block recovery from logseq 10, block 64 to scn 81924389
Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0
  Mem# 0: D:\ORACLE\ORADATA\xff\REDO01.LOG
Block recovery completed at rba 10.64.16, scn 0.81924390
Errors in file d:\oracle\diag\rdbms\xff\xff\trace\xff_smon_5164.trc:
ORA-01595: 释放区 (2) 回退段 (3) 时出错
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
Trace dumping is performing id=[cdmp_20240120134525]
Doing block recovery for file 3 block 3857
Resuming block recovery (PMON) for file 3 block 3857
Block recovery from logseq 10, block 64 to scn 81924396
Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0
  Mem# 0: D:\ORACLE\ORADATA\xff\REDO01.LOG
Block recovery stopped at EOT rba 10.66.16
Block recovery completed at rba 10.66.16, scn 0.81924391
Doing block recovery for file 3 block 272
Resuming block recovery (PMON) for file 3 block 272
Block recovery from logseq 10, block 64 to scn 81924390
Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0
  Mem# 0: D:\ORACLE\ORADATA\xff\REDO01.LOG
Block recovery completed at rba 10.66.16, scn 0.81924391
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sat Jan 20 13:45:26 2024
QMNC started with pid=56, OS id=8052 
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x66F8B134] [PC:0x93C7D88, kgegpa()+38]
Dump file d:\oracle\diag\rdbms\xff\xff\trace\alert_xff.log
Doing block recovery for file 3 block 3857
Resuming block recovery (PMON) for file 3 block 3857
Block recovery from logseq 10, block 64 to scn 81924396
Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0
  Mem# 0: D:\ORACLE\ORADATA\xff\REDO01.LOG
Block recovery completed at rba 10.66.16, scn 0.81924399
Errors in file d:\oracle\diag\rdbms\xff\xff\trace\xff_mmon_7876.trc  (incident=396089):
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: 在 "SYS.DBMS_HA_ALERTS_PRVT", line 310
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], 
Doing block recovery for file 3 block 3857
Resuming block recovery (PMON) for file 3 block 3857
Block recovery from logseq 10, block 64 to scn 81924396
Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0
  Mem# 0: D:\ORACLE\ORADATA\xff\REDO01.LOG
Block recovery completed at rba 10.66.16, scn 0.81924399
Errors in file d:\oracle\diag\rdbms\xff\xff\trace\xff_pmon_8876.trc  (incident=384102):
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
Sat Jan 20 13:45:39 2024
Errors in file d:\oracle\diag\rdbms\xff\xff\trace\xff_pmon_8876.trc:
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
PMON (ospid: 8876): terminating the instance due to error 472
Instance terminated by PMON, pid = 8876

这个故障比较简单,由于undo回滚异常导致,对异常的undo进行处理,数据库正常open,重建undo表空间导出数据,完成本次恢复工作

从ORA-00283 ORA-16433报错开始恢复

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

标题：从ORA-00283 ORA-16433报错开始恢复

接手一个客户无法正常启动的故障数据库，尝试recover 报ORA-00283 ORA-16433错误

[oracle@xff trace]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 27 04:46:23 2024

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


???:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs;
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL>
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode

通过对控制文件进行处理,再次尝试recover库

SQL> recover database;
ORA-00399: corrupt change description in redo log
ORA-00353: log corruption near block 134877 change 3249721295 time 01/27/2024 00:21:05
ORA-00312: online log 1 thread 1:'/u01/app/oracle/oradata/xff/redo01.log'

由于redo和数据文件不匹配,无法正常recover库,尝试强制打开库报ORA-600 2662错误

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [0], [3249721308], [0],[3249730440], [16777344],[],[],[],[],[],[]
ORA-00600: internal error code, arguments: [2662], [0], [3249721307], [0],[3249730440], [16777344],[],[],[],[],[],[]
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [3249721303], [0],[3249730440], [16777344],[],[],[],[],[],[]
Process ID: 117336
Session ID: 1146 Serial number: 11764

基于这种错误,尝试oradebug修改scn

SQL> oradebug setmypid
oradebug DUMPvar SGA kcsgscn_
Statement processed.
SQL> kcslf kcsgscn_ [06001FBB0, 06001FBE0) = 00000000 00000000 00000000 00000000 00000000 
SQL> oradebug poke 0x06001FBB0 4 0x10000000
oradebug DUMPvar SGA kcsgscn_
ORA-32521: error parsing ORADEBUG command:

发现报ORA-32521错误,证明常规的oradebug方法无法修改scn,参考相关文章:
oradebug poke ORA-32521/ORA-32519故障解决
第一次通过其他方法处理,由于计算失误导致数据库启动报ORA-600 2252错误

SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2252], [45264], [0], [11641],[3340959744], [],[],[],[],[],[]

该错误是相关文章参考:
记录一次ORA-00600[2252]故障解决
 ORA-00600: internal error code, arguments: [2252], [3987]
主机断电系统回到N年前数据库报ORA-600 kcm_headroom_warn_1错误
处理正确的scn值之后,数据库open成功,然后逻辑方式导出数据,恢复工作完成

SQL> alter database open ;

Database altered.

近期又遇到ORA-600 16703和ORA-702故障

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

标题：近期又遇到ORA-600 16703和ORA-702故障

比较常见的由于软件注入恶意代码或者被人恶意破坏的常见故障ORA-600 16703和ORA-702故障

上面的故障是由于bootstrap$表被删除导致，以前处理过类似case:
恶意删除bootstrap$导致数据库无法正常启动
恶意删除bootstrap$导致数据库无法正常启动
2019恢复第一单—ORA-704-ORA-702恢复
ORA-00702: bootstrap verison ” inconsistent with version ’8.0.0.0.0′

该故障的原因是一般是由于在互联网上下载了带恶意脚本的oracle安装介质,300天之后数据库重启会导致tab$被删除,参见:
ORA-600 16703故障解析—tab$表被清空
警告：互联网中有oracle介质被注入恶意程序导致—ORA-600 16703
再次提醒oracle使用者:
1. 不要使用从第三方网站下载非官方的数据库访问软件（发生过被注入恶意脚本的plsql dev案例:plsql dev引起的数据库被黑勒索比特币实现原理分析和解决方案）
2. 从官方下载oracle 安装程序和补丁程序（防止被注入恶意脚本,参考:警告：互联网中有oracle介质被注入恶意程序导致—ORA-600 16703）

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

ORA-600 ktsiseginfo1故障

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

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

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

又一例:ORA-600 kclchkblk_4和2662故障

Oracle误删除数据文件恢复

ORA-01595/ORA-600 4194处理

从ORA-00283 ORA-16433报错开始恢复

近期又遇到ORA-600 16703和ORA-702故障

分类目录归档：Oracle备份恢复