INTRODUCTION :
This Post describes that account is locked in the standby database, even after the account was unlocked in the primary.
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@DOYEN > conn sathish/sathish123
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
SYS@DOYEN > select username,account_status from dba_users where username=’SATHISH’;
USERNAME ACCOUNT_STATUS
——————————————————————————————
SATHISH OPEN
SYS@DOYEN > @check_db
DbId Name Log Mode Open Mode Database Role Created Resetlogs Remote Archive
———– ——— ———— ——————– —————- ——————- ——————- ——————
603299473 SATHISH ARCHIVELOG READ ONLY WITH APPLY PHYSICAL STANDBY 11/13/2019 20:16:17 11/13/2019 03:54:21 ENABLED
Solution :
This is because the standby 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.
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@DOYEN > ALTER USER SATHISH ACCOUNT UNLOCK;
ALTER USER SATHISH ACCOUNT UNLOCK
*
ERROR at line 1:
ORA-28015: Account unlocked, but the database is open for read-only access
SYS@DOYEN > conn sathish/sathish123
Connected.
