ORA-01157: cannot identify/lock data file

Written By askMLabs on Friday, January 7, 2011 | 5:57 AM

In the present article, we will see how to start the database working with ASM if someone accidentally
~~deleted~~moved the datafile which resides on ASM storage to a different location.

The present case is similar to the case we do on normal storage.
If we
~~delete~~moved database file to a different location and if it is not updated in database, we will not be able to start the database.
So we need to update the database with this information to start it successfully.

ASMCMD> cd +DG1/testdb/datafile
ASMCMD> ls -lt
Type      Redund Striped Time             Sys Name
DATAFILE UNPROT COARSE   DEC 31 14:00:00 Y    TS1.256.739201677
ASMCMD> cp 'TS1.256.739201677' '/u01/datafiles/ts1.dbf'
copying +DG1/testdb/datafile/TS1.256.739201677 -> /u01/datafiles/ts1.dbf
ASMCMD>

Remove the datafile from ASM using rm command.

[oracle@dhcppc1 ~]$ srvctl start database -d testdb
PRCR-1079 : Failed to start resource ora.testdb.db
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '+TESTDG/testdb/datafile/test1.257.738755445'

CRS-2674: Start of 'ora.testdb.db' on 'dhcppc1' failed
[oracle@dhcppc1 ~]$

SQL> startup
ORACLE instance started.

Total System Global Area 723984384 bytes
Fixed Size                  1338980 bytes
Variable Size             478151068 bytes
Database Buffers          239075328 bytes
Redo Buffers                5419008 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '+TESTDG/testdb/datafile/test1.257.738755445'

SQL>

SQL> alter database datafile 7 offline drop;

~~Database altered.~~

SQL> alter database open;

~~Database altered.~~

SQL> alter database rename file '+DG1/testdb/datafile/TS1.256.739201677' to '/u01/datafiles/ts1.dbf';

Database altered.

SQL> alter database open;

Database altered.

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TS1';

TABLESPACE_NAME STATUS
--------------- ---------
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
-------------------------------------------------- ---------
/u01/datafiles/ts1.dbf                             AVAILABLE

SQL>

Hope it helps

SRI

ASMCMD> cd +DG1/testdb/datafile
ASMCMD> ls -lt
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE DEC 31 14:00:00 Y TS1.256.739201677
ASMCMD> cp 'TS1.256.739201677' '/u01/datafiles/ts1.dbf'
copying +DG1/testdb/datafile/TS1.256.739201677 -> /u01/datafiles/ts1.dbf
ASMCMD>

Share this article :

+ comments + 6 comments

Bhupesh

January 7, 2011 at 4:02 PM

Hi Sri

This will work. But you will be loosing the data which the datafile contains. You will keep on gets a hit for errors when refer to that file for a particular object.

Regards
Bhupesh

learnwithme11g

January 7, 2011 at 4:38 PM

Yes , You are right Bhupesh. Thanks for correcting this. Now i will replace the word "delete" with "move". This suits perfect now i think.
Thanks
SRI

LALIT

February 10, 2012 at 10:04 AM

Hi accidently I delete the the datafile and I don't have any backup. Is there any way to recreate the DATAFILE. I am doing below steps please suggest is that the correct way

SQL> ALTER DATABASE RENAME FILE 'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/ORCL/DATAFILE/O1_MF_WEBMINDX_76VLLZ02_.DBF' TO 'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/ORCL/DATAFILE/WEBM.DBF';
ALTER DATABASE RENAME FILE 'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/ORCL/DATAFILE/O1_MF_WEBMINDX_76VLLZ02_.DBF' TO 'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/ORCL/DATAFILE/WEBM.DBF'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 6 - new file
'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/ORCL/DATAFILE/WEBM.DBF' not found
ORA-01110: data file 6:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ORCL\DATAFILE\O1_MF_WEBMINDX_76VLLZ02_.DB
F'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

February 14, 2012 at 9:14 AM

Hi Lalit,
I hope your database is running in archive log mode.

Follow the below action plan
1) Shutdown database and start it with mount state
2) Find the missing file no with
select * from v$recover_file;
select * from v$datafile where FILE#=11;
3) Recreate the lost datafie
alter database create datafile '' as '' size 10240 reuse.
4) alter database datafile '' online;
5) Recove and open database
Recover database;
alter database open;

Hope It helps
SRI

suman

May 29, 2012 at 10:30 AM

i have the same issue here my oracle database is 9.2.0.4.0 here i have added a data file and accedantly droped the data file but in 9i we dont have option of dropping a data file and i droped using alter database "datafile name" offline drop and later physically the file was dropeed but logically in v$data files it showing and from application it is throwing error ORA 01110 and ORA 00372 unable to modify that particular data file so please some one help me out

UNKNOWN

September 27, 2020 at 6:52 AM

Thanks for taking the time to discuss this, I feel strongly about it and love learning more on this topic. If possible, as you gain expertise, would you mind updating your blog with more information? It is extremely helpful for me. Locksmith Little Rock

askMLabs