Oracle REST Data Services – Installation and Configuration

The goal of this blog is to install and configure Oracle REST Data Services 19.2 in Standalone mode into an oracle database 12.2.

The official documentation is stored here and you will find a lot of resources and examples from the Thatjeffsmith website.

The first step is to download the last version of ORDS from the Oracle REST Data Services download page.

Unzip the zip file into a directory (or folder) of your choice :

-rw-r--r--. 1 oracle oinstall 63211594 Sep 4 10:15 ords-19.2.0.199.1647.zip
oracle@oel7:/tmp/ [DBI] unzip ords-19.2.0.199.1647.zip
oracle@oel7:/tmp/ [DBI] ls -ltr
total 122764
drwxrwxrwx. 2 oracle oinstall 36 May 20 2019 params
drwxrwxrwx. 6 oracle oinstall 64 May 20 2019 examples
-rw-rw-rw-. 1 oracle oinstall 38023 Jul 5 2019 index.html
drwxrwxrwx. 2 oracle oinstall 43 Jul 18 2019 installer
-rw-r--r--. 1 oracle oinstall 62454471 Jul 18 2019 ords.war
drwxr-xr-x. 3 oracle oinstall 21 Jul 18 2019 docs
-rw-r--r--. 1 oracle oinstall 63211594 Sep 4 10:15 ords-19.2.0.199.1647.zip

Perform an advanced installation :

oracle@oel7:/tmp/ [DBI] java -Doracle.net.tns_admin=/home/oracle/app/oracle/network/admin/tnsnames.ora -jar ords.war install advanced
This Oracle REST Data Services instance has not yet been configured.
Please complete the following prompts

Enter the location to store configuration data: /home/oracle/app/oracle/product/ords

Enter the name of the database server [localhost]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:2
Enter the database SID [xe]:DBI
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:
Requires to login with administrator privileges to verify Oracle REST Data Services schema.

Enter the administrator username:sys
Enter the database password for SYS AS SYSDBA:
Confirm password:

Retrieving information.
Enter the default tablespace for ORDS_METADATA [SYSAUX]:
Enter the temporary tablespace for ORDS_METADATA [TEMP]:
Enter the default tablespace for ORDS_PUBLIC_USER [USERS]:
Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]:
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
Sep 04, 2020 5:41:07 PM
INFO: reloaded pools: []
Installing Oracle REST Data Services version 19.2.0.r1991647
... Log file written to /home/oracle/ords_install_core_2020-09-04_174127_00883.log
... Verified database prerequisites
... Created Oracle REST Data Services proxy user
... Created Oracle REST Data Services schema
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to /home/oracle/ords_install_datamodel_2020-09-04_174220_00874.log
... Log file written to /home/oracle/ords_install_apex_2020-09-04_174225_00292.log
Completed installation for Oracle REST Data Services version 19.2.0.r1991647. Elapsed time: 00:01:01.973

Enter 1 if you wish to start in standalone mode or 2 to exit [1]:
Enter 1 if using HTTP or 2 if using HTTPS [1]:
Enter the HTTP port [8080]:
2020-09-04 17:43:11.493:INFO::main: Logging initialized @342587ms to org.eclipse.jetty.util.log.StdErrLog
Sep 04, 2020 5:43:11 PM
INFO: HTTP and HTTP/2 cleartext listening on port: 8080
Sep 04, 2020 5:43:12 PM
INFO: Disabling document root because the specified folder does not exist: /home/oracle/app/oracle/product/ords/ords/standalone/doc_root
2020-09-04 17:43:15.629:INFO:oejs.Server:main: jetty-9.4.z-SNAPSHOT; built: 2019-05-02T09:46:34.874Z; git: 14f32d50076f2b706f41a33066eb364d8492e199; jvm 1.8.0_232-b09
2020-09-04 17:43:15.789:INFO:oejs.session:main: DefaultSessionIdManager workerName=node0
2020-09-04 17:43:15.789:INFO:oejs.session:main: No SessionScavenger set, using defaults
2020-09-04 17:43:15.791:INFO:oejs.session:main: node0 Scavenging every 600000ms
Sep 04, 2020 5:43:35 PM
INFO: Configuration properties for: |apex|pu|
cache.caching=false
cache.directory=/tmp/apex/cache
cache.duration=days
cache.expiration=7
cache.maxEntries=500
cache.monitorInterval=60
cache.procedureNameList=
cache.type=lru
db.hostname=localhost
db.password=******
db.port=1521
db.sid=DBI
db.username=ORDS_PUBLIC_USER
debug.debugger=false
debug.printDebugToScreen=false
error.keepErrorMessages=true
error.maxEntries=50
jdbc.DriverType=thin
jdbc.InactivityTimeout=1800
jdbc.InitialLimit=3
jdbc.MaxConnectionReuseCount=1000
jdbc.MaxLimit=10
jdbc.MaxStatementsLimit=10
jdbc.MinLimit=1
jdbc.statementTimeout=900
log.logging=false
log.maxEntries=50
misc.compress=
misc.defaultPage=apex
resource.templates.enabled=true
security.disableDefaultExclusionList=false
security.maxEntries=2000

Sep 04, 2020 5:43:35 PM
WARNING: *** jdbc.MaxLimit in configuration |apex|pu| is using a value of 10, this setting may not be sized adequately for a production environment ***
Sep 04, 2020 5:43:35 PM
WARNING: *** jdbc.InitialLimit in configuration |apex|pu| is using a value of 3, this setting may not be sized adequately for a production environment ***
Sep 04, 2020 5:44:21 PM
INFO: Oracle REST Data Services initialized
Oracle REST Data Services version : 19.2.0.r1991647
Oracle REST Data Services server info: jetty/9.4.z-SNAPSHOT

2020-09-04 17:44:23.289:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@15ca7889{/ords,null,AVAILABLE}
2020-09-04 17:44:23.338:INFO:oejs.AbstractConnector:main: Started ServerConnector@6f204a1a{HTTP/1.1,[http/1.1, h2c]}{0.0.0.0:8080}
2020-09-04 17:44:23.339:INFO:oejs.Server:main: Started @414433ms

The ords properties are stored into /tmp/ords_params.properties

oracle@oel7:/tmp/params/ [DBI] more ords_params.properties
#Fri Sep 04 17:43:11 CEST 2020
db.hostname=localhost
db.port=1521
db.sid=DBI
db.username=APEX_PUBLIC_USER
migrate.apex.rest=false
plsql.gateway.add=false
rest.services.apex.add=false
rest.services.ords.add=true
schema.tablespace.default=SYSAUX
schema.tablespace.temp=TEMP
standalone.http.port=8080
standalone.mode=true
standalone.use.https=false
user.public.password=@05FA82FC82E07209C8D6400E78EBC780A319B1F48D2C46E372
user.tablespace.default=USERS
user.tablespace.temp=TEMP

The configuration data are stored under /home/oracle/app/oracle/product/ords/ords/defaults.xml.

Let’s check the  Oracle REST Data Services installation is valid :

oracle@oel7:/tmp/ [DBI] java -jar ords.war validate
Enter the name of the database server [localhost]:
Enter the database listen port [1521]:
Enter the database SID [DBI]:
Requires to login with administrator privileges to verify Oracle REST Data Services schema.

Enter the administrator username:sys
Enter the database password for SYS AS SYSDBA:
Confirm password:

Retrieving information.

Oracle REST Data Services will be validated.
Validating Oracle REST Data Services schema version 19.2.0.r1991647
... Log file written to /home/oracle/ords_validate_core_2020-09-04_183627_00259.log
Completed validating Oracle REST Data Services version 19.2.0.r1991647. Elapsed time: 00:00:12.532

oracle@oel7:/tmp/ [DBI]

If we check the validation log file, here is how ords validate the process :

more /home/oracle/ords_validate_core_2020-09-04_183627_00259.log
...

INFO: 18:36:35 Validating objects for Oracle REST Data Services.
VALIDATION: 18:36:35 Starting validation for schema: ORDS_METADATA
VALIDATION: 18:36:35 Validating objects
VALIDATION: 18:36:36 Validating ORDS Public Synonyms
VALIDATION: 18:36:39 Total objects: 252, invalid objects: 0
VALIDATION: 18:36:39 72 INDEX
VALIDATION: 18:36:39 3 LOB
VALIDATION: 18:36:39 12 PACKAGE
VALIDATION: 18:36:39 12 PACKAGE BODY
VALIDATION: 18:36:39 1 PROCEDURE
VALIDATION: 18:36:39 44 PUBLIC SYNONYM
VALIDATION: 18:36:39 1 SEQUENCE
VALIDATION: 18:36:39 27 TABLE
VALIDATION: 18:36:39 27 TRIGGER
VALIDATION: 18:36:39 20 TYPE
VALIDATION: 18:36:39 6 TYPE BODY
VALIDATION: 18:36:39 27 VIEW
VALIDATION: 18:36:39 Validation completed.
INFO: 18:36:39 Completed validating objects for Oracle REST Data Services.

Let’s now configure ORDS to use Oracle SQL Developer to administer (within a graphical interface) Oracle REST Data Services.

The first step is to configure an administrator user as follows :

oracle@oel7:/tmp/ [DBI] java -jar ords.war user adminlistener "Listener Administrator"
Enter a password for user adminlistener:
Confirm password for user adminlistener:
Sep 07, 2020 11:30:20 AM oracle.dbtools.standalone.ModifyUser execute
INFO: Created user: adminlistener in file: /home/oracle/app/oracle/product/ords/ords/credentials

Now let’s check into SQL Developer, which schema and database objects are related to ORDS :

Two schemas are created after the installation :

• ORDS_METADATA :
  • Owner of the PL/SQL packages used for implementing many Oracle REST Data Services capabilities. ORDS_METADATA is where the metadata about Oracle REST Data Services-enabled schemas is stored.
• ORDS_PUBLIC_USER, technical user, accessed :
  • User for invoking RESTful Services in the Oracle REST Data Services-enabled schemas.

As we are using Oracle REST Data Services without HTTPS, we  have to add the following properties into defaults.xml configuration file :

<entry key="security.verifySSL">false</entry>

Note that it is only appropriate to use this setting in development or test environments.
It is never appropriate to use this setting in production environments because it will result in user credentials being passed in clear text.

Now let’s launch Oracle REST Data Services in standalone mode :

oracle@oel7:/tmp/ [DBI] nohup java -jar ords.war standalone &
[1] 7731
oracle@oel7:/tmp/ [DBI] nohup: ignoring input and appending output to ‘nohup.out’

oracle@oel7:/tmp/ [DBI] tail -f nohup.out
2020-09-07 11:58:40.775:INFO::main: Logging initialized @2904ms to org.eclipse.jetty.util.log.StdErrLog
Sep 07, 2020 11:58:40 AM
INFO: HTTP and HTTP/2 cleartext listening on port: 8080
Sep 07, 2020 11:58:41 AM
...
db.hostname=localhost
db.password=******
db.port=1521
db.sid=DBI
db.username=ORDS_PUBLIC_USER
debug.debugger=false
...
security.verifySSL=false

Sep 07, 2020 11:58:44 AM
WARNING: *** jdbc.MaxLimit in configuration |apex|pu| is using a value of 10, this setting may not be sized adequately for a production environment ***
Sep 07, 2020 11:58:44 AM
WARNING: *** jdbc.InitialLimit in configuration |apex|pu| is using a value of 3, this setting may not be sized adequately for a production environment ***
Sep 07, 2020 11:58:45 AM
INFO: Oracle REST Data Services initialized
Oracle REST Data Services version : 19.2.0.r1991647
Oracle REST Data Services server info: jetty/9.4.z-SNAPSHOT

2020-09-07 11:58:46.174:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@28d25987{/ords,null,AVAILABLE}
2020-09-07 11:58:46.188:INFO:oejs.AbstractConnector:main: Started ServerConnector@63021689{HTTP/1.1,[http/1.1, h2c]}{0.0.0.0:8080}
2020-09-07 11:58:46.189:INFO:oejs.Server:main: Started @8319ms

Now, ords is installed and configured, we are ready to create some web services…

All details in my next blog…