ORACLE主备库用户状态为OPEN,但登录报ORA-28000: the account is locked

郑全

一个奇怪的问题，主库一个用户修改了密码，同步到备库后，导致备库该账户被锁无法登录，
但备库状态也为OPEN，就是无法登录。

后，重启备库后，问题解决。

参见：

Doc ID 2440122.1

Account status in dba_users show open but connection on Standby fails as 'ORA-28000: the account is locked' (Doc ID 2440122.1)

In this Document

Symptoms

Cause

Solution

References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later

Information in this document applies to any platform.

SYMPTOMS

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

Account status in dba_users show open but connection fails as 'account locked'.

When connecting to the user <user1> via sqldevelope or sqlplus, we are getting user account is locked. This is for standby ADG database

SQL> select username,account_status from dba_users where username='USER1';

USERNAME

--------------------------------------------------------------------------------

ACCOUNT_STATUS

--------------------------------

USER1

OPEN

SQL> SQL> conn USER1

Enter password:

ERROR:

ORA-28000: the account is locked

SQL> SELECT name, ctime,ptime FROM sys.user$ WHERE name = 'USER1';

NAME

--------------------------------------------------------------------------------

CTIME PTIME

--------- ---------

USER1

12-JAN-18 03-AUG-18

SQL> select open_mode,controlfile_type from v$database;

OPEN_MODE CONTROL

-------------------- -------

READ ONLY WITH APPLY STANDBY

CAUSE

Account status remains OPEN after a user's account is locked on a active standby database.

The account status reflects that of the account on the primary database. So the account status still shows OPEN even though the account is locked.

This is discussed in bug 16345308 and closed as 'Not a bug'.

SOLUTION

The account status cannot be updated on a readable standby.

There are 2 related "issues" here:

1) On the standby, if the logon fails n times in succession then subsequent logon attempts fail with ORA-28000.

If the standby database is then restarted the account is unlocked.

The reason for this is that the account status is kept persistently in

the SYS.USER_ASTATUS_MAP table. However, because this is a (read-only)

standby database we are unable to update this persistent status because

we are not able to update this table. Once the standby has been restarted

we have no way of knowing the status of the account other than by looking

at SYS.USER_ASTATUS_MAP which shows the account as OPEN (because the

account is OPEN on the primary). This is why we can logon successfully

after restarting the standby.

2) Because on the standby we are unable to update SYS.USER_ASTATUS_MAP, the

account status will reflect the status of the account on the primary.

This is why, in this test, the status remains OPEN.

This is not a bug, but a limitation due to the standby being a read-only database.

Note: on a 12.2, You can verify on a ADG database, by querying the view 'v_$RO_USER_ACCOUNT'.

if the account is locked, you can see an entry in this view. When the user is unlocked, the row would disappear.