问题描述：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/8482828