Forum Stats

  • 3,732,979 Users
  • 2,246,664 Discussions
  • 7,856,452 Comments

Discussions

OCI behaviour of 12c dedicated server process to a client OCIBreak() call

Billy Verreynne
Billy Verreynne Member Posts: 28,257 Red Diamond

I've run into an issue with Oracle 12c dedicated (and shared) server processes that do not respond to the client (e.g. sqlplus), when receiving an OCI break packet.

Works fine with the same clients (sqlplus 11.2.0.3 and 12.2.0.1) against 11G database (11.2.0.4) processes.

In brief (Linux version):

- you send a long running server request to the Oracle server process, e.g. "select count(*) from dba_objects t1, dba_objects t2;" (cartesian join ensures slowness)

- you press Ctrl-C to interrupt this silly SQL statement's execution

- the Ctrl-C send a SIGINT (interrupt signal) to the client process

- the client OCI driver handles the signal and sends an OOB (out of band) TCP packet with ! as the payload, in order to interrupt the server process's execution

- the server process responds, via TCP packet(s), to the client

Example of a sqlplus process's strace (system tace), to an 11.2.0.4 database. File handle 0 is stdin (keyboard), and handle 4 is the TCP socket to the Oracle server process. Note that the SIGINT is caught, the ! OOB packet send, followed by reading (via the socket), the server's response. After the server's response, the client goes back to reading stdin (handle 0) for keyboard input.

read(0, "select count(*) from dba_objects"..., 1024) = 53write(4, "\0015\0\0\6\0\0\0\0\0\3^\31a\200\0\0\0\0\0\0\376\377\377\377\377\377\377\3773\0\0"..., 309) = 309read(4, 0x9707d6, 8208)                 = ? ERESTARTSYS (To be restarted if SA_RESTART is set)--- SIGINT {si_signo=SIGINT, si_code=SI_USER, si_pid=24646, si_uid=1000} ---rt_sigprocmask(SIG_BLOCK, [], NULL, 8)  = 0sendto(4, "!", 1, MSG_OOB, NULL, 0)     = 1rt_sigprocmask(SIG_UNBLOCK, [], NULL, 8) = 0rt_sigreturn({mask=[]})                 = 0read(4, "\0\v\0\0\f\4\0\0\1\0\2", 8208) = 11write(4, "\0\v\0\0\f\4\0\0\1\0\2", 11)  = 11read(4, "\0\311\0\0\6\0\0\0\0\0\4\1\0\0\0\27\0\1\0\0\0\0\365\3\0\0\0\0\3\0\25\0"..., 8208) = 201write(1, "select count(*) from dba_objects"..., 52) = 52write(1, " ", 1)                        = 1 - x repeats 20 timeswrite(1, "*\n", 2)                      = 2write(1, "ERROR at line 1:\n", 17)      = 17write(1, "ORA-01013: user requested cancel"..., 54) = 54write(1, "\n", 1)                       = 1write(1, "\n", 1)                       = 1write(1, "\n", 1)                       = 1write(1, "SQL> ", 5)                    = 5read(0, 

Now the exact same events (long SQL send, pressing Ctrl-C), using the same sqlplus client, but connected to a 12.1.0.2 database.

read(0, "select count(*) from dba_objects"..., 1024) = 53write(4, "\1<\0\0\6\0\0\0\0\0\21i\34\376\377\377\377\377\377\377\377\2\0\0\0\0\0\0\0\1\0\0"..., 316) = 316read(4, 0x123e876, 8208)                = ? ERESTARTSYS (To be restarted if SA_RESTART is set)--- SIGINT {si_signo=SIGINT, si_code=SI_USER, si_pid=24469, si_uid=1000} ---rt_sigprocmask(SIG_BLOCK, [], NULL, 8)  = 0sendto(4, "!", 1, MSG_OOB, NULL, 0)     = 1rt_sigprocmask(SIG_UNBLOCK, [], NULL, 8) = 0rt_sigreturn({mask=[]})                 = 0read(4, 

.. and the sqlplus client driver waits and waits for a TCP response (on handle 4), from the Oracle server process.

This behaviour of 12c server processes is very frustrating. The wait state for the server process servicing the sqlplus client, says:

event="SQL*Net message to client" params="driver id=1413697536,#bytes=1,p3=0"

Thus it seems that the server process is attempting to respond to the ! OOB packet from the client, but the client is not seeing the response via the TCP socket.

Anyone seen this, or can confirm this?

Any ideas what to try? Can client side sqlnet.ora settings (which works for 11c connections), be the problem for 12c connections?

Input appreciated.

This discussion has been closed.