How to Fix an Oracle User's Password When It Has Expired

If an Oracle user's password has expired or is about to expire (within 14 days), then its account will be locked. If this occurs on an Oracle database being used with Mobility Suite, then there will be many Oracle ORA-28001 errors that appear in the '/var/log/nukona/celery.log' file that contain entries eluding to the Oracle password being expired. The below steps will need to be followed from the SQL Plus interface for both the mdmcore and appstore databases once their ORACLE_SID's have been exported from the Linux Terminal:

1. Export your database and login to the SQL Plus interface:

export ORACLE_SID=YourDB
sqlplus /nolog
connect sys as sysdba

2. Modify the default user profile to set the password policies to 'UNLIMITED':

ALTER PROFILE DEFAULT LIMIT COMPOSITE_LIMIT UNLIMITED
  PASSWORD_LIFE_TIME UNLIMITED
  PASSWORD_REUSE_TIME UNLIMITED
  PASSWORD_REUSE_MAX UNLIMITED
  PASSWORD_VERIFY_FUNCTION NULL
  PASSWORD_LOCK_TIME UNLIMITED
  PASSWORD_GRACE_TIME UNLIMITED
  FAILED_LOGIN_ATTEMPTS UNLIMITED;

3. Check the password expiration and status of the user account associated with the Mobility Suite's '/usr/local/nukona/etc/settings.cfg' file:

SELECT EXPIRY_DATE from dba_users where username = 'YourUser';

SELECT RESOURCE_NAME,LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME IN ('FAILED_LOGIN_ATTEMPTS','PASSWORD_LOCK_TIME');

SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME='YourUser';

4. Set the updated default password policy profile and reset the password for the user account associated for the Mobility Suite's databases while also unlocking the user account:

ALTER USER YourUser PROFILE DEFAULT;

connect YourUser

ALTER USER YourUser IDENTIFIED BY YourPassword;

ALTER USER YourUser ACCOUNT UNLOCK;

Your user account should now be unlocked, the password should be updated, the Mobility Suite services should have reestablished communication with the database, and the user account's password should not expire again. If there are any problems with running the above commands, it could be that the Mobility Suite services are still attempting to communicate with the database. If this happens, then the following commands can be run to end the sessions once the 'SID' and 'SERIAL' has been determined for 'YourUser':

SELECT s.sid, s.serial#, s.status, p.spid 
  FROM v$session s, v$process p 
 WHERE s.username = 'YourUser' --<<<--
  AND p.addr(+) = s.paddr
 /

ALTER SYSTEM KILL SESSION '<SID>, <SERIAL>'
/