Do you have any locked accounts that are being attempted to be logged in
to? OR, heavy attempts with invalid passwords?
There's a known issue where sessions that bang away at locked accounts (or
with invalid passwords) cause a basic DoS -like scenario where no users can
login.
You may want to turn on login audits to see if you're getting a lot of
failed logins for a particular account.
As far as I know this problem still exists in 12c.
High 'library cache lock' Wait Time Due to Invalid Login Attempts (Doc ID
1309738.1)
Chris
On Tue, May 29, 2018 at 9:33 AM, Chris Stephens <cstephens16@xxxxxxxxx>
wrote:
Oracle 12.2 on CentOS 7.
we recently upgraded one of our databases from 12.1 to 12.2 on centos 7.
we have an application that logs on and logs off for every database
operation regardless of size. that results in an obscenely high
connection/disconnection rate which eventually (after several minutes)
results in all logins to the application account hanging indefinitely on
"library cache lock". there is no lock holder. the session is attempting
to update last logon column in sys.user$ (one of the spare columns).
we are working to address the application issue properly but have
implemented database resident connection pooling to temporarily alleviate
the issue. we thought that had the issue licked but it happened again on
Friday when connection pool was in place. we think there were several
sessions created outside of the pool by this account earlier in the day or
the day before but i can't see how that would contribute to the problem.
we have an SR open with Oracle and have provided systemstate and
hanganalyze output to them. looking at the hang analyze shows blocked
sessions waiting on the previously mentioned "library cache lock" but
"blocking:
0 sessions".
has anyone seen this before?
any suggestions on how to work around the issue until we get the
application code changed to reuse sessions?
