断电引起的oracle数据库异常恢复----惜分飞

标签：bytes 分飞 DBF ---- orcl BaiduNetdiskDownload SQL oracle ORA

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：断电引起的oracle数据库异常恢复

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

服务器断电，数据库mount失败

SQL> startup mount pfile='d:/pfile.txt' ORACLE 例程已经启动。   Total System Global Area 1185853440 bytes Fixed Size                  2175168 bytes Variable Size             335548224 bytes Database Buffers          838860800 bytes Redo Buffers                9269248 bytes ORA-00205: ?????????, ??????, ???????

alert日志报错信息

Sun Mar 19 20:18:29 2023 ALTER DATABASE   MOUNT Errors in file d:\app\xifenfei\diag\rdbms\orcl\orcl\trace\orcl_ckpt_15064.trc  (incident=3697): ORA-00227: ????????????: (? 1, # ? 1) ORA-00202: ????: ''D:\BAIDUNETDISKDOWNLOAD\ORCL\CONTROL01.CTL'' Incident details in: d:\app\xifenfei\diag\rdbms\orcl\orcl\incident\incdir_3697\orcl_ckpt_15064_i3697.trc Sun Mar 19 20:18:30 2023 Errors in file d:\app\xifenfei\diag\rdbms\orcl\orcl\trace\orcl_m000_18084.trc  (incident=3761): ORA-00227: ????????????: (? 1, # ? 1) ORA-00202: ????: ''D:\BAIDUNETDISKDOWNLOAD\ORCL\CONTROL01.CTL'' Incident details in: d:\app\xifenfei\diag\rdbms\orcl\orcl\incident\incdir_3761\orcl_m000_18084_i3761.trc Sun Mar 19 20:18:29 2023 MMNL started with pid=16, OS id=9404 ORA-00227: ????????????: (? 1, # ? 1) ORA-00202: ????: ''D:\BAIDUNETDISKDOWNLOAD\ORCL\CONTROL01.CTL'' Checker run found 1 new persistent data failures Trace dumping is performing id=[cdmp_20230319201831] ORA-205 signalled during: ALTER DATABASE   MOUNT...

错误比较明显由于控制文件的block损坏导致数据库在mount的时候提示ORA-00205,重试重建ctl

SQL> CREATE CONTROLFILE REUSE DATABASE "orcl" NORESETLOGS  NOARCHIVELOG   2      MAXLOGFILES 50   3      MAXLOGMEMBERS 5   4      MAXDATAFILES 100   5      MAXINSTANCES 8   6      MAXLOGHISTORY 226   7  LOGFILE   8    GROUP 1 'D:\BaiduNetdiskDownload\orcl/redo01.log'  SIZE 50M,   9    GROUP 2 'D:\BaiduNetdiskDownload\orcl/redo02.log'  SIZE 50M,  10    GROUP 3 'D:\BaiduNetdiskDownload\orcl/redo03.log'  SIZE 50M  11  DATAFILE  12  'D:\BaiduNetdiskDownload\orcl\EXAMPLE01.DBF',  13  'D:\BaiduNetdiskDownload\orcl\GHZN.DBF',  14  'D:\BaiduNetdiskDownload\orcl\GHZN2.DBF',  15  'D:\BaiduNetdiskDownload\orcl\SYSAUX01.DBF',  16  'D:\BaiduNetdiskDownload\orcl\SYSTEM01.DBF',  17  'D:\BaiduNetdiskDownload\orcl\UNDOTBS01.DBF',  18  'D:\BaiduNetdiskDownload\orcl\USERS01.DBF'  19  CHARACTER SET ZHS16GBK  20  ; CREATE CONTROLFILE REUSE DATABASE "orcl" NORESETLOGS  NOARCHIVELOG * ERROR at line 1: ORA-01503: CREATE CONTROLFILE failed ORA-01565: error in identifying file 'D:\BaiduNetdiskDownload\orcl\UNDOTBS01.DBF' ORA-27041: unable to open file OSD-04001: 逻辑块大小无效 (OS 2613931212)

由于undo文件异常(大小不是block size的整数倍）,因此报OSD-04001: 逻辑块大小无效错误.对undo文件及其其他文件进行检查发现数据库文件有不少坏块,而且undo文件的文件头损坏

---

通过抛弃undo文件并进行一些处理,重建ctl成功,并且recover 数据库成功,顺利open数据库

 

SQL> CREATE CONTROLFILE REUSE DATABASE "orcl" NORESETLOGS  NOARCHIVELOG   2      MAXLOGFILES 50   3      MAXLOGMEMBERS 5   4      MAXDATAFILES 100   5      MAXINSTANCES 8   6      MAXLOGHISTORY 226   7  LOGFILE   8    GROUP 1 'D:\BaiduNetdiskDownload\orcl/redo01.log'  SIZE 50M,   9    GROUP 2 'D:\BaiduNetdiskDownload\orcl/redo02.log'  SIZE 50M,  10    GROUP 3 'D:\BaiduNetdiskDownload\orcl/redo03.log'  SIZE 50M  11  DATAFILE  12  'D:\BaiduNetdiskDownload\orcl\EXAMPLE01.DBF',  13  'D:\BaiduNetdiskDownload\orcl\GHZN.DBF',  14  'D:\BaiduNetdiskDownload\orcl\GHZN2.DBF',  15  'D:\BaiduNetdiskDownload\orcl\SYSAUX01.DBF',  16  'D:\BaiduNetdiskDownload\orcl\SYSTEM01.DBF',  17  'D:\BaiduNetdiskDownload\orcl\USERS01.DBF'  18  CHARACTER SET ZHS16GBK  19  ;   Control file created.   SQL> recover database; Media recovery complete. SQL> shutdown immediate; ORA-01109: database not open     Database dismounted. ORACLE instance shut down. SQL> startup mount pfile='d:/pfile.txt' ORACLE instance started.   Total System Global Area 1185853440 bytes Fixed Size                  2175168 bytes Variable Size             335548224 bytes Database Buffers          838860800 bytes Redo Buffers                9269248 bytes Database mounted. SQL> alter database open;   Database altered.

然后使用逻辑方式导出数据,运气不错业务文件没有任何坏块,system坏块在aud$上,无任何业务数据丢失.

• 恢复没有控制文件备份的rman数据文件备份
• 重建控制文件丢失数据文件导致悲剧
• 数据文件的三个创建SCN一点点探讨
• ora-600 kcratr_scan_lastbwr
• 分享一次ORA-01113 ORA-01110故障处理过程
• 重建control遗漏数据文件,reseltogs报ORA-1555错误处理
• 创建控制文件出现ORA-01565 ORA-27041 OSD-04002
• ORA-00600[kcrf_resilver_log_1]异常恢复
• ORA-00600 dbkif_find_next_record_1
• ora-600 kccpb_sanity_check_2故障处理
• 非归档数据库异常恢复一例
• ORA-600 kcratr_scan_lastbwr 恢复

标签：bytes,分飞,DBF,----,orcl,BaiduNetdiskDownload,SQL,oracle,ORA
From： https://www.cnblogs.com/xifenfei/p/17234587.html