-
Our production database just crashed. When I check alert file, it says:
Errors in file /opt/oracle/admin/A/bdump/lgwr_1830.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/dbms2/oradata/A/redoB03.log'
ORA-27086: skgfglk: unable to lock file - already in use
Intel SVR4 UNIX Error: 46: No record locks available
Additional information: 8
ORA-00312: online log 3 thread 1: '/dbms1/oradata/A/redoA03.log'
ORA-27086: skgfglk: unable to lock file - already in use
Intel SVR4 UNIX Error: 46: No record locks available
Additional information: 8
Fri Oct 26 12:17:51 2001
LGWR: terminating instance due to error 313
Instance terminated by LGWR, pid = 1830
I tried to mount the database, not it does not let me. So I terminated all the instance on the system and managed to mount. When I try to open, I got an error. The trace file looks like this:
Errors in file /opt/oracle/admin/A/bdump/dbw0_25200.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/dbms3/oradata/A/users01.dbf'
ORA-27086: skgfglk: unable to lock file - already in use
Intel SVR4 UNIX Error: 46: No record locks available
Additional information: 8
Fri Oct 26 13:46:55 2001
Errors in file /opt/oracle/admin/A/bdump/dbw0_25200.trc:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/dbms2/oradata/A/users02.dbf'
ORA-27086: skgfglk: unable to lock file - already in use
Intel SVR4 UNIX Error: 46: No record locks available
Additional information: 8
Fri Oct 26 13:46:55 2001
Errors in file /opt/oracle/admin/A/bdump/dbw0_25200.trc:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/dbms2/oradata/A/rbs02.dbf'
ORA-27086: skgfglk: unable to lock file - already in use
Intel SVR4 UNIX Error: 46: No record locks available
Additional information: 8
Fri Oct 26 13:46:55 2001
Errors in file /opt/oracle/admin/A/bdump/dbw0_25200.trc:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/dbms2/oradata/A/rbs03.dbf'
ORA-27086: skgfglk: unable to lock file - already in use
Intel SVR4 UNIX Error: 46: No record locks available
Additional information: 8
Fri Oct 26 13:46:55 2001
Errors in file /opt/oracle/admin/A/bdump/dbw0_25200.trc:
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/dbms3/oradata/A/rawdata01.dbf'
ORA-27086: skgfglk: unable to lock file - already in use
Intel SVR4 UNIX Error: 46: No record locks available
Additional information: 8
Fri Oct 26 13:46:55 2001
Errors in file /opt/oracle/admin/A/bdump/dbw0_25200.trc:
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/dbms4/oradata/A/index01.dbf'
ORA-27086: skgfglk: unable to lock file - already in use
Intel SVR4 UNIX Error: 46: No record locks available
Additional information: 8
Fri Oct 26 13:46:55 2001
Errors in file /opt/oracle/admin/A/bdump/dbw0_25200.trc:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/dbms4/oradata/A/index02.dbf'
ORA-27086: skgfglk: unable to lock file - already in use
Intel SVR4 UNIX Error: 46: No record locks available
Additional information: 8
Fri Oct 26 13:46:55 2001
Errors in file /opt/oracle/admin/A/bdump/dbw0_25200.trc:
ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
ORA-01110: data file 12: '/dbms2/oradata/A/rawdata02.dbf'
ORA-27086: skgfglk: unable to lock file - already in use
Intel SVR4 UNIX Error: 46: No record locks available
Additional information: 8
Fri Oct 26 13:46:55 2001
Errors in file /opt/oracle/admin/A/bdump/dbw0_25200.trc:
ORA-01157: cannot identify/lock data file 13 - see DBWR trace file
ORA-01110: data file 13: '/dbms3/oradata/A/users03.dbf'
ORA-27086: skgfglk: unable to lock file - already in use
Intel SVR4 UNIX Error: 46: No record locks available
Additional information: 8
ORA-1157 signalled during: alter database open ...
Does anyone know what is going on? Is it a o/s problem or a database problem?
Thank you in advance.
-
Problem Description:
====================
An ORA-01157 is issued whenever Oracle attempts to access a file but cannot
find it:
ORA-01157: "cannot identify data file %s - file not found"
Cause: The background process was not able to find one of the data files.
The database will prohibit access to this file but other files will
be unaffected. However the first instance to open the database will
need to access all online data files. Accompanying error from the
operating system describes why file was not found.
Action: Have operating system make file available to database. Then either
open the database or do ALTER SYSTEM CHECK DATAFILES.
ORA-01157 errors are usually followed by ORA-01110 and possibly an Oracle
operating system layer error such as ORA-07360. A DBWR trace file is generated
in the background_dump_dest directory.
Problem Explanation:
====================
Oracle tries to open all datafiles when you issue a STARTUP or ALTER DATABASE
OPEN. If a datafile cannot be found at this point, an ORA-01157 results.
There are two main possible causes for an ORA-01157 on startup:
1. The datafile does exist, but Oracle cannot find it.
1.A) It may have been renamed at the operating system level, moved to a
different directory, disk drive, etc. In this case, simply restoring the
datafile to its original name and location will solve the problem.
1.B) You intentionally moved the datafile to a different location, but forgot
to rename it at Oracle level. For example, you may have decided to move
the index tablespace datafiles to another disk to improve performance, but
forgot to rename them inside Oracle. In this case, you must rename the
datafile(s) from within Oracle.
2. The datafile has been physically removed or damaged to an extent that
Oracle cannot recognize it anymore.
In this case, that datafile is lost and the solution depends on the type of
datafile involved, i.e., on the tablespace to which the datafile belongs.
Solution Description:
=====================
There are two types of solutions for an ORA-01157:
I. THE DATAFILE IS LOST OR TOO DAMAGED
--------------------------------------
In this case, the solution depends on the tablespace to which the datafile
belongs. Look for a Solution Reference matching the type of datafile lost.
II. THE DATAFILE HAS SIMPLY BEEN MOVED OR RENAMED
-------------------------------------------------
If you originally wanted to change the name/location of the file, look for the
Solution Reference on how to rename datafiles inside Oracle. If the file has
been moved or renamed by mistake, simply restore it to its original
name/location and startup the database.
-
Hope you are back up and runniing. This could have been avoided by running a proactive aservice called dbdoctor they home page is http://www..net they have 60 reports that keep you on the front edge of things for about $400 per db.
Good luck
[Edited by pando on 10-26-2001 at 08:09 PM]
-
OK MGMISH, we get it. You're trying to sell the services of dbdoctor.net. Please refrain from answering every question with "you should have used dbdoctor.net"....
-
Thank you, mhaller. Through further investigation, it was found another database on the same machine has the same problem. So I assume it is a file system issue. Our unix admin restarted the server and everything seem to be working fine. He recently did an upgrade on Samba (a freeware that enable unix and windows to talk) and since then the database has crashed three times. Looks like some process is locking the data files so when Oracle need to read/write to the data files, it got locking error. I also recently increase on of the datafile size to 2G. I don't know if it has anything to do with it. But we have another datafile on the same database with 2G for quite some time and never had any problem. If anyone has similar experience, please let me know. We are running Oracle 8.1.6.0.0 on SCO unixware 7.1.1.
Thank you.
-
I can understand that it could be an O.S. security issues. But I don't believe it is to be blame with the SAMBA software. SAMBA only enable users within a network to a access your UNIX box who has security privs from a Windows computers and that's it.
It could be on the process of installing SAMBA some ORACLE related files were unintensionally altered by the unix admin. That's the only reason I can think of.
-
Thank you Redyp. I agree your analysis to some extent. But if it is the file security problem, why by restarting the server the problem dissapears? It seems to me at some point of time, there is this 'evil' process that locks the database files. But I don't know what action caused it.
-
Sounds like another database may have been created that is referencing the files.
In HP-UX, I can do an fuser on the file and see which process has it open. For example.
bitbom # fuser system01.dbf
system01.dbf: 7447o 7457o 7445o 7453o 7471o 7451o
bitbom # ps -ef|grep 7447
oracle 7447 1 9 10:57:52 ? 0:05 ora_lgwr_DLAW7
root 17991 17889 15 15:55:07 ttyp1 0:00 grep 7447
Note that each process has an 'o', this just indicates that the process nnnn has the file open. But it tells me which process has it open.
