OBIEE 12c BIPLATFORM / MDS Account Password Expired or Account locked

I have recently come across an issue where few of our BI components went down and while stopping/restarting the services, It prompts for weblogic User Name and Password and also failing to start the services.

I have started analyzing the AdminServer log files in OBIEE 12c where I found the issue details like

[jdbc:oracle:thin:@//biapp:1521/bidev]

caused by java.sql.SQLException: ORA-28001: the password expired.

Then I understand that the supporting schemas like MDS, BIPLATFORM, IAU, OPSS and etc., accounts are expired in database. Here is the steps that how I have resolved that.

Step1: Connect to oracle database as a sys user and run the below query to get the expired accounts.

SQL> SELECT USERNAME,ACCOUNT_STATUS FROM DBA_USERS WHERE ACCOUNT_STATUS LIKE 'EXPIRED%';

Above query will result all the expired account details.

OR

SQL> SELECT USERNAME,ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME LIKE 'DEV_%'

Above query will result all the accounts starting with the name 'DEV_'

 

If you see the above screenshot, 6 accounts are expired. Lets start fixing it.

Step2: First we have to get the existing password for the expired accounts. For that run below query.

SQL> SELECT USERNAME, PASSWORD FROM DBA_USERS WHERE USERNAME = 'DEV_BIPLATFORM';

This query will give the password for only DEV_BIPLATFORM account. same like, get the password for all the accounts.

Step3: Reset the password for the account

SQL> ALTER USER DEV_BIPLATFORM IDENTIFIED BY 'Admin123';

Step4: (Optional: If any account is locked)

SQL> ALTER USER DEV_BIPLATFORM ACCOUNT UNLOCK;

Step5: Disable Password expiry

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Note:

If you need to know the Profile name , then Please run the below Query:

SYNTAX: 

SELECT PROFILE FROM DBA_USERS WHERE USERNAME = '<USERNAME>';

E.G: 

SELECT PROFILE FROM DBA_USERS WHERE USERNAME = 'DEV_BIPLATFORM';