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; 数据库已更改。