6 Replies Latest reply on Jul 25, 2014 4:52 PM by clcarter

    Oracle 11g Express: listener/DB stopped regularly ...


      Dear all,


      we currently use oracle DB 11g express edition on Win XP in order to do nigthly test of our product accessing the DB via a JDBC driver. After two successful nightly test communicating via JDBC doesn't work anymore due to stopped DB listener. The first error message I got was "ORA-12518, TNS:listener could not hand off client connection", I fixed it via settings up maximum processes to 450, then I got the next error message where I don't know what to do "ORA-12560: TNS:protocol adapter error". Please can you help me since I need a stable DB for nightly continuous integration tests.


      Thank you in advance, Andreas.

        • 1. Re: Oracle 11g Express: listener/DB stopped regularly ...

          Bumping processes would seem to be a symptom fix, not a problem fix- if a session can't get a connection because available processes are at the max, must be some client programs with a bug/glitch where they aren't letting go of their session(s).


          Eg. maybe hitting an exception block, and the code never sees a "close connection" call? Until that bug (the problem) gets fixed, increasing process limits is not going to be much of a fix. Just a guess, can't know for sure without seeing the code.


          Anyways, for the ora-12560 something may have gone astray (or changed?) with host network adapter settings- hostname change? IP address change? IPv6? Is the host a DHCP client? Is there more than one NIC on the host? Do both the hostname and its IP address(es) resolve correctly? (... ipconfig /all ... `hostname` ... nslookup <hostname> ... nslookup <n.n.n.n> ...)


          One item to try, stop the listener and move the listener.ora file "out of the way" (eg move listener.ora listener.ora.bk) as others have suggested here before. Any RDBMS install has its default listener, named LISTENER, and if all the default settings are OK for your setup then using that should work fine. Double checking that the listener service still shows in the services applet would also be advisable, only takes a moment ... Start/Run/services.msc ...


          Another possible fix for ora-12560 is setting ...HOST= in listener.ora, that is the "any IPv4" address setting, but that is advisable only if the host has one NIC, otherwise the instance would be accepting connections at an address you might not want made available to remote clients.

          • 2. Re: Oracle 11g Express: listener/DB stopped regularly ...

            Oracle XE has a 1GB memory limitation.


            Each client connection, requires memory.


            If dedicated, an oracle.exe thread will be created (on Windows). The thread will contain both PGA (process memory) and UGA (static session memory).


            If shared, a dispatcher process will deal with the client communication (listener hands off connection to the dispatcher). An idle shared server process will deal with the client's request. A fixed pool of dispatchers and shared servers are needed - each with a PGA, So this scales better as this architecture can for example handle a 100 client sessions with 10 processes. Thus 10 PGAs. Versus dedicated servers that will consume a 100 PGAs. However, the UGAs are still needed. All 100. And this will now reside in the SGA (reducing space for other pools like the buffer cache and shared pool).


            Also shared server is only ideal for OLTP type sessions.


            So either way, memory (UGA and PGA) is spend on each client session. And memory needs to be spend on the SGA. With only 1GB to go around, it means there is a very hard limit to the number of client session that can be supported in Oracle XE.


            An "ORA-12518, TNS:listener could not hand off client connection" is an indication that the listener is unable to hand off the client to a dedicated or dispatcher/shared server process.Which implies that this limit has been reached.

            • 3. Re: Oracle 11g Express: listener/DB stopped regularly ...

              Good catch there Sir (?) Billy-Verreynne. A better spot of the possible problem cause on the -12518, you're deserving the answer points.


              I rarely push an XE instance beyond 20 or 30 sessions, when my XE is actually running. Which is not very often, got plenty other EE installs to poke and push for run-test-break-fix scenarios.

              • 4. Re: Oracle 11g Express: listener/DB stopped regularly ...

                I am no Sir. (geez, just look at my avatar!)


                My colleagues are using Oracle XE as db layer with a Java app that probes devices. So data is supplied to the program, and data (a fair amount at times) are collected by it. XE was chosen as Oracle SE is too pricey for that. The Java app uses threading (probes 1000's of devices at time) and thus a lot of Oracle client sessions.


                So we have bump, and bumped hard, against the Oracle XE ceiling of max sessions.

                • 5. Re: Oracle 11g Express: listener/DB stopped regularly ...

                  Dear clcarter and Billy~Verreynne,


                  thank you for the details about my oracle XE problem! Perhaps the explanation of Billy~Verreynne was the right idea. We had some nightly tests which opens simultaneous connections to the oracle DB. I switched them off and I will see on next monday if now the tests were successful and the Oracle XE doesn't report errors for the remaining single-connection tests. I will give you some small feedback on monday.


                  Best, Andreas.

                  • 6. Re: Oracle 11g Express: listener/DB stopped regularly ...

                    Whoops, sorry.


                    Do really like that hat, I'd like one preferably in orange.