Category Archives: ORDS

August 13, 2025

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

By Rich Soule

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!

Leave a comment   |  tags: , , , , , , , | posted in Code, Oracle APEX, Oracle Database, ORDS, SQL

October 5, 2023

APEX & ORDS for DBAs and System Admins is now live on YouTube

By Rich Soule

I’ve mentioned this presentation before on my blog and now I’ve recorded the entire thing including the bonus content. At the various user groups like RMOUG and Kscope, I was only given an hour and just told folks to “Read the rest of the presentation.” The media team at Insum (thanks Marc and Lauren!) and I bounced back and forth on doing multiple 1-hour events or jamming everything into a single presentation. Eventually, we decided to get it done in one go. It’s long… 2 hours and 6 minutes long. Apparently, I was enjoying myself so much that I didn’t even realize that, and if you do watch until the end you’ll notice that I thought it was actually 1 hour long, not 2.

I’m pretty pleased with the presentation and I’ve got a lot of good feedback from folks. If you want to use APEX and/or ORDS and your DBAs and/or System Admins don’t want to implement it, this presentation is my gift to you. Any DBA or System Admin should be able to learn exactly what they need to know to run APEX and ORDS in a production-ready configuration.

I’ve linked to this before and the link is in the description on YouTube, but here’s another direct link to all the code on every slide: https://github.com/RichardSoule/APEXandORDSforDBASandSysadmins

1 Comment | posted in Code, Conferences, Linux, Oracle APEX, ORDS, systemd

July 15, 2023

Kscope23 Recap

By Rich Soule

I’ve been back for a while but thought I’d post some plans for my Kscope content and talk about the conference itself now that it’s in the bag.

I arrived late Friday night and things were pretty quiet. I spent some time in the bar trying various bourbons and whiskeys before calling it an early night.

The next morning I ran into Connor McDonald (check out his amazing content on Youtube). He mentioned that he was planning to go visit the famous Tom Kyte (the original creator of Ask TOM, and author of one of the very best Oracle books, Expert Oracle Database Architecture) but apparently Tom fell off a ladder and broke some ribs. Hopefully, Tom will recover soon. A bit later I joined the team going to the community service day event. They gave us matching shirts to wear and we boarded buses to Mile High Behavioral Healthcare and did a bunch of cleaning, gardening, and painting. I was on the painting crew and painted a gazebo and a few tables with a great team of folks. I ended up buying MHBH some picnic tables after we went to pick up one of their existing picnic tables and it essentially disintegrated. When one of the guests of MHBH asked me “How do I get one of those shirts?” I gave him mine. While the shirt was cool I’m sure he’ll get more use out of it than I would have.

By Sunday the rest of the Insum team had started to show up and we had a great time re-connecting.

Both of my main presentations were on Monday. Thankfully I’d delivered both before, so I was really comfortable with the content although it was going to be my first time delivering my “You know you might have a bad data model when…” presentation in just thirty minutes (I’d been given one of the 30 minute sessions for this presentation). It’s a bunch of slides with the type of queries that you would either see in your applications or queries you can run against your application schemas to see if there might be opportunities for enhancements. Upon the advice of Steven Feuerstein, another member of the Insum team, instead of starting with the theory of normalization, I started with the actual queries. Since the theory portion would take about 5 minutes, my plan was to cut it off at 25 minutes and jump to the theory. I set an alarm on my phone for 25 minutes, let the audience know what I planned to do, and dove in. When I finished my queries section, I glanced at my watch to see how much time I had left. As I was glancing at my watch, the alarm on my phone went off! It was perfect and I got a bit of applause from the audience. I finished off the theory portion and then got a lot of good feedback including from some of the very experienced folks in the audience (Oracle ACE directors, etc.).

Later in the day, I did my “APEX and ORDS for DBAs and System Admins” presentation. While I’ve delivered the content at other events before, I always update my presentations to the latest and greatest software, and with the very frequent updates of both ORDS and APEX I had to update everything a few days before the conference.

This presentation is actually about 2 to 4 hours of content, but I only had an hour to deliver it. Basically, I cut the presentation in half and gave folks a preview of what would be in the 2nd half if I had more time. I also went through the first half of the presentation pretty quickly. The premise of the presentation is that people often come to Kscope and they see all these really cool APEX solutions, but then when they go back to their own IT department the DBA or System Admin just says “No.” The reason for the “No” can be anything they feel like coming up with at the time (“Security”, “Too complicated”, “I don’t know how to do it”, etc.) but the conference attendee doesn’t get the very cool APEX and/or ORDS features that they saw at the conference. To solve this problem, I broke the first half of the presentation into three sections.

  1. Install the APEX database component and patch it in the database.
    This section shows that APEX is just a component of the database (it shows up in the dba_registry view) and, by showing what is happening in the database we see that there are NO security changes in the database when you do this. It also showed how the installation and patch take under 10 minutes. On my boxes, the APEX install takes usually takes under six minutes and the patch takes under 30 seconds.
  2. Now that you have the APEX component in the database, you have access to bunches of very cool APIs. Some of those APIs enable you to reach out of the database to access things on remote servers if the DBAs allow it. I show exactly how to enable this and how to create and manage an Oracle TLS wallet (yes, people and Oracle often refer to this as an SSL wallet, but we really stopped using SSL back in the 1990s. It’s been TLS for over 20 years… and it really is a TLS wallet). Wallet management can be very tricky and I can’t tell you the number of times I’ve seen very bad wallet setups on Oracle servers. I explain the various wallet types (TDE wallet, SEPS wallet, XDB wallet, and TLS wallet) and show how I build and maintain them.
  3. Finally, we get to the last step which is setting up and configuring ORDS for a production-ready deployment. While Kris Rice, the Oracle lead for the ORDS team, disagrees with me, I really don’t like deploying ORDS with /ords/ in the URL. As Sir Tim Berner’s Lee explains, cool URLs don’t have the technology used to deploy them in the URL. Yes, that link is from 1998. I figure when Kris is knighted by the King he can tell me I’m wrong. I also show how to maintain ORDS over time. I show how to create the best

At this point, I’ve covered the first half of the presentation but an hour has passed and I have to just show folks what the next three sections are:

  • Adding a database resource manager plan that ‘just works’ for APEX deployments.
  • Building a Systemd service to run ORDS.
  • Deploying the ORDS service as a ‘regular’ operating system user instead of the root user. The root user can, of course, deploy ORDS on port 443 (the default HTTPS port) trivially, but some folks would prefer not to run ORDS as root, so I show how to do this.

My plan is to take all of that content and publish it here (update: The entire 2-hour APEX & ORDS for DBAs and System Admins is now live!), but it will take a while. Life is very busy and I’m not exactly sure when I’ll be able to finish it all. Until then, all of the code and examples from my both presentations can be found in the following Github repos:

Finally, I was a co-presenter with Cary Milsap of Method R in a presentation on tracing APEX. It was also well-received and sparked a lot of interesting discussions.

When I do get the time to post everything from my presentations I’ll try to remember to come back and here and link it in. Update: Here’s the link to the whole presentation from start to finish!

Until then, happy DBAing, System Admining, APEXing, and ORDSing.

Leave a comment   |  tags: | posted in Code, Oracle APEX, ORDS, SQL