附：
参考文档： 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.
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;