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.

Answer 2

I've found this problem in a RAC environment.

Case the environment is a RAC - real application cluster, check if the datafile is in a shared storage and visible in all nodes.

Here is a example, my environment uses ASM - Automatic Storage Management to storage the datafiles, but my tablespace users is in filesystem non shared:

One way is:

• Backup the datafile as copy to ASM;
• Switch datafile to copy;
• Recover datafile.

NOTE: make it in the node where the file is visible.

Example using tablespace users:

SQL> select file_id,file_name,tablespace_name from dba_data_files where tablespace_name = 'USERS';

   FILE_ID FILE_NAME                                                                                            TABLESPACE
---------- ---------------------------------------------------------------------------------------------------- ----------
        29 /home/oracle/tbsusersdata_D-MOOTDB_I-1967025112_TS-USERS_FNO-29_6qv2k42g                             USERS

In RMAN:

backup as copy datafile 29 format '+DATA/MOOTDB/DATAFILE/users01.dbf';
switch datafile 29 to copy;
recover datafile 29;