标签：LIFE mm dd 28002 28001 TIME PASSWORD ora
问题描述：trace文件中发现存在ora-28001告警，如下所示：
数据库：oracle 11.2.0.4 64位
Mon Oct 17 04:26:02 2022
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_1228.trc (incident=169673):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [17059], [0x19BFF78CB0], [0x19BFF79548], [0x1ABFF6CB90], [], [], [], [], [], [], [], []
ORA-28001: the password has expired
原因分析：经确认PASSWORD_LIFE_TIME参数已经为UNLIMITED，那为什么还会出现用户密码过期的异常呢？
oracle 11g默认default概要文件中PASSWORD_LIFE_TIME为180天，若将其修改为unlimited，那么还没有被提示ora-28002 or ora-28001告警的用户不会再遇到该问题，但修改之前就遇到ora-28002 or ora-28001告警的用户就需要更改一次密码，此后该用户才能正常登陆且PASSWORD_LIFE_TIME变为unlimited.
1、模拟场景
查用户Profile，此次以scott用户作为测试.
> select username,profile from dba_users;
USERNAME PROFILE
------------------------------ --------------------
SYS DEFAULT
SYSTEM DEFAULT
OUTLN DEFAULT
MGMT_VIEW DEFAULT
FLOWS_FILES DEFAULT
MDSYS DEFAULT
ORDSYS DEFAULT
EXFSYS DEFAULT
DBSNMP MONITORING_PROFILE
WMSYS DEFAULT
APPQOSSYS DEFAULT
USERNAME PROFILE
------------------------------ --------------------
APEX_030200 DEFAULT
OWBSYS_AUDIT DEFAULT
ORDDATA DEFAULT
CTXSYS DEFAULT
ANONYMOUS DEFAULT
SYSMAN DEFAULT
XDB DEFAULT
ORDPLUGINS DEFAULT
OWBSYS DEFAULT
SI_INFORMTN_SCHEMA DEFAULT
OLAPSYS DEFAULT
USERNAME PROFILE
------------------------------ --------------------
SCOTT DEFAULT
ORACLE_OCM DEFAULT
XS$NULL DEFAULT
MDDATA DEFAULT
DIP DEFAULT
APEX_PUBLIC_USER DEFAULT
SPATIAL_CSW_ADMIN_USR DEFAULT
SPATIAL_WFS_ADMIN_USR DEFAULT
30 rows selected.
查密码有效期.
> select * from dba_profiles s where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME'
PROFILE RESOURCE_NAME RESOURCE LIMIT
--------------- -------------------------------- -------- ---------------
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
> select username,account_status,to_char(lock_date,'yyyy-mm-dd hh24:mi:ss'),to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss'),to_char(created,'yyyy-mm-dd hh24:mi:ss') from dba_users where username=’SCOTT’;
USERNAME ACCOUNT_STATUS TO_CHAR(LOCK_DATE,' TO_CHAR(EXPIRY_DATE TO_CHAR(CREATED,'YY
------------------------------ -------------------------------- ------------------- ------------------- -------------------
SCOTT OPEN 2023-04-18 10:19:18 2009-08-15 00:50:14
1 rows selected.
修改password_life_time为1分钟过期.
SQL> alter profile default limit password_life_time 1/24/60;
Profile altered.
SQL> select * from dba_profiles s where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT PASSWORD_LIFE_TIME PASSWORD .0006
SQL> ! date
Sat Oct 22 20:51:05 CST 2022
SQL> conn scott/tiger
ERROR:
ORA-28002: the password will expire within 7 days
Connected.
SQL> select username,account_status,to_char(lock_date,'yyyy-mm-dd hh24:mi:ss'),to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss'),to_char(created,'yyyy-mm-dd hh24:mi:ss') from dba_users where username=’SCOTT’;
USERNAME ACCOUNT_STATUS TO_CHAR(LOCK_DATE,' TO_CHAR(EXPIRY_DATE TO_CHAR(CREATED,'YY
------------------------------ -------------------------------- ------------------- ------------------- -------------------
SCOTT EXPIRED(GRACE) 2022-10-29 20:56:10 2009-08-15 00:50:14
1 rows selected.
现将密码有效期设置为永久.
SQL> alter profile default limit password_life_time unlimited;
Profile altered.
SQL> conn scott/tiger
ERROR:
ORA-28002: the password will expire within 7 days
Connected.
SQL> select * from dba_profiles s where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
说明：成功模拟出异常场景，虽然scott用户的PASSWORD_LIFE_TIME为UNLIMITED，但登陆依然报警ora-28002.
2、解决方案
由于此处存在7天的宽限期，所以通过关键字“TRACE”查出用户信息，对于状态为EXPIRED & LOCKED的用户，可以通过相应关键字查询出来.
SQL> select * from dba_profiles where resource_name='PASSWORD_GRACE_TIME';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
MONITORING_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT
SQL> select username,account_status,to_char(lock_date,'yyyy-mm-dd hh24:mi:ss'),to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss'),to_char(created,'yyyy-mm-dd hh24:mi:ss') from dba_users where account_status like '%GRACE%';
USERNAME ACCOUNT_STATUS TO_CHAR(LOCK_DATE,' TO_CHAR(EXPIRY_DATE TO_CHAR(CREATED,'YY
------------------------------ -------------------------------- ------------------- ------------------- -------------------
SCOTT EXPIRED(GRACE) 2022-10-29 20:56:10 2009-08-15 00:50:14
重置密码
SQL> alter user scott identified by tiger account unlock;
User altered.
SQL> conn scott/tiger
Connected.
结论：scott用户恢复正常，成功处理ora-28001 ora28002的异常.
参考网址：https://blog.csdn.net/qq_43307934/article/details/84828285
标签：LIFE,
mm,
dd,
28002,
28001,
TIME,
PASSWORD,
ora
From： https://blog.51cto.com/u_12991611/5786176