6 Replies Latest reply: Dec 27, 2013 10:37 AM by Shivendra Narain Nirala RSS

    ORA-12520: TNS:listener could not find available handler for requested type of server

    user11234030

      From the output of "lsnrctl services PPBR0P1_CDCPBR11", we found blocked state as follows:

      Service "PPBR0P1" has 2 instance(s).

        Instance "PPBR0P11", status READY, has 2 handler(s) for this service...

          Handler(s):

            "DEDICATED" established:63936 refused:0 state:ready

               REMOTE SERVER

               (ADDRESS=(PROTOCOL=TCP)(HOST=160.86.18.198)(PORT=10110))

            "DEDICATED" established:139660 refused:0 state:blocked

               LOCAL SERVER

        Instance "PPBR0P12", status READY, has 1 handler(s) for this service...

          Handler(s):

            "DEDICATED" established:627599 refused:0 state:ready

               REMOTE SERVER

               (ADDRESS=(PROTOCOL=TCP)(HOST=160.86.18.199)(PORT=10110))

       

       

        1* select * from gv$resource_limit

      SQL> /

       

         INST_ID RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION                       LIMIT_VALUE

      ---------- ------------------------------ ------------------- --------------- ---------------------------------------- ----------------------------------------

               1 processes                                      490             596       2500                                     2500

               1 sessions                                       479             710       2755                                     2755

               1 enqueue_locks                                  116             352      34058                                    34058

               1 enqueue_resources                              248             757      12340                                UNLIMITED

               1 ges_procs                                      489             594       2501                                     2501

               1 ges_ress                                         0               0      53325                                UNLIMITED

               1 ges_locks                                        0               0      82536                                UNLIMITED

               1 ges_cache_ress                                4391            9801          0                                UNLIMITED

               1 ges_reg_msgs                                   438            1453       5750                                UNLIMITED

               1 ges_big_msgs                                   115             343       1934                                UNLIMITED

               1 ges_rsv_msgs                                     0               0       1000                                     1000

       

         INST_ID RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION                       LIMIT_VALUE

      ---------- ------------------------------ ------------------- --------------- ---------------------------------------- ----------------------------------------

               1 gcs_resources                              3009854         3623192    5125827                                  5125827

               1 gcs_shadows                                2107112         2304012    5125827                                  5125827

               1 dml_locks                                       64            6450      12120                                UNLIMITED

               1 temporary_table_locks                            0               3  UNLIMITED                                UNLIMITED

               1 transactions                                     3             162       3030                                UNLIMITED

               1 branches                                         0               0       3030                                UNLIMITED

               1 cmtcallbk                                        0               2       3030                                UNLIMITED

               1 sort_segment_locks                              22              29  UNLIMITED                                UNLIMITED

               1 max_rollback_segments                          170             170       3030                                    65535

               1 max_shared_servers                               1               1  UNLIMITED                                UNLIMITED

               1 parallel_max_servers                            30              73        640                                     3600

       

         INST_ID RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION                       LIMIT_VALUE

      ---------- ------------------------------ ------------------- --------------- ---------------------------------------- ----------------------------------------

               2 processes                                      586             630       2500                                     2500

               2 sessions                                       573             642       2755                                     2755

               2 enqueue_locks                                  121             250      34058                                    34058

               2 enqueue_resources                              227             761      12340                                UNLIMITED

               2 ges_procs                                      585             627       2501                                     2501

               2 ges_ress                                         0               0      53325                                UNLIMITED

               2 ges_locks                                        0               0      82536                                UNLIMITED

               2 ges_cache_ress                                4372            9717          0                                UNLIMITED

               2 ges_reg_msgs                                   683            1675       5750                                UNLIMITED

               2 ges_big_msgs                                   118            1082       1934                                UNLIMITED

               2 ges_rsv_msgs                                     0               0       1000                                     1000

       

         INST_ID RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION                       LIMIT_VALUE

      ---------- ------------------------------ ------------------- --------------- ---------------------------------------- ----------------------------------------

               2 gcs_resources                              3016125         3853151    5132343                                  5132343

               2 gcs_shadows                                2154134         2254592    5132343                                  5132343

               2 dml_locks                                       91            5313      12120                                UNLIMITED

               2 temporary_table_locks                            0             192  UNLIMITED                                UNLIMITED

               2 transactions                                     7             168       3030                                UNLIMITED

               2 branches                                         0               0       3030                                UNLIMITED

               2 cmtcallbk                                        0               3       3030                                UNLIMITED

               2 sort_segment_locks                              85              97  UNLIMITED                                UNLIMITED

               2 max_rollback_segments                          190             190       3030                                    65535

               2 max_shared_servers                               1               1  UNLIMITED                                UNLIMITED

               2 parallel_max_servers                            30              69        640                                     3600

       

      44 rows selected.

       

       

      Why?