After installing Oracle client 18.104.22.168 (or upgrading to 22.214.171.124 client) on the desktop, the session manager connection stops working. When a user tries to connect to Session Manager, they see "Provider cannot be found error"
It happens to be an Oracle client install bug. It seems that when Oracle Client 126.96.36.199 is installed (or upgraded to), it fails to properly register the Oracle provider for OLE DB. It does appear as installed in the Oracle Universal Installer, however it does not show up in the list of available providers. This happens for 32 and 64 bit Oracle Provider for OLE DB. Since we use OLE DB connection for session manager.... you see where I am going with this, right?
To complete the installation of Oracle Provider for OLE DB, please complete the following steps.
1. Open the command prompt. If you are running on Windows 7 or Windows 2008 please make sure to run the command window with "Run As Administrator" privileges.
2. In the command window execute the following command to register the Oracle Provider for OLE DB. The following command is for 32 bit Oracle client on a 64 bit OS. (As ArcGIS desktop is a 32 bit application, you will be installing 32 bit oracle client... I know I do not have to say that, but I want to mention it here anyways)
where <Oracle32BitHome> is the physical path to the oracle home on the client.
The issue is also highlighted on the Oracle Support. If you wish to read more about it search for Oracle Metalink Doc ID 1380742.1 (your DBA will need Oracle support account to log in).