In our production databases, all users have FAILED_LOGIN_ATTEMPTS
set to 5 on their PROFILES. After 5 incorrect passwords their account will
LOCK. When that happens in the Primary Database the value
for DBA_USERS.ACCOUNT_STATUS will be set to LOCKED.
But in Active Data Guard (ADG) environment it works a
bit differently. If the user LOCKED his account on the ADG instance he will
still get the error “ORA-28000: the account is locked” when trying to connect,
but DBA_USERS.ACCOUNT_STATUS will have a value of OPEN because the value gets
replicated from the Primary and is not a true reflection of the account status
on the Standby. Oracle created the V$RO_USER_ACCOUNT view to help with
troubleshooting the Account Status in a ADG instance.
See the output of V$RO_USER_ACCOUNT below for a user
that LOCKED his account on a ADG instance after 6 failed logins:
@>conn meyermar/x
ERROR:
ORA-28000: the account is locked
When logged in as SYS:
SQL>select DATABASE_ROLE, OPEN_MODE from
v$database;
DATABASE_ROLE
OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY
SQL> select ro.* from v$ro_user_account ro,
dba_users u where username='MEYERMAR' and USER_ID=USERID;
USERID
PASSW_EXPIRED PASSW_IN_GRACE PASSW_LOCKED PASSW_LOCK_UNLIM FAILED_LOGINS
EXPIRATION PASSW_LOCK
---------- ------------- -------------- ------------
---------------- ------------- ---------- ----------
131 0 0 0 1 6
Users LOCKED in ADG can be unlocked again with a
simple ALTER USER X ACCOUNT UNLOCK command. But this week we found that it did
not work in one of our ADG databases.
STANDBY DB> alter user puleo account unlock;
alter user puleo account unlock
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
ORA-06512: at line 1282
ORA-16000: database open for read-only access
Have not seen this error in a 11.2.0.4 ADG DB before,
the UNLOCK USER normally works in ADG, so I traced my session doing the UNLOCK:
STANDBY DB>alter session set
max_dump_file_size=UNLIMITED;
Session altered.
STANDBY DB>alter session set
tracefile_identifier=SQLERROR_10046;
Session altered.
STANDBY DB>alter session set events '10046 trace
name context forever, level 12';
STANDBY DB> alter user puleo account unlock;
The trace file showed “update to seg$” for the object GGS_DDL_SEQ,
which is a Goldengate Sequence. The Primary DB is using Goldengate with DDL
replication enabled. That is the difference between this ADG instance and all
other ADG instances where the USER UNLOCK works.
I found 2 solutions to temporary disable the
Goldengate DDL replication trigger in order to UNLOCK the USER in ADG.
either do
PRIMARY DB> alter trigger sys.GGS_DDL_TRIGGER_BEFORE
disable ;
STANDBY DB> alter user puleo account unlock;
PRIMARY DB> alter trigger
sys.GGS_DDL_TRIGGER_BEFORE enable ;
OR
STANDBY DB> alter system set
"_system_trig_enabled"=FALSE;
STANDBY DB> alter user puleo account unlock;
STANDBY DB> alter system set
"_system_trig_enabled"=TRUE;
I decided to log a SR with Oracle for the issue with
test cases and the solution as it would be great to have ADG behave the same in
all circumstances. Goldengate with DDL replication enabled should not influence
the Standby in this way. Feedback before the engineer closed the call was that
the following Goldengate Enhancement has been logged:
Enh 22198588 - DDL CHANGES IN DG BLOCKED BY BLOCKED BY
GG GGS_DDL_TRIGGER_BEFORE
