this is a follow up from this question.

Let me start by saying that i am NOT a DBA, so i'm really really lost with this.

A few weeks ago, we lost contact with one of our SID'S. All the other services are working, but this one in particular is not.

What we got was this message when trying to connect

ORA-01033: ORACLE initialization or shutdown in progress

An attempt to alter database open ended up in

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/xxx/xxx_data.dbf'

I tried to shutdown / restart the database, but got this message.

Total System Global Area  566231040 bytes
Fixed Size                  1220604 bytes
Variable Size             117440516 bytes
Database Buffers          444596224 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/xxx/xxx_data.dbf'

When all continued the same, I erased the dbf files (rm xxx_data.dbf xxx_index.dbf), and recreated them using touch xxx_data.dbf.

I also tried to recreate the tablespaces using


and got

Database not open

As I said, i don't know how bad this is, or how far i'm from gaining access to my database (well, to this SID at least, the others are working).

I would imagine that a last resource would be to throw everything away, and recreating it, but I don't know how to, and I was hoping there's a less destructive solution.

Any help will be greatly appreciated . Thanks in advance.

migrated from stackoverflow.com Mar 18 '10 at 20:36

This question came from our site for professional and enthusiast programmers.


Deleting the dbf files pretty much destroyed your database. You're going to need some expert assistance to have any hope of using the existing database.

You will need to restore from a backup. You do have a backup, right?

If you don't have a backup, then you will need to recreate the DB from the beginning, and import the data from another instance.

  • +1 for the importance of a backup. – APC Mar 19 '10 at 5:49

You could try to open the rest of your database. As DCookie said before, deleting the files (xxx_data.dbf xxx_index.dbf) has severely damaged your database. If you don't have any kind of backup and the data is really important to you, i can only stress the importance of not to fiddle around any longer with that system and get some help from oracle's professional services.

If you would like to try a recovery on yourself you could take the damaged tablespace offline and then offload the data from the rest of your database.

export ORACLE_SID=<your_lost_SID>
sqlplus /nolog
conn / as sysdba
startup mount
select TABLESPACE_NAME , FILE# from v$datafile_header where FILE# = 6;

At this point you can query the database for the name of the damaged tablespace. Now we are taking it offline. And start the rest of your database.

alter database datafile '/u01/app/oracle/oradata/xxx/xxx_data.dbf' offline;

If you are getting something like:

ORA-01145: offline immediate disallowed unless media recovery enabled

You are running your database in the noarchivelog mode. This is really bad in such a situation and you should stop here and ask for assistance! DO NOT GO ANY FURTHER NOW. Please! Just shutdown the instance. Maybe an expert can help.

If the tablespace is offline, you can continue and bring the rest of your database online.

alter database open;

Good Luck!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.