Wednesday, September 07, 2011

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.

No comments: