Oracle Application R12 12.2 one user cannot logon

We are using Oracle EBS R12 for last 12 years and recently upgraded to 12.2.10. We hardly had much technical issues with the instance until recent days, then came a real annoyance.

One particular user was unable to logon. Instead of OA_HTML page, he was either getting redirected to logout page or the OA_HTML page keeps on trying to load. One of the workarounds we came across was, resetting his password. Once the password changed, he was taken to setup password page and to regular landing page. Well, this also turned into troublesome as each time his session expired, we were forced to reset his password.

So what went wrong? We had an issue with concurrent manager outputs and logs opening into a blank page recently. This happened after RMAN backup up, that is scheduled and we had to run autoconfig to fix this issue. Although autoconfig fixed that issue, opened issues with user logon, as explained above. We kept on watching the alerts for possible errors and there were none. Anyhow, we decided to do the following as the ONLY one relevant document we were able to find on Oracle support was “Unable To Login With One User On Oracle EBS Application (Doc ID 2480008.1)” and the case described was not our case.

This is what finally worked:

  • Cleared global cache (didn’t fix issues)
  • Shutdown Apps tier
  • Ran autoconfig on database Tier
  • Ran autoconfig on both Run and Patch file systems
  • Restarted Apps tier and the issues were resolved

We really don’t know what happened after the RMAN & we didn’t update/altered the OS (Oracle Linux 7). Well, things could go south once in a while, after all Oracle EBS is such a huge and complex repository!

Oracle | Single listener for multiple versions of database

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 ;)