Tag: ords
- Written by: ilmarkerm
- Category: Blog entry
- Published: November 19, 2024
Tested with ORDS 24.3 running using GraalVM 21.
ORDS documentation has a chapter how to push ORDS metrics to OpenTelemetry endpoint, which also is supported by Prometheus. But Prometheus traditionally is using the opposite method, that Prometheus itself will regularly connect to monitored service endpoints and scrape all its metrics. Similar JavaAgent method can also be deployed to expose ORDS metrics as a traditional Prometheus pull based endpoint.
For this we need to deploy Prometheus JMX exporter as a Java agent in ORDS, this will expose /metrics endpoint that Prometheus can scrape.
First, JMX exporter downloads and documentation is available here.
# Download JMX exporter to ORDS host
curl -o jmx_prometheus_javaagent.jar "https://repo1.maven.org/maven2/io/prometheus/jmx/jmx_prometheus_javaagent/1.0.1/jmx_prometheus_javaagent-1.0.1.jar"
Create configuration file, config.yaml with the following contents. It will format the JMX data into more usable Prometheus metric names.
rules:
# Reformatting Oracle UCP metrics to have more usable names and adding data types
- pattern: "oracle.ucp.admin.UniversalConnectionPoolMBean<name=.+, poolName=\\|(.+)\\|(.+)\\|.+><>connectionsClosedCount"
name: oracle_ucp_connectionsClosedCount
type: COUNTER
labels:
poolName: $1_$2
- pattern: "oracle.ucp.admin.UniversalConnectionPoolMBean<name=.+, poolName=\\|(.+)\\|(.+)\\|.+><>connectionsCreatedCount"
name: oracle_ucp_connectionsCreatedCount
type: COUNTER
labels:
poolName: $1_$2
- pattern: "oracle.ucp.admin.UniversalConnectionPoolMBean<name=.+, poolName=\\|(.+)\\|(.+)\\|.+><>cumulative(\\w+)"
name: oracle_ucp_cumulative$3
type: COUNTER
labels:
poolName: $1_$2
- pattern: "oracle.ucp.admin.UniversalConnectionPoolMBean<name=.+, poolName=\\|(.+)\\|(.+)\\|.+><>(\\w+)"
name: oracle_ucp_$3
type: GAUGE
labels:
poolName: $1_$2
# This pattern below will add all the rest, tons of detailed java internal things
# Comment out if you do not want to see them
- pattern: '.*'
I will assume, that jmx_prometheus_javaagent.jar and config.yaml are placed under /home/ords
Next, change ORDS startup script so it would include the JMX agent. The easiest way is to use environment variable _JAVA_OPTIONS for it.
# Set Startup Java options
# 10.10.10.10 is my local server IP where metrics exporter will bind to, default is localhost
# 21022 is the port JMX exporter will listen to
# With this ORDS metrics would be exposed as http://10.10.10.10:21022/metrics
export _JAVA_OPTIONS="-javaagent:/home/ords/jmx_prometheus_javaagent.jar=10.10.10.10:21022:/home/ords/config.yaml"
# Start ORDS in standalone mode as usual
ords serve
Below is my full ORDS SystemD service file – /etc/systemd/system/ords.service
[Unit]
Description=Oracle Rest Data Services
After=syslog.target network.target
[Service]
Type=simple
User=ords
Group=ords
Restart=always
RestartSec=30
Environment="_JAVA_OPTIONS=-Xms3G -Xmx3G -javaagent:/home/ords/jmx_prometheus_javaagent.jar=10.10.10.10:21022:/home/ords/config.yaml"
Environment="JAVA_HOME=/home/ords/graalvm"
#Environment="JAVA_TOOL_OPTIONS=-Djava.util.logging.config.file=/home/ords/logging.conf"
ExecStart=/home/ords/ords/bin/ords --config /etc/ords/config serve --secure --port 8443 --key /etc/ords/server.key --certificate /etc/ords/server.pem
[Install]
WantedBy=multi-user.target
After restarting ORDS I can query its metrics endpoint.
curl http://10.10.10.10:21022/metrics
# You will see many Java and JVM metrics in the output. Example...
jvm_memory_pool_max_bytes{pool="Compressed Class Space"} 1.073741824E9
jvm_memory_pool_max_bytes{pool="G1 Eden Space"} -1.0
jvm_memory_pool_max_bytes{pool="G1 Old Gen"} 3.221225472E9
jvm_memory_pool_max_bytes{pool="G1 Survivor Space"} -1.0
jvm_memory_pool_max_bytes{pool="Metaspace"} -1.0
jvm_memory_pool_used_bytes{pool="CodeHeap 'non-nmethods'"} 1822336.0
jvm_memory_pool_used_bytes{pool="CodeHeap 'non-profiled nmethods'"} 5918080.0
jvm_memory_pool_used_bytes{pool="CodeHeap 'profiled nmethods'"} 2.3397888E7
jvm_memory_pool_used_bytes{pool="Compressed Class Space"} 7328848.0
jvm_memory_pool_used_bytes{pool="G1 Eden Space"} 2.57949696E8
jvm_memory_pool_used_bytes{pool="G1 Old Gen"} 2.280663304E9
jvm_memory_pool_used_bytes{pool="G1 Survivor Space"} 8528.0
jvm_memory_pool_used_bytes{pool="Metaspace"} 6.750048E7
jvm_memory_used_bytes{area="heap"} 2.538621528E9
jvm_memory_used_bytes{area="nonheap"} 1.05967632E8
jvm_threads_deadlocked_monitor 0.0
jvm_threads_peak 62.0
jvm_threads_started_total 62.0
jvm_threads_state{state="BLOCKED"} 0.0
jvm_threads_state{state="NEW"} 0.0
jvm_threads_state{state="RUNNABLE"} 12.0
jvm_threads_state{state="TERMINATED"} 0.0
jvm_threads_state{state="TIMED_WAITING"} 20.0
jvm_threads_state{state="UNKNOWN"} 0.0
jvm_threads_state{state="WAITING"} 15.0
# ORDS database connection pool metrics will be exported like this
# Just an example... all UCP attributes are exported, for all ORDS connection pools
oracle_ucp_abandonedConnectionTimeout{poolName="backoffice_lo"} 0.0
oracle_ucp_abandonedConnectionTimeout{poolName="marketing_communications_2_lo"} 0.0
oracle_ucp_abandonedConnectionsCount{poolName="backoffice_lo"} 0.0
oracle_ucp_abandonedConnectionsCount{poolName="marketing_communications_2_lo"} 0.0
oracle_ucp_availableConnectionsCount{poolName="backoffice_lo"} 10.0
oracle_ucp_availableConnectionsCount{poolName="marketing_communications_2_lo"} 10.0
oracle_ucp_averageBorrowedConnectionsCount{poolName="backoffice_lo"} 1.0
oracle_ucp_averageBorrowedConnectionsCount{poolName="marketing_communications_2_lo"} 1.0
oracle_ucp_averageConnectionWaitTime{poolName="backoffice_lo"} 0.0
oracle_ucp_averageConnectionWaitTime{poolName="marketing_communications_2_lo"} 0.0
oracle_ucp_borrowedConnectionsCount{poolName="backoffice_lo"} 0.0
oracle_ucp_borrowedConnectionsCount{poolName="marketing_communications_2_lo"} 0.0
oracle_ucp_bufferSize{poolName="backoffice_lo"} 1024.0
oracle_ucp_bufferSize{poolName="marketing_communications_2_lo"} 1024.0
oracle_ucp_connectionHarvestMaxCount{poolName="backoffice_lo"} 1.0
oracle_ucp_connectionHarvestMaxCount{poolName="marketing_communications_2_lo"} 1.0
oracle_ucp_connectionHarvestTriggerCount{poolName="backoffice_lo"} 2.147483647E9
oracle_ucp_connectionHarvestTriggerCount{poolName="marketing_communications_2_lo"} 2.147483647E9
oracle_ucp_connectionRepurposeCount{poolName="backoffice_lo"} 0.0
oracle_ucp_connectionRepurposeCount{poolName="marketing_communications_2_lo"} 0.0
oracle_ucp_connectionValidationTimeout{poolName="backoffice_lo"} 15.0
oracle_ucp_connectionValidationTimeout{poolName="marketing_communications_2_lo"} 15.0
oracle_ucp_connectionWaitTimeout{poolName="backoffice_lo"} 3.0
oracle_ucp_connectionWaitTimeout{poolName="marketing_communications_2_lo"} 3.0
oracle_ucp_connectionsClosedCount{poolName="backoffice_lo"} 0.0
oracle_ucp_connectionsClosedCount{poolName="marketing_communications_2_lo"} 0.0
oracle_ucp_connectionsCreatedCount{poolName="backoffice_lo"} 10.0
oracle_ucp_connectionsCreatedCount{poolName="marketing_communications_2_lo"} 10.0
oracle_ucp_createConnectionInBorrowThread{poolName="backoffice_lo"} 1.0
oracle_ucp_createConnectionInBorrowThread{poolName="marketing_communications_2_lo"} 1.0
oracle_ucp_cumulativeConnectionBorrowedCount{poolName="backoffice_lo"} 1.0
oracle_ucp_cumulativeConnectionBorrowedCount{poolName="marketing_communications_2_lo"} 1.0
oracle_ucp_cumulativeConnectionReturnedCount{poolName="backoffice_lo"} 1.0
oracle_ucp_cumulativeConnectionReturnedCount{poolName="marketing_communications_2_lo"} 1.0
oracle_ucp_cumulativeConnectionUseTime{poolName="backoffice_lo"} 60.0
oracle_ucp_cumulativeConnectionUseTime{poolName="marketing_communications_2_lo"} 30.0
oracle_ucp_cumulativeConnectionWaitTime{poolName="backoffice_lo"} 0.0
oracle_ucp_cumulativeConnectionWaitTime{poolName="marketing_communications_2_lo"} 0.0
oracle_ucp_cumulativeFailedConnectionWaitCount{poolName="backoffice_lo"} 0.0
oracle_ucp_cumulativeFailedConnectionWaitCount{poolName="marketing_communications_2_lo"} 0.0
oracle_ucp_cumulativeFailedConnectionWaitTime{poolName="backoffice_lo"} 0.0
oracle_ucp_cumulativeFailedConnectionWaitTime{poolName="marketing_communications_2_lo"} 0.0
oracle_ucp_cumulativeSuccessfulConnectionWaitCount{poolName="backoffice_lo"} 0.0
oracle_ucp_cumulativeSuccessfulConnectionWaitCount{poolName="marketing_communications_2_lo"} 0.0
oracle_ucp_cumulativeSuccessfulConnectionWaitTime{poolName="backoffice_lo"} 0.0
oracle_ucp_cumulativeSuccessfulConnectionWaitTime{poolName="marketing_communications_2_lo"} 0.0
oracle_ucp_failedAffinityBasedBorrowCount{poolName="backoffice_lo"} 0.0
oracle_ucp_failedAffinityBasedBorrowCount{poolName="marketing_communications_2_lo"} 0.0
oracle_ucp_failedRCLBBasedBorrowCount{poolName="backoffice_lo"} 0.0
oracle_ucp_failedRCLBBasedBorrowCount{poolName="marketing_communications_2_lo"} 0.0
oracle_ucp_failoverEnabled{poolName="backoffice_lo"} 0.0
oracle_ucp_failoverEnabled{poolName="marketing_communications_2_lo"} 0.0
oracle_ucp_inactiveConnectionTimeout{poolName="backoffice_lo"} 1800.0
oracle_ucp_inactiveConnectionTimeout{poolName="marketing_communications_2_lo"} 1800.0
oracle_ucp_initialPoolSize{poolName="backoffice_lo"} 10.0
oracle_ucp_initialPoolSize{poolName="marketing_communications_2_lo"} 10.0
oracle_ucp_labeledConnectionsCount{poolName="backoffice_lo"} 0.0
oracle_ucp_labeledConnectionsCount{poolName="marketing_communications_2_lo"} 0.0
oracle_ucp_loggingEnabled{poolName="backoffice_lo"} 0.0
oracle_ucp_loggingEnabled{poolName="marketing_communications_2_lo"} 0.0
oracle_ucp_maxConnectionReuseCount{poolName="backoffice_lo"} 1000.0
- Written by: ilmarkerm
- Category: Blog entry
- Published: November 6, 2024
I’m forced to write my first JavaScript related post 🙁 Oh, well.
ORDS version at the time of writing is 24.3.
Oracle Rest Data Services (ORDS) does support (currently read-only) GraphQL protocol for serving data from Oracle rest enabled tables. You can read more about it here: https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-graphql
To get GraphQL support working, ORDS need to be running using GraalVM JDK – but it’s not as simple as switching the JDK – GraalVM also needs to support JavaScript polyglot engine. When I started looking into this world I was properly confused, starting with naming – ORDS 24.3 installation checklist still requires GraalVM Enterprise Edition and when going to search for it find man-bear-pigs like GraalVM-EE-23-for-JDK-17. Properly confusing for a non-developer like me.
Luckily naming has been significantly simplified recently and GraalVM EE is dead
Naming starting from GraalVM for JDK21 is simplified, but what has gone much more complicated is installing JavaScript polyglot libraries for GraalVM. With GraalVM for JDK 17 there was a command “gu install” for it, but it has been removed starting from GraalVM for JDK 21.
ORDS installation checklist 24.3 acknowledges it, but then gives some strange XML code on how to install them. Not helpful for non-developers, like me. This XML is intended to describe dependencies for Java project (using Maven), so during build the dependencies would be fetched automatically. But I have nothing to build – GraphQL support is already in ORDS, I just need the dependencies downloaded.
I think, if the extra libraries are needed for ORDS built in functionality to work, ORDS should include them by default.
Installation steps
I know pretty much all software can be downloaded using Oracle provided yum repositories, but here I’m doing everything manually, to be able to control the versions precisely. And not to mess with RPM-s, unzipping this is so much easier and predictable and more usable across all possible Linux distributions.
All software is placed under /home/ords in my example.
First lets download GraalVM for JDK 21 itself. Oracle has started to offer non-website-clicking “script friendly” URLs that always point to the latest version, you can get them here. I’m not going to use ANY latest URL Oracle offers on purpose, since I’m an automation guy I need to be able to download predictable and internally tested versions of the software and be able to validate the downloaded software against known checksum value.
Download the software, all versions are current at the time of writing, but of course are very soon out of date
# GraalVM for JDK21
https://download.oracle.com/graalvm/21/archive/graalvm-jdk-21.0.5_linux-x64_bin.tar.gz
# Maven
https://dlcdn.apache.org/maven/maven-3/3.9.9/binaries/apache-maven-3.9.9-bin.tar.gz
# ORDS
https://download.oracle.com/otn_software/java/ords/ords-24.3.0.262.0924.zip
In Ansible something like this, also unzipping them. Take it as an example, and not copy it blindly
# Facts
graalvm_download_url: "https://download.oracle.com/graalvm/21/archive/graalvm-jdk-21.0.5_linux-x64_bin.tar.gz"
graalvm_download_checksum: "sha256:c1960d4f9d278458bde1cd15115ac2f0b3240cb427d51cfeceb79dab91a7f5c9"
graalvm_install_dir: "{{ ords_install_base }}/graalvm"
maven_download_url: "https://dlcdn.apache.org/maven/maven-3/3.9.9/binaries/apache-maven-3.9.9-bin.tar.gz"
maven_download_checksum: "sha512:a555254d6b53d267965a3404ecb14e53c3827c09c3b94b5678835887ab404556bfaf78dcfe03ba76fa2508649dca8531c74bca4d5846513522404d48e8c4ac8b"
maven_install_dir: "{{ ords_install_base }}/maven"
ords_download_url: "https://download.oracle.com/otn_software/java/ords/ords-24.3.0.262.0924.zip"
ords_installer_checksum: "sha1:6e8d9b15faa232911fcff367c99ba696389ceddc"
ords_install_dir: "{{ ords_install_base }}/ords"
ords_install_base: "/home/ords"
# Tasks
- name: Download GraalVM
ansible.builtin.get_url:
url: "{{ graalvm_download_url }}"
dest: "{{ ords_install_base }}/graalvm.tar.gz"
checksum: "{{ graalvm_download_checksum }}"
use_proxy: "{{ 'yes' if http_proxy is defined and http_proxy else 'no' }}"
environment:
https_proxy: "{{ http_proxy }}"
register: graalvm_downloaded
- name: Unzip GraalVM
ansible.builtin.unarchive:
remote_src: yes
src: "{{ ords_install_base }}/graalvm.tar.gz"
dest: "{{ graalvm_install_dir }}"
extra_opts:
- "--strip-components=1"
when: graalvm_downloaded is changed
# Download maven
- name: Download Maven
ansible.builtin.get_url:
url: "{{ maven_download_url }}"
dest: "{{ ords_install_base }}/maven.tar.gz"
checksum: "{{ maven_download_checksum }}"
use_proxy: "{{ 'yes' if http_proxy is defined and http_proxy else 'no' }}"
environment:
https_proxy: "{{ http_proxy }}"
register: maven_downloaded
- name: Unzip maven
ansible.builtin.unarchive:
remote_src: yes
src: "{{ ords_install_base }}/maven.tar.gz"
dest: "{{ maven_install_dir }}"
extra_opts:
- "--strip-components=1"
when: maven_downloaded is changed
# Download ORDS
- name: Download ORDS
ansible.builtin.get_url:
url: "{{ ords_download_url }}"
dest: "{{ ords_install_base }}/ords-latest.zip"
checksum: "{{ ords_installer_checksum }}"
use_proxy: "{{ 'yes' if http_proxy is defined and http_proxy else 'no' }}"
environment:
https_proxy: "{{ http_proxy }}"
register: ords_downloaded
- name: Unzip ORDS installer
ansible.builtin.unarchive:
remote_src: yes
src: "{{ ords_install_base }}/ords-latest.zip"
dest: "{{ ords_install_dir }}"
when: ords_downloaded is changed
Now the complicated part, adding GraalVM JavaScript polyglot libraries
Create pom.xml file in some directory with contents. 24.1.1 is the current JavaScript engine version, you can see the available versions in https://mvnrepository.com/artifact/org.graalvm.polyglot/polyglot
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>eu.ilmarkerm.ords</groupId>
<artifactId>ords-graaljs-download</artifactId>
<version>1.1.1</version>
<url>https://ilmarkerm.eu</url>
<name>POM to download GraalVM JavaScript engine</name>
<dependencies>
<dependency>
<groupId>org.graalvm.polyglot</groupId>
<artifactId>polyglot</artifactId>
<version>24.1.1</version>
</dependency>
<dependency>
<groupId>org.graalvm.polyglot</groupId>
<!-- Language: js -->
<artifactId>js</artifactId>
<version>24.1.1</version>
<type>pom</type>
</dependency>
</dependencies>
</project>
Execute maven to download the required libraries and place them under ORDS libraries
/home/ords/maven/bin/mvn dependency:copy-dependencies -DoutputDirectory=/home/ords/ords/lib/ext -DuseBaseVersion=true
It will download files like these (polyglot 24.1.1):
collections-24.1.1.jar
icu4j-24.1.1.jar
jniutils-24.1.1.jar
js-language-24.1.1.jar
nativebridge-24.1.1.jar
nativeimage-24.1.1.jar
polyglot-24.1.1.jar
regex-24.1.1.jar
truffle-api-24.1.1.jar
truffle-compiler-24.1.1.jar
truffle-enterprise-24.1.1.jar
truffle-runtime-24.1.1.jar
word-24.1.1.jar
Start ORDS (using GraalVM JDK) and then ORDS support for GraphQL is ready to be used.
Conclusion
I’m not a java developer, so things might be wrong here 🙂
But I do hope the situation improves over the next couple of ORDS versions.
- Written by: ilmarkerm
- Category: Blog entry
- Published: August 21, 2024
I previously wrote about how after successfully downgrading APEX users still get the error “Application Express is currently unavailable”. I now ran to the same issue again, with newer versions and the procedure how ORDS detects is APEX is currently being patched has changed.
This post is about downgrading to APEX 23.1 and ORDS 24.1.
After completing APEX downgrade to 23.1, ORDS 24.1 still reported that “Application Express is currently unavailable”. I followed my own previous blog and flipped the APEX internal patching status to APPLIED, but no luck this time. PANIC! I turned on tracking for ORDS sessions and this PL/SQL block seems to be the culprit.
DECLARE
NM OWA.VC_ARR := :1 ;
VL OWA.VC_ARR := :2 ;
L_PATCH VARCHAR2(10) := null;
L_VERSION VARCHAR2(100) := null;
SCHEMA VARCHAR2(30);
PART1 VARCHAR2(30);
PART2 VARCHAR2(30);
DBLINK VARCHAR2(30);
PART1_TYPE NUMBER(10);
OBJECT_NUM NUMBER(10);
BEGIN
OWA.INIT_CGI_ENV(:3 , NM, VL);
HTP.INIT;
HTP.HTBUF_LEN := 63;
--CHECK THE SCHEMA OVERRIDE FOR PL/SQL GATEWAY APPLICATION DETAILS
DECLARE
GATEWAY_DETAILS VARCHAR2(2000) := q'[ SELECT
(SELECT VALUE FROM ORDS_METADATA.USER_ORDS_PROPERTIES WHERE KEY = 'plsql.gateway.patching') as patchingValue,
(SELECT VALUE FROM ORDS_METADATA.USER_ORDS_PROPERTIES WHERE KEY = 'plsql.gateway.version') as versionValue
FROM dual]';
BEGIN
BEGIN
EXECUTE IMMEDIATE GATEWAY_DETAILS INTO L_PATCH, L_VERSION;
EXCEPTION
WHEN OTHERS THEN
-- ignore exception and leave l_patch as null so fallback is executed
NULL;
END;
IF L_PATCH IS NULL THEN
-- L_PATCH IS NULL, EITHER AN OLDER VERSION OF APEX IS PRESENT OR APEX IS NOT INSTALLED
-- CHECK IF APEX PRESENT
DECLARE
L_SQL VARCHAR2(200) := 'select patch_applied from apex_release';
BEGIN
DBMS_UTILITY.NAME_RESOLVE('APEX_RELEASE', 0, SCHEMA, PART1, PART2, DBLINK, PART1_TYPE, OBJECT_NUM);
EXECUTE IMMEDIATE L_SQL INTO L_PATCH;
EXCEPTION
WHEN OTHERS THEN
-- Could not access apex_release. Default l_patch to 'N' so it's still served
L_PATCH :='N';
END;
END IF;
END;
:4 := L_PATCH;
:5 :=L_VERSION;
END;
The flag that tells ORDS that APEX is currently being patched has been moved to ORDS_METADATA schema. And in my case the query indeed returned TRUE.
SELECT VALUE FROM ORDS_METADATA.USER_ORDS_PROPERTIES WHERE KEY = 'plsql.gateway.patching'
After flipping it to FALSE, downgraded APEX started working again.
UPDATE ORDS_METADATA.USER_ORDS_PROPERTIES set value='FALSE' WHERE KEY = 'plsql.gateway.patching';
- Written by: ilmarkerm
- Category: Blog entry
- Published: November 14, 2023
Oracle Rest Data Services (ORDS) is a HTTP frontend for various Oracle Database related tasks – database admin APIs, SQL Developer web and most famously ofcourse APEX. It is a java program and for a few years now it comes with a built in Jetty web server that is recommended to use for production workloads – without Tomcat, Weblogic or any other Java servlet container.
All the setup guides for ORDS in the Internet start ORDS usually on port 8443, or any other high port. But the default port for HTTPS is 443. If ORDS should be used without any additional web server/proxy, then wouldn’t it be nice to use the default HTTPS port already directly for ORDS?
This brings a little problem – Linux does not allow non-privileged users to open ports lower than 1024 and I really do not think it is a good idea to run ORDS as root.
The easiest way to achieve this I’ve found is to just use FirewallD to create an internal port-forward. I’ve tested this on Oracle Linux 8.
As an one-time operation, as root, configure FirewallD to allow incoming port 443 and then forward all traffic from 443 to 8443.
firewall-cmd --add-port=443/tcp --permanent
firewall-cmd --add-forward-port=port=443:proto=tcp:toport=8443 --permanent
firewall-cmd --reload
Then, start ORDS normally on port 8443, using non-privileged user.