On oracle ADG instances upto 11.2.0.3, if an oracle user is locked it wont get unlocked despite after reaching "PASSWORD_LOCK_TIME" time which we have mentioned in user profile.
Reason you can refer the doc "Doc ID 1600401.1" from metalink
Upto 11gr3, we need to explictly login to the Standby/ADG database and unlock this user.
But from 11.2.0.4 ADG this issue has been fixed, user will be unlocked automatically after "PASSWORD_LOCK_TIME" time and after successful login with the correct password for the user.
We no need to explictly login with SYSDBA unlock that user on Standby or ADG instances.
Locked user info can been seen from view "v$RO_USER_ACCOUNT" until its been unlocked on ADG instances.
After successful login with the correct password, info from v$RO_USER_ACCOUNT will be cleared.
Query to Check Oracle Locked User Details on ADG:
select a.USERNAME, a.USER_ID, b.PASSW_LOCKED, b.FAILED_LOGINS, b.PASSW_LOCK_TIME from dba_users a, v$RO_USER_ACCOUNT b
where a.USERNAME='TEST' and a.USER_ID=b.USERID;
Reason you can refer the doc "Doc ID 1600401.1" from metalink
Upto 11gr3, we need to explictly login to the Standby/ADG database and unlock this user.
But from 11.2.0.4 ADG this issue has been fixed, user will be unlocked automatically after "PASSWORD_LOCK_TIME" time and after successful login with the correct password for the user.
We no need to explictly login with SYSDBA unlock that user on Standby or ADG instances.
Locked user info can been seen from view "v$RO_USER_ACCOUNT" until its been unlocked on ADG instances.
After successful login with the correct password, info from v$RO_USER_ACCOUNT will be cleared.
Query to Check Oracle Locked User Details on ADG:
select a.USERNAME, a.USER_ID, b.PASSW_LOCKED, b.FAILED_LOGINS, b.PASSW_LOCK_TIME from dba_users a, v$RO_USER_ACCOUNT b
where a.USERNAME='TEST' and a.USER_ID=b.USERID;
