OCI-GovCloud Migration: Vanity URL for APEX

Vanity URL is alternative name for custom URL. As a personal aside, I struggle with the term “vanity URL” as if it should not be just URL. It is my URL associated with a domain that I own and that I control. A custom URL is marketing. A custom URL establishes a bit of trust in a trust-limited universe. The feature was introduced during the fall of 2021. There had been intermediate solutions and even during the fall when it was introduced, the capabilities of Oracles custom URL management changed.

If we’re lucky, they will continue to improve. It would be terrific not to have to go to the public market to buy multiple SSL certificates. AWS provides the SSL certs needed for free — as long as they are only installed on their load balancers.

Table of Contents


    We are hosting multiple commercial applications on Oracle’s OCI Govcloud environment. This is their implementation of the U.S. government’s FedRamp. Our key customers are government entities and we have a contract via the GSA. We stepped through the OCI Govcloud environment with very little understanding of how it is different and what restrictions it places on us.

    • Host and support multiple applications written in Oracle APEX via simple custom URLs
    • Encrypt traffic between user’s browser and the edge of our virtual cloud network (“encrypted in transit”)
    • Display the familiar lock icon in a browser’s URL region and have a trusted, validated certificate authority behind that cert.
    • Host applications from a database that does not have a public IP address nor a public endpoint URL

    We will proxy all traffic to the database through OCI’s edge infrastructure.

    Supporting Documentation

    Todd Bottger wrote a series of articles on this topic “Introducing Vanity URLS for APEX and ORDS on Oracle Autonomous Database” (Sept 2021). Do go study these. Read all four because on the last article he has you remove what you did in the prior articles and he admits that the capabilities changed as he wrote and published the articles.

    Begin with the End in Mind

    Develop a plan for the URLs and their needs. The more you plan the faster, easier, and cheaper this process goes. We have two commercial applications written in Oracle APEX. For each application, we have a customer service application and likely a training link. Sometimes we provide clients a link to our development space so that we can collaborate on new features. After 10 years of supporting these 2 applications, we were struck by the number of URL that we use.

    Then when we recognized that we had to pay for each SSL certificate, we needed to make sure that our actions make good sense. Depending upon your vendor, SSL certificates run between $300 and $800 per year per domain.

    Our plan:

    example1.comApplication #1
    cs.example1.comCustomer Service App for Application #1
    dev.example1.comLogin access to the Development version of the application
    apex.mycompany.comAPEX login for developers
    sql.mycompany.comSQL Developer access for developers
    example2.comApplication #2
    cs.example2.comCustomer Service App for application #2
    train.example2.comLogin access for the training site
    Custom URL Plan

    For each of these, we also have email addresses. We send users email as required by the applications. For each of these we have email groups with names like “notify@” and “support@” linked to their appropriate domain.

    In the example above, we need a minimum of 3 SSL certificates. Each certificate needs to be a wildcard. We then shopped for SSL certificates. We need the following:

    1. *.example1.com
    2. *.example2.com
    3. *.mycompany.com

    1. Buying SSL Certificates

    Don’t buy Multi-domain Wildcard Certificates

    During the 2 days I worked through these challenges, I bought and returned domains 3 times prior to getting it right. There is something called a multi-domain wildcard certificate. This uses Subject Alternative Name or SAN. While Oracle does not states it supports or does not support multi-domain wildcard SSL certificates, we did open a ticket to learn that the “common name” (CN) within the certificate must be the wildcard name as in *.example1.com

    Some vendors require the multi-domain wildcard certificate to be purchased with a fully-qualified domain name (FQDN, because we do need an acronym) as the Common Name. Therefore the purchase had to be linked with cs.example1.com instead of *.example1.com. Let’s admit that it appeared that Oracle’s load balancer and certificate store did not display the SAN within our certificate.

    Multi-domain Wildcard Certificates are cheaper than buying 3 single-domain SSL wildcard certificates.

    Do Buy Single-domain SSL wildcard certificates

    And when doing so, make sure that the Common Name (CN) is: *.example1.com. We started with 1 domain. We will add as we need to. There is wisdom in buying all you need on 1 day that way these things renew on the same date.

    Just to avoid the mistakes that I made, save everything including your:

    • Certificate Signing Request
    • Private Key
    • Public Key

    I also saved the CSR text because well: mistakes. I make them!

    2. OCI Create Certificate

    The button and service is called “create certificate” and you can create but basically, the feature is designed to Import a certificate.

    Our purchase yielded a zip file that had several folders:

    • CER-CRT Files
    • PKCS7 Files
    • Plain Text Files

    For Oracle OCI, stick to CER-CRT. You will need the private key from the CSR generation process and you’ll need your new wildcard cert which our vendor called star_example1_com.crt. The certificate chain is was in file with the word “bundle”. I’ve got no idea what your vendor will do.

    Follow the instructions through the rest.

    3. Autonomous Database (ADB)

    Creating an ADB was literally the first thing we did when we opened our GovCloud account. It felt mildly familiar. When you build a database, you do want direct access to it. What’s the point otherwise. But, the goal is to have an ADB that does not have direct access.

    Update Network Access

    You can switch your ADB between privately accessed only and available publicly. Do not rebuild your database (which I did, of course).

    If you foul up, toggle back.

    Capture your “Private Endpoint IP” address into your favorite text editor.

    4. Create Load Balancer

    To build and use a Load Balancer you will need the following:

    • Virtual Cloud Network + Subnet
    • Security Rules (make them easy and wide open for now)
    • Wildcard SSL Certificate purchased
    • SSL Certificate uploaded to OCI Certificates
    • Private/internal IP address for your Autonomous Database (ADB)

    After building the load balancer, you will have the following

    1. A load balancer
    2. With a Backend Set
    3. 1 or more listeners
    4. 1 or more Rule Sets

    Advantage to OCI Load Balancer versus AWS Load Balancer

    The OCI Load Balancer has rules that proxy and reverse proxy traffic through your Virtual Cloud Network. This means that you do not need a proxy server such as Apache (or others). While debatable if a cost savings due to the need to buy certificates on the open market, it does make support easier and therefore may reduce support costs.

    4.1 Click Create Load Balancer

    Select the external or public type of Load Balancer. Give it a nice name or at least a short name. You will have to type it and know what it is 10 years from now. Be nice to yourself.

    Select your networking assignments. For us, we had 1 Virtual Cloud Network and one Subnet. We also have 1 Network Security Group. Presently called “all from anywhere”. We will modify that to permit only certain ports through our network, for now, why add complexity. Get it working, then tighten the security.

    Select “Weighted Round Robin” and skip “add backends”. The next bits of entry are odd:

    Protocol: HTTP (not HTTPS)

    Port: 443 (Not 80)

    Status Code: 302 (not 200)

    Hey it works. Go with it.

    Click Hide Advanced Options

    Enter in a Backend Set Name (regrettably called a BS).

    Configure your listener

    Listener Name: Make it nice and readable. We used “APEX01_ADB”. Cleverly we named our ADB “APEX01”.

    Traffic: HTTPS

    Port for ingress traffic: 443

    SSL Cert: Select from list

    We did not do logging. We likely will later on when in production.

    Submit and build your load Balancer.

    4.2 Create DNS Entries

    Given we are migrating from AWS, we are using AWS Route 53 for DNS management. Maybe we’ll move that too some day. For now, every custom URL you create needs to have a DNS entry.

    Create a A record for the load balancer

    lb.example1.com points to 257.1.453.342

    Create a CNAME for each custom URL

    apex.example1 points to lb.example1.com

    4.3 Edit Backend Set

    I don’t know who named these things “backends” or decided to abbreviate them “BS”. Hard phrases to use in an office space. As the kids say: “Not Safe For Work” (NSFW). A Backend Set includes 1 or more Backends.

    Edit the backend set, and either add or edit the Backend.

    • IP Address: Your ADB internal IP address
    • Port: 443
    • Weight: 1

    The BS is done (sorry, they set me up for it).

    4.4 Create Hostnames

    Create hostnames for each of your subdomains or URL that you plan on using for your applications and APEX, SQL developer acess.

    4.5 Create a Rule Set

    You will create a Rule Set for each subdomain, domain, or custom URL (“vanity URL” but vanity does go out the window when you are sporting multiple backends and creating BS).

    When we (my colleague Eli Duvall and I) created these Rules Sets, we used our existing Apache configuration and our name plan from the top of the article. Each custom URL gets it own rule.

    For direct access to the OCI Landing Page this is a fair approach:

    • Specify URL Rules – checked
    • Source Path: /ords/
    • Match Type: Suffix Match
    • Redirect to Protocol: HTTPS
    • Host: apex01.example1.com
    • Port: {port}
    • Path: /ords/_/landing
    • Query: ?{query}
    • Response Code: 302 – Found
    • Specify request header rules:
      • Action: Add request header
      • Header: apex01
      • Value: 1

    When connecting directly to an application, the Path will be different.

    • Specify URL Rules – checked
    • Source Path: /ords/
    • Match Type: Suffix Match
    • Redirect to Protocol: HTTPS
    • Host: apex01.example1.com
    • Port: {port}
    • Path: /ords/r/backyard/bid-tracker
    • Query: ?{query}
    • Response Code: 302 – Found
    • Specify request header rules:
      • Action: Add request header
      • header: BidTracker
      • Value: 1

    4.6 Create Listeners

    We found it easier and more successful to create 1 listener per URL needed. Maybe we did it right too? It does work.

    • Protocol: HTTPS
    • Port: 443
    • Certificate Resource: select your certificate
    • Hostname: bid (select your hostname)
    • Backend Set Name: apex01_adb (select)
    • Timeout: 60

    Wait for Listener to be created or updated. Then return to select the Rule Set. Super easy to forget and will cause failure.

    Select the rule set that corresponds to your URL/application

    5 Test and Play

    We found a lot of ways to go off the rails, enjoy the frustrations associated with failures. When I look at the Work Requests associated with our 1 load balancer, I see that we had 57 Work Requests.

    Know what else I see? Success!

    I am very happy to see that my Backend is healthy and that my BS is OK.

    How to test: paste your domains to the URL bar in a browser.

    6 Oops

    As you play and explore, look at the URL information in your browser. It is the #1 troubleshooting trick I can offer. Because the load balancer does a proxy and a reverse proxy, when you make a mistake you may see you aimed at 1 target but the URL has a different address than you planned. It is all about balancing the prefix and suffix in your URL plan.

    Related Articles & our Checklist for Success

    The checklist for success includes:

    Migration Plan

    1 – Multi-domain custom URL that accurately permit users to log into the correct applications and allow developers the access that they need for database tools including APEX

    2 – Migrate a database pump file from AWS to OCI

    3 – Install a database pump file on OCI into specific schemas

    4 – Have functioning applications (kind of obvious, huh?)

    5- Be able to send email from Oracle APEX to users

    I’ll write and publish an article for each step including tricks, failures, and successes.

    Posted by Christina Moore

    First database application during undergrad years: 1985. First full-time work in Oracle db: 1995 at FedEx in the land of international shipping and regulatory compliance. First APEX application: 2008 with a U.S. based wholesale grocery distribution outfit (logistics, finance, and regulatory compliance). First commercial APEX application: 2012 (time tracking/invoices, grant management/finances/regulatory compliance. There seems to be a trend with finance, regulatory compliance, logistics.