OCI CLI to List all the Database and the Backup Status

OCI CLI to List all the Database and the Backup Status

This bash scripts needs the tenancy ID to be updated. It loops through all the compartments and then fetches the backup details of each database.  It validates if the database is a Standby and skips validation. It pulls only database with role as PRIMARY. It validates if Auto Backup is enabled and also it validate if recovery window is 30 days. Feel free to customize it to your requirement. It is not coded for RAC Instances , please customize it.

ociCompartmentList=$(oci iam compartment list --compartment-id ocid1.tenancy.oc1..aaaa1111222334)
for c in $(echo "$ociCompartmentList" | jq '.data | keys | .[]')
do
        compartment_ocid=$(echo "$ociCompartmentList" | jq -r ".data[$c].\"id\"")
        ocidbList=$(oci db database list -c $compartment_ocid)
        for i in $(echo "$ocidbList" | jq '.data | keys | .[]')
        do
                dbid=$(echo "$ocidbList" | jq -r ".data[$i].\"id\"")
                dbrole=$(oci db data-guard-association list --database-id $dbid| jq -r ".data[].\"role\"")
                if [    -z $dbrole ]
                then
                        echo "Stand alone Database , No Role Assigned , Setting to Primary"
                        dbrole="PRIMARY"
                fi
                if [ $dbrole == "PRIMARY" ]
                then
                        dbname=$(echo "$ocidbList" | jq -r ".data[$i].\"db-name\"")
                        dbsystemid=$(echo "$ocidbList" | jq -r ".data[$i].\"db-system-id\"")
                        dbbackupconfig=$(echo "$ocidbList" | jq -r ".data[$i].\"db-backup-config\"")
                        dbautoBackupEnabled=$(echo "$dbbackupconfig" | jq -r ".\"auto-backup-enabled\"")
                        dbrecoveryWindow=$(echo "$dbbackupconfig" | jq -r ".\"recovery-window-in-days\"")
                        hostname=$(oci db node list -c $compartment_ocid --db-system-id $dbsystemid| jq -r ".data[].\"hostname\"")
                        if [ $dbautoBackupEnabled == "true" ]
                        then
                                echo "Hostname: $hostname | Database: $dbname | AutoBackup: $dbautoBackupEnabled | SUCCESS"
                        else
                                echo "Hostname: $hostname | Database: $dbname | AutoBackup: $dbautoBackupEnabled | FAILURE"
                        fi
                        if [ $dbrecoveryWindow == "30" ]
                        then
                                echo "Hostname: $hostname | Database: $dbname | RecoveryWindow: $dbrecoveryWindow | SUCCESS"
                        else
                                echo "Hostname: $hostname | Database: $dbname | RecoveryWindow: $dbrecoveryWindow | FAILURE"
                        fi
                else
                        echo "Hostname: $hostname | Database: $dbname | Role : $dbrole - Skipping"
                fi
        done
done 

Hostname: ebstestserver-001| Database: TEST1 |AutoBackup: true |SUCCESS

Hostname: ebstestserver-001| Database: TEST1 |RecoveryWindow: 30 |SUCCESS

Hostname: ebstestserver-002| Database: TEST2 |AutoBackup: false |FAILURE

Hostname: ebstestserver-003| Database: TEST3 |AutoBackup: true |SUCCESS

Hostname: ebstestserver-001| Database: TEST3 |RecoveryWindow: 30 |SUCCESS

Hostname: ebstestserver-004| Database: TEST4 |AutoBackup: true |SUCCESS

Hostname: ebstestserver-005| Database: TEST5 |AutoBackup: true |SUCCESS

Hostname: ebstestserver-001| Database: TEST5 |RecoveryWindow: 10 |FAILURE

OCI CLI to List all the Database and its Host Lifecycle

 OCI CLI to List all the Database and its Host Lifecycle

This bash scripts needs the tenancy ID to be updated. It loops through all the compartments and then fetches the life cycle of each database and it respective hosts. 

The below code fetches lifecycle of  the hosts where the database is running.

ociCompartmentList=$(oci iam compartment list --compartment-id ocid1.tenancy.oc1..aaaa1111222334)

for c in $(echo "$ociCompartmentList" | jq '.data | keys | .[]')

do

 compartment_ocid=$(echo "$ociCompartmentList" | jq -r ".data[$c].\"id\"")

 ocidbList=$(oci db database list -c $compartment_ocid)

 for i in $(echo "$ocidbList" | jq '.data | keys | .[]')

 do

ocidbnodeList=$(oci db node list -c $compartment_ocid --db-system-id $(echo $ocidbList | jq -r ".data[$i].\"db-system-id\""))

hostname=$(echo "$ocidbnodeList" | jq -r ".data[].\"hostname\"")

lifecycle=$(echo "$ocidbnodeList" | jq -r ".data[].\"lifecycle-state\"")

echo "Hostname: $hostname | LifeCycle: $lifecycle"

 done

done

Hostname: ebstestserver-001| LifeCycle: RUNNING

Hostname: ebstestserver-002| LifeCycle: RUNNING

Hostname: ebstestserver-003| LifeCycle: RUNNING

Hostname: ebstestserver-004| LifeCycle: RUNNING

Hostname: ebstestserver-005| LifeCycle: RUNNING

The below code fetches the lifecycle of the database along with the hostnames

ociCompartmentList=$(oci iam compartment list --compartment-id ocid1.tenancy.oc1..aaaa1111222334)

for c in $(echo "$ociCompartmentList" | jq '.data | keys | .[]')

do

 compartment_ocid=$(echo "$ociCompartmentList" | jq -r ".data[$c].\"id\"")

 ocidbList=$(oci db database list -c $compartment_ocid)

 for i in $(echo "$ocidbList" | jq '.data | keys | .[]')

 do

dbname=$(echo "$ocidbList" | jq -r ".data[$i].\"db-name\"")

dbsystemid=$(echo "$ocidbList" | jq -r ".data[$i].\"db-system-id\"")

lifecycle=$(echo "$ocidbList" | jq -r ".data[$i].\"lifecycle-state\"")

hostname=$(oci db node list -c $compartment_ocid --db-system-id $dbsystemid| jq -r ".data[].\"hostname\"")

echo "Hostname: $hostname | Database: $dbname | LifeCycle: $lifecycle"

 done

done

Hostname: ebstestserver-001| Database: TEST1 |LifeCycle: RUNNING

Hostname: ebstestserver-002| Database: TEST2 |LifeCycle: RUNNING

Hostname: ebstestserver-003| Database: TEST3 |LifeCycle: RUNNING

Hostname: ebstestserver-004| Database: TEST4 |LifeCycle: RUNNING

Hostname: ebstestserver-005| Database: TEST5 |LifeCycle: RUNNING

Note : The script is helps to get the state of single instance vm db systems. for RAC you may have to modify it.

OCI CLI to List all the Hosts Lifecycle

OCI CLI to List all the Hosts Lifecycle

This bash scripts needs the tenancy ID to be updated. It loops through all the compartments and then fetches the life cycle of each hosts. 

ociCompartmentList=$(oci iam compartment list --compartment-id ocid1.tenancy.oc1..aaaa1111222334)
for c in $(echo "$ociCompartmentList" | jq '.data | keys | .[]')
do
 compartment_ocid=$(echo "$ociCompartmentList" | jq -r ".data[$c].\"id\"")
 ociinstanceList=$(oci compute instance list -c $compartment_ocid)
 for i in $(echo "$ociinstanceList" | jq '.data | keys | .[]')
 do
   hostname=$(echo "$ociinstanceList" | jq -r ".data[$i].\"display-name\"")
   lifecycle=$(echo "$ociinstanceList" | jq -r ".data[$i].\"lifecycle-state\"")
   echo "Hostname: $hostname | LifeCycle: $lifecycle"
 done
done

Hostname: ebstestserver-001| LifeCycle: RUNNING
Hostname: ebstestserver-002| LifeCycle: RUNNING
Hostname: ebstestserver-003| LifeCycle: RUNNING
Hostname: ebstestserver-004| LifeCycle: RUNNING
Hostname: ebstestserver-005| LifeCycle: RUNNING
Hostname: ebstestserver-006| LifeCycle: RUNNING
Hostname: ebstestserver-007| LifeCycle: RUNNING
Hostname: ebstestserver-008| LifeCycle: RUNNING
Hostname: ebstestserver-009| LifeCycle: RUNNING
Hostname: ebstestserver-010| LifeCycle: RUNNING
Hostname: ebstestserver-011| LifeCycle: RUNNING
Hostname: ebstestserver-012| LifeCycle: RUNNING
Hostname: ebstestserver-013| LifeCycle: RUNNING
Hostname: ebstestserver-014| LifeCycle: RUNNING
Hostname: ebstestserver-015| LifeCycle: RUNNING
Hostname: ebstestserver-016| LifeCycle: RUNNING
Hostname: ebstestserver-017| LifeCycle: RUNNING
Hostname: ebstestserver-018| LifeCycle: RUNNING
Hostname: ebstestserver-019| LifeCycle: RUNNING
Hostname: ebstestserver-020| LifeCycle: RUNNING

Hope it helps.

Ignore Python Warning

 Ignore Python Warning

# compartmentList=$(oci iam compartment list)

/usr/lib/python3.6/site-packages/oci/packages/cryptography/hazmat/bindings/openssl/binding.py:179: CryptographyDeprecationWarning: OpenSSL version 1.0.2 is no longer supported by the OpenSSL project, please upgrade. The next version of cryptography will completely remove support for it.

  utils.CryptographyDeprecationWarning,

# 

here is a way to ignore the warning if you have updated packages

# export PYTHONWARNINGS="ignore"

# compartmentList=$(oci iam compartment list)

#

EBS 12.2 ADOP SQL to Find Session Details

EBS 12.2 ADOP SQL to Find Session Details 

A Simple and Useful SQL to find the list of Patches and Actions that are performed in each Patching Cycle. Hope it helps.

set pagesize 200;
set linesize 200;
column adop_session_id format 999999999999;
column activity format a15;
column status format a15;
column applied_fs format a10;
column patch_fs format a10;
column options format a10;
column node format a15;
column start_date format a17;
column end_date format a17;
column clone_status format a20;
column adop_session_id new_value sid noprint
ttitle left 'Session ID : ' sid skip 2
break on adop_session_id skip page
select 
adop_session_id,
node_name node,
decode(length(trim(translate(bug_number, ' +-.0123456789', ' '))),null,'Patch-'||bug_number,bug_number) activity,
status ,
upper(decode(substr(applied_file_system_base,1,1),'/',substr(applied_file_system_base,-3),applied_file_system_base)) applied_fs,
upper(decode(substr(patch_file_system_base,1,1),'/',substr(patch_file_system_base,-3),patch_file_system_base)) patch_fs,
upper(adpatch_options) options,
to_char(start_date,'DD-Mon-YYYY HH24:MI') start_date,
to_char(end_date,'DD-Mon-YYYY HH24:MI') end_date,
clone_status,
autoconfig_status
from apps.ad_adop_session_patches
order by adop_session_id,start_date; 

File System for Oracle EBS Release 12

Well there are many ways to get Oracle EBS Release 12 installed, I am here going to write about Enterprise Deployments. 

Shared vs Non-Shared - when enterprise deployments are considered you may need multiple nodes for your internal Private tier and for your external DMZ tier. The code for EBS has to be available on all the nodes so that it can be accessed by end users through various services like http , forms , concurrent manager... The code becomes easy to maintain if it is Shared and one single repository is used by all these multi-node in an enterprise deployments. Config becomes easy , Patching becomes easy... Having them in the Non-Shared isolates the code and becomes tought to maintain it. Though EBS 12 provides various tools to keep them in sync but it gets complex as you grow. So choosing a Shared file system is a good decision for enterprise deployments.

Shared Storage that are commonly available are NFS/ZFS , in OCI we have FSS 

Here is another option that might be interesting in OCI which is attaching a volume to multiple instances.

Please be aware of the limitation and do review before considering this solution.

- Concurrent write operations to block volumes are not provided so basically you can have only one instance write to it at a time. well there must be a solution for this... not one there are many... Cluster Aware Solution. There are many options listed from differnet vendors which you can find here https://en.wikipedia.org/wiki/Clustered_file_system 

Oracle Cluster File System so called as OCFS2 (version 2) is a Cluster Aware solution provided by Oracle. Similar solutions are provided by GlusterFS , Veritas , GFS2 , VMFS

In OCI there are three simple steps to get this for your Enterprise Deployments

• Create your EBS deployments and attach block volume to the middleware instances as Read/Write-Sharable
• Setup OCFS2 
• Create File Systems on OCFS2
• Mount the File System 
• Install/Clone EBS with Shared APPL_TOP Option

Detailed documentation on how to setup OCFS2 is available in the Linux Admin Guide

Oracle Linux Admin Guide for OCFS2

Also there is a video guide by Oracle on how to setup OCFS2 on OCI

Configure and Test an OCFS2 Cluster with Oracle Linux on Oracle Cloud Infrastructure

Happy New Year 2021