Setting Up Customer-Managed ORDS 24.3 on Oracle Autonomous Database: A Complete Guide
Part 1: Setting Up Customer-Managed ORDS 24.3 on Oracle Autonomous Database
Introduction
In this step-by-step guide, we will walk you through the process of setting up Customer-Managed ORDS 24.3 on Oracle Autonomous Database.
Usually, after installing APEX on the database, users need to install, configure, and deploy ORDS. But with the Autonomous Database, ORDS comes preinstalled on a separate, robust and scalable hardware infrastructure. When you use the default ORDS on Autonomous Database, you can't change any of the ORDS configuration options.
Use a Customer-Managed environment if you want manual control over the configuration and management of ORDS. For example, choose this option if your applications need larger connection pools or if you want more control over the ORDS configuration options. Plus, this lets us run APEX & ORDS on our own custom domain in an Always free tier.
As you can see in the architecture diagram below, there's quite a bit to cover. So, we'll split this blog into several parts. Let's begin with setting up the infrastructure and installing the Customer-Managed ORDS.
Create a Compute Instance
To start, let's create a new Compute Instance using Oracle Linux 8. Simply log in to your OCI console and open the OCI navigation menu. Navigate to Compute and click Instances, then click on Create instance.
Make sure you are in the right Compartment!
For this Tutorial, you can use the always-free shape e.g. VM.Standard.E2.1.Micro. Under Networking, select your VCN if it exists, or create a new one with a public subnet. Ensure that you download your SSH keys and store them in a secure location, as they will be necessary to connect to your instance during the installation of your custom-managed ORDS.
For more details, you can read how to create a new instance on a public subnet here.
Now that the first step is done, we can start provisioning the Autonomous Database.
If you prefer to assign a Public Reserved IP address to the instance you can read here how to do it.
Provisioning an Autonomous Database
After the Compute Instance has been created, the Autonomous Database can be set up. To do this, you have to switch to "Oracle Database > Autonomous Database" in the navigation and create a new database there. Since this example is aimed at an APEX application, "APEX" can be selected as the workload type.
The "APEX" workload type has slightly restricted functionality, but is cheaper!
Then click the "Always Free" toggle, or if you prefer, you can set up a paid version. Next, choose the database version, like 23ai 🔥
Finally, enter your Admin Credentials and click Create.
Once the ADB is ready, we can download the Wallet, which we'll need later during the ORDS installation.
Downloading the Wallet
To do that, simply click on "Database connection" and then click the "Download wallet" button.
Set up the APEX static resources (optional)
Oracle recommends using the Oracle Content Delivery Network to set up the APEX static resources. To do this, open the Database Actions and go to the SQL Worksheet. Then, execute the following PL/SQL command to set the IMAGE_PREFIX parameter to the APEX 24.1.1 static resources CDN.
begin
apex_instance_admin.set_parameter(
p_parameter => 'IMAGE_PREFIX',
p_value => 'https://static.oracle.com/cdn/apex/24.1.1/' );
commit;
end;
Installing and Configuring Customer-Managed ORDS on Autonomous Database
Next, we need to install the Customer-Managed ORDS on our Compute Instance. Let's begin by connecting to our Instance. From a Terminal connect to your Oracle Cloud VM:
ssh -i ssh-key-custom-ords-instance.key opc@130.61.249.6
You can read here how to connect to your instance in three different ways (Cloud Shell, PuTTY and Bastion).
As a standard procedure, it is recommended to update the system with the latest software when working on a VM. Simply execute the following command to achieve this.
sudo yum update
This will take a few minutes! After that, you will see a summary of the updates.
As an Oracle Cloud Infrastructure (OCI) customer, you will have access to software like Oracle Instant Client, Java runtimes, and ORDS through a private YUM repository. To enable the repository, just run the following command:
sudo yum-config-manager --enable ol8_oci_included
Installing Java
Before proceeding, it's essential to ensure that your system has Java 11 or higher installed. If not, install Java using the following commands:
sudo yum install java-21-openjdk.x86_64 -y
Verify your Java version.
java -version
Upload Wallet
Before we start the ORDS installation, we need to upload the Wallet from our ADB.
To do this, run the following command:
scp -i ssh-key-custom-ords-instance.key /Users/therwix/Desktop/Wallet.zip opc@130.61.249.6:/tmp
Don't run the command on the Compute Instance itself and make sure to adjust the Wallet-path, the SSH-key, and the IP-address in the command.
After that, just connect to your Compute Instance again and move the Wallet to the following path and set the right permissions, for example:
sudo mv /tmp/Wallet.zip /opt/oracle
sudo chown oracle:oinstall /opt/oracle/Wallet.zip
Installing and Configuring Customer-Managed ORDS
Now it's time to install ORDS! Just run the command below to get started.
sudo yum install ords -y
If everything worked correctly, it should look something like this.
The next step is to configure your Customer-Managed ORDS. Use the ORDS Command-Line Interface (CLI), which will guide you through the setup by asking for the necessary information.
sudo -iu oracle
ords install adb --interactive --prompt-password
Enter the following installation prompts to continue:
Entering the Autonomous Database Wallet Path: /opt/oracle/Wallet.zip
Enter a number to select the TNS Network alias to use: e.g. 1
Enter the Administrator Username: ADMIN
Enter the Database Password for ADMIN: Password1234!
Entering the ORDS Runtime Database Username: ORDS_PUBLIC_USER2
Entering the Database Password for ORDS_PUBLIC_USER2: Password1234!
Entering the PL/SQL Gateway Database Username: ORDS_PLSQL_GATEWAY2
Enter the Database Password for ORDS_PLSQL_GATEWAY2: Password1234!
Entering a Number to Select and Enable Additional Feature: 1
Enter a Number to Configure and Start ORDS: 1
Entering a Number to Select a Protocol: 1
Entering the HTTP Port: 8080
When you're all done, it should look something like this.
Configure Firewall and Ingress Rules
Make sure you have an OCI Security List rule that opens port 8080 and makes it accessible to your client machine. On the Compute instance, you also need to ensure that the firewall port is open.
Just run the following command as opc or root user:
sudo firewall-cmd --permanent --zone=public --add-port 8080/tcp
sudo firewall-cmd --reload
Next, we need to update the Ingress Rules for the VCN to allow incoming HTTP traffic. So it is not enough to open the VM's firewall.
Navigate to "Networking > Virtual Cloud Networks" and select the VCN where the VM is located. Under "Resources" click on "Security Lists" and there on the "Default Security Lists" of your network. As you can see, there are three rules by default.
For HTTP traffic, we need one more rule. So click on "Add Ingress Rules".
Select CIDR as Source Type and as Source CIDR enter 0.0.0.0/0 so that everything can connect to it, and as Destination Port Range enter 8080 for the HTTP rule .
Start your Customer-Managed ORDS
Finally, let's start ORDS and set it up to start automatically on boot with the following command.
sudo systemctl start ords
sudo systemctl enable ords
Test your Customer-Managed ORDS
With the setup now complete, you have the ability to access APEX or SQL-Developer Web by using the Compute instance's IP address, along with the assigned port 8080.
http://130.61.249.6:8080/ords/
Conclusion
In this step-by-step guide, you've successfully gone through the process of installing and setting up a Customer-Managed ORDS on Oracle Autonomous Database. From creating a Compute Instance and provisioning an Autonomous Database to setting up the APEX static resources and configuring network rules, we've covered everything to ensure a smooth setup. Now that your Custom-Managed ORDS is up and running, you're all set to manage and access your APEX applications and SQL Developer Web with ease, boosting your database management skills.
Next, we'll explore how to enable SSL and set up a custom URL.