1 2 Previous Next 21 Replies Latest reply: Feb 6, 2013 8:21 PM by 937555 RSS

    ORA-09968: unable to lock file

    937555
      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
          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
            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
              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
                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
                  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
                    // *Action: Check additional error messages in the alert log and the process trace file.                                                                                                                                                                               
                    • 7. Re: ORA-09968: unable to lock file
                      937555
                      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
                        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
                          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
                            I thought you were on a *NIX system

                            cd $ORACLE_HOME\database
                            • 11. Re: ORA-09968: unable to lock file
                              937555
                              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
                                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
                                  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
                                    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