Database Crash -- Urgent!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Database Crash -- Urgent!!

  1. #1
    Join Date
    Dec 2000
    Posts
    37

    Red face

    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.


  2. #2
    Join Date
    Apr 2000
    Posts
    126

    Wink

    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.

  3. #3
    Join Date
    Oct 2001
    Location
    Denver, Co
    Posts
    2
    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]

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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"....
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Dec 2000
    Posts
    37
    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.







  6. #6
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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.

  7. #7
    Join Date
    Dec 2000
    Posts
    37
    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.


  8. #8
    Join Date
    Dec 1999
    Location
    Cincinnati, Ohio USA
    Posts
    99
    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.
    Doug

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width