关于Oracle 12.2 中system密码过期(expire)问题的研究

众所周知,在Oracle数据库12.2中,sys和system的口令被列入了Password Life Cycle management,
因此就存在sys密码和system密码在default profile中密码有效期是180天的问题.
请参考:How to set SYS And System Expiration Dates (文档 ID 2177278.1)


环境描述:
Oracle Database 12.2 x64bit 单机 ,RHEL6.7,虚拟机.建库时间(v$database.created)是2017-03-27,06:07:39
该RHEL6.7启动之后,操作系统时间是2017-07-25,于是,我 date -s '2017-11-11 22:37:30' ,然后clock -w,
然后重启os,然后启动db,执行如下查询,发现sys和system的 EXPIRY_DATE依然是2017-09-23,06:09:12,也就是修改os时间之前的日期.

SQL> select username,account_status,LOCK_DATE,EXPIRY_DATE,PROFILE from dba_users order by 1;USERNAME  ACCOUNT_STATUS       LOCK_DATE   EXPIRY_DATE       PROFILE------------------------- -------------------- ------------------- ------------------- -------------------------ANONYMOUS  EXPIRED & LOCKED     2017-01-26,15:27:28 2017-01-26,15:27:28 DEFAULTAPPQOSSYS  EXPIRED & LOCKED     2017-01-26,14:23:28 2017-01-26,14:23:28 DEFAULTAUDSYS  EXPIRED & LOCKED     2017-01-26,13:53:26 2017-01-26,13:53:26 DEFAULTCTXSYS  EXPIRED & LOCKED     2017-01-26,14:57:34 2017-01-26,14:57:34 DEFAULTDBSFWUSER  EXPIRED & LOCKED     2017-01-26,14:07:52 2017-01-26,14:07:52 DEFAULTDBSNMP  EXPIRED & LOCKED     2017-01-26,14:23:26 2017-01-26,14:23:26 DEFAULTDIP  EXPIRED & LOCKED     2017-01-26,14:05:46 2017-01-26,14:05:46 DEFAULTDVF  EXPIRED & LOCKED     2017-01-26,15:27:28 2017-01-26,15:27:28 DEFAULTDVSYS  EXPIRED & LOCKED     2017-01-26,15:26:39 2017-01-26,15:26:39 DEFAULTGGSYS  EXPIRED & LOCKED     2017-01-26,14:24:33 2017-01-26,14:24:33 DEFAULTGSMADMIN_INTERNAL  EXPIRED & LOCKED     2017-01-26,14:04:59 2017-01-26,14:04:59 DEFAULTGSMCATUSER  EXPIRED & LOCKED     2017-01-26,14:24:27 2017-01-26,14:24:27 DEFAULTGSMUSER   EXPIRED & LOCKED     2017-01-26,14:05:01 2017-01-26,14:05:01 DEFAULTLBACSYS   EXPIRED & LOCKED     2017-01-26,15:27:28 2017-01-26,15:27:28 DEFAULTMDDATA  EXPIRED & LOCKED     2017-01-26,15:27:28 2017-01-26,15:27:28 DEFAULTMDSYS  EXPIRED & LOCKED     2017-01-26,14:57:47 2017-01-26,14:57:47 DEFAULTOJVMSYS   EXPIRED & LOCKED     2017-01-26,14:51:07 2017-01-26,14:51:07 DEFAULTOLAPSYS   EXPIRED & LOCKED     2017-01-26,15:12:32 2017-01-26,15:12:32 DEFAULTORACLE_OCM  EXPIRED & LOCKED     2017-01-26,14:09:34 2017-01-26,14:09:34 DEFAULTORDDATA   EXPIRED & LOCKED     2017-01-26,14:57:47 2017-01-26,14:57:47 DEFAULTORDPLUGINS  EXPIRED & LOCKED     2017-01-26,14:57:47 2017-01-26,14:57:47 DEFAULTORDSYS  EXPIRED & LOCKED     2017-01-26,14:57:46 2017-01-26,14:57:46 DEFAULTOUTLN  EXPIRED & LOCKED     2017-01-26,13:53:40 2017-01-26,13:53:40 DEFAULTREMOTE_SCHEDULER_AGENT  EXPIRED & LOCKED     2017-01-26,14:07:51 2017-01-26,14:07:51 DEFAULTSI_INFORMTN_SCHEMA  EXPIRED & LOCKED     2017-01-26,14:57:47 2017-01-26,14:57:47 DEFAULTSPATIAL_CSW_ADMIN_USR  EXPIRED & LOCKED     2017-01-26,15:15:55 2017-01-26,15:15:55 DEFAULTSYS  OPEN   2017-09-23,06:09:12 DEFAULTSYS$UMF   EXPIRED & LOCKED     2017-01-26,14:16:23 2017-01-26,14:16:23 DEFAULTSYSBACKUP  EXPIRED & LOCKED     2017-01-26,13:53:27 2017-01-26,13:53:27 DEFAULTSYSDG  EXPIRED & LOCKED     2017-01-26,13:53:27 2017-01-26,13:53:27 DEFAULTSYSKM  EXPIRED & LOCKED     2017-01-26,13:53:27 2017-01-26,13:53:27 DEFAULTSYSRAC  EXPIRED & LOCKED     2017-01-26,13:53:27 2017-01-26,13:53:27 DEFAULTSYSTEM  OPEN    2017-09-23,06:09:12 DEFAULT   ------------->>>注意此行.WMSYS  EXPIRED & LOCKED     2017-01-26,14:48:37 2017-01-26,14:48:37 DEFAULTXDB  EXPIRED & LOCKED     2017-01-26,14:27:18 2017-01-26,14:27:18 DEFAULTXS$NULL   EXPIRED & LOCKED     2017-01-26,14:07:19 2017-01-26,14:07:19 DEFAULT36 rows selected.

那我就有点疑问了,按照DEFAULT这个profile 180天的设置,如下:

SQL> select * from  dba_profiles where profile='DEFAULT' and RESOURCE_NAME='PASSWORD_LIFE_TIME';PROFILE  RESOURCE_NAME  RESOURCE LIMIT    COM INH IMP---------------------------------------- -------------------------------- -------- ---------------------------------------- --- --- ---DEFAULT  PASSWORD_LIFE_TIME  PASSWORD 180    NONO  NOSQL> 

现在(2017-11-11 23:22:11)已经超过了2017-09-23,06:09:12,为啥system的密码还不expire呢? 我猜测肯定有一个"触发system密码expire"的时机.

过了一段时间之后(中间有别的事情,不是特意耽误),当RHEL6.7的os时间是2017-11-18之后,我这么做了一下:

C:\Users\Administrator>sqlplus system/aaaaaa@12cR2_80.111SQL*Plus: Release 11.2.0.4.0 Production on 星期三 7月 26 12:37:16 2017Copyright (c) 1982, 2013, Oracle.  All rights reserved.ERROR:ORA-28002: the password will expire within 7 days ---------->>>注意此处的提示,我灵光一闪,莫非"触发system密码expire"的时机是在system登录时?连接到:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionSQL> 

于是我立即查看监听日志:

2017-11-12T03:14:47.639148+08:0012-NOV-2017 03:14:47 * (CONNECT_DATA=(SERVICE_NAME=orcl)(CID=(PROGRAM=H:\app\Administrator\product\11.2.0\client_1\bin\sqlplus.exe)(HOST=HUNTER-PC)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.80.1)(PORT=59262)) * establish * orcl * 0

在2017-11-18 03:14:47,在 HUNTER-PC上,使用sqlplus.exe登录到本12.2数据库中.

然后立即去查看dba_users视图:

SQL> set lines 400SQL> set pages 400SQL> alter session set nls_date_format = 'yyyy-mm-dd,hh24:mi:ss';SQL> column profile format a25SQL> column username format a25SQL> column account_status format a20SQL> col default_tablespace format a20SQL> col temporary_tablespace format a20SQL> select username,account_status,LOCK_DATE,EXPIRY_DATE,PROFILE from dba_users order by 1;USERNAME  ACCOUNT_STATUS       LOCK_DATE   EXPIRY_DATE       PROFILE------------------------- -------------------- ------------------- ------------------- -------------------------ANONYMOUS  EXPIRED & LOCKED     2017-01-26,15:27:28 2017-01-26,15:27:28 DEFAULTAPPQOSSYS  EXPIRED & LOCKED     2017-01-26,14:23:28 2017-01-26,14:23:28 DEFAULTAUDSYS  EXPIRED & LOCKED     2017-01-26,13:53:26 2017-01-26,13:53:26 DEFAULTCTXSYS  EXPIRED & LOCKED     2017-01-26,14:57:34 2017-01-26,14:57:34 DEFAULTDBSFWUSER  EXPIRED & LOCKED     2017-01-26,14:07:52 2017-01-26,14:07:52 DEFAULTDBSNMP  EXPIRED & LOCKED     2017-01-26,14:23:26 2017-01-26,14:23:26 DEFAULTDIP  EXPIRED & LOCKED     2017-01-26,14:05:46 2017-01-26,14:05:46 DEFAULTDVF  EXPIRED & LOCKED     2017-01-26,15:27:28 2017-01-26,15:27:28 DEFAULTDVSYS  EXPIRED & LOCKED     2017-01-26,15:26:39 2017-01-26,15:26:39 DEFAULTGGSYS  EXPIRED & LOCKED     2017-01-26,14:24:33 2017-01-26,14:24:33 DEFAULTGSMADMIN_INTERNAL  EXPIRED & LOCKED     2017-01-26,14:04:59 2017-01-26,14:04:59 DEFAULTGSMCATUSER  EXPIRED & LOCKED     2017-01-26,14:24:27 2017-01-26,14:24:27 DEFAULTGSMUSER   EXPIRED & LOCKED     2017-01-26,14:05:01 2017-01-26,14:05:01 DEFAULTLBACSYS   EXPIRED & LOCKED     2017-01-26,15:27:28 2017-01-26,15:27:28 DEFAULTMDDATA  EXPIRED & LOCKED     2017-01-26,15:27:28 2017-01-26,15:27:28 DEFAULTMDSYS  EXPIRED & LOCKED     2017-01-26,14:57:47 2017-01-26,14:57:47 DEFAULTOJVMSYS   EXPIRED & LOCKED     2017-01-26,14:51:07 2017-01-26,14:51:07 DEFAULTOLAPSYS   EXPIRED & LOCKED     2017-01-26,15:12:32 2017-01-26,15:12:32 DEFAULTORACLE_OCM  EXPIRED & LOCKED     2017-01-26,14:09:34 2017-01-26,14:09:34 DEFAULTORDDATA   EXPIRED & LOCKED     2017-01-26,14:57:47 2017-01-26,14:57:47 DEFAULTORDPLUGINS  EXPIRED & LOCKED     2017-01-26,14:57:47 2017-01-26,14:57:47 DEFAULTORDSYS  EXPIRED & LOCKED     2017-01-26,14:57:46 2017-01-26,14:57:46 DEFAULTOUTLN  EXPIRED & LOCKED     2017-01-26,13:53:40 2017-01-26,13:53:40 DEFAULTREMOTE_SCHEDULER_AGENT  EXPIRED & LOCKED     2017-01-26,14:07:51 2017-01-26,14:07:51 DEFAULTSI_INFORMTN_SCHEMA  EXPIRED & LOCKED     2017-01-26,14:57:47 2017-01-26,14:57:47 DEFAULTSPATIAL_CSW_ADMIN_USR  EXPIRED & LOCKED     2017-01-26,15:15:55 2017-01-26,15:15:55 DEFAULTSYS  OPEN   2017-09-23,06:09:12 DEFAULTSYS$UMF   EXPIRED & LOCKED     2017-01-26,14:16:23 2017-01-26,14:16:23 DEFAULTSYSBACKUP  EXPIRED & LOCKED     2017-01-26,13:53:27 2017-01-26,13:53:27 DEFAULTSYSDG  EXPIRED & LOCKED     2017-01-26,13:53:27 2017-01-26,13:53:27 DEFAULTSYSKM  EXPIRED & LOCKED     2017-01-26,13:53:27 2017-01-26,13:53:27 DEFAULTSYSRAC  EXPIRED & LOCKED     2017-01-26,13:53:27 2017-01-26,13:53:27 DEFAULTSYSTEM  EXPIRED(GRACE)   2017-11-19,03:14:49 DEFAULT ------------->>>注意此处,EXPIRY_DATE已经变成了"2017-11-19,03:14:49"了.WMSYS  EXPIRED & LOCKED     2017-01-26,14:48:37 2017-01-26,14:48:37 DEFAULTXDB  EXPIRED & LOCKED     2017-01-26,14:27:18 2017-01-26,14:27:18 DEFAULTXS$NULL   EXPIRED & LOCKED     2017-01-26,14:07:19 2017-01-26,14:07:19 DEFAULT36 rows selected.SQL> ---------------->>>因此:基本能断定"触发system密码expire"的时机是在system登录时.

总结:

sys密码expire会导致存储了sys密码的带库备份软件无法运行rman备份,后果你懂的.

system密码expire会导致使用system做应用程序登录的应用程序无法运行,也会导致自动运行的job无法运行,