Skip to Main Content
  • Questions
  • Unable to open the database: ORA-01157: cannot identify/lock data file

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Gyan.

Asked: March 01, 2017 - 5:08 am UTC

Last updated: October 11, 2017 - 9:36 am UTC

Version: 11.2.0.4

Viewed 50K+ times! This question is

You Asked

first run the command startup nomount, done, then run the query, alter database mount, done, also open the control files, then run the query as:
ALTER DATABASE OPEN, gives the following issue as:
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/orcl/users01.dbf'



and we said...

Oracle is unable to access the file, either because it doesn't exist or something else has gone wrong!

MOS note 184327.1 discusses causes and solutions:


Common Causes and Solutions for ORA-1157

Note: Throughout this note we refer to "backups" but if you have a valid physical standby database
you may also use the standby database's datafiles to recover the primary database.

1. The datafile does exist, but Oracle cannot find it.

The datafile may have been renamed at the operating system level, moved to a different directory or disk drive either intentionally or unintentionally.

In this case, restore and recover the datafile or move the datafile to its original name.

2. The datafile does not exist or is unusable by Oracle. The datafile has been physically removed or damaged to an extent that Oracle cannot recognize it anymore.

For example, the datafile might be truncated or overwritten, in which case
ORA-27046 will accompany ORA-1157 error.

For example:

ORA-27046: file size is not a multiple of logical block size

In this case, the user has two options:

A Recreate the tablespace that the datafile belongs to.


This option is best suited for USERS, INDEX, TEMPORARY tablespaces.

It is also recommended for UNDO tablespaces if the database had been SHUTDOWN CLEANLY, so that no active transactions are there in the rollback segments of this tablespace.

If the tablespace is SYSTEM tablespace, then this amounts to recreating or rebuilding the database.

This method is best suited for temporary tablespaces (since they do not contain important data), but can be used for USERS tablespaces and INDEXES tablespaces.

This method would be helpful wherein reasonably recent exports of the objects in the tablespace are available, or that the tables in the tablespace can be repopulated by running a script or program, loading the data through SQL*Loader, etc.

The steps involved are:

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;

Note: The users can stop with this step if they do not want the
tablespace anymore in the database.

5. Recreate the tablespace.

CREATE TABLESPACE tablespace_name DATAFILE 'datafile_full_path_name' SIZE required_size;

6. Recreate all the previously existing objects in the tablespace.

This can be done using the creation scripts for the objects in that tablespace or using the recent export dump available for that tablespace objects.


B. Recover the datafile using normal recovery procedures.

This option is best suited for READ ONLY tablespaces and for USERS, INDEX tablespaces where recreating is not a feasible option.

If the tablespace is of type UNDO, then this is the method to be used if the database was not SHUTDOWN CLEANLY.
(that is, if shutdown abort had been used or the database had crashed)

If the tablespace is SYSTEM, then this is the recommended method, if there are backups and archivelogs are available. If the database is in
NOARCHIVELOG mode, then you can recover only if the required changes are present in the ONLINE redologs.

In many situations, recreating the user tablespace is impossible or too laborious. The solution then is to restore the lost datafile from a backup
and do media recovery on it. If the database is in NOARCHIVELOG mode, you will only succeed in recovering the datafile if the redo to be applied
to the datafile is within the range of the online logs.

This method would be ideal for READ ONLY tablespaces. If the tablespace was not switched to READ-WRITE after backup was taken and if the tablespace was
READ ONLY at the time of backup, then recovery is just restoring the backup of this tablespace.

These are the steps:

1. Restore the lost file from a backup.

2. If the database is down, mount it.

STARTUP MOUNT;

3. Issue the following query:

SELECT V1.GROUP#, MEMBER, SEQUENCE#,
FIRST_CHANGE#
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# ;

This will list all your online redolog files and their respective sequence and first change numbers.

4. If the database is in NOARCHIVELOG mode, issue the query:

SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;

If the CHANGE# is GREATER than the minimum FIRST_CHANGE# of your logs, the datafile can be recovered. Just keep in mind that all the logs to
applied will be online logs, and move on to step 5.

If the CHANGE# is LESS than the minimum FIRST_CHANGE# of your logs, the file cannot be recovered. Your options at this point would be to restore
the most recent full backup (and thus lose all changes to the database since) or recreate the tablespace as explained in scenario a.


5. Recover the datafile:

RECOVER DATAFILE 'full_path_file_name' ;

6. Confirm each of the logs that you are prompted for until you receive the message "Media Recovery Complete". If you are prompted for a non-existing
archived log, Oracle probably needs one or more of the online logs to proceed with the recovery. Compare the sequence number referenced in the
ORA-280 message with the sequence numbers of your online logs. Then enter the full path name of one of the members of the redo group whose sequence
number matches the one you are being asked for. Keep entering online logs as requested until you receive the message "Media Recovery Complete" .

7. If the database is at mount point, open it.

Operating Systems (OS) Tempfiles missing:

When using TEMPORARY tablespaces with tempfiles, the absence of the tempfile at the OS level can cause ORA-1157. Since Oracle does not checkpoint tempfiles, the database can be opened even with missing tempfiles.

The solution in this case would be to drop the logical tempfile and add a new one.

For example:

select * from dba_objects order by object_name;
select * from dba_objects order by object_name;
*
ERROR at line 1:

ORA-01157: cannot identify/lock data file 1026 - see DBWR trace file
ORA-01110: data file 1026: '/Oracle/oradata/temp2_01.tmp'
Solution:

alter database tempfile '/Oracle/oradata/temp2_01.tmp' drop;

select tablespace_name, file_name from dba_temp_files;

alter tablespace temp2 add tempfile '/Oracle/oradata/temp2_01.tmp' size 5m;


There are some other possibilities. If the above doesn't help, to go My Oracle Support and check out doc 184327.1

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Gyan Tamang, March 01, 2017 - 3:51 pm UTC


Krutagn Patel, August 26, 2017 - 6:22 am UTC

Thanks that's work exactly what i want.

rich, October 10, 2017 - 11:05 pm UTC

great explanation of how to solve this problem. It was my fault I wrecked the DB, but you provided the exact way to recover, Thanks you.

Chris Saxon
October 11, 2017 - 9:36 am UTC

Thanks, though credit goes to the author of the MOS note (184327.1)

Great explanation

Diego Andrade, May 04, 2018 - 6:03 pm UTC

Thanks for this helpfull article.
It helped me to solve my problem in just 3 min.
Thanks a lot!

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.