Sunday, 30 April 2017

ORA-28000 "the account is locked" in the standby database


Scenario:

The user attempts to logon and gets an error ORA-28000 "the account is locked" in the primary database which is configure with Dataguard.
The database administrator unlock this user's account in the primary database.  Still, the user cannot connect to the standby, getting the same error ORA-28000 for the particular user, But the account status shows open in the standby.

In Standby :


SYS@ORCL > conn BNSUSER/sbl123
ERROR:
ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.

SYS@ORCL > select username,account_status from dba_users where username='BNSUSER';

USERNAME    ACCOUNT_STATUS
--------------------------
BNSUSER    OPEN


Reason :


 This is because the standby database is open read-only and cannot update any tables.  When a user's account has to be locked on the standby database, it is locked only in memory there.

Solution:

A privileged user (sysdba, the database administrator) must logon to the standby and unlock the account there.  A message ORA-28015 "Account unlocked, but the database is open for read-only access" confirms that the account is now unlocked in the standby database.  From then on, the user can logon to the standby database without getting any error.

SYS@ORCL > ALTER USER BNSUSER ACCOUNT UNLOCK;
ALTER USER BNSUSER ACCOUNT UNLOCK
*
ERROR at line 1:
ORA-28015: Account unlocked, but the database is open for read-only access

SYS@ORCL > conn BNSUSER/SBL123
Connected.