Mostly Making oracle easy, mostly:
Search for:
In ORDS

SQL Developer Web – for 2 or more Databases

4 Mins Read

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 /

java -jar ords.war install … 5 minutes later here is what you have

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.

Let’s see if we can capture some free db2 SEO 🙂

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.

Two different databases, both served with one instance of ORDS for 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.

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.

Related Posts

5 Comments

  2. Johann Mey Reply

    Hi Jeff,

    Thanks for the article. With your help I have managed to set up two database connections on one ORDS instance and can access both of them through SQL Developer web.

    However, I am having problems accessing both databases through SQL developer web at the same time. On one browser tab I’m connected to my first database via SQL Developer web and can perform queries successfully.
    I then open another browser tab and connect to the second database via SQL Developer web (different URL) successfully. When navigating back to the first browser tab and attempting to perform a query on the first database again, a pop-up appears which states that my session has expired. I have repeated this process a couple of times (on both browser tabs) and can confirm that the session expires as soon as I login to SQL Developer web on the other browser tab.

    Is there a setting that I need to set or is there a limitation in place that the ORDS instance can only connect to one database at the same time?

    Thanks

    • Johann Mey

      Upon further investigation I found that this is actually related to the cookies stored in my browser for the SQL Developer Web page. Opening the SQL developer web page for the second database in incognito mode solves the problem. It thus seems that the cookie stored for SQL Developer Web dictates to which database I am able to connect to, which is less of a problem in my use-case.

  3. Jason Francis Reply

    Hi Jeff,

    The ability to use an alias for your database connections in the URL, (as you’ve done here) is that something that can still be done if you install ORDS into a multitenant CDB and are using the pluggable mapping feature? (https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/20.2/aelig/using-multitenant-architecture-oracle-rest-data-services.html#GUID-694B2F89-CE4F-4AB0-88E2-EB35D03DEC3C)

    By that I mean including the db.serviceNameSuffix in your ORDS config. With that configuration in place I can’t seem to find a way to alias a database connection. The URL contains the instance name which I’d rather not use.

    Thanks.

    • thatjeffsmith Post Author

      If i understand you, you should be able to have an alias for the the CDB, via the mapping pattern, but you wouldn’t be able to alias the PDBs in that CDB.

      Otherwise, you’d need to do multiple PDB installs, then you could alias those pdbs via the url mapping.

Write A Comment