记录一次ORA-01200完美恢复---惜分飞

客户虚拟化平台断电，导致oracle其数据库启动ORA-01200错误

SQL> alter database open; alter database open * ERROR at line 1: ORA-01122: database file 1 failed verification check ORA-01110: data file 1: '/oradata/orcl/system01.dbf' ORA-01200: actual file size of 1122560 is smaller than correct size of 1131520 blocks

对应的alert日志如下

Thu Jan 11 11:36:48 2024 ALTER DATABASE   MOUNT Successful mount of redo thread 1, with mount id 1685778896 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE   MOUNT Thu Jan 11 11:36:52 2024 ALTER DATABASE OPEN Read of datafile '/oradata/orcl/system01.dbf' (fno 1) header failed with ORA-01200 Rereading datafile 1 header failed with ORA-01200 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10847.trc: ORA-01122: database file 1 failed verification check ORA-01110: data file 1: '/oradata/orcl/system01.dbf' ORA-01200: actual file size of 1122560 is smaller than correct size of 1131520 blocks ORA-1122 signalled during: ALTER DATABASE OPEN... Thu Jan 11 11:36:53 2024 Checker run found 1 new persistent data failures Thu Jan 11 11:41:55 2024 alter database open Read of datafile '/oradata/orcl/system01.dbf' (fno 1) header failed with ORA-01200 Rereading datafile 1 header failed with ORA-01200 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12550.trc: ORA-01122: database file 1 failed verification check ORA-01110: data file 1: '/oradata/orcl/system01.dbf' ORA-01200: actual file size of 1122560 is smaller than correct size of 1131520 blocks ORA-1122 signalled during: alter database open...

报错比较明显system01.dbf文件本来大小应该为1131521个block,但是实际上只有1122561个block,因此无法正常启动,通过修改数据文件欺骗数据库
 

---

然后对异常的system文件进行处理,把人工构造的部分除掉

SQL> alter database datafile 1 resize 8770M; Database altered.

rman检测system文件正常
 

---

数据库恢复完成,数据完美恢复(0丢失,不用逻辑迁移),该库可以继续使用,以前有过类似case:
bbed处理ORA-01200故障
ORA-01122 ORA-01200故障处理
ORA-1200/ORA-1207数据库恢复