Environment: Windows OS (Windows 11)
Currently I’ve 2 different versions of Oracle database installed & instances running from my development machine. Oracle 11G R2 (11.20.4) and Oracle 19c (19.14) & yes, we are supported by Oracle. I had 11G installed first & later installed 19c for checking out APEX and ORDS.
As 11G listener was running, I didn’t notice anything problematic until, tried to connect as a specific user. I started getting the error that the listener doesn’t know of the service name.
PS D:\Oracle19c> sqlplus / as sysdba SQL*Plus: Release 18.104.22.168.0 - Production on Thu Jan 12 12:45:30 2023 Version 22.214.171.124.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 126.96.36.199.0 - Production Version 188.8.131.52.0 SQL> quit Disconnected from Oracle Database 19c Enterprise Edition Release 184.108.40.206.0 - Production Version 220.127.116.11.0 PS D:\Oracle19c> sqlplus system@SCTCDB SQL*Plus: Release 18.104.22.168.0 - Production on Thu Jan 12 12:45:34 2023 Version 22.214.171.124.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Enter password: ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Enter user-name:
I went ahead and using netca registered the 19c service with 11g listener, restarted the listener services and this time instead of complaining about the missing service, connection attempt froze.
After some confusion, I decided to drop the 11g listener services and created a new listener from 19c environment.
Registered both 19c & 11g services using 19c NETCA this time & restarted the listener. I was able to connect to both databases using username/password@servicename syntax this time.
So let us see what went wrong. 11g database was configured for connections from Oracle Developer 6i legacy applications.
This cannot be modified without failing the legacy application. Please refer the below to understand how the authentication works with different versions of databases and what has changed.
Check for the SQLNET.ALLOWED_LOGON_VERSION Parameter Behavior (oracle.com)
As I am not allowed to change 11g environment, the ONLY one way I can use the same listener was to register 11g service with 19c listener. Simple, clean and effective. Hope this helps few out there who is not a certified DBA like me ;)