标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-00742 ORA-01110 ORA-01555 ORA-01578 ORA-01595 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (103)
- 数据库 (1,748)
- DB2 (22)
- MySQL (75)
- Oracle (1,594)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (162)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (68)
- Oracle Bug (8)
- Oracle RAC (54)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (584)
- Oracle安装升级 (96)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (84)
- PostgreSQL (30)
- pdu工具 (6)
- PostgreSQL恢复 (9)
- SQL Server (30)
- SQL Server恢复 (11)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (38)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (21)
-
最近发表
- Oracle 19c 202504补丁(RUs+OJVM)-19.27
- Oracle Recovery Tools修复ORA-600 6101/kdxlin:psno out of range故障
- pdu完美支持金仓数据库恢复(KingbaseES)
- 虚拟机故障引起ORA-00310 ORA-00334故障处理
- pg创建gbk字符集库
- PostgreSQL运行日志管理
- ora-600 kdsgrp1 错误描述
- GAM、SGAM 或 PFS 页上存在页错误处理
- ORA-600 krhpfh_03-1208
- VMware勒索加密恢复(vmdk勒索恢复)
- ORA-39773: parse of metadata stream failed故障处理
- sql数据库备份失败—失败: 23(数据错误(循环冗余检查)
- vmdk文件被加密恢复(虚拟机文件加密)
- 差点被误操作的ORA-600 kcratr_nab_less_than_odr故障
- win平台19c 打patch遭遇2个小问题汇总
- pg单个数据库目录恢复-pdu恢复单个数据库目录数据
- pg删除数据恢复—pdu恢复pg delete数据
- .[OnlyBuy@cyberfear.com].REVRAC勒索mysql恢复
- 表dml操作权限授权给public,导致只读用户失效
- 21c数据库恢复遭遇ora-600 ktugct: corruption detected
标签归档:ORA-01172 ORA-01151
虚拟机故障引起ORA-00310 ORA-00334故障处理
有客户由于硬件底层问题,导致运行在虚拟机环境中的oracle数据库突然爆大量错误
Reread (file 5, block 2371528) found same corrupt data (no logical check) Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_j000_10927.trc (incident=397049): ORA-01578: ORACLE data block corrupted (file # 5, block # 2371528) ORA-01110: data file 5: '/home/oracle/app/oradata/users01.dbf' Wed Apr 02 23:10:24 2025 Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_j000_10927.trc (incident=397050): ORA-00600: internal error code, arguments: [5400], [], [], [], [], [], [], [], [], [], [], [] Wed Apr 02 23:15:29 2025 Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_11605.trc (incident=397075): ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], [], [], [], [], [] Wed Apr 02 23:20:32 2025 Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_11530.trc (incident=397034): ORA-00600: internal error code, arguments: [25027], [6], [196610], [], [], [], [], [], [], [], [], [] Wed Apr 02 23:20:52 2025 Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_11528.trc (incident=397027): ORA-00600: internal error code, arguments: [ktspfpblk:kcbz_objdchk], [0], [0], [1], [], [], [], [], [], [], [], [] Wed Apr 02 23:22:53 2025 Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_11609.trc (incident=397082): ORA-00600: internal error code, arguments: [6002], [6], [189], [1], [0], [], [], [], [], [], [], [] Wed Apr 02 23:26:41 2025 Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_m000_11966.trc (incident=397035): ORA-00600: internal error code, arguments: [dbgrmblur_update_range_1], [11], [6], [], [], [], [], [], [], [], [], [] Wed Apr 02 23:31:47 2025 Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_j000_10927.trc: ORA-12012: error on auto execute of job "SYS"."ORA$AT_SA_SPC_SY_49685" ORA-08102: index key not found, obj# 39, file 1, block 55190 (2) Thu Apr 03 00:15:18 2025 Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x8] [PC:0xB9EC41, ksuloget()+421] [flags: 0x0, count: 1] Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_m000_12633.trc (incident=400879): ORA-07445: exception encountered:core dump [ksuloget()+421][SIGSEGV][ADDR:0x8][PC:0xB9EC41][Address not mapped to object] Thu Apr 03 00:15:23 2025 Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_pmon_4097.trc (incident=396817): ORA-00600: internal error code, arguments: [1100], [0x2E3947E78], [0x2E3947E78], [], [], [], [], [], [], [], [], []
数据库crash掉之后,处理好硬件环境和虚拟机启动之后,数据库直接启动失败,报ORA-01172 ORA-01151
Beginning crash recovery of 1 threads Started redo scan Completed redo scan read 29239 KB redo, 4020 data blocks need recovery Started redo application at Thread 1: logseq 211603, block 9107 Recovery of Online Redo Log: Thread 1 Group 4 Seq 211603 Reading mem 0 Mem# 0: /home/oracle/app/oradata/orcl/redo04.log Mem# 1: /home/oracle/app/oradata/orcl/redo041.log Hex dump of (file 2, block 4835) in trace file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_19174.trc Reading datafile '/home/oracle/app/oradata/orcl/sysaux01.dbf' for corruption at rdba: 0x008012e3 (file 2, block 4835) Reread (file 2, block 4835) found same corrupt data (logically corrupt) RECOVERY OF THREAD 1 STUCK AT BLOCK 4835 OF FILE 2 Aborting crash recovery due to error 1172 Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_19174.trc: ORA-01172: recovery of thread 1 stuck at block 4835 of file 2 ORA-01151: use media recovery to recover block, restore backup if needed Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_19174.trc: ORA-01172: recovery of thread 1 stuck at block 4835 of file 2 ORA-01151: use media recovery to recover block, restore backup if needed ORA-1172 signalled during: ALTER DATABASE OPEN...
然后再次尝试重启提示ORA-01113 ORA-01110
Fri Apr 04 09:34:36 2025 ALTER DATABASE OPEN Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_4076.trc: ORA-01113: file 5 needs media recovery ORA-01110: data file 5: '/home/oracle/app/oradata/users01.dbf' ORA-1113 signalled during: ALTER DATABASE OPEN...
可以自行尝试了各种恢复,比如using backup controlfile,until cancel,rectl等操作,数据库均为open成功,基本上都是卡在类似如下报ORA-00310 ORA-00334错
Sat Apr 05 10:17:34 2025 ALTER DATABASE RECOVER database using backup controlfile Media Recovery Start started logmerger process Sat Apr 05 10:17:34 2025 WARNING! Recovering data file 1 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 2 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 3 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 4 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 5 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 6 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 7 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 8 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 9 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 10 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 11 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 12 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 13 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 14 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 15 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. Parallel Media Recovery started with 28 slaves ORA-279 signalled during: ALTER DATABASE RECOVER database using backup controlfile ... Sat Apr 05 10:17:59 2025 ALTER DATABASE RECOVER LOGFILE '/home/oradata/redo02.log' Media Recovery Log /home/oradata/redo02.log Sat Apr 05 10:17:59 2025 Errors with log /home/oradata/redo02.log Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_pr00_12141.trc: ORA-00310: archived log contains sequence 211550; sequence 211603 required ORA-00334: archived log: '/home/oradata/redo02.log' ORA-310 signalled during: ALTER DATABASE RECOVER LOGFILE '/home/oradata/redo02.log' ... ALTER DATABASE RECOVER CANCEL Media Recovery Canceled Completed: ALTER DATABASE RECOVER CANCEL
基于上述情况,数据库由于底层异常,导致所需要的redo和实际存在的redo文件内容不匹配,只能屏蔽一致性强制打开库
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], [1685409503], [0], [1685415469], [12583040], [] ORA-00600: internal error code, arguments: [2662], [0], [1685409502], [0], [1685415469], [12583040], [] ORA-01092: ORACLE instance terminated. Disconnection force ORA-00600: internal error code, arguments: [2662], [0], [1685409498], [0], [1685415469], [12583040], [] Process ID: 10637 Session ID: 645 Serial number: 7
ORA-600 2662这个错误比较常见,通过修改数据库scn,进行规避然后尝试打开库
Sat Apr 05 10:31:45 2025 alter database open resetlogs RESETLOGS is being done without consistancy checks. This may result in a corrupted database. The database should be recreated. RESETLOGS after incomplete recovery UNTIL CHANGE 1685409495 Resetting resetlogs activation ID 1725417463 (0x66d7c7f7) Sat Apr 05 10:31:46 2025 Setting recovery target incarnation to 2 Initializing SCN for created control file Database SCN compatibility initialized to 3 Warning - High Database SCN: Current SCN value is 1685409498, threshold SCN value is 0 Sat Apr 05 10:31:46 2025 Assigning activation ID 1725412798 (0x66d7b5be) Thread 1 opened at log sequence 1 Current log# 2 seq# 1 mem# 0: /home/oradata/redo02.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Sat Apr 05 10:31:46 2025 SMON: enabling cache recovery Undo initialization finished serial:0 start:61632504 end:61632514 diff:10 (0 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 ********************************************************************* WARNING: The following temporary tablespaces contain no files. This condition can occur when a backup controlfile has SMON: enabling tx recovery 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 AL32UTF8 Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_smon_22927.trc (incident=8145): ORA-00600: internal error code, arguments: [4137], [9.1.436887], [0], [0], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/oorcl/oorcl/incident/incdir_8145/oorcl_smon_22927_i8145.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Stopping background process MMNL ORACLE Instance oorcl (pid = 17) - Error 600 encountered while recovering transaction (9, 1). Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_smon_22927.trc: ORA-00600: internal error code, arguments: [4137], [9.1.436887], [0], [0], [], [], [], [], [], [], [], [] Sat Apr 05 10:31:46 2025 Sweep [inc][8145]: completed Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_smon_22927.trc (incident=8146): ORA-00600: internal error code, arguments: [4137], [9.1.436887], [0], [0], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/oorcl/oorcl/incident/incdir_8146/oorcl_smon_22927_i8146.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Sat Apr 05 10:31:46 2025 Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_p054_2643.trc (incident=8625): ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/oorcl/oorcl/incident/incdir_8625/oorcl_p054_2643_i8625.trc Sat Apr 05 10:31:46 2025 Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_p034_2603.trc (incident=8465): ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/oorcl/oorcl/incident/incdir_8465/oorcl_p034_2603_i8465.trc replication_dependency_tracking turned off (no async multimaster replication found) LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Completed: alter database open resetlogs Sat Apr 05 10:31:48 2025 Starting background process CJQ0 Sat Apr 05 10:31:48 2025 CJQ0 started with pid=80, OS id=2852 SMON: Restarting fast_start parallel rollback Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_smon_22927.trc (incident=8147): ORA-00600: internal error code, arguments: [4137], [9.1.436887], [0], [0], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/oorcl/oorcl/incident/incdir_8147/oorcl_smon_22927_i8147.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Sat Apr 05 10:31:50 2025 Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_p000_2535.trc (incident=8169): ORA-00600: internal error code, arguments: [4198], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/oorcl/oorcl/incident/incdir_8169/oorcl_p000_2535_i8169.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. ORACLE Instance oorcl (pid = 17) - Error 600 encountered while recovering transaction (9, 1). Block recovery from logseq 1, block 19 to scn 2147483682 Recovery of Online Redo Log: Thread 1 Group 2 Seq 1 Reading mem 0 Mem# 0: /home/oradata/redo02.log Block recovery completed at rba 1.734.16, scn 0.2147483683 Block recovery from logseq 1, block 404 to scn 2147483682 Recovery of Online Redo Log: Thread 1 Group 2 Seq 1 Reading mem 0 Mem# 0: /home/oradata/redo02.log Block recovery completed at rba 1.734.16, scn 0.2147483683 Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_smon_22927.trc (incident=8148): ORA-00600: internal error code, arguments: [4198], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/oorcl/oorcl/incident/incdir_8148/oorcl_smon_22927_i8148.trc Sat Apr 05 10:31:50 2025 Sweep [inc][8147]: completed Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. SMON: Parallel transaction recovery slave got internal error SMON: Downgrading transaction recovery to serial Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_smon_22927.trc (incident=8149): ORA-00600: internal error code, arguments: [4137], [10.28.1201778], [0], [0], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/oorcl/oorcl/incident/incdir_8149/oorcl_smon_22927_i8149.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. ORACLE Instance oorcl (pid = 17) - Error 600 encountered while recovering transaction (10, 28). Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_smon_22927.trc: ORA-00600: internal error code, arguments: [4137], [10.28.1201778], [0], [0], [], [], [], [], [], [], [], [] Sat Apr 05 10:31:50 2025 Sweep [inc][8149]: completed Checker run found 1 new persistent data failures Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_smon_22927.trc (incident=8150): ORA-00600: internal error code, arguments: [4137], [10.28.1201778], [0], [0], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/oorcl/oorcl/incident/incdir_8150/oorcl_smon_22927_i8150.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. ORACLE Instance oorcl (pid = 17) - Error 600 encountered while recovering transaction (10, 28). Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_smon_22927.trc (incident=8151): ORA-00600: internal error code, arguments: [4137], [10.28.1201778], [0], [0], [], [], [], [], [], [], [], [] Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Sat Apr 05 10:31:51 2025 Sweep [inc][8150]: completed ORACLE Instance oorcl (pid = 17) - Error 600 encountered while recovering transaction (10, 28).
虽然数据库open成功,但是有ORA-600 4137/ORA-600 kturbleurec1/ORA-600 4198等错误,但是这里比较明显的undo有问题,对于异常undo进行处理,然后逻辑导出数据,导入新库完成本次恢复任务
在线mv方式迁移数据文件导致数据库无法正常启动
有客户在数据库没有关闭的情况下,直接操作系统层面mv方式把数据文件从一个分区迁移到另外一个分区,再创建ln -s(软连接)的方式实现数据文件不修改路径的方式数据文件迁移,结果数据库重启之后,库无法正常启动,报ORA-01172 ORA-01151错误
Fri Dec 29 09:49:19 2023 ALTER DATABASE OPEN Beginning crash recovery of 1 threads parallel recovery started with 11 processes Started redo scan Completed redo scan read 11591 KB redo, 1566 data blocks need recovery Started redo application at Thread 1: logseq 6320, block 479571 Recovery of Online Redo Log: Thread 1 Group 4 Seq 6320 Reading mem 0 Mem# 0: /data/oracle/oradata/orcl/redo04.log Fri Dec 29 09:49:19 2023 Hex dump of (file 6, block 3598593) in trace file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p002_6696.trc Fri Dec 29 09:49:19 2023 Fri Dec 29 09:49:19 2023 Hex dump of (file 5, block 27832) in trace file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p008_6708.trc Hex dump of (file 6, block 3598208) in trace file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p010_6712.trc Reading datafile '/oadate/xff/xff_01.dbf' for corruption at rdba: 0x01b6e901 (file 6, block 3598593) Reading datafile '/oadate/xff/xff.dbf' for corruption at rdba: 0x01406cb8 (file 5, block 27832) Reread (file 6, block 3598593) found same corrupt data (logically corrupt) Reading datafile '/oadate/xff/xff_01.dbf' for corruption at rdba: 0x01b6e780 (file 6, block 3598208) Reread (file 5, block 27832) found same corrupt data (logically corrupt) RECOVERY OF THREAD 1 STUCK AT BLOCK 3598593 OF FILE 6 Reread (file 6, block 3598208) found same corrupt data (logically corrupt) RECOVERY OF THREAD 1 STUCK AT BLOCK 3598208 OF FILE 6RECOVERY OF THREAD 1 STUCK AT BLOCK 27832 OF FILE 5 Fri Dec 29 09:49:32 2023 Slave exiting with ORA-1172 exception Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p010_6712.trc: ORA-01172: recovery of thread 1 stuck at block 3598208 of file 6 ORA-01151: use media recovery to recover block, restore backup if needed Fri Dec 29 09:49:32 2023 Fri Dec 29 09:49:32 2023 Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p008_6708.trc: ORA-10388: parallel query server interrupt (failure) Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p002_6696.trc: ORA-10388: parallel query server interrupt (failure) Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p008_6708.trc: ORA-10388: parallel query server interrupt (failure) Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p002_6696.trc: ORA-10388: parallel query server interrupt (failure) Fri Dec 29 09:49:32 2023 Aborting crash recovery due to slave death, attempting serial crash recovery Beginning crash recovery of 1 threads Started redo scan Completed redo scan read 11591 KB redo, 1566 data blocks need recovery Started redo application at Thread 1: logseq 6320, block 479571 Recovery of Online Redo Log: Thread 1 Group 4 Seq 6320 Reading mem 0 Mem# 0: /data/oracle/oradata/orcl/redo04.log Hex dump of (file 6, block 3598593) in trace file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6690.trc Reading datafile '/oadate/xff/xff_01.dbf' for corruption at rdba: 0x01b6e901 (file 6, block 3598593) Reread (file 6, block 3598593) found same corrupt data (logically corrupt) RECOVERY OF THREAD 1 STUCK AT BLOCK 3598593 OF FILE 6 Aborting crash recovery due to error 1172 Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6690.trc: ORA-01172: recovery of thread 1 stuck at block 3598593 of file 6 ORA-01151: use media recovery to recover block, restore backup if needed Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6690.trc: ORA-01172: recovery of thread 1 stuck at block 3598593 of file 6 ORA-01151: use media recovery to recover block, restore backup if needed ORA-1172 signalled during: ALTER DATABASE OPEN...
sqlplus恢复数据库报错
SQL> recover datafile 6; ORA-00283: recovery session canceled due to errors ORA-00600: internal error code, arguments: [3020], [6], [3578240], [28744064], [], [], [], [], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 6, block# 3578240, file offset is 3543138304 bytes) ORA-10564: tablespace xff ORA-01110: data file 6: '/oadate/xff/xff_01.dbf' ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
alert日志报ORA-600 3020错误
Fri Dec 29 17:43:03 2023 ALTER DATABASE RECOVER datafile 6 Media Recovery Start Serial Media Recovery started Recovery of Online Redo Log: Thread 1 Group 4 Seq 6320 Reading mem 0 Mem# 0: /data/oracle/oradata/orcl/redo04.log Fri Dec 29 17:43:42 2023 Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30140.trc (incident=462294): ORA-00600: internal error code, arguments: [3020], [6], [3578240], [28744064], [], [], [], [], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 6, block# 3578240, file offset is 3543138304 bytes) ORA-10564: tablespace XFF ORA-01110: data file 6: '/oadate/xff/xff_01.dbf' ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK' Incident details in: /data/oracle/diag/rdbms/orcl/orcl/incident/incdir_462294/orcl_ora_30140_i462294.trc Fri Dec 29 17:43:42 2023 Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Media Recovery failed with error 600 ORA-283 signalled during: ALTER DATABASE RECOVER datafile 6 ...
此类故障是由于在线拷贝数据文件,可能有不少最新写入的数据都有数据文件和redo不一致的风险,引起这里的ORA-600 3020最好不要通过allow N corruption的方式跳过,因为可能导致大量数据文件坏块,这样就不光丢失了redo数据,可能数据文件中好的block中的很多数据也丢失.对于这种情况,我们为了减少客户的数据丢失,选择了最少数据丢失的方法:通过bbed修改文件头,然后直接recover 数据文件,open库
Fri Dec 29 18:05:36 2023 ALTER DATABASE RECOVER datafile 5 Media Recovery Start Serial Media Recovery started Recovery of Online Redo Log: Thread 1 Group 4 Seq 6320 Reading mem 0 Mem# 0: /data/oracle/oradata/orcl/redo04.log Media Recovery Complete (orcl) Completed: ALTER DATABASE RECOVER datafile 5 ALTER DATABASE RECOVER datafile 6 Media Recovery Start Serial Media Recovery started Recovery of Online Redo Log: Thread 1 Group 4 Seq 6320 Reading mem 0 Mem# 0: /data/oracle/oradata/orcl/redo04.log Media Recovery Complete (orcl) Completed: ALTER DATABASE RECOVER datafile 6 Fri Dec 29 18:07:02 2023 ALTER DATABASE OPEN Beginning crash recovery of 1 threads parallel recovery started with 11 processes Started redo scan Completed redo scan read 11591 KB redo, 0 data blocks need recovery Started redo application at Thread 1: logseq 6320, block 479571 Recovery of Online Redo Log: Thread 1 Group 4 Seq 6320 Reading mem 0 Mem# 0: /data/oracle/oradata/orcl/redo04.log Completed redo application of 0.00MB Completed crash recovery at Thread 1: logseq 6320, block 502754, scn 2657849964 0 data blocks read, 0 data blocks written, 11591 redo k-bytes read Thread 1 advanced to log sequence 6321 (thread open) Thread 1 opened at log sequence 6321 Current log# 5 seq# 6321 mem# 0: /data/oracle/oradata/orcl/redo05.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set SMON: enabling cache recovery [2656] Successfully onlined Undo Tablespace 2. Undo initialization finished serial:0 start:933676634 end:933676704 diff:70 (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 AL32UTF8 No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Fri Dec 29 18:07:04 2023 QMNC started with pid=31, OS id=2687 Completed: ALTER DATABASE OPEN
然后逻辑方式迁移数据到新库中,最大程度抢救客户数据
ORA-01172 ORA-01151 故障恢复
节点2报Error: Controlfile sequence number in file header is different from the one in memory,导致实例异常
Tue May 09 23:03:24 2023 Thread 2 cannot allocate new log, sequence 16728 Checkpoint not complete Current log# 3 seq# 16727 mem# 0: +DATA/xff/onlinelog/group_3.265.941900045 Current log# 3 seq# 16727 mem# 1: +FRA/xff/onlinelog/group_3.259.941900045 Thread 2 advanced to log sequence 16728 (LGWR switch) Current log# 4 seq# 16728 mem# 0: +DATA/xff/onlinelog/group_4.266.941900045 Current log# 4 seq# 16728 mem# 1: +FRA/xff/onlinelog/group_4.260.941900045 Tue May 09 23:03:31 2023 LNS: Standby redo logfile selected for thread 2 sequence 16728 for destination LOG_ARCHIVE_DEST_2 Tue May 09 23:03:32 2023 Archived Log entry 431615 added for thread 2 sequence 16727 ID 0x5ffc99b5 dest 1: Tue May 09 23:05:30 2023 Error: Controlfile sequence number in file header is different from the one in memory Please check that the correct mount options are used if controlfile is located on NFS USER (ospid: 30162): terminating the instance Tue May 09 23:05:30 2023 System state dump requested by (instance=2, osid=30162), summary=[abnormal instance termination]. System State dumped to trace file /u01/app/oracle/diag/rdbms/xff/xff2/trace/xff2_diag_6650.trc Instance terminated by USER, pid = 30162
在节点1 进行实例重组之后,节点1 实例异常
Tue May 09 23:04:54 2023 Thread 1 cannot allocate new log, sequence 2060 Checkpoint not complete Current log# 1 seq# 2059 mem# 0: +DATA/xff/onlinelog/group_1.261.941899887 Current log# 1 seq# 2059 mem# 1: +FRA/xff/onlinelog/group_1.257.941899887 Thread 1 advanced to log sequence 2060 (LGWR switch) Current log# 2 seq# 2060 mem# 0: +DATA/xff/onlinelog/group_2.262.941899889 Current log# 2 seq# 2060 mem# 1: +FRA/xff/onlinelog/group_2.258.941899889 Tue May 09 23:04:58 2023 ********************* ATTENTION: ******************** The controlfile header block returned by the OS has a sequence number that is too old. The controlfile might be corrupted. PLEASE DO NOT ATTEMPT TO START UP THE INSTANCE without following the steps below. RE-STARTING THE INSTANCE CAN CAUSE SERIOUS DAMAGE TO THE DATABASE, if the controlfile is truly corrupted. In order to re-start the instance safely, please do the following: (1) Save all copies of the controlfile for later analysis and contact your OS vendor and Oracle support. (2) Mount the instance and issue: ALTER DATABASE BACKUP CONTROLFILE TO TRACE; (3) Unmount the instance. (4) Use the script in the trace file to RE-CREATE THE CONTROLFILE and open the database. ***************************************************** Tue May 09 23:05:31 2023 Reconfiguration started (old inc 20, new inc 22) List of instances: 1 (myinst: 1) Global Resource Directory frozen * dead instance detected - domain 0 invalid = TRUE Communication channels reestablished Master broadcasted resource hash value bitmaps Non-local Process blocks cleaned out Tue May 09 23:05:31 2023 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived Tue May 09 23:05:31 2023 LMS 0: 3 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 Tue May 09 23:05:32 2023 Instance recovery: looking for dead threads Submitted all GCS remote-cache requests Post SMON to start 1st pass IR Fix write in gcs resources Reconfiguration complete Tue May 09 23:06:00 2023 ARC1 (ospid: 26512): terminating the instance Tue May 09 23:06:00 2023 System state dump requested by (instance=1, osid=26512 (ARC1)), summary=[abnormal instance termination]. System State dumped to trace file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_diag_26311.trc Tue May 09 23:06:01 2023 ORA-1092 : opitsk aborting process Instance terminated by ARC1, pid = 26512
实例重启报错
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2059 Reading mem 0 Mem# 0: +DATA/dbm/onlinelog/group_1.261.941899887 Mem# 1: +FRA/dbm/onlinelog/group_1.257.941899887 Recovery of Online Redo Log: Thread 2 Group 3 Seq 16727 Reading mem 0 Mem# 0: +DATA/dbm/onlinelog/group_3.265.941900045 Mem# 1: +FRA/dbm/onlinelog/group_3.259.941900045 Recovery of Online Redo Log: Thread 2 Group 4 Seq 16728 Reading mem 0 Mem# 0: +DATA/dbm/onlinelog/group_4.266.941900045 Mem# 1: +FRA/dbm/onlinelog/group_4.260.941900045 Hex dump of (file 1, block 102777) in trace file /u01/app/oracle/diag/rdbms/dbm/dbm2/trace/dbm2_ora_30749.trc Reading datafile '+DATA/dbm/datafile/system.256.941899799' for corruption at rdba: 0x00419179 (file 1, block 102777) Reread (file 1, block 102777) found different corrupt data (logically corrupt) Hex dump of (file 1, block 102777) in trace file /u01/app/oracle/diag/rdbms/dbm/dbm2/trace/dbm2_ora_30749.trc RECOVERY OF THREAD 2 STUCK AT BLOCK 102777 OF FILE 1 Abort recovery for domain 0 Aborting crash recovery due to error 1172 Errors in file /u01/app/oracle/diag/rdbms/dbm/dbm2/trace/dbm2_ora_30749.trc: ORA-01172: recovery of thread 2 stuck at block 102777 of file 1 ORA-01151: use media recovery to recover block, restore backup if needed Abort recovery for domain 0 Errors in file /u01/app/oracle/diag/rdbms/dbm/dbm2/trace/dbm2_ora_30749.trc: ORA-01172: recovery of thread 2 stuck at block 102777 of file 1 ORA-01151: use media recovery to recover block, restore backup if needed ORA-1172 signalled during: ALTER DATABASE OPEN /* db agent *//* {0:890:17} */...
人工recover操作失败报ORA-600 3020错误
SQL> recover datafile 1; ORA-00283: recovery session canceled due to errors ORA-00600: internal error code, arguments: [3020], [1], [102777], [4297081],[], [] ORA-10567: Redo is inconsistent with data block (file# 1, block# 102777, file offset is 841949184 bytes) ORA-10564: tablespace SYSTEM ORA-01110: data file 1: '+DATA/dbm/datafile/system.256.941899799' ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 469884 ---alert日志 Tue May 09 23:28:44 2023 ALTER DATABASE RECOVER datafile 1 Media Recovery Start Serial Media Recovery started Recovery of Online Redo Log: Thread 2 Group 3 Seq 16727 Reading mem 0 Mem# 0: +DATA/xff/onlinelog/group_3.265.941900045 Mem# 1: +FRA/xff/onlinelog/group_3.259.941900045 ORA-279 signalled during: ALTER DATABASE RECOVER datafile 1 ... ALTER DATABASE RECOVER CONTINUE DEFAULT Media Recovery Log +FRA/xff/archivelog/2023_05_09/thread_1_seq_2055.20899.1136415701 ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... ALTER DATABASE RECOVER CONTINUE DEFAULT Media Recovery Log +FRA/xff/archivelog/2023_05_09/thread_1_seq_2056.20837.1136415753 ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... ALTER DATABASE RECOVER CONTINUE DEFAULT Media Recovery Log +FRA/xff/archivelog/2023_05_09/thread_1_seq_2057.20911.1136415803 ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... ALTER DATABASE RECOVER CONTINUE DEFAULT Media Recovery Log +FRA/xff/archivelog/2023_05_09/thread_1_seq_2058.21898.1136415853 Recovery of Online Redo Log: Thread 2 Group 4 Seq 16728 Reading mem 0 Mem# 0: +DATA/xff/onlinelog/group_4.266.941900045 Mem# 1: +FRA/xff/onlinelog/group_4.260.941900045 Recovery of Online Redo Log: Thread 1 Group 1 Seq 2059 Reading mem 0 Mem# 0: +DATA/xff/onlinelog/group_1.261.941899887 Mem# 1: +FRA/xff/onlinelog/group_1.257.941899887 Hex dump of (file 1, block 102777) in trace file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_16246.trc Reading datafile '+DATA/xff/datafile/system.256.941899799' for corruption at rdba: 0x00419179 (file 1, block 102777) Reread (file 1, block 102777) found different corrupt data (logically corrupt) Hex dump of (file 1, block 102777) in trace file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_16246.trc Tue May 09 23:28:59 2023 Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_16246.trc (incident=6868615): ORA-00600: internal error code, arguments: [3020], [1], [102777], [4297081], [], [], [], [], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 1, block# 102777, file offset is 841949184 bytes) ORA-10564: tablespace SYSTEM ORA-01110: data file 1: '+DATA/xff/datafile/system.256.941899799' ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 469884 Incident details in: /u01/app/oracle/diag/rdbms/xff/xff1/incident/incdir_6868615/xff1_ora_16246_i6868615.trc Tue May 09 23:29:00 2023 Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Media Recovery failed with error 600 ORA-283 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... ALTER DATABASE RECOVER CANCEL ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...
根据上述报错信息可以确认报错的是一个index,而且非系统核心对象,可以通过allow 1 corruption方式进行恢复,并且open库成功
SQL> recover datafile 1 allow 1 corruption; Media recovery complete. SQL> alter database open; Database altered. SQL> select owner,object_name,object_type from dba_objects where object_id=469884; OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- OBJECT_TYPE --------------------------------------------------------- SYSTEM PK_XFF_SERVERS INDEX SQL> alter index system.PK_XFF_SERVERS rebuild online; Index altered.
数据库完美恢复,数据0丢失,业务可以直接正常使用