Troubleshooting ORA-28000: Account Locked in Active Data Guard

 

A common issue you might encounter in Active Data Guard is the ORA-28000 error, where an account such as DBSNMP is locked in the standby database, even though it appears open in the primary database.

When attempting to connect to the standby database, you might see the following error: 

oracle@xxxxxx bin]$ sqlplus dbsnmp@"<DBNAME>
SQL*Plus: Release 12.2.0.1.0 Production 
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-28000: the account is locked

But the user is not showing as locked, the account_status in dba_users show 'OPEN' in both primary and standby:

Primary database:

SQL> select account_status from dba_users where username='DBSNMP';
ACCOUNT_STATUS
--------------------------------
OPEN

Standby database:

SQL> select account_status from dba_users where username='DBSNMP';
ACCOUNT_STATUS
--------------------------------
OPEN

This issue arises because the account can become locked in the standby database (Active Data Guard), while remaining open in the primary database. The dba_users table in the standby database reflects the status from the primary database, which can be misleading. Instead, the V_$RO_USER_ACCOUNT table in the read-only standby database shows whether the account is actually locked due to failed login attempts.

SQL> select rua.con_id, du.username username, rua.userid, rua.PASSW_LOCKED, rua.PASSW_LOCK_UNLIM, to_char(rua.PASSW_LOCK_TIME,'DD-MON-YYYY HH24:MI:SS') locked_date
          from V$RO_USER_ACCOUNT rua, dba_users du
          where rua.userid=du.user_id and (rua.PASSW_LOCKED = 1 OR rua.PASSW_LOCK_UNLIM = 1);

This will show you when the account has been locked 

You will not be able to unlock the user directly from Standby Database.

Please run the following steps from Primary database

1. SQL>alter user dbsnmp account unlock;

2. SQL>alter system switch logfile;

3. SQL>alter system archive log all;

4. Check the Standby alert log file and make sure the archive logs are applied in standby database.

5. Now the user will be open in standby

6. Test the user connection in standby

7. Run below command in STANDBY Database

     SQL>select * from v_$RO_USER_ACCOUNT where username='DBSNMP';

You will not see an entry means your account is unlocked in STANDBY Database.

Note: 

Pre 12.1 version, all the user UNLOCK changes have to come from primary to Standby Active Data Guard database.

From 12.1.0.2 , we can directly UNLOCK the user from read only, Active Data Guard database.

SQL> alter user <username> account unlock;