9 Ответы Последний ответ: 16.07.2016 9:05, автор: Samrat Sinha

    ORA-3136

    Beauty_and_dBest

      Hi ALL,

       

      EBS R12.1.3

      11gR2

      OL5

       

       

      I have checked our alert PROD log , and all I can see are lots and lots of  ORA-3136.

      It filled up with these messages, I can not even see any other messages except this one

      How do I resolved this please...

       

      Kind regards,

      jc

       

      ***********************************************************************

      Fatal NI connect error 12170.

       

        VERSION INFORMATION:

              TNS for Linux: Version 11.1.0.7.0 - Production

              Oracle Bequeath NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production

              TCP/IP NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production

        Time: 27-JUN-2016 17:55:35

        Tracing not turned on.

        Tns error struct:

          ns main err code: 12535

       

      TNS-12535: TNS:operation timed out

          ns secondary err code: 12606

          nt main err code: 0

          nt secondary err code: 0

          nt OS err code: 0

        Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=172.26.1.240)(PORT=42783))

      WARNING: inbound connection timed out (ORA-3136)

      Mon Jun 27 17:55:45 2016

       

      ***********************************************************************

       

       

       

      I searched google, and it says the cause is our shared pool is small?

       

      Our current parameter settings are:

       

      processes=2400

      sessions=2645

      sga_target = 15G

      shared_pool_size = 832M

      shared_pool_reserved_size = 80M

      pga_aggregate_target = 15G


       

      How much more do I need to increase them?



      I see this Oracle recommended sizing


      processes=2500

      sessions=5000

      sga_target=14G

      shared_pool_size=3000M

      shared_pool_reserved_size=300M

      pga_aggregate_target=20G


      Would these helped resolved the issue?



      Kind regards,

        • 1. Re: ORA-3136
          Girish Sunkara

          Hello,

           

          Try to reproduce the issue from a different client machine, preferably from server. This will rule out the possibility of bad network. If the execution fails from a particular client then it could be due to the bad network from that client to server. 

           

          Also, may also be a one time occurrence, if the issue is not reproduced you can ignore this error.

           

          Regards,

          Girish.

          1 пользователь считает эту информацию полезной
          • 2. Re: ORA-3136
            Beauty_and_dBest

            Thanks Girish,

             

            But we can not able to reproduce it to a test server because we do have have enough users to make stress-load-regression test.

            As I said it is recurring everyday filling the alter log with tons of this message.

             

            Is there a command to check the network if it is the culprit?

             

             

            Kind regards,

            • 3. Re: ORA-3136
              Bashar.

              You may consider Oracle Load Testing Suite:

               

              Application Testing Suite

               

              Regards,

              Bashar

              1 пользователь считает эту информацию полезной
              • 4. Re: ORA-3136
                VishnuVinnakota

                Hi,

                 

                   The error is basically informational. However, if you would like to troubleshoot or address it:

                • SQLNET.INBOUND_CONNECT_TIMEOUT is the parameter value which might need to be modified. The error says the one of the clients is unable to connect in the time limit.
                • Check the listener log file as well and try to enable debugging of listener to find better details

                 

                Troubleshooting Guide ORA-3136: WARNING Inbound Connection Timed Out (Doc ID 465043.1)

                Allocation or FTP Process Hangs on Server During Processing with 'TNS-12535: TNS:operation Timed Out' in Alert Log (Doc ID 1571472.1)

                Fatal NI Connect Error 12170, 'TNS-12535: TNS:operation timed out' Reported in 11g Alert Log (Doc ID 1286376.1)

                 

                Thanks,

                Vishnu

                1 пользователь считает эту информацию полезной
                • 5. Re: ORA-3136
                  Beauty_and_dBest

                  Hi ALL,

                   

                  I already increased the shared pool but it seems the issue ins not resolved.

                   

                  This is the count how many times ORA-3136  appeared per day:

                   

                  Before increasing shared pool:

                    102 jun20 Monday

                    193 jun21 Tue

                     75 jun22 Wed

                     88 jun23 Thu

                    129 jun24 Fri

                   

                  After increasing shared pool:

                     83 jul06  Wed

                    317 jul07  Thu

                    178 jul08  Fri

                  No transactions on weekends

                     55 jul11  Mon

                    173 jul12  Tue

                    279 jul13  Wed

                   

                  It seems it got worst?

                   

                  My next action plan will be to set > SQLNET.INBOUND_CONNECT_TIMEOUT = 180 ?

                   

                  Kind regards,

                  • 6. Re: ORA-3136
                    Samrat Sinha

                    Hi

                     

                    I had the same issue in one of our EBS environment few months back. Followed many metalink notes but nothing worked in my way. Oracle's recommendation was to increase the SQLNET.INBOUND_CONNECT_TIMEOUT value but this too didn't resolve my problem.

                     

                    What i did--In my case, the total memory allocated to DB was-96GB. And SGA was set too high to 70GB and PGA was set to 13 GB. So adding these two makes it 83GB. And internally OS takes around 9-10 GB for its own processes. Thus memory left to my DB server was 3 GB. I monitored my system for few more days and observed that swap was continuously getting used when Concurrent processing were high. I went through many awr reports and then set the SGA to 40% of my total memory. In my case, this resolved the issue. Just wanted to share my experience.

                     

                    Hope it helps

                     

                    Thanks

                    1 пользователь считает эту информацию полезной
                    • 7. Re: ORA-3136
                      Bashar.

                      I agree with you that SGA and PGA should not be set to very high values as it may cause some weird issues.

                      The sizing should be done based on the estimated number of users and then tuned to make the best use of resources.

                       

                      Regards,

                      Bashar

                      1 пользователь считает эту информацию полезной
                      • 8. Re: ORA-3136
                        Beauty_and_dBest

                        Thanks bashar and ALL

                         

                        The generic DBAs (non-apps dba) does not agree either increasing SQLNET.INBOUND_CONNECT_TIMEOUT.

                        See their comments> How to set  SQLNET.INBOUND_CONNECT_TIMEOUT?

                         

                        Does this mean that the network connection speed between apps node and db node is slow?

                        Or maybe the solution is to put apps & db in one server so that no more network issues can arise?

                         

                        Or Maybe it is the scheduling or processing style we are doing? We have  hundreds of users calling same program to run every minute? may have cause the issue?

                        Or Maybe I just ignore them? As the percentage misses is slow, When we run 10,000+ programs and missis 200 inbound ORA-3136.

                         

                        ,

                         

                        Kind regards,

                        • 9. Re: ORA-3136
                          Samrat Sinha

                          Hi

                           

                          Putting apps and db in same node is not all a solution. If you can accept the downtime then it be configured in same node. I would suggest you to get involved your network team and see if there are any packet loss happening.

                           

                          If the errors are acceptable then it can be ignored.

                           

                           

                          Thanks

                          1 пользователь считает эту информацию полезной