Random account locks on Oracle 23ai and a potential solution for Oracle APEX

TLDR: I’ve been running into an issue where my Oracle Base Database on Oracle Cloud running Oracle 23ai appears to be ‘automatically locking accounts at random times’. To potentially prevent one of these random locks from stopping APEX from working, try this unsupported but working adjustment to your APEX_PUBLIC_USER account: alter user apex_public_user account unlock no authentication;

The background: My database is what is currently called on Oracle Cloud, an “Oracle Base Database”. Unlike the Oracle Autonomous Database, where you get a pluggable database in a container database that someone else manages, here you get full access to the database file system and full access to everything about the database (root container, full sys user access, etc.). I say “currently called” because we actually put this database on Oracle Cloud way back in Sept of 2021. That’s when this database was migrated from an on-premises Oracle Database Appliance to Oracle Cloud.

Oracle Cloud has changed a bunch since then, but overall, I couldn’t be happier with the migration. With Oracle Base Database, you “let” Oracle manage the software locations and database locations (Oracle uses Automatic Storage Management for the database and fast recovery area storage). Patches and upgrades (we started with 19c, but are now on 23ai) are straightforward and controlled at your own pace, implemented by simple choices in the Oracle Cloud UI.

For many years, this database “just worked”. The business ran its processes, and the APEX application we built for them just did its thing. On July 22nd, I got a call from the business saying “APEX isn’t working”. When I went and looked, the APEX_PUBLIC_USER account was locked. This is strange because there wasn’t a reason for the account to be locked. Nobody did anything. The database profile for the APEX_PUBLIC_USER has a password life time of unlimited, so it wasn’t a profile thing. I unlocked the account, APEX started working again, and life was good. An investigation into the unified audit trail didn’t show anything. This was a “mystery”. Anyone in tech would agree that a mystery isn’t good.

On August 11th, I got the same call. Again, the APEX_PUBLIC_USER account was locked. I again unlocked it. This time I did a bigger investigation with a coworker. He’s been struggling with the same random locking behavior for the APEX_PUBLIC_USER in his DEV, TEST, and PROD environments for the last 4 months (he’s had many Oracle SRs open and closed on this while he’s been bounced around various teams within Oracle, and his random locks have happened much more frequently than mine). As we looked at things, we realized that there is an amount of correlation between database patches being applied and accounts getting locked. It’s not exact, but here are some of the queries that we looked at:

  select cdb$name as container -- Awesome hidden colum on CDB_ views!
       , target_build_description
       , action_time 
    from cdb_registry_sqlpatch 
order by action_time desc;

  select username
       , cdb$name as container
       , lock_date
       , last_login
       , created
       , cu.* 
    from cdb_users cu 
order by cu.lock_date desc nulls last;

select cdb$name as container
     , cp.* 
  from cdb_profiles cp
 where resource_name = 'INACTIVE_ACCOUNT_TIME';

Obviously, if you don’t have access to the root container, you can change the above queries to use the DBA views in your own pluggable (or non-container) database if you eliminate the pdb_name column.

Something very interesting was that there were a LOT of accounts getting locked at the “same time”, but that time was different for different pluggable databases in the same container database.

I’ve got two “opportunities for future enhancement” logged against the APEX product and APEX documentation. This is the current slide in my latest (award-winning!) APEX & ORDS for DBAs and System Admins presentation (an earlier version of this can be found on YouTube).

A while back, I had shared that with my coworker, and he had implemented it in his dev and test environment:

alter user apex_public_user account unlock no authentication;

Since implementing this, he has not had the locking issue for the APEX_PUBLIC_USER his 23ai environments.

I went ahead and implemented this in DEV, TEST, and PROD. We’ll see what happens, and if any of the SRs my coworker has filed with Oracle Support get an actual resolution, I’ll update this post!