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
Subscribe to:
Post Comments (Atom)
31 comments:
The solution is simple although if someone know how to do :)
And regarding that person so can't u find out who did this ?
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.
Problem solved becoz of ur post.
Thanks.
welcome Aman :)
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? :)
thanks
Hi Nayyar,
I experienced the same error,and now my problem got fixed after following your solution.
Thank you
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 !!!
Thanks Man. You the boss. really appreciate
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
@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
Thanks alot,
After 1 hr solved the issue.
I deleted the database manually that was creating problem
Thanks it helped me !
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.?
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
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?
The solution is resolved my problem. Thanks nayyares.
this is best solution.. thanks a lot nayyar.
Thank You! Simple and direct solution....I realize that there is so much more to learn!
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
Thanks for the great help...saved my life today.
Thanks a lot Nayyar, great solution, it worked for me.
Dropping a datafile is never a solution in production - u lose data with it. Any alternate solutions
Raj, you may take tablespace offline, then rename corrupt filename, and restore plus recover file from backup!
thanks
Thanks this works
nayyar bhai the solution was great,now my database is working,thanks bhai
Thanks A lot Bhayya...It Worked For Me.
Very helpful. I was thinking to recover all with rman. Lol thanks!
Very helpful. I was thinking to recover all with rman. Lol thanks!
Post a Comment