Oracle Fusion Applications DB Users account expired

If you receive the error while starting the fusion applications like

FunctionalSetupServer_1 [ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'  WLS Kernel 1376716681974 BEA-001129 Received exception while creating connection for pool "ApplicationDB": ORA-28001: the password has expired


To solve this,

For Oracle Fusion installations, you need to change all fusion-related accounts if you find one of them has expired or is going to expire.  To find the account status and its expiry date, you can use:

SQL> select username, account_status, lock_date, expiry_date from dba_users;


USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA
------------------------------ -------------------------------- ---------
SCM_FUSION_MDS_SOA             EXPIRED                          17-JUL-13
SCM_FUSION_SOAINFRA            EXPIRED                          16-JUL-13
FUSION_OTBI                    EXPIRED                          17-JUL-13
FUSION_BIPLATFORM              EXPIRED                          16-JUL-13
HCM_FUSION_SOAINFRA            EXPIRED                          16-JUL-13
FUSION_ORA_ESS                 EXPIRED                          16-JUL-13
SETUP_FUSION_MDS_SOA           EXPIRED                          16-JUL-13
PRC_FUSION_SOAINFRA            EXPIRED                          16-JUL-13
FUSION_PORTLET                 EXPIRED                          18-JUL-13
PRC_FUSION_MDS_SOA             EXPIRED                          18-JUL-13
OIC_FUSION_MDS_SOA             EXPIRED                          18-JUL-13


To extract the encrypted password for the users who have expired accounts



select
  'alter user "'||username||'" identified by values '''||
  extract(xmltype(dbms_metadata.get_xml('USER',username)),
  '//USER_T/PASSWORD/text()').getStringVal()||''';'  old_password
from
   dba_users where account_status='EXPIRED'




Verify the passwords and usernames are correctly populated like this one.

alter user "SCM_FUSION_MDS_SOA" identified by values '163ADA6508E9D1C3';
alter user "SCM_FUSION_SOAINFRA" identified by values 'D912786349BA6EA3';
alter user "FUSION_OTBI" identified by values '66E632C33281B01E';
alter user "FUSION_BIPLATFORM" identified by values 'EA482B1786E8F466';
alter user "HCM_FUSION_SOAINFRA" identified by values '1D1C913177201A02';
alter user "FUSION_ORA_ESS" identified by values 'E35CAA10530BC219';
alter user "SETUP_FUSION_MDS_SOA" identified by values '9594CA5CD06C3825';
alter user "PRC_FUSION_SOAINFRA" identified by values 'F89E2FC5B64583F9';
alter user "FUSION_PORTLET" identified by values '18BE75B962643F54';
alter user "PRC_FUSION_MDS_SOA" identified by values '35A07CDF207B6F04';
alter user "OIC_FUSION_MDS_SOA" identified by values 'A4E4736B03BB24FF';
alter user "OIC_FUSION_SOAINFRA" identified by values '442823F64859765D';


Execute these statements agaist SQL*PLUS as sys user only.

SQL> alter user "SCM_FUSION_MDS_SOA" identified by values '163ADA6508E9D1C3';

User altered.

Cheers!!!