Oracle备份恢复 – 第9页

Buffer I/O error on dev故障数据库恢复

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

标题：Buffer I/O error on dev故障数据库恢复

客户对虚拟化环境进行物理机加内存,结果发现数据库服务器无法正常启动,报Buffer I/O error on dev错误

通过对底层虚拟化磁盘进行分析,确定vg为centos,数据所在的磁盘组为root

进一步恢复其中数据

然后顺利打开数据库,数据0丢失

[oracle@xifenfei u01]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 13 23:27:42 2023
Version 19.13.0.0.0

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

Connected to an idle instance.
 
SQL> startup
ORACLE instance started.

Total System Global Area 1.0100E+10 bytes
Fixed Size                 13625520 bytes
Variable Size            6241124352 bytes
Database Buffers         3825205248 bytes
Redo Buffers               19927040 bytes
Database mounted.
Database opened.
SQL> select status,count(1) from v$datafile group by status;

STATUS    COUNT(1)
------- ----------
SYSTEM           1
ONLINE          21

win强制修改盘符导致oracle异常恢复

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

标题：win强制修改盘符导致oracle异常恢复

有客户反馈,他们在系统没有关闭数据库的情况下,强制修改了win系统盘符,然后导致数据库异常,启动报错

Sat Feb 25 12:50:40 2023
Recovery of Online Redo Log: Thread 1 Group 2 Seq 10440 Reading mem 0
  Mem# 0 errs 0: G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
Sat Feb 25 12:50:40 2023
Completed redo application
Sat Feb 25 12:50:40 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p001_5604.trc:
ORA-00600: 内部错误代码, 参数: [2037], [25801018], [2973409798], [6], [255], [25], [1198764346], [100796692]

Sat Feb 25 12:50:40 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_7648.trc:
ORA-07445: 出现异常错误: 核心转储 [ACCESS_VIOLATION][_kslwlmod+166][PC:0x469742][ADDR:0x54F8][UNABLE_TO_WRITE][]
ORA-04096: 触发器 '' 的 WHEN 子句过大, 限量为 2K

Sat Feb 25 12:50:40 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_7252.trc:
ORA-00600: internal error code, arguments: [ksuapc2], [258], [0], [2], [1], [2], [], []

Sat Feb 25 12:50:43 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p001_5604.trc:
ORA-00081: 地址范围 [0x77240440, 0x77240444) 不可读
ORA-07445: 出现异常错误: 核心转储 [ACCESS_VIOLATION][_ksl_cleanup+723][PC:0x46E373][ADDR:0x1C][UNABLE_TO_READ][]
ORA-00081: 地址范围 [0x77240440, 0x77240444) 不可读
ORA-00600: 内部错误代码, 参数: [2037], [25801018], [2973409798], [6], [255], [25], [1198764346], [100796692]

Sat Feb 25 12:50:45 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\bdump\orcl_dbw0_6332.trc:
ORA-07445: ??????: ???? [ACCESS_VIOLATION][_kews_idle_wait+378][PC:0x604AE6][ADDR:0xED30C470][UNABLE_TO_WRITE][]

Sat Feb 25 12:50:48 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_7648.trc:
ORA-00600: 内部错误代码, 参数: [kslwlflux:1], [0xAB805400], [0x549C], [2], [], [], [], []
ORA-00081: 地址范围 [0x74480443, 0x74480447) 不可读
ORA-00600: 内部错误代码, 参数: [kslwlflux:1], [0xAB805400], [0x549C], [2], [], [], [], []
ORA-00081: 地址范围 [0x74480443, 0x74480447) 不可读
ORA-00600: 内部错误代码, 参数: [kslwlflux:1], [0xAB805400], [0x549C], [2], [], [], [], []
ORA-00081: 地址范围 [0x74480443, 0x74480447) 不可读
ORA-00600: 内部错误代码, 参数: [kslwlflux:1], [0xAB805400], [0x549C], [2], [], [], [], []
ORA-00081: 地址范围 [0x74480443, 0x74480447) 不可读
ORA-00600: 内部错误代码, 参数: [kslwlflux:1], [0xAB805400], [0x549C], [2], [], [], [], []
ORA-00081: 地址范围 [0x74480443, 0x74480447) 不可读
ORA-00600: 内部错误代码, 参数: [kslwlflux:1], [0xAB805400], [0x549C], [2], [], [], [], []
ORA-00081: 地址范围 [0x74480443, 0x74480447) 不可读
ORA-00600: 内部错误代码, 参数: [kslwlflux:1], [0xAB805400], [0x549C], [2], [], [], [], []
ORA-00081: 地址范围 [0x74480443, 0x74480447) 不可读
ORA-00600: 内部错误代码, 参

Sat Feb 25 12:51:34 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\bdump\orcl_d000_8116.trc:
ORA-07445: ??????: ???? [ACCESS_VIOLATION][_kmcgms+121][PC:0x5D6C71][ADDR:0x50][UNABLE_TO_WRITE][]

Sat Feb 25 12:52:04 2023
USER: terminating instance due to error 472
Sat Feb 25 12:52:48 2023
USER: terminating instance due to error 472
Sat Feb 25 12:52:48 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_6252.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION][_ksuitm+631][PC:0x410C07][ADDR:0x1][UNABLE_TO_READ][]

Sat Feb 25 12:55:35 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_7656.trc:
ORA-07445: ??????: ???? [ACCESS_VIOLATION][_kews_idle_wait+378][PC:0x604AE6][ADDR:0xE530C470][UNABLE_TO_WRITE][]

通过恢复一些恢复之后,数据库open之后又挂掉

Sat Feb 25 15:05:49 2023
SMON: enabling tx recovery
Sat Feb 25 15:05:49 2023
Database Characterset is ZHS16GBK
Sat Feb 25 15:05:50 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_5308.trc:
ORA-00600: 内部错误代码, 参数: [4194], [34], [31], [], [], [], [], []

Sat Feb 25 15:05:50 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_7568.trc:
ORA-00600: 内部错误代码, 参数: [kcbgtcr_13], [], [], [], [], [], [], []

Sat Feb 25 15:05:51 2023
Non-fatal internal error happenned while SMON was doing logging scn->time mapping.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
Sat Feb 25 15:05:51 2023
Doing block recovery for file 2 block 2951
Block recovery from logseq 10441, block 78 to scn 109906860017
Sat Feb 25 15:05:51 2023
Recovery of Online Redo Log: Thread 1 Group 3 Seq 10441 Reading mem 0
  Mem# 0 errs 0: G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
Block recovery stopped at EOT rba 10441.81.16
Block recovery completed at rba 10441.81.16, scn 25.2532677517
Doing block recovery for file 2 block 113
Block recovery from logseq 10441, block 78 to scn 109906859718
Sat Feb 25 15:05:52 2023
Recovery of Online Redo Log: Thread 1 Group 3 Seq 10441 Reading mem 0
  Mem# 0 errs 0: G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
Block recovery completed at rba 10441.80.16, scn 25.2532677516
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=30, OS id=6904
Sat Feb 25 15:05:53 2023
db_recovery_file_dest_size of 2048 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.
Sat Feb 25 15:05:53 2023
Completed: alter database open
Sat Feb 25 15:05:53 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j000_5400.trc:
ORA-00600: 内部错误代码, 参数: [4194], [6], [4], [], [], [], [], []

Sat Feb 25 15:05:54 2023
DEBUG: Replaying xcb 0xac458698, pmd 0x8d7e9b9c for failed op 8
Doing block recovery for file 2 block 1515
No block recovery was needed
Sat Feb 25 15:05:55 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j000_5400.trc:
ORA-00600: 内部错误代码, 参数: [4194], [6], [4], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [6], [4], [], [], [], [], []

Sat Feb 25 15:05:56 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j000_5400.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4194], [6], [4], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [6], [4], [], [], [], [], []

Sat Feb 25 15:05:57 2023
Doing block recovery for file 2 block 2951
Block recovery from logseq 10441, block 78 to scn 109906860017
Sat Feb 25 15:05:57 2023
Recovery of Online Redo Log: Thread 1 Group 3 Seq 10441 Reading mem 0
  Mem# 0 errs 0: G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
Block recovery completed at rba 10441.81.16, scn 25.2532677620
Doing block recovery for file 2 block 113
Block recovery from logseq 10441, block 78 to scn 109906860083
Sat Feb 25 15:05:57 2023
Recovery of Online Redo Log: Thread 1 Group 3 Seq 10441 Reading mem 0
  Mem# 0 errs 0: G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
Block recovery completed at rba 10441.138.16, scn 25.2532677684
Sat Feb 25 15:05:57 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j003_1716.trc:
ORA-12012: 自动执行作业 42568 出错
ORA-00607: 当更改数据块时出现内部错误
ORA-00607: 当更改数据块时出现内部错误

Sat Feb 25 15:05:59 2023
Flush retried for xcb 0xac4c5a80, pmd 0x8c0cec74
Doing block recovery for file 2 block 2951
Block recovery from logseq 10441, block 78 to scn 109906860017
Sat Feb 25 15:05:59 2023
Recovery of Online Redo Log: Thread 1 Group 3 Seq 10441 Reading mem 0
  Mem# 0 errs 0: G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
Sat Feb 25 15:05:59 2023
DEBUG: Replaying xcb 0xac458698, pmd 0x8d7e9b9c for failed op 8
Doing block recovery for file 2 block 1515
No block recovery was needed
Sat Feb 25 15:05:59 2023
Block recovery completed at rba 10441.81.16, scn 25.2532677620
Sat Feb 25 15:06:00 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j002_6400.trc:
ORA-00600: internal error code, arguments: [4194], [6], [4], [], [], [], [], []

Sat Feb 25 15:06:02 2023
DEBUG: Replaying xcb 0xac458698, pmd 0x8d7e9b9c for failed op 8
Doing block recovery for file 2 block 1515
No block recovery was needed
Sat Feb 25 15:06:02 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\bdump\orcl_pmon_1076.trc:
ORA-00600: 内部错误代码, 参数: [4194], [6], [4], [], [], [], [], []

Sat Feb 25 15:06:03 2023
PMON: terminating instance due to error 472
Sat Feb 25 15:06:03 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j007_7188.trc:
ORA-00472: PMON 进程因错误而终止

Sat Feb 25 15:06:03 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j006_7624.trc:
ORA-00472: PMON 进程因错误而终止

Sat Feb 25 15:06:03 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j005_5688.trc:
ORA-00472: PMON  process terminated with error

Sat Feb 25 15:06:10 2023
Errors in file g:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_7568.trc:
ORA-00472: PMON 进程因错误而终止

Instance terminated by PMON, pid = 1076

这个ORA-600 4194错误主要是由于undo异常,从而引起pmon异常,报ORA-00472错误.对undo进行处理,数据库稳定open,逻辑导出数据,完成恢复工作,完美帮忙客户恢复数据.

ORA-600 kcbzpbuf_1故障恢复

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

标题：ORA-600 kcbzpbuf_1故障恢复

数据库启动报错ORA-03113

SQL> startup;
ORACLE instance started.

Total System Global Area 5.1310E+10 bytes
Fixed Size                  2265224 bytes
Variable Size            1.8119E+10 bytes
Database Buffers         3.3152E+10 bytes
Redo Buffers               36069376 bytes
Database mounted.

ORA-03113: end-of-file on communication channel
Process ID: 117892
Session ID: 568 Serial number: 3

分析alert日志发现ORA-600 kcbzpbuf_1报错

Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 4 Seq 4744 Reading mem 0
  Mem# 0: /home/oradata/redo04.log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4745 Reading mem 0
  Mem# 0: /home/oradata/redo01.log
Wed Jan 11 14:44:35 2023
Hex dump of (file 87, block 3143379) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_116740.trc
Corrupt block relative dba: 0x15eff6d3 (file 87, block 3143379)
Bad header found during preparing block for write
Data in bad block:
 type: 0 format: 2 rdba: 0x00000000
 last change scn: 0x0b7e.593518d5 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x18d50001
 check value in block header: 0x342b
 computed block checksum: 0x0
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_116740.trc  (incident=553128):
ORA-00600: internal error code, arguments: [kcbzpbuf_1], [4], [1], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_553128/orcl_dbw0_116740_i553128.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_dbw0_116740.trc:
ORA-00600: internal error code, arguments: [kcbzpbuf_1], [4], [1], [], [], [], [], [], [], [], [], []
DBW0 (ospid: 116740): terminating the instance due to error 471
Wed Jan 11 14:44:36 2023
System state dump requested by (instance=1, osid=116740 (DBW0)), summary=[abnormal instance termination].
Instance terminated by DBW0, pid = 116740

错误比较明显,在应用日志的时候，redo和数据文件的block不匹配，从而出现Corrupt block relative dba: 0x15eff6d3 (file 87, block 3143379)问题,通过bbed对该block进行修复，数据库直接recover成功

RMAN> recover database;

Starting recover at 2023-01-11 14:53:44
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 2023-01-11 14:53:45

数据库open成功

SQL> alter database open;

Database altered.

数据库报ORACLE Instance orcl (pid = 14)类似错误

Thread 1 opened at log sequence 4745
  Current log# 1 seq# 4745 mem# 0: /home/oradata/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Jan 11 14:54:10 2023
SMON: enabling cache recovery
[108954] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:2313624 end:2313634 diff:10 (0 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
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_110633.trc  (incident=577160):
ORA-01578: ORACLE data block corrupted (file # 87, block # 3143379)
ORA-01110: data file 87: '/home/oradata/xifenfei04.dbf'
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Jan 11 14:54:10 2023
QMNC started with pid=80, OS id=114315
Completed: alter database open
Wed Jan 11 14:54:10 2023
db_recovery_file_dest_size of 4182 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.
ORACLE Instance orcl (pid = 14) - Error 1578 encountered while recovering transaction (10, 0) on object 156475.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_110633.trc:
ORA-01578: ORACLE data block corrupted (file # 87, block # 3143379)
ORA-01110: data file 87: '/home/oradata/xifenfei04.dbf'

对其异常对象进行分析,确认是回收站对象,清理回收站

数据库后续运行正常【alert日志没有其他报错】，该恢复完成，业务数据可以直接使用，数据0丢失

Oracle 19c 断电异常恢复

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

标题：Oracle 19c 断电异常恢复

19.3数据库由于异常断电,导致数据库无法启动，报ORA-600 ktbair2: illegal inheritance,ORA-600 6101等错误

2023-01-02T22:01:37.310225+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_pr0l_3788.trc:
ORA-10562: Error occurred while applying redo to data block (file# 10, block# 399386)
ORA-10564: tablespace DATA
ORA-01110: data file 10: 'D:\ORADATA\DATA04.ORA'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 81882
ORA-00600: internal error code, arguments: [ktbair2: illegal  inheritance], [], [], [], [], [], [], [], [], [], [], []
2023-01-02T22:01:37.544630+08:00
Slave exiting with ORA-10562 exception
2023-01-02T22:01:37.560258+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_pr03_4364.trc:
ORA-10562: Error occurred while applying redo to data block (file# 2, block# 716430)
ORA-10564: tablespace DATA
ORA-01110: data file 2: 'D:\ORADATA\DATA01.ORA'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 81743
ORA-00600: internal error code, arguments: [6101], [0], [16], [0], [0], [0], [], [], [], [], [], []
2023-01-02T22:01:38.294726+08:00
Slave exiting with ORA-10562 exception
2023-01-02T22:01:38.310354+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_pr0e_5980.trc:
ORA-10562: Error occurred while applying redo to data block (file# 2, block# 714911)
ORA-10564: tablespace DATA
ORA-01110: data file 2: 'D:\ORADATA\DATA01.ORA'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 74014
ORA-00600: internal error code, arguments: [ktbair2: illegal  inheritance], [], [], [], [], [], [], [], [], [], [], []
2023-01-02T22:01:48.921092+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_pr00_4760.trc:
ORA-00283: recovery session canceled due to errors
ORA-00448: normal completion of background process
2023-01-02T22:01:49.171125+08:00
ORA-756 signalled during: ALTER DATABASE RECOVER  database  ...

报错比较明显由于redo和datafile不匹配导致recover 不成功,尝试强制拉库

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

Undo initialization recovery: err:600 start: 20760593 end: 20762484 diff: 1891 ms (1.9 seconds)
2023-01-02T22:09:05.539709+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_6068.trc:
ORA-00600: 内部错误代码, 参数: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
2023-01-02T22:09:05.555336+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_6068.trc:
ORA-00600: 内部错误代码, 参数: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_6068.trc  (incident=293955):
ORA-00603: ORACLE 服务器会话因致命错误而终止
ORA-01092: ORACLE 实例终止。强制断开连接
ORA-00600: 内部错误代码, 参数: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_293955\orcl_ora_6068_i293955.trc
2023-01-02T22:09:06.805497+08:00
opiodr aborting process unknown ospid (6068) as a result of ORA-603
2023-01-02T22:09:06.961768+08:00
ORA-603 : opitsk aborting process

参考类似处理open数据库成功:
ORA-600 kcbzib_kcrsds_1报错
 12C数据库报ORA-600 kcbzib_kcrsds_1故障处理
 redo异常强制拉库报ORA-600 kcbzib_kcrsds_1修复
 ORA-00603 ORA-01092 ORA-600 kcbzib_kcrsds_1

Oracle Recovery Tools快速恢复ORA-19909

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

标题：Oracle Recovery Tools快速恢复ORA-19909

数据库服务器异常断电，数据库启动报ORA-01113 ORA-01110错误,无法正常open

Sun Jan 01 17:02:55 2023
alter database mount exclusive
Successful mount of redo thread 1, with mount id 1652739647
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount exclusive
alter database open
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_ora_4396.trc:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\ORACLE11G\ORADATA\ORCL\SYSTEM01.DBF'
ORA-1113 signalled during: alter database open...

offline datafile 4，并open数据库

Sun Jan 01 20:36:22 2023
alter database datafile 4 offline drop
Completed: alter database datafile 4 offline drop

Sun Jan 01 20:37:40 2023
ALTER DATABASE OPEN
Thread 1 opened at log sequence 13068
  Current log# 3 seq# 13068 mem# 0: E:\ORACLE11G\ORADATA\ORCL\REDO03.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 ZHS16GBK
No Resource Manager plan active
WARNING: AQ_TM_PROCESSES is set to 0. System operation                     might be adversely affected.
Completed: ALTER DATABASE OPEN

尝试recover datafile 4和online datafile 4失败

Sun Jan 01 22:33:19 2023
ALTER DATABASE RECOVER datafile 4
Media Recovery Start
Serial Media Recovery started
WARNING! Recovering data file 4 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 4 ...
Sun Jan 01 22:34:02 2023
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC
Errors with log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
ALTER DATABASE RECOVER CANCEL
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL
ALTER DATABASE RECOVER datafile 4
Media Recovery Start
Serial Media Recovery started
WARNING! Recovering data file 4 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 4 ...
Sun Jan 01 22:34:15 2023
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC
Errors with log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC
Errors with log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
ALTER DATABASE RECOVER CANCEL
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL
Sun Jan 01 22:36:34 2023
alter database datafile 4 online
ORA-1113 signalled during: alter database datafile 4 online

在datafile 4 offline的情况下，resetlogs库

Sun Jan 01 23:50:01 2023
ALTER DATABASE RECOVER  database until cancel  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 56 slaves
Sun Jan 01 23:50:02 2023
Warning: Datafile 4 (E:\ORACLE11G\ORADATA\ORCL\USERS01.DBF) 
    is offline during full database recovery and will not be recovered
Media Recovery Not Required
Completed: ALTER DATABASE RECOVER  database until cancel  
Sun Jan 01 23:50:15 2023
alter database open
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_ora_2480.trc:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
ORA-1589 signalled during: alter database open...
Sun Jan 01 23:50:34 2023
alter database open RESETLOGS
RESETLOGS after complete recovery through change 158902238
Resetting resetlogs activation ID 1504008459 (0x59a5590b)
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_ora_2480.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'E:\ORACLE11G\ORADATA\ORCL\REDO01.LOG'
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_ora_2480.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 2 of thread 1 is not current copy
ORA-00312: online log 2 thread 1: 'E:\ORACLE11G\ORADATA\ORCL\REDO02.LOG'
Sun Jan 01 23:50:36 2023
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_m000_8340.trc:
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: 'E:\ORACLE11G\ORADATA\ORCL\REDO01.LOG'
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_ora_2480.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1: 'E:\ORACLE11G\ORADATA\ORCL\REDO03.LOG'
Sun Jan 01 23:50:38 2023
Setting recovery target incarnation to 3
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_m000_8340.trc:
ORA-00314: log 2 of thread 1, expected sequence# 13070 doesn't match 0
ORA-00312: online log 2 thread 1: 'E:\ORACLE11G\ORADATA\ORCL\REDO02.LOG'
Sun Jan 01 23:50:39 2023
Assigning activation ID 1652808490 (0x6283db2a)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: E:\ORACLE11G\ORADATA\ORCL\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Jan 01 23:50:39 2023
SMON: enabling cache recovery
Checker run found 5 new persistent data failures
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
File #4 is offline, but is part of an online tablespace.
data file 4: 'E:\ORACLE11G\ORADATA\ORCL\USERS01.DBF'
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
No Resource Manager plan active
WARNING: AQ_TM_PROCESSES is set to 0. System operation                     might be adversely affected.
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Sun Jan 01 23:50:47 2023
Completed: alter database open RESETLOGS

后续尝试恢复datafile 4报ORA-19909

Mon Jan 02 00:02:10 2023
alter database datafile 4 online
Completed: alter database datafile 4 online
Mon Jan 02 00:03:31 2023
ALTER DATABASE RECOVER  database using backup controlfile  
Media Recovery Start
 started logmerger process
Mon Jan 02 00:03:31 2023
Datafile 4 is on orphaned branch
          File status = 4
        Abs fuzzy SCN = 0
 Hot backup fuzzy SCN = 0
Media Recovery failed with error 19909
Slave exiting with ORA-283 exception
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_pr00_8868.trc:
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 4 belongs to an orphan incarnation
ORA-01110: data file 4: 'E:\ORACLE11G\ORADATA\ORCL\USERS01.DBF'
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER  database using backup controlfile  ...

通过Oracle Database Recovery Check检查发现,确实datafile 4的状态为:WRONG RESETLOGS

对于此类情况,参考：Oracle Recovery Tools 解决ORA-01190 ORA-01248等故障快速解决

ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Mon Jan 02 16:14:15 2023
Media Recovery failed with error 264
Slave exiting with ORA-283 exception
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_pr00_10712.trc:
ORA-00283: 恢复会话因错误而取消
ORA-00264: 不要求恢复
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...
Mon Jan 02 16:14:29 2023
ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Mon Jan 02 16:14:29 2023
Media Recovery failed with error 264
Slave exiting with ORA-283 exception
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_pr00_20032.trc:
ORA-00283: 恢复会话因错误而取消
ORA-00264: 不要求恢复
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...
alter database open
Mon Jan 02 16:14:37 2023
Thread 1 advanced to log sequence 2 (thread open)
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: H:\BAIDUNETDISK\ORCL\REDO02.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Jan 02 16:14:37 2023
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
 
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
 
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Mon Jan 02 16:14:37 2023
QMNC started with pid=22, OS id=14152 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open

后续增加tempfile,导出数据完成本次恢复

IMP-00009: abnormal end of export file

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

标题：IMP-00009: abnormal end of export file

exp导出数据正常,没有任何报错

imp导入报IMP-00009和IMP-00020,而且报错表之后数据均未导入,imp程序结束

IMP-00009: abnormal end of export file
IMP-00020: long column too large for column buffer size (2)
Import terminated successfully with warnings.

使用show=y进行dmp文件验证,也报IMP-00009错误,证明是dmp本身异常

通过dul对dmp文件分析

找出来损坏的位置,对其进行人工修复,然后imp顺利导入

故障原因是由于direct=true和分区表（该表132列,而且是空表）一起触发的某个bug

truncate sys用户表导致数据库异常恢复

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

标题：truncate sys用户表导致数据库异常恢复

有客户本想truncate业务用下面所有的表,结果连接成SYS用户,并且拼接truncate 批量语句,导致sys用户下面大量表被truncate

sqlplus无法登录数据库

通过分析obj$发现truncate成功了大量sys用户下面表

基于这种情况,只能把业务数据恢复到一个新库中,然后应用厂商重新配置调试应用.提醒各位:truncate/drop等风险较高操作,一定要核实用户,避免误操作,如果真的遇到此类误操作,第一时间保护现场,原则上只要truncate表之后以前的block没有被覆盖均可恢复

ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况

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

标题：ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况

由于主机异常断电，导致oracle数据库无法正常启动,数据库启动报错ORA-07445 kdxlin,ORA-01172,ORA-00312,ORA-00742等错误

Fri Nov 25 11:24:53 2022
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Started redo scan
Completed redo scan
 read 900 KB redo, 386 data blocks need recovery
Started redo application at
 Thread 1: logseq 93214, block 60163
Recovery of Online Redo Log: Thread 1 Group 1 Seq 93214 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG
Completed redo application of 0.46MB
Fri Nov 25 11:25:02 2022
Hex dump of (file 3, block 208) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p004_1988.trc
Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\XFF\UNDOTBS01.DBF' for corruption at rdba: 0x00c000d0 (file 3, block 208)
Reread (file 3, block 208) found valid data
Hex dump of (file 3, block 208) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p004_1988.trc
Repaired corruption at (file 3, block 208)
Hex dump of (file 3, block 152) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p004_1988.trc
Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\XFF\UNDOTBS01.DBF' for corruption at rdba: 0x00c00098 (file 3, block 152)
Reread (file 3, block 152) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 152 OF FILE 3
Fri Nov 25 11:25:02 2022
Hex dump of (file 3, block 6859) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p001_19268.trc
Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\XFF\UNDOTBS01.DBF' for corruption at rdba: 0x00c01acb (file 3, block 6859)
Reread (file 3, block 6859) found same corrupt data (logically corrupt)
Fri Nov 25 11:25:13 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p010_7024.trc  (incident=224379):
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\incident\incdir_224379\XFF_p010_7024_i224379.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Nov 25 11:25:13 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p005_12036.trc  (incident=224343):
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\incident\incdir_224343\XFF_p005_12036_i224343.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Nov 25 11:25:18 2022
Sweep [inc][224379]: completed
Sweep [inc][224343]: completed
Sweep [inc2][224379]: completed
Sweep [inc2][224343]: completed
RECOVERY OF THREAD 1 STUCK AT BLOCK 6859 OF FILE 3
Fri Nov 25 11:25:33 2022
Slave exiting with ORA-1172 exception
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p004_1988.trc:
ORA-01172: 线程 1 的恢复停止在块 152 (在文件 3 中)
ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份
Fri Nov 25 11:25:34 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p001_19268.trc:
ORA-10388: parallel query server interrupt (failure)
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p001_19268.trc:
ORA-10388: parallel query server interrupt (failure)
Fri Nov 25 11:25:38 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p010_7024.trc:
ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况
ORA-00312: 联机日志 1 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG'
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Fri Nov 25 11:25:41 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p005_12036.trc  (incident=224344):
ORA-01578: ORACLE 数据块损坏 (文件号 27, 块号 520567)
ORA-01110: 数据文件 27: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMPP.DBF'
ORA-10564: tablespace POWERMPP
ORA-01110: 数据文件 27: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMPP.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 89776
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\incident\incdir_224344\XFF_p005_12036_i224344.trc
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p005_12036.trc:
ORA-01578: ORACLE 数据块损坏 (文件号 27, 块号 520567)
ORA-01110: 数据文件 27: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMPP.DBF'
ORA-10564: tablespace POWERMPP
ORA-01110: 数据文件 27: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMPP.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 89776
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p010_7024.trc  (incident=224380):
ORA-01578: ORACLE 数据块损坏 (文件号 26, 块号 227101)
ORA-01110: 数据文件 26: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMSP.DBF'
ORA-10564: tablespace POWERMSP
ORA-01110: 数据文件 26: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMSP.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 99375
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Fri Nov 25 11:25:51 2022
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\incident\incdir_224380\XFF_p010_7024_i224380.trc
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p010_7024.trc:
ORA-01578: ORACLE 数据块损坏 (文件号 26, 块号 227101)
ORA-01110: 数据文件 26: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMSP.DBF'
ORA-10564: tablespace POWERMSP
ORA-01110: 数据文件 26: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMSP.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 99375
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Fri Nov 25 11:25:54 2022
Aborting crash recovery due to slave death, attempting serial crash recovery
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 900 KB redo, 386 data blocks need recovery
Started redo application at
 Thread 1: logseq 93214, block 60163
Recovery of Online Redo Log: Thread 1 Group 1 Seq 93214 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG
Hex dump of (file 3, block 6743) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_ora_4172.trc
Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\XFF\UNDOTBS01.DBF' for corruption at rdba: 0x00c01a57 (file 3, block 6743)
Reread (file 3, block 6743) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 6743 OF FILE 3
Fri Nov 25 11:26:09 2022
Aborting crash recovery due to error 1172
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_ora_4172.trc:
ORA-01172: 线程 1 的恢复停止在块 6743 (在文件 3 中)
ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_ora_4172.trc:
ORA-01172: 线程 1 的恢复停止在块 6743 (在文件 3 中)
ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份
ORA-1172 signalled during: alter database open...

尝试人工recover恢复,报ORA-00283 ORA-00742 ORA-00312错误

SQL> recover database;
ORA-00283: 恢复会话因错误而取消
ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况
ORA-00312: 联机日志 1 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG'

通过特殊这里之后recover库成功

SQL> recover database until cancel;
ORA-00279: 更改 47073228694 (在 11/25/2022 08:11:15 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\XFF\ARCHIVELOG\2022_11_25\O1_MF_1_932
14_%U_.ARC
ORA-00280: 更改 47073228694 (用于线程 1) 在序列 #93214 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG
已应用的日志。
完成介质恢复。

打开数据库报ORA-600 2662错误

使用oracle patch scn工具快速修改 open库成功

SQL> startup mount pfile='d:/pfile.txt'
ORACLE 例程已经启动。

Total System Global Area       1603411968 bytes
Fixed Size                        2281656 bytes
Variable Size                  1191186248 bytes
Database Buffers                402653184 bytes
Redo Buffers                      7290880 bytes
数据库装载完毕。
SQL> ALTER DATABASE OPEN;

数据库已更改。

然后逻辑导出数据,导入新库,完成数据迁移工作

ora-600 kccpb_sanity_check_2故障处理

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

标题：ora-600 kccpb_sanity_check_2故障处理

数据库启动报ORA-600 kccpb_sanity_check_2

SQL> startup mount pfile='d:/pfile.txt'
ORACLE 例程已经启动。

Total System Global Area 1258291200 bytes
Fixed Size                  1250548 bytes
Variable Size             243272460 bytes
Database Buffers         1006632960 bytes
Redo Buffers                7135232 bytes
ORA-00600: ??????, ??: [kccpb_sanity_check_2], [66014], [66011], [0x0], [], [],[], []

重建控制文件报错ora-600 kccsga_update_amx_1

SQL> CREATE CONTROLFILE REUSE DATABASE "zs" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 50
  3      MAXLOGMEMBERS 5
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 226
  7  LOGFILE
  8    GROUP 1 'd:\zs\redo01.log'  SIZE 50M,
  9    GROUP 2 'd:\zs\redo02.log'  SIZE 50M,
 10    GROUP 3 'd:\zs\redo03.log'  SIZE 50M
 11  DATAFILE
 12  'd:\zs\SYSAUX01.DBF',
………………
 22  'd:\zs\SYSTEM01.DBF',
 23  'd:\zs\UNDOTBS01.DBF',
 24  'd:\zs\USERS01.DBF'
 25  CHARACTER SET zhs16gbk
 26  ;
CREATE CONTROLFILE REUSE DATABASE "zs" NORESETLOGS  NOARCHIVELOG
*
第 1 行出现错误:
ORA-01503: CREATE CONTROLFILE ??
ORA-00600: ??????, ??: [kccsga_update_amx_1], [9], [2920], [292], [], [], [],[]

重启实例，重建ctl成功.尝试恢复库提示需要很久之前的日志，因为有两个数据文件scn异常

通过oracle recovery tools修改文件头

再次recover数据库成功顺利open库导出客户需要数据

ORA-600 3417和ORA-600 3005故障处理

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

标题：ORA-600 3417和ORA-600 3005故障处理

运行中的数据库突然报ORA-600 3417错误,lgwr进程异常数据库crash

Thu Nov 17 03:00:14 2022
Archived Log entry 23860 added for thread 2 sequence 1958 ID 0x6200e2f5 dest 1:
Thu Nov 17 03:13:11 2022
Auto-tuning: Shutting down background process GTX1
Thu Nov 17 04:00:02 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_lgwr_1740.trc  (incident=672186):
ORA-00600: 内部错误代码, 参数: [3417], [2], [0], [0], [0], [1], [2], [], [], [], [], []
Thu Nov 17 04:00:04 2022
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 D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_lgwr_1740.trc:
ORA-00600: 内部错误代码, 参数: [3417], [2], [0], [0], [0], [1], [2], [], [], [], [], []
LGWR (ospid: 1740): terminating the instance due to error 470

重启之后报ORA-600 3005错误,数据库启动失败

Thu Nov 17 04:03:09 2022
Successful mount of redo thread 2, with mount id 1648753015
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
Lost write protection disabled
Completed: ALTER DATABASE MOUNT /* db agent *//* {0:1:38} */
ALTER DATABASE OPEN /* db agent *//* {0:1:38} */
This instance was first to open
Beginning crash recovery of 1 threads
 parallel recovery started with 31 processes
Thu Nov 17 04:03:14 2022
Started redo scan
ORA-00600: ??????, ??: [3005], [1], [706], [10374], [0], [0], [], [], [], [], [], []
Thu Nov 17 04:03:15 2022
Reconfiguration started (old inc 14, new inc 16)
List of instances:
 1 2 (myinst: 2) 
 Global Resource Directory frozen
Thu Nov 17 04:03:15 2022
 Communication channels reestablished
Thu Nov 17 04:03:16 2022
 * domain 0 valid = 0 according to instance 1 
 Master broadcasted resource hash value bitmaps
 Non-local Process blocks cleaned out
Thu Nov 17 04:03:16 2022
 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Nov 17 04:03:16 2022
Sweep [inc][688298]: completed
Sweep [inc2][688298]: completed
Thu Nov 17 04:03:16 2022
 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Thu Nov 17 04:03:16 2022
 LMS 2: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
 Set master node info 
 Submitted all remote-enqueue requests
 Dwn-cvts replayed, VALBLKs dubious
 All grantable enqueues granted
 Post SMON to start 1st pass IR
 Submitted all GCS remote-cache requests
 Post SMON to start 1st pass IR
 Fix write in gcs resources
Reconfiguration complete
Abort recovery for domain 0
Aborting crash recovery due to error 600
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_ora_15352.trc:
ORA-00600: ??????, ??: [3005], [1], [706], [10374], [0], [0], [], [], [], [], [], []
Abort recovery for domain 0
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_ora_15352.trc:
ORA-00600: ??????, ??: [3005], [1], [706], [10374], [0], [0], [], [], [], [], [], []
ORA-600 signalled during: ALTER DATABASE OPEN /* db agent *//* {0:1:38} */...

尝试人工进行recover恢复库

SQL> recover database;
ORA-00279: 更改 310644203 (在 11/17/2022 01:00:05 生成) 对于线程 2 是必需的
ORA-00289: 建议:
+DATA/orcl/archivelog/2022_11_17/thread_2_seq_1956.22763.1120960801
ORA-00280: 更改 310644203 (用于线程 2) 在序列 #1956 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: 更改 310663747 (在 11/17/2022 02:00:01 生成) 对于线程 2 是必需的
ORA-00289: 建议:
+DATA/orcl/archivelog/2022_11_17/thread_2_seq_1957.22764.1120962585
ORA-00280: 更改 310663747 (用于线程 2) 在序列 #1957 中


ORA-10877: error signaled in parallel recovery slave


ORA-01112: 未启动介质恢复

通过查看alert日志确认由于ORA-00353错误导致recover database失败

Thu Nov 17 08:07:39 2022
ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 32 slaves
Thu Nov 17 08:07:41 2022
Recovery of Online Redo Log: Thread 1 Group 1 Seq 705 Reading mem 0
  Mem# 0: +DATA/orcl/onlinelog/group_1.261.1116409583
ORA-279 signalled during: ALTER DATABASE RECOVER  database  ...
Thu Nov 17 08:08:07 2022
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log +DATA/orcl/archivelog/2022_11_17/thread_2_seq_1956.22763.1120960801
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log +DATA/orcl/archivelog/2022_11_17/thread_2_seq_1957.22764.1120962585
Thu Nov 17 08:08:14 2022
Recovery of Online Redo Log: Thread 2 Group 4 Seq 1958 Reading mem 0
  Mem# 0: +DATA/orcl/onlinelog/group_4.266.1116409589
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_pr00_7116.trc  (incident=704315):
ORA-00353: 日志损坏接近块 20866 更改 310761542 时间 11/17/2022 03:00:04
ORA-00312: 联机日志 1 线程 1: '+DATA/orcl/onlinelog/group_1.261.1116409583'
Thu Nov 17 08:08:26 2022
Sweep [inc][704315]: completed
Thu Nov 17 08:08:27 2022
Media Recovery failed with error 354
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_pr00_7116.trc:
ORA-00283: 恢复会话因错误而取消
ORA-00354: 损坏重做日志块标头
ORA-00353: 日志损坏接近块 20866 更改 310761542 时间 11/17/2022 03:00:04
ORA-00312: 联机日志 1 线程 1: '+DATA/orcl/onlinelog/group_1.261.1116409583'
Thu Nov 17 08:08:27 2022
ORA-10877 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
ALTER DATABASE RECOVER CANCEL 
ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...

通过对redo进行处理顺利recover成功并完美open库

SQL> recover database;
完成介质恢复。
SQL> alter database open;

数据库已更改。

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