Database won’t start because of missing datafile

Introduction

This post discusses an approach to bypass a database startup failure due to a missing datafile.  Datafiles are physical files used by the Oracle database for storing data. Dropping a datafile or tablespace results in permanent loss of data.  As such, this procedure should be only considered in environments such as Dev or Test where data loss is not an issue.

Main Article

I had a situation today where my database would not start because “someone” had deleted a datafile.  While deleting the datafile at the OS level was probably not a particularly good decision, things like this do happen occasionally, so how to get the database back up?

Here is the error message when the database refused to start:

ORA-01157: cannot identify/lock data file 14 - see DBWR trace file
ORA-01110: data file 14: 'C:\DATABASE\ORADATA\ORCL\CDBWKLW.DBF'

This command is the quickest and easiest way to get rid of the problem:

# Copyright 2012 Oracle Corporation. 
# All Rights Reserved. 
# 
# Provided on an 'as is' basis, without warranties or conditions of any kind, 
# either express or implied, including, without limitation, any warranties or 
# conditions of title, non-infringement, merchantability, or fitness for a 
# particular purpose. You are solely responsible for determining the 
# appropriateness of using and assume any risks. You may not redistribute.

alter database datafile 'c:\database\oradata\orcl\cdbwklw.dbf' offline drop;

After this the database will start happily.  You may need to drop the tablespace too.

Add Your Comment