But now I am more convinced that it is good to set this setting also for applications.
Lately I was called to look at a database, which was meant to be used in series of application tests. The main problem was that the performance was so poor that it was ridiculous to try to run with any load at all. After a longer while I logged on and the first observation was that a lot of sessions waited on library cache lock. There was no sql_id for those sessions and the set of those waiting sessions continuously (though slowly) changed - I mean here new sids. No other activity actually.
After further investigation I realize what were those sessions with no sql_id - I assumed these were connection tries from an application server with wrong password set. In order to be sure I set another profile for all the accounts and after a while all problems have gone and the database returned to usable state.
So what really happened was the following:
- there were few hosts with application servers on board with some applications and the database - all those nodes created an integrated test environment
- there was a request for the database refresh and new password were applied
- passwords were changed to the new values, but partially - for 2 kinds of data sources (i.e. connecting to 2 schemas) there were old passwords left intact
- with start of application servers and applications deployed on them there show the connections to the database
- after a while the following situation established - there were so many the connection tries with wrong password that the library cache lock was continuously grabbed by one of those tries apparently in order to check password
- when I enabled the new profile with FAILED_LOGIN_ATTEMPTS set to 10, the 2 accounts were locked, but the rest of the database was available
No comments:
Post a Comment