Problem description
The 'icd' account has been locked.
Analysis progress
1. Confirmed how many accounts had been locked.
2. Made clear about the IP address of each server.
CTI/datastationserver: 99.xx.165.65 and 99.xx.165.66
CMSservice: 99.xx.165.212 (and 99.xx.165.65 is the same machine, configured withdual IP)
Databaseserver: 99.xx.165.68, named (icduat)
3. Checked the 'icd' user was locked fromwhich connected server
a) Log in as the dba user to unlock, first set thespecific time format to view the specific time.
SQL > alter session setnls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
b) View the specific locked time (specific username).
SQL> select username, lock_date fromdba_users where username='icd';
USERNAME LOCK_DATE
Icd 2009-03-10 00:51
c) Unlock
SQL > alter user test account unlock;
User altered.
d) Check which IP caused the user locked.
View the$ORACLE_HOME/network/admin/log/listener.log log
Sample result as below
10-MAR-2009 08:51:03 *(CONNECT_DATA=(SID=lhoms)(SERVER=DEDICATED)(CID=(PROGRAM=oracle)(HOST=omstestdb)(USER=oraoms))))(ADDRESS=(PROTOCOL =tcp)(HOST=10.xx.1.11)(PORT=49434)) *establish * lhoms * 0
10-MAR-2009 08:51:03 *(CONNECT_DATA=(SID=lhoms)(SERVER=DEDICATED)(CID=(PROGRAM=oracle)(HOST=omstestdb)(USER=oraoms))))(ADDRESS=(PROTOCOL =tcp)(HOST=10.xx.1.11)(PORT=49435)) *establish * lhoms * 0
This shows that the ip of 10.xx.1.11above is attempted to be locked by multiple failed logins.
Root cause
After checking, found that a DB Link was created but the password for the 'icd' interconnection didn't change.
Solution description
Changed the interconnection password and the 'icd' user can log in.
