Forum Stats

  • 3,759,865 Users
  • 2,251,607 Discussions
  • 7,870,847 Comments

Discussions

Each schema on separate listener

1235»

Answers

  • EdStevens
    EdStevens Member Posts: 28,484 Gold Crown
    edited Feb 20, 2018 2:07PM
    rp0428 wrote:So how do you get the one database to register a specific service on a specific listener?By specifying the services and listeners in the LISTENER_NETWORKS parameter.https://docs.oracle.com/database/121/NETAG/listenercfg.htm#NETAG1470 

    Registering Information with All Listeners in a Network

    A network may contain multiple local and remote listeners. By default, all listeners are cross-registered with each other. By specifying a set of listeners in the LISTENER_NETWORKS initialization parameter, you can designate a subset of local listeners with a subset of remote listeners. Listeners specified by the LISTENER_NETWORKS parameter should not be specified by the LOCAL_LISTENER and REMOTE_LISTENER parameters.

    Sorry, but I'm still not seeing how one ties that to SERVICE_NAMES, so that app_svc_1 registers with app_listener_1 and app_svc_2 registers with app_listener_2. 

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited Feb 20, 2018 2:13PM

    Catinredboots, here is a link to the Oracle description of how the listener works and mention of using Oracle services

    https://docs.oracle.com/database/121/CNCPT/dist_pro.htm#CNCPT1274

    - -

    HTH -- Mark D Powell --

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond
    edited Feb 20, 2018 2:24PM
    Catinredboots wrote:Please explain this "the app will communicate with that second user process on a completely different port. In fact, all processes get their own port." - as I'm not sure what you mean it will get a different port. I thought each time there is connection to database the port number will be the same as we have only one listener registered with one port. 

    The Listener is only involved in brokering the connection request. Once the connection is established, the Listener is no longer involved between the application and the database. The only time port 1521 is used, it is only for setting up that connection. Oracle creates a process on the db server. The app talks directly to this process and never talks to the Listener again.

    Here, I have two sessions for me:

    SQL> select paddr from v$session where username='PEASLAND';

    PADDR

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

    00000000DE253578

    00000000DE24F130

    I then get the OS process ID for those two sessions:

    SQL> select spid from v$process where addr in

      2  ('00000000DE253578','00000000DE24F130');

    SPID

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

    32177

    31993

    Now let's look at see what those ports are:

    lsof -p 32177 -n | grep TCP

    oracle_32 32177 oracle  33u    IPv4 635709546      0t0      TCP 10.xxx.xxx.xxx:52622->10.xxx.xxx.yyy (ESTABLISHED)

    lsof -p 31993 -n | grep TCP

    oracle_31 31993 oracle  32u    IPv4 530845258      0t0      TCP 10.xxx.xxx.xxx:46162->10.xxx.xxx.yyy (ESTABLISHED)

    I have two connections using two different processes. One is communicating via port 52622 the other via port 46162.

    Now all that said, in the end your app devs really don't care about all of this. If you have a discussion with them to try to figure out exactly what their concerns and requirements are, you'll probably find out that none of them really care in the grand scheme of things. All they care about is that they want the app to connect to the database. A secondary concern might be that they don't want all traffic through one port. But as you can see, each process on the database side gets a different port. I'm reading between the lines, but I bet your devs though all traffic went through port 1521, and that's just not the case. Port 1521 is only used to establish the connection then not again for that session.

    Cheers,
    Brian

    L. FernigriniJuanM
  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited Feb 20, 2018 3:18PM

    All, Earlier I had said >> Normally, the listener is only used to make the initial connection to Oracle and every session is assigned its own port for communication with Oracle. <<  I had also asked the server which tuned out to be Linux.

    - -

    I wounder if this request from the developers traces it roots back to a past solution on the Windows platform to firewall port access issues.  It was possible on Windows to force all connections to use one port, normally 1521, through the firewall.

    - -

    Solving Firewall Problems on Windows (Doc ID 68652.1)

    Windows: How to configure USE_SHARED_SOCKET 8i - 10gR1 (Doc ID 124140.1)

    - -

    Just speculation.

    - -

    Brian, I like your example even if I got a permissions error when I tried it on AIX.

    - -

    HTH -- Mark D Powell --

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,573 Red Diamond
    edited Feb 21, 2018 12:00AM

    Mark, I assume permission errors on the lsof command? Use netstat instead to get both client and server endpoints (IP:port) - and it also can show the process owner of an endpoint, for processes that you own.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,573 Red Diamond
    edited Feb 21, 2018 12:13AM

    Tagging along with Brian's response - a similar example, but showing how to get the endpoints used via either lsof or netstat - using dynamic port

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,573 Red Diamond
    edited Feb 21, 2018 12:18AM
    Catinredboots wrote:Please explain this "the app will communicate with that second user process on a completely different port. In fact, all processes get their own port." - as I'm not sure what you mean it will get a different port. I thought each time there is connection to database the port number will be the same as we have only one listener registered with one port. 

    It is easy to understand from a Berkeley sockets perspective:

    InternetSocketBasicDiagram_zhtw.png

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited Feb 21, 2018 11:06AM

    Billy, your assumption is correct I got permissions error with lsof.  It also appears that the netstat parameters may vary on AIX compared to the commands as listed in the referenced thread.  But thank you anyway.

  • Unknown
    edited Feb 21, 2018 1:26PM
    Sorry, but I'm still not seeing how one ties that to SERVICE_NAMES, so that app_svc_1 registers with app_listener_1 and app_svc_2 registers with app_listener_2. 

    Can you explain what it is you aren't 'seeing'?

    The DB is the entity that decides what listeners to register services with. Only the listeners it registers a service with (including the DB service)  will refer connection requests to that database/service.

  • EdStevens
    EdStevens Member Posts: 28,484 Gold Crown
    edited Feb 21, 2018 1:57PM
    rp0428 wrote:Sorry, but I'm still not seeing how one ties that to SERVICE_NAMES, so that app_svc_1 registers with app_listener_1 and app_svc_2 registers with app_listener_2. Can you explain what it is you aren't 'seeing'?The DB is the entity that decides what listeners to register services with. Only the listeners it registers a service with (including the DB service) will refer connection requests to that database/service.

    What I'm not seeing is how to register a specific service name to a specific listener.  So that given that db_1 has services dbsvc_1 and dbsvc_2, such that listener_1 knows only of dbsvc_1 and listener_2 know only of dbsvc_2.  In particular I've read and re-read several times the section "Registering Information with All Listeners in a Network" and find nothing about registering specific services.

This discussion has been closed.