8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Oracle REST Data Services (ORDS) : Configure Multiple Databases (ORDS Versions 3.0 to 21.4)

This article is for ORDS versions 3.0 to 21.4. If you want to use version ORDS 22.1 onward, use this article.

If you have used mod_plsql in the past, you will know that each Database Access Descriptor (DAD) can make a connection to a specific user@database combination, allowing a single Oracle HTTP Server to service requests for multiple schemas in multiple databases. Oracle REST Data Services (ORDS) is similar in this respect.

Related articles.

Background and Assumptions

When you install ORDS, you are prompted for database credentials, which are used to configure a default database connection called "apex", which is associated with the default URL (http(s)://server:port/ords/). If your ORDS installation is only used for a single purpose, like deploying APEX from a single database, then you don't need to worry about adding new database connections.

If you require any of the following from a single ORDS installation, you will need to create additional database connections and URL mappings.

For a multi-purpose ORDS installation, it would be sensible to forget the existence of the default database connections as the associated URL can be a little confusing without an additional qualifier in the path.

For each database connection created, you need a URL mapping, to link a specific path or URL to the database. This mapping can take one of two forms.

The examples in this article will use the request path prefix approach.

This article assumes the following.

Create a New Database Connection for APEX

The following examples create a new database connection called "pdb1", which supports APEX, APEX RESTful Services and Oracle REST Data Services. This can be done interactively or using a parameter file in silent node.

This shows how to do it interactively, without the parameter file.

$ cd /u01/ords
$ $JAVA_HOME/bin/java -jar ords.war setup --database pdb1
Specify the database connection type to use.
Enter number for [1] Basic  [2] TNS  [3] Custom URL [1]:
Enter the name of the database server [localhost.localdomain]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name [pdb1]:
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:

Retrieving information.
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:
Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:
Enter the database password for APEX_LISTENER:
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:
Confirm password:
Enter a number to select a feature to enable [1] SQL Developer Web [2] REST Enabled SQL [3] None [1]:3
2020-06-17T21:02:56.009Z INFO   reloaded pools: [|apex||, |apex|al|, |apex|pu|, |apex|rt|, |pdb1||, |pdb1|al|, |pdb1|pu|, |pdb1|rt|]
2020-06-17T21:02:56.031Z INFO   Oracle REST Data Services schema version 19.4.6.r1421859 is installed.
$

The main steps here are as follows.

We could achieve a similar result silently using a parameter file.

Create a file called "/u01/ords/params/pdb1_ords_params.properties" with the following contents.

db.hostname=localhost.localdomain
db.port=1521
db.servicename=pdb1
#db.sid=
db.username=APEX_PUBLIC_USER
db.password=ApexPassword1
migrate.apex.rest=false
plsql.gateway.add=true
rest.services.apex.add=true
rest.services.ords.add=true
schema.tablespace.default=APEX
schema.tablespace.temp=TEMP
standalone.mode=false
#standalone.use.https=true
#standalone.http.port=8080
# ORDS19 Onward
#standalone.static.path=/home/oracle/apex/images
# Pre-ORDS19
#standalone.static.images=/home/oracle/apex/images
user.apex.listener.password=ApexPassword1
user.apex.restpublic.password=ApexPassword1
user.public.password=ApexPassword1
user.tablespace.default=APEX
user.tablespace.temp=TEMP
sys.user=SYS
sys.password=SysPassword1
restEnabledSql.active=false
feature.sdw=false
$ cd /u01/ords
$ $JAVA_HOME/bin/java -jar ords.war setup --database pdb1 --parameterFile params/pdb1_ords_params.properties --silent

Retrieving information.
2020-06-17T20:54:32.779Z INFO   reloaded pools: [|apex||, |apex|al|, |apex|pu|, |apex|rt|, |pdb1||, |pdb1|pu|, |pdb1|al|, |pdb1|rt|]
2020-06-17T20:54:32.873Z INFO   Oracle REST Data Services schema version 19.4.6.r1421859 is installed.
$

With the new database connection in place, you need to map a URL to point to it.

$ $JAVA_HOME/bin/java -jar ords.war map-url --type base-path /pdb1 pdb1
Jun 17, 2016 7:02:55 PM oracle.dbtools.url.mapping.file.MapURL execute
INFO: Creating new mapping from: [base-path,/pdb1] to map to: [pdb1,,]
$

You will now be able to access APEX in the database using the following URL.

http:/localhost.localdomain:8080/ords/pdb1/

There is a configuration file for every database user you defined, as well as an entry for the URL mapping.

ls /u01/ords/conf/ords/conf/pdb1*
cat /u01/ords/conf/ords/url-mapping.xml

Create a New Database Connection for PLSQL Applications

If you have web applications built using the PL/SQL Web Toolkit, you can deploy them using ORDS, in a similar way to using a DAD. In this case, each schema that requires PL/SQL to be exposed will need a separate database connection.

We create a new user and create a stored procedure to run.

CONN sys@pdb1 AS SYSDBA

DROP USER plsqluser CASCADE;
CREATE USER plsqluser IDENTIFIED BY plsqluser1
  DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
  
GRANT CREATE SESSION, CREATE PROCEDURE TO plsqluser;

CONN plsqluser/plsqluser1@pdb1

CREATE OR REPLACE PROCEDURE plsql_test (p_text IN VARCHAR2) AS
BEGIN
  HTP.print(p_text);
END;
/

The following examples create a new database connection called "plsqluser-pdb1", which connects to the "plsqluser" user on the "pdb1" database. The database connection does not support Oracle REST Data Services or APEX.

This shows how to do it interactively, without the parameter file.

$ $JAVA_HOME/bin/java -jar ords.war setup --database plsqluser-pdb1
Specify the database connection type to use.
Enter number for [1] Basic  [2] TNS  [3] Custom URL [1]:
Enter the name of the database server [localhost.localdomain]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name [pdb1]:
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:2
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:
Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:plsqluser
Enter the database password for plsqluser:
Confirm password:
Enter a number to select a feature to enable [1] SQL Developer Web [2] REST Enabled SQL [3] None [1]:3
2020-06-17T21:16:24.054Z INFO   reloaded pools: [|apex||, |apex|al|, |apex|pu|, |apex|rt|, |pdb1||, |pdb1|al|, |pdb1|pu|, |pdb1|rt|, |plsqluser-pdb1||]
$

The main steps here are as follows.

We could achieve a similar result silently using a parameter file.

Create a file called "/u01/ords/params/plsqluser-pdb1_ords_params.properties" with the following contents.

db.hostname=localhost.localdomain
db.port=1521
db.servicename=pdb1
#db.sid=
db.username=plsqluser
db.password=plsqluser1
migrate.apex.rest=false
plsql.gateway.add=true
rest.services.apex.add=false
rest.services.ords.add=false
#schema.tablespace.default=APEX
#schema.tablespace.temp=TEMP
standalone.mode=false
#standalone.use.https=true
#standalone.http.port=8080
# ORDS19 Onward
#standalone.static.path=/home/oracle/apex/images
# Pre-ORDS19
#standalone.static.images=/home/oracle/apex/images
#user.apex.listener.password=ApexPassword1
#user.apex.restpublic.password=ApexPassword1
#user.public.password=ApexPassword1
#user.tablespace.default=APEX
#user.tablespace.temp=TEMP
sys.user=SYS
sys.password=SysPassword1
restEnabledSql.active=false
feature.sdw=false
$ cd /u01/ords
$ $JAVA_HOME/bin/java -jar ords.war setup --database plsqluser-pdb1 --parameterFile params/plsqluser-pdb1_ords_params.properties --silent

Retrieving information.
2020-06-17T21:26:35.705Z INFO   reloaded pools: [|apex||, |apex|al|, |apex|pu|, |apex|rt|, |pdb1||, |pdb1|al|, |pdb1|pu|, |pdb1|rt|, |plsqluser-pdb1||]
$

With the new database connection in place, you need to map a URL to point to it.

$ $JAVA_HOME/bin/java -jar ords.war map-url --type base-path /plsqluser-pdb1 plsqluser-pdb1
2020-06-17T21:19:15.634Z INFO   Creating new mapping from: [base-path,/plsqluser-pdb1] to map to: [plsqluser-pdb1, null, null]
$

You will now be able to access the PL/SQL procedure using the following URL.

http://localhost.localdomain:8080/ords/plsqluser-pdb1/plsql_test?p_text=Banana

There is a configuration file for the user you defined, as well as an entry for the URL mapping.

ls /u01/ords/conf/ords/conf/plsqluser-pdb1.xml
cat /u01/ords/conf/ords/url-mapping.xml

Create a New Database Connection for ORDS RESTful Services

From ORDS 3.0 onward, it is not mandatory to have an APEX installation. If you do not need to support APEX or PL/SQL applications, you could define a database connection as follows.

This shows how to do it interactively, without the parameter file.

$ $JAVA_HOME/bin/java -jar ords.war setup --database pdb2
Specify the database connection type to use.
Enter number for [1] Basic  [2] TNS  [3] Custom URL [1]:
Enter the name of the database server [localhost.localdomain]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name [pdb2]:
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:

Retrieving information.
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:2
Enter a number to select a feature to enable [1] SQL Developer Web [2] REST Enabled SQL [3] None [1]:3
2020-06-17T21:42:16.159Z INFO   reloaded pools: [|apex||, |apex|al|, |apex|pu|, |apex|rt|, |pdb1||, |pdb1|al|, |pdb1|pu|, |pdb1|rt|, |plsqluser-pdb1||, |pdb2|pu|]
2020-06-17T21:42:16.179Z INFO   Oracle REST Data Services schema version 19.4.6.r1421859 is installed.
$

The main steps here are as follows.

We could achieve a similar result silently using a parameter file.

Create a file called "/u01/ords/params/pdb2_ords_params.properties" with the following contents.

db.hostname=localhost.localdomain
db.port=1521
db.servicename=pdb2
#db.sid=
db.username=ORDS_PUBLIC_USER
#db.password=ApexPassword1
migrate.apex.rest=false
plsql.gateway.add=false
rest.services.apex.add=false
rest.services.ords.add=true
#schema.tablespace.default=APEX
#schema.tablespace.temp=TEMP
standalone.mode=false
#standalone.use.https=true
#standalone.http.port=8080
# ORDS19 Onward
#standalone.static.path=/home/oracle/apex/images
# Pre-ORDS19
#standalone.static.images=/home/oracle/apex/images
#user.apex.listener.password=ApexPassword1
#user.apex.restpublic.password=ApexPassword1
user.public.password=ApexPassword1
#user.tablespace.default=APEX
#user.tablespace.temp=TEMP
sys.user=SYS
sys.password=SysPassword1
restEnabledSql.active=false
feature.sdw=false
$ cd /u01/ords
$ $JAVA_HOME/bin/java -jar ords.war setup --database pdb2 --parameterFile params/pdb2_ords_params.properties --silent

Retrieving information.
2020-06-17T21:59:23.683Z INFO   reloaded pools: [|apex||, |apex|al|, |apex|pu|, |apex|rt|, |pdb1||, |pdb1|al|, |pdb1|pu|, |pdb1|rt|, |pdb2|pu|, |plsqluser-pdb1||]
2020-06-17T21:59:23.736Z INFO   Oracle REST Data Services schema version 19.4.6.r1421859 is installed.
$

With the new database connection in place, you need to map a URL to point to it. Without a RESTful service in place, any request would result in a 404 error. We might map the following.

$ $JAVA_HOME/bin/java -jar ords.war map-url --type base-path /pdb2 pdb2
Jun 17, 2016 8:26:24 PM oracle.dbtools.url.mapping.file.MapURL execute
INFO: Creating new mapping from: [base-path,/pdb2] to map to: [pdb2,,]
$

If the "testuser1@pdb2" schema contained a REST service called "testmodule1/emp/", it might be accessed using the following type of URL. Creating REST services will be covered in a separate article.

http://localhost.localdomain:8080/ords/pdb2/testuser1/testmodule1/emp/

There is a configuration file for the user you defined, as well as an entry for the URL mapping.

ls /u01/ords/conf/ords/conf/pdb2*
cat /u01/ords/conf/ords/url-mapping.xml

JDBC Configuration

Check out the "defaults.xml" for parameters you may need to set to more realistic values, especially the "jdbc.MaxLimit" setting.

$ grep jdbc /u01/ords/conf/ords/defaults.xml
<entry key="jdbc.DriverType">thin</entry>
<entry key="jdbc.InactivityTimeout">1800</entry>
<entry key="jdbc.InitialLimit">3</entry>
<entry key="jdbc.MaxConnectionReuseCount">1000</entry>
<entry key="jdbc.MaxLimit">10</entry>
<entry key="jdbc.MaxStatementsLimit">10</entry>
<entry key="jdbc.MinLimit">1</entry>
<entry key="jdbc.statementTimeout">900</entry>
$

For more information see:

Hope this helps. Regards Tim...

Back to the Top.