Listener error

All posts relating to Oracle database administration.

Moderator: Tim...

Listener error

Postby jnrpeardba » Fri Dec 20, 2013 10:54 am

Morning Tim,

A strange one to me this morning

I have a server with 2 db's and x2 listeners. And yet I have one of the db's attempting to use another one's listener.

db's
LNCBCD
LEMHD

Listener.ora entries

Code: Select all
SID_LIST_LISTENER_LNCBCD =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = LNCBCD)
      (ORACLE_HOME = /u01/sq/ora_1/db/11.2.0.3)
    )
)


LISTENER_LNCBCD =
  (DESCRIPTION_LIST =
    (DESCRIPTION = (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = htse-lkncbc-live-wk.systems.uk.hsbc)(PORT = 1523))
        (ADDRESS = (PROTOCOL = IPC)(KEY = REGISTER))

    )
  )
)

SID_LIST_LISTENER_LEMHD =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = LEMHD)
      (ORACLE_HOME = /u01/sq/ora_1/db/11.2.0.3)
    )
)


LISTENER_LEMHD =
  (DESCRIPTION_LIST =
    (DESCRIPTION = (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = htse-lkemh-live-wk.systems.uk.hsbc)(PORT = 1524))
        (ADDRESS = (PROTOCOL = IPC)(KEY = REGISTER))

    )
  )
)


status of listsners

Code: Select all
gbl04958_dummy /home/oracle # lsnrctl status LISTENER_LEMHD

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-DEC-2013 10:37:33

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=htse-lkemh-live-wk.systems.uk.hsbc)(PORT=1524)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=REGISTER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_LNCBCD
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                20-DEC-2013 10:18:44
Uptime                    0 days 0 hr. 18 min. 49 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/sq/ora_1/db/11.2.0.3/network/admin/listener.ora
Listener Log File         /u01/sq/ora_1/db/11.2.0.3/log/diag/tnslsnr/gbl04958/listener_lncbcd/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=128.164.154.234)(PORT=1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=REGISTER)))
Services Summary...
Service "LNCBCD" has 1 instance(s).
  Instance "LNCBCD", status UNKNOWN, has 1 handler(s) for this service...
Service "LNCBCD.systems.uk.hsbc" has 1 instance(s).
  Instance "LNCBCD", status READY, has 1 handler(s) for this service...
The command completed successfully


gbl04958_dummy /home/oracle # lsnrctl status LISTENER_LNCBCD

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-DEC-2013 10:38:25

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=htse-lkncbc-live-wk.systems.uk.hsbc)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_LNCBCD
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                20-DEC-2013 10:18:44
Uptime                    0 days 0 hr. 19 min. 41 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/sq/ora_1/db/11.2.0.3/network/admin/listener.ora
Listener Log File         /u01/sq/ora_1/db/11.2.0.3/log/diag/tnslsnr/gbl04958/listener_lncbcd/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=128.164.154.234)(PORT=1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=REGISTER)))
Services Summary...
Service "LNCBCD" has 1 instance(s).
  Instance "LNCBCD", status UNKNOWN, has 1 handler(s) for this service...
Service "LNCBCD.systems.uk.hsbc" has 1 instance(s).
  Instance "LNCBCD", status READY, has 1 handler(s) for this service...
The command completed successfully
gbl04958_dummy /home/oracle #


Why is the listsner LEMHD trying to use the details of the listsner and port belonging to LNCBCD? I've looked at tnsnames and listener.ora files and cannot spot the issue causing the error

if you can shed a light, much appreciated,

Jnrpeardba
jnrpeardba
Advisor
 
Posts: 392
Joined: Wed May 04, 2011 3:14 pm

Re: Listener error

Postby Tim... » Fri Dec 20, 2013 11:18 am

Hi.

The instance should have the LOCAL_LISTENER parameter set to the listener alias definition in the tnsnames.ora file. For example, the tnsnames.ora shouls contain.

Code: Select all
LISTENER_LNCBCD =
  (ADDRESS = (PROTOCOL = TCP)(HOST = htse-lkemh-live-wk.systems.uk.hsbc)(PORT = 1523))

LISTENER_LEMHD =
  (ADDRESS = (PROTOCOL = TCP)(HOST = htse-lkemh-live-wk.systems.uk.hsbc)(PORT = 1524))


Then, the LOCAL_LISTENER parameter in each instance needs to be set.

Code: Select all
-- On LNCBCD
LOCAL_LISTENER=LISTENER_LNCBCD

-- On LEMHD
LOCAL_LISTENER=LISTENER_LEMHD


This way, the instances are explicitly told which listener to use.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Listener error

Postby jnrpeardba » Fri Dec 20, 2013 12:25 pm

Thanks Tim,

not come across that before, but I modified my file and low and behold it works fine.

Thanks again for your quick response

Jnrpeardba
jnrpeardba
Advisor
 
Posts: 392
Joined: Wed May 04, 2011 3:14 pm

Re: Listener error

Postby Tim... » Fri Dec 20, 2013 4:40 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 6 guests