ANBOB™

今天一套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

打赏

微信扫一扫，打赏作者吧～