On March 11th 2020 the Swiss Oracle User Group Day should have happened in Bern. Due to the current situation with the corona virus the event was canceled.
For this Event my presentation “Wie man die Oracle REST API als DBA nutzen kann!” was accepted. To share the topic with a broader audience I will write some blog posts about the topic and try to include most of the content of the presentation.
The goal of this blog post is to introduce ORDS to DBAs. Therefore I’ll start with some very basics. The first and probably the lowest part to explain is what does ORDS stand for.
ORDS: Oracle REST Data Services
Now we know that ORDS stands for a product of Oracle which contains another acronym. Therefore the next step is to explain what REST Data Services are.
REST: Representational state transfer
You can find a lot of information explaining the whole concept in detail. I stay with a not 100% correct definition (please let me know if I am 100% wrong) and try to be as simple as possible. REST is a concept of sending a defined request to a web server which leads to a response in a defined way. The request can start an action on the web server or just send information to the client.
As a response of the web server the client gets the information in a defined way too. In the world of ORDS the response comes as JavaScript Object Notation (JSON).
{
"Movies":[
{
"Name" : "Die Hard",
"Actor" : "Bruce Willis",
"Year" : 1988,
"Link" : null,
"Good" : true
},
{
"Name" : "Die Hard 2",
"Actor" : "Bruce Willis",
"Year" : 1990,
"Link" : null,
"Good" : false
}
]
}
This is a object formatted in the JSON Notation.
Why do I as a DBA care?
REST and ORDS is popular in the developer community is heavily used in micro services architectures. Therefore why should we as DBAs care about this?
Due to the circumstances that the DBA world is involved in the automation of the infrastructure it is important to know the tools used for the automation and with ORDS we are not only able to get results of queries Oracle also provides an interface for the Oracle Database itself.
With these endpoints we are able to do a lot of DBA tasks via ORDS with just one HTTP Request.
With each version of ORDS Oracle provides more and more features for DBAs. Currently (ORDS Version 19.4) Oracle provides endpoints for the following areas:
- Data Dictionary
- Environment
- Fleet Patching and Provisioning
- General
- Monitoring
- Performance
- Pluggable Database Lifecycle Management
To get an overview of all the Endpoints and a description of how to use these please visit the documentation: REST APIs for Oracle Database
Installation of ORDS
I will use the most simple installation method. The standalone Installation. For productive environments there is the possibility to use a Java Application Server. Currently Oracle Weblogic Server >= 12.2.1.3 and Apache Tomcat >= 8.5 are supported.
After the download of the most current version of ORDS. We copy the zip file to the database server and start with the installation:
oracle@dbhost1:~: mkdir -p /u01/app/oracle/product/ords/19.4
oracle@dbhost1:~: mkdir -p /u01/app/oracle/admin/ords/config
oracle@dbhost1:~: cd /u01/app/oracle/product/ords/19.4
oracle@dbhost1:/u01/app/oracle/product/ords/19.4/: unzip ords-19.4.0.352.1226.zip
oracle@dbhost1:/u01/app/oracle/product/ords/19.4/: java -jar ords.war standalone
This Oracle REST Data Services instance has not yet been configured.
Please complete the following prompts
Enter the location to store configuration data: /u01/app/oracle/admin/ords/config
Enter 1 if using HTTP or 2 if using HTTPS [1]:2
Enter the HTTPS port [8443]:
Enter the SSL hostname:dbhost1
Enter 1 to use the self-signed certificate or 2 if you will provide the SSL certificate [1]:
2020-03-24 09:39:34.846:INFO::main: Logging initialized @17012ms to org.eclipse.jetty.util.log.StdErrLog
2020-03-24 09:39:34.890:INFO:oeju.TypeUtil:main: JVM Runtime does not support Modules
2020-03-24T08:39:35.571Z INFO HTTPS listening on host: localhost port: 8443
2020-03-24T08:39:35.591Z INFO Disabling document root because the specified folder does not exist: /u01/app/oracle/admin/ords/config/ords/standalone/doc_root
2020-03-24 09:39:35.971:INFO:oejs.Server:main: jetty-9.4.24.v20191120; built: 2019-11-22T11:09:44.612Z; git: 8b8c80157294e38f81ef8ea2358a0c49bf5db918; jvm 1.8.0_242-b08
2020-03-24 09:39:36.016:INFO:oejs.session:main: DefaultSessionIdManager workerName=node0
2020-03-24 09:39:36.016:INFO:oejs.session:main: No SessionScavenger set, using defaults
2020-03-24 09:39:36.017:INFO:oejs.session:main: node0 Scavenging every 600000ms
2020-03-24T08:39:36.333Z INFO No pools configured yet
2020-03-24T08:39:36.490Z INFO Oracle REST Data Services initialized
Oracle REST Data Services version : 19.4.0.r3521226
Oracle REST Data Services server info: jetty/9.4.24.v20191120
2020-03-24 09:39:36.993:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@45b4c3a9{/ords,null,AVAILABLE,@Secured}
2020-03-24 09:39:37.014:INFO:oejus.SslContextFactory:main: x509=X509@7daa0fbd(selfsigned,h=[],w=[]) for Server@42530531[provider=null,keyStore=oracle.dbtools.standalone.InMemoryResource@5a3bc7ed,trustStore=oracle.dbtools.standalone.InMemoryResource@5a3bc7ed]
2020-03-24 09:39:37.062:INFO:oejs.AbstractConnector:main: Started Secured@1984b1f{SSL,[ssl, http/1.1]}{0.0.0.0:8443}
2020-03-24 09:39:37.062:INFO:oejs.Server:main: Started @19230ms
With the command “java -jar ords.war standalone” we configure the ORDS standalone webserver. During the first invocation of this command you’ll be ask the following question:
- The location for the configuration files
- HTTP or HTTPS (default HTTP I have chosen HTTPS)
- If you select HTTPS you’ll be asked for the Port on which the webserver will listen
- If you select HTTPS you’ll be asked for the hostname for the certificate
- If you select HTTPS you’ll be asked if you chose a self-signed certificate or if you provide the SSL certificate
Be aware that with this command the webserver will be started too and the command prompt will not come back because the webserver is started in this process. To quit the webserver you can stop the process with “ctrl + c”.
Now we have to enable the database api to use the DBA features:
oracle@dbhost1:/u01/app/oracle/product/ords/19.4/: java -jar ords.war set-property database.api.enabled true
2020-03-24T08:46:40.624Z INFO Modified: /u01/app/oracle/admin/ords/config/ords/defaults.xml, setting: database.api.enabled = true
After enabling the database API we must create a ORDS user with the “System Administrator” ORDS privilege. Keep in mind on this server I do not have create a database. Therefore this user is a ORDS user and does not have anything in common with a database nor a OS user.
oracle@dbhost1:/u01/app/oracle/product/ords/19.4/: java -jar ords.war user admin "System Administrator"
Enter a password for user admin:
Confirm password for user admin:
2020-03-24T08:49:00.905Z INFO Created user: admin in file: /u01/app/oracle/admin/ords/config/ords/credentials
With this command a user called admin is created and the credentials are stored in the file u01/app/oracle/admin/ords/config/ords/credentials
oracle@dbhost1:/u01/app/oracle/product/ords/19.4/: cat /u01/app/oracle/admin/ords/config/ords/credentials
admin;{SSHA-512}oCoHCcth67Cn6EKTN6QoMGDMnj95Ag9xGSwzYe0XRiHYKNt5BsmLOAZl0qe04rnv01/VualbAVJ+1r5r7UkRPz/MKNXZhbYp;System Administrator
Now we have configured ORDS for DBAs. All the configuration parameters are stored in the configuration file: /u01/app/oracle/admin/ords/config/ords/defaults.xml
oracle@dbhost1:/u01/app/oracle/product/ords/19.4/: cat /u01/app/oracle/admin/ords/config/ords/defaults.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Tue Mar 24 09:46:40 CET 2020</comment>
<entry key="cache.caching">false</entry>
<entry key="cache.directory">/tmp/apex/cache</entry>
<entry key="cache.duration">days</entry>
<entry key="cache.expiration">7</entry>
<entry key="cache.maxEntries">500</entry>
<entry key="cache.monitorInterval">60</entry>
<entry key="cache.procedureNameList"/>
<entry key="cache.type">lru</entry>
<entry key="database.api.enabled">true</entry>
<entry key="debug.debugger">false</entry>
<entry key="debug.printDebugToScreen">false</entry>
<entry key="error.keepErrorMessages">true</entry>
<entry key="error.maxEntries">50</entry>
<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>
<entry key="log.logging">false</entry>
<entry key="log.maxEntries">50</entry>
<entry key="misc.compress"/>
<entry key="misc.defaultPage">apex</entry>
<entry key="security.disableDefaultExclusionList">false</entry>
<entry key="security.maxEntries">2000</entry>
</properties>
Autostart of ORDS
Due to the circumstances that ORDS does not offer a daemon mode or something similar we create a systemctl service which starts automatically at server start.
[root@dbhost1 ~]# cat /etc/systemd/system/oracle-ords.service
[Unit]
Description=Oracle ORDS Service
After=network.target
[Service]
User=oracle
Group=oinstall
Environment=ORACLE_BASE=/u01/app/oracle
ExecStart=/bin/java -jar /u01/app/oracle/product/ords/19.4/ords.war standalone
[Install]
WantedBy=multi-user.target
This systemctl script is very basic and every improvement is welcome.
In this script the environment variable ORACLE_BASE is set during the start of ORDS. This is important that ORDS is able to find the installed ORACLE_HOMEs.
Now we are able to enable and start ORDS as a systemctl service:
[root@dbhost1 ~]# systemctl enable oracle-ords
[root@dbhost1 ~]# systemctl start oracle-ords
[root@dbhost1 ~]# ps -ef | grep ords
oracle 1359 1 29 13:39 ? 00:00:12 /bin/java -jar /u01/app/oracle/product/ords/19.4/ords.war standalone
Testing the configuration
To test the configuration we can either use the browers and open as an example the following url:
https://<hostname>:<port>/ords/_/db-api/stable/environment/homes/
In this example it would be:
https://dbhost1:8443/ords/_/db-api/stable/environment/homes/
The following page will show up and you have to authenticate with the previous created System Administrator user
After the login you’ll be redirected to a page with the information about all installed Oracle Homes on this server. Depending on the used browser the page will look different.
As an alternative you can use a browser plugin like RESTClient for Firefox. You can also use curl to get the information. Please be aware that you have to send the username and password as base64 encoded string. On Linux systems this can be performed as shown in the example below. The username and the password used in this case is Username: admin, Password: admin.
oracle@dbhost1:~: curl -L -X GET -k -H "Authorization: Basic $(echo -n admin:admin | base64)" -i 'https://dbhost1:8443/ords/_/db-api/stable/environment/homes'
HTTP/1.1 301 Moved Permanently
Location: https://192.168.33.11:8443/ords/_/db-api/stable/environment/homes/
Transfer-Encoding: chunked
HTTP/1.1 200 OK
Date: Tue, 24 Mar 2020 12:45:04 GMT
Content-Type: application/json
X-Frame-Options: SAMEORIGIN
Transfer-Encoding: chunked
{"items":[{"name":"RDBMS19","version":"19.5.0.0.0","default":false,"read_only_home":false,
"links" : [ {
"rel" : "self",
"href" : "https://dbhost1:8443/ords/_/db-api/stable/environment/homes/RDBMS19/"
} ]
} ],
"links" : [ {
"rel" : "self",
"href" : "https://dbhost1:8443/ords/_/db-api/stable/environment/homes/"
}, {
"rel" : "describedby",
"href" : "https://dbhost1:8443/ords/_/db-api/stable/metadata-catalog/"
} ]
In my next blog post I’ll show how to create a database with one curl command.
