Analytics Software

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!

Yes, it is possible to have multiple listeners running on a single DB Server.

It is also possible to poke yourself in the eye with a sharp pencil.

It is equally foolish to do either possible action.

Do you realize & understand that you can shutdown any Oracle listener & that there will be ZERO impact to existing connected Oracle sessions?

Oracle listener is only active to service the initial connection requests coming from remote client.

If you have a stable application that use Connection Pooling, no listener is really need after the Connection Pool has been initially established.

How do you limit a listener to  connecting  to a single schema?

What is the advantage of having a dedicated listener for each schema?

Is it posible to have each schema on separate listener so each schema will be accessed from different port?

No - listeners are used to connect to databases - not schemas.

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!

To be honest,  you have a very strange requirement.

In order to help you better, please explain what issue you have.

Not required , strange requirement.

Catinredboots wrote:
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.

Not possible.

And a clear indication that you are entirely unfamiliar with Oracle client-server architecture, and concepts. You should explain instead what you think the requirement or issue is, instead of asking how a broken and flawed solution can be crafted.

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!

As others have said, it is not possible, and not necessary.

And as other's have asked, why do you think you'd want to?

============================================================================

Please stay after school and write the following sentence 100 times:

One single listener, using the default name of LISTENER and the default port of 1521, is quite capable of -- indeed, WAS DESIGNED TO -- service multiple databases of multiple versions running from multiple homes.  Multiple listeners simply adds complications, and for no gain whatsoever.

============================================================================

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.

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 benefit from some serious (re)training!

Why not just have different schemas for testing isolation?

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. 

yes it is possible but does not have sense.

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. 

No problem with that. Configure your listener to listen on as many ports as necessary.

So it is possible and how would you do that? Is there official oracle documentation for this?

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. 

There is ZERO relationship between listener port# and any particular schema!

It is 100% daft to try to relate port# & schema name!

Catinredboots wrote:
So it is possible and how would you do that? Is there official oracle documentation for this? 

create as many listener you need. each one on different port. then on each application connection string put a different port in order to contact the desired listener.

Well, that's a strange requirement, as it really does not make any sense, because LOGICALLY you are still working with the same database etc.

The behaviour you WISH to have I think is

https://docs.oracle.com/cd/B28359_01/server.111/b28310/create007.htm#ADMIN11125

If you really wish to have different ports, just specify different listeners in the listener.ora:

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))

    )

)

and start them separately

lsnrctl start listener_1

lsnrctl start listener_2

lsnrctl start listener_3

JuanM wrote:
Catinredboots wrote:
So it is possible and how would you do that? Is there official oracle documentation for this? 
create as many listener you need. each one on different port. then on each application connection string put a different port in order to contact the desired listener. 

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.

============================================================================

Please stay after school and write the following sentence 100 times:

One single listener, using the default name of LISTENER and the default port of 1521, is quite capable of -- indeed, WAS DESIGNED TO -- service multiple databases of multiple versions running from multiple homes.  Multiple listeners simply adds complications, and for no gain whatsoever.

============================================================================

And since I expected better of you, after you have finished, clean the blackboard and do it again. 

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))))

Catinredboots wrote:
So it is possible and how would you do that? Is there official oracle documentation for this? 

In spite of what JuanM said, it is not possible.

Why are you avoiding the obvious and repeated question of why you want to do this and what you think it will achieve?

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))))

how does above meet OP's requirement below for separate schema on each port?

>Is it possible to have each schema on separate listener so each schema will be accessed from different port?

Catinredboots, what your developers seem to want is not actually possible nor would it be beneficial.  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.

- -

What platform are you on (OS version) and what full version of Oracle is in use?

- -

HTH -- Mark D Powell --

Linux Red Hat 7, Oracle 12.1

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...

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))))
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...

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,
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

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.

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 )

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.

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...

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.

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?

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

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

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?

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!"

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.

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

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.

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 

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.

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.

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

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. 

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

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

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

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.

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

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:

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.

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.

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.