ORA-01157: cannot identify/lock data file

Printable View

03-28-2003, 08:00 AM
oraclekb

ORA-01157: cannot identify/lock data file

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.
03-28-2003, 08:15 AM
aarroyob

Have you looked at dbwr trace file as suggested in the above error?
03-28-2003, 08:20 AM
oraclekb

Re: cannot identify/lock data file

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.
03-28-2003, 09:31 AM
marist89

Show me the output from :

Code:

groups ls -lg /oracle/oradata/POL1/NETRMS_UCR.dbf
03-28-2003, 09:44 AM
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.
03-28-2003, 11:55 AM
marist89

OK, it's a link. How about:
ls -lg /dev/rNETRMS_UCR
03-28-2003, 12:47 PM
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
03-28-2003, 01:03 PM
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.
03-28-2003, 01:09 PM
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.
10-12-2003, 11:55 PM
yls177

Quote:

Originally posted by marist89
Show me the output from :

Code:

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

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

so what could be wrong?
10-13-2003, 03:10 AM
pando

Quote:

Originally posted by yls177
mine output is "/oracle/sid/sapdata1/facti_2/facti.data2"

so what could be wrong?

omg :confused:
10-13-2003, 09:44 AM
OracleDoc

links + dbf's=bad big no no.

Oracle won't support it.when it comes to critical database files
10-13-2003, 11:48 AM
yls177

Quote:

Originally posted by pando
omg :confused:

pardon me.. but i dont get what u are trying to get at
10-13-2003, 11:50 AM
pando

I dont get your question neither! Do you use character/block device....?! I was confused if your question is relevant here :o
10-13-2003, 11:42 PM
yls177

Re: ORA-01157: cannot identify/lock data file

Quote:

Originally posted by 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.

hi, i got this error, and when i follow the postings, i do a ls -lg
/oracle/sid/sapdata1/facti_2/facti.data2 and got "/oracle/sid/sapdata1/facti_2/facti.data2" as well.

i didnt get any links to any device files.
10-14-2003, 08:26 AM
marist89

Quote:

Originally posted by OracleDoc
links + dbf's=bad big no no.

Oracle won't support it.when it comes to critical database files

I don't know about that. There are several instances in metalink of how to setup soft links to critical files for Oracle to use. In fact, I was instructed by an Oracle Support analyst to create a soft link to a datafile during a recovery.

http://metalink.oracle.com/metalink/...T&p_id=45967.1
http://metalink.oracle.com/metalink/...T&p_id=73595.1
etc.

In addition 99% of system administrators will use soft links for the top of a filesystem's mount point. If that's your convention, great. However, I think saying Oracle won't support it is misleading.