Changes to SYS user’s password not reflected in column PTIME of SYS.USER$ table

Today I noticed that the changes to the SYS user’s password were not being reflected in the ptime column of SYS.USER$. While this may be trivial, this column can be used to identify passwords that have not been changed recently. This issue is documented by Oracle in “After Changing SYS Password, DBA_USERS.EXPIRY_DATE Not Updated For SYS User (Doc ID 2518310.1)” and Bug 28538439. In summary, after applying the July 2020 patch, you need to apply Patch 28538439 and perform the below steps:

This was performed on a RAC database running Release 12.1.0.2.0.

After changing the SYS user’s password, the SYS.USER$ table displayed the below information:

set line 300
col name format a15
SELECT    name, ctime, ptime
FROM      sys.user$
where     name like 'SYS%'
order by  name
;  
 
NAME            CTIME     PTIME
--------------- --------- ---------
SYS             07-AUG-18 10-JUN-19 <-- Incorrect date

Check to see if patch 28538439 has been applied:

opatch lsinv -all | grep 28538439
     21117072, 22611167, 23182225, 25139545, 
     26196152, 27487279, 28538439 <-- Patch has been applied

The solution is to set the below parameter to “TRUE”:

show parameter _enable_ptime_update_for_sys;
 
No output is displayed, indicating that the 
parameter has not been set

Note that this parameter cannot be set by an ALTER SYSTEM command. It has to be present in the spfile at startup.

Create a temporary pfile from the spfile:

create pfile='/tmp/PRD1_pfile.txt' from spfile='+ORA_DATA_1/PRD1/PARAMETERFILE/spfile';

Edit the temporary pfile and add in the parameter:

vi /tmp/PRD1_pfile.txt
 
add the below line
 
*._enable_ptime_update_for_sys = TRUE

Stop the database and check the status:

srvctl stop database -d PRD1 -o immediate
 
srvctl status database -d PRD1

Start up one node of the RAC in nomount to recreate the spfile from the pfile:

sqlplus / as sysdba
 
startup nomount pfile='/tmp/PRD1_pfile.txt';
 
create spfile='+ORA_DATA_1/PRD1/PARAMETERFILE/spfile' from pfile='/tmp/PRD1_pfile.txt';
 
shutdown immediate;

Start up the RAC (all nodes) and check the status:

srvctl start database -d PRD1
srvctl status database -d PRD1

Check that the parameter to resolve Bug 28538439 has been set:

show parameter _enable_ptime_update_for_sys;
 
Output will be:
 
NAME                         TYPE    VALUE
---------------------------- ------- -----
_enable_ptime_update_for_sys boolean TRUE

This is not retroactive to the previous change to the SYS user’s password. The next time the SYS user’s password is changed, it will be reflected in the SYS.USER$ table:

set line 300
col name format a15
SELECT    name, ctime, ptime
FROM      sys.user$
where     name like 'SYS%'
order by  name
;  
 
NAME            CTIME     PTIME
--------------- --------- ---------
SYS             07-AUG-18 03-DEC-20 <-- Correct date