ORA-28000: the account is locked.

ORA-28000: the account is locked.

 

问题：ORACLE EBS R12，浏览器访问500报错误，查看log发现应用不能建立DB连接

原因现象：

  1、DB服务能正常启动。

  2、AP服务使用./adstrtal.shapps/apps无法自动启动，但是可以分别启动单项服务。

  3、使用./adautocfg.sh发生error，查log发现ORA-28000:the account is locked.----从此入手

解决办法：

  1、手动设置环境变量ORACLE_HOME、ORACLE_SID,否则sqlplus不能启动。

  2、到ORACLE_HOME/bin打开终端运行sqlplus / assysdba

  3、对账户解锁，可在数据库管理员账户(sys/system)中使用“alteruser”命令的account unlock子句。

          alter user username accountunlock;

      账户解锁后，username 账户再一次被允许连接。

Details:
Oracle Database Version: 11g R1(11.1.0.7) EBS R12.1.2, OEL 5.5 x86_64bit
Application User: APPS
Error: ORA-28000: the account islocked

Login asSYSDBA

SQL> conn /as sysdba

Check the APPSUSRaccount status.

SQL>SELECT username, account_status FROM dba_users WHERE username=‘APPS’;
USERNAME ACCOUNT_STATUSPROFILE
——————– ——————–—————
APPS LOCKED(TIMED) DEFAULT

 

Here we can see the account status is LOCKED (TIMED) and thedefault user’s profile is DEFAULT.

Check the resourcelimits of DEFAULT profile.

SQL>SELECT resource_name,resource_type,limit FROM dba_profiles WHEREprofile=’DEFAULT’;

RESOURCE_NAME RESOURCELIMIT
——————————– ——–———-
COMPOSITE_LIMIT KERNELUNLIMITED
SESSIONS_PER_USER KERNELUNLIMITED
CPU_PER_SESSION KERNELUNLIMITED
CPU_PER_CALL KERNELUNLIMITED
LOGICAL_READS_PER_SESSIONKERNEL UNLIMITED
LOGICAL_READS_PER_CALLKERNEL UNLIMITED
IDLE_TIME KERNELUNLIMITED
CONNECT_TIME KERNELUNLIMITED
PRIVATE_SGA KERNELUNLIMITED
FAILED_LOGIN_ATTEMPTSPASSWORD 10
PASSWORD_LIFE_TIMEPASSWORD UNLIMITED
PASSWORD_REUSE_TIMEPASSWORD UNLIMITED
PASSWORD_REUSE_MAXPASSWORD UNLIMITED
PASSWORD_VERIFY_FUNCTIONPASSWORD NULL
PASSWORD_LOCK_TIMEPASSWORD UNLIMITED
PASSWORD_GRACE_TIMEPASSWORD UNLIMITED

All resource limits for DEFAULT profile is set to UNLIMITED, butonly for FAILED_LOGIN_ATTEPTS attribute, it’s set to some value(10). Due to this the user account keeps getting locked(timed).Whenwe check in the Oracle Documentations, it’sstated that FAILED_LOGIN_ATTEPTS attribute for DEFAULT profile hasbeen changed since 10.2.0.1 from UNLIMITED to 10.

What we can do is, either we may need to change the resource limitfor FAILED_LOGIN_ATTEPTS attribute in DEFAULT profile, or create anew profile for that user with FAILED_LOGIN_ATTEPTS attribute valueset to UNLIMITED. But for security reasons, we will not tamper theDEFAULT profile, which is not recommended too. Then let’s go forcreating a new profile and assign that profile to the user.

Create aprofile.

SQL>CREATE PROFILE APPS_DEFAULT LIMIT
 COMPOSITE_LIMITUNLIMITED
 SESSIONS_PER_USERUNLIMITED
 CPU_PER_SESSIONUNLIMITED
 CPU_PER_CALLUNLIMITED
 LOGICAL_READS_PER_SESSIONUNLIMITED
 LOGICAL_READS_PER_CALLUNLIMITED
 IDLE_TIMEUNLIMITED
 CONNECT_TIMEUNLIMITED
 PRIVATE_SGAUNLIMITED
 FAILED_LOGIN_ATTEMPTSUNLIMITED
 PASSWORD_LIFE_TIMEUNLIMITED
 PASSWORD_REUSE_TIMEUNLIMITED
 PASSWORD_REUSE_MAXUNLIMITED
 PASSWORD_VERIFY_FUNCTIONNULL
 PASSWORD_LOCK_TIMEUNLIMITED
 PASSWORD_GRACE_TIMEUNLIMITED;

Profilecreated.

Assign the newlycreated profile to the user as defaultprofile.

SQL> ALTERUSER appusr PROFILE appusr_default;

User altered.

Unlock the useraccount:

SQL> ALTERUSER apps ACCOUNT UNLOCK;

Useraltered.

 

Now check again thestatus of APPS user.

SQL>SELECT username, account_status FROM dba_users WHERE username=‘APPS’;
USERNAME ACCOUNT_STATUSPROFILE
——————– ——————–—————
APPS OPEN APPS_DEFAULT