Re: strange tns listener problem..

On Oct 9, 3:49 pm, DJH <NOS..._at_NOSPAM.COM> wrote:
> I am having a strange situation where the service seems to disappear
> from the tns listener "registry". Is there a way to force oracle to
> re-register with the listener?
>
> In the following examples you will see "cu" appear initially then
> disappear in the next "stat."
>
> also if you try to connect this is what you get:
>
> xxwww:15:44:/root> sqlplus xxx/xxx_at_cu
>
> ERROR:
> ORA-12514: TNS:listener does not currently know of service requested in
> connect
> descriptor
>
> Enter user-name:
> ERROR:
> ORA-12154: TNS:could not resolve the connect identifier specified
>
> Enter user-name:
> ERROR:
> ORA-12154: TNS:could not resolve the connect identifier specified
>
> SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
>
> Thanks for any help!!
>
> ------------
>
> [root_at_db1]~# lsnrctl stat
>
> LSNRCTL for Linux: Version 10.2.0.2.0 - Production on 09-OCT-2007 12:46:21
>
> Copyright (c) 1991, 2005, Oracle. All rights reserved.
>
> Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
> STATUS of the LISTENER
> ------------------------
> Alias LISTENER
> Version TNSLSNR for Linux: Version 10.2.0.2.0 - Production
> Start Date 09-OCT-2007 12:44:49
> Uptime 0 days 0 hr. 1 min. 31 sec
> Trace Level off
> Security ON: Local OS Authentication
> SNMP OFF
> Listener Parameter File
> /u01/app/oracle/product/primary/network/admin/listener.ora
> Listener Log File
> /u01/app/oracle/product/primary/network/log/listener.log
> Listening Endpoints Summary...
> (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
> (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db1.xxxx.com)(PORT=1521)))
> Services Summary...
> Service "PLSExtProc" has 1 instance(s).
> Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this
> service...
> Service "cu" has 1 instance(s).
> Instance "cu", status READY, has 1 handler(s) for this service...
> Service "cuXDB" has 1 instance(s).
> Instance "cu", status READY, has 1 handler(s) for this service...
> Service "cu_XPT" has 1 instance(s).
> Instance "cu", status READY, has 1 handler(s) for this service...
> The command completed successfully
>
> ####################################Then LATER cu disappears .....
> ####################################
>
> [root_at_db1]~# lsnrctl stat
>
> LSNRCTL for Linux: Version 10.2.0.2.0 - Production on 09-OCT-2007 15:02:48
>
> Copyright (c) 1991, 2005, Oracle. All rights reserved.
>
> Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
> STATUS of the LISTENER
> ------------------------
> Alias LISTENER
> Version TNSLSNR for Linux: Version 10.2.0.2.0 - Production
> Start Date 09-OCT-2007 12:44:49
> Uptime 0 days 2 hr. 17 min. 59 sec
> Trace Level off
> Security ON: Local OS Authentication
> SNMP OFF
> Listener Parameter File
> /u01/app/oracle/product/primary/network/admin/listener.ora
> Listener Log File
> /u01/app/oracle/product/primary/network/log/listener.log
> Listening Endpoints Summary...
> (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
> (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db1.xxxx.com)(PORT=1521)))
> Services Summary...
> Service "PLSExtProc" has 1 instance(s).
> Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this
> service...
> The command completed successfully

I have not heard of this problem but what I would suggest is that if you are relying on the database to register the service with the listener rather than coding entries in the listerner.ora file that you consider hard coding the services into the listerner.ora file. This should work around whatever issue is causing the services to get dropped.

A search of metalink for any known bug that results in this issue would also be a worth a try.

IMHO -- Mark D Powell -- Received on Tue Oct 09 2007 - 15:03:26 CDT