oracle账号频繁锁定问题定位及分析过程

现象：
Enter password:
ERROR:
ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.

解决过程

查看锁定时间及锁定类别：

SQL> conn / as sysdba
Connected.
SQL> select username,to_char(lock_date,'yyyy/mm/dd hh24:mi:ss') as locktime,account_status from dba_users where username='***********';

USERNAME LOCKTIME
------------------------------ --------------------
ACCOUNT_STATUS
--------------------------------
*********** 2012/01/13 09:42:21
LOCKED(TIMED)

由此可知，该账号是由于错误登陆次数超过默认次数而锁定

账号解锁

SQL> alter user *********** account unlock;

User altered.

查看监听日志记录，确定账号被锁原因：

目录：$ORACLE_HME/network/log/

$ tail -20 listener.log

发现 大量以下信息

... ...

13-1月 -2012 09:44:48 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=m1hf)(CID=(PROGRAM=C:\Program?Files\utstxsec\utsdaemon.exe)(HOST=***********)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.37)(PORT=62083)) * establish * m1hf * 0
13-1月 -2012 09:44:48 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=m1hf)(CID=(PROGRAM=C:\Program?Files\utstxsec\utsdaemon.exe)(HOST=***********)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.37)(PORT=62084)) * establish * m1hf * 0
13-1月 -2012 09:44:52 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=m1hf)(CID=(PROGRAM=C:\Program?Files\utstxsec\utsdaemon.exe)(HOST=***********)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.37)(PORT=62086)) * establish * m1hf * 0
13-1月 -2012 09:44:52 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=m1hf)(CID=(PROGRAM=C:\Program?Files\utstxsec\utsdaemon.exe)(HOST=***********)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.37)(PORT=62087)) * establish * m1hf * 0

定位原因：
由于在使用 uts 数据同步软件后，修改了一次 *********** 账号的密码，而未修改其在前台应用接口的密码，使得周期调度的数据同步软件 ust 频繁以错误密码登陆，导致数据库用户*********** 频繁被锁。

解决办法：

1、将相关应用接口的密码修改（推荐采用）
2、修改用户 profile 默认失败登陆次数为无线

SQL> select * from dba_profiles where RESOURCE_NAME like 'FAILED_LOGIN_ATTEMPTS';

PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD
10

MONITORING_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD
UNLIMITED

修改为无线次失败登陆
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
对应将用户口令过期设置为无限期的sql如下：
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;