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

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;