Day: January 12, 2023

Oracle | Single listener for multiple versions of database

On By windows7bugsIn Oracle database 11g, Oracle Database 19c, WindowsLeave a comment

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 19.0.0.0.0 - Production on Thu Jan 12 12:45:30 2023
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0
PS D:\Oracle19c> sqlplus system@SCTCDB

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 12 12:45:34 2023
Version 19.14.0.0.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 ;)