10 Replies Latest reply on Aug 11, 2011 12:39 PM by 715920

    Communication between TNS_Listener and Apex Listener

    715920
      Dear all,
      I how does the communication between TNS Listener, Apex Listener, and Apex? For some reason I got the following error from Apex listener:
      Caused by: oracle.dbtools.rt.web.WebException: Unable to start the Universal Connection Pool: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource
      .
      .
      .
      Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
      ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
      I truncated some of the Java object messages.

      This occurred when I tried to connect to Apex. Then I can fix this by going to:
      http://localhost:8080/apex/listenerConfigure
      then, everything worked fine.

      My listener.ora file:
      LISTENER =
        (DESCRIPTION_LIST =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
          )
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = firefly.snowdrop.com)(PORT = 1521))
          )
        )
      
      ADR_BASE_LISTENER = /usr/local/oracle
      
      ADR_BASE_ORACLE_LISTENER = /usr/local/oracle
      my tnsnames.ora file
      INARA =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = firefly.snowdrop.com)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = inara.snowdrop.com)
         )
        )
      where inara is the instance name and firefly is the hostname.

      Does the order of whether starting TNS Listener first or starting Apex Listener first matter?

      Best regards,
      Val

      Edited by: Valerie Debonair on Aug 1, 2011 5:47 AM
        • 1. Re: Communication between TNS_Listener and Apex Listener
          Udo
          Hello Val,
          Does the order of whether starting TNS Listener first or starting Apex Listener first matter?
          yes. The APEX Listener maintains a connection pool. Depending on the parameters you configured it for, this pool will start with a certain minimum and initial number of connections, which means that these will be opened upon restart. Possibly you didn't touch that part, so you're running on defaults, which would be three connection as initial value and a minimum of one connection.

          To ensure that there are no other obstacles, you could run a tnsping from the host you run the APEX Listener on to see if the TNS target is reachable at all.
          Note that using the basic JDBC connection also needs the database Listener to be up first.

          -Udo
          1 person found this helpful
          • 2. Re: Communication between TNS_Listener and Apex Listener
            jflack
            The way to solve this sort of problem is to break it down into component parts. The TNS listener is providing network services to the database which the ApEx listener is consuming. There are other consumers of this service, like SQL*Plus or SQL Developer. To take the ApEx Listener out of the equation, you should first try to reach your database with SQL*Plus or SQL Developer on the same server where the ApEx Listener will be running. I'd try SQL Developer, because it uses a JDBC connection much like the one used by the ApEx Listener. When you set up your connection in SQL Developer, give it the same parameters that you used when you configured the ApEx Listener.

            Now, my guess is that SQL Developer won't be able to connect either, but at least that will tell you that the problem is with the connection parameters that you gave, not with the ApEx Listener. The error you are getting says that the TNS Listener does not know of the SID. That tells me that the TNS Listener is up and running, and that you were able to connect to the TNS Listener, but the database that you told it to connect you to is unknown.

            You specify a database in your client side (SQL Developer or the ApEx Listener) with either a SID or a Service Name if you are using the Basic Connection Type or a Network Alias if you are using the TNS Connection Type. For a TNS Connection Type, the Network Alias points to an entry in the TNSNAMES.ORA file, which contains the SID or SERVICE_NAME parameter. Which reminds me - you didn't include your ApEx Listener configuration information.

            A DBA can connect to the database and issue this SELECT to find the service name:
            select * from v$parameter where name = 'service_names'
            1 person found this helpful
            • 3. Re: Communication between TNS_Listener and Apex Listener
              715920
              Hm, I'm not able to reproduce the error. There was one time when I started APEX Listener got:
              java.net.BindException: Address already in use: 8080=com.sun.grizzly.http.SelectorThreadHandler@46d999a
              and when I did the netstat -a got:
              root@firefly:~# netstat -apnl |grep 8080
              tcp6       0      0 :::8080                 :::*                    LISTEN      4530/tnslsnr  
              I had no idea what made 8080 tagged by the TNS listener, as I started it out before APEX Listener. Very curious how did this happen.

              There was one time netstat -a showed me this (this happened after I stopped Apex Listener, stopped TNS Listener, shutdown the database, started database, started TNS Listener, and started Apex Listener):
              root@firefly:~# netstat -a |grep 8080
              tcp        0      0 firefly.snowdrop.:22110 firefly.snowdrop.c:8080 ESTABLISHED
              tcp        0      0 firefly.snowdrop.:22111 firefly.snowdrop.c:8080 ESTABLISHED
              tcp        0      0 firefly.snowdrop.:22107 firefly.snowdrop.c:8080 ESTABLISHED
              tcp        0      0 firefly.snowdrop.:22109 firefly.snowdrop.c:8080 ESTABLISHED
              tcp        0      0 firefly.snowdrop.:22112 firefly.snowdrop.c:8080 ESTABLISHED
              tcp        0      0 firefly.snowdrop.:22108 firefly.snowdrop.c:8080 ESTABLISHED
              tcp6       0      0 [::]:8080               [::]:*                  LISTEN     
              tcp6       0      0 firefly.snowdrop.c:8080 firefly.snowdrop.:22108 ESTABLISHED
              tcp6       0      0 firefly.snowdrop.c:8080 firefly.snowdrop.:22107 ESTABLISHED
              tcp6       0      0 firefly.snowdrop.c:8080 firefly.snowdrop.:22112 ESTABLISHED
              tcp6       0      0 firefly.snowdrop.c:8080 firefly.snowdrop.:22109 ESTABLISHED
              tcp6       0      0 firefly.snowdrop.c:8080 firefly.snowdrop.:22110 ESTABLISHED
              tcp6       0      0 firefly.snowdrop.c:8080 firefly.snowdrop.:22111 ESTABLISHED
              What are 22108, 22107, etc? I cannot reproduce this either.

              Now I only got
              root@firefly:~# netstat -a |grep 8080
              tcp6       0      0 [::]:8080               [::]:*                  LISTEN     
              root@firefly:~# netstat -pnl |grep 8080
              tcp6       0      0 :::8080                 :::*                    LISTEN      5035/java   
              which is normal, right?

              Edited by: Valerie Debonair on Aug 1, 2011 9:43 PM
              • 4. Re: Communication between TNS_Listener and Apex Listener
                Udo
                Hello Val,
                I had no idea what made 8080 tagged by the TNS listener, as I started it out before APEX Listener. Very curious how did this happen.
                Well, this was the XDB HTTP Server. Since it is "inside" the database, its connections are handled by the database listener as well. You probably deactivated that server now, so you don't see the bind anymore.
                What are 22108, 22107, etc? I cannot reproduce this either.
                Are you sure you didn't see any Java process in the netstat list that time?
                Its pretty normal you have other ports opened as well. That's how socket communication works: Have a (well-)known port (usually below 1024, 8080 is just "registered") to call a service, but if this would be the only port, you could only handle one client at a time. Therefore, the server only handles the initial request on that port and opens a new port for the actual client session. This port is supposed to be outside the well-known ports. Depending on the implementation, some services use ports from 1024 on, some start after 16384, some after 32768 (where there are only little registered ports) and "good" services start after 49152, because that's where the "non-registered" ports are.
                So I'd say your netstat shows some normal usage - there are open (local) client connections to the service listening on port 8080.
                Could it be you opened the listenerConfigure or listenerAdmin before you ran netstat?

                -Udo
                1 person found this helpful
                • 5. Re: Communication between TNS_Listener and Apex Listener
                  715920
                  Udo wrote:
                  Are you sure you didn't see any Java process in the netstat list that time?
                  Its pretty normal you have other ports opened as well. That's how socket communication works: Have a (well-)known port (usually below 1024, 8080 is just "registered") to call a service, but if this would be the only port, you could only handle one client at a time. Therefore, the server only handles the initial request on that port and opens a new port for the actual client session. This port is supposed to be outside the well-known ports. Depending on the implementation, some services use ports from 1024 on, some start after 16384, some after 32768 (where there are only little registered ports) and "good" services start after 49152, because that's where the "non-registered" ports are.
                  So I'd say your netstat shows some normal usage - there are open (local) client connections to the service listening on port 8080.
                  Could it be you opened the listenerConfigure or listenerAdmin before you ran netstat?

                  -Udo
                  Hi Udo,
                  when you said 'service', did you mean 'a connection between Apex and Apex listener' or something else? I remember that the 8080 connection establishments that netstat -a showed happened after I had had Apex Listener connecting and reconnecting to Apex multiple times. This might be the cause, huh?
                  • 6. Re: Communication between TNS_Listener and Apex Listener
                    715920
                    jflack wrote:
                    You specify a database in your client side (SQL Developer or the ApEx Listener) with either a SID or a Service Name if you are using the Basic Connection Type or a Network Alias if you are using the TNS Connection Type. For a TNS Connection Type, the Network Alias points to an entry in the TNSNAMES.ORA file, which contains the SID or SERVICE_NAME parameter. Which reminds me - you didn't include your ApEx Listener configuration information.

                    A DBA can connect to the database and issue this SELECT to find the service name:
                    select * from v$parameter where name = 'service_names'
                    Hi jflack,
                    I can't reproduce the problem, but your remark made me wondered. I have SQL Developer running fine with Basic Connection Type and using SID Inara. I remember I configure Apex Listener using Basic Connection Type, which was done once in the initial configuration. My questions are:
                    1. Is there any way to review and change the connection type after Apex works and runs? Just for curiosity sake.
                    2. If I'm using Basic Connection Type, I don't have to include anything on my TNSNAME.ORA file, do I?

                    Edited by: Valerie Debonair on Aug 2, 2011 9:23 PM
                    • 7. Re: Communication between TNS_Listener and Apex Listener
                      Udo
                      Hi Val,
                      I remember that the 8080 connection establishments that netstat -a showed happened after I had had Apex Listener connecting and reconnecting to Apex multiple times. This might be the cause, huh?
                      I think so.
                      1. Is there any way to review and change the connection type after Apex works and runs? Just for curiosity sake.
                      Yes, there is. Take a look into the apex-config.xml . It's located in the apex.home if you set this up as start up parameter for the standalone mode. Otherwhise it defaults to a directory "apex" below your your users temp directory.
                      Note that you should always stop the APEX Listener before changing the connection parameters.
                      2. If I'm using Basic Connection Type, I don't have to include anything on my TNSNAME.ORA file, do I?
                      On a client perspective, that's true. However, since you have the database on the same host, you have a tnsnames.ora there as well. You need to have that one configured properly for the database components, such as the database listener and the client (e.g. sqlplus) usually included in the database installation.

                      -Udo
                      1 person found this helpful
                      • 8. Re: Communication between TNS_Listener and Apex Listener
                        715920
                        Udo wrote:
                        2. If I'm using Basic Connection Type, I don't have to include anything on my TNSNAME.ORA file, do I?
                        On a client perspective, that's true. However, since you have the database on the same host, you have a tnsnames.ora there as well. You need to have that one configured properly for the database components, such as the database listener and the client (e.g. sqlplus) usually included in the database installation.

                        -Udo
                        hi Udo,
                        my tnsnames.ora is configured for an existing server and it doesn't have client configurations like sqlplus, sql developer, or apex listener. However so far the server can connect to those clients with no problem (I just found out that unreproducible error about "cannot find SID" sometimes occurs in the beginning of Apex kicking off and can be resolved by wait for a while for the connection to reconnect), so do I still need to modify the .ora file?
                        • 9. Re: Communication between TNS_Listener and Apex Listener
                          Udo
                          Hi Val,
                          my tnsnames.ora is configured for an existing server and it doesn't have client configurations like sqlplus, sql developer, or apex listener. However so far the server can connect to those clients with no problem (I just found out that unreproducible error about "cannot find SID" sometimes occurs in the beginning of Apex kicking off and can be resolved by wait for a while for the connection to reconnect), so do I still need to modify the .ora file?
                          No, if your database listener is configured properly (i.e., it listens to the SID you try to connect to on the network interface/hostname/ip adress/... it should use), there is no need to do any further configuration, especially not on a different machine.

                          -Udo
                          • 10. Re: Communication between TNS_Listener and Apex Listener
                            715920
                            Hi Udo,
                            thanks for all your feedback and answers to the questions. I notice that you single handedly taking care of this thread giving us tremendous help. Thank you very much [thumbs up!]......