背景：数据库断电启动，只能到mount状态。
报错如下：
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 51 failed verification check
ORA-01110: data file 51: '/opt/oracle/oradata/data/data107.dbf'
ORA-01200: actual file size of 393856 is smaller than correct size of 394496 blocks。

查看了数据文件的最新修改时间，最新修改时间分为好几批。这下，坏了。没有备份，怎么办？
recover database了下，还是不行。

转载：http://blog.chinaunix.net/uid-22948773-id-3302663.html 感谢美丽人生的博客提供了解决办法。

[oracle@db2server huateng]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 5 17:24:07 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 506368000 bytes
Fixed Size 1337520 bytes
Variable Size 331351888 bytes
Database Buffers 167772160 bytes
Redo Buffers 5906432 bytes
Database mounted.
ORA-01122: database file 6 failed verification check
ORA-01110: data file 6: '/u01/app/oracle/oradata/huateng/htyansp01.dbf'
ORA-01200: actual file size of 7399 is smaller than correct size of 12800
blocks
这个错误表示文件头记录的数据文件的大小与实际不一致：

SQL> ! ls -ltr /u01/app/oracle/oradata/huateng/htyansp01.dbf
-rw-r--r-- 1 oracle oinstall 60620800 08-05 17:25 /u01/app/oracle/oradata/huateng/htyansp01.dbf
SQL> select bytes from v$datafile where name='/u01/app/oracle/oradata/huateng/htyansp01.dbf';
BYTES
----------
104857600
SQL> select 60620800-8192 from dual;
60620800-8192
-------------
60612608
SQL> select 60612608/8192 from dual;
60612608/8192
-------------
7399
实际文件的大小为 7399 个BLOCKS。
SQL> select bytes from v$datafile where name='/u01/app/oracle/oradata/huateng/htyansp01.dbf';
BYTES
----------
104857600
SQL> select 104857600/8192 from dual;
104857600/8192
--------------
12800

而控制文件中记录的文件大小为12800个 BLOCKS。

如果有备份的话可以通过备份恢复的方法来解决这个问题。如果没有备份的话，
一种简单解决这个错误的方法是通过把数据文件构造到和控制文件记录的一模一样来骗过oracle。

如下：

SQL> alter database open;

alter database open
*
ERROR at line 1:
ORA-01122: database file 6 failed verification check
ORA-01110: data file 6: '/u01/app/oracle/oradata/huateng/htyansp01.dbf'
ORA-01200: actual file size of 7399 is smaller than correct size of 12800
blocks
SQL> select 12800-7399 from dual
2 ;
12800-7399
----------
5401
SQL> ! dd if=/dev/zero of=/u01/app/oracle/oradata/huateng/htyansp01.dbf bs=8192 count=5401 seek=7400
5401+0 records in
5401+0 records out
44244992 bytes (44 MB) copied, 0.152335 seconds, 290 MB/s
SQL> ! ls -ltr /u01/app/oracle/oradata/huateng/htyansp01.dbf
-rw-r--r-- 1 oracle oinstall 104865792 08-05 17:37 /u01/app/oracle/oradata/huateng/htyansp01.dbf
SQL> alter database open;
Database altered.

解决过程：

ORA-01200: actual file size of 393856 is smaller than correct size of 394496 blocks。

SQL> select 394496-393856 from dual;

394496-393856
-------------
640

SQL> ! dd if=/dev/zero of=/opt/oracle/oradata/data/data107.dbf bs=16384 count=640 seek=393857
??? 640+0 ??
??? 640+0 ??

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 56 failed verification check
ORA-01110: data file 56: '/opt/oracle/oradata/data/data112.dbf'
ORA-01200: actual file size of 354240 is smaller than correct size of 354816 blocks

又有新的文件冒出来了。接着如法炮制。一共解决了七八个这样的文件之后，终于有变化了：
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr1_lastbwr], [], [], [], [],
[], [], []

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

自此，终于打开数据库了。赶紧备份。

美丽人生的博客还介绍了一种使用bbed修改数据文件头的做法，看得懂的可以试试。

ITPub博客

一次ora-01200错误的修复

最新文章