Dangers of Password Change – Wait Event: library cache lock

Database user password change is a pretty common task, right? In 99% of the time I’d say it’s even boring …
alter user xyz identified by ***;
and you are done.
But there is that 1% that can cause headaches. And this post will cover just that.

The client implemented a requirement that application schema passwords need to be changed at least once a year. I developed a process around the password change.
As a good DBA, I verified incoming database links, recreated all of them after the password change. Or so I thought. I missed one db link, and this one was coming back to bite me.

Later in the day, I received a call from one developer: the application log is filling up with error messages “invalid username/password” coming from this database link.
The application was connecting through a database link from dbs2 to dbs1, using the app_user (the one who’s password I just changed).

The fix is obvious: connect to dbs2 and recreate the database link with the correct password.

SQL>connect sysadm_user@dbs2
SQL>drop database link dbs1_link;
SQL>create database link dbs1_link connect app_user identified by new_password using ‘dbs1’;
SQL>select * from dual@dbs1_link;
ORA-03113 end-of-file on communication channel (after 60+ seconds of waiting)

What is going on here? Did the remote database just crashed?

Quickly I open up another session and connect with my own ID to dbs1
SQL>connect drbt/***@dbs1
Connected.

No it did not crash (Yay, I am relieved). I test the connection as app_user to dbs1

SQL>conn app_user/***
waiting…waiting…I start getting impatient here….waiting…Connected
It took about 60 seconds to connect.

Something is really wrong here. I test connection with my ID, it works great, connecting as the app_user it either times out or it takes 60 seconds.

I check the alert log of dbs1, there are definitely some timeout errors in the alert log:

TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0

I start searching on Google and Oracle Support site. I test one more time connecting as the app_user and this time it works. It connects easily within a fraction of a second to dbs1.
I test the database link, and it works too. Problem fixed.

The problem might be fixed, I just need to find out the cause of it, ’cause otherwise I can’t sleep.

This is what Enterprise Manager shows in the Top Activity.

The graph is all brown, which means concurrency. Drilling down on the session, noticed top sessions are all SYS user sessions waiting on library cache lock:

The ASH report for that time frame shows the following:

Top Phases of execution: Connection Management

Top Call Types: OAUTH:

Notice the count of these calls: 6587 within 20 minutes.

Oracle Support has the following Document ID 1309738.1 High ‘library cache lock’ Wait Time Due to Invalid Login Attempts and it explains the cause.
There are some bugs related to this issue, and also this is expected behaviour, there is an intentional wait after a wrong password authentication attempt. If needed, this wait can be disable, and not recommended.
If there would be only 10 -15 attempts to connect with the wrong username password, that would not cause a problem, however looking at the count of the calls, 6000+, that could make the system or application unusable.

Lessons learned:
-confirm that you do recreate all the database links (this was the step that I missed, I recreate all database links, except the one that was in use, of course).

Tip of the day for password changes:

Create a process to change the password and automate it, which includes and is not limited to the following:

-extract DDL of all database links that reference the user who’s password is changed (incoming db links)
-test db links prior to password change, to confirm functionality
-generate a rollback script for the password change, in case a rollback is required
-change user password
-test new password works
-confirm last password change date
-drop database links that reference the user who’s password is changed
-recreate database links with the new password
-test database links

What is your experience in changing passwords? If you would like to share with me, please leave a message in the comments section or email me! Looking forward to hear from you!
See you next time on DBA Paradise, the place where DBAs go to grow!