oracle user account
1. select from dba_users table to see when the account was locked.
myuser@myuser] ./orcl_my.sh
Rerunning as Oracle
SQL*Plus: Release 10.2.0.2.0 - Production on Tue Mar 5 12:06:37 2013
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
SQL> startup
ORACLE instance started.
SQL>
SQL> set line 4000;
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select account_status, lock_date from dba_users where username = 'MYUSER1';
ACCOUNT_STATUS LOCK_DATE
-------------------------------- -------------------
LOCKED(TIMED) 2013-03-03 23:44:54
SQL> alter user MYUSER1 account unlock;
User altered.
SQL>
2. check the lister.log, according to the time, get the ip that caused the problem
myuser@myuser] cat /rhel5pdi/apollo/var/env/OracleEE/server/network/log/listener.log
03-MAR-2013 23:44:53 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.148)(PORT=39082)) * establish * orcl * 0
03-MAR-2013 23:44:53 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.148)(PORT=39083)) * establish * orcl * 0
03-MAR-2013 23:44:53 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.148)(PORT=39084)) * establish * orcl * 0
03-MAR-2013 23:44:53 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.148)(PORT=39085)) * establish * orcl * 0
03-MAR-2013 23:44:53 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.148)(PORT=39080)) * establish * orcl * 0
03-MAR-2013 23:44:53 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.148)(PORT=39086)) * establish * orcl * 0
03-MAR-2013 23:44:53 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.148)(PORT=39081)) * establish * orcl * 0
03-MAR-2013 23:44:53 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.148)(PORT=39079)) * establish * orcl * 0
03-MAR-2013 23:44:53 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.148)(PORT=39078)) * establish * orcl * 0
03-MAR-2013 23:44:53 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.148)(PORT=39077)) * establish * orcl * 0
03-MAR-2013 23:44:55 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.148)(PORT=39090)) * establish * orcl * 0
03-MAR-2013 23:44:55 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.148)(PORT=39089)) * establish * orcl * 0
03-MAR-2013 23:44:55 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.148)(PORT=39091)) * establish * orcl * 0
03-MAR-2013 23:44:55 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.148)(PORT=39092)) * establish * orcl * 0
03-MAR-2013 23:44:55 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.148)(PORT=39093)) * establish * orcl * 0
03-MAR-2013 23:44:55 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.148)(PORT=39095)) * establish * orcl * 0
03-MAR-2013 23:44:55 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.148)(PORT=39094)) * establish * orcl * 0
03-MAR-2013 23:44:55 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.148)(PORT=39088)) * establish * orcl * 0
03-MAR-2013 23:44:55 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.148)(PORT=39097)) * establish * orcl * 0
03-MAR-2013 23:44:55 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.148)(PORT=39096)) * establish * orcl * 0
So, the client 192.168.0.148 may enter a wrong pwd more than 5 times and make the account locked.
3. use user profile to solve this problem
SQL> show parameter resource_limit;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean FALSE
SQL> alter system set resource_limit=true;
System altered.
SQL> select username, b.* from dba_users a , dba_profiles b where a.profile=b.profile;
...
USERNAME PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ ------------------------------ -------------------------------- -------- ----------------------------------------
MYUSER1 DEFAULT IDLE_TIME KERNEL UNLIMITED
MYUSER1 DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
MYUSER1 DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
MYUSER1 DEFAULT CPU_PER_CALL KERNEL UNLIMITED
MYUSER1 DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
MYUSER1 DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
MYUSER1 DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
....
224 rows selected.
SQL> create profile cgs_limit_profile limit password_lock_time 0.0007;
Profile created.
SQL> alter user MYUSER1 profile cgs_limit_profile;
User altered.
SQL> select username, b.* from dba_users a , dba_profiles b where a.profile=b.profile;
USERNAME PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ ------------------------------ -------------------------------- -------- ----------------------------------------
MYUSER1 CGS_LIMIT_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT
MYUSER1 CGS_LIMIT_PROFILE PASSWORD_LOCK_TIME PASSWORD .0006
MYUSER1 CGS_LIMIT_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
MYUSER1 CGS_LIMIT_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT
MYUSER1 CGS_LIMIT_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT
MYUSER1 CGS_LIMIT_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT
MYUSER1 CGS_LIMIT_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
MYUSER1 CGS_LIMIT_PROFILE PRIVATE_SGA KERNEL DEFAULT
MYUSER1 CGS_LIMIT_PROFILE CONNECT_TIME KERNEL DEFAULT
USERNAME PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ ------------------------------ -------------------------------- -------- ----------------------------------------
MYUSER1 CGS_LIMIT_PROFILE IDLE_TIME KERNEL DEFAULT
MYUSER1 CGS_LIMIT_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
MYUSER1 CGS_LIMIT_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
MYUSER1 CGS_LIMIT_PROFILE CPU_PER_CALL KERNEL DEFAULT
MYUSER1 CGS_LIMIT_PROFILE CPU_PER_SESSION KERNEL DEFAULT
MYUSER1 CGS_LIMIT_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
MYUSER1 CGS_LIMIT_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
