Forum Stats

  • 3,768,190 Users
  • 2,252,758 Discussions
  • 7,874,486 Comments

Discussions

Each schema on separate listener

245

Answers

  • Catinredboots
    Catinredboots Member Posts: 128
    edited Feb 20, 2018 10:05AM

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

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Feb 20, 2018 10:12AM
    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!

  • JuanM
    JuanM Member Posts: 2,155 Gold Trophy
    edited Feb 20, 2018 10:15AM
    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.

  • Joerg.Sobottka
    Joerg.Sobottka Senior Consultant and Oracle Ace Member Posts: 596 Bronze Trophy
    edited Feb 20, 2018 10:18AM

    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
  • EdStevens
    EdStevens Member Posts: 28,519 Gold Crown
    edited Feb 20, 2018 10:20AM
    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. 

  • JohnWatson2
    JohnWatson2 Member Posts: 4,327 Silver Crown
    edited Feb 20, 2018 10:22AM

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

  • EdStevens
    EdStevens Member Posts: 28,519 Gold Crown
    edited Feb 20, 2018 10:22AM
    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?

    John Thorton
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Feb 20, 2018 10:25AM
    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?

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited Feb 20, 2018 10:26AM

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

  • Catinredboots
    Catinredboots Member Posts: 128
    edited Feb 20, 2018 10:28AM

    Linux Red Hat 7, Oracle 12.1

This discussion has been closed.