ORA-01200错误的修复(一)

在数据库启动的过程中可能遇到如下的错误：

 

[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>

这个错误表示文件头记录的数据文件的大小与实际不一致：

 

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.

SQL>