Oracle Cheatsheet

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;