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.
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.
