云主机oracle异常恢复----惜分飞

某客户数据库放在x云上面,需要对数据库盘进行扩容,在扩容之前对该盘做了快照,结果没有想到悲剧发生了

[root@xifenfei ~]# df -h Filesystem      Size  Used Avail Use% Mounted on /dev/vda1        99G   64G   31G  68% / devtmpfs         16G     0   16G   0% /dev tmpfs            16G     0   16G   0% /dev/shm tmpfs            16G  720K   16G   1% /run tmpfs            16G     0   16G   0% /sys/fs/cgroup /dev/vdb        2.0T  1.2T  910G  56% /www/xifenfei tmpfs           3.2G     0  3.2G   0% /run/user/1004 tmpfs           3.2G     0  3.2G   0% /run/user/0

如上显示,客户的数据文件都放在/dev/vdb中了,但是很不幸,redo文件放在/data中(也就是vda磁盘组中),没有被做快照,结果客户还原vdb快照之后,发现现象如下

SQL> set pages 10000 SQL> set numw 16 SQL> SELECT status,   2  checkpoint_change#,   3  checkpoint_time,last_change#,   4  count(*) ROW_NUM   5  FROM v$datafile   6  GROUP BY status, checkpoint_change#, checkpoint_time,last_change#   7  ORDER BY status, checkpoint_change#, checkpoint_time; STATUS         CHECKPOINT_CHANGE# CHECKPOINT_T     LAST_CHANGE#          ROW_NUM -------------- ------------------ ------------ ---------------- ---------------- ONLINE                69632585947 04-JUL-22                                   38 SYSTEM                69632585947 04-JUL-22                                    2 SQL> set numw 16 SQL> col CHECKPOINT_TIME for a40 SQL> set lines 150 SQL> set pages 1000 SQL> SELECT status,   2  to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time,FUZZY,checkpoint_change#,   3  count(*) ROW_NUM   4  FROM v$datafile_header   5  GROUP BY status, checkpoint_change#, to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss'),fuzzy   6  ORDER BY status, checkpoint_change#, checkpoint_time; STATUS         CHECKPOINT_TIME                          FUZZY  CHECKPOINT_CHANGE#          ROW_NUM -------------- ---------------------------------------- ------ ------------------ ---------------- ONLINE         2022-07-04 09:03:24                      YES           69631105424               40

---

通过上述分析,该库相当数据文件和redo文件之间相差了一段时间数据,而且该库为非归档,基于这种情况,该库只能强制打开,在打开过程中遇到ORA-600 ktpridestroy2错误

SMON: enabling tx recovery Database Characterset is AL32UTF8 No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) SMON: Restarting fast_start parallel rollback Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_7332.trc  (incident=41257): ORA-00600: internal error code, arguments: [ktpridestroy2], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /data/oracle/diag/rdbms/orcl/orcl/incident/incdir_41257/orcl_smon_7332_i41257.trc Starting background process QMNC Mon Jul 04 16:31:44 2022 QMNC started with pid=36, OS id=7454 LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Fatal internal error happened while SMON was doing active transaction recovery. Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_7332.trc: ORA-00600: internal error code, arguments: [ktpridestroy2], [], [], [], [], [], [], [], [], [], [], [] SMON (ospid: 7332): terminating the instance due to error 474 Instance terminated by SMON, pid = 7332

对应trace文件

Dump continued from file: /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_7332.trc ORA-00600: internal error code, arguments: [ktpridestroy2], [], [], [], [], [], [], [], [], [], [], [] ========= Dump for incident 41257 (ORA 600 [ktpridestroy2]) ======== *** 2022-07-04 16:31:44.261 dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0) ----- SQL Statement (None) ----- Current SQL information unavailable - no cursor. ----- Call Stack Trace ----- calling           &