Get ORA-01157 when startup database

Question

I created a datafile /tmp/encrypted_ts.dbf. When the machine started again, the datafile was gone. When I started it I got ORA-01157, the details are:

ORA-01157: cannot identify/lock data file 54 - see DBWR trace file
ORA-01110: data file 54: '/tmp/encrypted_ts.dbf'

So I ran alter database datafile 54 offline drop;, and I got ORA-01516. How can I startup the database again?

Answer 1

Let me show you what Oracle says about your errors.

ORA-01157: cannot identify/lock data file string - see DBWR trace file

Cause: The background process was either unable to find one of the data files or failed to lock it because the file was already in use. The database will prohibit access to this file but other files will be unaffected. However the first instance to open the database will need to access all online data files. Accompanying error from the operating system describes why the file could not be identified.

Action: Have operating system make file available to database. Then either open the database or do ALTER SYSTEM CHECK DATAFILES.

ORA-01516: nonexistent log file, data file, or temporary file "string"

Cause: An attempt was made to use ALTER DATABASE to rename a log file, data file, or temporary file; or to change attributes of a data file or temporary file (for example, resize, autoextend, online or offline); or to re-create or move a data file. The attempt failed because the specified file is not known to the database's control file or is not of a type supported by the request.

Action: Specify the name or number of an existing file of the correct type, as appropriate. Check the relevant V$ table for a list of possible files.

In this case, you can recreate the tablespace that the datafile belongs to:

1. If the database is down, mount it.

   STARTUP MOUNT;
   

2. Offline drop the datafile.

   ALTER DATABASE DATAFILE 'full_path_file_name' OFFLINE DROP;
   

3. If the database is at mount, open it.

   ALTER DATABASE OPEN;
   

4. Drop the user tablespace.

   DROP TABLESPACE tablespace_name INCLUDING CONTENTS;
   

5. Recreate the tablespace.

I have tested the above steps to simulate your problem, created new tablespace with the datafile in /tmp directory, and it worked perfectly for me.