SQL Developer Web – for 2 or more Databases

Can you run Oracle SQL Developer Web for your on-premise Oracle Database?

Yes!

Can you run Oracle SQL Developer Web for 2 or more on-premise Oracle databases, pluggable or completely separate instances?

Yes!

ORDS and your first database

When you install and configure ORDS, it begins with a single database. The installer will prompt you for connection information for A database, which will then have the ORDS schemas added, and a JDBC connection pool to that database established.

And, the routing pattern for that connection pool will be /

But I have N databases!

If you have ORDS installed in your mid-tier, there’s no reason you can’t tell it to support as many Oracle Databases as you need it to across your network.

Even if you have it running on your database server, you could tell it to support all of the instances on that server, or all of the pluggable databases in those instances. Note: consider your HA requirements, if your server goes down, you’ve just lost those databases AND your web server. If you have RAC or Data Guard, or Database is good, but what about ORDS?

The Docs covers how to add new databases to ORDS. Oracle-Base also has a lovely step-by-step tutorial.

When you come out the end of that, you will have 1 + N connection pools.

The original (install time db) pool will be mapped to /

The new database pool will be mapped to /something-else. The mapping pattern is completely up to you.

Enabling SQL Developer on the new pool

Each connection pool gets is own set of configuration parameters. This allows you to say things like, THIS database gets the DB API enabled, but THIS database does NOT.

So unless you have it configured in the defaults.xml, you’ll need to add these two lines in your new connection pool configuration XML file.

Jeffreys-Mini:conf thatjeffsmith$ pwd
/ords/config/ords/conf
Jeffreys-Mini:conf thatjeffsmith$ ls
apex_pu.xml pdb2_pu.xml
Jeffreys-Mini:conf thatjeffsmith$ cat pdb2_pu.xml 
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Mon Feb 03 09:14:00 EST 2020</comment>
<entry key="db.password">@050ADCCAF965D100292F9BB31CCE13112C</entry>
<entry key="db.servicename">pdb2</entry>
<entry key="db.username">ORDS_PUBLIC_USER</entry>
<entry key="jdbc.auth.enabled">true</entry>
<entry key="feature.sdw">true</entry>
<entry key="restEnabledSql.active">true</entry>
</properties>

So with the pools defined, mapped, and configured, I have two databases available for SQL Developer Web on my local ORDS instance.

Now, there’s always been a tiny part of my brain that’s always been bothered by this pattern…it’s that the first database, I want it to be labelled too!

Here’s what I want.

1. /ords/db1 for my first database, all REST Services, APEX, and SDW served out of this mapping pattern
2. /ords/db2 for my 2nd database…
3. …and carry on as needed

URL Mapping Configuration

To make ME happy, I’ve updated my ORDS url-mapping.xml file to look like this:

<pool-config xmlns="http://xmlns.oracle.com/apex/pool-config">
  <pool name="pdb2" base-path="/pdb2" updated="2020-02-03T14:15:01.319Z"/>
  <pool name="apex" base-path="/orcl"/>
</pool-config>

Your default connection pool is called ‘apex’ for historical reasons, so I’ve added an entry for the FIRST pool, and defined a base-path for /orcl.

My environment is a a single 19c with Multitenant architecture Oracle Database. I have two pluggable databases, orcl and pdb2.

So now when I start up ORDS, things that I used to address as:

http://localhost:8080/ords/hr/module/service

Going forward I will now address as:

http://localhost:8080/ords/orcl/hr/module/service

This includes SQL Developer Web (SDW) as well. And I can now use SDW for as many databases as I have configured and enabled.

A BIG, BIG BUT
I don’t recommend you go back and change your first connection pool mapping, because if you do, you’ll have to put in URL rewrites or redirects via a load balancer as all of your old links will 404. However, if you are doing an install of ORDS and you’re setting it up for 10 databases all at once, something to consider to avoid confusion later!

One ORDS, two databases, two SQL Developer Webs…

Now that I have my two pools, I can load anything I want between the two databases, and of course that includes SQL Developer Web.

An issue with the /ords/sql-developer page

Access to a user in SQL Developer web is based on the REST Enabled Schemas mapping pattern. We have a generic login page that will help you setup this url combo for your user.

This easier login link for your database will work for the first pool, but it won’t work for the 2nd, 3rd, 4th, .. .pools. This is a bug. The workaround is once you have the rest-enabled schema, to go to /ords/dbN/schema/_sdw to get a login for that user as shown in the image above.