Troubleshooting ORDS startup errors, the pool is invalid.

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

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

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.

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.

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.

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.

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

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.

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!

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.

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.

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.’

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.

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.