Install ORDS & SQL Developer Web for Multitenant, Part One

When I say ’22,’ I mean 22.2 – that’s the current version as of this blog post. What I’m talking about here will be valid for version 22.1 and higher, more or less.

When I say for ‘Multitenant Instances,’ I mean we are going to :

• point the installer to a Container Database
• setup each and every pluggable database to share a single ORDS connection pool
• have SQL Developer Web available for all of my PDBs

Multitenant Architecture & Licensing Considerations

The Multitenant architecture was introduced in 12c. The TL/DR; description is that you basically have a single instance of Oracle running where resources can be shared across one or more database services. This really cuts down on the amount of resources required to run multiple databases on a single machine.

As of 19c, you’re allowed to have up to three of these pluggable databases in a given instance of Oracle without having to license the ‘Multitenant Option.’

For all offerings, if you are not licensed for Oracle Multitenant, then you may have up to 3 user-created PDBs in a given container database at any time.

Oracle® Database Database Licensing Information User Manual, pg 11

As of version 21c of Oracle, you no longer have the option of running the old ‘classic’ vs multitenant architecture. It’s just a matter of whether you choose to run one database or many per Oracle instance.

The endgame

When launching SQL Developer Web, I want to be able to connect to ANY of my PDBs from a single ORDS install, and a single connection pool.

What you need to know

There will be ONE connection pool. Every PDB will be running the same version of ORDS, and have the same options. The PDB$SEED will be setup such that if we create a new PDB, it’ll be good to go for ORDS/SQL Developer Web.

Why you might not want to do this –

• You need control / flexibility

Maybe PDB3 needs 40 connections but PDB35 needs 300. Maybe only 4 out of 10 of your PDBs require ORDS.

Maybe application 123 has a requirement for ORDS 21.4 but application 456 has to have the latest and greatest, always.

In general I recommend folks do PDB level installs. Where I waver on that is if you want to use the PDB Lifecycle Management REST APIs and/or you have a LOT of PDBs you need to enable for ORDS.

Let’s go already!

Open the Docs, go to section 6.

Using the Multitenant Architecture with Oracle REST Data Services

My Rig: 2018 Mac Mini with Oracle
VirtualBox 19c (19.c) Multitenant Install
Oracle Java 17 (17 update 3)

Download ORDS

Here’s the link to 22.2, no sign-in or click-thru required.

If you haven’t already downloaded Java, go and do that now.

Unzip ORDS to it’s new home.

I’ve gone with

 /opt/ords/22.2

OR, if you’re on Linux, then you can also grab our RPM via Oracle Yum Repository.

Figure our where you’re going to stash your ORDS configuration files.

I’m going to store my configuration folders and files here:

/opt/ords/config

Remember this folder. You do NOT have to create it in advance, but it won’t hurt if you do. Just DO NOT put this inside the ORDS product directory structure. We’ll warn you, and you’ll make it harder on yourself when it comes to do the next upgrade.

Run the installer (interactive)

I’m assuming this is an ad-hoc thing for you, and not something you’re wanting to script/automate for deployments. You CAN and SHOULD do that, but that’s not this blog post.

cd /opt/ords/22.2/bin
./ords --config /opt/ords/config install

Answer the prompts

You’re going to be asked some questions. Answer them. You can probably just hit the Enter/Return key to take most of the defaults, mostly.

As seen in the screen capture, ORDS created the configuration folder for me. Going forward, ORDS will be storing all of the information needed for the application to run there.

ORDS has asked me about my database, and I went with a basic connection, connecting as SYS.

The service name is ‘orclcdb’ – that’s a CONTAINER instance of Oracle, and NOT a pluggable database.

Answer more questions

ORDS is saying, ‘Hey, this is a CDB! Here are ALL the pluggable databases we are going to setup, you ok with that?’

If the PDB isn’t open, ORDS will open it. We’re going to have your SYS credentials, so we can do what we want.

If this was Zork, option [1] here is picking up the knife.

Answer more questions

Answer a few more questions

You’re going to be asked the ‘standalone webserver ORDS’ questions, like do you want to run HTTP or HTTPS, what port do you want, and do you want ORDS to startup and run after we’re done installing.

You’ll want to do HTTPS for real installs, but for dev and sandboxes, HTTP is probably OK. If real data is being worked with, HTTPS is the only way to go. You’ll need a proper SSL Cert from say, Let’s Encrypt. Don’t use the self-signed cert that ORDS generates.

Enter a number to configure and start ORDS in standalone mode
    [1] Configure and start ORDS in standalone mode
    [2] Skip
  Choose [1]: 

Pick option [2].

Watch the magic

ORDS will now do the installer work. It’s going to scroll off the screen quite a bit, and we’ll end up with something close to this.

What all did it do?

• Created our config folders
• Installed ORDS_PUBLIC_USER to the CDB
• Installed ORDS_METADATA to all of the PDBs including the PDB$SEED
• Created a connection pool using ORDS_PUBLIC_USER to the CDB
• Put the ORDS_PUBLIC_USER password in a java wallet

Making the PDBs addressable

This step is critical!

6.3 Making All PDBs Addressable by Oracle REST Data Services (Pluggable Mapping)

Log into your CDB and run this command:

I’m running this on my Mac, I won’t have databases of the same name colliding into each other, so it’s just ‘orcl’ for me vs say ‘orcl.thatjeffsmith.com’

The domain is NULL, but we still need to tell ORDS about this.

We’re going to add this information to the ORDS config.

Jeffreys-Mini:bin thatjeffsmith$ ./ords --config /opt/ords/config config set --global db.serviceNameSuffix ""

ORDS: Release 22.2 Production on Wed Jul 06 16:29:30 2022

Copyright (c) 2010, 2022, Oracle.

Configuration:
  /opt/ords/config/

The global setting named: db.serviceNameSuffix was set to: 

The ORDS docs isn’t quite clear that this is necessary even if the suffix is NULL, we’ve filed a ticket with the Doc team to update this language so there can be no ambiguity.

If you neglect to do this, you may find yourself getting ‘Invalid Username or Password’ errors or even 404’s as ORDS won’t be able to properly address the PDBs when changing containers from the CDB to the PDB.

Now we can start ORDS!

If you forget the –config parameter, or if you don’t have it set via an environment variable, ORDS will just assume the configuration folders are in the current directory.

ORDS is running, now what?

REST Enable some schemas

Using SQLcl or SQL Developer, connect to your PDB, and enable your schema(s).

Launch SQL Developer Web

Go to where ORDS is running, and pull up /ords/sql-developer

You’ll be prompted for the name of the PDB and the user you want to login as.

The first user prompt will be the p_url_mapping_pattern parameter. So if you REST Enabled HR as ‘peeps,’ then you’ll enter peeps on the first prompt.

On the second prompt, you’ll need your username / password credentials. That will always be the username as defined in the database.

Is that it?

Almost!

You’ll want to setup ORDS to start up automatically, so either in a script or doing env setup, make sure the ORDS config folder is defined, and make sure you’re redirecting stdout to a log file somewhere.

What is coming in Part Two?

I’m going to show you how to setup the PDB Lifecycle operations with the DB API feature of ORDS. I’ve done this already before in version 19.4 of ORDS, but there’s all new commands in 22.2, plus a bit of review in never a bad idea.