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

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

user11234030 Newbie
Currently Being Moderated

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?

Legend

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