Each new Oracle Database release changes the client/server communication protocol. A newer protocol supports new features, strengthens security, and so forth. Before upgrading your Oracle Database, you must ensure your clients can connect afterward. If not, your clients may face:

ORA-28040: No matching authentication procotol error
ORA-03134: Connections to this server version are no longer supported

Oracle lists the supported clients in the client/server interoperability support matrix (Doc ID 207303.1). Take a look – it is probably the most colorful MOS note.

For Oracle Database 23c, your clients must be version:

• 23c
• 21c
• 19c

How do you know if your clients are ready for the next release?

A Solution

The view V$SESSION_CONNECT_INFO lists the client driver and version of connected sessions. You can join V$SESSION to get more details.

SELECT PROGRAM, CLIENT_DRIVER, CLIENT_VERSION 
FROM   GV$SESSION S, GV$SESSION_CONNECT_INFO CI 
WHERE  S.SID=CI.SID AND S.SERIAL#=CI.SERIAL# 

Here’s is an example of how that data might look like:

• If I want to upgrade to Oracle Database 23c, it looks good except for the emagent with a 12.1.0.2 driver.
• When client driver is SERVER it is the database itself making connections. In this case, the scheduler was running jobs. You can disregard those entries.
• The entry with client driver (null) was a thick OCI client used by a C++ program.

The above query gives an overview of the situation right now. But we need to persist the data to check all clients over time. We can solve that with a scheduler job.

If you monitor your database for weeks or a month, you should know which clients connect to your Oracle Database.

A high-five to my friend Frank for helping with some sample connection data.

The Code

Here’s a piece of code that creates a schema, a few objects, and a scheduler job.

The scheduler runs the job every 5 minutes. The job finds new connections, notes the client’s driver, and stores that info in a table.

Run the following code as SYS AS SYSDBA:

--Create a schema
DROP USER ORA_CLIENT_CHECK CASCADE;
CREATE USER ORA_CLIENT_CHECK NO AUTHENTICATION;

--Required privileges
GRANT SELECT ON GV_$SESSION TO ORA_CLIENT_CHECK;
GRANT SELECT ON GV_$SESSION_CONNECT_INFO TO ORA_CLIENT_CHECK;
ALTER USER ORA_CLIENT_CHECK DEFAULT TABLESPACE USERS;
ALTER USER ORA_CLIENT_CHECK QUOTA UNLIMITED ON USERS;

--View contains a current list of clients connected
CREATE VIEW ORA_CLIENT_CHECK.CURRENT_SESSIONS AS (
SELECT DISTINCT S.MACHINE, S.OSUSER, S.PROGRAM, S.MODULE, CLIENT_DRIVER, CLIENT_VERSION FROM GV$SESSION S, GV$SESSION_CONNECT_INFO CI WHERE S.SID=CI.SID AND S.SERIAL#=CI.SERIAL# AND CI.CLIENT_DRIVER != 'SERVER');

--Create a table to hold the result
CREATE TABLE ORA_CLIENT_CHECK.CONNECTED_SESSIONS AS SELECT * FROM ORA_CLIENT_CHECK.CURRENT_SESSIONS WHERE 1=0;

--Create a scheduler job that runs every 5 minutes
BEGIN
   DBMS_SCHEDULER.create_job (
      job_name        => '"ORA_CLIENT_CHECK"."GET_NEW_CONNECTIONS"',
      job_type        => 'PLSQL_BLOCK',
      job_action      => 'BEGIN MERGE INTO ORA_CLIENT_CHECK.CONNECTED_SESSIONS OLD USING (SELECT * FROM ORA_CLIENT_CHECK.CURRENT_SESSIONS) NEW ON (OLD.MACHINE=NEW.MACHINE AND OLD.OSUSER=NEW.OSUSER AND OLD.PROGRAM=NEW.PROGRAM AND OLD.MODULE=NEW.MODULE AND OLD.CLIENT_DRIVER=NEW.CLIENT_DRIVER AND OLD.CLIENT_VERSION=NEW.CLIENT_VERSION) WHEN NOT MATCHED THEN INSERT (MACHINE, OSUSER, PROGRAM, MODULE, CLIENT_DRIVER, CLIENT_VERSION) VALUES(NEW.MACHINE, NEW.OSUSER, NEW.PROGRAM, NEW.MODULE, NEW.CLIENT_DRIVER, NEW.CLIENT_VERSION); COMMIT; END;',
      start_date      => SYSTIMESTAMP,
      repeat_interval => 'freq=minutely; interval=5; bysecond=0;',
      end_date        => NULL,
      enabled         => TRUE,
      comments        => 'Checks for new connections and store the client version/driver to table.');
END;
/

Query the table to find clients that you must upgrade:

SELECT * 
FROM   ORA_CLIENT_CHECK.CURRENT_SESSIONS;

The Limitations

• The code monitors a single database only.

• If you use Active Data Guard, you must consider the users that connect to the standby only. You can use DML redirect to send the data back to the primary database.

• If you have a job that connects very seldom or connects for a very short time only, there is a risk that the code won’t capture it.

Other Ideas

Unified Auditing captures much information, but the client driver and version is not part of it. If you can’t sample from V$SESSION_CONNECT_INFO, this could be a viable solution.

You can use the below query to find a unique list of sessions and the program they used to connect. Then, you connect each host and manually ensure that the client driver is up-to-date:

SELECT DISTINCT userhost, program, client_program_name 
FROM   unified_audit_trail;

Of course, this requires you to have configured Unified Auditing.