Very quickly, we’ll go from a uploading a Salary.csv dataset to rendering a chart of Job Titles for the highest education level and permitting RESTful queries on data…
Example chart generated on a sharable URL
Query the CSV data through ORDS RESTful interface
Salary Data
Let’s start with a CSV dataset. For this exercise we’ll use a public domain Salary by Job Title and Country from kaggle.com. The download is a ZIP archive with two files. In this case it is just the Salary.csv which will be used.
This dataset provides a comprehensive collection of salary information from various industries and regions across the globe.
Oracle Object Storage Buckets will be used for storing the Salary.csv
I’m not going into all the details of downloading the archive file, unzipping it, creating a bucket, and uploading the Salary.csv but needless to say, those are important steps to get you this stage…
Dataset is uploaded to my ‘example-bucket’
Note that the file has a URL for accessing and downloading it but that URL is not publicly accessible.
The file can be accessed at this URL, but not publicly
External Table
In the database, the DBMS_CLOUD package can create a table which has it’s data loaded from the CSV file. To get access to object storage from the database we require a credential object through DBMS_CLOUD.CREATE_CREDENTIAL. Details on achieving that are already covered in Ulrike’s Oracle Object Storage Access for all Oracle Databases with DBMS_CLOUD article and my previous article Data move mojo with ORDS 21.3.0. In fact, I’m going to reuse that DATAPUMP_CRED_NAME credential. That’s just out of convenience for me. You be you and do your own thing.
To create the SALARY table we must specify a column list with data types and sizes. That requires figuring out, based on the data and also the description on kaggle.com, what data types makes sense. Some columns are obviously numeric, but it is not always clear how big or small those columns should be.
begin
DBMS_CLOUD.CREATE_EXTERNAL_TABLE(table_name=>'SALARY',
credential_name => 'DATAPUMP_CRED_NAME',
file_uri_list => 'https://frckdrxj9oco.objectstorage.eu-frankfurt-1.oci.customer-oci.com/n/frckdrxj9oco/b/example-bucket/o/Salary.csv',
format => json_object('type' VALUE 'CSV', 'skipheaders' VALUE '1'),
column_list =>'Age NUMBER(4,1),
Gender varchar2(6),
Education_Level NUMBER(1),
Job_Title varchar2(100),
Experience NUMBER(4,1),
Salary NUMBER(9,2),
Country varchar2(200),
Race varchar2(100),
Senior NUMBER(1)'
);
end;
/
Note that the file_uri_list parameter came directly from the object details panel in Object Storage.
Unless there’s a syntax error, creating the table will always work. You’ll only find out about problems when you run a select query. That’s when the DB attempts to read the file.
If you have done this right…select * from salary returns data from the Salary.csv file.
Chart
ORDS Database Actions has a charts and dashboard feature that we’re going to use now. Charts enable you to create charts from the database. The chart is constructed using the input SQL command. Dashboards enable you to group charts together to create reports. Let’s define a bar chart showing the number of job titles for the highest education level. So that the chart is not too busy I’m limiting the chart to only show job titles that have more than 10 occurrences.
Defining a bar chart for job titles
For simplicity my chart definition says that it is unprotected, so it is publicly available and anyone can see it with the URL. That’s ok with this scenario because the data is in the public domain anyway but please consider the protection on your charts and dashboards.
That URL can be shared and it will render in the browser like this…
Note that the data can be viewed as a chart, or as a table.
Although the chart and its data is available to anyone with the URL, the data is just the data for the chart.
No direct access to the original Salary.csv data, just aggregation from a specific query
For many folks the only way they can create a chart based on a CSV file and share it is through using a spreadsheet and sharing that, which means sharing the underlying data. In this case the data is not shared, just the chart. So far so cool. Now let’s look at sharing that data and allowing clients to query it.
AutoREST
This will be a short section. It is as simple as:
EXEC ORDS.ENABLE_OBJECT(p_object => 'SALARY');
That’s it. The SALARY table is now accessible through a REST service at /ords/<my schema>/salary/. Moreover, the Filter Object query parameter provides a powerful search mechanism through the Salary.csv file. For example, let’s find people in the data set with more than 30 years experience: q={"experience":{"$gt":30}}
Records in the CSV can be search with relational operators!
You can’t do that so easily in a text editor 😁
See Filter Objects in the ORDS Developer Guide for more information. Of course this RESTful URL can be protected to require a authorisation for accessing it too while the chart is left unprotected.
Note that with standard REST Enabled tables you can perform DELETE, GET, POST and PUT methods but with external tables, the only functional method is GET. All others will get a HTTP 555 response with this message: ORA-30657: operation not supported on external organized table.
Conclusion
If you have followed the above steps you have opened up new ways of accessing, sharing and interrogating data in a CSV file.
To reiterate, when using an external table the data comes from files in the Cloud or from files in a directory. The data does not get loaded or copied into a table in the database. Drop in a new Salary.csv file with different data and it is immediately picked up on the next query.
The combination of Object Storage, Autonomous Database and Oracle REST Data Services provide some interesting options for doing more with CSV files. It has its limits though. To be realistic, large volumes of data and complex queries would require actually loading copies of the data into the database. For that one should consider Autonomous Database Data Studio to load the data. You can still use ORDS as above to share the data as charts or expose a RESTful interface to the data.
Welcome to the third instalment of my series on using Oracle REST Data Services (ORDS), NGINX, Docker, SSL and Autonomous Database! In this article, I will show you how to quickly get started using ORDS and Docker. Together we will walk through the basics of building the Docker image, storing configuration in a Docker volume, running multiple ORDS instances and balancing the load using NGINX. With the help of this guide, you will be able to have a load balanced Customer Managed ORDS with Autonomous Database up and running in no time. To recap on the previous articles:
Load Balancing ORDS with NGINX introduced the concept of load balancing and the most basic of configurations to get started with NGINX running in docker. That was entirely using HTTP as the transport protocol.
HTTPS Load Balance: NGINX & ORDS took that a step further by using a self signed certificate so that the traffic between client and server was over the more secure HTTPS protocol. That was with ORDS instances running on port 8080 and 8090.
Autonomous Database – hosted and managed for free
Autonomous Database
In this article the ORDS instances will be running in Docker and sharing a configuration for an Autonomous Database hosted on Oracle Cloud Infrastructure Free Tier resources. The prerequisite for this article is an understanding of Installing and Configuring Customer Managed ORDS on Autonomous Database. The database has ORDS and APEX already installed. However, the credentials for ORDS Runtime user and PLSQL Gateway user are not known so the ords install adb command instruction will be used to create and configure additional users in the database to be used by our new ORDS instances.
Oracle Content Delivery Network
In the previous article we had the APEX images in the global/doc_root directory. It is much easier to not have to configure an ORDS instance to serve those static files and to use the Oracle Content Deliver Network instead. One should note that by default, the APEX installation in the Autonomous Database does not use the Oracle CDN for the APEX static resources. So if you have not done so already, use Oracle CDN for the APEX images. The URL to use will depend on the version of APEX in use. At the time of writing, that is APEX 22.2.0. Once you have made this change the next APEX upgrade will keep the IMAGE_PREFIX parameter in synch. See https://support.oracle.com/epmos/faces/DocumentDisplay?id=2817084.1 and https://blogs.oracle.com/apex/post/running-customer-managed-ords-on-autonomous-database-heres-how-to-get-ready-for-apex-211-upgrade for more information on using Oracle CDN with APEX
As shown in the previous article it is already straight forward to use ORDS from the command line to configure and run in standalone mode. In doing so, you are satisfying the most fundamental requirement for ORDS by providing a supported Java Runtime Environment for it to run in. Running ORDS in Docker takes care of that dependancy and provides a consistent structure. For your convenience, I have defined a Dockerfile to create an image with the latest version of ORDS built in. It does require the JDK 17 image from Oracle Container Registry jdk repository. To use images from the Oracle Container Registry you must first sign in using your Oracle Account to accept the license agreement for the Oracle image. Once you have accepted the licence, follow the installation instructions on the page to login and pull the jdk:17 image:
That will pull into your local Docker repository the most recent Oracle JDK 17 build.
Dockerfile for ORDS Entrypoint
The configuration is quite simple. A couple of folders are exposed for providing configuration and library extensions. That configuration directory is essential but in the majority of cases, customers do not have custom extensions so the lib/ext folder will not be used in this article. Similarly, although the Dockerfile specifies that both port 8080 and port 8443 should be exposed, we will only be using port 8080 for HTTP traffic in this article. It is NGINX that will be terminating the HTTPS traffic before routing upstream to our ORDS instances.
The Dockerfile we’ll use to create the ORDS image is available at ORDS_Latest_Dockerfile. Contents listed below.
#
# Defines a docker image, based on the Oracle JDK image, to run Oracle REST Data Services. During the image building
# process the most recent version of ORDS will be automatically downloaded and extracted.
#
# Volumes for configuration and lib/ext are defined.
#
# docker run -p 8080:8080 -v ords-adb-config:/opt/ords-config/ -v ords-lib-ext:/opt/ords/latest/lib/ext ords-latest/oraclejdk
#
# See https://peterobrien.blog/ for more information and examples.
#
FROM container-registry.oracle.com/java/jdk:17
MAINTAINER Peter O'Brien
ENV LATEST=/opt/ords-latest/
ENV CONFIG=/opt/ords-config/
WORKDIR $LATEST
ADD https://download.oracle.com/otn_software/java/ords/ords-latest.zip $LATEST
RUN jar xf ords-latest.zip; rm ords-latest.zip; chmod +x bin/ords
VOLUME $LATEST/lib/ext/ $CONFIG
EXPOSE 8080
EXPOSE 8443
WORKDIR $CONFIG
ENTRYPOINT ["/opt/ords-latest/bin/ords"]
CMD ["serve"]
To use the above Dockerfile and build an image locally called ords-latest/oraclejdk use the following command
> docker build --tag ords-latest/oraclejdk \
https://gist.githubusercontent.com/pobalopalous/fc6ab4ee777f6b7f32a400e920df682d/raw/ORDS_Latest_Dockerfile
Downloading build context from remote url: https://gist.githubusercontent.com/pobalopalous/fc6ab4ee777f6b7f32a400e920df682d/raw/ORDS_Latest_Dockerfile [===============Downloading build context from remote url: https://gist.githubusercontent.com/pobalopalous/fc6ab4ee777f6b7f32a400e920df682d/raw/ORDS_Latest_Dockerfile [==================================================>] 878B/878B
Downloading build context from remote url: https://gist.githubusercontent.com/pobalopalous/fc6ab4ee777f6b7f32a400e920df682d/raw/ORDS_Latest_Dockerfile [==================================================>] 878B/878B
Sending build context to Docker daemon 2.56kB
Step 1/13 : FROM container-registry.oracle.com/java/jdk:17
---> 4945318567e9
Step 2/13 : MAINTAINER Peter O'Brien
---> Using cache
---> 1bb5b3ea1d92
Step 3/13 : ENV LATEST=/opt/ords-latest/
---> Using cache
---> 4798e9cbc8d1
Step 4/13 : ENV CONFIG=/opt/ords-config/
---> Using cache
---> a1f6e0bf441c
Step 5/13 : WORKDIR $LATEST
---> Using cache
---> 1b961db4ee2d
Step 6/13 : ADD https://download.oracle.com/otn_software/java/ords/ords-latest.zip $LATEST
Downloading [==================================================>] 94.62MB/94.62MB
---> Using cache
---> f6d009ada2f1
Step 7/13 : RUN jar xf ords-latest.zip; rm ords-latest.zip; chmod +x bin/ords
---> Using cache
---> f6d20c737486
Step 8/13 : VOLUME $LATEST/lib/ext/ $CONFIG
---> Using cache
---> fde34609973e
Step 9/13 : EXPOSE 8080
---> Using cache
---> 77933cb86baa
Step 10/13 : EXPOSE 8443
---> Using cache
---> 094fc3d8332b
Step 11/13 : WORKDIR $CONFIG
---> Using cache
---> 2d1b41e2c6f0
Step 12/13 : ENTRYPOINT ["/opt/ords-latest/bin/ords"]
---> Using cache
---> 9974ac45526d
Step 13/13 : CMD ["serve"]
---> Using cache
---> 4cbe74b80bb5
Successfully built 4cbe74b80bb5
Successfully tagged ords-latest/oraclejdk:latest
You now have an image in your local Docker repository ready to run. Note that the base image is an Oracle JDK 17 one. You can of course change that to something else. At the time of writing, only Oracle JDK 11 and 17 are supported Java Runtime Environments for ORDS.
Docker volume for ORDS configuration
Now it’s time to start putting the ORDS configuration together. In the previous article I outlined a configuration folder structure which was defined on the host computer file system. We are deviating from that in two ways. First, as outlined above, we will not have any APEX images in the global/doc_root directory because we are using the Oracle CDN with APEX in the hosted Autonomous Database. Second, we’re using a Docker volume, rather than the local filesystem, to store all the configuration.
Docker volumes are an ideal way to persist data generated by and used by Docker containers. They provide several benefits, such as:
Data isolation: Docker volumes are independent of the underlying filesystem, which ensures that the data persists even if the container is moved to a different host.
Easy deployment: Docker volumes can be shared across multiple containers and hosts, making it easy to deploy applications in different environments.
Data security: Docker volumes are stored outside the container, so they are not affected by any changes within the container. This ensures that your data remains secure and consistent.
Performance: Docker volumes are stored on the host system, which can be faster than using shared storage. This can improve the performance of your containers.
The first configuration item for a Customer Managed ORDS on Autonomous Database is the wallet and getting that wallet zip file into the Docker volume involves a few steps that may not be intuitive if you are not familiar with Docker volumes. You see, to copy a file into a Docker volume, one must do that through a running container, but before we have a running container, we must first create the volume.
Let’s assume you have downloaded your Autonomous Database wallet zip file to your ~/Downloads directory. For example: ~/Downloads/Wallet_DB202301101106.zip. We’re going to put it in the ords-adb-config volume as /opt/ords-config/Wallet_Autonomous.zip but first we must start a container to use it.
Note that we’re not mapping to any ports and once we’re finished with this container it will be removed. Let’s copy that wallet zip file. We know the name of the container is ords-latest because that’s the name we gave in the docker run command. Your wallet file name will be different but we’re going to copy it to /opt/ords-config/Wallet_Autonomous.zip to keep things simple for subsequent commands. If you are going to have multiple pools, you will have to have distinct filenames.
That ords-latest container is no longer required. It only came into existence to allow you to copy the zip file. When you stop the container it should be removed automatically.
> docker stop ords-latest
Configuration for Customer Managed ORDS
The wallet zip file is a good start but now it’s time to run through the Customer Managed ORDS with Autonomous Database install step which will create additional users in the database and store the necessary pool settings in the ords-adb-config Docker volume. We’re going to use the non-interactive silent installation so will have to provide the passwords for the existing ADMIN user, and the two users to create. Referring back to the ORDS documentation, the ords install adb command is…
Let’s create that file with the passwords to use. We can delete it once the ords install adb command completes. Create the adbs_passwords.txt file with three passwords on each line:
<PASSWORD FOR admin-user>
<PASSWORD FOR db-user>
<PASSWORD FOR gateway-user>
In my case the adbs_passwords.txt file looks like this:
With my passwords file I can pass all these details in one command as I run it in Docker. Note that the entire command line also specifies -i which instructs the docker engine to use standard input ( STDIN ) for the container.
> docker run -i -v ords-adb-config:/opt/ords-config/ \
install adb \
--admin-user ADMIN \
--db-user ORDS_PUBLIC_USER2 \
--gateway-user ORDS_PLSQL_GATEWAY2 \
--wallet /opt/ords-config/Wallet_Autonomous.zip \
--wallet-service-name db202301101106_low \
--feature-sdw true \
--feature-db-api true \
--feature-rest-enabled-sql true \
--password-stdin < adbs_passwords.txt
ORDS: Release 22.4 Production on Mon Mar 06 09:52:30 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
/opt/ords-config/
Oracle REST Data Services - Non-Interactive Customer Managed ORDS for Autonomous Database
Connecting to Autonomous database user: ADMIN TNS Service: db202301101106_low
Retrieving information
Checking Autonomous database user: ORDS_PLSQL_GATEWAY2 TNS Service: db202301101106_low
The setting named: db.wallet.zip.path was set to: /opt/ords-config/Wallet_Autonomous.zip in configuration: default
The setting named: db.wallet.zip.service was set to: db202301101106_low in configuration: default
The setting named: db.username was set to: ORDS_PUBLIC_USER2 in configuration: default
The setting named: db.password was set to: ****** in configuration: default
The setting named: plsql.gateway.mode was set to: proxied in configuration: default
The setting named: feature.sdw was set to: true in configuration: default
The global setting named: database.api.enabled was set to: true
The setting named: restEnabledSql.active was set to: true in configuration: default
The setting named: security.requestValidationFunction was set to: ords_util.authorize_plsql_gateway in configuration: default
2023-03-06T09:52:38.256Z INFO Connecting to Autonomous database user: ADMIN TNS Service: db202301101106_low
------------------------------------------------------------
Date : 06 Mar 2023 09:52:38
Release : Oracle REST Data Services 22.4.4.r0411526
Database : Oracle Database 19c Enterprise Edition
DB Version : 19.18.0.1.0
------------------------------------------------------------
Container Name: C4TOSECRETNQ2JA_DB202301101106
------------------------------------------------------------
[*** script: ords_runtime_user.sql]
PL/SQL procedure successfully completed.
2023-03-06T09:52:42.532Z INFO ... Verifying Autonomous Database runtime user
[*** script: ords_gateway_user.sql]
PL/SQL procedure successfully completed.
2023-03-06T09:52:43.674Z INFO ... Verifying Autonomous Database gateway user
2023-03-06T09:52:43.675Z INFO Completed configuring for Customer Managed Oracle REST Data Services version 22.4.4.r0411526. Elapsed time: 00:00:05.407
[*** Info: Completed configuring for Customer Managed Oracle REST Data Services version 22.4.4.r0411526. Elapsed time: 00:00:05.407
]
2023-03-06T09:52:43.720Z INFO To run in standalone mode, use the ords serve command:
2023-03-06T09:52:43.723Z INFO ords --config /opt/ords-config serve
2023-03-06T09:52:43.723Z INFO Visit the ORDS Documentation to access tutorials, developer guides and more to help you get started with the new ORDS Command Line Interface (http://oracle.com/rest).
Note that because the Docker entrypoint for the image that we built earlier was specified as /opt/ords-latest/bin/ords which means we can run the ords command line with any supported commands and arguments.
Don’t forget to rmadbs_passwords.txt. You do not need it anymore.
In summary, we’ve just told ORDS to use the wallet zip file and the ADMIN credentials to connect to the hosted service, create some users and persist configuration details on the ords-adb-config volume. The docker container exits because the command is complete. You can see the ORDS configuration by running the ords config list command.
> docker run -v ords-adb-config:/opt/ords-config/ \
ords-latest/oraclejdk config list
ORDS: Release 22.4 Production on Mon Mar 06 19:07:27 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
/opt/ords-config/
Database pool: default
Setting Value Source
---------------------------------- -------------------------------------- -----------
database.api.enabled true Global
db.password ****** Pool Wallet
db.username ORDS_PUBLIC_USER2 Pool
db.wallet.zip.path /opt/ords-config/Wallet_Autonomous.zip Pool
db.wallet.zip.service db202301101106_low Pool
feature.sdw true Pool
plsql.gateway.mode proxied Pool
restEnabledSql.active true Pool
security.requestValidationFunction ords_util.authorize_plsql_gateway Pool
No doubt you will remember this from the previous article about HTTPS and NGINX with ORDS. There’s one more configuration setting to address. That’s to tell ORDS what header key / value pair to use to trust that the request was received by a load balancer over HTTPS even though ORDS is receiving traffic over HTTP.
docker run -v ords-adb-config:/opt/ords-config/ \
ords-latest/oraclejdk \
config set security.httpsHeaderCheck "X-Forwarded-Proto: https"
At this point we have a Docker volume ords-adb-config which has all the configuration settings necessary to run one or more Customer Managed ORDS with Autonomous Database instances as we see fit.
Start it up!
From the previous article you have a NGINX configuration that you have running in Docker to talk to two ORDS instances listening on port 8080 and 8090. Now let’s replace those ORDS instances with ones running in Docker with the above ords-adb-config Docker volume. You can leave the NGINX container running but if you have not done so already, shutdown those ORDS instances.
Up until now, we have not specified a container name when running ORDS in Docker. For convenience, we’ll refer to the container listening on port 8080 as ords-latest-8080 and the other one as ords-latest-8090.
To check that they are up and running have a look at the process list.
> docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
2c11ababaf1b ords-latest/oraclejdk "/opt/ords-latest/bi…" 4 hours ago Up 4 hours 8443/tcp, 0.0.0.0:8090->8080/tcp, :::8090->8080/tcp ords-latest-8090
7fd8c821be64 nginx "/docker-entrypoint.…" 6 hours ago Up 6 hours 0.0.0.0:80->80/tcp, :::80->80/tcp, 0.0.0.0:443->443/tcp, :::443->443/tcp optimistic_kilby
9e0d8ec541bc 30e6e561dc7d "/opt/ords-latest/bi…" 6 hours ago Up 6 hours 0.0.0.0:8080->8080/tcp, :::8080->8080/tcp ords-latest-8080
Also use the docker logs command to keep track of the activity and status. We’ve given specific names for the two ORDS containers so we can refer to them directly,
> docker logs -f ords-latest-8080
ORDS: Release 22.4 Production on Mon Mar 06 13:48:57 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
/opt/ords-config/
2023-03-06T13:48:58.335Z INFO HTTP and HTTP/2 cleartext listening on host: 0.0.0.0 port: 8080
2023-03-06T13:48:58.389Z INFO Disabling document root because the specified folder does not exist: /opt/ords-config/global/doc_root
2023-03-06T13:49:07.009Z INFO Configuration properties for: |default|lo|
...
Mapped local pools from /opt/ords-config/databases:
/ords/ => default => VALID
2023-03-06T13:49:14.790Z INFO Oracle REST Data Services initialized
Oracle REST Data Services version : 22.4.4.r0411526
Oracle REST Data Services server info: jetty/10.0.12
Oracle REST Data Services java info: Java HotSpot(TM) 64-Bit Server VM 17.0.6+9-LTS-190
> docker logs -f ords-latest-8090
ORDS: Release 22.4 Production on Mon Mar 06 13:56:22 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
/opt/ords-config/
2023-03-06T13:56:23.011Z INFO HTTP and HTTP/2 cleartext listening on host: 0.0.0.0 port: 8080
2023-03-06T13:56:23.066Z INFO Disabling document root because the specified folder does not exist: /opt/ords-config/global/doc_root
2023-03-06T13:56:32.683Z INFO Configuration properties for: |default|lo|
...
Mapped local pools from /opt/ords-config/databases:
/ords/ => default => VALID
2023-03-06T13:56:32.683Z INFO Oracle REST Data Services initialized
Oracle REST Data Services version : 22.4.4.r0411526
Oracle REST Data Services server info: jetty/10.0.12
Oracle REST Data Services java info: Java HotSpot(TM) 64-Bit Server VM 17.0.6+9-LTS-190
As a reminder, to check the logs for the NGINX container you’ll have to specify the container name that was allocated at runtime. In my case it is optimistic_kilby.
> docker logs -f optimistic_kilby
/docker-entrypoint.sh: /docker-entrypoint.d/ is not empty, will attempt to perform configuration
/docker-entrypoint.sh: Looking for shell scripts in /docker-entrypoint.d/
/docker-entrypoint.sh: Launching /docker-entrypoint.d/10-listen-on-ipv6-by-default.sh
10-listen-on-ipv6-by-default.sh: info: Getting the checksum of /etc/nginx/conf.d/default.conf
10-listen-on-ipv6-by-default.sh: info: Enabled listen on IPv6 in /etc/nginx/conf.d/default.conf
/docker-entrypoint.sh: Launching /docker-entrypoint.d/20-envsubst-on-templates.sh
/docker-entrypoint.sh: Launching /docker-entrypoint.d/30-tune-worker-processes.sh
/docker-entrypoint.sh: Configuration complete; ready for start up
to: 192.168.5.2:8080 {GET / HTTP/1.1} upstream_response_time 0.155 request_time 0.155
172.17.0.1 - - [06/Mar/2023:13:52:58 +0000] "GET /ords/ HTTP/1.1" 301 169 "-" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/110.0.0.0 Safari/537.36"
to: 192.168.5.2:8090 {GET /ords/ HTTP/1.1} upstream_response_time 2.356 request_time 2.356
to: 192.168.5.2:8080 {GET /ords/f?p=4550:1:117375695883225::::: HTTP/1.1} upstream_response_time 2.101 request_time 2.101
to: 192.168.5.2:8090 {GET / HTTP/1.1} upstream_response_time 0.006 request_time 0.006
172.17.0.1 - - [06/Mar/2023:13:53:03 +0000] "GET /ords/ HTTP/1.1" 301 169 "-" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/110.0.0.0 Safari/537.36"
to: 192.168.5.2:8080 {GET /ords/ HTTP/1.1} upstream_response_time 2.045 request_time 2.045
From the NGINX logs you can see that traffic is being alternated between the ORDS instance listening on port 8080 and 8090.
As before, the request goes over HTTPS through NGINX and routed upstream to an ORDS instance.
You can stop a container and restart it to confirm the failover works as before.
Conclusion
Building on the previous articles you now have both NGINX and ORDS running in Docker and using an Autonomous Database. This is still effectively a development / proof of concept environment because the DNS entry and SSL certificate are not properly setup to operate seamlessly. The nginx.conf is hardcoded with two upstream ORDS instances to use and the containers are using two specific ports on the host machine. In the next article we’ll look at using docker compose so that we have more flexibility around this.
Using the Dockerfile from this article you have created an ORDS image which can be used to run ORDS commands and update your configuration in ords-adbs-config. As an additional exercise you can look into increasing pool size (jdbc.MaxLimit) and doing a rolling restart of the two ORDS docker containers to pick up that configuration change.
Now that ORDS 21.3.0 is available in all Oracle Autonomous Database regions there are even more options for copying data to, from and within Oracle Cloud. This release contains two important enhancements to the RESTful Data Pump services provided by the ORDS Database API
Any REST Enabled database account can access the Data Pump services. Of course, the database account in question still needs to have the necessary permissions in the database to perform the actions but before this release, only administrators could access the Database API Data Pump services.
Use Oracle Cloud Object Storage for export / import file destination / source. The databases must support the interaction with Object Storage though DBMS_CLOUD package. This is available out of the box with Oracle Autonomous Database.
Options? I got plenty
With these new capabilities, particularly with the Object Storage integration, moving to a hosted Oracle Autonomous Database is even easier. Not only that, moving data between existing Autonomous Databases is now easier too. To demonstrate, this article provides a step by step walk through of copying tables from a 19c Autonomous Database to a 21c Autonomous Database.
One request to export and one request to import!
Copying data between Autonomous Databases made easy
Just to reiterate the demonstration scenario: non-administrator database users will copy tables and data from hosted 19c database to a hosted 21c database with hosted object storage as the intermediary. That’s achieved through one ORDS Database API Data Pump request to export and one ORDS Database API Data Pump request to import.
To make this all happen so easily does require a little preparation of key components. Here’s what is required…
Two Autonomous databases. For this demonstration they are in the same region, but the Object Storage bucket could be configured to replicate to another region.
Object Storage bucket. A private bucket for file storage in your Oracle Cloud tenancy. The databases will have access to the bucket but will require credentials to do so.
Authorisation Token. These are revokable credentials that will be used by both the source and target databases to connect to the Object Storage bucket.
Database Account. In both databases a REST Enabled database user is required. It is these user credentials that will be used to make the REST request to ORDS. Also, it is these database users that will be performing the DBMS_DATAPUMP procedure calls. Therefore they will require certain privileges in the database. If they are not Administrator users they must have these privileges granted to them. More on that later.
Note that all of the above: Database, Object Storage and ORDS, are available with the Free Tier account on Oracle Cloud Infrastructure.
Autonomous Databases
For this demonstration there are two Always Free databases in the Frankfurt region. The one named DB 201912101106 database is an Oracle 19c Enterprise Edition database. The one named DB 202109201918 is a the 21c database.
Two Always Free Autonomous Databases
Object Storage Bucket
To keep files for this demonstration separate there is a dedicated Object Storage bucket which can be deleted afterwards. There’s plenty of space available in the Free Tier and the bucket is private but it is a good practice not leave exported data files around long.
Select ‘Buckets’ in the ‘Storage’ section.
The bucket could be called anything but for simplicity in this demonstration it is named: DataPumpBucket
Create an Object Storage Bucket called DataPumpBucket with a few clicks
Auth Token
So that the databases can interact with the private Object Storage bucket they need authentication credentials. This is achieved through creating an Auth Token and storing it as a credential in the databases. Tokens are created for a specific Oracle Cloud user and are managed in the User Settings section for the user. The quickest way there is to select User Settings from the profile menu option in the top right corner.
Once in the User Settings section select the Auth Tokens resources tab and click on the Generate Token button. In the popup dialog enter a name for the token. This name can be anything that helps describe the purpose of the token and makes it easier to identify the token later for deleting it if necessary. What really matters is the generated token value because that is effectively the secret password that will be stored in both the source and target databases. After entering a name, in this case it was DataPumpToken, press the Generate Token button to produce a token value. The value is hidden by default. Copy that value as it will not be retrievable once the dialog box is closed. That token value will be used latter when creating Database Credentials in both databases.
Create an Auth Token and copy the value
Database Account – Source and Target Schemas
The source schema will have tables with data that will be exported to the Object Storage bucket. That user must be created, granted the necessary privileges, REST Enabled, and have the tables created too. With the exception of creating tables, the same actions will be taken for the database account in the target database. In both cases the schema/user will be called DBTST. In the source database, the 19c one, the ADMIN user can create a REST Enabled user through Database Actions.
Creating a standard REST Enabled database user
The same can be done by the ADMIN user in the 21c target database. Once created these users can invoke the Data Pump services in ORDS Database API but won’t achieve much as they do not have the necessary privileges. In both databases the ADMIN user must run the following to grant these privileges.
-- REQUIRED FOR DATA PUMP TO OBJECT STORAGE
GRANT EXECUTE ON DBMS_CLOUD TO DBTST;
-- REQUIRED FOR DATA PUMP LOGS
GRANT READ, WRITE on DIRECTORY DATA_PUMP_DIR to DBTST;
-- THESE EXPLICIT GRANTS ARE REQUIRED FOR DATA PUMP
GRANT CREATE ROLE TO DBTST ;
GRANT CREATE VIEW TO DBTST ;
GRANT CREATE SESSION TO DBTST ;
GRANT CREATE TABLE TO DBTST ;
GRANT CREATE SEQUENCE TO DBTST ;
GRANT UNLIMITED TABLESPACE TO DBTST ;
GRANT CREATE PROCEDURE TO DBTST ;
Note that the above is not necessary for users with the DBA role as those users will have these privileges already.
Database Credentials
Now we get into defining the credentials that both source and target database accounts will use. Although Data Pump can work with global credentials the ORDS Database API currently restricts the use of credentials to just those that the user has defined themselves. In other words, the credential must found by name in USER_CREDENTIALS view and ENABLED. Login to both the source and target database accounts and run this command, using the token value copied from the Auth Token step and the corresponding username…
Defining a credential in the database with OCI username and Auth Token as password
In the above example the credential name given is DATAPUMP_CRED_NAME. It could be called anything. Whatever name is given, this is the name that is used in the request payload to initiate an export or import job later.
Source Tables
The purpose of this exercise is to show data being copied from one source ( 19c Autonomous Database ) to another ( 21c Autonomous Database ) so let’s create some tables and put some data in them. For the source database account run this dpexp_setup.sql script.
Create tables and populate them with data.
Setup a REST client
Any client for sending HTTP requests will do and curl is a common command line utility that is used in many examples. Although ubiquitous curl does have a few drawbacks for REST calls. In particular that it does not format the response structure well. In this demonstration we’ll use the Postman app to make the requests. For convenience we will import the OpenAPI document that is generated by ORDS. The URL for the openapi.json document will be different for both the source and target REST Enabled schemas and we can take advantage of that to have separate API collections.
To get started, import the API document for the source schema and set the Basic Authentication credentials that will be inherited by all the requests in the collection. Rename the collection to make it clearer what it is for.
Import OpenAPI document to Postman
Make your move
Now it’s time to do what we came here for…
Export from the source Autonomous Database
See the files in Object Storage
Import to the target Autonomous Database
See the data in the new tables
Export
For this demonstration we’ll export all tables, except for JOB_HISTORY, in the DBTST schema to the bucket called DataPumpBucket. The URL for bucket and the Data Pump filename pattern will be used. You can derive the URL from the OCI console Bucket details page which ends with /DataPumpBucket/objects. Replace the https://console. prefix with https://objectstorage. Then replace the DataPumpBucket/objects suffix with DataPumpBucket/o/ and then add the filename pattern. In this case we’ll go with EXPDAT%U-DBTST.DMP. Oracle Data Pump will create numbered files with that pattern in the bucket.
Initiate a Data Pump Import Job
Now that the Data Pump Job is created its status and log can be retrieved. It’s easy to create a new REST request in Postman just by clicking on the link in the response body. Save the request definition so that the previously defined Basic Authentication can be inherited.
Review the job status and log file
Review Object Storage
Refresh the bucket details page to see that Oracle Data Pump has created files there.
Oracle Data Pump created export files based on the requested filename pattern
Import
Now for getting the data imported to the target database. As before, the Postman app has the Database API document imported as a collection so that the URL and basic authorisation details are defined in one place. Using the Database API we can confirm that this DBTST schema does not have any tables.
Database API – Get all tables with Filter Object query
Next, submit the import request. The credential_name and file_name parameters are the same as the export but operation parameter value should now be IMPORT.
Initiate the Data Pump import job
Now review the Data Pump import job status and confirm that the tables have been created.
Check the job status and see that the tables are created
Now that the tables are there, Database Actions can be used to confirm they have data too.
See the imported data
While in Database Actions you can review Data Pump jobs too!
Conclusion
What has been demonstrated here is just one of the many options now available getting data to, from or between Autonomous Databases. There were a few perquisite steps but once they are in place, exporting and importing can be repeated with just one request each.
Although an on-premise database may not have the DBMS_CLOUD integration for accessing Oracle Cloud Object Storage, the ability for non-administrator users to import and export to their database directory is now available through ORDS Database API.
The demonstration showed exporting and importing tables but don’t forget that through the ORDS Database API Data Pump services you can do much more. Export the full databases, remap a schema on import, and much more. Over to you to take this further in your environment.
A little while ago ORDS version 20.2.1.227.0350 became available for download from oracle.com and was also rolled out to Autonomous Database Shared regions around the world. Oracle REST Data Services has been available on Oracle Cloud for quite some time but this is the first time the release notes specifically mention Autonomous Database Shared.
If you are running a customer-managed ORDS for your Autonomous Transaction Processing, Autonomous Data Warehouse, or Autonomous JSON Database, you must upgrade your ORDS to version 20.2.1. Failure to do so could lead to service disruptions.
Oracle REST Data Services 20.2.1.r2270350 Release Note
Why does this matter? Before answering that, some basic concepts to cover first. At a high level, an ORDS installation consists of three parts: – The Java application that accepts, validates and processes the HTTP requests. This is the ords.war running standalone, in WebLogic Server or Tomcat Server. – The file system that contains the configuration settings for the ords.war to make and manage database connections, as well as static files and so on. – The database that ORDS connects to and where users define & run their REST services in. This information and the database procedures for running these services are in the ORDS_METADATA schema.
When a customer first installs and configures ORDS the database(s) which ORDS will provide a RESTful interface for, will also have ORDS specific database objects installed. The version of the ords.war and the version of the ORDS_METADATA should be the same because they are coded, built and tested together before release. While it is possible that ORDS will work just fine if the ords.war and ORDS_METADATA versions are out of synch the expected behaviour is unspecified in this scenario.
At a high level, an ORDS installation consists of the application, file system and database.
ORDS Java application ( ords.war ) version
There are a number of ways to get the application version. From the command line…
java -jar ords.war version
Update for ORDS 22+
When using ORDS 22+ the above command will show you the version but will also show this error message:
java -jar ords.war version
Warning: Support for executing: java -jar ords.war has been deprecated.
Please add ords to your PATH and use the ords command instead.
...
ORDS: Release 22.4 Production
...
The output does give you the high level version number but the correct command to use with ORDS 22+ is in fact:
ords --version
That’s assuming the ords script is in your path. If not, the correct command is:
$ORDS_INSTALL_DIR/bin/ords --version
Now, back to the other steps to get the ORDS Java Application version number…
From the SQL Developer Web through Preferences > About…
From within APEX through SQL Workshop > RESTful Services > ORDS RESTful Services…
From within APEX through Help (?) > About. In this case it’s called the APEX_LISTENER_VERSION…
ORDS repository ( ORDS_METADATA ) version
The metadata version can be obtained through an SQL query in the database…
select ords.installed_version from dual;
The above query can be executed in SQL Developer Web worksheet, APEX SQL Workshop > SQL Commands, sqlplus, sqlcl, SQL Developer. Effectively, anywhere you can run a query from.
Conclusion
It is possible for an ORDS installation to have the ords.war and ORDS_METADATA versions out of synch. This can happen if one runs a new version of ords.war against a database that already has ORDS installed without going through the installation or upgrade process or the database metadata is upgraded but the runtime ords.war is not. The latter can happen for Customer Managed ORDS on Autonomous Database. When doing so, always regularly check the application and database versions are in synch.
Peter's Random Topic Library 