8 Replies Latest reply: Feb 8, 2013 6:44 AM by EdStevens RSS

    How To Create Listner.ora for 3databases on single system?

    K. Varun
      Hello Forum mates

      I have installed Oracle 10g for practice purpose on my System(oel-4).

      i have created two databases manually(aprod,sam) and one with DBCA (dprod).

      my doubt is do we need separate listener.ora file each database?

      If Not what should be the SID_NAME= ?

      GLOBAL_DBNAME=?

      PORT=?
      etc,etc....

      Thank You!
        • 1. Re: How To Create Listner.ora for 3databases on single system?
          EdStevens
          K. Varun wrote:
          Hello Forum mates

          I have installed Oracle 10g
          A version that is out of support. Why not use 11g?
          for practice purpose on my System(oel-4).
          An obsolete version of OL. Why not use the latest version of 5.x, or 6.x?
          >
          i have created two databases manually(aprod,sam) and one with DBCA (dprod).

          my doubt is do we need separate listener.ora file each database?
          One single listener, with the default name of 'listener', listening on the default port of 1521, is quite capable of -- indeed, was designed to -- service multiple database instances of multiple versions runnning from multiple oracle homes.
          >
          If Not what should be the SID_NAME= ?
          The listener doesn't care.
          >
          GLOBAL_DBNAME=?
          The listener doesn't care

          >
          PORT=?
          etc,etc....

          Thank You!
          read: [url http://edstevensdba.wordpress.com/2011/02/09/sqlnet_overview/] Help! I can’t connect to my database
          read: [url http://edstevensdba.wordpress.com/2011/02/16/sqlnet_client_cfg/] Help! I can’t connect to my database (part duex)
          read: [url http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/] ora-12154tns-03505 
          • 2. Re: How To Create Listner.ora for 3databases on single system?
            sb92075
            K. Varun wrote:
            Hello Forum mates

            I have installed Oracle 10g for practice purpose on my System(oel-4).

            i have created two databases manually(aprod,sam) and one with DBCA (dprod).

            my doubt is do we need separate listener.ora file each database?
            NO

            use only single listener.

            move, remove, rename, delete any existing listener.ora file.

            no listener.ora file is required to start or use single listener.

            lsnrctl start
            -- wait 60+ seconds then do as below
            lsnrctl status
            lsnrctl service

            COPY commands & results then PASTE all back here
            • 3. Re: How To Create Listner.ora for 3databases on single system?
              Salman Qureshi
              my doubt is do we need separate listener.ora file each database?
              No, infact you need to create only one listener and start it (for efault listener, you don't need to have any entry in listener.ora file, just go on command line and start the listener using "lsnrctl start" command)
              Default listener listens on 1521 port and each database running on this server will automatically register itself with default listener.
              If Not what should be the SID_NAME= ?
              To specify the instance name for static registration.
              GLOBAL_DBNAME=?
              Used for static registration. This is used if you are not using default listener (which does not listen to port 1521 or if you are not using local_listener parameter at database level for the non default listener) to register the instance with the listener.

              Probably you need to read the detail to understand many thing, please see bellow document.

              http://docs.oracle.com/cd/B14117_01/network.101/b10776/listener.htm

              Salman
              • 4. Re: How To Create Listner.ora for 3databases on single system?
                K. Varun
                [oracle@srvr96 ~]$ lsnrctl start

                LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 06-FEB-2013 06:31:28

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

                Starting /oraeng/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

                TNSLSNR for Linux: Version 10.2.0.1.0 - Production
                Log messages written to /oraeng/app/oracle/product/10.2.0/db_1/network/log/listener.log
                Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srvr96.india.com)(PORT=1521)))

                Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
                STATUS of the LISTENER
                ------------------------
                Alias LISTENER
                Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
                Start Date 06-FEB-2013 06:31:28
                Uptime 0 days 0 hr. 0 min. 0 sec
                Trace Level off
                Security ON: Local OS Authentication
                SNMP OFF
                Listener Log File /oraeng/app/oracle/product/10.2.0/db_1/network/log/listener.log
                Listening Endpoints Summary...
                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srvr96.india.com)(PORT=1521)))
                The listener supports no services
                The command completed successfully



                [oracle@srvr96 ~]$ lsnrctl status

                LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 06-FEB-2013 06:33:41

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

                Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
                STATUS of the LISTENER
                ------------------------
                Alias LISTENER
                Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
                Start Date 06-FEB-2013 06:31:28
                Uptime 0 days 0 hr. 2 min. 12 sec
                Trace Level off
                Security ON: Local OS Authentication
                SNMP OFF
                Listener Log File /oraeng/app/oracle/product/10.2.0/db_1/network/log/listener.log
                Listening Endpoints Summary...
                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srvr96.india.com)(PORT=1521)))
                Services Summary...
                Service "aprod" has 1 instance(s).
                Instance "aprod", status READY, has 1 handler(s) for this service...
                Service "aprod_XPT" has 1 instance(s).
                Instance "aprod", status READY, has 1 handler(s) for this service...
                Service "dprod.world.com" has 1 instance(s).
                Instance "dprod", status READY, has 1 handler(s) for this service...
                Service "dprodXDB.world.com" has 1 instance(s).
                Instance "dprod", status READY, has 1 handler(s) for this service...
                Service "dprod_XPT.world.com" has 1 instance(s).
                Instance "dprod", status READY, has 1 handler(s) for this service...
                Service "sam" has 1 instance(s).
                Instance "sam", status READY, has 1 handler(s) for this service...
                Service "sam_XPT" has 1 instance(s).
                Instance "sam", status READY, has 1 handler(s) for this service...
                The command completed successfully



                [oracle@srvr96 ~]$ lsnrctl service

                LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 06-FEB-2013 06:34:16

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

                Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
                Services Summary...
                Service "aprod" has 1 instance(s).
                Instance "aprod", status READY, has 1 handler(s) for this service...
                Handler(s):
                "DEDICATED" established:0 refused:0 state:ready
                LOCAL SERVER
                Service "aprod_XPT" has 1 instance(s).
                Instance "aprod", status READY, has 1 handler(s) for this service...
                Handler(s):
                "DEDICATED" established:0 refused:0 state:ready
                LOCAL SERVER
                Service "dprod.world.com" has 1 instance(s).
                Instance "dprod", status READY, has 1 handler(s) for this service...
                Handler(s):
                "DEDICATED" established:0 refused:0 state:ready
                LOCAL SERVER
                Service "dprodXDB.world.com" has 1 instance(s).
                Instance "dprod", status READY, has 1 handler(s) for this service...
                Handler(s):
                "D000" established:0 refused:0 current:0 max:1022 state:ready
                DISPATCHER <machine: srvr96.india.com, pid: 7371>
                (ADDRESS=(PROTOCOL=tcp)(HOST=srvr96.india.com)(PORT=32776))
                Service "dprod_XPT.world.com" has 1 instance(s).
                Instance "dprod", status READY, has 1 handler(s) for this service...
                Handler(s):
                "DEDICATED" established:0 refused:0 state:ready
                LOCAL SERVER
                Service "sam" has 1 instance(s).
                Instance "sam", status READY, has 1 handler(s) for this service...
                Handler(s):
                "DEDICATED" established:0 refused:0 state:ready
                LOCAL SERVER
                Service "sam_XPT" has 1 instance(s).
                Instance "sam", status READY, has 1 handler(s) for this service...
                Handler(s):
                "DEDICATED" established:0 refused:0 state:ready
                LOCAL SERVER
                The command completed successfully
                • 5. Re: How To Create Listner.ora for 3databases on single system?
                  EdStevens
                  K. Varun wrote:
                  [oracle@srvr96 ~]$ lsnrctl start

                  LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 06-FEB-2013 06:31:28

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

                  Starting /oraeng/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

                  TNSLSNR for Linux: Version 10.2.0.1.0 - Production
                  Log messages written to /oraeng/app/oracle/product/10.2.0/db_1/network/log/listener.log
                  Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srvr96.india.com)(PORT=1521)))

                  Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
                  STATUS of the LISTENER
                  ------------------------
                  Alias LISTENER
                  Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
                  Start Date 06-FEB-2013 06:31:28
                  Uptime 0 days 0 hr. 0 min. 0 sec
                  Trace Level off
                  Security ON: Local OS Authentication
                  SNMP OFF
                  Listener Log File /oraeng/app/oracle/product/10.2.0/db_1/network/log/listener.log
                  Listening Endpoints Summary...
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srvr96.india.com)(PORT=1521)))
                  The listener supports no services
                  The command completed successfully



                  [oracle@srvr96 ~]$ lsnrctl status

                  LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 06-FEB-2013 06:33:41

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

                  Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
                  STATUS of the LISTENER
                  ------------------------
                  Alias LISTENER
                  Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
                  Start Date 06-FEB-2013 06:31:28
                  Uptime 0 days 0 hr. 2 min. 12 sec
                  Trace Level off
                  Security ON: Local OS Authentication
                  SNMP OFF
                  Listener Log File /oraeng/app/oracle/product/10.2.0/db_1/network/log/listener.log
                  Listening Endpoints Summary...
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srvr96.india.com)(PORT=1521)))
                  Services Summary...
                  Service "aprod" has 1 instance(s).
                  Instance "aprod", status READY, has 1 handler(s) for this service...
                  Service "aprod_XPT" has 1 instance(s).
                  Instance "aprod", status READY, has 1 handler(s) for this service...
                  Service "dprod.world.com" has 1 instance(s).
                  Instance "dprod", status READY, has 1 handler(s) for this service...
                  Service "dprodXDB.world.com" has 1 instance(s).
                  Instance "dprod", status READY, has 1 handler(s) for this service...
                  Service "dprod_XPT.world.com" has 1 instance(s).
                  Instance "dprod", status READY, has 1 handler(s) for this service...
                  Service "sam" has 1 instance(s).
                  Instance "sam", status READY, has 1 handler(s) for this service...
                  Service "sam_XPT" has 1 instance(s).
                  Instance "sam", status READY, has 1 handler(s) for this service...
                  The command completed successfully



                  [oracle@srvr96 ~]$ lsnrctl service

                  LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 06-FEB-2013 06:34:16

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

                  Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
                  Services Summary...
                  Service "aprod" has 1 instance(s).
                  Instance "aprod", status READY, has 1 handler(s) for this service...
                  Handler(s):
                  "DEDICATED" established:0 refused:0 state:ready
                  LOCAL SERVER
                  Service "aprod_XPT" has 1 instance(s).
                  Instance "aprod", status READY, has 1 handler(s) for this service...
                  Handler(s):
                  "DEDICATED" established:0 refused:0 state:ready
                  LOCAL SERVER
                  Service "dprod.world.com" has 1 instance(s).
                  Instance "dprod", status READY, has 1 handler(s) for this service...
                  Handler(s):
                  "DEDICATED" established:0 refused:0 state:ready
                  LOCAL SERVER
                  Service "dprodXDB.world.com" has 1 instance(s).
                  Instance "dprod", status READY, has 1 handler(s) for this service...
                  Handler(s):
                  "D000" established:0 refused:0 current:0 max:1022 state:ready
                  DISPATCHER <machine: srvr96.india.com, pid: 7371>
                  (ADDRESS=(PROTOCOL=tcp)(HOST=srvr96.india.com)(PORT=32776))
                  Service "dprod_XPT.world.com" has 1 instance(s).
                  Instance "dprod", status READY, has 1 handler(s) for this service...
                  Handler(s):
                  "DEDICATED" established:0 refused:0 state:ready
                  LOCAL SERVER
                  Service "sam" has 1 instance(s).
                  Instance "sam", status READY, has 1 handler(s) for this service...
                  Handler(s):
                  "DEDICATED" established:0 refused:0 state:ready
                  LOCAL SERVER
                  Service "sam_XPT" has 1 instance(s).
                  Instance "sam", status READY, has 1 handler(s) for this service...
                  Handler(s):
                  "DEDICATED" established:0 refused:0 state:ready
                  LOCAL SERVER
                  The command completed successfully
                  looks to me as if your default listener named LISTENER, on the default port of 1521, knows of three databases: aprod, dprod, and sam.
                  So what is the issue at this point?
                  • 6. Re: How To Create Listner.ora for 3databases on single system?
                    K. Varun
                    I want to create my own listner and tns name!
                    • 7. Re: How To Create Listner.ora for 3databases on single system?
                      Aman....
                      K. Varun wrote:
                      I want to create my own listner and tns name!
                      But why when the default is going to work fine for all the databases? The TNS names entry can be created using the GUI tool NETCA. So check that and create an entry using the default listener that you have.

                      Aman....
                      • 8. Re: How To Create Listner.ora for 3databases on single system?
                        EdStevens
                        K. Varun wrote:
                        I want to create my own listner
                        WHY?!?!?! Why do you want to create unnecessary complexity in your system.
                        and tns name!
                        Every client has its own tnsnames.ora, so I haven't a clue as to what you mean by "create my own ... tns name".

                        I suspect your "want" is due to a severe mis-understanding of how tns works and the relationship between client, listener, and database. I also strongly suspect you did not read the links I provided you earlier.

                        Edited by: EdStevens on Feb 8, 2013 6:44 AM