DBA Hub

📋Steps in this guide1/6

Oracle REST Data Services (ORDS) : Configure Multiple Databases (ORDS Version 22.1 Onward)

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
13 views
1

Background and Assumptions

When you install ORDS you are prompted for database credentials, which are used to configure a default database connection, 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. This article assumes the following. - You already have a functioning installation of ORDS . - The paths for the ORDS configuration match those from the ORDS installation article listed above. - You have one or more Oracle databases available. - Where necessary, the database already has a functioning APEX installation. This is not necessary for ORDS, but the APEX example will obviously need it. The examples assume the following environment variables exist to set the relevant paths.

Code/Command (click line numbers to comment):

1
2
3
4
export ORDS_HOME=/u01/ords
export ORDS_CONFIG=/u01/config/ords
export PATH=${ORDS_HOME}/bin:${PATH}
export ORDS_LOGS=${ORDS_CONFIG}/logs
2

Create a New Database Connection for APEX

The following examples create a new database connection called "pdb1", which supports APEX and Oracle REST Data Services. For each new connection we have to install ORDS in the destination database. This can be done interactively or using command line arguments. Here we use command line arguments to create the new pool. The main steps here are as follows. - We supply the location of the configuration and log folders. If the environment variable is set, we don't really need to use the argument, but I prefer to be explicit. - We supply the pool name using the argument. If this is omitted, the default configuration will be altered, rather than a new pool being created. - We supply the database connection details. - We've enabled the DB API, Rest Enabled SQL and SQL Developer Web. These aren't necessary, but they are nice to include. - We set the argument to proxied, and the is set to . - We supply the passwords for the and users using file redirection. Once the command has run, we can see a new subdirectory for "pdb1" under the databases configuration. We now have two paths available, representing the two connections. The URL will be different, depending on if you configured ORDS to use HTTPS. In this example we named the new pool after the database connection, but we could have given it a different name that looks better in the URL.

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
export DB_PORT=1521
export DB_SERVICE=pdb1
export SYSDBA_USER=SYS
export SYSDBA_PASSWORD=SysPassword1
export ORDS_PASSWORD=OraPassword1

ords --config ${ORDS_CONFIG} install \
     --log-folder ${ORDS_LOGS} \
     --db-pool pdb1 \
     --admin-user ${SYSDBA_USER} \
     --db-hostname ${HOSTNAME} \
     --db-port ${DB_PORT} \
     --db-servicename ${DB_SERVICE} \
     --feature-db-api true \
     --feature-rest-enabled-sql true \
     --feature-sdw true \
     --gateway-mode proxied \
     --gateway-user APEX_PUBLIC_USER \
     --proxy-user \
     --password-stdin <<EOF
${SYSDBA_PASSWORD}
${ORDS_PASSWORD}
EOF

$ cd $ORDS_CONFIG/databases/
$ tree
.
|-- default
|   |-- pool.xml
|   `-- wallet
|       `-- cwallet.sso
`-- pdb1
    |-- pool.xml
    `-- wallet
        `-- cwallet.sso

4 directories, 4 files
$

HTTPS:
Default: http://localhost:8080/ords/
PDB1   : http://localhost:8080/ords/pdb1/

HTTPS:
Default: https://localhost:8443/ords/
PDB1   : https://localhost:8443/ords/pdb1/
3

Create a New Database Connection for PLSQL Applications

If we have web applications built using the PL/SQL Web Toolkit, we 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. We have two options for creating the pool. We can use a proxied connection, via the , or connect directly to the schema with the PL/SQL we need to run. The following example creates a new database pool called "plsqluser-pdb1", which connects to the user on the PDB1 database. The database connection uses proxy access, but does not support APEX. The main steps here are as follows. - We supply the location of the configuration and log folders. - We supply the pool name using the argument. - We supply the database connection details. - We set the argument to proxied, and the is set to . - We supply the passwords for the and users using file redirection. This example does a similar thing, but uses direct access instead of proxying. Notice the is set to "direct" and we supply the password to the user. Regardless of the method used, once the command has run, we can see a new subdirectory for "plsqluser-pdb1" under the databases configuration. By default the "security.requestValidationFunction" attribute is set up for APEX, so we need to either provide a different security function, blank the value, or delete the setting to access our PL/SQL. After restarting ORDS, we can now access the PL/SQL procedure using the following URL.

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
81
82
83
84
85
conn sys/SysPassword1@//localhost:1521/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@//localhost:1521/pdb1

create or replace procedure plsql_test (p_text in varchar2) as
begin
  htp.print(p_text);
end;
/

export DB_PORT=1521
export DB_SERVICE=pdb1
export SYSDBA_USER=SYS
export SYSDBA_PASSWORD=SysPassword1
export ORDS_PASSWORD=OraPassword1

ords --config ${ORDS_CONFIG} install \
     --log-folder ${ORDS_LOGS} \
     --db-pool plsqluser-pdb1 \
     --admin-user ${SYSDBA_USER} \
     --db-hostname ${HOSTNAME} \
     --db-port ${DB_PORT} \
     --db-servicename ${DB_SERVICE} \
     --gateway-mode proxied \
     --gateway-user PLSQLUSER \
     --proxy-user \
     --password-stdin <<EOF
${SYSDBA_PASSWORD}
${ORDS_PASSWORD}
EOF

export DB_PORT=1521
export DB_SERVICE=pdb1
export SYSDBA_USER=SYS
export SYSDBA_PASSWORD=SysPassword1
export ORDS_PASSWORD=OraPassword1
export DIRECT_PASSWORD=plsqluser1

ords --config ${ORDS_CONFIG} install \
     --log-folder ${ORDS_LOGS} \
     --db-pool plsqluser-pdb1 \
     --admin-user ${SYSDBA_USER} \
     --db-hostname ${HOSTNAME} \
     --db-port ${DB_PORT} \
     --db-servicename ${DB_SERVICE} \
     --gateway-mode direct \
     --gateway-user PLSQLUSER \
     --password-stdin <<EOF
${SYSDBA_PASSWORD}
${DIRECT_PASSWORD}
EOF

$ cd $ORDS_CONFIG/databases/
$ tree
.
|-- default
|   |-- pool.xml
|   `-- wallet
|       `-- cwallet.sso
|-- pdb1
|   |-- pool.xml
|   `-- wallet
|       `-- cwallet.sso
`-- plsqluser-pdb1
    |-- pool.xml
    `-- wallet
        `-- cwallet.sso

6 directories, 6 files
$

# Set to blank value.
ords --config ${ORDS_CONFIG} config --db-pool plsqluser-pdb1 set security.requestValidationFunction ""

# Remove the setting.
ords --config ${ORDS_CONFIG} config --db-pool plsqluser-pdb1 delete security.requestValidationFunction

https://localhost:8443/ords/plsqluser-pdb1/plsql_test?p_text=Banana
4

Create a New Database Connection for ORDS RESTful Services

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. The main steps here are as follows. - We supply the location of the configuration and log folders. - We supply the pool name using the argument. - We supply the database connection details. - We set the argument to disabled. - We supply the passwords for the and users using file redirection. Once the command has run, we can see a new subdirectory for "plsqluser-pdb1" under the databases configuration. The base URL for this pool would now be as follows, but this is not a full URL to a web service. A full URL would look more like this, with the appropriate values substituted for the web service.

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
export DB_PORT=1521
export DB_SERVICE=pdb1
export SYSDBA_USER=SYS
export SYSDBA_PASSWORD=SysPassword1
export ORDS_PASSWORD=OraPassword1

ords --config ${ORDS_CONFIG} install \
     --log-folder ${ORDS_LOGS} \
     --db-pool rest-only-pdb1 \
     --admin-user ${SYSDBA_USER} \
     --db-hostname ${HOSTNAME} \
     --db-port ${DB_PORT} \
     --db-servicename ${DB_SERVICE} \
     --gateway-mode disabled \
     --password-stdin <<EOF
${SYSDBA_PASSWORD}
${ORDS_PASSWORD}
EOF

$ cd $ORDS_CONFIG/databases/
$ tree
.
|-- default
|   |-- pool.xml
|   `-- wallet
|       `-- cwallet.sso
|-- pdb1
|   |-- pool.xml
|   `-- wallet
|       `-- cwallet.sso
|-- plsqluser-pdb1
|   |-- pool.xml
|   `-- wallet
|       `-- cwallet.sso
|-- rest-only-pdb1
    |-- pool.xml
    `-- wallet
        `-- cwallet.sso

8 directories, 8 files
$

https://localhost8443/ords/rest-only-pdb1/

https://localhost8443/ords/rest-only-pdb1/{schema-alias)/{module-name}/{template}/
5

JDBC Configuration

The default values for the JDBC connections are fine for testing, but you will probably need to increase them for production systems that are under load. They can be set at the default level, or on a per-pool basis. We must restart ORDS for these settings to take effect. The full list of configurable settings can be found here .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
# Set limits for default pool.
ords --config ${ORDS_CONFIG} config set jdbc.InitialLimit 5
ords --config ${ORDS_CONFIG} config set jdbc.MaxLimit 15

# Set limits for a specific pool.
ords --config ${ORDS_CONFIG} config --db-pool plsqluser-pdb1 set jdbc.InitialLimit 5
ords --config ${ORDS_CONFIG} config --db-pool plsqluser-pdb1 set jdbc.MaxLimit 15
6

Considerations

If you are adding multiple connections, make sure to use the "--config-only" flag for all but the first you set up, or you may accidentally remove some of the config you added previously. For more information see: - Configuring Oracle REST Data Services for Multiple Databases - About the Oracle REST Data Services Configuration Files - Oracle REST Data Services (ORDS) : All Articles - Oracle REST Data Services (ORDS) : Installation on Tomcat (ORDS Version 22.1 Onward) Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!