DBA Hub

📋Steps in this guide1/5

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

This article describes how to create new database connections so ORDS can service APEX, PL/SQL application and ORDS RESTful services.

oracle miscconfigurationintermediate
by OracleDba
19 views
1

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. - Deploy APEX applications from multiple databases. - Deploy PL/SQL Web Toolkit applications from multiple databases or multiple schemas within a single database. - Deploy REST web services from multiple databases. 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. - Request Path Prefix: A specific part or pattern in the URL, for example "/testdb/". - Request URL Prefix: The base URL including the HTTP(s), for example "http://ol7-locadomain:8080/ords/testdb/". The examples in this article will use the request path prefix approach. This article assumes the following.
2

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. The main steps here are as follows. - Enter hostname, port and service name for the database connection. - Pick [1] for the Oracle REST Data Services to allow ORDS-style RESTful web services. It's not necessary for APEX, but it does no harm, so I always pick it. If you pick this option, you will have to enter your ORDS_PUBLIC_USER password. - Pick [1] for the PL/SQL Gateway and accept the APEX_PUBLIC_USER username and provide the password. - Pick [1] for the Application Express RESTful Services and provide the passwords for the APEX_LISTENER and APEX_REST_PUBLIC_USER users. This is not necessary if you are not planing to use the APEX RESTful web services, but I feel like you might as well configure everything. - Pick [3] so SQL Developer Web and REST Enabled SQL are not enabled. 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. With the new database connection in place, you need to map a URL to point to it. You will now be able to access APEX in the database using the following URL. There is a configuration file for every database user you defined, as well as an entry for the URL mapping.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
$
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.
$

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.
$

$
$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,,]
$

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

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

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. 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. The main steps here are as follows. - Enter hostname, port and service name for the database connection. - Pick [2] so Oracle REST Data Services are not enabled. - Pick [1] for the PL/SQL Gateway, but specify plsqluser for the username and provide the password. - Pick [3] so SQL Developer Web and REST Enabled SQL are not enabled. 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. With the new database connection in place, you need to map a URL to point to it. You will now be able to access the PL/SQL procedure using the following URL. There is a configuration file for the user you defined, as well as an entry for the URL mapping.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
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;
/

$
$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||]
$

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||]
$

$
$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]
$

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

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

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. The main steps here are as follows. - Enter hostname, port and service name for the database connection. Notice we connected to a different database this time. - Pick [1] so Oracle REST Data Services are enabled. Since this is the first time ORDS has connected to this server it requires SYSDBA credentials so it can build the ORDS components. - The tablespace errors are because ORDS is trying to reuse the settings in the "/u01/ords/params/ords_params.properties" file. If you want to avoid these, alter the contents of that file before you begin. The errors do not cause a problem as the setup allows you to specify the correct values. - Pick [2] so the PL/SQL Gateway is not configured, as we don't want to configure APEX or direct PL/SQL applications. - Pick [3] so SQL Developer Web and REST Enabled SQL are not enabled. 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. 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. 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. There is a configuration file for the user you defined, as well as an entry for the URL mapping.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
$
$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.
$

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.
$

$
$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,,]
$

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

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

JDBC Configuration

Check out the "defaults.xml" for parameters you may need to set to more realistic values, especially the "jdbc.MaxLimit" setting. For more information see: - Configuring Multiple Databases - Oracle REST Data Services (ORDS) : All Articles - Oracle REST Data Services (ORDS) : Installation on Tomcat - Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL - Oracle REST Data Services (ORDS) : AutoREST - Oracle REST Data Services (ORDS) : Authentication Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
$ 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>
$

Comments (0)

Please to add comments

No comments yet. Be the first to comment!