DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, December 2, 2014

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@DOYEN > conn SARAVANA/saravana123123
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='SARAVANA';

USERNAME                                                                                   ACCOUNT_STATUS
------------------------------------------------------------------------------------------ 
SARAVANA                                                                                               OPEN


SYS@DOYEN > @check_db

                                                                                                                 Remote
       DbId Name      Log Mode     Open Mode            Database Role    Created             Resetlogs           Archive
----------- --------- ------------ -------------------- ---------------- ------------------- ------------------- --------
  603299473 DOYEN   ARCHIVELOG   READ ONLY WITH APPLY PHYSICAL STANDBY 11/16/2013 20:16:17 07/22/2014 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 SARAVANA ACCOUNT UNLOCK;
ALTER USER SARAVANA ACCOUNT UNLOCK
*
ERROR at line 1:
ORA-28015: Account unlocked, but the database is open for read-only access

SYS@DOYEN > conn SARAVANA/saravana123123
Connected.


Reference :
========

ORA-28000 "the account is locked" in the standby database, even after the account was unlocked in the primary. (Doc ID 1600401.1)