Ilmar Kerm

Oracle, databases, Linux and maybe more

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.

In the previous post I looked how to create Oracle database home in OCI, using custom patches. Here I create a Oracle Base database on top of that custom home.

I was struggling for days with this, mostly because I first wanted to create database on LVM storage, but after running for many many hours, the provisioning got stuck always at 42%, without any error messages. Finally, with tons of experimentation, I found out that if I replace LVM with ASM storage, it all just works.

# List all availability domainsdata "oci_identity_availability_domains" "ads" {
    compartment_id = oci_identity_compartment.compartment.id
}

# Create the Oracle Base database
resource "oci_database_db_system" "test23ai" {
    # Waits until DB system is provisioned
    # It can take a few hours
    availability_domain = data.oci_identity_availability_domains.ads.availability_domains[0].name
    compartment_id = oci_identity_compartment.compartment.id
    db_home {
        database {
            admin_password = "correct#HorseBatt5eryS1-_ple"
            character_set = "AL32UTF8"
            #database_software_image_id = oci_database_database_software_image.db_23051.id
            db_name = "test23ai"
            pdb_name = "PDB1"
            db_backup_config {
                auto_backup_enabled = false
            }
        }
        db_version = oci_database_database_software_image.db_23051.patch_set
        database_software_image_id = oci_database_database_software_image.db_23051.id
    }
    hostname = "test23ai1"
    shape = "VM.Standard.E5.Flex"
    ssh_public_keys = ["ssh-rsa AAAAB3NzaC1yc2EAAA paste your own ssh public key here"]
    subnet_id = oci_core_subnet.subnet.id

    cpu_core_count = 2
    data_storage_size_in_gb = 256
    database_edition = "ENTERPRISE_EDITION"
    db_system_options {
        storage_management = "ASM" # LVM did not work for me, provisioning stuck at 42% for many many hours until it times out
    }
    display_name = "Test 23ai"
    domain = "dev.se1"
    #fault_domains = var.db_system_fault_domains
    license_model = "LICENSE_INCLUDED"
    node_count = 1
    # Network Security Groups
    #nsg_ids = var.db_system_nsg_ids
    source = "NONE"
    storage_volume_performance_mode = "BALANCED"
    time_zone = "UTC"
}

And to see the connection options, they are visible from terraform state. Here we see database connection information, donnection strings in different formats are visible – so your terraform code could now take these values and store it in some service discovery/parameter store.

ilmar_kerm@codeeditor:oci-terraform-example (eu-stockholm-1)$ terraform state show oci_database_db_system.test23ai

# oci_database_db_system.test23ai:
resource "oci_database_db_system" "test23ai" {
    availability_domain             = "AfjF:EU-STOCKHOLM-1-AD-1"
    compartment_id                  = "ocid1.compartment.oc1..aaaa"
    cpu_core_count                  = 2
    data_storage_percentage         = 80
    data_storage_size_in_gb         = 256
    database_edition                = "ENTERPRISE_EDITION"
    defined_tags                    = {
        "Oracle-Tags.CreatedBy" = "default/ilmar.kerm@gmail.com"
        "Oracle-Tags.CreatedOn" = "2024-10-02T14:57:03.606Z"
    }
    disk_redundancy                 = "HIGH"
    display_name                    = "Test 23ai"
    domain                          = "dev.se1"
    fault_domains                   = [
        "FAULT-DOMAIN-2",
    ]
    freeform_tags                   = {}
    hostname                        = "test23ai1"
    id                              = "ocid1.dbsystem.oc1.eu-stockholm-1.anqxeljr4ebxpbqanr42p2zebku5hdk5nci2"
    iorm_config_cache               = []
    license_model                   = "LICENSE_INCLUDED"
    listener_port                   = 1521
    maintenance_window              = []
    memory_size_in_gbs              = 32
    node_count                      = 1
    reco_storage_size_in_gb         = 256
    scan_ip_ids                     = []
    shape                           = "VM.Standard.E5.Flex"
    source                          = "NONE"
    ssh_public_keys                 = [
        "ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQCvccb2GOc+VU6V0lw367a5sgKqn0epAok9vCVboK6WvQid6byo7hkWUSixIuB6ZPGG89n3ig4r my ssh public key",
    ]
    state                           = "AVAILABLE"
    storage_volume_performance_mode = "BALANCED"
    subnet_id                       = "ocid1.subnet.oc1.eu-stockholm-1.aaaaaaaal6ru"
    time_created                    = "2024-10-02 14:57:04.037 +0000 UTC"
    time_zone                       = "UTC"
    version                         = "23.5.0.24.07"
    vip_ids                         = []

    data_collection_options {
        is_diagnostics_events_enabled = false
        is_health_monitoring_enabled  = false
        is_incident_logs_enabled      = false
    }

    db_home {
        create_async               = false
        database_software_image_id = "ocid1.databasesoftwareimage.oc1.eu-stockholm-1.anqxeljr4ebxpbqa3h6dawz"
        db_home_location           = "/u01/app/oracle/product/23.0.0.0/dbhome_1"
        db_version                 = "23.5.0.24.07"
        defined_tags               = {}
        display_name               = "dbhome20241002145704"
        freeform_tags              = {}
        id                         = "ocid1.dbhome.oc1.eu-stockholm-1.anqxeljrb3hetziaby6hwudav"
        state                      = "AVAILABLE"
        time_created               = "2024-10-02 14:57:04.042 +0000 UTC"

        database {
            admin_password             = (sensitive value)
            character_set              = "AL32UTF8"
            connection_strings         = [
                {
                    all_connection_strings = {
                        "cdbDefault"   = "test23ai1.dev.se1:1521/test23ai_rf6_arn.dev.se1"
                        "cdbIpDefault" = "(DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.2.80)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=test23ai_rf6_arn.dev.se1)))"
                    }
                    cdb_default            = "test23ai1.dev.se1:1521/test23ai_rf6_arn.dev.se1"
                    cdb_ip_default         = "(DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.2.80)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=test23ai_rf6_arn.dev.se1)))"
                },
            ]
            database_software_image_id = "ocid1.databasesoftwareimage.oc1.eu-stockholm-1.anqxeljr4ebxpbqa3h6"
            db_name                    = "test23ai"
            db_unique_name             = "test23ai_rf6_arn"
            db_workload                = "OLTP"
            defined_tags               = {
                "Oracle-Tags.CreatedBy" = "default/ilmar.kerm@gmail.com"
                "Oracle-Tags.CreatedOn" = "2024-10-02T14:57:03.740Z"
            }
            freeform_tags              = {}
            id                         = "ocid1.database.oc1.eu-stockholm-1.anqxeljr4ebxpbqac3v4lrr"
            ncharacter_set             = "AL16UTF16"
            pdb_name                   = "PDB1"
            pluggable_databases        = []
            state                      = "AVAILABLE"
            time_created               = "2024-10-02 14:57:04.043 +0000 UTC"

            db_backup_config {
                auto_backup_enabled       = false
                auto_full_backup_day      = "SUNDAY"
                recovery_window_in_days   = 0
                run_immediate_full_backup = false
            }
        }
    }

    db_system_options {
        storage_management = "ASM"
    }
}

I’ll continue exploring using OCI services with Terraform. Now it is time to start looking into databases. High Oracle PM-s have been lobbying for a database image creation service, where you just supply patch numbers and Oracle will return you the fully built database home. I see that this service is now available in the cloud (for cloud databases only).

I’ll try it out, using terraform.

resource "oci_database_database_software_image" "db_23051" {
    # NB! Waits until image is provisioned
    # This took 10m47s to provision
    compartment_id = oci_identity_compartment.compartment.id
    display_name = "23-db-23051"

    image_shape_family = "VM_BM_SHAPE" # For use in Database Base service
    # oci db version list
    # NB! 23.0.0.0 seems to be behind on patches, 23.0.0.0.0 seems to be current    
    database_version = "23.0.0.0.0"
    image_type = "DATABASE_IMAGE"
    # Can't find how to query that list - but the format seems quite self-explanatory
    # Exadata Cloud Service Software Versions (Doc ID 2333222.1)
    patch_set = "23.5.0.24.07"
}

I had hard time finding out the allowed values for parameter patch_set, but they seem to be described in Doc ID 2333222.1 (and what the contents are).

Examining the state of the created resource

ilmar_kerm@codeeditor:oci-terraform-example (eu-stockholm-1)$ terraform state show oci_database_database_software_image.db_23051

# oci_database_database_software_image.db_23051:
resource "oci_database_database_software_image" "db_23051" {
    compartment_id                           = "ocid1.compartment.oc1..aaaaaaaasbzzr7i54kpv6oc5s7i23isiij6n2tyentd5udc34ptzagovrgqa"
    database_software_image_included_patches = [
        "35221462",
        "36741532",
        "36744688",
    ]
    database_software_image_one_off_patches  = [
        "35221462",
        "36741532",
        "36744688",
    ]
    database_version                         = "23.0.0.0.0"
    defined_tags                             = {
        "Oracle-Tags.CreatedBy" = "default/ilmar.kerm@gmail.com"
        "Oracle-Tags.CreatedOn" = "2024-09-29T12:36:02.119Z"
    }
    display_name                             = "23-db-23051"
    freeform_tags                            = {}
    id                                       = "ocid1.databasesoftwareimage.oc1.eu-stockholm-1.anqxeljr4ebxpbqadhgioquzxv6qtrui72e3sn3c7iwxcljncmdq7fx5jdbq"
    image_shape_family                       = "VM_BM_SHAPE"
    image_type                               = "DATABASE_IMAGE"
    is_upgrade_supported                     = false
    patch_set                                = "23.5.0.24.07"
    state                                    = "AVAILABLE"
    time_created                             = "2024-09-29 12:36:02.123 +0000 UTC"
}

One thing I notice here (verified with testing), that the parameter database_software_image_one_off_patches gets pre-populated with included patches after the image is created – so you have to include the included patches also the the parameter value.

With 19c version process is similar

resource "oci_database_database_software_image" "db_19241" {
    # NB! Waits until image is provisioned
    # This took 16m4s to provision
    compartment_id = oci_identity_compartment.compartment.id
    display_name = "19-db-19241"

    image_shape_family = "VM_BM_SHAPE" # For use in Database Base service
    # oci db version list
    database_version = "19.0.0.0"
    image_type = "DATABASE_IMAGE"
    patch_set = "19.24.0.0"
}

I did try to apply MRP on top of it, but maybe the cloud patch numbers are different, since the usual MRP patch number did not apply on top of it.

In the next post I’ll try to spin up an actual database using the image.

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';

We are refreshing development databases from production using storage thin cloning. Everything works automatically and part of refresh procedure is also running some SQLPlus scripts, using OS authentication, as SYS.

One database also has APEX, that uses APEX Social Login feature to give users single sign-on for both APEX Builder and the end user application. You can read here how to set it up using Azure SSO. But since APEX is fully inside the database, this means that the production SSO credentials get copied over during database refresh.

I would like to have a SQL script that would run as part of the database refresh procedure, that would replace the APEX SSO credentials. And I’d like that script to run as SYS.