14 Replies Latest reply: Dec 28, 2012 6:30 AM by EdStevens RSS

    How to open oracle listner for three database instances

    user8643895
      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
          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
            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
              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
                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
                  Could you help me with how to create these profiles...
                  • 6. Re: How to open oracle listner for three database instances
                    user8643895
                    I need some help with creating profiles
                    • 7. Re: How to open oracle listner for three database instances
                      ShishirTekadeR
                      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
                        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
                          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
                            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
                              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
                                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
                                  ------------------------------------------------------------------------------------------------------------------------
                                  . 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
                                    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