How to recover my Oracle database in DBF?

Tags:
DBF file
Oracle Database

Hi everybody,

I'm getting the following error on starting Oracle database through the enterprise manager console:
ORA-01122: Database file 2 failed verification check
ORA-01110: Datafile 2 'D:oracleoradatanew1undotbs01.dbf'
ORA-01200: Actual file size of 72733 is smaller than correct size of 72960 blocks.
The database mounts but is not able to open..(I don't have any backup of the database nor the datafiles) Please help. Thanks.

Answer Wiki

Thanks. We'll let you know when a new response is added.

You’re lucky that I’ve found your topic here. I suppose that I can help you.

>> datafile 2 ‘D:\oracle\oradata\new1\undotbs01.dbf’

As the name of the datafile suggest, it’s a datafile attached to undo tablespace and if it is so then only one concern is there –

whether last time ur database was shutdown properly or not.

First of all at mount stage check the datafile belongs to undo tablespace or not. Use this query from sqlplus at mount stage –

SQL> select D.name ” Filename”, T.name ” Tablespace name” from 

2 V$datafile D, V$tablespace T 

3 where D.TS# = T.TS#;

FILE_NAME

——————————————————————————

TABLESPACE_NAME

——————————

D:\ORACLE\ORADATA\TANUDB1\SYSTEM01.DBF

SYSTEM

D:\ORACLE\ORADATA\TANUDB1\UNDOTBS01.DBF

UNDOTBS1

D:\ORACLE\ORADATA\TANUDB1\DRSYS01.DBF

DRSYS

( on my machine)

As it shows that UNDOTBS01.DBF belongs to undotbs1 ( undo tablespace ) , if it is so on ur machine then do this :

–Take the specific datafile offline

SQL> alter database datafile ‘D:\ORACLE\ORADATA\TANUDB1\UNDOTBS01.DBF’ offline drop;

Database altered.

– Open the database

SQL> alter database open;

Database altered.

– create a new undo tablespace (like this)

SQL> create undo tablespace undotbs2

2 datafile ‘D:\ORACLE\ORADATA\TANUDB1\UNDOTBS02.dbf’ size 100M;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2;

System altered.

and u can use ur system freely, nothing is lost. 

A word of caution —

This scenario is going to work provided 

– That datafile belongs to undo tablespace.

But if it doesn’t help try free demo of recovery toolbox for DBF, it’s gonna hel

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following