14 Replies Latest reply on May 11, 2012 5:34 PM by user3266490

    lsnrctl and sqlplus hang status

    user3266490
      Hi,

      DB:10.2
      OS:aix

      The lsnrctl and sqlplus hang status while logging.

      I tried to use sqlplus -prelim.but no use.Sufficient space in server ,tmp also.
      Swap also 100% free,cpu also idle and sufficient physical memory available.

      Only think is some of the hard disks usage is 100%.Tnsping not working.

      Any suggestions?

      Thanks and Regards,
      VN
        • 1. Re: lsnrctl and sqlplus hang status
          krramnik
          Did you check the alert log of the database
          • 2. Re: lsnrctl and sqlplus hang status
            sb92075
            user3266490 wrote:
            Hi,

            DB:10.2
            OS:aix

            The lsnrctl and sqlplus hang status while logging.

            I tried to use sqlplus -prelim.but no use.Sufficient space in server ,tmp also.
            Swap also 100% free,cpu also idle and sufficient physical memory available.

            Only think is some of the hard disks usage is 100%.Tnsping not working.
            When was last all OK?
            What has changed since then?
            • 3. Re: lsnrctl and sqlplus hang status
              user3266490
              Hi,

              Thanks for your reply.


              Thu May 10 12:01:51 2012
              Thread 1 advanced to log sequence 1216 (LGWR switch)
              Current log# 4 seq# 1216 mem# 0: /db/rd01/CM6/redo0401.rdo
              Current log# 4 seq# 1216 mem# 1: /db/rd02/CM6/redo0402.rdo
              Fri May 11 06:00:13 2012
              Thread 1 advanced to log sequence 1217 (LGWR switch)
              Current log# 3 seq# 1217 mem# 0: /db/rd01/CM6/redo0301.rdo
              Current log# 3 seq# 1217 mem# 1: /db/rd02/CM6/redo0302.rdo
              Fri May 11 12:00:58 2012
              Process startup failed, error stack:
              Fri May 11 12:00:58 2012
              Errors in file /u01/app/oracle/product/CM6/bdump/CM6_psp0_790622.trc:
              ORA-27300: OS system dependent operation:fork failed with status: 2
              ORA-27301: OS failure message: No such file or directory
              ORA-27302: failure occurred at: skgpspawn5
              ORA-27303: additional information: skgpspawn5
              Fri May 11 12:00:59 2012
              Process m001 died, see its trace file
              Fri May 11 12:00:59 2012
              ksvcreate: Process(m001) creation failed
              oracle@cmm-ax01:/u01/app/oracle/product/CM6/bdump: date
              Fri May 11 12:47:44 CUT 2012

              oracle@cmm-ax01:/u01/app/oracle/product/CM6/bdump: cat /u01/app/oracle/product/CM6/bdump/CM6_psp0_790622.trc
              -bash: fork: Resource temporarily unavailable
              oracle@cmm-ax01:/u01/app/oracle/product/CM6/bdump:

              oracle@cmmm-ax01:/u01/app/oracle/product/CM6/bdump: cat /u01/app/oracle/product/CM6/bdump/CM6_psp0_790622.trc
              /u01/app/oracle/product/CM6/bdump/CM6_psp0_790622.trc
              Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
              With the Partitioning, OLAP, Data Mining and Real Application Testing options
              ORACLE_HOME = /u01/app/oracle/product
              System name: AIX
              Node name: cmmm-ax01
              Release: 3
              Version: 5
              Machine: 00C108CA4C00
              Instance name: CM6
              Redo thread mounted by this instance: 1
              Oracle process number: 3
              Unix process pid: 790622, image: oracle@cmmm-ax01 (PSP0)

              *** SERVICE NAME:(SYS$BACKGROUND) 2011-11-09 15:21:39.787
              *** 2011-11-10 09:17:28.226
              *** SESSION ID:(48.1) 2011-11-09 15:21:39.787
              *** 2011-11-09 15:21:39.787
              Dump diagnostics for process J002 pid 1196104 which did not start after 120 seconds:
              (spawn_time:xDEBE0F3E6 now:xDEBE2CB47 diff:x1D761)
              *** 2011-11-10 09:17:28.226
              Dumping diagnostic information for J002:
              OS pid = 1196104
              loadavg : 0.49 0.57 0.59
              swap info: free_mem = 3577.59M rsv = 18.00M
              alloc = 17.02M avail = 4608.00M swap_free = 4590.98M
              F S UID PID PPID C PRI NI ADDR SZ WCHAN STIME TTY TIME CMD
              240001 A oracle 1196104 1 3 61 20 3721510 92484 15:19:39 - 0:00 [oracle]
              ERROR: process 1196104 is not alive
              *** 2011-11-10 09:17:29.714
              *** 2011-11-21 15:21:39.966
              Dump diagnostics for process J001 pid 880892 which did not start after 120 seconds:
              (spawn_time:xE29AD3786 now:xE29AF0FEA diff:x1D864)
              *** 2011-11-21 15:21:39.966
              Dumping diagnostic information for J001:
              OS pid = 880892
              loadavg : 1.73 1.64 1.62
              swap info: free_mem = 2796.55M rsv = 18.00M
              alloc = 16.93M avail = 4608.00M swap_free = 4591.07M
              skgpgpstack: read() for cmd /bin/ps -elf | /bin/egrep 'PID | 880892' | /bin/grep -v grep timed out after 60 seconds
              open: The file access permissions do not allow the specified action.
              skgpgpstack: read() for cmd /bin/sh -c '/usr/bin/procstack 880892 2>&1' timed out after 60 seconds
              *** 2011-11-21 15:24:40.018
              Killing process (ospid 880892): did not start after 120 seconds
              ... and the process is still alive after kill!
              Killed process 880892 is still alive after 183 seconds!
              *** 2011-11-21 15:25:43.019
              Killed process 880892 is still alive after 243 seconds!
              *** 2011-11-21 15:26:43.020
              Killed process 880892 is still alive after 303 seconds!
              *** 2011-11-21 15:27:43.022
              Killed process 880892 is still alive after 363 seconds!
              *** 2011-11-21 15:28:43.023
              Killed process 880892 is still alive after 423 seconds!
              *** 2011-11-21 15:29:43.024
              Killed process 880892 is still alive after 483 seconds!
              *** 2011-11-21 15:30:43.025
              Killed process 880892 is still alive after 543 seconds!
              *** 2011-11-21 15:31:43.026
              Killed process 880892 is still alive after 603 seconds!
              *** 2011-11-21 15:32:43.027
              Killed process 880892 is still alive after 663 seconds!
              *** 2011-11-21 15:33:43.028
              Killed process 880892 is still alive after 723 seconds!
              *** 2011-11-21 15:34:43.029
              Killed process 880892 is still alive after 783 seconds!
              *** 2011-11-21 15:35:43.031
              Killed process 880892 is still alive after 843 seconds!
              *** 2011-11-21 15:36:43.032
              Killed process 880892 is still alive after 903 seconds!
              *** 2011-11-21 15:37:43.033
              Killed process 880892 is still alive after 963 seconds!
              *** 2011-11-21 15:38:43.034
              Killed process 880892 is still alive after 1023 seconds!
              *** 2011-11-21 15:39:43.035
              Killed process 880892 is still alive after 1083 seconds!
              *** 2011-11-21 15:40:43.037
              Killed process 880892 is still alive after 1143 seconds!
              *** 2011-11-21 15:41:43.038
              Killed process 880892 is still alive after 1203 seconds!
              *** 2011-11-21 15:42:43.039
              Killed process 880892 is still alive after 1263 seconds!
              *** 2011-11-21 15:43:46.035
              Killed process 880892 is still alive after 1326 seconds!
              *** 2011-11-21 15:44:46.036
              Killed process 880892 is still alive after 1386 seconds!
              *** 2011-11-21 15:44:46.036
              Dump diagnostics for process J001 pid 1114146 which did not start after 120 seconds:
              (spawn_time:xE29C25B79 now:xE29C43640 diff:x1DAC7)
              *** 2011-11-21 15:44:46.036
              Dumping diagnostic information for J001:
              OS pid = 1114146
              loadavg : 1.90 1.88 1.74
              swap info: free_mem = 2773.38M rsv = 18.00M
              alloc = 17.42M avail = 4608.00M swap_free = 4590.58M
              skgpgpstack: read() for cmd /bin/ps -elf | /bin/egrep 'PID | 1114146' | /bin/grep -v grep timed out after 60 seconds
              open: The file access permissions do not allow the specified action.
              skgpgpstack: read() for cmd /bin/sh -c '/usr/bin/procstack 1114146 2>&1' timed out after 60 seconds
              *** 2011-11-21 15:47:46.091
              Killing process (ospid 1114146): did not start after 120 seconds
              ... and the process is still alive after kill!
              Killed process 880892 is still alive after 1569 seconds!
              Killed process 1114146 is still alive after 183 seconds!
              *** 2011-11-21 15:48:49.092
              Killed process 880892 is still alive after 1629 seconds!
              Killed process 1114146 is still alive after 243 seconds!
              *** 2011-11-21 15:49:49.093
              Killed process 880892 is still alive after 1689 seconds!
              Killed process 1114146 is still alive after 303 seconds!
              *** 2011-11-21 15:50:49.095
              Killed process 880892 is still alive after 1749 seconds!
              Killed process 1114146 is still alive after 363 seconds!
              *** 2011-11-21 15:51:49.096
              Killed process 880892 is still alive after 1809 seconds!
              Killed process 1114146 is still alive after 423 seconds!
              *** 2012-05-11 12:00:58.777
              Process startup failed, error stack:
              ORA-27300: OS system dependent operation:fork failed with status: 2
              ORA-27301: OS failure message: No such file or directory
              ORA-27302: failure occurred at: skgpspawn5
              ORA-27303: additional information: skgpspawn5
              oracle@cmm-ax01:/u01/app/oracle/product/CM6/bdump:


              we didn't changed anything at db level.It is fine till this morning.


              Thanks & Regards,
              VN

              Edited by: user3266490 on May 11, 2012 6:31 PM
              • 4. Re: lsnrctl and sqlplus hang status
                878451
                hi,
                try reading the trace with another editor.
                • 5. Re: lsnrctl and sqlplus hang status
                  sb92075
                  what clues exist within OS messages file?
                  • 6. Re: lsnrctl and sqlplus hang status
                    krramnik
                    You may need to increase maxuproc parameter at OS level and reboot server.

                    Refer Metalink ID *1125188.1*
                    1 person found this helpful
                    • 7. Re: lsnrctl and sqlplus hang status
                      user3266490
                      Hi,

                      Thanks for your reply.

                      I couldn't understand your words.could you make clear?

                      Thanks & Regards,
                      VN
                      • 8. Re: lsnrctl and sqlplus hang status
                        krramnik
                        Also refer Metalink ID 392006.1 (It has step by step solution)
                        • 9. Re: lsnrctl and sqlplus hang status
                          sb92075
                          bcm@bcm-laptop:~$ oerr ora 27300
                          27300, 00000, "OS system dependent operation:%s failed with status: %s"
                          // *Cause:  OS system call error
                          // *Action: contact Oracle Support
                          bcm@bcm-laptop:~$ oerr ora 27301
                          27301, 00000, "OS failure message: %s"
                          // *Cause:  OS system call error
                          // *Action: contact Oracle Support
                          //
                          bcm@bcm-laptop:~$ oerr ora 27302
                          27302, 00000, "failure occurred at: %s"
                          // *Cause:  OS system call error
                          // *Action: contact Oracle Support
                          //
                          bcm@bcm-laptop:~$ oerr ora 27303
                          27303, 00000, "additional information: %s"
                          // *Cause:  OS system call error
                          // *Action: contact Oracle Support
                          bcm@bcm-laptop:~$

                          guess what I would do next?
                          • 10. Re: lsnrctl and sqlplus hang status
                            user3266490
                            Hi,


                            Thanks for your reply.

                            Could you explain why have to reset the value?

                            checked at os level.
                            -bash-2.05b$ lsattr -E -l sys0 -a maxuproc -F value
                            128


                            Thanks & Regards,
                            VN
                            • 11. Re: lsnrctl and sqlplus hang status
                              sb92075
                              user3266490 wrote:
                              Hi,


                              Thanks for your reply.

                              Could you explain why have to reset the value?

                              checked at os level.
                              -bash-2.05b$ lsattr -E -l sys0 -a maxuproc -F value
                              128


                              Thanks & Regards,
                              VN
                              ps -ef | wc -l

                              what results from OS command above?

                              Edited by: sb92075 on May 11, 2012 6:35 AM
                              1 person found this helpful
                              • 12. Re: lsnrctl and sqlplus hang status
                                krramnik
                                oracle has problem in forking more process. So need to increase. It seems 128 is a very low value for maxuproc. AIX recommended value is 16384

                                chdev -l sys0 -a maxuproc 16384

                                Edited by: krramnik on May 11, 2012 7:08 PM
                                • 13. Re: lsnrctl and sqlplus hang status
                                  user3266490
                                  Hi,


                                  Thanks for your reply.


                                  bash-2.05b$ ps -ef | wc -l
                                  243
                                  -bash-2.05b$



                                  Thanks and Regards,
                                  VN
                                  • 14. Re: lsnrctl and sqlplus hang status
                                    user3266490
                                    Hi,

                                    Thanks for your reply.
                                    It is some network issue.

                                    Thanks and Regards,
                                    VN