Listener Failure During SQL*Plus Session

I received the following by E-mail recently (questions 1 to 3 have been removed and will be dealt with in separate posts):

 

Hi,

I need some help regards below queries
please find time to answer this below queries
Thanks for your support.

Q4) What if a user got connected and listener is restarted or listener fails what happens to

 

thanks and regards
Vijay

I thought I knew the answer but, as always, decided to check (the test was done on an Oracle 9 database). First I started a SQL*Plus client session as USER1:

SQL> conn user1/user1@test9

Connected.

SQL> select to_char(sysdate,'hh24:mi:ss') time_now

  2  from dual;

 

TIME_NOW

--------

17:41:56

 

SQL>

 

And another as USER2:

 

SQL> conn user2/user2@test9

Connected.

SQL> select to_char(sysdate,'hh24:mi:ss') time_now

  2  from dual;

 

TIME_NOW

--------

17:42:16

 

SQL>

 

Then I stopped the listener on the server hosting the database:

 

TEST9 > lsnrctl stop LISTENER

 

LSNRCTL for Compaq Tru64 UNIX: Version 9.2.0.7.0 - Production on 07-SEP-2011 17:48:59

 

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SERVER01)(PORT=1521)))

The command completed successfully

TEST9 >

 

Then, in the first client session, I tried running the same SQL:

 

SQL> l

  1  select to_char(sysdate,'hh24:mi:ss') time_now

  2* from dual

SQL> /

 

TIME_NOW

--------

17:49:31

 

SQL>

 

And, in the second client session, I tried to reconnect to the database:

 

SQL> conn user2/user2@test9

ERROR:

ORA-12541: TNS:no listener

 

Warning: You are no longer connected to ORACLE.

SQL>

 

Then I started the listener again (some of the output has been omitted):

 

TEST9 > lsnrctl start LISTENER

 

LSNRCTL for Compaq Tru64 UNIX: Version 9.2.0.7.0 - Production on 07-SEP-2011 17:50:12

 

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

 

Starting /oracle/app/oracle/product/9.2.7/bin/tnslsnr: please wait...

 

TNSLSNR for Compaq Tru64 UNIX: Version 9.2.0.7.0 - Production

System parameter file is /oracle/app/oracle/product/9.2.7/network/admin/listener.ora

Log messages written to /oracle/app/oracle/product/9.2.7/network/log/listener.log

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server01)(PORT=1521)))

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SERVER01)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Compaq Tru64 UNIX: Version 9.2.0.7.0 - Production

Start Date                07-SEP-2011 17:50:13

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  OFF

SNMP                      OFF

Listener Parameter File   /oracle/app/oracle/product/9.2.7/network/admin/listener.ora

Listener Log File         /oracle/app/oracle/product/9.2.7/network/log/listener.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server01)(PORT=1521)))

Services Summary...

Etc

Etc

Service "TEST9" has 1 instance(s).

  Instance "TEST9", status UNKNOWN, has 1 handler(s) for this service...

Etc

Etc

The command completed successfully

TEST9 >

 

Then I was interrupted but, a bit later, the first client session was still connected to the database:

 

SQL> show user

USER is "USER1"

SQL> l

  1  select to_char(sysdate,'hh24:mi:ss') time_now

  2* from dual

SQL> /

 

TIME_NOW

--------

18:25:24

 

SQL>

 

And the second session was able to reconnect:

 

SQL> conn user2/user2@test9

Connected.

SQL> l

  1  select to_char(sysdate,'hh24:mi:ss') time_now

  2* from dual

SQL> /

 

TIME_NOW

--------

18:26:20

 

SQL>

 

So the answer is that, if the listener goes down, existing client connections carry on working but you cannot start new ones.