偶尔我们会遇到ORA-01122,ORA-01110,ORA-01200错误,例如:
ORA-01122: database file 9 failed verification check ORA-01110: data file 9: '/u02/oradata/orcl/users01.dbf ' ORA-01200: actual file size of 64000 is smaller than correct size of 65600 [oracle@mlab2 ~]$ oerr ora 1122 01122, 00000, "database file %s failed verification check" // *Cause: The information in this file is inconsistent with information // from the control file. See accompanying message for reason. // *Action: Make certain that the db files and control files are the correct // files for this database. [oracle@mlab2 ~]$ oerr ora 1110 01110, 00000, "data file %s: '%s'" // *Cause: Reporting file name for details of another error. The reported // name can be of the old file if a data file move operation is // in progress. // *Action: See associated error message. [oracle@mlab2 ~]$ oerr ora 1200 01200, 00000, "actual file size of %s is smaller than correct size of %s blocks" // *Cause: The size of the file as returned by the operating system is smaller // than the size of the file as indicated in the file header and the // control file. Somehow the file has been truncated. Maybe it is the // result of a half completed copy. // *Action: Restore a good copy of the data file and do recovery as needed.
以上ORA-1200错误的描述是 当数据文件的实际大小 小于了数据文件头描述的块数*block_size， 这里所说的文件实际大小是通过OS层的系统调用获得的； 这种现象一般意味着数据文件可能被截断了。
注意，当备份和归档备份完备的情况下可以直接还原备份来修复该问题，本文特定针对那些无备份的情况下遇到该问题的解决。建议对于SYSTEM表空间下的数据文件不要尝试这样修复。
首先有必要的是对数据库做一个全库的冷备，分析ORA-01110 和 ORA-01122的报错 可以了解到对应存在问题的具体数据文件号，如下面显然是FILE#=9的文件出错了：
ORA-01122: database file 9 failed verification check ORA-01110: data file 9: '/u02/oradata/orcl/users01.dbf '
在上面的例子中ORA-01200: actual file size of 64000 is smaller than correct size of 65600，即实际的文件大小为64000*db_block_size+1个Oracle BLock=64000 * 8192 + 8192 =524296192
具体是不是这么大，可以通过 ls -ltr 数据文件 来确定。
但是ORACLE从数据文件头中获取的数据文件理论大小是65600 –> 65000 * 8192 +8192=532488192 bytes。
由此比较2者会发现存在65600-64000=1600个块的差距，这1600个块的差距可以通过dd来实现添加到现有的数据文件中，当然这里加的是空块。
例如：
dd if=<locationf datafile having problem> of=<output/target datafile> count=< > bs=<db_block_size in bytes> 如dd if=/u02/oradata/orcl/users01.dbf of=/tmp/corr_temp.DBF count=64000 bs=8192 并追加1600个空块到新的数据文件里 dd if=/dev/zero of=<location of datafile> bs=<db_block_size in bytes> seek=<Actual block number reported + 1 > count=<Difference in number of block> 如dd if=/dev/zero of=/u02/oradata/careware/users01.dbf bs=8192 seek=64001 count=1600 conv=notrun
由于数据库打开时仅仅验证数据文件头和控制文件信息，所以上述操作能够骗过ORACLE的检测。 但扔建议在数据库打开后，将所有可用数据导出并重建数据库。
Leave a Reply