ORA-01200 , ORA-01110 and ORA-01122

Please be aware that the note below is not an official one.
You may apply what is written below, but on your own risk.






Applies to: Oracle Server - Enterprise Edition - Version: 9.2.0.6 to 10.2.0.4
Information in this document applies to any platform.
Goal
This article is intended fora database that is failing with the following error message
while starting up the database:
ORA-01122: database file 9 failed verification check
ORA-01110: data file 9: '/u02/oradata/careware/users01.dbf '
ORA-01200: actual file size of 64000 is smaller than correct size of 65600
The solution is intended for users whose database is in NO ARCHIVE LOG mode and when
user doesnot have any other mean of recovering the database.
For database in ARCHIVE LOG mode the best and supported option is to restore and recover.
Please do not trythis on SYSTEM tablespace as data dictionary objects may be incosistent after this procedure is applied.

Solution
No Archive log mode :-
> Take an Cold backup of the database.
> Once the Cold backup is taken do the following :-
The error message ORA-01110 and ORA-01122 would indicate the datafile number and name having the problem.
The error message ORA-01200provides information about thecurrent file size and thecorrect size
Oracle expects.
Error: ORA 1200
Text: actual file size of <num> is smaller than correct size of <num> 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.


For example in this case
ORA-01200: actual file size of 64000 is smaller than correct size of 65600
The actual filesize at OS level--> 64000 *db_block_size + One Oracle block--> 64000 * 8192 + 8192=524296192 bytes .
You can confirm the same by executing
ls -lrt <location of datafile>
Datafile size Oracle expects: correct size of 65600 --> 65000 * 8192 +8192=532488192 bytes(At OS)
So the difference in the number of blocks is
(correct filesize as in controlfile/database- actual file size)=65600 -64000=1600(blocks)
So in the next steps1600 empty/zero blocks will be added to the existing datafile to match it with the value in the control file.

Solution dd if=<locationf datafile having problem> of=<output/target datafile> count=< > bs=<db_block_size in bytes>

Taking the above example (First we take an dd backupof datafile):

dd if=/u02/oradata/careware/users01.dbf of=/tmp/corr_temp.DBF count=64000 bs=8192

Nowadd 1600 zero blocks to datafile /u02/oradata/careware/users01.dbf

syntax

dd if=/dev/zeroof=<location of datafile> bs=<db_block_size in bytes> seek=<Actual blocknumber reported + 1 > count=<Difference in number of block>

In parameter seekspecify the block from which it should append 1600 blocks.
In this case since the file contains64000(as indicated by the error message) soseek=64001 which isthe next block from where the append will occur.

ddif=/dev/zeroof=/u02/oradata/careware/users01.dbfbs=8192seek=64001count=1600conv=notrunc

Now check the file size at OS level (It should be 65600* 8192 + 8192)bytes

Do a ls -lrt <filename> to confirm the same.

Warning!!Once the database is open, export all the objects present in the tablespace containing the datafile. Pleasenote that forany segment which had blocksthat got truncated at OS level, the export may fail if it tries to read data from the zero padded blocks. In that caseit may be needed to apply a procedure tosalvage good records.

Once export is complete, create a new tablespace and import the data.
Once it is confirmed thatthe data is good, drop the old tablespace.