ORA-00227 ORA-00202 – Database SOS

oracle dbv工具是Oracle数据库离线检测坏块的工具主要是用来检测数据文件坏块(物理和逻辑坏块),虽然在一定程度上面可以检测controlfile的坏块,但是不是特别准,在一次恢复案例中数据库启动报controlfile损坏,但是dbv检测是正常的
数据库在mount的过程中报controlfile 损坏ORA-00227

把控制文件从asm里面拷贝到文件系统,然后通过dbv进行检测,一切正常

[oracle@oracle1 ~]$ dbv blocksize=16384 file=/tmp/control01.ctl 

DBVERIFY: Release 11.2.0.4.0 - Production on Wed Jul 1 14:21:58 2026

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /tmp/control01.ctl


DBVERIFY - Verification complete

Total Pages Examined         : 1312
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 395
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 917
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 4294967295 (65535.4294967295)

这个故障通过重建ctl,打开数据库成功

联系：手机/微信(+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$上,无任何业务数据丢失.

分类目录归档：ORA-00227 ORA-00202