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.