In our EBS R12/ 11g database we have seen that apps database user login from SQLPLUS hangs after refresh.
Symptom:
During refresh process, after database recover/open and starting the database listener, apps login starts to hang. adconfig/adcfgclone.pl also hangs as it uses apps to connect to database
On checking alert log, there were continuous “inbound connection timed out (ORA-3136)” error.
On stopping the database listener, apps login was working fine.
Explanation:
As soon as the database listener is started after the database recover/open, database contention increased to very high value because of library cache locks (can be seen from OEM or database scripts). Since database is just opened and still a replica of Production with all production passwords so all the integrated applications were trying to reach the target database through LISTENER and because of wrong passwords the connections were failing but connection contention increased.
Workaround:
Set the following event in the init file:
EVENT="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1"
This will disable the logon delay.
Bounce the database and It should work fine after that.
You MUST remove this parameter after apps password change step.
Reference:
Bug 7715339 – Logon failures causes “row cache lock” waits – Allow disable of logon delay [ID 7715339.8]
Document:7715339.8 Logon failures causes “row cache lock” waits – Allow disable of logon delay
Note: In 11.1.0.7, if One off fix Patch 7715339 is applied, the delay is disabled unconditionally, and there is no need to set the event.
I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
Latest posts by Brijesh Gogia (see all)
- Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2021
- Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2021
- Oracle Multitenant DB 2 : Benefits of the Multitenant Architecture - March 19, 2021