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

ACCOUNT_STATUS = EXPIRED(GRACE)

Users are identified within the app, so all DB connections are done through 1 schema. We created a profile for this schema where the password will never expire. App support has a manual process to change the password every 60 days, but Oracle must never be allowed to expire the password automatically.

The problem is that I found the ACCOUNT_STATUS for the main schema to be EXPIRED(GRACE) and that the EXPIRY_DATE is 3 days away.

This schemas profile was changed by accident, and rectified within a few hours. But the damage was done the ACCOUNT_STATUS still showed EXPIRED(GRACE).

I found an excellent blog post by Robert Geier (http://blog.contractoracle.com/2009/11/analysis-of-oracle-password-expiry.html) to explain the situation. Here’s a abbreviation of his explanation: password expiry is controlled by the PASSWORD_LIFE_TIME profile limit, is activated at login, and is reset by a password change. Changing the profile will not change aud$.ASTATUS.

SQL> select USERNAME, PROFILE, ACCOUNT_STATUS from dba_users where username = ‘QWERTY’;

USERNAME PROFILE ACCOUNT_STATUS

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

QWERTY QWERTY_NONEXPIRY EXPIRED(GRACE)

So even though the profile was changed back to QWERTY_NONEXPIRY the ACCOUNT_STATUS was still EXPIRED(GRACE). The only way to reset the ACCOUNT_STATUS back to OPEN is to change the password, because only a password change will reset aud$.ASTATUS. But a password change will give a ORA-28007 because of the current profile limits for PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME gets violated.

SQL> select password from sys.user$ where name='QWERTY';

PASSWORD

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

87FE2DD49B2882B2

SQL> alter user QWERTY identified by values '87FE2DD49B2882B2';

alter user QWERTY identified by values '87FE2DD49B2882B2'

*

ERROR at line 1:

ORA-28007: the password cannot be reused

So I had to create a new temporary profile with the required settings to allow me to change the password to be the same as the current password:

SQL> create profile tmtmp limit PASSWORD_REUSE_MAX UNLIMITED PASSWORD_REUSE_TIME UNLIMITED;

SQL> alter user QWERTY profile tmtmp;

SQL> alter user QWERTY identified by values '87FE2DD49B2882B2';

User altered.

SQL> alter user QWERTY profile QWERTY_NONEXPIRY;

SQL> select USERNAME, PROFILE, ACCOUNT_STATUS from dba_users where username = 'QWERTY';

USERNAME PROFILE ACCOUNT_STATUS

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

QWERTY QWERTY_NONEXPIRY OPEN