Home » RDBMS Server » Backup & Recovery » Deletion of Datafile (10.2.0, Windows Xp)
Deletion of Datafile [message #441284] Sat, 30 January 2010 03:44 Go to next message
muthu_dba
Messages: 17
Registered: December 2009
Location: Bangalore
Junior Member
I have a tablespace "data" with datafile named "data1.dbf" size 20M. Unfortunately the datafile was deleted from system. I dont have any backup for that datafile. Now my database is not opening.

It shows the error like

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'F:\ORACLE\ORADATA\NEWDB\DATA1.DBF'

Is there is any solution to recover tahe datafile and open it?
Re: Deletion of Datafile [message #441286 is a reply to message #441284] Sat, 30 January 2010 03:58 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Based on the information provided it`s not possible...to recover.If you don`t have the backup.
sriram Smile
Re: Deletion of Datafile [message #441287 is a reply to message #441284] Sat, 30 January 2010 03:58 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
In your "Database Backup and Recovery Advanced User's Guide
10g Release 2 (10.2)" there is a section titled "Re-Creating Datafiles When Backups Are Unavailable: Scenario" wehich might help.
Re: Deletion of Datafile [message #441293 is a reply to message #441287] Sat, 30 January 2010 04:29 Go to previous messageGo to next message
muthu_dba
Messages: 17
Registered: December 2009
Location: Bangalore
Junior Member
That scenario deals with the recovery of damaged datafile when no backup is available , But it contains the physical corrupted datafile. But in my case the physical datafile was deleted.In this case how to recover the datafile?
Re: Deletion of Datafile [message #441297 is a reply to message #441293] Sat, 30 January 2010 05:19 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Hello again - I think you are misunderstanding the technique. It will recreate a file that has been deleted. The principle is that if your current controlfile includes the definition of the missing datafile (which it will do, if the only problem is that the file was deleted) then you can recreate the file using that definition. Then, if you have all the archive logs generated since the file was first created, you can recover it.
This was a standard exercise in the 8.0/8i/9i DB Admin courses, it isn't included in the 10g or 11g curriculum but I usually demo it anyway:
orcl> create tablespace ts1 datafile 'c:\tmp\ts1.dbf' size 2m;

Tablespace created.

orcl>
orcl> drop table t1;

Table dropped.

orcl> create table t1 tablespace ts1 as select * from all_users;

Table created.

orcl> alter tablespace ts1 offline;

Tablespace altered.

orcl> host erase c:\tmp\ts1.dbf

orcl> alter database create datafile 'c:\tmp\ts1.dbf'  as 'c:\tmp\ts1.dbf' ;

Database altered.

orcl> recover datafile 'c:\tmp\ts1.dbf';
Media recovery complete.
orcl> alter tablespace ts1 online;

Tablespace altered.

orcl> select count(*) from t1;

  COUNT(*)
----------
        43

orcl>




Previous Topic: Restore only from Rman back files
Next Topic: Recover Oracle database (without RMAN) - Server/Oracle database has crashed
Goto Forum:
  


Current Time: Fri Mar 29 10:06:36 CDT 2024