Automate Oracle APEX Deployment for OCI Database Using Terraform

Recently, I was assisting a customer design & configure APEX (Oracle Application Express) for databases on OCI. The purpose of this blog is to augment some finer details, tips & tricks that may help with successful installation (already documented in some detail in this whitepaper.)

Let's quickly look at the architecture / deployment topology. In this example, we will have one centralized APEX instance mapped to multiple database instances. The APEX instance will be provisioned in a public subnet so it can be accessed from the internet. However, the OCI databases will be secured within one or more private subnets.

Step 1: Download Terraform release 0.11.15 (oci) here

Step 2: Depending on your OS, install/configure Terraform on your laptop/PC. For example, if you are running on a mac, download the darwin_amd64 and unzip this in a folder.

Step 3: Copy the "terraform" to your /usr/local/bin directory to install/configure Terraform on your machine

$ unzip terraform_0.11.15-oci_darwin_amd64.zip
$ cp terraform /usr/local/bin

Step 4: Download the APEX terraform template & scripts here. This contains the terraform templates & scripts to install and configure ORDS & APEX on a OCI Compute VM within a public subnet.

Step 5: Create a bucket within your OCI object storage. Download the following and place them within this bucket. The Terraform script will use these to install the appropriate versions of APEX, ORDS & web server.

Hint: You can make this bucket public briefly if you don't want to bother with pre-auth requests etc.. since this will only hold the binaries. Once done, we can change the visibility back to private or blow this bucket up.

a) Download the latest APEX binary here
b) Download the latest ORDS binary here
c) Optionally (if you prefer running APEX on Tomcat) download the latest tomcat zip

Note: Make sure the Apex and ORDS versions are compatible with the version of database you provisioned on OCI.

Step 6: On your OCI tenancy, make sure you have a public subnet within your VCN, attached a internet gateway and a security list. The security list must have the following ingress rules;

0.0.0.0/0           TCP          TF_VAR_COM_PORT (Fetch this based on the port on which you would expose APEX over.)

Step 7: Open the ingress rule on the private subnet (where your database is running) to allow the port (eg., 1521) from the public subnet (where APEX will be deployed)

Step 8: For simplicity, we will be exposing APEX over ip-based access. If you prefer to frontend your apex installation with a DNS, follow the whitepaper reference above. Does a great job offering DNS options.

Step 9: Unzip the ORDS zip file downloaded in Step 4 above.

Step 10: Run terraform --version and make sure you are running on 0.11.15 version. This is important as the terraform template & scripts are written based on this version. You may encounter errors trying to run this AS-IS with later versions of terraform.

Step 11: Gather the following info before proceeding. You will need access to your OCI tenancy to gather lot of details.

a) Generate a pair of SSH public & private keys. These will be used while provisioning the new compute VM that will host APEX & ORDS. Save these for future ssh access to your apex/ords compute VM. Gather the absolute paths for both private & public keys.
b) Generate a API key and fingerprint for your user id (Remember, this is the user that will be used by terraform OCI provider to make API calls into OCI). If you don't know how to generate a API signing key and fingerprint refer to OCI documentation here.
c) OCI Tenancy OCID
d) OCI User ID OCID
e) OCI User Fingerprint
f) OCI Compartment OCID
g) Target Database private IP address
h) Target Database Service Name (If you are running on a multitenant database, make sure you provide the PDB service name and not the root CDB service name).

Hint: Click on DB Connection button on the OCI DB console. It shows the CDB root connection info along with the service name info. Simply replace the CDB domain name with the PDB name. This should look like <<pdb>>.<<subnet>>.<<vcn>>.oraclevcn.com

i) Region: This is the region identifier where you have the database running & eventually APEX. Get your region identifier here.
j) AD: Availability Domain where you would like to install APEX/ORDS. eg., 1, 2 or 3
k) OEL Version: APEX Compute VM will be provisioned on OEL OS. Indicate which version of OEL you would like installed. eg., 7.6
l) Compute Instance Name & Display Name (Choose an appropriate name)
m) Instance Shape: eg., VM.Standard2.2 for a 2 OCPU VM
n) Object Storage URLs for APEX zip, ORDS war file & optionally tomcat zip files
o) Apex/ORDS webserver port number eg., 8080, 8888 etc..
Hint: Remember to open this port on the public subnet security list

Step 12: cd into the ORDS-APEX_Comp directory and run setup.sh. This script will prompt for all the values described above. Once this script is executed, this creates a env-vars file that contains these values for Terraform to use. Alternatively, you can directly edit this file and provide values.

Step 13: Once you make sure all variables are set properly, execute the following;

Hint: You may be prompted for the DB admin password.

terraform init

terraform plan (This should indicate 5 actions that will be performed on your OCI tenancy). This would change depending on whether you choose Tomcat or Jetty as your web server.

+ null_resource.remote-exec_init
      id:                                       <computed>

  + null_resource.remote-exec_tomcat-1
      id:                                       <computed>

  + null_resource.remote-exec_tomcat-2
      id:                                       <computed>

  + null_resource.remote-exec_tomcat-apex
      id:                                       <computed>

  + oci_core_instance.ORDS-Comp-Instance
      id:                                       <computed>

terraform apply

This should run for approximately 15-20 mins. Make sure there are no errors while you run this script. If there are errors, execute "terraform destroy" to rollback all changes. Also if APEX/ORDS is partially deployed on the database (by terraform), clean this up manually. Follow the documentation here.

Some common causes of errors;
1) You may have installed APEX manually on this database before. This will conflict with the terraform APEX install attempt.
2) Terraform script partially executed and failed during APEX install. (Hint: One way to find this out is by observing a bunch of PL/SQL ORA-* errors during terraform execution)

Solution is to clean up APEX manually and ensuring terraform is rolled back using the "destroy" command.

To add / map multiple databases to this APEX/ORDS instance, simply execute the apex_add_db.sh script bundled with the ORDS Terraform scripts.

$ ./apex_add_db.sh -p <database_admin_password> -i <IP_address> -s <database_service_name>

Hope this helps quickly spin up APEX for multiple DB instances.

Transform your on-premise Oracle Investments to Cloud - A Perspective !!

This article is an inspiration from some of the questions that I get asked by customers every day.

1) We have made a lot of on-premise Oracle investments - especially database. How can Oracle help us with our cloud transformation initiatives?
2) How does Oracle DB Cloud Service compare to AWS RDS, Oracle software on Azure? Why should we choose Oracle cloud over competition?
3) What is Oracle's strategy and vision for enterprise customers who have made significant investments over the years on-prem?
4) Other than price-point TCO benefits, what other benefits does Oracle Cloud offer?

In my job role as an enterprise cloud architect, I engage with my customers by bringing in a point-of-view that helps nurture long-term strategy discussions, enrich ideas, propose solution & options to further their cloud/digital transformation endeavors.

In this article, we will analyze a typical customer scenario with various cloud options, inherent PaaS advantages, cost comparisons and non-quantifiable benefits.

Before we delve deep into the details and cost comparisons, I want to state a safe harbor disclaimer that all views (including data points, pricing and options) expressed in this article are my own, based on experience and does not necessarily reflect the views of Oracle. As an Oracle enthusiast and evangelist, this article is purely intended to present a point-of-view, analyze options, value and benefits.

Okay.. Let's take a quick peek at the Oracle database cloud offerings. Built on the basic premise of offering "complete choice", customers have the option to subscribe to the smallest standard DB instance on a VM for development, 2-node RAC cluster DB instance on bare metal for high performance production workloads or opt for the subscription based extreme performance Exadata in the cloud.

Unique to Oracle Cloud, for customers with existing on-premise database licenses, it's an understatement to say the BYOL PaaS pricing model is "attractive". Just for quick comparisons, at published Pay-as-yo-go pricing;

License included DBCS Enterprise Edition (1 OCPU / Hour) is $0.8064
BYOL to Oracle DBCS Enterprise Edition (1 OCPU /Hour) is $0.2903

That is 64% savings right off the bat.

1 OCPU is equivalent of one physical core of Intel Xeon processor with hyper threading enabled - equivalent of AWS' 2 vCPUs and 1 Azure Core.

Let's now look at how this compares to Oracle database on AWS, Azure and GCP. This list is not exhaustive but a selection of a few key considerations for enterprise mission-critical workloads.

AWS and Azure are authorized cloud environments. Google Cloud Platform is not an authorized cloud environment for Oracle Database (predominantly because of how GCP virtualizes their servers).

However, should customers choose AWS or Azure cloud to host Oracle Database? - depends on a few factors;

First and foremost consideration when customers move workloads to cloud: IaaS or PaaS? Database on IaaS only offers "IaaS" benefits like saving datacenter costs. PaaS options like Oracle Database Cloud Service offers higher level of service benefits in the cloud including automated provisioning, elastic scaling, patching, rollback etc..

a) High Availability (HA): For customers with HA needs, this could be a deal breaker as neither Azure nor AWS support RAC (Real Application Clusters). At best AWS RDS offers replication and Multi-AZ deployments but not with zero-downtime.

b) PaaS / Fully Managed: If you are looking for a fully managed, elastic, seamlessly scalable, full-stack patching capabilities, AWS/Azure may not be right fit.

c) License Cost: Although AWS and Azure are authorized cloud environments for running Oracle database, when counting Oracle Processor license requirements, the Oracle Processor Core Factor Table is not applicable. This basically makes it 2x more expensive for customers to run Oracle database on AWS/Azure than on-premise.

d) Provisioned IOPS: Costs can quickly add up if customers choose "provisioned IOPS" SSD for storage. By default, for all workloads Oracle Cloud offers high performance NVMe based SSD storage.

e) Data Security & Encryption: TDE (Transparent Data Encryption) is included and enabled by default in the Oracle Cloud for all Oracle editions and options (including database standard edition). For eg., with AWS customer must buy the "Advanced Security" option.

f) Database Options: Oracle cloud bundles database options into 4 broad offerings. Standard, Enterprise, Enterprise High Performance & Enterprise Extreme Performance. For BYOL customers, even the basic Enterprise Edition comes included with database options such as Diagnostics Pack, Tuning Pack, Real Application Testing, Data Masking & Subsetting Pack. This means, customers with Database EE license can leverage these features in the cloud even if they are not currently licensed on-premise - thus presenting a huge advantage.

g) Backup & Restore: Oracle offers in-place restore for your database backups. This means, you can choose from any of the available backups (automated / point-in-time / most recent) and perform a restore on the same database instance. In contrast, AWS allows restore from backups but creates a "new" database instance - potentially impacting application connectivity, VPC, security group re-configuration.

Now, let's take a typical customer scenario as we walk through various options;

Current Install Base (8 Processor Licenses):

• Oracle Database Enterprise Edition

Licensed Database Options:

• Partitioning
• Real Application Clusters (RAC)
• Active Data Guard
• Advanced Compression
• Database Vault
• Diagnostics Pack
• Tuning Pack
• OLAP
• Advanced Security

Quick note on Oracle on-prem license metrics - 1 Processor license typically has a 0.5 core factor multiplier unless customers have deployed on high horsepower systems such as Intel Itaniums or IBM Ps.

In this scenario, this means customer can deploy Oracle software on 16 cores - which typically is equivalent to 32 vCPUs in a virtualized environment (Assumption: 1 physical core -> 2 threads).

At list price, initial cost of the above configuration would be $1.27 M (including software license acquisition & support). Pragmatically, @ 60% discount, this could be $500 K.

	Year 1	Year 2	Year n
DB EE License	$1.27 M	$0	$0
Support	$358 K	$358 K	$358 K
Total	$1.63 M	$358 K	$358 K
@ 60% Discount	$508 K	$143 K	$143 K

Now, let's pivot this on-premise database to PaaS (Database as a Service)...
Customer has 2 options;

• Subscribe to "license-included" DBCS (PaaS). This would preserve their on-prem licenses which could be re-purposed for other projects still on-prem
• BYOL (Bring Your Own License) option - Convert on-premise database investments to cloud with heavily discounted PaaS subscription costs (Credits applied since customer owns on-prem Oracle database licenses)

For the same configuration, closest option for license-included DBCS is DBCS Extreme Performance (support for RAC & Active Data Guard). Customer is also entitled for other database options like In-Memory, Advanced Analytics etc.. as they are bundled under Extreme Performance edition.

However, with BYOL, customers can bring their DB Enterprise Edition license along with the licensed options and run it on Oracle cloud as PaaS. In this case, customer also gains access to features like Real Application Testing, Data Masking & Subsetting Pack,

This is another unique Oracle cloud feature. For eg., AWS does not offer a "license-included" RDS for Oracle Database Enterprise Edition.

Irrespective of options, subscription cost includes underlying infrastructure (compute, storage & networking), infrastructure support, software (database) licence, software support and automations.

	Year 1	Year 2	Year n
License Included DBCS Extreme Performance	$360 K	$360 K	$360 K
BYOL DBCS EE	$41 K	$41 K	$41 K

Clearly BYOL option is a winner with ~89% savings over license included PaaS.

That's not all. The above is based on published PAYG pricing. Further discounting available on monthly commits.

Of course, no one size fits all !! Customers have a wide range of options to choose their deployment on VMs, Bare Metal or Exadata. Engage your Oracle team for value add services including portfolio analysis, TCO & tailored roadmap.

Please leave your feedback and thoughts.