Re: strange tns listener problem..

On Oct 10, 3:36 am, Torfrid <t.k.l..._at_usit.uio.no> wrote:
> On Oct 10, 7:04 am, DJH <NOS..._at_NOSPAM.COM> wrote:
>
>
>
>
>
> > The Boss wrote:
> > > DJH 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
>
> > > Only last weekend, I had a similar experience:
> > > after a complete relocation of an Oracle DB server to new hardware, I also
> > > got ORA-12514's and the services not showing up on lsnrctl.
> > > Stopping/starting both the instance and the listener didn't help, so I tried
> > > "alter system register", without luck so it seemed.
> > > So I went searching the docs, Metalink, Google etc., and was very surprised
> > > getting a phone call (after about 15-20 minutes) from one of our customer
> > > representatives that all of a sudden he was able to remotely connect again
> > > ...
> > > I still have no clue what was going on and whether the "alter system
> > > register" was successful after all.
>
> > This is exactly the problem I had. I also am not runnning "lsnrctl start
> > " as root but as oracle. I only did a "stat" as root.
>
> > This problem is not caused by a "root" user issue as all oracle
> > processes are started as user "oracle" group "dba."
>
> > Thanks.
>
> We had a very similar problem a few weeks ago. We were told it was
> caused by (unpublished) bug 4518443 and fixed in 10.2.0.3.
> We have just upgraded to 10.2.0.3 and not seen the problem since
> (yet...)
> Torfrid- Hide quoted text -
>
> - Show quoted text -

Torfrid, thank you for posting the bug number. I have found on more than one occasion where being able to locate the bug number very helpful.

HTH -- Mark D Powell -- Received on Sun Oct 14 2007 - 14:34:06 CDT