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

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