Home » RDBMS Server » Server Administration » DBF files are not deleted (10.2.0)
DBF files are not deleted [message #379436] Tue, 06 January 2009 07:56 Go to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Hi All,

I have oracle DB with multipules SIDs in that.Now I deleted the SID(using DBCA) but still there are .dbf files exists in the SID which are not deleted even on SID deletion.

I tried to delete them manually but I get the error "already in use"

I tried to run the following query -


SQL> ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\emdb\AGLP2118TEMP.DBF' OFFLINE DROP;


It ran successfully but files are not deleted.

Could you please tell me how can I delete there files.

Thanks
Vipin Kumar
Re: DBF files are not deleted [message #379439 is a reply to message #379436] Tue, 06 January 2009 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use rm.
This command does not delete the file and it is not its purpose, it just inform Oracle that you have deleted or will delete the file.
Moreover the full command is:
ALTER DATABASE DATAFILE '...' OFFLINE FOR DROP;


Regards
Michel

[Updated on: Tue, 06 January 2009 08:02]

Report message to a moderator

Re: DBF files are not deleted [message #379452 is a reply to message #379439] Tue, 06 January 2009 10:10 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Thanks.

We are using Windows Server.


Regards
Vipin Kumar
Re: DBF files are not deleted [message #379457 is a reply to message #379452] Tue, 06 January 2009 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dhimanvipin wrote on Tue, 06 January 2009 17:10
We are using Windows Server.

Then use DEL

Regards
Michel

Re: DBF files are not deleted [message #379459 is a reply to message #379457] Tue, 06 January 2009 10:40 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Thanks.

I tried the same but it is giving problem "already in use"

Please suggest.

Regards
Vipin Kumar
Re: DBF files are not deleted [message #379461 is a reply to message #379459] Tue, 06 January 2009 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is because of this b... of Windows, the process holds the file until it ends. So you will be able to delete the file only after the instance shut down.

Regards
Michel
Re: DBF files are not deleted [message #379466 is a reply to message #379461] Tue, 06 January 2009 11:33 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Thanks Michel.

The SID is already deleted so there would not be any service running for this SID so which instance I should stop.

Regards
Vipin Kumar
Re: DBF files are not deleted [message #379473 is a reply to message #379466] Tue, 06 January 2009 12:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you kill the service or instance, it may that some handles are in Windows memory.
Then you have to restart the server to free them.

Regards
Michel
Re: DBF files are not deleted [message #379558 is a reply to message #379473] Wed, 07 January 2009 00:53 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
I followed the same and manully deleted these files.

But now other SIDs are not coming up giving errors-

ORA-01033: Oracle Initialization in process.

Please suggest what to do now.

Regards
Vipin Kumar
Re: DBF files are not deleted [message #379566 is a reply to message #379558] Wed, 07 January 2009 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at alert.log to know what happened during startup.

Regards
Michel
Re: DBF files are not deleted [message #379570 is a reply to message #379566] Wed, 07 January 2009 01:30 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Here is some part of the requested logs -

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
Wed Jan 07 12:36:21 2009
alter database mount exclusive
Wed Jan 07 12:36:27 2009
Setting recovery target incarnation to 2
Wed Jan 07 12:36:28 2009
Successful mount of redo thread 1, with mount id 475500677
Wed Jan 07 12:36:28 2009
Database mounted in Exclusive Mode
Completed: alter database mount exclusive
Wed Jan 07 12:36:28 2009
alter database open
Wed Jan 07 12:36:32 2009
Errors in file d:\oracle\product\10.2.0\admin\emtier3\bdump\emtier3_dbw0_132.trc:
ORA-01157: cannot identify/lock data file 126 - see DBWR trace file
ORA-01110: data file 126: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMDB\MLVPSCHEMA.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

Wed Jan 07 12:36:32 2009
Errors in file d:\oracle\product\10.2.0\admin\emtier3\bdump\emtier3_dbw0_132.trc:
ORA-01157: cannot identify/lock data file 127 - see DBWR trace file
ORA-01110: data file 127: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMDB\MLVPINDEXES.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.



Regards
Vipin Kumar
Re: DBF files are not deleted [message #379579 is a reply to message #379570] Wed, 07 January 2009 02:27 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And? Have you actually READ what you copy/pasted?

ORA-01110: data file 126: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMDB\MLVPSCHEMA.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


It seems you have deleted a datafile from the file system that was not dropped before.
Re: DBF files are not deleted [message #379586 is a reply to message #379579] Wed, 07 January 2009 03:04 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Thanks

Yes I did read.

I just stopped the service and deleted the same..

Did i do some mistake?

Does you mean i should have use the drop command first and then do the manual delete?

Regards
Vipin
Re: DBF files are not deleted [message #379589 is a reply to message #379586] Wed, 07 January 2009 03:29 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yes.

Datafiles have to be dropped first from Oracle with the drop command, then deleted form the filesystem with the OS delete command.

Re: DBF files are not deleted [message #379590 is a reply to message #379589] Wed, 07 January 2009 03:47 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Ohh ..

So how to recover these files?
Re: DBF files are not deleted [message #379592 is a reply to message #379590] Wed, 07 January 2009 03:56 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Depends. Did you WANT do drop them?

If not, and you still need them and the data in them, then then you would have to restore from a backup.

If you don't need them , and you wanted to drop them anyway, then open the database into the MOUNT stage, drop them, then OPEN the database.

Detailed steps are here.
Re: DBF files are not deleted [message #379602 is a reply to message #379592] Wed, 07 January 2009 05:06 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
I want to drop them at the same time want to run the db which is not up at this point(due to this problem).

But I have others SID(in the same DB) which is running and I can only logged into the DB by that SID.

So can I follow the process told by you by using in some other SID?
Re: DBF files are not deleted [message #379605 is a reply to message #379602] Wed, 07 January 2009 05:42 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

But I have others SID(in the same DB)



That makes no sense. A database instance can only have one SID.

Anything you want do to with that database you have do to with the SID of that database.

Re: DBF files are not deleted [message #379621 is a reply to message #379605] Wed, 07 January 2009 06:34 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
I think i am not able to understand you.

Here is the example -

Suppose i have oracle DB installed at my server and there are three SIDs A,B and C.

Now I created some users in A and wrongly give the path of B's directory during creating of tablespace

eg
Create tablespace ......
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\B\MLVPSCHEMA.DBF'


Now I deleted B SID but these files are not deleted from B's directory because there are currently in use by some user in A SID.

Now I stopped all the services(for all SID's) and manually deleted this file.

and restart the services for A. now when i tried to logged in with some user in A then i got the problem mentioned by me.

Now I can not logged in to A SID becuase of that problem but I can login to C.

So my question is can i follow the steps told by u by login into C?

Hope I am understood.



Re: DBF files are not deleted [message #379624 is a reply to message #379621] Wed, 07 January 2009 06:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Suppose i have oracle DB installed at my server and there are three SIDs A,B and C.

So you meant you have 3 databases.

Quote:
Now I deleted B SID but these files are not deleted from B's directory because there are currently in use by some user in A SID.

Now I stopped all the services(for all SID's) and manually deleted this file.

and restart the services for A. now when i tried to logged in with some user in A then i got the problem mentioned by me.

So you deleted A files and you either have to restore from a backup or connect to A (as SYSDBA) to ALTER DATABASE ... DROP" in A (and lose your data).

Regards
Michel
Re: DBF files are not deleted [message #379640 is a reply to message #379624] Wed, 07 January 2009 07:33 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Thanks Michel.

But question is how would I connect to A?

Regards
Vipin
Re: DBF files are not deleted [message #379641 is a reply to message #379640] Wed, 07 January 2009 07:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
C:\> set ORACLE_SID=A
C:\> sqlplus / as sysdba

Regards
Michel

Re: DBF files are not deleted [message #379893 is a reply to message #379641] Thu, 08 January 2009 04:32 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Since SID A is not up( and not comming up due to this problem) then how could i loggin to this SID.

Please suggest.
Re: DBF files are not deleted [message #379894 is a reply to message #379592] Thu, 08 January 2009 04:33 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
I tried this but alter command is not working.

it is saying no such files, logs etc exist.

I am going into big problem now. Please help me out.

Re: DBF files are not deleted [message #379898 is a reply to message #379894] Thu, 08 January 2009 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I tried this but alter command is not working.

This is NOT an Oracle message.

Copy and paste (in text not screen shot) what you did and what you got.

Regards
Michel
Re: DBF files are not deleted [message #379905 is a reply to message #379898] Thu, 08 January 2009 05:34 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
As suggested by ThomasG

I ran the follwing -

ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMDB\MLVPINDEXES.DBF' OFFLINE DROP;


and got the following error

Quote:

ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMDB\MLVPINDEXES.DBF' OFFLINE DROP
*
ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile
"D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMDB\MLVPINDEXES.DBF"



Regards
Vipin
Re: DBF files are not deleted [message #379924 is a reply to message #379905] Thu, 08 January 2009 08:05 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Were you doing this against a MOUNTed database or an OPEN database?
Re: DBF files are not deleted [message #380070 is a reply to message #379924] Fri, 09 January 2009 00:44 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Hi did the following-
1. Shutdown immediate
2.CONNECT / AS SYSDBA
3.STARTUP MOUNT;
4.ALTER DATABASE DATAFILE ‘<datafile name with complete path>’ OFFLINE DROP;



Then it gives me

Quote:


ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMDB\MLVPINDEXES.DBF' OFFLINE DROP
*
ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile
"D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMDB\MLVPINDEXES.DBF"

Re: DBF files are not deleted [message #380073 is a reply to message #380070] Fri, 09 January 2009 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So this file does not belong to this database.

Regards
Michel
Re: DBF files are not deleted [message #380083 is a reply to message #380073] Fri, 09 January 2009 01:09 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Thanks Michel.

It belongs to this database.

If I delete it then I get the error
Quote:

ORA-01110: data file 126: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMDB\MLVPINDEXES.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.





Regards
Vipin Kumar
Re: DBF files are not deleted [message #380084 is a reply to message #380083] Fri, 09 January 2009 01:14 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Quote:
If I delete it then I get the error


Quote:
ORA-01110: data file 126: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMDB\MLVPINDEXES.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.



Are you sure; When delete your database your getting this error??

When try to delete (from OS level; using DEL key) You got any pop-up message??

Babu

Re: DBF files are not deleted [message #380086 is a reply to message #380084] Fri, 09 January 2009 01:25 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Earlier it was giving the error " in use somthing" but it is not giving this error now by pressing DEL.

But in error logs its shows the -
Quote:

ORA-01110: data file 126: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMDB\MLVPINDEXES.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.




Please suggest.

Regards
Vipin Kumar
Re: DBF files are not deleted [message #380095 is a reply to message #380086] Fri, 09 January 2009 02:39 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
1. How many databases have you got now?
2. What's datafile in which database have you tried to delete?
3. Post your steps clearly again. For example
C:\>set oracle_sid=meta

C:\>sqlplus /"as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jan 9 15:43:00 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

sys@META>

3. Define which datafile and its status you've just deleted. For example
sys@META> @sqlplus\tbs
+------------------------------------------------------------------+
+--Identified tablespaces associated with datafiles--+
+------------------------------------------------------------------+

TBS_NAME             DFT_NAME                                 MB STATUS
-------------------- ------------------------------ ------------ -------
SYSTEM               C:\ORACLE\PRODUCT\10.2.0\METAD          480 SYSTEM
                     ATA\META\SYSTEM01.DBF

UNDOTBS1             C:\ORACLE\PRODUCT\10.2.0\METAD           30 ONLINE
                     ATA\META\UNDOTBS01.DBF

SYSAUX               C:\ORACLE\PRODUCT\10.2.0\METAD          260 ONLINE
                     ATA\META\SYSAUX01.DBF

USERS                C:\ORACLE\PRODUCT\10.2.0\METAD            5 ONLINE
                     ATA\META\USERS01.DBF

EXAMPLE              C:\ORACLE\PRODUCT\10.2.0\METAD          100 ONLINE
                     ATA\META\EXAMPLE01.DBF

LOGMNR               C:\ORACLE\PRODUCT\10.2.0\METAD       10,240 ONLINE
                     ATA\META\LOGMNR01.DBF

STREAM               C:\ORACLE\PRODUCT\10.2.0\METAD       10,240 ONLINE
                     ATA\META\STREAM01.DBF


7 rows selected.

sys@META>
.

You can use this simple script:
sys@META> host cat sqlplus\tbs.sql
Prompt +------------------------------------------------------------------+
Prompt +--Identified tablespaces associated with datafiles--+
Prompt +------------------------------------------------------------------+


set termout on
set feedback on
set linesize 150
col tbs_name format a20
col dft_name format a30
col MB format 999,999,999

select  vt.name tbs_name,
        vd.name dft_name,
        vd.bytes/1024/1024 MB,
        vd.status
from v$tablespace vt, v$datafile vd
where vt.ts#=vd.ts#
/

col tbs_name clear
col dft_name clear
col MB clear

sys@META>

[Updated on: Fri, 09 January 2009 02:42]

Report message to a moderator

Re: DBF files are not deleted [message #380173 is a reply to message #380086] Fri, 09 January 2009 07:02 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
dhimanvipin wrote on Fri, 09 January 2009 08:25
Earlier it was giving the error " in use somthing" but it is not giving this error now by pressing DEL.

But in error logs its shows the -
Quote:

ORA-01110: data file 126: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMDB\MLVPINDEXES.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.




Please suggest.

Regards
Vipin Kumar



This would suggest that once you have deleted a file it can no longer be opened. Which seems rather obvious to me.

Previous Topic: Oracle 9i release
Next Topic: forget my user id and password what to do ?
Goto Forum:
  


Current Time: Thu Dec 08 04:05:00 CST 2016

Total time taken to generate the page: 0.10811 seconds