Thursday, 9 October 2014

ORA-28000 "the account is locked" in the standby database, even after the account was unlocked in the primary.

It  was a very interesting case i came across. We have a Production database with a Standby database opened in Read-only mode for reporting (the Active data guard Feature). An  user account was locked in the production database. I issued the following command to unlock it.

SQL> alter user <username> account unlock;

wasn't that easy. Now as per my understanding the user account should have been unlocked in the standby database as well. Just to check i logged in the standby database

SQL> conn <username>/<password>

ERROR:
ORA-28000: the account is locked


now what !!!!    Still, the user cannot connect to the standby, getting the same error ORA-28000.

Luckily i found the following document on the Oracle Support. "ORA-28000 "the account is locked" in the standby database, even after the account was unlocked in the primary. (Doc ID 1600401.1)".

which states the cause of the issue as

"A privileged user (a database administrator) must unlock account in the standby database.  It is not enough to unlock the same account in the primary database.  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."


The solution to the problem is

"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."

4 comments:

  1. And, if your standby database is RAC, you have to unlock the account, on the standby, on every instance!

    Cheers,
    Norm.

    ReplyDelete
  2. Not required as this is RAC database with multiple instances

    ReplyDelete
  3. Not required as this is RAC database with multiple instances

    ReplyDelete