ORA-01157: cannot identify/lock data file

[oraclekb]

Environment:
OS: IBM AIX 5.1
DB: Oracle9i R9.2


I am getting this error when selecting from the tables in this tablespace:

ORA-01157: cannot identify/lock data file 18 - see DBWR trace file
ORA-01110: data file 18: '/oracle/oradata/POL1/NETRMS_UCR.dbf'

I checked the file does exist in the specified location.

Thanks for all the replies.

[aarroyob]

Have you looked at dbwr trace file as suggested in the above error?

[oraclekb]

I checked the trace file..not much from there

ORA-01157: cannot identify/lock data file 18 - see DBWR trace file
ORA-01110: data file 18: '/oracle/oradata/POL1/NETRMS_UCR.dbf'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

Repetadily I am getting this in the trace file.

Thanks for the replies.

[marist89]

Show me the output from :

groups
ls -lg /oracle/oradata/POL1/NETRMS_UCR.dbf

[oraclekb]

dpdhqha0@/home/oracle=>ls -lg /oracle/oradata/POL1/NETRMS_UCR.dbf

Output is like:
lrwxrwxrwx 1 dba 16 Mar 26 08:57 /oracle/oradata/POL1/NETRMS_UCR.dbf -> /dev/rNETRMS_UCR

Thanks for helping me.

[marist89]

OK, it's a link. How about:
ls -lg /dev/rNETRMS_UCR

[oraclekb]

Jeff,

Thanks for your attention and helping me. The output to ls -lg looks like:

dpdhqha0@/home/oracle=>ls -lg /dev/*NET*
brw-rw---- 1 dba 103, 13 Mar 25 16:53 /dev/NETRMS
brw-rw---- 1 dba 103, 14 Mar 25 16:53 /dev/NETRMS_SECURITY
brw-rw---- 1 dba 103, 15 Mar 25 16:53 /dev/NETRMS_UCR
crw-rw---- 1 dba 103, 13 Mar 25 16:53 /dev/rNETRMS
crw-rw---- 1 dba 103, 14 Mar 25 16:53 /dev/rNETRMS_SECURITY
crw-rw---- 1 dba 103, 15 Mar 25 16:53 /dev/rNETRMS_UCR

[marist89]

Hmm. What we got here is a link pointing to a block device. /oracle/oradata/POL1/NETRMS_UCR.dbf is a link that points to the raw device /dev/rNETRMS_UCR. Usually in a case like this, /dev/rNETRMS_UCR isn't accessible from the host. You may want to try and dd the data off that device just to verify you can access it.

Otherwise, the error is telling you Oracle can't read /oracle/oradata/POL1/NETRMS_UCR.dbf and identify it as an oracle file. I would guess it's something to do either with permissions on the device or the device has some error. I would check your system messages file (/var/adm/messages in Solaris, not sure in AIX) to see if there are any system messages with this device.

[marist89]

Actually, your link is pointing to the character device and not the block device. I would look at my other datafiles in my database to make sure they are looking at the rdev instead of the dev.

In other words, you may need to point /oracle/oradata/POL1/NETRMS_UCR.dbf to /dev/NETRMS_UCR instead of /dev/rNETRMS_UCR.

[yls177]

Originally posted by marist89
Show me the output from :

groups
ls -lg /oracle/oradata/POL1/NETRMS_UCR.dbf

mine output is "/oracle/sid/sapdata1/facti_2/facti.data2"

so what could be wrong?