| DBF files are not deleted [message #379436] |
Tue, 06 January 2009 07:56  |
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  |
 |
Michel Cadot
Messages: 68666
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 #379558 is a reply to message #379473] |
Wed, 07 January 2009 00:53 |
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 #379570 is a reply to message #379566] |
Wed, 07 January 2009 01:30 |
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 |
ThomasG
Messages: 3211
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 |
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 #379592 is a reply to message #379590] |
Wed, 07 January 2009 03:56 |
ThomasG
Messages: 3211
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 |
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 |
ThomasG
Messages: 3211
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 |
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 #379905 is a reply to message #379898] |
Thu, 08 January 2009 05:34 |
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 #380070 is a reply to message #379924] |
Fri, 09 January 2009 00:44 |
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 #380083 is a reply to message #380073] |
Fri, 09 January 2009 01:09 |
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 #380086 is a reply to message #380084] |
Fri, 09 January 2009 01:25 |
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 |
trantuananh24hg
Messages: 744
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 |
ThomasG
Messages: 3211
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
