| Deletion of Datafile [message #441284] |
Sat, 30 January 2010 03:44  |
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 #441287 is a reply to message #441284] |
Sat, 30 January 2010 03:58  |
John Watson
Messages: 8960
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 |
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 |
John Watson
Messages: 8960
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>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
