Oracle Technologies Blog

By ASKM

ORA-01157: cannot identify/lock data file

Posted by Srikrishna Murthy Annam on January 7, 2011

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>
Advertisements

5 Responses to “ORA-01157: cannot identify/lock data file”

  1. Bhupesh said

    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

  2. learnwithme11g said

    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

  3. LALIT said

    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.

  4. learnwithme11g said

    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

  5. suman said

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: