首页 » ORACLE 9i-23ai » 19c ADG standby account is “OPEN” state, but login fails with “ORA-28000: the account is locked”

19c ADG standby account is “OPEN” state, but login fails with “ORA-28000: the account is locked”

今天一套oracle 19c(19.16)dataguard 环境,一个维护数据库用户(非sys, system)在primary和standby side都是OPEN状态,但是在备库登录时依旧提示ora-28000 : the account is locked, 记录一下处理方法。

1,检查用户状态
— On primary

SQL> select account_status from dba_users where username='ANBOB';
ACCOUNT_STATUS
— — — — — — — — — — — — — — — —
OPEN

— On standby

SQL> select account_status from dba_users where username='ANBOB';
ACCOUNT_STATUS
— — — — — — — — — — — — — — — —
OPEN

Note:
注意dba_users的account_status是从primary 同步过来的,如果查询当前standby用户状态从v$ro_user_account.

-- PASSW_LOCKED 1:locked  0:unlock
SQL> select rua.con_id, du.username username, rua.userid, rua.PASSW_LOCKED, rua.FAILED_LOGINS,
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);

Note:
如果用户被锁可以从该view查询到记录,unlock后记录不再显示 。

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

According to this note, this is expected behavior wherein user explicitly attempted failed
As account_status is in OPEN state, DBA_USERS On Standby DB, will give ACCOUNT_STATUS as OPEN because it is synced from Primary. You may need to query v_$RO_USER_ACCOUNT to get correct information on ADG.

这是standby(read only) database的memory 级lock,可以尝试解锁。

确保备库不存在错误的密码尝试触发了FAILED_LOGIN_ATTEMPTS后的预期行为。

尝试二种方法
1,直接在standby unlock

alter user <username> account unlock;

12c前会因为是read only 提示ora-错误, 忽略就行,从12c(12.1.0.2)开始支持直接可以在standby unlock user. 尝试登录。

— or —
2,从primary 同步

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

        
打赏

对不起,这篇文章暂时关闭评论。