iniy.org

oracle day 2: datafile is missing

2 min read

playing around with oracle the other day I added a new tablespace and thought it would be smart to put the corresponding datafile on my tmp-directory. At first everything worked fine, now some days later I wanted to check out the java jdbc connector for oracle, but the database didn’t start up. So first lesson I learned, thats where oracle errorlog-files are located:

/usr/lib/oracle/xe/app/oracle/admin/XE/bdump/alert_XE.log

so let’s see, what’s the content

... more lines ...
ALTER DATABASE OPEN
Tue Jun 22 21:45:07 2010
Errors in file /usr/lib/oracle/xe/app/oracle/admin/XE/bdump/xe_dbw0_8782.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/tmp/data.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
... more lines ...

so what happened is: during reboot my operating system deleted all files in /tmp including my datafile and without it oracle wouldn’t start.

first try: just drop the tablespace
as it was an empty test tablespace I just wanted to drop it with the following command

DROP TABLESPACE DATA INCLUDING CONTENTS AND DATAFILES;

JOINK!!!
there was an error, that the database was not opened, so apparently the database could not start because of the missing datafile, but to drop the errornous tablespace I had to start the database. luckily then I found how to do it.
http://www.mydigitallife.info/2007/08/04/how-drop-tablespace-and-recover-oracle-database-when-accidentally-delete-datafile/
CAUTION in my case it was an empty test database, in your case it may not, you better know what you are doing

  1. stop the database (eg. shutdown with sqlplus)
  2. connect with sqlplus as sysdba if not already done and issue the following command: STARTUP MOUNT;
  3. now drop the missing datafile with: ALTER DATABASE DATAFILE ‘/tmp/data.dbf’ OFFLINE DROP;
  4. open the database: ALTER DATABASE OPEN;
  5. and drop the tablespace: DROP TABLESPACE DATA INCLUDING CONTENTS; of course it’s just DATA in my case, your tablespace may have another name