Thursday, August 6, 2009

ORA-01157: cannot identify/lock data file

Yesterday when we were testing auto startup of development environment databases, i found that one of the database is getting following error:

ERROR at line 1:
ORA-01157: cannot identify/lock data file 29 - see DBWR trace file
ORA-01110: data file 29:
'/opt/oracle/product/oracle9i/dbs/C:oracleproduct10.2.0oradatapentahoptho_ts.dbf
'

from above datafile name you have realized that its a kinda jerk :s someone has made a datafile with no sense and then he/she have removed the file by O.S command, but he/she did'nt updated database about it !

So during the test when we were starting this database it came untill mount stage and then got stuck !!! i.e.

Problem:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 29 - see DBWR trace file
ORA-01110: data file 29:
'/opt/oracle/product/oracle9i/dbs/C:oracleproduct10.2.0oradatapentahoptho_ts.dbf
'

so to fix it, i did following:

Solution:


SQL>alter database datafile 29 OFFLINE DROP;
SQL>alter database open;


cheers

31 comments:

Sohail Akhtar said...

The solution is simple although if someone know how to do :)

And regarding that person so can't u find out who did this ?

nayyares said...

As these databases are in Development environment so we d'nt have audit & trail, it is quite possible to track in case if auditing is enabled.

Aman said...
This comment has been removed by the author.
Aman said...

Problem solved becoz of ur post.
Thanks.

nayyares said...

welcome Aman :)

Eddy B. said...
This comment has been removed by the author.
Eddy B. said...

Hi, I'm having a similiar problem with you.

I can open database using

ALTER DATABASE OPEN;

but when I tried to execute

ALTER TABLESPACE users ONLINE;

I got this error :

ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01111: name for data file 9 is unknown - rename to correct file
ORA-01110: data file 9: 'D:\ORACLE\ORA90\DATABASE\MISSING00009'


any advice? :)

Ghulam Mujtaba Kori said...

thanks

Unknown said...

Hi Nayyar,

I experienced the same error,and now my problem got fixed after following your solution.

Thank you

Unknown said...

I tried hard with other options I had and was about to do a reinstall of the database. Then I got this option which worked like a charm. Thank you very much !!!

Unknown said...

Thanks Man. You the boss. really appreciate

Rani said...

On SPARCEnterpriseT5220 server. which is running on Solaris 10 and connected to NAS Device


root@lmsop007 # nfsstat -m /data03



/data03 from 192.168.128.10:/raid0/data/nfs_test

Flags: vers=3,proto=tcp,sec=sys,hard,nointr,noac,link,symlink,acl,rsize=32768,wsize=32768,retrans=5,timeo=600

Attr cache: acregmin=3,acregmax=60,acdirmin=30,acdirmax=60

We are trying to restore some compressed RMAN backup pieces to /data03 on the NAS device, but the RMAN process hangs when trying to write the files to the NAS filesystem. If the destination for the RMAN files is changed to a filesystem mounted on internal disks, it works fine.



Additionally, if we create our own files on /data03, there is no issue, and they can be edited and manipulated with mv, cp, etc. with no issue.



So we think the issue is due to the interaction of RMAN and NFS – can you help? The NAS in question is a Thecus N3200

nayyares said...

@Rani,

in such situation i would try following i.e.

1. check disk space at NAS.
2. check permission given on NAS's mount point to RMAN/ORACLE user.
3. check is there any firewall exist between NAS-to-dbserver network link, if yes, check its keep-alive time.
4. check OS TCP keep-alive time.
5. check your ssh client keep-alive time.
6. try to simulate same rman restore on same NAS server but on different mount point, would be better if completely different disc.
7. we had same issue with solaris 9 & oracle 9 rman restore few years ago, but we fixed it by changing IPs. though it mostly happen with cisco firewall equipment.

HTH

Anmol More said...

Thanks alot,
After 1 hr solved the issue.
I deleted the database manually that was creating problem

D O N I V said...

Thanks it helped me !

Prasad said...

Hi,
Please suggest..

I have 11g database, in USER01.dbf is 11gb and TEMP01.dbf is 8GB. i want to reduce the size. how can i achive it.?

nayyares said...

Parsad, you did'nt gave context detail of your objective, in case if you think smaller files will still serve the cause, you can recreate the files with smaller sizes. But i would never recommend you to resize the files unless you have some really good reason.

thanks

syed said...

i ve d same pblm (working on test DB) i deleted 1 DF n started my DB , d same error (01157) was displayed, without any info about d lost DF how do i come to know if there is 1 or more loss of DF?

Vinoth - Oracle DBA said...

The solution is resolved my problem. Thanks nayyares.

mahesh said...

this is best solution.. thanks a lot nayyar.

Kejriman said...

Thank You! Simple and direct solution....I realize that there is so much more to learn!

god Of DeViLs said...

Hi Naayar,

Thanks very much for this simple solution, i have been looking out for this solution for hours,
This was the most simple one.

Thanks Very much

Vishwa

Javed A. Khan said...

Thanks for the great help...saved my life today.

Farhan Ahmed said...

Thanks a lot Nayyar, great solution, it worked for me.

Raj said...

Dropping a datafile is never a solution in production - u lose data with it. Any alternate solutions

nayyares said...

Raj, you may take tablespace offline, then rename corrupt filename, and restore plus recover file from backup!

thanks

Kasun Amarasinghe said...

Thanks this works

Kazim said...

nayyar bhai the solution was great,now my database is working,thanks bhai

Ravi Kumar said...

Thanks A lot Bhayya...It Worked For Me.

Unknown said...

Very helpful. I was thinking to recover all with rman. Lol thanks!

Unknown said...

Very helpful. I was thinking to recover all with rman. Lol thanks!