The problem background

Oracle RAC Architecture , Restart the listeners of the two database nodes , Whether it will affect the established client connection ？

Experimental tests

There are two database nodes DB01、DB02, Corresponding SCAN-vip by x.x.x.34, And one with Oracle client Application server AP01. Suppose the database name is FLEXCOM, The user account and password are flex/xlef_101.

Establish a connection from the application server to the database . Sign in AP01 perform ：

su - oracle
sqlplus flex/[email protected]:1521/FLEXCOM

Login successful .

Stop the listeners of the two database nodes in turn ：

ssh $DB01_IP
su - grid
lsnrctl stop

ssh $DB02_IP
su - grid
lsnrctl stop

Test whether the existing connection is disconnected . go back to AP01 Initially established session , Trying to perform SQL sentence ：

show parameter session;
select grantee,privilege from dba_sys_privs where grantee='XXX';

SQL Statement executed successfully , It can be seen that stopping the listener has no effect on the established session .

Test whether a new connection can be established ：

su - oracle
sqlplus flex/[email protected]:1521/FLEXCOM
ERROR: ORA-12516: TNS:listener could not find available handler with matching protocol stack
Enter user-name:
Enter password:
ERROR: ORA-12162: TNS:net service name is incorrectly specified

so , After stopping the listener , Unable to establish new client connection .

Start the listeners of the two database nodes ：

ssh $DB01_IP
su - grid
lsnrctl start

ssh $DB02_IP
su - grid
lsnrctl start

Check the monitor status ：

lsnrctl status

To sum up, we can see that , When the client successfully connects to Oracle After the database , Restarting the listener does not interrupt the existing connection ; Just can't establish a new connection . therefore , Upgrade the database 、 During restart, etc , You need to stop the listener first , Then kill the existing session connection .

Error reporting analysis

After stopping the listener , An error occurred while trying to connect to the database , We received two error messages ：ORA-12516 and ORA-12162.

ORA-12516: TNS:listener could not find available handler with matching protocol stack
Cause: None of the known and available service handlers for the given SERVICE_NAME support the client’s protocol stack: transport, session, and presentation protocols.
Action: Check to make sure that the service handlers (e.g. dispatchers) for the given SERVICE_NAME are registered with the listener, are accepting connections, and that they are properly configured to support the desired protocols.

ORA-12516 It's because we stopped the listener of the database .

ORA-12162: TNS:net service name is incorrectly specified
Cause: The connect descriptor corresponding to the net service name in TNSNAMES.ORA or in the directory server (Oracle Internet Directory) is incorrectly specified.
Action: If using local naming make sure there are no syntax errors in the corresponding connect descriptor in the TNSNAMES.ORA file. If using directory naming check the information provided through the administration used for directory naming. If using ORACLE_SID, please check if ORACLE_SID is// set.

ORA-12612 Because we're not Oracle client Server configuration tnsnames.ora As a result of .

References
【1】Database error maessages