This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Feb 6, 2013 6:21 PM by 937555 RSS

ORA-09968: unable to lock file

937555 Newbie
Currently Being Moderated
Hello,

I am using Oracle 11g in my Windows machine. I wrote a multithreaded program that will be inserting into Oracle database so I was getting this exception before-

ORA-12519: TNS:no appropriate service handler found


So after going through various article on the internet. I found that the solution for above exception is to increase the number of processes- So I followed the below thing and it works fine for me after that.

cmd>sqlplus / as sysdba
sqlplus>alter system set processes=300 scope=spfile;
sqlplus>shut immediate;
sqlplus>startup



Now after some more time, I increased the processes to something like below

alter system set processes=1000000 scope=spfile;


And since then whenever I am starting up my Oracle database, it is giving me this exception below and it is happening since one day. I am not sure how to fix this problem?


ORA-10997: another startup/shutdown operation of this instance inprogress
ORA-09968: unable to lock file


Can anyone help me with this?
  • 1. Re: ORA-09968: unable to lock file
    sb92075 Guru
    Currently Being Moderated
    sqlplus
    / as sysdba
    shutdown abort
    startup
    exit

    COPY the results from doing as above then PASTE all back here
  • 2. Re: ORA-09968: unable to lock file
    937555 Newbie
    Currently Being Moderated
    I did exactly as said by you- As soon as execute startup command, I get the following exception below


    SQL> shutdown abort
    ORACLE instance shut down.
    SQL> startup
    ORA-10997: another startup/shutdown operation of this instance inprogress
    ORA-09968: unable to lock file
    SQL>

    Edited by: user10204577 on Feb 6, 2013 3:20 PM
  • 3. Re: ORA-09968: unable to lock file
    937555 Newbie
    Currently Being Moderated
    I did as said by you- As soon as execute startup command, I get the following exception below
    SQL> shutdown abort
    ORACLE instance shut down.
    SQL> startup
    ORA-10997: another startup/shutdown operation of this instance inprogress
    ORA-09968: unable to lock file
    SQL>
  • 4. Re: ORA-09968: unable to lock file
    sb92075 Guru
    Currently Being Moderated
    10997, 00000, "another startup/shutdown operation of this instance inprogress"
    // *Cause:  An Oracle Instance startup or shutdown operation failed to procure
    //          the serialization primitive. Another foreground process may have
    //          attempted startup or shutdown operation in parallel.
    // *Action: Check additional error messages in the alert log and the process
    //          trace file.
    [oracle@localhost ~]$ oerr ora 9968
    09968, 00000, "unable to lock file"
    // *Cause:   The system call for locking a file returned an error when trying
    //           to acquire a database mount lock or the instance startup/shutdown
    //           lock.
    // *Action: Check errno for more information.
  • 5. Re: ORA-09968: unable to lock file
    937555 Newbie
    Currently Being Moderated
    Now what I am supposed to do? Not sure what should I look for and where?
  • 6. Re: ORA-09968: unable to lock file
    sb92075 Guru
    Currently Being Moderated
    // *Action: Check additional error messages in the alert log and the process trace file.                                                                                                                                                                               
  • 7. Re: ORA-09968: unable to lock file
    937555 Newbie
    Currently Being Moderated
    In the aler_orcl.log file, I have this-


    Tue Feb 05 22:09:57 2013
    PMON started with pid=2, OS id=14288
    Tue Feb 05 22:09:57 2013
    VKTM started with pid=3, OS id=13112 at elevated priority
    VKTM running at (20)ms precision
    Tue Feb 05 22:09:57 2013
    DIAG started with pid=4, OS id=13768
    Tue Feb 05 22:09:57 2013
    DBRM started with pid=5, OS id=11476
    Tue Feb 05 22:09:57 2013
    PSP0 started with pid=6, OS id=12344
    Tue Feb 05 22:09:57 2013
    DSKM started with pid=7, OS id=13276
    Tue Feb 05 22:09:57 2013
    DIA0 started with pid=8, OS id=5368
    Tue Feb 05 22:09:57 2013
    MMAN started with pid=7, OS id=11232
    Tue Feb 05 22:09:57 2013
    DBW0 started with pid=9, OS id=12992
    Tue Feb 05 22:09:57 2013
    LGWR started with pid=10, OS id=14140
    Tue Feb 05 22:09:57 2013
    CKPT started with pid=11, OS id=2964
    Tue Feb 05 22:09:57 2013
    SMON started with pid=12, OS id=4444
    Tue Feb 05 22:09:57 2013
    RECO started with pid=13, OS id=13660
    Tue Feb 05 22:09:57 2013
    MMON started with pid=14, OS id=10596
    starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
    starting up 1 shared server(s) ...
    ORACLE_BASE from environment = D:\app\rjamal
    Tue Feb 05 22:09:57 2013
    ALTER DATABASE MOUNT
    Tue Feb 05 22:09:57 2013
    MMNL started with pid=15, OS id=13392
    Setting recovery target incarnation to 2
    Successful mount of redo thread 1, with mount id 1334447288
    Database mounted in Exclusive Mode
    Lost write protection disabled
    Completed: ALTER DATABASE MOUNT
    Tue Feb 05 22:10:04 2013
    ALTER DATABASE OPEN
    Thread 1 opened at log sequence 1400
    Current log# 2 seq# 1400 mem# 0: D:\APP\RJAMAL\ORADATA\ORCL\REDO02.LOG
    Successful open of redo thread 1
    MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
    SMON: enabling cache recovery
    Successfully onlined Undo Tablespace 2.
    Verifying file header compatibility for 11g tablespace encryption..
    Verifying 11g file header compatibility for tablespace encryption completed
    SMON: enabling tx recovery
    Database Characterset is WE8MSWIN1252
    Opening with internal Resource Manager plan
    Starting background process FBDA
    Starting background process SMCO
    Tue Feb 05 22:10:06 2013
    SMCO started with pid=20, OS id=13828
    replication_dependency_tracking turned off (no async multimaster replication found)
    Starting background process QMNC
    Tue Feb 05 22:10:07 2013
    QMNC started with pid=21, OS id=2640
    Tue Feb 05 22:10:06 2013
    FBDA started with pid=18, OS id=13760
    Tue Feb 05 22:10:11 2013
    db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
    user-specified limit on the amount of space that will be used by this
    database for recovery-related files, and does not reflect the amount of
    space available in the underlying filesystem or ASM diskgroup.
    Tue Feb 05 22:10:17 2013
    Completed: ALTER DATABASE OPEN
    Tue Feb 05 22:13:20 2013
    ALTER SYSTEM SET processes=10000 SCOPE=SPFILE;
    Stopping background process SMCO
    Stopping background process FBDA
    Shutting down instance: further logons disabled
    Stopping background process QMNC
    Stopping background process MMNL
    Stopping background process MMON
    Tue Feb 05 22:13:30 2013
    Shutting down instance (immediate)
    License high water mark = 7
    Waiting for dispatcher 'D000' to shutdown
    All dispatchers and shared servers shutdown
    ALTER DATABASE CLOSE NORMAL
    Tue Feb 05 22:13:33 2013
    SMON: disabling tx recovery
    SMON: disabling cache recovery
    Tue Feb 05 22:13:34 2013
    Shutting down archive processes
    Archiving is disabled
    Archive process shutdown avoided: 0 active
    Thread 1 closed at log sequence 1400
    Successful close of redo thread 1
    Completed: ALTER DATABASE CLOSE NORMAL
    ALTER DATABASE DISMOUNT
    Completed: ALTER DATABASE DISMOUNT
    ARCH: Archival disabled due to shutdown: 1089
    Shutting down archive processes
    Archiving is disabled
    Archive process shutdown avoided: 0 active
    ARCH: Archival disabled due to shutdown: 1089
    Shutting down archive processes
    Archiving is disabled
    Archive process shutdown avoided: 0 active
    Tue Feb 05 22:13:35 2013
    Stopping background process VKTM:
    Tue Feb 05 22:13:37 2013
    Instance shutdown complete
    Tue Feb 05 22:13:44 2013
    Starting ORACLE instance (normal)
    LICENSE_MAX_SESSION = 0
    LICENSE_SESSIONS_WARNING = 0
    Picked latch-free SCN scheme 3
    Using LOG_ARCHIVE_DEST_1 parameter default value as D:\app\rjamal\product\11.1.0\db_1\RDBMS
    Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
    Autotune of undo retention is turned on.
    IMODE=BR
    ILAT =1210
    LICENSE_MAX_USERS = 0
    SYS auditing is disabled
    Starting up ORACLE RDBMS Version: 11.1.0.6.0.
    Using parameter settings in server-side spfile D:\APP\RJAMAL\PRODUCT\11.1.0\DB_1\DATABASE\SPFILEORCL.ORA
    System parameters with non-default values:
    processes = 10000
    memory_target = 9856M
    control_files = "D:\APP\RJAMAL\ORADATA\ORCL\CONTROL01.CTL"
    control_files = "D:\APP\RJAMAL\ORADATA\ORCL\CONTROL02.CTL"
    control_files = "D:\APP\RJAMAL\ORADATA\ORCL\CONTROL03.CTL"
    db_block_size = 8192
    compatible = "11.1.0.0.0"
    db_recovery_file_dest = "D:\app\rjamal\flash_recovery_area"
    db_recovery_file_dest_size= 2G
    undo_tablespace = "UNDOTBS1"
    remote_login_passwordfile= "EXCLUSIVE"
    db_domain = ""
    dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
    audit_file_dest = "D:\APP\RJAMAL\ADMIN\ORCL\ADUMP"
    audit_trail = "DB"
    db_name = "orcl"
    open_cursors = 300
    diagnostic_dest = "D:\APP\RJAMAL"
    Tue Feb 05 22:13:45 2013
    PMON started with pid=2, OS id=3744
    Tue Feb 05 22:13:45 2013
    VKTM started with pid=3, OS id=12620 at elevated priority
    VKTM running at (20)ms precision
    Tue Feb 05 22:13:45 2013
    DIAG started with pid=4, OS id=13356
    Tue Feb 05 22:13:45 2013
    DBRM started with pid=5, OS id=1036
    Tue Feb 05 22:13:45 2013
    PSP0 started with pid=6, OS id=11528
    Tue Feb 05 22:13:45 2013
    DSKM started with pid=7, OS id=14160
    Tue Feb 05 22:13:45 2013
    DIA0 started with pid=8, OS id=13856
    Tue Feb 05 22:13:45 2013
    MMAN started with pid=7, OS id=6740
    Tue Feb 05 22:13:45 2013
    DBW0 started with pid=9, OS id=13044
    Tue Feb 05 22:13:45 2013
    LGWR started with pid=10, OS id=14068
    Tue Feb 05 22:13:45 2013
    CKPT started with pid=11, OS id=13788
    Tue Feb 05 22:13:45 2013
    SMON started with pid=12, OS id=14036
    Tue Feb 05 22:13:45 2013
    RECO started with pid=13, OS id=12132
    Tue Feb 05 22:13:45 2013
    MMON started with pid=14, OS id=12404
    starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
    starting up 1 shared server(s) ...
    ORACLE_BASE from environment = D:\app\rjamal
    Tue Feb 05 22:13:45 2013
    ALTER DATABASE MOUNT
    Tue Feb 05 22:13:45 2013
    MMNL started with pid=15, OS id=4560
    Setting recovery target incarnation to 2
    Successful mount of redo thread 1, with mount id 1334401436
    Database mounted in Exclusive Mode
    Lost write protection disabled
    Completed: ALTER DATABASE MOUNT
    Tue Feb 05 22:13:52 2013
    ALTER DATABASE OPEN
    Thread 1 opened at log sequence 1400
    Current log# 2 seq# 1400 mem# 0: D:\APP\RJAMAL\ORADATA\ORCL\REDO02.LOG
    Successful open of redo thread 1
    MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
    SMON: enabling cache recovery
    Successfully onlined Undo Tablespace 2.
    Verifying file header compatibility for 11g tablespace encryption..
    Verifying 11g file header compatibility for tablespace encryption completed
    SMON: enabling tx recovery
    Database Characterset is WE8MSWIN1252
    Opening with internal Resource Manager plan
    Starting background process FBDA
    Starting background process SMCO
    Tue Feb 05 22:13:54 2013
    FBDA started with pid=18, OS id=4684
    Tue Feb 05 22:13:54 2013
    SMCO started with pid=20, OS id=11544
    replication_dependency_tracking turned off (no async multimaster replication found)
    Starting background process QMNC
    Tue Feb 05 22:13:55 2013
    QMNC started with pid=21, OS id=10872
    Tue Feb 05 22:14:00 2013
    db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
    user-specified limit on the amount of space that will be used by this
    database for recovery-related files, and does not reflect the amount of
    space available in the underlying filesystem or ASM diskgroup.
    Tue Feb 05 22:14:06 2013
    Completed: ALTER DATABASE OPEN
    Tue Feb 05 22:15:05 2013
    Thread 1 advanced to log sequence 1401
    Current log# 3 seq# 1401 mem# 0: D:\APP\RJAMAL\ORADATA\ORCL\REDO03.LOG
    Tue Feb 05 22:18:49 2013
    Starting background process CJQ0
    Tue Feb 05 22:18:49 2013
    CJQ0 started with pid=26, OS id=11732
    Setting Resource Manager plan SCHEDULER[0x2C0A]:DEFAULT_MAINTENANCE_PLAN via scheduler window
    Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
    Tue Feb 05 22:25:04 2013
    ALTER SYSTEM SET processes=10000000 SCOPE=SPFILE;
    Stopping background process SMCO
    Stopping background process FBDA
    Shutting down instance: further logons disabled
    Tue Feb 05 22:25:11 2013
    Stopping background process CJQ0
    Stopping background process QMNC
    Stopping background process MMNL
    Stopping background process MMON
    Tue Feb 05 22:25:14 2013
    Shutting down instance (immediate)
    License high water mark = 7
    Stopping Job queue slave processes, flags = 7
    Tue Feb 05 22:25:14 2013
    ORA-1089 : opidrv aborting process J000 ospid (1016_13040)
    Job queue slave processes stopped
    Waiting for dispatcher 'D000' to shutdown
    All dispatchers and shared servers shutdown
    ALTER DATABASE CLOSE NORMAL
    Tue Feb 05 22:25:17 2013
    SMON: disabling tx recovery
    SMON: disabling cache recovery
    Tue Feb 05 22:25:17 2013
    Shutting down archive processes
    Archiving is disabled
    Archive process shutdown avoided: 0 active
    Thread 1 closed at log sequence 1401
    Successful close of redo thread 1
    Completed: ALTER DATABASE CLOSE NORMAL
    ALTER DATABASE DISMOUNT
    Completed: ALTER DATABASE DISMOUNT
    ARCH: Archival disabled due to shutdown: 1089
    Shutting down archive processes
    Archiving is disabled
    Archive process shutdown avoided: 0 active
    ARCH: Archival disabled due to shutdown: 1089
    Shutting down archive processes
    Archiving is disabled
    Archive process shutdown avoided: 0 active
    Tue Feb 05 22:25:19 2013
    Stopping background process VKTM:
    Tue Feb 05 22:25:22 2013
    Instance shutdown complete
    Tue Feb 05 22:25:28 2013
    Starting ORACLE instance (normal)
    Wed Feb 06 11:53:59 2013
    Starting ORACLE instance (normal)
    Wed Feb 06 11:56:01 2013
    Starting ORACLE instance (normal)



    And I don't know where I should look for process trace file?
  • 8. Re: ORA-09968: unable to lock file
    sb92075 Guru
    Currently Being Moderated
    user10204577 wrote:
    Tue Feb 05 22:25:19 2013
    Stopping background process VKTM:
    Tue Feb 05 22:25:22 2013
    Instance shutdown complete
    Tue Feb 05 22:25:28 2013
    Starting ORACLE instance (normal)
    Wed Feb 06 11:53:59 2013
    Starting ORACLE instance (normal)
    Wed Feb 06 11:56:01 2013
    Starting ORACLE instance (normal)



    And I don't know where I should look for process trace file?
    above is NOT normal with ZERO details for the startups!


    cd $ORACLE_HOME/dbs
    ls -ltr

    sqlplus
    / as sysdba
    create pfile from spfile;
    exit

    ls -ltr

    COPY the results from doing as above then PASTE all back here
  • 9. Re: ORA-09968: unable to lock file
    937555 Newbie
    Currently Being Moderated
    I am working in windows. So here is the result-

    D:\app\rjamal\product\11.1.0\db_1\dbs>dir
    Volume in drive D is Data
    Volume Serial Number is 1E88-E5EB

    Directory of D:\app\rjamal\product\11.1.0\db_1\dbs

    02/06/2013 11:56 AM <DIR> .
    02/06/2013 11:56 AM <DIR> ..
    09/12/2007 10:44 AM 2,911 init.ora
    1 File(s) 2,911 bytes
    2 Dir(s) 467,899,789,312 bytes free

    D:\app\rjamal\product\11.1.0\db_1\dbs>sqlplus

    SQL*Plus: Release 11.1.0.6.0 - Production on Wed Feb 6 16:16:37 2013

    Copyright (c) 1982, 2007, Oracle. All rights reserved.

    Enter user-name: / as sysdba
    Connected to an idle instance.

    SQL> create pfile from spfile;

    File created.

    SQL> exit
    Disconnected

    D:\app\rjamal\product\11.1.0\db_1\dbs>dir
    Volume in drive D is Data
    Volume Serial Number is 1E88-E5EB

    Directory of D:\app\rjamal\product\11.1.0\db_1\dbs

    02/06/2013 11:56 AM <DIR> .
    02/06/2013 11:56 AM <DIR> ..
    09/12/2007 10:44 AM 2,911 init.ora
    1 File(s) 2,911 bytes
    2 Dir(s) 467,899,789,312 bytes free

    D:\app\rjamal\product\11.1.0\db_1\dbs>



    I don't know where this file got created for pfile?
  • 10. Re: ORA-09968: unable to lock file
    sb92075 Guru
    Currently Being Moderated
    I thought you were on a *NIX system

    cd $ORACLE_HOME\database
  • 11. Re: ORA-09968: unable to lock file
    937555 Newbie
    Currently Being Moderated
    Here you go- Below is the result-


    D:\app\rjamal\product\11.1.0\db_1\database>dir
    Volume in drive D is Data
    Volume Serial Number is 1E88-E5EB

    Directory of D:\app\rjamal\product\11.1.0\db_1\database

    02/06/2013 04:12 PM <DIR> .
    02/06/2013 04:12 PM <DIR> ..
    05/04/2012 04:57 PM <DIR> archive
    05/04/2012 04:58 PM 2,048 hc_orcl.dat
    02/06/2013 04:16 PM 936 INITorcl.ORA
    12/22/2005 04:07 AM 31,744 oradba.exe
    02/06/2013 11:53 AM 5,040 oradim.log
    05/07/2012 08:01 AM 1,536 PWDorcl.ora
    02/05/2013 10:25 PM 2,560 SPFILEORCL.ORA
    6 File(s) 43,864 bytes
    3 Dir(s) 467,898,974,208 bytes free

    D:\app\rjamal\product\11.1.0\db_1\database>sqlplus

    SQL*Plus: Release 11.1.0.6.0 - Production on Wed Feb 6 16:27:22 2013

    Copyright (c) 1982, 2007, Oracle. All rights reserved.

    Enter user-name: / as sysdba
    Connected to an idle instance.

    SQL> create pfile from spfile;

    File created.

    SQL> exit
    Disconnected

    D:\app\rjamal\product\11.1.0\db_1\database>dir
    Volume in drive D is Data
    Volume Serial Number is 1E88-E5EB

    Directory of D:\app\rjamal\product\11.1.0\db_1\database

    02/06/2013 04:12 PM <DIR> .
    02/06/2013 04:12 PM <DIR> ..
    05/04/2012 04:57 PM <DIR> archive
    05/04/2012 04:58 PM 2,048 hc_orcl.dat
    02/06/2013 04:27 PM 936 INITorcl.ORA
    12/22/2005 04:07 AM 31,744 oradba.exe
    02/06/2013 11:53 AM 5,040 oradim.log
    05/07/2012 08:01 AM 1,536 PWDorcl.ora
    02/05/2013 10:25 PM 2,560 SPFILEORCL.ORA
    6 File(s) 43,864 bytes
    3 Dir(s) 467,898,970,112 bytes free

    D:\app\rjamal\product\11.1.0\db_1\database>
  • 12. Re: ORA-09968: unable to lock file
    sb92075 Guru
    Currently Being Moderated
    user10204577 wrote:
    Hello,

    I am using Oracle 11g in my Windows machine. I wrote a multithreaded program that will be inserting into Oracle database so I was getting this exception before-

    ORA-12519: TNS:no appropriate service handler found


    So after going through various article on the internet. I found that the solution for above exception is to increase the number of processes- So I followed the below thing and it works fine for me after that.

    cmd>sqlplus / as sysdba
    sqlplus>alter system set processes=300 scope=spfile;
    sqlplus>shut immediate;
    sqlplus>startup
    what results when you repeat the exact same again, again?
  • 13. Re: ORA-09968: unable to lock file
    937555 Newbie
    Currently Being Moderated
    I get same exception everytime. It was working for me with smaller number

    alter system set processes=300 scope=spfile;


    But after I ran this higher number of processes-

    alter system set processes=1000000 scope=spfile;


    After that it is hanged since then. Whenever I startup, it gives me that exception again.
  • 14. Re: ORA-09968: unable to lock file
    sb92075 Guru
    Currently Being Moderated
    user10204577 wrote:
    I get same exception everytime. It was working for me with smaller number

    alter system set processes=300 scope=spfile;


    But after I ran this higher number of processes-

    alter system set processes=1000000 scope=spfile;


    After that it is hanged since then. Whenever I startup, it gives me that exception again.
    if you reduce the processes back to 300; what happens?
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points