ORA-00742 ORA-00312 恢复

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

标题:ORA-00742 ORA-00312 恢复

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

有客户反馈,断电之后数据库启动报ORA-00742和ORA-00312,无法正常open
ORA-742-ORA-312


我们远程上去尝试open库结果也报同样错误

[oracle@oldhis oradata]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 10 09:40:03 2024

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover database;
Media recovery complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00742: Log read detects lost write in thread %d sequence %d block %d
ORA-00312: online log 3 thread 1: '/oradata/shrdh/redo03.log'


SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         3 CURRENT
         2 INACTIVE

因为recover已经成功,但是依旧报ORA-742错误,尝试查询scn相关信息

SQL> set pages 10000
set numw 16
SELECT status,
checkpoint_change#,
checkpoint_time,last_change#,
count(*) ROW_NUM
FROM v$datafile
GROUP BY status, checkpoint_change#, checkpoint_time,last_change#
ORDER BY status, checkpoint_change#, checkpoint_time;


set numw 16
col CHECKPOINT_TIME for a40
set lines 150
set pages 1000
SELECT status,
to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time,FUZZY,checkpoint_change#,
count(*) ROW_NUM
FROM v$datafile_header
GROUP BY status, checkpoint_change#, to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss'),fuzzy
ORDER BY status, checkpoint_change#, checkpoint_time;

SQL> SQL>   2    3    4    5    6    7  
STATUS  CHECKPOINT_CHANGE# CHECKPOIN     LAST_CHANGE#          ROW_NUM
------- ------------------ --------- ---------------- ----------------
ONLINE          1279351848 26-MAR-24       1279351848               19
SYSTEM          1279351848 26-MAR-24       1279351848                1

SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6  
STATUS  CHECKPOINT_TIME                          FUZ CHECKPOINT_CHANGE#          ROW_NUM
------- ---------------------------------------- --- ------------------ ----------------
ONLINE  2024-03-26 00:05:45                      NO          1279351848               20

基于这样的情况,我们判断数据库直接open成功

SQL> recover database using backup controlfile;
ORA-00279: change 1279351848 generated at 03/26/2024 00:05:45 needed for thread 1
ORA-00289: suggestion : /oradata/arch/shrdh/shrdh_1_12984_974767526.arc
ORA-00280: change 1279351848 for thread 1 is in sequence #12984


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradata/shrdh/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

后面比较不幸,数据库报ORA-600 4194错误导致数据库异常

Wed Apr 10 09:43:08 2024
ALTER DATABASE RECOVER  database using backup controlfile  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 4 slaves
ORA-279 signalled during: ALTER DATABASE RECOVER  database using backup controlfile  ...
Wed Apr 10 09:43:24 2024
ALTER DATABASE RECOVER    LOGFILE '/oradata/shrdh/redo03.log'  
Media Recovery Log /oradata/shrdh/redo03.log
Media Recovery Complete (shrdh)
Completed: ALTER DATABASE RECOVER    LOGFILE '/oradata/shrdh/redo03.log'  
alter database open resetlogs
RESETLOGS after complete recovery through change 1279351849
Clearing online redo logfile 1 /oradata/shrdh/redo01.log
Clearing online log 1 of thread 1 sequence number 12982
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /oradata/shrdh/redo02.log
Clearing online log 2 of thread 1 sequence number 12983
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /oradata/shrdh/redo03.log
Clearing online log 3 of thread 1 sequence number 12984
Clearing online redo logfile 3 complete
Resetting resetlogs activation ID 1820377766 (0x6c80c2a6)
Online log /oradata/shrdh/redo01.log: Thread 1 Group 1 was previously cleared
Online log /oradata/shrdh/redo02.log: Thread 1 Group 2 was previously cleared
Online log /oradata/shrdh/redo03.log: Thread 1 Group 3 was previously cleared
Wed Apr 10 09:43:34 2024
Setting recovery target incarnation to 2
Wed Apr 10 09:43:34 2024
Assigning activation ID 2011515185 (0x77e54931)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /oradata/shrdh/redo01.log
Successful open of redo thread 1
Wed Apr 10 09:43:34 2024
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Apr 10 09:43:34 2024
SMON: enabling cache recovery
[25089] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:1273646224 end:1273646494 diff:270 (2 seconds)
Dictionary check beginning
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
replication_dependency_tracking turned off (no async multimaster replication found)
Errors in file /u01/app/oracle/diag/rdbms/shrdh/shrdh/trace/shrdh_smon_21704.trc  (incident=84296):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/shrdh/shrdh/incident/incdir_84296/shrdh_smon_21704_i84296.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Starting background process QMNC
Wed Apr 10 09:43:35 2024
QMNC started with pid=24, OS id=25340 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Block recovery from logseq 1, block 61 to scn 1279351933
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /oradata/shrdh/redo01.log
Block recovery stopped at EOT rba 1.99.16
Block recovery completed at rba 1.99.16, scn 0.1279351933
Block recovery from logseq 1, block 61 to scn 1279351919
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /oradata/shrdh/redo01.log
Block recovery completed at rba 1.87.16, scn 0.1279351922
Errors in file /u01/app/oracle/diag/rdbms/shrdh/shrdh/trace/shrdh_smon_21704.trc:
ORA-01595: error freeing extent (2) of rollback segment (7))
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Completed: alter database open resetlogs
Wed Apr 10 09:43:37 2024
Errors in file /u01/app/oracle/diag/rdbms/shrdh/shrdh/trace/shrdh_m000_25343.trc  (incident=84392):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/shrdh/shrdh/incident/incdir_84392/shrdh_m000_25343_i84392.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Wed Apr 10 09:43:37 2024
Starting background process CJQ0
Wed Apr 10 09:43:37 2024
CJQ0 started with pid=29, OS id=25357 
Starting background process SMCO
Wed Apr 10 09:43:37 2024
SMCO started with pid=30, OS id=25360 
Wed Apr 10 09:43:38 2024
Flush retried for xcb 0x115b42d28, pmd 0x1148dea70
Block recovery from logseq 1, block 61 to scn 1279351933
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /oradata/shrdh/redo01.log
Block recovery completed at rba 1.99.16, scn 0.1279351934
Errors in file /u01/app/oracle/diag/rdbms/shrdh/shrdh/trace/shrdh_pmon_21679.trc  (incident=84208):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/shrdh/shrdh/incident/incdir_84208/shrdh_pmon_21679_i84208.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/shrdh/shrdh/trace/shrdh_pmon_21679.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
PMON (ospid: 21679): terminating the instance due to error 472
Wed Apr 10 09:43:47 2024
Instance terminated by PMON, pid = 21679

报错比较明显,对undo进行处理即可.

Control file mount id mismatch!故障处理

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

标题:Control file mount id mismatch!故障处理

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

通过沟通确认客户由于存储双活异常,业务运行在主存储上,另外一套存储修复之后,进行存储双活同步,结果在这个过程中由于遭遇Control file mount id mismatch! 导致数据库crash了

2023-05-03T20:21:07.446873+08:00
Archived Log entry 491897 added for T-1.S-246903 ID 0x97d92f0b LAD:1
2023-05-03T20:47:53.902701+08:00
Error: 2141
Control file mount id mismatch!
fhmid: 2592441863, SGA mid: 2624617448
Requesting DIAG on each RAC instance to dump the control file header block
2023-05-03T20:47:55.906490+08:00
Errors in file /opt/rac/oracle/diag/rdbms/xff/xff1/trace/xff1_rms0_20989.trc:
2023-05-03T20:47:56.521500+08:00
RMS0 (ospid: 20989): terminating the instance
2023-05-03T20:47:56.610656+08:00
System state dump requested by (instance=1, osid=20989 (RMS0)), summary=[abnormal instance termination].
System State dumped to trace file /opt/rac/oracle/diag/rdbms/xff/xff1/trace/xff1_diag_20912_20230503204756.trc
2023-05-03T20:47:58.480397+08:00
License high water mark = 395
2023-05-03T20:48:02.600203+08:00
Instance terminated by RMS0, pid = 20989
2023-05-03T20:48:02.601563+08:00
Warning: 2 processes are still attach to shmid 393226:
 (size: 28672 bytes, creator pid: 19941, last attach/detach pid: 20912)
2023-05-03T20:48:03.481726+08:00
USER (ospid: 967): terminating the instance
2023-05-03T20:48:03.483351+08:00
Instance terminated by USER, pid = 967

节点自动重启报错ORA-600 kccsbck_first

2023-05-03T20:48:34.870435+08:00
NOTE: ASMB mounting group 2 (FRA)
NOTE: ASM background process initiating disk discovery for grp 2 (reqid:0)
NOTE: Assigning number (2,1) to disk (/dev/asm_data0g)
NOTE: Assigning number (2,0) to disk (/dev/asm_data0f)
SUCCESS: mounted group 2 (FRA)
NOTE: grp 2 disk 1: FRA_0001 path:/dev/asm_data0g
NOTE: grp 2 disk 0: FRA_0000 path:/dev/asm_data0f
2023-05-03T20:48:34.919965+08:00
NOTE: dependency between database xff and diskgroup resource ora.FRA.dg is established
2023-05-03T20:48:38.983416+08:00
Errors in file /opt/rac/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_2436.trc  (incident=1333249):
ORA-00600: ??????, ??: [kccsbck_first], [1], [2624617448], [], [], [], [], [], [], [], [], []
Incident details in: /opt/rac/oracle/diag/rdbms/xff/xff1/incident/incdir_1333249/xff1_ora_2436_i1333249.trc
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 /* db agent *//* {0:8:116} */...

再次重启数据库报错ORA-00742 ORA-00312

2023-05-04T08:18:59.635790+08:00
Aborting crash recovery due to error 742
2023-05-04T08:18:59.635897+08:00
Errors in file /opt/rac/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_80855.trc:
ORA-00742: ??????? 2 ?? 244996 ? 8262 ??????????
ORA-00312: ???? 7 ?? 2: '+FRA/xff/ONLINELOG/group_7.446.1059323695'
ORA-00312: ???? 7 ?? 2: '+DATA/xff/ONLINELOG/group_7.272.1059323695'
Abort recovery for domain 0, flags 4
2023-05-04T08:18:59.647994+08:00
Errors in file /opt/rac/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_80855.trc:
ORA-00742: ??????? 2 ?? 244996 ? 8262 ??????????
ORA-00312: ???? 7 ?? 2: '+FRA/xff/ONLINELOG/group_7.446.1059323695'
ORA-00312: ???? 7 ?? 2: '+DATA/xff/ONLINELOG/group_7.272.1059323695'
ORA-742 signalled during: ALTER DATABASE OPEN /* db agent *//* {2:37368:2} */...
2023-05-04T08:19:00.820708+08:00
License high water mark = 33
2023-05-04T08:19:00.820936+08:00
USER (ospid: 82788): terminating the instance
2023-05-04T08:19:01.827132+08:00
Instance terminated by USER, pid = 82788

明显数据库在启动的时候做实例恢复,发现redo写丢失,从而引起数据库无法正常open,对于此类故障,处理比较多
ORA-00742 ORA-00312 故障恢复-1
ORA-00742 ORA-00312故障恢复-2
ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况

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

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

标题: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错误
20221125215429


使用oracle patch scn工具快速修改 open库成功
patch_scn-ora-600-2662

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-00742 ORA-00312故障恢复

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

标题:ORA-00742 ORA-00312故障恢复

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

11.2.0.4版本数据库启动报ORA-00742 ORA-00312错误

Fri Feb 25 15:02:27 2022
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 2001521283
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Fri Feb 25 15:02:31 2022
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 11 processes
Started redo scan
Aborting crash recovery due to error 742
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_98720.trc:
ORA-00742: Log read detects lost write in thread %d sequence %d block %d
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/xifenfei/redo01.log'
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_98720.trc:
ORA-00742: Log read detects lost write in thread %d sequence %d block %d
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/xifenfei/redo01.log'
ORA-742 signalled during: ALTER DATABASE OPEN...

现场人员进行错误恢复尝试

Fri Feb 25 15:17:17 2022
ALTER DATABASE RECOVER    CANCEL  
Fri Feb 25 15:17:17 2022
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_pr00_130027.trc:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/xifenfei/system01.dbf'
ORA-1547 signalled during: ALTER DATABASE RECOVER    CANCEL  ...
ALTER DATABASE RECOVER CANCEL 
ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...

通过一些人工修复异常redo之后,数据库顺利直接open成功

ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 12 slaves
Sat Feb 26 09:45:07 2022
Recovery of Online Redo Log: Thread 1 Group 1 Seq 5281 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/xifenfei/redo01.log
Sat Feb 26 09:45:08 2022
Media Recovery Complete (xifenfei)
Completed: ALTER DATABASE RECOVER  database  
Sat Feb 26 09:45:25 2022
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 11 processes
Started redo scan
Completed redo scan
 read 4861 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 5281, block 2, scn 262332375
Recovery of Online Redo Log: Thread 1 Group 1 Seq 5281 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/xifenfei/redo01.log
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 5281, block 9725, scn 262353916
 0 data blocks read, 0 data blocks written, 4861 redo k-bytes read
Initializing SCN for created control file
Database SCN compatibility initialized to 3
Warning - High Database SCN: Current SCN value is 262353917, threshold SCN value is 0
If you have not previously reported this warning on this database, 
please notify Oracle Support so that additional diagnosis can be performed.
Sat Feb 26 09:45:25 2022
Thread 1 advanced to log sequence 5282 (thread open)
Thread 1 opened at log sequence 5282
  Current log# 2 seq# 5282 mem# 0: /u01/app/oracle/oradata/xifenfei/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Feb 26 09:45:25 2022
SMON: enabling cache recovery
[111600] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:69076924 end:69077284 diff:360 (3 seconds)
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
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sat Feb 26 09:45:26 2022
QMNC started with pid=20, OS id=116832 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open

数据库正常open,数据0丢失,数据库可以直接使用

redo异常强制拉库报ORA-600 kcbzib_kcrsds_1修复

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

标题:redo异常强制拉库报ORA-600 kcbzib_kcrsds_1修复

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

节点2 asm dismount导致redo写报错(ORA-00340,ORA-00345),经过分析asm和系统日志,确认是由于多路径异常导致io异常

2022-01-24T23:44:39.966602+08:00
WARNING: group 4 is being dismounted.
WARNING: ASMB force dismounting group 4 (REDO) due to ASM server dismount
SUCCESS: diskgroup REDO was dismounted
2022-01-24T23:44:41.103783+08:00
Errors in file /u01/app/oracle/diag/rdbms/xff/XFF2/trace/XFF2_lgwr_228507.trc:
ORA-00345: redo log write error block 11961764 count 6
ORA-00312: online log 10 thread 2: '+REDO/XFF/ONLINELOG/group_10.261.1074690685'
2022-01-24T23:44:41.156809+08:00
Errors in file /u01/app/oracle/diag/rdbms/xff/XFF2/trace/XFF2_lgwr_228507.trc:
ORA-00340: IO error processing online log 10 of thread 2
ORA-00345: redo log write error block 11961764 count 6
ORA-00312: online log 10 thread 2: '+REDO/XFF/ONLINELOG/group_10.261.1074690685'
Errors in file /u01/app/oracle/diag/rdbms/xff/XFF2/trace/XFF2_lgwr_228507.trc  (incident=1341402):
ORA-340 [] [] [] [] [] [] [] [] [] [] [] []
Incident details in: /u01/app/oracle/diag/rdbms/xff/XFF2/incident/incdir_1341402/XFF2_lgwr_228507_i1341402.trc
2022-01-24T23:44:41.505251+08:00
USER (ospid: 133928): terminating the instance due to error 340

由于节点2是突然crash,节点1做实例恢复失败,由于节点2的redo发生了写丢失,导致节点1实例恢复后库crash,进而是的该集群的相关数据库节点全部crash

2022-01-24T23:46:08.440519+08:00
Slave encountered ORA-10388 exception during crash recovery
2022-01-24T23:46:08.442854+08:00
Slave encountered ORA-10388 exception during crash recovery
Abort recovery for domain 0, flags 4
2022-01-24T23:46:08.444531+08:00
Aborting crash recovery due to error 742
2022-01-24T23:46:08.444695+08:00
Errors in file /u01/app/oracle/diag/rdbms/xff/XFF1/trace/XFF1_ora_450481.trc:
ORA-00742: Log read detects lost write in thread 2 sequence 63507 block 11941482
ORA-00312: online log 10 thread 2: '+REDO/XFF/ONLINELOG/group_10.261.1074690685'
Abort recovery for domain 0, flags 4
2022-01-24T23:46:08.771108+08:00
Errors in file /u01/app/oracle/diag/rdbms/xff/XFF1/trace/XFF1_ora_450481.trc:
ORA-00742: Log read detects lost write inthread 2 sequence 63507 block 11941482
ORA-00312: online log 10 thread 2: '+REDO/XFF/ONLINELOG/group_10.261.1074690685'
ORA-742 signalled during: ALTER DATABASE OPEN /* db agent *//* {0:17:165} */...
2022-01-24T23:46:10.143155+08:00
License high water mark = 33
2022-01-24T23:46:10.143752+08:00
USER (ospid: 451049): terminating the instance
2022-01-24T23:46:11.167337+08:00
Instance terminated by USER, pid = 451049

经过第三方强制拉库之后,数据库报ORA-600 kcbzib_kcrsds_1

2022-01-25T10:13:37.922332+08:00
Completed crash recovery at
 Thread 2: RBA 5.3.16, nab 3, scn 0x00000a348a032122
 0 data blocks read, 0 data blocks written, 0 redo k-bytes read
2022-01-25T10:13:38.071326+08:00
Thread 2 advanced to log sequence 6 (thread recovery)
validate pdb 0, flags x4, valid 0, pdb flags x204 
* validated domain 0, flags = 0x200
CRASH recovery complete: pdb 0 valid 1 (flags x4, pdb flags x200) 
Picked broadcast on commit scheme to generate SCNs
Endian type of dictionary set to little
2022-01-25T10:13:38.389741+08:00
TT00: Gap Manager starting (PID:220505)
2022-01-25T10:13:38.646484+08:00
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: +DATA/XFF/ONLINELOG/group_1.536.1094875107
Successful open of redo thread 1
2022-01-25T10:13:38.647243+08:00
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Errors in file /u01/app/oracle/diag/rdbms/xff/XFF1/trace/XFF1_ora_216590.trc  (incident=1879556):
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/xff/XFF1/incident/incdir_1879556/XFF1_ora_216590_i1879556.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************
2022-01-25T10:13:39.734366+08:00
Errors in file /u01/app/oracle/diag/rdbms/xff/XFF1/trace/XFF1_ora_216590.trc:
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
2022-01-25T10:13:39.734424+08:00
Errors in file /u01/app/oracle/diag/rdbms/xff/XFF1/trace/XFF1_ora_216590.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
2022-01-25T10:13:39.734499+08:00
Errors in file /u01/app/oracle/diag/rdbms/xff/XFF1/trace/XFF1_ora_216590.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
2022-01-25T10:13:39.734536+08:00
Error 704 happened during db open, shutting down database
Errors in file /u01/app/oracle/diag/rdbms/xff/XFF1/trace/XFF1_ora_216590.trc  (incident=1879557):
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/xff/XFF1/incident/incdir_1879557/XFF1_ora_216590_i1879557.trc
2022-01-25T10:13:39.894464+08:00
2022-01-25T10:13:40.446888+08:00
opiodr aborting process unknown ospid (216590) as a result of ORA-603
2022-01-25T10:13:40.470643+08:00
ORA-603 : opitsk aborting process
License high water mark = 36
2022-01-25T10:13:40.471453+08:00
USER (ospid: 216590): terminating the instance due to error 704
2022-01-25T10:13:41.436133+08:00
opiodr aborting process unknown ospid (189796) as a result of ORA-1092
2022-01-25T10:13:41.439011+08:00
ORA-1092 : opitsk aborting process
2022-01-25T10:13:41.472060+08:00
PMON (ospid: 189585): terminating the instance due to error 704

该错误是12c之后才有的报错,由于文件异常导致,通过以前的解决经验,接手这个问题之后快速调整数据库文件头信息,顺利open库
参考以前相关blog内容:
Oracle 12c redo 丢失恢复
模拟19c数据库redo异常恢复
ORA-600 kcbzib_kcrsds_1报错
12C数据库报ORA-600 kcbzib_kcrsds_1故障处理
ORA-00603 ORA-01092 ORA-600 kcbzib_kcrsds_1

[oracle@xifenfei02 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 26 00:31:50 2022

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

Connected to an idle instance.

SQL> startup mount pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 3.2320E+11 bytes
Fixed Size                 29879248 bytes
Variable Size            4.5634E+10 bytes
Database Buffers         1.9059E+11 bytes
Redo Buffers             1043861504 bytes
In-Memory Area           8.5899E+10 bytes
Database mounted.
SQL> alter database open ;

Database altered.