We have instance name db1 on the exadata server. This db is being monitored by platinum gateway as we have platinum support for exadata. Because we have done a lot of testing as poc and now its time to make it production i decided to drop and rebuild the database.
After i rebuilt it, In oem i started seeing lot of concurrency waits in brown color all i can get info from oem is library cache lock and most of the sessions having this lock are related to sys user.
So i want to find out whats actually causing the issue and generated a awr report and sure enough i found the same library cache lock here.
I ran ADDM report and it found the same concurrency wait event consuming 40% of time and it said might be related to shared pool size and there are no recommendations.
So i dig deep into AWR report and found a interesting wait event on top
connection management call elapsed time
Statistic Name Time (s) % of DB Time
connection management call elapsed time 19,275.19 99.37
Looks like most of it is because of this. So i decided some application is constantly trying to connect to the db and might be giving wrong password.
In our database if any user inputs incorrect password for 5 times account gets locked except for application and monitoring profile users. So i thought to start with dbsnmp user.
I just thought of checking dba_audit_trail and found orarom user ( which is a readonly account for platinum support) is contsantly trying to login and getting ORA-01017. I got this info from audit trail.
This is because when i created the new db i used a new password for dbsnmp. So i logged into platinum support gateway and changed the password. Immediately that wait event is gone.
After i rebuilt it, In oem i started seeing lot of concurrency waits in brown color all i can get info from oem is library cache lock and most of the sessions having this lock are related to sys user.
So i want to find out whats actually causing the issue and generated a awr report and sure enough i found the same library cache lock here.
I ran ADDM report and it found the same concurrency wait event consuming 40% of time and it said might be related to shared pool size and there are no recommendations.
So i dig deep into AWR report and found a interesting wait event on top
connection management call elapsed time
Statistic Name Time (s) % of DB Time
connection management call elapsed time 19,275.19 99.37
Looks like most of it is because of this. So i decided some application is constantly trying to connect to the db and might be giving wrong password.
In our database if any user inputs incorrect password for 5 times account gets locked except for application and monitoring profile users. So i thought to start with dbsnmp user.
I just thought of checking dba_audit_trail and found orarom user ( which is a readonly account for platinum support) is contsantly trying to login and getting ORA-01017. I got this info from audit trail.
This is because when i created the new db i used a new password for dbsnmp. So i logged into platinum support gateway and changed the password. Immediately that wait event is gone.