This discussion is archived
8 Replies Latest reply: Feb 8, 2013 4:44 AM by EdStevens RSS

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

K. Varun Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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?
    SalmanQureshi Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    [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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    I want to create my own listner and tns name!
  • 7. Re: How To Create Listner.ora for 3databases on single system?
    Aman.... Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

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