ORDS installs & upgrades, the TNS edition

I’ve demonstrated both an ORDS install and upgrade scenario here before, but we’ve been making some tweaks lately in order to make things easier, simpler, better. So I thought I’d take a hot minute to show you what that looks like.

If you’re looking to see how to fix a bad password with ORDS, this is the post you want.

Installation environment

I have no existing ORDS configuration.

I have a TNSNames.ora file, which ORDS will find via my existing TNS_ADMIN environment variable value.

I have Java 17 on my machine.

Starting the install

I’m going to decide where I want to keep my configuration settings for ORDS, and I’m going to supply that on the install command using the –config flag.

c:\ords\24.1.1\bin>ords --config c:\ords\new_config install

Many of you will have TNS files with many dozens, if not hundreds, of entries. In this case, we’ll print like the first 9 of those for convenience.

I’m going to go with option [3]

If I had chosen option [S] instead, I would go back to being prompted for the machine/network name, port number, and service name of our database.

In older versions of ORDS, you’d be given a whole slew of follow-up prompts. For the interactive installer, we now present you a list of all the defaults, and you can opt-in to change things as needed.

Now, if I decide I need to update or change one of the defaults, I can still do that here, let’s say I want port 8181 instead of 8080.

Once I choose ‘A’ for ‘Accept and Continue,’ ORDS will kick off the installation or upgrade process.

ORDS then does its work, and prints out everything it does to screen, and will also write out the information to a log file in your ORDS folder.

Install & Serve log

The output looks a bit like this –

Connecting to database user: ORDS_PUBLIC_USER url: jdbc:oracle:thin:@TNS-FREE-PDB?TNS_ADMIN=C:\\Users\\thatjeffsmith\\Documents
The setting named: db.connectionType was set to: tns in configuration: default
The setting named: db.tnsAliasName was set to: TNS-FREE-PDB in configuration: default
The setting named: db.tnsDirectory was set to: C:\Users\thatjeffsmith\Documents in ...
The setting named: security.requestValidationFunction was set to: ords_util.authorize_plsql_gateway in configuration: default

2024-06-24T13:11:04.877Z INFO        Updating ORDS_PUBLIC_USER database password in FREEPDB1
------------------------------------------------------------
Date       : 24 Jun 2024 13:11:04
Release    : Oracle REST Data Services 24.1.1.r1201228

Database   : Oracle Database 23ai Free
DB Version : 23.4.0.24.05
[*** script: ords_change_password.sql]

PL/SQL procedure successfully completed.

... OK at this point, ORDS is switching over to SERVE from INSTALL

2024-06-24T13:11:07.717Z WARNING     *** jdbc.MaxLimit in configuration |default|lo| is using a value of 10, this setting may not be sized adequately for a production environment ***
2024-06-24T13:11:07.718Z WARNING     *** jdbc.InitialLimit in configuration |default|lo| is using a value of 10, this setting may not be sized adequately for a production environment ***
2024-06-24T13:11:16.366Z INFO

Mapped local pools from C:\ords\new_config\databases:
  /ords/                              => default                        => VALID

..THIS IS A GOOD SIGN! It means the connection pool is created and available 

2024-06-24T13:11:16.562Z INFO        Oracle REST Data Services initialized
Oracle REST Data Services version : 24.1.1.r1201228
Oracle REST Data Services server info: jetty/10.0.20
Oracle REST Data Services java info: Java HotSpot(TM) 64-Bit Server VM 21.0.3+7-LTS-jvmci-23.1-b37

When you ask someone at Oracle for help with ORDS, we’ll often ask about this ‘log’ – this stuff above is what we’re after. It’ll show us why something isn’t working, at least from the ORDS program perspective.

ORDS has finished the installer and has started up in standalone mode

ORDS might be running, but that doesn’t mean the database pools are ‘healthy.’

Apart from seeing your pool (in this case, default), shown as VALID, another way to confirm the connection pool is in the expected state, is to look for ORDS_PUBLIC_USER shown in V$SESSION for your database –

So, not only is ORDS running, but it’s connected to your database. This is another thing I ask users to test when they come to me for help.

But, why is ORDS ‘hanging at the end?’

I’ve gotten this question a few times recently.

At the end of the installation, ORDS goes from ‘install’ mode to ‘serve’ mode – that is, the ORDS process is now running the ‘standalone’ webserver, and it’s listening on the machine at :8181/ords

If you were to hit ctrl+C in your terminal, the ORDS process (stanalone webserver), would be shutdown. You would then need to do a follow-up ‘ords –config c:\… serve’ command to start it back up.

Testing ORDS, PT1: enabling a schema

First, ‘REST Enable’ a SCHEMA in your database. This will let you do two things:

• login to SQL Developer web as that user
• publish REST APIs in that schema

In a terminal, login as the desired user, then run this code block:

If this fails, there’s a good chance your ORDS install didn’t complete successfully, and you need to go scour the log for errors/warnings.

ORDS is a public synonym to ORDS_METADATA.ORDS – a PL/SQL package, and ENABLE_SCHEMA is a procedure with these default parameters:

/**
   * Enable/disable the specified schema for ORDS access.
   * Only DBA users can enable/disable a schema other than their own.
   *
   * @param p_enabled  True if ORDS schema access is to be enabled, otherwise disabled.
   * @param p_schema   The name of the schema.
   * @param p_url_mapping_type     URL mapping type, Valid values:      
       'BASE_PATH','BASE_URL'.
   * @param p_url_mapping_pattern  URL mapping pattern.
   * @param p_auto_rest_auth       True if authentication is required prior to actioning by default, otherwise no authentication is required by default.
   */
  PROCEDURE enable_schema(
      p_enabled             IN boolean DEFAULT TRUE,
      p_schema              IN ords_schemas.parsing_schema%type DEFAULT NULL,
      p_url_mapping_type    IN ords_url_mappings.type%type DEFAULT 'BASE_PATH',
      p_url_mapping_pattern IN ords_url_mappings.pattern%type DEFAULT NULL,
      p_auto_rest_auth      IN boolean DEFAULT NULL);
...

Testing ORDS, PT2: Logging into SQL Developer Web

On the machine where ORDS is running, open a browser to the correct port, and to the /ords resource. On my Windows machine that would be http:localhost:1521/ords

If you want to try from a different machine, obviously use the network name, and ensure there’s no firewall preventing you to access that machine.

If you don’t have a browser, you can of course cURL the endpoint.

Testing with cURL

Assuming you have either the landing page or APEX enabled, you can curl the base ords endpoint, but be sure to use the -L flag, because that will need to follow the redirect to the actual page/resource being served.

Testing in the browser

Instead of using cURL, we just put the same URL in the browser. Assuming your machine can reach the machine and port where ORDS is running and listening for your HTTP or HTTPS requests, it should look like this –

So what are we seeing above?

1. Going to /ords
2. Being redirected to the landing page
3. Clicking the SQL Developer Web link
4. logging in as user HR
5. running a query

All of this working tells us that, the ORDS webserver is running, it’s correctly configured for our database, and the HR schema is good for REST APIs.

A note or warning on relying on your tnsnames.ora file for the pool configurations

If that file moves or changes, your could ‘break’ your pools. If you’re expecting ORDS to see that the TNS_ADMIN value has changed for it’s runtime and startup up pools, think again.

Note these two lines in your pool.xml file –

<entry key="db.tnsAliasName">TNS-FREE-PDB</entry>
<entry key="db.tnsDirectory">C:\Users\thatjeffsmith\Documents</entry>

If you see ORDS starutp errors around the pools being invalid, keep an eye on this. The fix would be obvious, or trivial even, update the pool config to point to the correct file and/or service name.

Are you done?

No. If you’d downloaded the ords zip and ran the interactive installer, as soon as that process dies, so does ORDS. You’ll need to create a service on your machine that at startup time, launches ORDS, and sends the std out & std err messages to a rotating log file. If you’ve used YUM to do an install, our RPM has sorted this for you.