Minding your own business as one does, you go to startup ORDS and are suddenly informed that one or more of your connection pools are invalid!

The pool named: |default | is invalid and will be ignored

This means there are no database connections for this database being created.

If the pool is invalid, then ORDS can’t connect to the database. And if ORDS can’t connect to the database, then you will see things like your APEX apps not load, your REST APIs not work, or your MODPLSQL apps not come up.

In this post, I want to give you a set of logical steps you can follow to figure out what’s going on, and how to fix the problem(s)!

Step 1: Read the error message(s)

Sometimes error messages can be vague and even misleading. But more often than not, the errors generated on startup up ORDS are pretty straightforward. Let’s look at scenario #1:

2023-01-10T21:45:45.391Z WARNING     The pool named: |default|lo| is invalid and will be ignored: ORDS was unable to make a connection to the database.  This can occur if the database is unavailable, the maximum number of sessions has been reached or the pool is not correctly configured. The connection pool named: |default|lo| had the following error(s): ORA-28000: The account is locked.

ORA-28000 isn’t vague. It’s telling us that our database account is locked.

Step 2: Go into the database and fix said error(s)

My default ORDS connection pool is going against my ORCLPDB1 database, which is a PDB, and I have a PDB install.

With an administrator account e.g. SYSTEM, I can run the following query:

 SELECT *
   FROM dba_users
  WHERE account_status = 'LOCKED'
    AND username LIKE '%ORDS%' -- optional
On older installs of ORDS you may have other accounts locked such as APEX_PUBLIC_USER.

This is a simple fix, you can use the SQL Developer GUI to unlock this account, or you can fix it quickly with SQL.

In the GUI, go to ‘Other Users,’ find the user account, and right-click. Then select ‘Edit User,’ and uncheck ‘Account is Locked.’

That runs this SQL –

ALTER USER "ORDS_PUBLIC_USER"
ACCOUNT UNLOCK ;

But Jeff, I’m not sure which database or which user is having problems?

Great question.

Let’s say you’re on an older version of ORDS, or you’re not super familiar with the ORDS plumbing. Before version 22.1 of ORDS, having a database configured for REST APIs and APEX could have as many as 3-4 connection pools established.

Let’s say you’re on an older version of ORDS, like v18.4.

After you’ve heard me chastise you for not having upgraded, you’re ready to just fix the problems already.

In version 18.4, we ‘burn into the WAR file’ the location of the ‘configdir.’

So instead of running

c:\ORDS\21.4>java -jar ords.war standalone

Run

c:\ORDS\21.4>java -jar ords.war configdir
2023-01-10T22:08:10.584Z INFO        The config.dir value is C:\ORDS\ords_214_config

This will return back the configuration directory this war file is using when you startup ORDS. I’ve seen machines with several ORDS ‘installs’ and config directories. It’s easy to get confused and actually update the wrong files or even start the wrong ORDS, so this can help.

Ok, so I know to go poke around the c:\ords\ords_214_config directory.

Docs: ORDS 21.4 and earlier configuration directory/file layout

If I open the defaults.xml file, it’ll show me which database we’re supposed to be working with.

If you like ST:TNG, check out the LCARS them for VS Code.

And in the conf folder, we’ll find the apex_pu.xml file. This is the old default xml file for the first ORDS connection pool.

We can see the database account for our pool is indeed ORDS_PUBLIC_USER.

If you’ve configure ORDS for APEX, you WILL have other pools, at least APEX_PUBLIC_USER. You’ll see corresponding XML files with those usernames and passwords.

APEX User Accounts/Pools

  • APEX_PUBLIC USER – ORDS uses this account to invoke APEX PL/SQL gateway calls
  • APEX_REST_PUBLIC_USER – executes APEX defined REST APIs
  • APEX_LISTENER – queries APEX defined REST API definitions

You’ll see corresponding pools called apex_pu, apex_rt, apex_al, etc.

Advice: If you’re going to reset passwords for multiple accounts, do it all at once. It’s easy to fat-finger the passwords into the XML files or the CLI if you go one-by-one. Reset all of them in the DB, then update all of the config files in one, clean sweep.

Me after screwing this up with a customer and then watching Kris come in and do in like 5 minutes.

Let’s say you’re on a newer version of ORDS, like v22.4.

The configuration layout is different from what we just looked at. The configdir is no longer burned into the WAR file. And we have consolidated all those multiple pools into a single one for each database.

That’s right, everything starts as ORDS_PUBLIC_USER.

As for the configuration directory, there should be no more ambiguity. You EITHER have a ENVVAR defining it, or you pass it on the ‘serve’ command to the ords script/exe.

For example,

c:\ORDS\22.4\bin>ords --config c:\ords\23c_config serve

So that tells us where to look at the config files.

They layout is a good bit different. In one file we have everything we need to know about the connection, including the pools’ USERNAME.

How to get to the database, and which account to connect. That’s what is used for the connection pool, default.

If you notice the ‘wallet’ directory, that’s where the Oracle database account’s passwords is now securely stored.

Ok, we ‘fixed’ the account, let’s startup ORDS

Doh!

Ok, so now we’re seeing this.

2023-01-10T22:22:59.514Z WARNING     The pool named: |default|lo| is invalid and will be ignored: The username or password for the connection pool named |default|lo|, are invalid, expired, or the account is locked

Well, we ‘know’ the account is locked anymore, but we should check anyway.

Check!

Ok, so what’s wrong?

Step 1, Again: Read the error message

The username or password is invalid. Ok, we know the username, ORDS_PUBLIC_USER, is good. That account is IN the database.

But can it connect?

Let’s find out!

Ok, so whatever I think the password is, it’s wrong.

Step 2: Fix it

This is accomplished in two steps:

  1. update the password in the database
  2. update the passwords in the ORDS config file

Let’s do the database first.

Do, what?

Ok, so I ran into this problem the other day with a customer. While they had a PDB, their install was actually a CDB (Container) install. That means that ORDS_PUBLIC_USER is defined in the Container, and not the pluggable.

The ‘db.servicename’ parameter in this case would be pointing to the CDB, that would be your first giveaway.

Once you know which instance to connect to, change the password.

USE BETTER PASSWORDS.

Ok, now we can update the password in the ORDS config for this pool/user.

I’ve already written that blog post.

I’ll show you again for 22.1 and higher though. And then we’ll start ORDS and ‘cross our fingers.’

ORDS comes up, and the pool is established!

If you need another check, query gv$session in the database. If you don’t see pool’s database account, you’ve still got problems.

Check, check, and check.

Well, we’re not quite done. Yet.

Step N: Read the Warnings.

2023-01-10T22:39:50.747Z WARNING     *** jdbc.InitialLimit in configuration |default|lo| is using a value of 3, this setting may not be sized adequately for a production environment ***

Let’s be clear, this is NEVER good for a production environment. It is good for Jeff’s laptop and just Jeff using it for something like writing blogs about ORDS and using SQL Developer Web? Yeah, probably.

It’s not a coincidence that the setting is set to ‘3’ and we see 3 sessions in our database for ORDS_PUBLIC_USER.

You need to profile your application. Based on concurrent users, based on good your APEX apps are, how fast your SQL and PL/SQL are – you need to add connections, or ROOM for more connections in your pool(s).

I say pretty much the same thing in a much more official place, in our Best Practices Docs.

You can find some advice on finding that happy place here.

But wait, I didn’t make any changes, how did this break in the first place?!?

This can usually be explained by one of the following:

You DID make a change, you just didn’t realize it would affect the system in the way it did.

The user account’s PROFILE has a rule to LOCK accounts after X failed login attempts. Someone, or some program tried to access the database with the wrong user and password combo.

The user account’s PROFILE has a rule to EXPIRE PASSWORD after X days.

Of the 3 items, the 2nd one is one to keep an eye on. If it were a program or scheduled job that has the password wrong, and it’s still running, your account is probably going to get logged again.

Have a look see at the FAILED LOGINs audit trail for details.

SQL Developer Web: The OS USER, date, & error msg should hint the troublemaker.
thatjeffsmith
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

2 Comments

  1. Avatar

    Very helpful. I have had several issues like this in the past and it is not always easy to troubleshoot. But if ORDS can generate the messages “ORA-28000: the account is locked”, would it be that difficult then to put also in the error message what user it is trying to connect with? It would save a lot of steps from your article above. And especially, since the architecture of the connections has changed somewhat over the different versions of ORDS, this would be so helpful.

    • thatjeffsmith

      That’s not a bad idea, I can bring it up to see if there would be any security issues with that. I can’t think of any, but will let you know.

Write A Comment