Problem solve Get help with specific problems with your technologies, process and projects.

Database crash when adding disk space

We are in the process of adding more disk space to our Oracle database server. Apparently the system ran out of physical space which caused the database to crash. We received the following message when trying to restart the database.

1) ORA-01122 datafile failed verification
2) ORA-01110 datafile "path"
3) ORA-01200 Actual file size of xxxx is smaller than correct size of xxxx (2k blocks)

No autologs or current datafile backups exist. All recovery information I've found on the Internet indicates a backup file needs to be used before recovery can be done.

As an alternative, can the "Alter Database....resize" command be used to resize the datafile to a slightly larger size? And will it resolve the control-file / data-file size discrepancy? Some data may be corrupted but I am willing to take the chance.
Here's the description of your errors from the Oracle Error Messages Guide:

1122 The information in this file is inconsistent with information from the control file.

1200 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 datafile and do recovery as needed.

The bottom line is that without a backup you're pretty much out of luck for that datafile (I hope this was a test system!). If all your other datafiles are OK and if the datafile you lost didn't contain the SYSTEM tablespace or all your user data, then you could drop that datafile and then recreate it and manually add back in what you lost. You would need to use the command ALTER DATABASE DATAFILE 'filename' offline drop; after starting up the database in nomount mode.

Dig Deeper on Oracle database backup and recovery

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.