dg备库用户状态为OPEN，但登录报ORA-28000,账户被锁的错误

郑全

主备库都是OPEN状态，但备库无法登录

原因是该用户修改了密码，但 备库有用户通过中间件连接，使用以前的密码在登录，导致该用户被锁，但备库是只读的，所以，在DBA_USERS里面看不到，但可以在  v_$RO_USER_ACCOUNT  中，看到 PASSW_LOCK_UNLIM=1


解决办法：
   主库解锁该用户，并切换同步到备库，备库重启一下即可。


附：
  参考文档： DG standby: account-status OPEN but login fails with "ORA-28000: the account is locked" (Doc ID 2718878.1)


DG standby: account-status OPEN but login fails with "ORA-28000: the account is locked" (Doc ID 2718878.1)

In this Document

Symptoms
Cause
Solution
References

APPLIES TO:Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.
SYMPTOMSCustomer is getting ORA-28000: the account is locked in Active Data Guard
User DBSNMP or any user is locked on ADG Standby database (read only mode) and it is open in Primary database.


Problem Description
---------------------------------------------------
[oracle@xxxxxx bin]$ sqlplus dbsnmp@"(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxx-scan.xxx-xx.xxx.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XXXX)))"

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




CAUSEDBSNMP or any other user is locked in Active Data Guard but OPEN in Primary DB.
On Standby DB, DBA_USERS will give ACCOUNT_STATUS as OPEN because it is synced from Primary.
But V_$RO_USER_ACCOUNT table in read-only standby DB show an entry if the account in STANDBY is LOCKED due to failed login attempts to standby:
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 (locked_date column in above query).
SOLUTIONYou 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;