Automating Oracle OCI Backup Failure Detection with OCI dbaascli

The following script is designed to streamline the detection of database backup failures and automate the initiation of Level 1 backups for failed databases:

#!/bin/bash
# July 2023 Sandeep Reddy Narani
#
# Interactive database picker
# Displays a menu of databases defined in oratab, asks which one to use, and then sets environment variables using oraenv

for dbname in $(ps -ef | grep smon | grep c | awk '{print $8}' | cut -d'_' -f3 | sed 's/.$//' | uniq | sort); do
    failed_db=$(dbaascli database backup --dbname $dbname --showHistory --all | grep -vi archive | grep -v "dbaascli execution completed" | tail -n 2 | awk -F "|" '/Failure/{print $0}' | tail -n 1)

    if [ -n "$failed_db" ]; then
        echo "---Found Failure entry for $dbname---"
        echo "--Starting level1 backup for Failed Database ---"
        echo "Backup failure detected for database $dbname. Level 1 backup started." | mailx -s "OCI-Backup Failure Alert" oracle_oem_reports@bmc.com
        dbaascli database backup --dbname $dbname --start --level1
    else
        echo "No Failed Backups found for $dbname Database."
    fi
done
exit;

1. The script first identifies the running databases by parsing the output of the ps -ef command.
2. For each database, it queries the dbaascli utility to check the backup history and looks for recent backup failures.
3. If a backup failure is detected, the script sends an email alert to notify the relevant personnel.
4. The script then initiates a Level 1 backup for the failed database using the dbaascli utility.

When executed, the script will generate an output similar to the following: sh chk_runocibkp.sh

 sh chk_runocibkp.sh
Output: 
No Failed Backups found for ceaa001p Database.
No Failed Backups found for ceaa003p Database.
No Failed Backups found for ceaa004p Database.
No Failed Backups found for ceaa005p Database.
No Failed Backups found for ceaa006p Database.

• Proactive Management: Immediate alerts ensure quick response and prevent prolonged backup failures.
• Reduction of Manual Effort: Manual checks and intervention are minimized, reducing the risk of human errors.

Automating critical database management tasks like backup failure detection helps maintain the stability and availability of your Oracle databases. By leveraging the power of dbaascli and scripting, you can ensure that your databases are properly protected and that potential issues are addressed promptly.

For more information about Oracle Database as a Service (DBaaS) and the dbaascli utility, check out the Oracle documentation.