Oracle Cheatsheet: 2021

Friday, November 19, 2021

Service Unavailable after upgraded to APEX 21.2

I was freak out when I saw this right after APEX upgrade. I've been doing APEX upgrade since APEX 4 and this is the first time I encounter this error.

Reviewing the upgrade log and I found this foreign Key error

Error starting at line : 53 File @ C:\Users\chiup\Downloads\apex_21.2\apex\coreins5.sql
In command -
begin
    --
    -- We need to directly delete dependencies (to XDB), because the
    -- namespace switch from SERVER to DBTOOLS would result in a FK error.
    -- There is no suitable API in dbms_registry.
    --
    delete from sys.registry$dependencies d
        where cid = 'APEX';
    --
    -- Update registry
    --
    wwv_install_api.upgrade_registry;
end;
Error report -
ORA-02292: integrity constraint (SYS.REGISTRY_PROGRESS_FK) violated - child record found
ORA-06512: at "SYS.DBMS_REGISTRY", line 299
ORA-06512: at "APEX_210200.WWV_INSTALL_API", line 603
ORA-06512: at line 12
02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
*Cause:    attempted to delete a parent key value that had a foreign
           dependency.
*Action:   delete dependencies first then parent or disable constraint.

Checking the SYS.REGISTRY$PROGRESS table, I found an orphan record way back from APEX 5 days.

Delete the orphan record, drop APEX_210200 schema, re-run the upgrade process and I am back in business.

Saturday, October 30, 2021

Run Oracle Database Express Edition using Podman on an Oracle Cloud compute instance

In this article, I will demonstrate how to run Oracle Database Express Edition using Podman on an Oracle Cloud compute instance.

On Oracle Cloud Console

create instance, name instance-21c-xe
change image to Oracle Linux 8.0
New VCN, New Public Subnet, Assign Public IPv4 address

It is not necessary to use a paid instance. The free instance will work as well.

Wait for the instance to be ready. Go to Security List, add Ingress Rule for port 1521

ssh into the new instance

sudo bash
ln -sf /usr/share/zoneinfo/Canada/Eastern /etc/localtime
dnf -y install podman podman-docker buildah skopeo dnf-utils zip unzip tar gzip git sqlcl
dnf -y update
podman pull docker.io/gvenzl/oracle-xe:21-full
podman run -d -p 1521:1521 -e ORACLE_PASSWORD=SysPassword1 --name 21cFull -v oracle-volume:/opt/oracle/XE21CFULL/oradata gvenzl/oracle-xe:21-full
podman ps

Give it 2 minutes to startup

podman logs 21cFull

You should find this in the output
#########################
DATABASE IS READY TO USE!
#########################

Testing via SQLcl

sql system/SysPassword1@//localhost/XEPDB1

Setup archivelog mode via opening a session into the container

podman exec -it --user=oracle 21cFull bash
sqlplus / as sysdba
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

Setup autostart on reboot

podman generate systemd --name 21cFull > /lib/systemd/system/container-21cFull.service
systemctl enable container-21cFull

All set. Reboot, wait for a few minutes and check again

Wednesday, August 25, 2021

Using Google Fonts in Oracle APEX

1. Pick the font you want from Google Fonts. In this example, I picked Roboto, and you should add all the styles to the font family.

2. Add this CSS via Theme Roller->Custom CSS

@import url('https://fonts.googleapis.com/css2?family=Roboto:ital,wght@0,100;0,300;0,400;0,500;0,700;0,900;1,100;1,300;1,400;1,500;1,700;1,900&display=swap');
:root {
  --a-base-font-family: 'Roboto', sans-serif;
}

Sunday, August 8, 2021

Add Prism Syntax Hightlighter to Blogger

1. Goto Usage with CDNs

2. Go to your blogger, Template->Edit HTML and paste the followings right before the </head> tag. We have also included Line Numbers and Copy to Clipboard Button plugin.

<head>
<link crossorigin='anonymous' href='https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/themes/prism.min.css' integrity='sha512-tN7Ec6zAFaVSG3TpNAKtk4DOHNpSwKHxxrsiw4GHKESGPs5njn/0sMCUMl2svV4wo4BK/rCP7juYz+zx+l6oeQ==' referrerpolicy='no-referrer' rel='stylesheet'/>
<link crossorigin='anonymous' href='https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/plugins/line-numbers/prism-line-numbers.min.css' integrity='sha512-cbQXwDFK7lj2Fqfkuxbo5iD1dSbLlJGXGpfTDqbggqjHJeyzx88I3rfwjS38WJag/ihH7lzuGlGHpDBymLirZQ==' referrerpolicy='no-referrer' rel='stylesheet'/>
<link crossorigin='anonymous' href='https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/plugins/toolbar/prism-toolbar.min.css' integrity='sha512-ycl7dIZ0VJ5535/dzskAMTwOI6OoTNZ3PeD+tfckvYqMmAzaEwQfJHqJTSqcV2iQeJnp5XxnFy5jKotibstp7A==' referrerpolicy='no-referrer' rel='stylesheet'/>
<style>
  pre {
    font-size:smaller;
   }
</style>
</head>
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/components/prism-core.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/plugins/line-numbers/prism-line-numbers.min.js" integrity="sha512-dubtf8xMHSQlExGRQ5R7toxHLgSDZ0K7AunqPWHXmJQ8XyVIG19S1T95gBxlAeGOK02P4Da2RTnQz0Za0H0ebQ==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/plugins/toolbar/prism-toolbar.min.js" integrity="sha512-YrvgEHAi5/3o2OT+/vh1z19oJXk/Kk0qdVKbjEFl9VRmcLTaWRYzVziZCvoGpJ2TrnV7rB8pnJcz1ioVJjgw2A==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/plugins/copy-to-clipboard/prism-copy-to-clipboard.min.js" integrity="sha512-pUNGXbOrc+Y3dm5z2ZN7JYQ/2Tq0jppMDOUsN4sQHVJ9AUQpaeERCUfYYBAnaRB9r8d4gtPKMWICNhm3tRr4Fg==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/plugins/autoloader/prism-autoloader.min.js"></script>

3. In your edit post screen, hit HTML and put this surrounding the code you want to hightlight.

<pre class="line-numbers"><code class="language-sql">
Your SQL Statement here
</code></pre>

Thursday, August 5, 2021

NGINX SSL Reverse Proxy for Tomcat/ORDS/APEX

This is a simple reverse proxy to achieve any URL structure you want for your APEX server. NGINX is responsible for handle almost everything, reverse proxy, URL redirection, HTTP/2, cache, gzip, HSTS, OCSP stapling, etc. Tomcat/ORDS/APEX is sitting behind NGINX, communicating with NGINX via HTTP.

This is the stack I am running at the moment.

NGINX 1.21.1
Tomcat 9.0.50
ORDS 21.2.0.r1741826
APEX 21.1.2

C:\Program Files\nginx\conf\nginx.conf

worker_processes auto;
pid        logs/nginx.pid;

events {
    worker_connections  1024;
}

http {
    include       mime.types;
    default_type  application/octet-stream;
    sendfile      on;
    keepalive_timeout 75;
	client_max_body_size 200M;
	proxy_cache_path "C:/Program Files/nginx/temp/proxy_cache" levels=1:2 keys_zone=STATIC:10m inactive=24h  max_size=1g use_temp_path=off;

server {
    listen 80 default_server;
    server_name _;
	
    rewrite ^ https://$host$request_uri? permanent;
}

server {
    listen 443 ssl http2 default_server;
    server_name _;
 
	gzip on;
	gzip_types	text/css text/plain text/javascript	application/javascript application/json application/x-javascript application/xml application/xml+rss application/xhtml+xml application/x-font-ttf application/x-font-opentype application/vnd.ms-fontobject image/svg+xml image/x-icon application/rss+xml application/atom_xml;
    gzip_proxied    no-cache no-store private expired auth;
    gzip_min_length 1000;
	
	ssl_certificate "E:/ssl/fullchain.cer";
	ssl_certificate_key "E:/ssl/leavemealone.com.key";
	ssl_protocols TLSv1.2 TLSv1.3;
	ssl_ciphers "EECDH+ECDSA+AESGCM EECDH+aRSA+AESGCM EECDH+ECDSA+SHA384 EECDH+ECDSA+SHA256 EECDH+aRSA+SHA384 EECDH+aRSA+SHA256 EECDH+aRSA+RC4 EECDH EDH+aRSA HIGH !RC4 !aNULL !eNULL !LOW !3DES !MD5 !EXP !PSK !SRP !DSS";
	ssl_ecdh_curve secp384r1; 
	ssl_prefer_server_ciphers on;	
	ssl_dhparam "E:/ssl/dhparam4096.pem";
	
	ssl_session_cache   shared:SSL:10m;
	ssl_session_timeout 10m;
	ssl_session_tickets off;
	
	ssl_stapling on;
	ssl_stapling_verify on;
	ssl_trusted_certificate "E:/ssl/ca.cer";

	add_header          Strict-Transport-Security "max-age=31536000; includeSubDomains" always;
#	add_header          X-Content-Type-Options nosniff;
#   add_header          X-Frame-Options SAMEORIGIN;
#   add_header          X-XSS-Protection "1; mode=block";

    location / {
        proxy_connect_timeout       600;
        proxy_send_timeout          600;
        proxy_read_timeout          600;
        send_timeout                600;

		proxy_set_header Host $host;
		proxy_set_header X-Forwarded-Host $host:$server_port;
		proxy_set_header X-Real-IP $remote_addr;
		proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
		proxy_set_header X-Forwarded-Proto $scheme;
		proxy_set_header Origin "";
        proxy_pass http://127.0.0.1:8080;
		proxy_http_version 1.1;
    }
	
	# cache apex application/workspace static files
	location ~* /ords/(.*)/r/([0-9/]*)files/static/v([0-9]+)/ {
		proxy_set_header Host $host;
		proxy_set_header X-Forwarded-Host $host:$server_port;
		proxy_set_header X-Real-IP $remote_addr;
		proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
		proxy_set_header X-Forwarded-Proto $scheme;
		proxy_set_header Origin "";
        proxy_pass http://127.0.0.1:8080;
		proxy_http_version 1.1;
		
		proxy_redirect off;
		add_header X-Cache-Status $upstream_cache_status;
		expires 300d;
		proxy_cache STATIC;
		proxy_cache_key $host$uri$is_args$args;
		proxy_cache_valid 200 24h;
		proxy_cache_use_stale  error timeout invalid_header updating http_500 http_502 http_503 http_504;
	}
  }  	
}

Let me explain this line by line:

Line 14, proxy cache setting
Line 17-20, rediect all HTTP traffic to HTTPS
Line 24, setup HTTP/2
Line 27-30, enable gzip
Line 32-33, SSL key pair. fullchain.cer contains the server public certificate followed by immediate certificate in the same file
Line 34-38, SSL protocol, chipers setting
Line 40-42, SSL session cache setting
Line 44-46, OCSP stapling setting. ca.cer contains only the immediate certificate
Line 48, add HSTS header
Line 54-57, proxy timeout setting
Line 59-63, pass some extra headers to ORDS, so that your app can now where this request originally comes from
Line 64, Google Chrome enforces stricter CORS rules, than e.g. Firefox. By setting the Origin to blank we can make reverse proxying work, otherwise Chrome would block it
Line 65, the actual reverse proxy command saying that traffic is internally rerouted to http://127.0.0.1:8080
Line 80-86, proxy cache setting. We put every file found on a path like /ords/*/r/*files/static/vnnn/ subfolder for at least 24hrs and also send a 300 day expiry header to the client

There is not a lot of changes in Tomcat. Basically we need to ensure HTTP (port 8080) is working, limit access to localhost and adding the actual IP address %{X-Forwarded-For} to tomcat log file.

E:\tomcat9\conf\server.xml

<Connector port="8080" protocol="HTTP/1.1"
		scheme="https"
        proxyPort="443"
		maxHttpHeaderSize="32767"
		maxPostSize="-1"
		disableUploadTimeout="true" />
        
   <Engine name="Catalina" defaultHost="localhost">
      <Host name="localhost"  appBase="webapps"
            unpackWARs="true" autoDeploy="true">
            
...

		<Valve className="org.apache.catalina.valves.RemoteAddrValve"
			allow="127\.\d+\.\d+\.\d+|::1|0:0:0:0:0:0:0:1"/>

		<Valve className="org.apache.catalina.valves.RemoteIpValve"
				internalProxies="127\.0\.[0-1]\.1"
				remoteIpHeader="X-Forwarded-For"
				requestAttributesEnabled="true"
				protocolHeader="x-forwarded-proto"
				protocolHeaderHttpsValue="https"/>
		
        <Valve className="org.apache.catalina.valves.AccessLogValve" directory="logs"
               prefix="localhost_access_log" suffix=".txt"
               pattern="%{X-Forwarded-For}i %h %l %u %t &quot;%r&quot; %s %b" />
      </Host>
   </Engine>

Line 2-3, in some situations APEX internally creates a redirect to a different URL path, e.g. during Authentication using Social-Login it will redirect to …/ords/apex_authentication.callback… With the verison of the stack I am using, these two lines might not be required anymore. I am still leaving them here for the peace of mind.
Line 14-15, to allow access only for the clients connecting from localhost
Line 17-26, adding the actual IP address %{X-Forwarded-For} to tomcat log file

Friday, July 30, 2021

Oracle SQL Explain Plan

Display the execution plan stored in the plan table. We are not executing the actual SQL.

EXPLAIN PLAN
    FOR SELECT /*+ gather_plan_statistics */
               COUNT (*) FROM dba_objects;

SELECT * FROM TABLE (DBMS_XPLAN.display (format => 'TYPICAL'));

Displays the actual execution plan used to run a query stored in the cursor cache. We have to execute the SQL first.

SELECT /*+ gather_plan_statistics */
       COUNT (*) FROM dba_objects;

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (format => 'ALLSTATS LAST +cost +bytes +peeked_binds'));

Sunday, July 4, 2021

Upgrade ORDS in OCI

Update ORDS

yum -y update

Fix ords.conf

yum update will mess up the ORDS config file. We need to remove the extra ORDS_BASE_PATH.

vi /etc/ords/ords.conf

# ORDS Service Script Configuration File (ords.config)
#
# This file should be placed in the same folder as the ORDS Service Script
# (ords.sh). Following are the properties that can be set via this file:
#
# ORDS_CONFIGDIR  The absolute path to an ORDS configuration directory. If set
#                 and not empty then the script will overwrite the configdir
#                 property in the ords.war file located next to the script
#                 whenever the script is called with the start parameter.
# JAVA_HOME       The Java base path to be used when starting ORDS. It should
#                 contain the binaries folder "bin" and the "java" executable
#                 under it. If the path provided does not exist, is not
#                 readable or doesn't contain the "bin/java" executable, then
#                 the PATH's java binary will be used to determine the
#                 JAVA_HOME.
# JAVA_OPTIONS    A string containing the Java options to be passed to the JVM
#                 when starting ORDS. Parameters should be in a single line
#                 separates by spaces just as they'd be when invoking a JVM
#                 from the command line. No shell expansions are performed. No
#                 options are passed by default to the ORDS' JVM.
# ORDS_BASE_PATH  The base path for ORDS installation by default is /opt/oracle
#                 but if the rpm was installed with --prefix [path] to relocate
#                 the package this variable will track the installation path.

#####Example#####
#ORDS_CONFIGDIR=/opt/oracle/ords/conf
#JAVA_HOME=/usr/java/jre1.8.0_211-amd64/
#JAVA_OPTIONS=-Dsecurity.forceHTTPS=true
ORDS_BASE_PATH=/opt/oracle
ORDS_BASE_PATH=/opt/oracle

Run ORDS and setup the config directory

ords standalone and enter /opt/oracle/ords/config

Monday, April 5, 2021

Enable MFA for Oracle Cloud Infrastructure Sign In

Navigate to Service User Console

Enable MFA

Enable MFA in Sign-On Polices

At this point, the setup is completed. We have enabled MFA for all logins. Next time, when users login, they will be asked to setup QR code. You could your faviourite authenticiator app, For example Google Authenticator, Microsoft Authenticator or 1Password.

Sunday, March 7, 2021

Bash script for Instance Pool Lifecycle Management

This script will create a new image, a new instance configuration based on the new image and update the current instance pool with the new instance configuration.

After that, we can run recycle_instances.sh to recycle all the instances. See my previous post for details.

instance-config-ords-as-template.json

{
    "instanceType": "compute",
    "launchDetails": {
      "compartmentId": "{{compartmentId}}",
      "createVnicDetails": {
        "assignPublicIp": false
      },
      "metadata": {
        "ssh_authorized_keys": "ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDuPoZBbXjC/5ojt+ECoIj5KGmLHWPPreWcjkp/1metOBgRv8f6W7w615+kRcrrdtyB5Tk6MzIs6CmF8RZ1BkkSankhHG62aKkqXn7T9VDenvEHaJpJqQRkhkYzJKyYqL+04O942gSgv8Kpw1IpFWvznfelf30xaxQzcLa0tMjYvOmqTmeAndEM3E1ZVMcEq3r3OlTTjCyfBPsdRPV2hFClvQziueRrUF61lhLotPUkCKxc6Iie+OpqW5hhU8vypFT0MAB6hoTH7EO7BGmQWInQlO3Pt4m7q9dNSee731TzRceDFa5cC/uigeAFgjEY8lwM5CFrcgMW3n3B3BpfumQv PC@PCLAPPY"
      },
        "shape": "VM.Standard.E3.Flex",
        "shapeConfig": {
          "memoryInGBs": 16.0,
          "ocpus": 1.0
        },
        "sourceDetails":   {
          "bootVolumeSizeInGBs": null,
          "imageId": "{{imageId}}",
          "sourceType": "image"
        }
    }
}

update_instance_pool_image_part2_template.txt

#!/usr/bin/bash

COMPARTMENT_OCID="{{compartmentId}}"
INSTANCE_POOL_OCID="{{instancepoolId}}"
INSTANCE_CONFIG_JSON="{{INSTANCE_CONFIG_JSON}}"
INSTANCE_CONFIG_NAME="{{INSTANCE_CONFIG_NAME}}"

INSTANCE_CONFIG_OCID=`oci compute-management instance-configuration create --compartment-id $COMPARTMENT_OCID --instance-details file://$INSTANCE_CONFIG_JSON --display-name $INSTANCE_CONFIG_NAME | jq -r '.data.id'`
sleep 30
oci compute-management instance-pool update --wait-for-state RUNNING --instance-pool-id $INSTANCE_POOL_OCID --instance-configuration-id $INSTANCE_CONFIG_OCID

update_instance_pool_image_part1.sh

#!/usr/bin/bash

COMPARTMENT_OCID="ocid1.compartment.oc1..aaaaaaaarocn3npultgruh5iwghhvor6s3kairokq4mil5bp52va6qkk7x6a"
INSTANCE_POOL_OCID="ocid1.instancepool.oc1.ca-toronto-1.aaaaaaaaswx6wld7z77u32shwivedvgn5usofurtjzfd3kdnfopbi56wqlfa"
INSTANCE_OCID="ocid1.instance.oc1.ca-toronto-1.an2g6ljrmpjzp2icj3zh7m5ndqp365ahhxv5j2b2u4t7omixuugdqwojymsq"

DATETIME=`date +"%Y-%m%d-%H%M"`
IMAGE_NAME="ords-as_"$DATETIME
INSTANCE_CONFIG_JSON="instance-config-ords-as_"$DATETIME".json"
INSTANCE_CONFIG_NAME="instance-config-ords-as_"$DATETIME
UPDATE_INSTANCE_POOL_SCRIPT="update_instance_pool_image_part2.sh"

sed "s/{{INSTANCE_CONFIG_JSON}}/$INSTANCE_CONFIG_JSON/g;s/{{INSTANCE_CONFIG_NAME}}/$INSTANCE_CONFIG_NAME/g;s/{{compartmentId}}/$COMPARTMENT_OCID/g;s/{{instancepoolId}}/$INSTANCE_POOL_OCID/g" update_instance_pool_image_part2_template.txt > $UPDATE_INSTANCE_POOL_SCRIPT
chmod 755 $UPDATE_INSTANCE_POOL_SCRIPT

IMAGE_OCID=`oci compute image create --compartment-id $COMPARTMENT_OCID --instance-id $INSTANCE_OCID --display-name $IMAGE_NAME | jq -r '.data.id'`

sed "s/{{imageId}}/$IMAGE_OCID/g;s/{{compartmentId}}/$COMPARTMENT_OCID/g" instance-config-ords-as-template.json > $INSTANCE_CONFIG_JSON

echo "Custom Image $IMAGE_NAME created. This instance will be taken offline for several minutes during the imaging process. Run update_pool_instance_image_part2.sh when instance come back online."

update_instance_pool_image_part2.sh

#!/usr/bin/bash

COMPARTMENT_OCID="ocid1.compartment.oc1..aaaaaaaarocn3npultgruh5iwghhvor6s3kairokq4mil5bp52va6qkk7x6a"
INSTANCE_POOL_OCID="ocid1.instancepool.oc1.ca-toronto-1.aaaaaaaaswx6wld7z77u32shwivedvgn5usofurtjzfd3kdnfopbi56wqlfa"
INSTANCE_CONFIG_JSON="instance-config-ords-as_2021-0308-0731.json"
INSTANCE_CONFIG_NAME="instance-config-ords-as_2021-0308-0731"

INSTANCE_CONFIG_OCID=`oci compute-management instance-configuration create  --compartment-id $COMPARTMENT_OCID --instance-details file://$INSTANCE_CONFIG_JSON --display-name $INSTANCE_CONFIG_NAME | jq -r '.data.id'`
sleep 30
oci compute-management instance-pool update --wait-for-state RUNNING --instance-pool-id $INSTANCE_POOL_OCID --instance-configuration-id $INSTANCE_CONFIG_OCID

Tuesday, March 2, 2021

Let's Encrypt Automatic Certificate Renewal in OCI Load Balancer

First we need to setup an acme instance to run the SSL certificate renewal against Let's encrypt nightly. To do that, the acme instance must be behind the load balancer with the SSL certificate associated with it. On top of that, we only want Let's Encrypt traffic reaching this instance. To achive this, we use Path Route Sets.

Add Backend Set

Add Backends

Add Path Route Sets

Assign Path Route Sets to the Load Balancer SSL listener

At this point, only the Let's Encrypt traffic will be routed to the acme instance. Normal traffic will not be affected.

acme.sh is probably the easiest & smartest shell script to automatically issue & renew the free certificates from Let's Encrypt. On the acme instance, we will do the followings

Install acme.sh

curl https://get.acme.sh | sh -s email=pchiu@leavemealone.com

Issue SSL Certificate

~/.acme.sh/acme.sh --issue -d pws.leavemealone.com -w /opt/oracle/ords/config/ords/standalone/doc_root

Install SSL Certificate

~/.acme.sh/acme.sh --install-cert -d pws.leavemealone.com --key-file /opt/oracle/ords/config/ords/standalone/leavemealone.com.key --cert-file /opt/oracle/ords/config/ords/standalone/leavemealone.com.pem --ca-file /opt/oracle/ords/config/ords/standalone/ca.cer --reloadcmd "/home/oracle/renew_lb_certs.sh"

Create and Install pkcs8 private key

~/.acme.sh/acme.sh --renew -d pws.leavemealone.com --to-pkcs8
cp ~/.acme.sh/pws.leavemealone.com/pws.leavemealone.com.pkcs8 /opt/oracle/ords/config/ords/standalone/
chmod 600 /opt/oracle/ords/config/ords/standalone/pws.leavemealone.com.pkcs8

Set notifications

export MAIL_TO="pchiu@leavemealone.com"
export MAIL_FROM="no-reply@leavemealone.com"
acme.sh --set-notify --notify-hook mail

At this point, we have setup acme.sh to renewal the cetificate automatically for us and we will put the new certifcate in /opt/oracle/ords/config/ords/standalone

The following bash script will take the new certificate in /opt/oracle/ords/config/ords/standalone and add it to the load balancer and update the listener to use it.

renew_lb_certs.sh

#!/usr/bin/bash

LB_OCID="ocid1.loadbalancer.oc1.ca-toronto-1.aaaaaaaaxgcpd4izvacefithsabg3l4dhvl7y6gt2mcgitdlwt3ez45vzp5q"
LISTENER_NAME="listener_lb_323"
BACKEND_SET_NAME="bs_lb_2021-0122-2326"
CERT_PATH="/opt/oracle/ords/config/ords/standalone"

CERT_NAME="pws.leavemealone.com_"`date +"%Y-%m%d-%H%M"`

~/.acme.sh/acme.sh --renew -d pws.leavemealone.com --to-pkcs8
cp ~/.acme.sh/pws.leavemealone.com/pws.leavemealone.com.pkcs8 /opt/oracle/ords/config/ords/standalone/
chmod 600 /opt/oracle/ords/config/ords/standalone/pws.leavemealone.com.pkcs8

oci lb certificate create --load-balancer-id $LB_OCID --wait-for-state SUCCEEDED --certificate-name $CERT_NAME --ca-certificate-file $CERT_PATH/ca.cer --private-key-file $CERT_PATH/pws.leavemealone.com.key --public-certificate-file $CERT_PATH/pws.leavemealone.com.pem
oci lb listener update --force --wait-for-state SUCCEEDED --listener-name $LISTENER_NAME --default-backend-set-name $BACKEND_SET_NAME --port 443 --protocol HTTP --load-balancer-id $LB_OCID --ssl-certificate-name $CERT_NAME --hostname-names \[\"pws.leavemealone.com\"\] --routing-policy-name acme --rule-set-names \[\"ADD_HSTS\"\] --cipher-suite-name oci-default-http2-ssl-cipher-suite-v1

#oci lb certificate list --all --load-balancer-id $LB_OCID

We still need to manually update the certificate between load balancer and instances in private subnet

Update Certificate in Backend Set

Sunday, February 21, 2021

Secure OCI Load Balancer Setup

Encryption of data in Transit

Assumption: Load Balancer is listening on Port 80,443 on the public side and ORDS standalone is listening on Port 8080,8443 on the private side.

End to End SSL

Use SSL to communicate with ORDS standalone

Ensure Health Check is using Port 8443

Add HSTS response header

Rule Set for adding HSTS response header

Listener on Port 443 with the rule set

Setup another listener on Port 80, rediect traffic to 443

Rule Set for Redirection

Listener on Port 80 with the rule set

Run SSL Labs Test

Saturday, February 20, 2021

Bash script to recycle all running instances in an instance pool

We have an instance pool, auto scaling with minimum 2 instances. After we update the instance configuration for the instance pool, we need to bring all running instances up to date.

The process is terminating one instance at a time, wait till the load balancer get back to "OK" and repeat. Eventually, we will terminated all instances with the old instance configuration with zero downtime.

recycle_instances.sh

#!/usr/bin/bash

COMPARTMENT_OCID="ocid1.compartment.oc1..aaaaaaaarocn3npultgruh5iwghhvor6s3kairokq4mil5bp52va6qkk7x6a"
LOAD_BALANCER_OCID="ocid1.loadbalancer.oc1.ca-toronto-1.aaaaaaaaxgcpd4izvacefithsabg3l4dhvl7y6gt2mcgitdlwt3ez45vzp5q"
INSTANCE_POOL_OCID="ocid1.instancepool.oc1.ca-toronto-1.aaaaaaaaswx6wld7z77u32shwivedvgn5usofurtjzfd3kdnfopbi56wqlfa"


# Wait till Load Balancer status is OK
lb_status_check () {
while [ "$LB_STATUS" != "OK" ]
do
  sleep 1m
  LB_STATUS=`oci lb load-balancer-health get --load-balancer-id $LOAD_BALANCER_OCID | jq -r '.data.status'`
  date
  echo "Load Balance Status: $LB_STATUS"
done
}

# Ensure Load Balancer is in OK status before we start
LB_STATUS=`oci lb load-balancer-health get --load-balancer-id $LOAD_BALANCER_OCID | jq -r '.data.status'`
lb_status_check

# Terminate all running instances in this instance pool
for INSTANCE_OCID in $(oci compute instance list --compartment-id $COMPARTMENT_OCID --lifecycle-state RUNNING | jq -r '.data[]| select(."freeform-tags"."oci:compute:instancepool" | contains("'$INSTANCE_POOL_OCID'"))? | .id') 
do
  date
  echo "Terminate instance OCID: ${INSTANCE_OCID}"
  oci compute instance terminate --force --instance-id ${INSTANCE_OCID}
  LB_STATUS="UNKNOWN"
  sleep 5m
  lb_status_check
done

date
echo "Recycle all running instances completed"

Instance Pool Lifecycle Management

List all custom images

oci compute image list --all --compartment-id ocid1.compartment.oc1..aaaaaaaarocn3npultgruh5iwghhvor6s3kairokq4mil5bp52va6qkk7x6a | jq '.data[]|select(."compartment-id" | contains("ocid"))? | ."display-name"'

Create a custom image based on the master instance

oci compute image create --compartment-id ocid1.compartment.oc1..aaaaaaaarocn3npultgruh5iwghhvor6s3kairokq4mil5bp52va6qkk7x6a --instance-id ocid1.instance.oc1.ca-toronto-1.an2g6ljrmpjzp2icj3zh7m5ndqp365ahhxv5j2b2u4t7omixuugdqwojymsq --display-name ords-as-v3

Create a new instance configuration

oci compute-management instance-configuration create  --compartment-id ocid1.compartment.oc1..aaaaaaaarocn3npultgruh5iwghhvor6s3kairokq4mil5bp52va6qkk7x6a --instance-details file://instance-config-ords-as-v4.json --display-name instance-config-ords-as-v4

Update the instance pool with the new instance configuration

oci compute-management instance-pool update --instance-pool-id ocid1.instancepool.oc1.ca-toronto-1.aaaaaaaaswx6wld7z77u32shwivedvgn5usofurtjzfd3kdnfopbi56wqlfa --instance-configuration-id ocid1.instanceconfiguration.oc1.ca-toronto-1.aaaaaaaasggwcn4pa3vejt5t3wthrze46fctsfw7hmnmbetxj4qav6mykrrq

List all running instances using the old instance configuration

oci compute instance list --all --compartment-id ocid1.compartment.oc1..aaaaaaaarocn3npultgruh5iwghhvor6s3kairokq4mil5bp52va6qkk7x6a --lifecycle-state RUNNING | jq '.data[]| select(."freeform-tags"."oci:compute:instanceconfiguration" | contains("ocid"))? | {"display-name": ."display-name", id: .id, ic: ."freeform-tags"."oci:compute:instanceconfiguration"}'

Teminate all running instances using the old instance configuration

oci compute instance terminate --instance-id ocid1.instance.oc1.ca-toronto-1.an2g6ljrmpjzp2icr34kwlkjldkmohstbs7bfwgtm3e5k2plcc2lox6ow5qa  --force

oci lb load-balancer-health get --load-balancer-id ocid1.loadbalancer.oc1.ca-toronto-1.aaaaaaaaxgcpd4izvacefithsabg3l4dhvl7y6gt2mcgitdlwt3ez45vzp5q

oci compute instance terminate --instance-id ocid1.instance.oc1.ca-toronto-1.an2g6ljrmpjzp2icfnl33iqrbcy7rbpn7ixg46s6dgb4tyrbdqtm2cfiv5iq --force

oci lb load-balancer-health get --load-balancer-id ocid1.loadbalancer.oc1.ca-toronto-1.aaaaaaaaxgcpd4izvacefithsabg3l4dhvl7y6gt2mcgitdlwt3ez45vzp5q

Friday, February 5, 2021

Apply Oracle Database Oct 2020 CPU Patch

Microsoft Windows BP 19.9.0.0.20 (& associated OJVM)

Download these files from Oracle Support

1. p6880880_190000_MSWIN-x86-64.zip - OPatch

2. p32172777_199000DBRU_Generic.zip - Patch 32172777: DATAPATCH SHOULD SKIP RUNNING STANDARD.SQL
3. p31719903_190000_MSWIN-x86-64.zip - Patch 31719903: WINDOWS DATABASE BUNDLE PATCH 19.9.0.0.201020
4. p31668882_190000_MSWIN-x86-64.zip - Patch 31668882: OJVM RELEASE UPDATE 19.9.0.0.0

Update OPatch

Open command window as Administrator

rename %ORACLE_HOME%\OPatch to %ORACLE_HOME%\OPatch_20210205
unzip p6880880_190000_MSWIN-x86-64.zip to %ORACLE_HOME%\OPatch

Apply Patch 31719903: WINDOWS DATABASE BUNDLE PATCH 19.9.0.0.201020

Take Windows Services screenshot, shutdown all Oracle services, change them to MANUAL startup and reboot

unzip p31719903_190000_MSWIN-x86-64.zip
cd 31719903
%ORACLE_HOME%\OPatch\opatch apply

Apply Patch 32172777: DATAPATCH SHOULD SKIP RUNNING STANDARD.SQL

unzip p32172777_199000DBRU_Generic.zip
cd 32172777
rename %ORACLE_HOME%\sqlpatch\sqlpatch.pm to sqlpatch_20210205.pm
copy files\sqlpatch\sqlpatch.pm to %ORACLE_HOME%\sqlpatch\

Start listener, start Oracle database service

sqlplus / as sysdba
startup
alter pluggable database all open;
exit

cd /D %ORACLE_HOME%\OPatch
datapatch -verbose

Enabling new optimizer fixes

SELECT * FROM V$SYSTEM_FIX_CONTROL;

EXECUTE dbms_optim_bundle.getBugsforBundle;

EXECUTE dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES');

CREATE PFILE FROM SPFILE;

Apply Patch 31668882: OJVM RELEASE UPDATE 19.9.0.0.0

Reboot

unzip p31668882_190000_MSWIN-x86-64.zip
cd 31668882 
%ORACLE_HOME%\OPatch\opatch prereq CheckConflictAgainstOHWithDetail -ph .
%ORACLE_HOME%\OPatch\opatch apply

Start listener, start Oracle database service

sqlplus / as sysdba
shutdown
startup upgrade
alter pluggable database all open upgrade
exit

cd /D %ORACLE_HOME%\OPatch
datapatch -verbose

Base on the screenshot taken earlier, put back the original startup setting

sqlplus / as sysdba
shutdown
exit

reboot

Re-compile all database objects

sqlplus / as sysdba
exec utl_recomp.recomp_parallel;

Saturday, January 30, 2021

Oracle Kubernetes Cluster

In Oracle Cloud Shell

oci ce cluster create-kubeconfig --cluster-id ocid1.cluster.oc1.ca-toronto-1.aaaaaaaaae3dqmjwhaygkojumrrgknztme3wgnjwmfrwinlfgczwemzqg43d --file $HOME/.kube/config --region ca-toronto-1 --token-version 2.0.0 
kubectl create -f https://k8s.io/examples/application/deployment.yaml
kubectl get deployments
kubectl get pods -o wide
kubectl expose deployment nginx-deployment --port=80 --type=LoadBalancer
kubectl exec -n default -it nginx-deployment-6b474476c4-4b9fd -- cat /etc/nginx/nginx.conf

Friday, January 29, 2021

Autonomous Database datapump Export and Import

Create Auth Token

Identity->Users->User Details->Auth Token

descripton: datapump
token: ]V5Yln>rsk#>U]Pb64VK

Create Object Storage Bucket

Namespace: yzlrtthntlse

Setup Credential in Autonomous Database

sql admin@prod_high

BEGIN
    dbms_cloud.create_credential(credential_name => 'EXPORT_CRED', username => 'oracleidentitycloudservice/pchiu@leavemealone.com',
                                password => ']V5Yln>rsk#>U]Pb64VK');
END;
/
ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.EXPORT_CRED';

Export schema from Autonomous Database to Object Storage Bucket

expdp admin@prod_high filesize=5GB schema=ABC dumpfile=default_credential:https://objectstorage.ca-toronto-1.oraclecloud.com/n/yzlrtthntlse/b/bucket/o/prod-%U.dmp parallel=16 directory=data_pump_dir

Import dumpfiles from Object Storage Bucket into Autonomous Database

impdp admin@prod_high dumpfile=default_credential:https://objectstorage.ca-toronto-1.oraclecloud.com/n/yzlrtthntlse/b/bucket/o/db01-%U.dmp parallel=16 directory=data_pump_dir

Sunday, January 24, 2021

Oracle Linux Instance Stress Test

Install stress-ng

wget http://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/e/epel-release-7-13.noarch.rpm
rpm -Uvh epel-release*rpm
yum install -y stress-ng
rpm -e epel-release-7-13.noarch

Start Stress Test

#To run 8 CPU stressors with a timeout of 60 seconds and a summary at the end of operations.
stress-ng --cpu 8 --timeout 600 --metrics-brief

Autoscaling a Load Balanced Compute Instance

Prepare the template compute instance

Create /usr/local/bin/warmup. Instance specific setting goes here.

cat << EOF > /usr/local/bin/warmup
#!/bin/sh
# warmup: sync displayName data into our index and mark as healthy for load balancer
DISPLAY_NAME=`curl -H "Authorization: Bearer Oracle" -Ls http://169.254.169.254/opc/v2/instance/displayName`
cat << EOF > /opt/oracle/ords/config/ords/standalone/doc_root/index.html
<h3>$DISPLAY_NAME</h3>
<img style="max-width: 100%; height: auto; width: auto;" src="oci.jpg">
EOF

Create /etc/systemd/system/warmup.service

cat << EOF > /etc/systemd/system/warmup.service
[Unit]
After=network.target
  
[Service]
ExecStart=/usr/local/bin/warmup
  
[Install]
WantedBy=default.target
EOF

Setup warmup as service

systemctl start warmup
systemctl enable warmup
systemctl status warmup

Reboot the instance and ensure everything comes up. If okay, then Create Custom Image from this instance, ords-as-v1

Create instance-config-ords-as-v1.json, fix compartmentId, ssh_authorized_keys, imageId

cat << EOF > instance-config-ords-as-v1.json
{
    "instanceType": "compute",
    "launchDetails": {
      "compartmentId": "ocid1.compartment.oc1..aaaaaaaarocn3npultgruh5iwghhvor6s3kairokq4mil5bp52va6qkk7x6a",
      "createVnicDetails": {
      },
      "metadata": {
        "ssh_authorized_keys": "ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDuPoZBbXjC/5ojt+ECoIj5KGmLHWPPreWcjkp/1metOBgRv8f6W7w615+kRcrrdtyB5Tk6MzIs6CmF8RZ1BkkSankhHG62aKkqXn7T9VDenvEHaJpJqQRkhkYzJKyYqL+04O942gSgv8Kpw1IpFWvznfelf30xaxQzcLa0tMjYvOmqTmeAndEM3E1ZVMcEq3r3OlTTjCyfBPsdRPV2hFClvQziueRrUF61lhLotPUkCKxc6Iie+OpqW5hhU8vypFT0MAB6hoTH7EO7BGmQWInQlO3Pt4m7q9dNSee731TzRceDFa5cC/uigeAFgjEY8lwM5CFrcgMW3n3B3BpfumQv PC@PCLAPPY"
      },
        "shape": "VM.Standard.E3.Flex",
        "shapeConfig": {
          "memoryInGBs": 16.0,
          "ocpus": 1.0
        },
        "sourceDetails":   {
          "bootVolumeSizeInGBs": null,
          "imageId": "ocid1.image.oc1.ca-toronto-1.aaaaaaaabpzh6hlkg7jrdmm4eghhzhb3pwbk7pevvkn2cxqm3pk7s46fjn5q",
          "sourceType": "image"
        }
    }
}
EOF

Instance Pool Setup

Create instance configuration via OCI

oci compute-management instance-configuration create --instance-details file://instance-config-ords-as-v1.json --compartment-id ocid1.compartment.oc1..aaaaaaaarocn3npultgruh5iwghhvor6s3kairokq4mil5bp52va6qkk7x6a --display-name instance-config-ords-as-v1

# Get instance configuration details sample in JSON
oci compute-management instance-configuration create --generate-param-json-input instance-details

# Get instance configuration details
oci compute-management instance-configuration get --instance-configuration-id ocid1.instanceconfiguration.oc1.ca-toronto-1.aaaaaaaaxljbut4sn5hwqybubuarqr2lb5omshiog27bta4dcyvtydldetaa

From Instance Configurations, create Instance Pool, assign Load Balancer and create Autoscaling Configuration

Stress Test

We can run stress-ng on one instance, bring the CPU to 100% and wait to see auto scaling bring up a new instance within the Instance Pool.

Saturday, January 23, 2021

Setup ORDS Standalone Against Autonomous Database

Why ORDS standalone

For standalone ORDS, Oracle use Jetty. Jetty is a very capable webserver that on my laptop scale to 200+ rest calls per second. There deeper details on it's scaling abilities here: http://www.eclipse.org/jetty/documentation/current/high-load.html The best advantage is it simply works, scales, easy to get up and running. The disadvantage is mainly it's a purpose built and configured web server for ORDS. If someone needs more general web server features, it'd best to use WLS / Tomcat / Glassfish.

Download the following software to the Compute Instance

SQLcl
APEX and Patch Set
Autonomous database ADMIN wallet

Install software via yum

yum-config-manager --enable ol7_oci_included
yum update -y
yum install -y ords
yum install -y java
yum install -y jq
yum install -y oracle-release-el7
#yum search oracle-instant
yum install -y oracle-instantclient19.9-basic.x86_64
yum install -y oracle-instantclient19.9-tools.x86_64

Allow access to Port 8080

firewall-cmd --zone=public --add-port 8080/tcp --permanent
firewall-cmd --zone=public --add-port 8443/tcp --permanent
#firewall-cmd --permanent --zone=public --add-service=http
#firewall-cmd --permanent --zone=public --add-service=https
firewall-cmd --reload

Install SQLcl and ADMIN wallet

unzip sqlcl-20.4.1.351.1718.zip -d /opt
unzip Wallet_PROD.zip -d /usr/lib/oracle/19.9/client64/lib/network/admin
ln -s /opt/sqlcl/bin/sql /usr/lib/oracle/19.9/client64/bin/sql

add these to ~oracle/.bash_profile
export PATH=/usr/lib/oracle/19.9/client64/bin:$PATH
export LD_LIBRARY_PATH=/usr/lib/oracle/19.9/client64/lib
export TNS_ADMIN=/usr/lib/oracle/19.9/client64/lib/network/admin

Install APEX and Patch Set

RELEASE=20.2.0.00.20
mkdir -p /opt/oracle/apex/images/$RELEASE
unzip apex_20.2.zip -d /tmp/
cp -R /tmp/apex/images/* /opt/oracle/apex/images/$RELEASE
rm -rf /tmp/apex

unzip  p32006852_2020_Generic.zip -d /tmp/
cp -R /tmp/32006852/images/* /opt/oracle/apex/images/$RELEASE
rm -rf /tmp/32006852

ORDS Setup

Create alternate ORDS_PUBLIC_USER2 user

sql admin@prod_low
create user ords_public_user2 identified by "Opt12345678901234567890!";
grant connect to ORDS_PUBLIC_USER2;
begin
    ords_admin.provision_runtime_role(
        p_user => 'ORDS_PUBLIC_USER2'
        , p_proxy_enabled_schemas => true
    );
end;
/

Setup enviornment variables

ORDS_CONFIG_DIR=/opt/oracle/ords/config
ORDS_USER=ORDS_PUBLIC_USER2
ORDS_PASSWORD=Opt12345678901234567890!
SERVICE_NAME=prod_low
WALLET_BASE64=`base64 -w 0 Wallet_PROD.zip`

Create directories

mkdir -p $ORDS_CONFIG_DIR/ords/conf
mkdir -p $ORDS_CONFIG_DIR/ords/standalone/doc_root
mkdir -p $ORDS_CONFIG_DIR/ords/standalone/etc
mkdir -p $ORDS_CONFIG_DIR/ords/standalone/logs

Create $ORDS_CONFIG_DIR/ords/conf/apex_pu.xml

cat << EOF > $ORDS_CONFIG_DIR/ords/conf/apex_pu.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
  <entry key="db.username">$ORDS_USER</entry>
  <entry key="db.password">!$ORDS_PASSWORD</entry>
  <entry key="db.wallet.zip.service">$SERVICE_NAME</entry>
  <entry key="db.wallet.zip"><![CDATA[$WALLET_BASE64]]></entry>
</properties>
EOF

Create $ORDS_CONFIG_DIR/ords/defaults.xml

cat << EOF > $ORDS_CONFIG_DIR/ords/defaults.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
  <entry key="plsql.gateway.enabled">true</entry>
  <entry key="jdbc.InitialLimit">20</entry>
  <entry key="jdbc.MinLimit">20</entry>
  <entry key="jdbc.MaxLimit">50</entry>
  <entry key="jdbc.MaxStatementsLimit">20</entry>
</properties>
EOF

Create $ORDS_CONFIG_DIR/ords/standalone/etc/jetty-http.xml

cat << EOF > $ORDS_CONFIG_DIR/ords/standalone/etc/jetty-http.xml
<?xml version="1.0"?>
<!DOCTYPE Configure PUBLIC "-//Jetty//Configure//EN" "http://www.eclipse.org/jetty/configure.dtd">
<Configure id="Server" class="org.eclipse.jetty.server.Server">
    <Ref id="Handlers">
      <Call name="addHandler">
        <Arg>
          <New id="RequestLog" class="org.eclipse.jetty.server.handler.RequestLogHandler">
            <Set name="requestLog">
              <New id="RequestLogImpl" class="org.eclipse.jetty.server.NCSARequestLog">
                <Set name="filename"><Property name="jetty.logs" default="/opt/oracle/ords/config/ords/standalone/logs/"/>ords-access-yyyy_mm_dd.log</Set>
                <Set name="filenameDateFormat">yyyy_MM_dd</Set>
                <Set name="retainDays">90</Set>
                <Set name="append">true</Set>
                <Set name="extended">false</Set>
                <Set name="logCookies">false</Set>
                <Set name="LogTimeZone">GMT</Set>
            </New>
          </Set>
        </New>
        </Arg>
      </Call>
    </Ref>
</Configure>
EOF

Edit /opt/oracle/ords/config/ords/standalone/standalone.properties

jetty.port=8080
standalone.context.path=/ords
standalone.doc.root=/opt/oracle/ords/config/ords/standalone/doc_root
standalone.scheme.do.not.prompt=true
standalone.static.context.path=/i
standalone.static.path=/opt/oracle/apex/images
jetty.secure.port=8443
#ssl.cert=leavemealone.com.pem
#ssl.cert.key=leavemealone.com.key
#ssl.host=pws.leavemealone.com

Configure ORDS

ords configdir $ORDS_CONFIG_DIR

/etc/ords/ords.conf ORDS_BASE_PATH=/opt/oracle

Test run ORDS

ords standalone
wget http://localhost:8080/i/20.2.0.00.20/apex_version.txt

Auto Start ORDS

systemctl start ords
systemctl enable ords
systemctl status ords

Switch APEX static resources repository to Oracle Content Delivery Network (CDN).

BEGIN
    apex_instance_admin.set_parameter (
        p_parameter   => 'IMAGE_PREFIX',
        p_value       => 'https://static.oracle.com/cdn/apex/20.2.0.00.20/');

    COMMIT;
END;