OraFAQ Forum: Server Administration » DATAFILE DELETED FROM THE DISK

Home » RDBMS Server » Server Administration » DATAFILE DELETED FROM THE DISK (9.0.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DATAFILE DELETED FROM THE DISK [message #328035]

Wed, 18 June 2008 12:19

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

Report message to a moderator

Re: DATAFILE DELETED FROM THE DISK [message #328038 is a reply to message #328035]

Wed, 18 June 2008 12:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I hope you have good backups & know how to successfully restore the database.

Report message to a moderator

Re: DATAFILE DELETED FROM THE DISK [message #328039 is a reply to message #328035]

Wed, 18 June 2008 12:27

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?

Report message to a moderator

Re: DATAFILE DELETED FROM THE DISK [message #328041 is a reply to message #328039]

Wed, 18 June 2008 12:33

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

Report message to a moderator

Re: DATAFILE DELETED FROM THE DISK [message #328057 is a reply to message #328041]

Wed, 18 June 2008 13:51

ThomasG
Messages: 3212
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.

Report message to a moderator

Re: DATAFILE DELETED FROM THE DISK [message #328059 is a reply to message #328057]

Wed, 18 June 2008 14:01

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???

Report message to a moderator

Re: DATAFILE DELETED FROM THE DISK [message #328060 is a reply to message #328059]

Wed, 18 June 2008 14:04

Mahesh Rajendran
Messages: 10707
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?

Report message to a moderator