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 :
1 2 3 4 5 6 7 8 9 10 11 | -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 :
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 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 | 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 |
1 |
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 :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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 :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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 :
1 2 3 4 5 | 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 :
1 | <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 :
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 | 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…