Shell Script for Daily to Identify New Invalid generated daily in Oracle Database


In this post, I am sharing a shell script to daily identify if there are any new invalids generated in oracle database.
You can modify and change as you want.


Scripts:

vi invalids.sh


#!/bin/bash

# Set environment variables (adjust as needed based on env)
export ORACLE_SID=FOA12
export PATH=$ORACLE_HOME/bin:$PATH
export ORAENV_ASK=NO

#Mail functionality
RECIPIENTS=support@funoracleapps.com
send_mail() {
mailx -s "New Invalid Objects Detected" ${RECIPIENTS} < mail_body.txt
}


# Directory to store previous invalids list
PREVIOUS_INVALIDS_FILE="/tmp/previous_invalids.txt"

# Directory to store current invalids list
CURRENT_INVALIDS_FILE="/tmp/current_invalids.txt"

# Get current invalid objects
sqlplus -S "/as sysdba" <<EOF
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SPOOL $CURRENT_INVALIDS_FILE
SELECT object_name FROM dba_objects WHERE status = 'INVALID';
SPOOL OFF
EXIT
EOF

# Check if the previous invalids file exists
if [[ -f $PREVIOUS_INVALIDS_FILE ]]; then
    # Compare current and previous invalids to find new invalids
    comm -13 <(sort $PREVIOUS_INVALIDS_FILE) <(sort $CURRENT_INVALIDS_FILE) > new_invalids.txt
    if [[ -s new_invalids.txt ]]; then
        # Send email notification with new invalids
        echo "New invalid objects found:" > mail_body.txt
        cat new_invalids.txt >> mail_body.txt
        send_mail
    else
        echo "No new invalid objects found."
    fi
else
    echo "Script is running first time or previous invalid object data file is not present"
    sort $CURRENT_INVALIDS_FILE > new_invalids.txt
    cat new_invalids.txt >> mail_body.txt
    send_mail
fi

# Replace previous invalids file with current one
cp $CURRENT_INVALIDS_FILE $PREVIOUS_INVALIDS_FILE

# Clean up temporary files
rm -f new_invalids.txt mail_body.txt






If you like please follow and comment