12 Replies Latest reply: Dec 8, 2010 2:32 AM by Abdul Halim RSS

    Solaris Error: 32: Broken pipe

    Abdul Halim
      Dear sir,

      Yesterday I found in alert log file bellows warning.
      and In that time some clients inform us they are cannot log on
      to the application. after few times they could log on to
      database automatically.

      Oracle Database 10g
      Version:- 10.2.0.4
      OS :- Sun Solaries spark
      and
      It is dedicated environment .

      In parameter file
      ----------------------

      processes = 4000


      Content of alert log file.
      ===================

      Mon Dec 6 16:03:00 2010
      Thread 1 advanced to log sequence 67690 (LGWR switch)
      Current log# 3 seq# 67690 mem# 0: /d01/oracle/oradata/stlbas/redo03.log
      Mon Dec 6 16:20:39 2010
      Process J000 died, see its trace file
      Mon Dec 6 16:20:39 2010
      kkjcre1p: unable to spawn jobq slave process
      Mon Dec 6 16:20:39 2010
      Errors in file /d04/admin/stlbas/bdump/stlbas_cjq0_1885.trc:

      Process J000 died, see its trace file
      Mon Dec 6 16:20:45 2010
      kkjcre1p: unable to spawn jobq slave process
      Mon Dec 6 16:20:45 2010
      Errors in file /d04/admin/stlbas/bdump/stlbas_cjq0_1885.trc:

      Mon Dec 6 16:21:01 2010
      Process J000 died, see its trace file
      Mon Dec 6 16:21:01 2010
      kkjcre1p: unable to spawn jobq slave process
      Mon Dec 6 16:21:01 2010
      Errors in file /d04/admin/stlbas/bdump/stlbas_cjq0_1885.trc:

      Mon Dec 6 16:21:17 2010
      Process J000 died, see its trace file
      Mon Dec 6 16:21:17 2010
      kkjcre1p: unable to spawn jobq slave process
      Mon Dec 6 16:21:17 2010
      Errors in file /d04/admin/stlbas/bdump/stlbas_cjq0_1885.trc:

      Mon Dec 6 16:36:24 2010
      Process J000 died, see its trace file
      Mon Dec 6 16:36:24 2010
      kkjcre1p: unable to spawn jobq slave process
      Mon Dec 6 16:36:24 2010
      Errors in file /d04/admin/stlbas/bdump/stlbas_cjq0_1885.trc:

      Process J000 died, see its trace file
      Mon Dec 6 16:36:30 2010
      kkjcre1p: unable to spawn jobq slave process
      Mon Dec 6 16:36:30 2010
      Errors in file /d04/admin/stlbas/bdump/stlbas_cjq0_1885.trc:

      Mon Dec 6 16:36:41 2010
      Process J000 died, see its trace file
      Mon Dec 6 16:36:41 2010
      kkjcre1p: unable to spawn jobq slave process
      Mon Dec 6 16:36:41 2010
      Errors in file /d04/admin/stlbas/bdump/stlbas_cjq0_1885.trc:

      Mon Dec 6 16:38:18 2010
      Thread 1 advanced to log sequence 67691 (LGWR switch)
      Current log# 4 seq# 67691 mem# 0: /d01/oracle/oradata/stlbas/redo04.log




      contents in the trace file are -
      ==========================


      /d04/admin/stlbas/bdump/stlbas_cjq0_1885.trc
      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
      With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
      and Real Application Testing options
      ORACLE_HOME = /d04/oracle/ora102
      System name:     SunOS
      Node name:     badb1
      Release:     5.10
      Version:     Generic_142900-07
      Machine:     sun4v
      Instance name: stlbas
      Redo thread mounted by this instance: 1
      Oracle process number: 26
      Unix process pid: 1885, image: oracle@badb1 (CJQ0)

      *** SERVICE NAME:(SYS$BACKGROUND) 2010-12-01 13:24:30.248
      *** SESSION ID:(2191.1) 2010-12-01 13:24:30.248
      *** 2010-12-01 13:24:30.248
      Process J000 is dead (pid=25006, state=3):
      *** 2010-12-01 13:24:37.277
      Process J000 is dead (pid=25012, state=3):
      *** 2010-12-01 13:59:56.397
      Process J000 is dead (pid=1185, state=3):
      *** 2010-12-01 15:55:42.277
      Process J000 is dead (pid=15686, state=3):
      *** 2010-12-06 16:20:39.526
      Process J000 is dead (pid=9458, state=3):
      *** 2010-12-06 16:20:45.565
      Process J000 is dead (pid=9480, state=3):
      *** 2010-12-06 16:21:01.641
      Process J000 is dead (pid=9518, state=3):
      *** 2010-12-06 16:21:17.712
      Process J000 is dead (pid=9550, state=3):
      *** 2010-12-06 16:36:24.213
      Process J000 is dead (pid=11942, state=3):
      *** 2010-12-06 16:36:30.238
      Process J000 is dead (pid=11970, state=3):
      *** 2010-12-06 16:36:41.289
      Process J000 is dead (pid=11998, state=3):




      content of listener log file like bellows (in that time only)
      =====================================


      06-DEC-2010 16:16:21 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=APPLICATION-07)(USER=001zohur))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.36)(PORT=1771)) * establish * STLBAS * 0
      06-DEC-2010 16:16:21 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\RWRBE60.exe)(HOST=APPLICATION-02)(USER=154shahadat))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.32)(PORT=1528)) * establish * STLBAS * 0
      06-DEC-2010 16:16:21 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\RWRBE60.exe)(HOST=APPLICATION-07)(USER=043sathekur))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.36)(PORT=1758)) * establish * STLBAS * 12518
      TNS-12518: TNS:listener could not hand off client connection
      TNS-12547: TNS:lost contact
      TNS-12560: TNS:protocol adapter error
      TNS-00517: Lost contact
      Solaris Error: 32: Broken pipe
      06-DEC-2010 16:16:21 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=APPLICATION-07)(USER=038alfee))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.36)(PORT=1772)) * establish * STLBAS * 12518
      TNS-12518: TNS:listener could not hand off client connection
      TNS-12547: TNS:lost contact
      TNS-12560: TNS:protocol adapter error
      TNS-00517: Lost contact
      Solaris Error: 32: Broken pipe
      06-DEC-2010 16:16:22 * service_update * stlbas * 0
      06-DEC-2010 16:16:24 * service_update * stlbas * 0
      06-DEC-2010 16:16:25 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=APPLICATION-07)(USER=003aftab))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.36)(PORT=1794)) * establish * STLBAS * 12518
      TNS-12518: TNS:listener could not hand off client connection
      TNS-12547: TNS:lost contact
      TNS-12560: TNS:protocol adapter error
      TNS-00517: Lost contact
      Solaris Error: 32: Broken pipe
      06-DEC-2010 16:16:25 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\RWRBE60.exe)(HOST=NEW-SUN-APP)(USER=023sohel))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.37)(PORT=4291)) * establish * STLBAS * 12518
      TNS-12518: TNS:listener could not hand off client connection
      TNS-12547: TNS:lost contact
      TNS-12560: TNS:protocol adapter error
      TNS-00517: Lost contact
      Solaris Error: 32: Broken pipe
      06-DEC-2010 16:16:27 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=APPLICATION-07)(USER=038alfee))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.36)(PORT=1796)) * establish * STLBAS * 0
      06-DEC-2010 16:16:27 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=APPLICATION-07)(USER=038alfee))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.36)(PORT=1797)) * establish * STLBAS * 0
      06-DEC-2010 16:16:28 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\RWRBE60.exe)(HOST=APPLICATION-11)(USER=030salah))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.33)(PORT=3982)) * establish * STLBAS * 0
      06-DEC-2010 16:16:29 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=stlbas)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=REPORTS_APP)(USER=504refayet))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.233)(PORT=4995)) * establish * stlbas * 0
      06-DEC-2010 16:16:29 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=APPLICATION-07)(USER=019aporna))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.36)(PORT=1800)) * establish * STLBAS * 0
      06-DEC-2010 16:16:29 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=stlbas)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=REPORTS_APP)(USER=504refayet))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.233)(PORT=4996)) * establish * stlbas * 0
      06-DEC-2010 16:16:30 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=APPLICATION-07)(USER=003aftab))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.36)(PORT=1801)) * establish * STLBAS * 12518
      TNS-12518: TNS:listener could not hand off client connection
      TNS-12547: TNS:lost contact
      TNS-12560: TNS:protocol adapter error
      TNS-00517: Lost contact
      Solaris Error: 32: Broken pipe
      06-DEC-2010 16:16:30 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=APPLICATION-07)(USER=019aporna))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.36)(PORT=1802)) * establish * STLBAS * 12518
      TNS-12518: TNS:listener could not hand off client connection
      TNS-12547: TNS:lost contact
      TNS-12560: TNS:protocol adapter error
      TNS-00517: Lost contact
      Solaris Error: 32: Broken pipe
      06-DEC-2010 16:16:30 * service_update * stlbas * 0
      06-DEC-2010 16:16:30 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=stlbas)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=REPORTS_APP)(USER=501azad))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.233)(PORT=4997)) * establish * stlbas * 0
      06-DEC-2010 16:16:31 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=stlbas)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=REPORTS_APP)(USER=501azad))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.233)(PORT=4999)) * establish * stlbas * 0
      06-DEC-2010 16:16:32 * service_update * stlbas * 0




      What is the cause ?
      and
      why it shown "Solaris Error: 32: Broken pipe" ? please


      Best Regards
      Halim
        • 1. Re: Solaris Error: 32: Broken pipe
          Rajesh Lathwal
          Paste output of :

          select * from v$resource_limit ;
          • 2. Re: Solaris Error: 32: Broken pipe
            Abdul Halim
            Thanks

            Sorry for not Formatting .
            Actually I don't know how format code here.

            SQL>
            SQL>
            SQL> select * from v$resource_limit ;
            
            RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
            ------------------------------ ------------------- --------------- ---------- ----------
            processes                                     1607            4000       4000       4000
            sessions                                      1596            4005       4405       4405
            enqueue_locks                                 1380            6280      57390      57390
            enqueue_resources                              563            2901      19600  UNLIMITED
            ges_procs                                        0               0          0          0
            ges_ress                                         0               0          0  UNLIMITED
            ges_locks                                        0               0          0  UNLIMITED
            ges_cache_ress                                   0               0          0  UNLIMITED
            ges_reg_msgs                                     0               0          0  UNLIMITED
            ges_big_msgs                                     0               0          0  UNLIMITED
            ges_rsv_msgs                                     0               0          0          0
            gcs_resources                                    0               0          0          0
            gcs_shadows                                      0               0          0          0
            dml_locks                                       43             718      19380  UNLIMITED
            temporary_table_locks                            0               3  UNLIMITED  UNLIMITED
            transactions                                   306             722       4845  UNLIMITED
            branches                                         1              13       4845  UNLIMITED
            cmtcallbk                                        2               4       4845  UNLIMITED
            sort_segment_locks                            1594            4451  UNLIMITED  UNLIMITED
            max_rollback_segments                          136             361       4845      65535
            max_shared_servers                               0               0  UNLIMITED  UNLIMITED
            parallel_max_servers                          1324            3600       2560       3600
            
            22 rows selected.
            
            SQL>
            Regards
            Halim

            Edited by: Abdul Halim on Dec 7, 2010 4:04 PM

            Edited by: Abdul Halim on Dec 7, 2010 4:04 PM
            • 3. Re: Solaris Error: 32: Broken pipe
              Nicolas.Gasparotto
              Abdul Halim wrote:
              Thanks

              Sorry for not Formatting .
              Actually I don't know how format code here.
              Use tags
               
              {noformat}
              {noformat}paste your output here{noformat}
              {noformat}
              
              Nicolas.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
              • 4. Re: Solaris Error: 32: Broken pipe
                Abdul Halim
                Many thanks
                SQL>
                SQL>
                SQL> select * from v$resource_limit ;
                
                RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
                ------------------------------ ------------------- --------------- ---------- ----------
                processes                                     1607            4000       4000       4000
                sessions                                      1596            4005       4405       4405
                enqueue_locks                                 1380            6280      57390      57390
                enqueue_resources                              563            2901      19600  UNLIMITED
                ges_procs                                        0               0          0          0
                ges_ress                                         0               0          0  UNLIMITED
                ges_locks                                        0               0          0  UNLIMITED
                ges_cache_ress                                   0               0          0  UNLIMITED
                ges_reg_msgs                                     0               0          0  UNLIMITED
                ges_big_msgs                                     0               0          0  UNLIMITED
                ges_rsv_msgs                                     0               0          0          0
                gcs_resources                                    0               0          0          0
                gcs_shadows                                      0               0          0          0
                dml_locks                                       43             718      19380  UNLIMITED
                temporary_table_locks                            0               3  UNLIMITED  UNLIMITED
                transactions                                   306             722       4845  UNLIMITED
                branches                                         1              13       4845  UNLIMITED
                cmtcallbk                                        2               4       4845  UNLIMITED
                sort_segment_locks                            1594            4451  UNLIMITED  UNLIMITED
                max_rollback_segments                          136             361       4845      65535
                max_shared_servers                               0               0  UNLIMITED  UNLIMITED
                parallel_max_servers                          1324            3600       2560       3600
                
                22 rows selected.
                • 5. Re: Solaris Error: 32: Broken pipe
                  Rajesh Lathwal
                  >
                  RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
                  ------------------------------ ------------------- --------------- ---------- ----------
                  processes 1607 4000 4000 4000
                  sessions 1596 4005 4405 4405
                  You have reached your max utilization of process parameter.

                  So increase processes and sessions parameter and take database bounce to reflect changes.

                  Regards
                  Rajesh

                  Edited by: Rajesh on Dec 7, 2010 3:52 PM
                  • 6. Re: Solaris Error: 32: Broken pipe
                    Abdul Halim
                    Many Thanks Rajesh

                    One more question

                    Is there any side affect of so big processes and sessions parameter ?

                    suppose

                    Processes=6000
                    sessions = 6000

                    Or any thing consideration .


                    Regards
                    Halim
                    • 7. Re: Solaris Error: 32: Broken pipe
                      asifkabirdba
                      select count(*)
                      from V$process;

                      Use this query during the peak hours to count the process number connected in the database. Set those parameters 50% higher than value of the query.


                      Regards
                      Asif Kabir
                      • 8. Re: Solaris Error: 32: Broken pipe
                        Rajesh Lathwal
                        Check CPU/Memory utilization of server before increasing the values .

                        Also you might have to increase other parameter also like SGA and PGA accordingly. So check their utilization also.

                        You will have to analyze database load during peak time and also find out the which processes are creating so many sessions.

                        Also sessions parameter is a derived parameter . Set its values as (1.1 * PROCESSES) + 5 .

                        Regards
                        Rajesh

                        Edited by: Rajesh on Dec 7, 2010 4:14 PM
                        • 9. Re: Solaris Error: 32: Broken pipe
                          Nicolas.Gasparotto
                          Abdul Halim wrote:
                          Many Thanks Rajesh

                          One more question

                          Is there any side affect of so big processes and sessions parameter ?

                          suppose

                          Processes=6000
                          sessions = 6000
                          If you have so high requirements, you really should think about a pool of connections. Each dedicated session creates process onto the server, consequently, you may overload your server itself. These parameters should not be set too large.
                          You should read some warning in the docs :
                          http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams191.htm#REFRN10197
                          http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams169.htm#REFRN10175

                          Nicolas.
                          • 10. Re: Solaris Error: 32: Broken pipe
                            Abdul Halim
                            Many Thanks to everybody


                            Regards
                            Halim
                            • 11. Re: Solaris Error: 32: Broken pipe
                              Rajesh Lathwal
                              please also mark thread as answered ..

                              Regards
                              Rajesh
                              • 12. Re: Solaris Error: 32: Broken pipe
                                Abdul Halim
                                I got my answer. thanks