Each schema on separate listener

Joerg.Sobottka

JohnWatson2 wrote:Why would you run all those listeners? Just do this,listener=(description=(address_list=((address=(protocol=tcp)(host=qwe)(port=1521))(address=(protocol=tcp)(host=qwe)(port=1522))(address=(protocol=tcp)(host=qwe)(port=1523))))

@JohnWatson2

Because if it is for testing purposes you might want to stop ONE of the listeners as you may do changes on the schema in the database.So it's better to have different listeners with different ports. It still does not make sense at all, but...
It would be better to use services with the same listener and on one port, then you can start and stop the services depending on the Schema/application, kick out already connected sessions depending on the services, etc. It's easy to do an upgrade for one schema by stopping the corresponding servce while all other test-applications can still run. And you can do it using scripts inside of the database why the listener configuration is outside, and... and...

EdStevens

joerg.sobottka wrote:JohnWatson2 wrote:Why would you run all those listeners? Just do this,listener=(description=(address_list=((address=(protocol=tcp)(host=qwe)(port=1521))(address=(protocol=tcp)(host=qwe)(port=1522))(address=(protocol=tcp)(host=qwe)(port=1523))))JohnWatson2Because if it is for testing purposes you might want to stop ONE of the listeners as you may do changes on the schema in the database.So it's better to have different listeners with different ports. It still does not make sense at all, but... 
It would be better to use services with the same listener and on one port, then you can start and stop the services depending on the Schema/application, kick out already connected sessions depending on the services, etc. It's easy to do an upgrade for one schema by stopping the corresponding servce while all other test-applications can still run. And you can do it using scripts inside of the database why the listener configuration is outside, and... and...

But there is still zero relationship between the port and the schema. At best the relationship would be between the port and the service name registered by the database.

JuanM

JuanM,Why are you trying to teach the OP how to run with scissors?  And what you suggest still does not achieve her objective.Your proposed multiple listeners all service the same database.  There is nothing, zero, zilch, nada, null to prevent any user from using any of the listeners (on their individual ports) to connect to any schema in that database.  This is even worse, and more pointless than simply trying to have a different listener for each of multiple databases.

Ed, even if we are not agreed it doesn't mean that this cannot be implemented.

Normal users must not connected to production databases. then having just application users, this connection configuration must be implemented on application logic. each different user connection string could reach different listener.

I am currently wondering why the OP needs something like this but it is obvious that he/she is not interested in your good effort.

in summary, some people need to learn things at the hard/bad way.

that's s all

Joerg.Sobottka

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.

Rasheed_

You cannot register a specific schema with a specific pot numbered listener .

DB service will be registered the listener (So you cannot isolate a single schema access from a Listener that is already registered with DB )

EdStevens

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.

Joerg.Sobottka

Rasheed_ wrote:You cannot register a specific schema with a specific pot numbered listener .DB service will be registered the listener (So you cannot isolate a single schema access from a Listener that is already registered with DB )

You can isolate a single schema access using service names and an after logon trigger, e.g. if the name of the user you used to connect does not match the name of the service you needed to connect, you log off automatically. All you do need is to compare the two SYS_CONTEXT values...

EdStevens

joerg.sobottka wrote:Rasheed_ wrote:You cannot register a specific schema with a specific pot numbered listener .DB service will be registered the listener (So you cannot isolate a single schema access from a Listener that is already registered with DB )You can isolate a single schema access using service names and an after logon trigger, e.g. if the name of the user you used to connect does not match the name of the service you needed to connect, you log off automatically. All you do need is to compare the two SYS_CONTEXT values...

Well, that would not involve multiple listeners or multiple ports, but simply registering multiple services with the one single default listener.

That could be done, but would about as silly as the OP's proposal.

John Thorton

Catinredboots wrote: Hello,Is it posible to have each schema on separate listener so each schema will be accessed from different port?Please let me know if it is possible. Thanks!

What problem are you REALLY trying to solve?

Joerg.Sobottka

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;
/