This discussion is archived
14 Replies Latest reply: Dec 28, 2012 4:30 AM by EdStevens RSS

How to open oracle listner for three database instances

user8643895 Newbie
Currently Being Moderated
please help me with following questions

I have 3 database instances

orcl
JIP02T
JIP04T
when i start the oracle database, does it start all 3 instances? when i start the oracle listner, does it start listner for all 3 instances?

when i echo $ORACLE_SID

[ora112@localhost admin]$ echo $ORACLE_SID
orcl
i can only see orcl and oracle listner is started only for one database instance.i can connect to only one database instance usign sql developer how can i start oracle database listner for other database instances as well
  • 1. Re: How to open oracle listner for three database instances
    Niket Kumar Pro
    Currently Being Moderated
    no,you need to start all instances one by one.

    you are using same listener for all three instances that's why you need to start only one listener.
  • 2. Re: How to open oracle listner for three database instances
    user8643895 Newbie
    Currently Being Moderated
    I actually I have configured 3 listners, but oracle starts only one listener when i type lsnrctl start

    this is how i have configured /opt/oracle/11.2.0/network/admin/listener.ora

    LISTENER_JIP04K =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1531))
    )

    SID_LIST_LISTENER_JIP04K =
    (SID_LIST =
    (SID_DESC =
    (GLOBAL_DBNAME = JIP04K)
    (ORACLE_HOME = /product/oracle/11.2.0)
    (SID_NAME = JIP04K)
    )
    )

    LISTENER_JIP02T =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1530))
    )

    ADR_BASE_LISTENER_JIP04K = /product/oracle

    SID_LIST_LISTENER_JIP02T =
    (SID_LIST =
    (SID_DESC =
    (GLOBAL_DBNAME = JIP02T)
    (ORACLE_HOME = /product/oracle/11.2.0)
    (SID_NAME = JIP02T)
    )
    )

    ADR_BASE_LISTENER_JIP02T = /product/oracle

    , but when i start the listner, it is giving me following information and database instance is started on port 1521, which is wrong port from what i have configured(1530)

    [ora112@localhost ~]$ lsnrctl start

    LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 26-DEC-2012 22:48:16

    Copyright (c) 1991, 2011, Oracle. All rights reserved.

    Starting /opt/oracle/11.2.0/bin/tnslsnr: please wait...

    TNSLSNR for Linux: Version 11.2.0.3.0 - Production
    System parameter file is /opt/oracle/11.2.0/network/admin/listener.ora
    Log messages written to /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
    Start Date 26-DEC-2012 22:48:16
    Uptime 0 days 0 hr. 0 min. 0 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /opt/oracle/11.2.0/network/admin/listener.ora
    Listener Log File /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
    The listener supports no services
    The command completed successfully

    Edited by: user8643895 on Dec 27, 2012 12:12 AM
  • 3. Re: How to open oracle listner for three database instances
    ShishirTekadeR Explorer
    Currently Being Moderated
    For multiple database in single server please create three different profile (like profile_orcl , profile_JIP02T, profile_JIP04T)
    then add all env in profile file

    ORACLE_BASE=
    ORACLE_SID=orcl
    ORACLE_HOME=

    for starting instance please run one by one profile

    then start the oracle + listener.

    lsnrctl start listener_orcl


    Best Regards,
    Shishir Tekade.
    My Blog: http://shishirtekade.blogspot.com
  • 4. Re: How to open oracle listner for three database instances
    ShishirTekadeR Explorer
    Currently Being Moderated
    please kill running listener and follow below steps:=

    ps -ef | grep tns

    kill -9 process ID

    then

    lsnrctl start LISTENER_JIP04K

    lsnrctl start LISTENER_JIP02T


    Best Regards,
    Shishir Tekade.
    My Blog: http://shishirtekade.blogspot.com
  • 5. Re: How to open oracle listner for three database instances
    user8643895 Newbie
    Currently Being Moderated
    Could you help me with how to create these profiles...
  • 6. Re: How to open oracle listner for three database instances
    user8643895 Newbie
    Currently Being Moderated
    I need some help with creating profiles
  • 7. Re: How to open oracle listner for three database instances
    ShishirTekadeR Explorer
    Currently Being Moderated
    please share below O/P

    env | grep ORA

    uname -a



    ORACLE_HOSTNAME=
    ORACLE_SID=
    ORACLE_BASE=
    ORACLE_HOME=
    PATH=$PATH:$ORACLE_HOME/bin:/usr/local/bin:$ORACLE_HOME/OPatch
    LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$$ORACLE_HOME/lib32:$ORACLE_HOME/lib


    *****************************************
    Best Regards,
    Shishir Tekade.
    My Blog: http://shishirtekade.blogspot.com
  • 8. Re: How to open oracle listner for three database instances
    EdStevens Guru
    Currently Being Moderated
    user8643895 wrote:
    please help me with following questions

    I have 3 database instances

    orcl
    JIP02T
    JIP04T
    when i start the oracle database, does it start all 3 instances? when i start the oracle listner, does it start listner for all 3 instances?

    when i echo $ORACLE_SID

    [ora112@localhost admin]$ echo $ORACLE_SID
    orcl
    i can only see orcl and oracle listner is started only for one database instance.i can connect to only one database instance usign sql developer how can i start oracle database listner for other database instances as well
    One single listener, using the default name of LISTENER and listening on the default port of 1521 is quite capable of -- indeed, WAS DESIGNED TO -- service multiple database instances of multiple versions running from multiple ORACLE_HOMEs. The listener is nothing but a connection broker, very much like a telephone switchboard. Trying to have a listener for each database on a given server makes as much sense as the telephone company building and maintaining a separate dedicated switchboard for each customer.

    So for the listener the first thing you need to do is delete your listner.ora file. The listener will quite happily start with all default settings without it.
    Then start the listener with the simple command
    lsnrctl start
    When it first starts it will report 'supports no services'. Don't worry. That's simply because the database instances haven't yet had a chance to self-register with the listener.


    Next, as to starting the databases, yes, in general each one has to be started individually.
    You should have a file /etc/oratab that lists all of your databases on the server
    First, set the environment for a given instance. This includs setting PATH, ORACLE_BASE, ORACLE_HOME, and ORACLE_SID. Oracle provides a utility called oraenv to do that for you, based on what it finds in /etc/oratab. The syntax is
    . oraenv
    Note that is "dot" SPACE oraenv

    You will be prompted for the value of ORACLE_SID (one of the databases). It looks like this:
    oracle:orcl$ . oraenv
    ORACLE_SID = [orcl] ? dwsbx
    The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
    
    oracle:dwsbx$
    Once that is set, you then simply connect to an idle instance and start it.
    oracle:dwsbx$ sqlplus / as sysdba
    
    SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 27 07:12:33 2012
    
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
    Connected to an idle instance.
    
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area 1653518336 bytes
    Fixed Size                  2213896 bytes
    Variable Size             973080568 bytes
    Database Buffers          671088640 bytes
    Redo Buffers                7135232 bytes
    Database mounted.
    Database opened.
    SQL>
    When the database starts, it will register itself with the listener.

    repeat for each database.
    and google 'start oracle database on server startup'

    Edited by: EdStevens on Dec 27, 2012 7:13 AM
  • 9. Re: How to open oracle listner for three database instances
    EdStevens Guru
    Currently Being Moderated
    ShishirTekadeR wrote:
    For multiple database in single server please create three different profile (like profile_orcl , profile_JIP02T, profile_JIP04T)
    then add all env in profile file

    ORACLE_BASE=
    ORACLE_SID=orcl
    ORACLE_HOME=

    for starting instance please run one by one profile
    the oracle utility oraenv will do this for you. No need to create new 'profiles' unless you have additional requirements.
    then start the oracle + listener.

    lsnrctl start listener_orcl

    I advise against naming the listner as if it belongs to a paticuler database. You only need one listener regardless of how many databases you have running on the server. Trying to name the listener for a single database just complicates and confuses the situation.
    Best Regards,
    Shishir Tekade.
    My Blog: http://shishirtekade.blogspot.com
  • 10. Re: How to open oracle listner for three database instances
    EdStevens Guru
    Currently Being Moderated
    ShishirTekadeR wrote:
    please kill running listener and follow below steps:=

    ps -ef | grep tns

    kill -9 process ID

    then

    lsnrctl start LISTENER_JIP04K

    lsnrctl start LISTENER_JIP02T

    NO, NO, NO.

    The listener is just a connection broker, like a telephone switchboard. Creating a separate listener for each database is like the telephone company building and maintaining a separate switchboard for each customer. One single listener, using the default name of LISTENER and using the default port of 1521 is quite capable of -- indeed, WAS DESIGNED TO -- service multiple databases of multiple versions running from multiple ORACLE_HOMEs.
    Best Regards,
    Shishir Tekade.
    My Blog: http://shishirtekade.blogspot.com
  • 11. Re: How to open oracle listner for three database instances
    EdStevens Guru
    Currently Being Moderated
    user8643895 wrote:
    I need some help with creating profiles
    Patience, Grasshopper

    You posted this follow-up a mere ten minutes after your previous post.

    This forum is not a chat line, and it is not paid support.

    Everyone here has a job for which they are paid, and this forum is not it.

    No one is responsible for monitoring it and giving a quick response.

    Furthermore, it is a global forum. The person with the information you seek may very well live 20 time zones away from you and was going to bed just as you posted. He will not even see your post for several more hours.

    Your original post went up in the middle of the night for half the world.

    No one with the information you seek is deliberately withholding it until you sound sufficiently desperate.
  • 12. Re: How to open oracle listner for three database instances
    EdStevens Guru
    Currently Being Moderated
    user8643895 wrote:
    Could you help me with how to create these profiles...
    There is no need. That was bad advice. All you need to do is use the facilities oracle already provides. See my other post, regarding the use of 'oraenv' to set your environment.
  • 13. Re: How to open oracle listner for three database instances
    ShishirTekadeR Explorer
    Currently Being Moderated
    ------------------------------------------------------------------------------------------------------------------------
    . oraenv


    Note that is "dot" SPACE oraenv

    You will be prompted for the value of ORACLE_SID (one of the databases). It looks like this:
    oracle:orcl$ . oraenv
    ORACLE_SID = [orcl] ? dwsbx
    The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle

    oracle:dwsbx$
    -------------------------------------------------------------------------------------------------------------------------

    HIGHLY APPRECIATED U R IMP Tips!!!!!

    But it’s very difficult to remember & manually enter oracle SID & HOME details every time after run (. Oraenv)
    .profile creation is simple method and no need to remember SID & HOME details.(for multiple DB in one server)



    As per user information
    He is using different port number for different instance
    And also it is very simple method to manage three different DB in three different listener entry (for DB maintenance activity.)
    Single listener in multiple instance (if listener down all DB connection will affect)


    *****************************************
    Best Regards,
    Shishir Tekade.
    My Blog: http://shishirtekade.blogspot.com

    Edited by: ShishirTekadeR on Dec 27, 2012 10:29 PM

    Edited by: ShishirTekadeR on Dec 27, 2012 10:30 PM

    Edited by: ShishirTekadeR on Dec 27, 2012 10:31 PM
  • 14. Re: How to open oracle listner for three database instances
    EdStevens Guru
    Currently Being Moderated
    ShishirTekadeR wrote:
    ------------------------------------------------------------------------------------------------------------------------
    . oraenv


    Note that is "dot" SPACE oraenv

    You will be prompted for the value of ORACLE_SID (one of the databases). It looks like this:
    oracle:orcl$ . oraenv
    ORACLE_SID = [orcl] ? dwsbx
    The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle

    oracle:dwsbx$
    -------------------------------------------------------------------------------------------------------------------------

    HIGHLY APPRECIATED U R IMP Tips!!!!!

    But it’s very difficult to remember & manually enter oracle SID & HOME details every time after run (. Oraenv)
    .profile creation is simple method and no need to remember SID & HOME details.(for multiple DB in one server)
    You don't have to "remember & manually enter oracle SID & HOME details every time after run " oraenv sets all those details for you. All you have to remember is the name of the instance you want to work with. The same thing you'd have to remember if you if you created (as you suggested) three different "profiles" - actually three different shell scripts, each to set the environment for a specific instance.

    Yes, I do place "default" settings in .bash_profile. But with multiple databases the "default" settings are often not going to be what you need so you'll still have to run some process to change them. Either a home-grown script or oraenv. And either way you will have to remember the names of the instances you have to chose from. So in general I see no advantage of reinventing the wheel.


    >
    >
    >
    As per user information
    He is using different port number for different instance
    Which accomplishes absolutely nothing except extra configuration headaches. It accomplishes nothing for security. It accomplishes nothing for availability.
    And also it is very simple method to manage three different DB in three different listener entry (for DB maintenance activity.)
    Not as easy as managing one default listener. By far the most frequent listener configuration issue I see on this forum is when people try to configure one listener per database.
    Single listener in multiple instance (if listener down all DB connection will affect)
    And why would the listener be down? I've been working full time with Oracle since version 7.3 - somewhere around 1995. I've NEVER seen a listener fall over and die. I have seen many people on this forum whose db connections failed because they were trying (unsucessfully) to configure multiple listeners.

    All of my servers are running multiple databases. I have one test server that is running close to a dozen separate databases. One listener. I've NEVER had a situation where I thought to myself, "Gee, it would really help if I had created a separate server for each database"

    >
    >
    *****************************************
    Best Regards,
    Shishir Tekade.
    My Blog: http://shishirtekade.blogspot.com

    Edited by: ShishirTekadeR on Dec 27, 2012 10:29 PM

    Edited by: ShishirTekadeR on Dec 27, 2012 10:30 PM

    Edited by: ShishirTekadeR on Dec 27, 2012 10:31 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points