Forum Stats

  • 3,759,882 Users
  • 2,251,610 Discussions
  • 7,870,857 Comments

Discussions

Each schema on separate listener

124

Answers

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond
    edited Feb 20, 2018 11:31AM

    I'm going to provide an answer you haven't seen before in this thread...

    The "functionality" you seek is already there...built into the single Listener you have running right now. Ok...so its a bit of a stretch but let me explain.

    Let's assume your Listener is listening on port 1521. A connection request comes in to the database. The connection is established. At this point, Oracle has created a user process on the server. The app no longer talks to the Listener. It talks to this user process. And this user process is on its own port! If you have another connection to another schema, the app will communicate with that second user process on a completely different port. In fact, all processes get their own port.

    Cheers,
    Brian

  • EdStevens
    EdStevens Member Posts: 28,484 Gold Crown
    edited Feb 20, 2018 11:39AM
    joerg.sobottka wrote:EdStevens wrote:joerg.sobottka wrote:This is, what I am telling since the beginning - to use service names (in addition to the listeners with separate ports)... Please read all of my posts  And you can use in addition SYS_CONTEXT in a after-logon-trigger to round up. At best the relationship would be between the port and the service name registered by the database.Please post a working, reproducible demo. Remember, to meet the OP's requirement, you MUST use the listener_user1 listener to connect to user1. So your demo needs to include proof that listener_user1 rejected a connection for user2. Or event the database rejected a connection for user2 if it came through listener_user1.The SERVICE_NAME will reject the connection, NOT the listener, but you can use the listener to not allow an application to connect to the database at all... Unfortunately I can't test it, but it could be implemented like followed. I still believe it's some kind of stupid to do it that way, but it doesn't mean, you CAN'T realize it that way... so the listener.ora should look like:LISTENER_1 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521)) ))LISTENER_2 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1522)) ))LISTENER_3 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1523)) ))SID_LIST_LISTENER1 =
    (SID_LIST =
    (SID_DESC =
    (GLOBAL_DBNAME = mysid.com)
    (ORACLE_HOME =/u01/app/oracle/product/xxx)
    (SID_NAME = mysid)
    )
    )

    SID_LIST_LISTENER2 =
    (SID_LIST =
    (SID_DESC =
    (GLOBAL_DBNAME = mysid.com)
    (ORACLE_HOME =/u01/app/oracle/product/xxx)
    (SID_NAME = mysid)
    )
    )SID_LIST_LISTENER3 =
    (SID_LIST =
    (SID_DESC =
    (GLOBAL_DBNAME = mysid.com)
    (ORACLE_HOME =/u01/app/oracle/product/xxx)
    (SID_NAME = mysid)
    )
    )TNSNAMES.ORA on each application server:Application_1521 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = application_1521) ) )Application_1522 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1522)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = application_1522) ) )Application_1523 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1523)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = application_1523) ) )And then you make a trigger inside of the database like the following:CREATE OR REPLACE TRIGGER global_logon_trg AFTER logon ON DATABASE
    DECLARE
    p_session_user varchar2(64);
    p_service varchar2(64);
    BEGIN
    SELECT UPPER(SYS_CONTEXT('USERENV', 'SESSION_USER')) INTO p_session_user FROM DUAL;
    SELECT UPPER(SYS_CONTEXT('USERENV', 'SERVICE_NAME')) INTO p_service FROM DUAL; IF ((p_session_user <> p_service) THEN
    DBMS_SESSION.SET_IDENTIFIER('about to raise app_error..');
    RAISE_APPLICATION_ERROR(-20003,'You are not allowed to connect to the database');
    END IF;
    END;
    /

    So how do you get the one database to register a specific service on a specific listener?

    John Thorton
  • EdStevens
    EdStevens Member Posts: 28,484 Gold Crown
    edited Feb 20, 2018 11:42AM
    Catinredboots wrote:Application team would like to have different application server connect to a different port number per schema, it is for some kind of testing. Thanks everyone for you explanation, it looks like it is not possible and I will tell them know that they will need a separate database for each one. 

    "Application team would like . . . "

    I'd like World Peace.

    Maybe the application team should explain in more detail why they "would like" and what they think it will accomplish - beyond "some kind of testing".

    I agree with John .. "Application team would benefit from some serious (re)training!"

    John ThortonBPeaslandDBA
  • Catinredboots
    Catinredboots Member Posts: 128
    edited Feb 20, 2018 11:57AM

    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.

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

    Catinredboots, I have to agree with Ed and John.  Just reply to your applications team that after conferring with several experienced DBA's that their opinion was that this is a "stupid" request and that they should re-think their ideas based on how the listener hands off requests, the potential use of Oracle services, etc....

    - -

    IMHO -- Mark D Powell --

  • Catinredboots
    Catinredboots Member Posts: 128
    edited Feb 20, 2018 12:08PM

    I completely agree with you guys as this is a "stupid" request as I never faced this question before and that is why I was asking this q in the forums here. FYI they didn't talked with any DBAs prior to suggesting this approach.

  • JohnWatson2
    JohnWatson2 Member Posts: 4,317 Silver Crown
    edited Feb 20, 2018 12:12PM
    Catinredboots wrote:I completely agree with you guys as this is a "stupid" request as I never faced this question before and that is why I was asking this q in the forums here. FYI they didn't talked with any DBAs prior to suggesting this approach. 

    Just do it, man. If your client asks you to do something that won't do any harm, what's the problem with complying with the request? Arguing about it is the sort of thing that leads people to hate their DBA.

    ps - I have to desubscribe from this, far too many mails. The last one from me 

  • jgarry
    jgarry Member Posts: 13,842
    edited Feb 20, 2018 12:30PM
    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. 

    That's the normal way things work.  The listener is a broker - it receives a request for a service on the port it is listening on, then negotiates with the db and the client for the client to work through a different port.  That normally only takes a brief time, then the listener goes on and services other requests.  In the case of shared server, there are dispatcher processes the client can connect to.

    You can declare service names and divide apps up by that.  I'm guessing this is what your app team was trying to say.  Somewhere in the docs it explains the details on how to do this, and so manage the services people can connect to.

    Description of Figure 1-6 follows

  • Unknown
    edited Feb 20, 2018 1:07PM

    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.
  • jgarry
    jgarry Member Posts: 13,842
    edited Feb 20, 2018 1:17PM

    Ah thanks, that example 9-4 was what I had in mind, but didn't have time to search for.

This discussion has been closed.