How to get a list of locked users in Oracle

Products

• CA Business Service Insight

Issue/Introduction

When trying to get to the login screen we see an Oracle error saying we cannot connect. How can we check if we're locked out of the database?

You may also see strings errors, such as [MSG - Messages/General/ERROR_INTERNAL_ERROR_TEXT] 

Environment

Release:
Component: OBLCRE

Resolution

The following query will list all the users in the system and their lock status, so you can see if any need to be unlocked:

SELECT username, 
       account_status
  FROM dba_users;

This should be done with the system admin account (such as SYS).
If you do have any locked ones, then they can be unlocked and/or have their password reset with
 

ALTER USER user_name IDENTIFIED BY password ACCOUNT UNLOCK;

Then you'll need to use the PassUpdate utility on both Web and App servers to ensure that BSI knows the correct password for that user:

   %OG_HOME%\Utilities\PassUpdate\PassUpdate.exe

You may need to check all the accounts that BSI uses and are shown in the PassUpdate utility. I often run the following set of commands (one by one to make sure they all go through) to reset and unlock all the default users. Note that this should be done with the SYS database account.

alter profile DEFAULT limit password_life_time UNLIMITED;    <- needed only if you want to change the default password expiration
ALTER USER OBLICORE ACCOUNT UNLOCK;
ALTER USER USRSLA ACCOUNT UNLOCK;
ALTER USER USRMTN ACCOUNT UNLOCK;
ALTER USER USRCSL ACCOUNT UNLOCK;
ALTER USER USREDR ACCOUNT UNLOCK;
ALTER USER USRPSL ACCOUNT UNLOCK;
ALTER USER USRRPT ACCOUNT UNLOCK;
ALTER USER USRDASH ACCOUNT UNLOCK;
ALTER USER USRINSIGHT ACCOUNT UNLOCK;
ALTER USER OBLICORE identified by oblicore;
ALTER USER USRSLA identified by usrsla;
ALTER USER USRMTN identified by usrmtn;
ALTER USER USRCSL identified by usrcsl;
ALTER USER USREDR identified by usredr;
ALTER USER USRPSL identified by usrpsl;
ALTER USER USRRPT identified by usrrpt;
ALTER USER USRDASH identified by usrdash;
ALTER USER USRINSIGHT identified by usrinsight;

Additional Information

These account problems often occur because Oracle sets the default account profile to expire passwords after 30 days.
To change this and prevent it from happening again you can run:

alter profile DEFAULT limit password_life_time UNLIMITED;

You'll find more about PassUpdate in the BSI Documentation:

https://techdocs.broadcom.com/us/en/ca-enterprise-software/business-management/clarity-business-service-insight/8-3-5/administration/passwords.html