ORA-01200: actual file size of X is smaller than correct size of Y

Yesterday My colleague has encountered the following issue, which is interesting.

Environment:-

1) Linux & 10g Database

2) Oracle Block size =8192

3) OS Block/Page size = 4096

Issue:- ORA-01200: actual file size of X is smaller than correct size of Y

Verification:-

1) Database was not opening (non system datafile)

2) DBV says no corruption

3) Can create a control file but a long procedure

4) No archive log mode (no media recovery possible)

5) Apparently a new datafile, assuming a lesser objects resides in it.

6) Another assumption ,  my datafile is not in good state in terms of datafile header not by the original data.

So with the last assumption makes me to try something odd, as I know there is no other way and no backup I have, just to play with it.

After of course googling for right commands I started suggesting to my friend

1) Create a copy of original file to some other place

2) Create a empty file using

Linux:

a) dd if=/dev/zero of=/path/to/desired/big/file count=1024

b) vi the file jst add lines lines until you required size

Solaris:

Use mkfile [-nv] size [k| b| m] filename

HP:

Use prealloc

Windows: Batch script for input the required and original size.

https://forums.oracle.com/forums/thread.jspa?threadID=224262&start=15&tstart=0

#include <File.au3>
Global $file1 = InputBox("ORA-01200", "File to increase", "c:oracleoradatacmlocallobts1.dbf")
Global $size = Number(InputBox("ORA-01200", "Required size (bytes)", 4142653440))
_FileWriteLog (@ScriptDir & "ORA-01200.log", FileGetSize($file1) - $size)
While FileGetSize($file1) < $size
FileWrite($file1, Chr(32))
_FileWriteLog (@ScriptDir & "ORA-01200.log", FileGetSize($file1) - $size)
WEnd
_FileWriteLog (@ScriptDir & "ORA-01200.log", FileGetSize($file1) - $size)

3) Now my two files a) original file b) diff size file (i.e empty file with different size) are ready let append/concatenate those using

cat original.dbf diff.dbf >> original1.dbf

4) Rename the original1.dbf to your original.dbf

5) Try to restart your database, if not opening due to media recovery you can use parameter _allow_resetlogs_corruption=true –> to bypass the media recovery and open resetlogs.

This may work only in cases of the data intact and just header need to be updated.

-Thanks

Geek DBA