| Deleted Datafile [message #64020] |
Mon, 06 December 2004 22:00  |
Jitendra Agrawal
Messages: 71
Registered: December 2003
|
Member |
|
|
Hi all,
I am using oracle 9i Release 2.
I accidently deleted on of the datafiles and now I am not able to start database.
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/home/oracle/OraHome1/oradata/mahimtura.dbf'
How do I start database? How do I access the data in rest of the datafiles and tablespaces?
Thanks in advance.
Jitendra Agrawal.
|
|
|
|
| Re: Deleted Datafile [message #64029 is a reply to message #64020] |
Tue, 07 December 2004 09:10  |
Nino
Messages: 6
Registered: November 2004
|
Junior Member |
|
|
If you haven't got any backup of your physical datafile, you should delete the file entry from the database in order to startup and open the database normally.
First startup mount the database
At this point you should be able to select * from v$datafile and the database returns you the list of the datafiles including this of the accidentally deleted file. Oracle still 'thinks' the file is there.
then: alter database datafile 'datafile' offline drop
or something similar to making oracle forgetting the datafile. The exact syntax is described in oracles sqlref or consult the dbadmin guide in the managing tablespaces and datafiles section.
After that you should create a full backup of the database.
Works with Oracle 9....
hope I could help
cheers
|
|
|
|
|
|
| Re: Deleted Datafile [message #131215 is a reply to message #64020] |
Thu, 04 August 2005 19:43 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
I have similar problem today and I need help.
One of my colleagues today deleted a datafile which belongs to the USERS tablespace. This is what he did (or at least what he told me)
1. took datafile off line
2. shutdown immediate
3. mount
4. alter datafile dfname offline drop;
5. then he deleted OS file…
6. alter database open.
Everything when OK, but I think he did not consider that the DB is in Non Archive mode. I have done the same but always tought that we need Archive to be ON.
Right now the database is online and accessible for some users and though EM, but when I start the process which login with the users/schema which originally was using this datafile, I got an error – Cannot read file 24 – filename… and the app terminates.
What can I do in this situation? I cannot create a new tablespace and to copy all datafiles there and then to drop the old one – the size is over 600G. It’ll take forever. No need to say – there’s no cold back up made today, and the latest I found is too old…
How can I get rid of this datafile – which doesn’t even exist on the server, but Oracle still looking for it?
Thanks a lot for the help .mj
|
|
|
|
| Re: Deleted Datafile [message #131254 is a reply to message #131215] |
Fri, 05 August 2005 00:03 |
tarundua
Messages: 1080
Registered: June 2005
Location: India
|
Senior Member |
|
|
| Quote: |
Recovery of Data files -- No Backups Available ( all from book)
Oracle enables u to recover data files that have no backups and belong to the non system tablespace.The following conditions must be met for recovering data files:
-- You must have all the archived redo and online log files since the creation of data files.
-- You must have the current or a backup control file, which was created after the data files were created ( that is, the control file should recognize the data files)
-- If an open database recovery needs to be done, you must take the data files or associated tablespace, offline.
Now the procedure :
1. IF the database is closed, then mount the instance. Otherwise, perform all the operations with the database open.
2. Take the tablespace or data file on the disk that failed OFFLINE.
3. You can regenerated the data files that are lost or damaged by executing the following command --
SQL> ALTER DATABASE CREATE DATAFILE
'D:\Oracle\oradata\target\ormtest01.dbf'
AS 'C:\Oracle\oradata\target\ormtest01.dbf';
(In the preceding coed, we are Relocating the file to drive C cos drive D has crashed)
4. Recover the tablespace or datafile using RECOVER command.
5. Bring the tablespace or datafile online.
|
It might help u. Now ur database is in No ARchive mode so luckily if u have all the changes in the log files then i think u can use the above method to recreate the lost datafile.
regards,
tarun
|
|
|
|
| Re: Deleted Datafile [message #131260 is a reply to message #131254] |
Fri, 05 August 2005 00:53 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
Thanks so much for your answer - 2 questions though:
1. Recover the tablespace or datafile using RECOVER command - what will be the fastest way to recover?
2. Bring the tablespace or datafile online.- I still will need to get rid of this datafile - how to remove it with no problems?
Thanks very very much,mj
|
|
|
|
| Re: Deleted Datafile [message #131262 is a reply to message #131260] |
Fri, 05 August 2005 01:03 |
tarundua
Messages: 1080
Registered: June 2005
Location: India
|
Senior Member |
|
|
hi,
| Quote: |
1. Recover the tablespace or datafile using RECOVER command - what will be the fastest way to recover?
|
Because only one datafile is lost u can jst fire the command
SQL> Recover datafile 'datafile';
and nw u can use it.
| Quote: |
2. Bring the tablespace or datafile online.- I still will need to get rid of this datafile - how to remove it with no problems?
|
Do u want to remove this file permanently? what about the data that is stored in this file. Pls explain it clearly.
|
|
|
|
| Re: Deleted Datafile [message #131352 is a reply to message #131262] |
Fri, 05 August 2005 08:49 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
The problem is that my colleague created this file on the wrong disk and he created the file 2-3 hours before to find out that it's wrong. That's why he went and deleted it. The data is not too important as all missing entries could be reloaded from an external table. Everytime the system finds that there's a missing records it goes back and reloads it by the unique idetifier itself if it's not the more then 4 weeks old - the data is from yesterday and still available. I also have an SP to synch the data. It'll take me about 30-45 min. But I need to remove the file as this is another department file system and we cannot use it...
Thanks a lot,mj
|
|
|
|
| Re: Deleted Datafile [message #131636 is a reply to message #131254] |
Mon, 08 August 2005 13:41 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
I still cannot do it.
It did not want to create a new file at all.
How can I drop the file no matter what - just to get rid of it?
It's very improtant right now to bring the database on. The fiels is off line but the erre I'm getting is:
SQL> select max(addr_id) from rr39.address;
select max(addr_id) from rr39.address
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P007
ORA-00376: file 24 cannot be read at this time
ORA-01110: data file 24: '/mnt/db_indx/ora/orafiles/FS_data.dbf'
What can I do?
Thanks a lot,
mj
|
|
|
|
| Re: Deleted Datafile [message #131684 is a reply to message #131636] |
Tue, 09 August 2005 00:10 |
tarundua
Messages: 1080
Registered: June 2005
Location: India
|
Senior Member |
|
|
Ok, u dnt want to recreate the file. But what about those objects that were strored in that file.
The error is there because u r trying to access those objects that were stored in that deleted file.
If u really dnt want that file then what u can do is :
>> Recreate the file
>> Move all the objects from the tablespace (holding that file) to any other existing tablespace.
>> Now take that file/tablespace offline or delete and further u can access those objects because they r stored in another tablespace.
Is it the solution in ur scenario ?
|
|
|
|
| Re: Deleted Datafile [message #131816 is a reply to message #131684] |
Tue, 09 August 2005 10:27 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
Thanks a lot for your idea, but I cannot use it...
I'm stacked with a datafile which is unrecognized, cannot be recover or recreate, and I cannot get rid of it...
Why this is such a problem for Oracle on Unix - it's much easier on Windows...
I do not want the file or the data in it, I cannot restore it or recover it, I just want to remove it and to be able to use the tablespace like before.
This is so frustrating for me. Any idea is greatly appreciated.
Thanks,mj
|
|
|
|
| Re: Deleted Datafile [message #132385 is a reply to message #131816] |
Fri, 12 August 2005 06:40 |
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
|
Senior Member |
|
|
HI
Your requirement may not be feasible for certain objects in tablespace might be referencing extents in that datafile that you have deleted, just as you are observing.
Try to follow steps sent by Tarun & do specify what error, if any, you do encounter during the process.
Regds
Girish
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
