Home » RDBMS Server » Server Administration » DATAFILE DELETED FROM THE DISK (9.0.2)
DATAFILE DELETED FROM THE DISK [message #328035] Wed, 18 June 2008 12:19 Go to next message
madman
Messages: 4
Registered: June 2008
Junior Member
Hi,

I was trying to upgrade from oracle 9i to 10g.Everything was going fine until I made a big mistake.I have deleted the DATAFILE manually from the disk and now when I try to do anything I'm experiencing errors.
Below are the errors and the datafile details ::


C:\Documents and Settings\devadm>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 18 17:33:00 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

SQL> select * from dba_objects;
select * from dba_objects
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only


SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 264838752 bytes
Fixed Size 454240 bytes
Variable Size 159383552 bytes
Database Buffers 103809024 bytes
Redo Buffers 1191936 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 24 - see DBWR trace file
ORA-01110: data file 24: 'D:\ORACLE\DEV\SAPDATA1SYSTEM.DATA2'


SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 264838752 bytes
Fixed Size 454240 bytes
Variable Size 159383552 bytes
Database Buffers 103809024 bytes
Redo Buffers 1191936 bytes
Database mounted.
SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 24: 'D:\ORACLE\DEV\SAPDATA1SYSTEM.DATA2'
ORA-01157: cannot identify/lock data file 24 - see DBWR trace file
ORA-01110: data file 24: 'D:\ORACLE\DEV\SAPDATA1SYSTEM.DATA2'


SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> select status from v$instance;

STATUS
------------
MOUNTED

this is the datafile I have manually deleted :
"ORA-01110: data file 24: 'D:\ORACLE\DEV\SAPDATA1SYSTEM.DATA2'"

Now I dont understand what could be done to make this working so that my upgrade to 10g is not interrupted.

Is there any process to again create the datafile of same name and proceed further.I have also tried to do it through sql but i get the same errors and was not able to do anything


please help
Re: DATAFILE DELETED FROM THE DISK [message #328038 is a reply to message #328035] Wed, 18 June 2008 12:24 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
I hope you have good backups & know how to successfully restore the database.
Re: DATAFILE DELETED FROM THE DISK [message #328039 is a reply to message #328035] Wed, 18 June 2008 12:27 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
>>I hope you have good backups & know how to successfully restore the database.

If he knew,why would have he asked?


@OP
How did you upgrade?
Re: DATAFILE DELETED FROM THE DISK [message #328041 is a reply to message #328039] Wed, 18 June 2008 12:33 Go to previous messageGo to next message
madman
Messages: 4
Registered: June 2008
Junior Member
i still didnt complete the upgrade process I have just install a patch for oracle 9i then installed 10g, still there are many a scripts to be executed but unfortunately I'm stuck
Re: DATAFILE DELETED FROM THE DISK [message #328057 is a reply to message #328041] Wed, 18 June 2008 13:51 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Doesn't matter.

Your DATA is gone.

The only two options left are :

- Restore the data from backup OR
- Delete the database and create a new one.

You can't continue the upgrade process from the point you are stuck now.
Re: DATAFILE DELETED FROM THE DISK [message #328059 is a reply to message #328057] Wed, 18 June 2008 14:01 Go to previous messageGo to next message
madman
Messages: 4
Registered: June 2008
Junior Member
as it is the system tablespace datafile which was deleted im in trouble...is it possible to create the system tablespace again by deleting the current system tablespace?

Also I have a small trick currently developing in my mind.
can a system tablespace datafile with the same name created on a different computer and then copied to this computer at the exactly same location from where the database file was created???
Re: DATAFILE DELETED FROM THE DISK [message #328060 is a reply to message #328059] Wed, 18 June 2008 14:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Also I have a small trick currently developing in my mind.
may be with other technologies. Not with Oracle.
I just cannot digest that you were upgrading without a backup.
Did you try your luck with windows recycle bin? Or did you wipe it real clean?
icon8.gif  Re: DATAFILE DELETED FROM THE DISK [message #328062 is a reply to message #328060] Wed, 18 June 2008 14:10 Go to previous messageGo to next message
madman
Messages: 4
Registered: June 2008
Junior Member
unfortunately wiped it real clean Sad Embarassed Mad Shocked
Re: DATAFILE DELETED FROM THE DISK [message #328068 is a reply to message #328062] Wed, 18 June 2008 15:21 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
You're a madman Wink
Re: DATAFILE DELETED FROM THE DISK [message #328180 is a reply to message #328035] Thu, 19 June 2008 04:33 Go to previous message
abed24
Messages: 16
Registered: October 2006
Location: Jordan
Junior Member

try to recover the file using third party program,
such as http://www.recovermyfiles.com/

Regards
Previous Topic: 10g error ora-12514 tns:listener does not currently know of service requested in connect descriptor
Next Topic: Last Modified Date
Goto Forum:
  


Current Time: Fri Dec 09 00:12:44 CST 2016

Total time taken to generate the page: 0.20466 seconds