Re: strange tns listener problem..

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.

-- 
Jeroen