UNLOCK user in ADG

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