0

Below is the script i drafted, that will work based on the SIDs it will get from

 ps -ef | grep pmon

Once the SID is grepped, it will pass the SID to dbenv() to set the necessary parameters, and it also cuts the DB_VERSION from /etc/oratab entries.

Based on the version, if 12 or 11 then the script should execute a block, or if the version is 10 or 9, it should execute a block.

12 or 11 has the alert logs under TRACE_FILE's value, 10 or 9 wont have any output for TRACE_FILE, so 10 and 9 should clear the alert log based on BDUMPs value.

So I have drafted the below script and it works fine, I feel the script has got lot of repetition where i applied logic for DB_VERSION.

Any ideas on how could this script be enhanced

#############################################################################################################################################################
#!/bin/bash
#############################################################################################################################################################

TODAY=`date +%Y-%m-%d`
DATE=`date +%Y%b%d`
YESTERDAY=`date -d '-1 day' +%b%Y`
YDAY=`date -d '-1 day' +%Y%b%d`
HOST=`hostname`
LOG_LOCATION="/home/oracle/utility_script/dba_maint/logs"

mkdir -p ${LOG_LOCATION}

LOG_FILE="${LOG_LOCATION}/oracle_files_cleanup_${DATE}.log"

rm ${LOG_FILE} 2>/dev/null

dbenv ()
{
        ORACLE_HOME=`cat /etc/oratab | grep ^$ORACLE_SID | cut -d":" -f2`; export ORACLE_HOME
        PATH=$ORACLE_HOME/bin:$PATH ; export PATH
        LD_LIBRARY_PATH=$ORACLE_HOME/lib ; export LD_LIBRARY_PATH
        DB_VERSION=`cat /etc/oratab | grep "^$ORACLE_SID" | cut -d":" -f2 | rev | cut -d"/" -f2| rev | cut -d"." -f1`; export DB_VERSION
}


dbcheck()
{
        sqlplus / as sysdba << EOF &>${LOG_LOCATION}/dbcheck.out
        exit
EOF
}

sql_plus()
{
        sqlplus -s / as sysdba << EOF &>/dev/null
        SET NEWPAGE NONE;
        set lines 200 pages 300;
        set feedback off;
        set heading off;
        spool ${LOG_LOCATION}/$1.log
        $2
        exit
EOF
}

for SID in `ps -eaf | grep pmon | grep -v grep | awk '{print $8}' | sort | cut -d"_" -f3`
do
        ORACLE_SID=${SID} ; export ORACLE_SID
        dbenv ${ORACLE_SID}                                                     #-- Passing the ORACLE_SID to dbenv function to source the database.
        if [ ${DB_VERSION} -eq 11 -o ${DB_VERSION} -eq 12 ]
        then
                dbcheck
                DB_CHECK=`cat ${LOG_LOCATION}/dbcheck.out | egrep "ORA|SP2|idle"`
                LOWER_SID=`echo ${ORACLE_SID} | tr '[A-Z]' '[a-z]'`

#-- Queries to fetch the proper log location from database

                ADUMP="select DISPLAY_VALUE from v\$parameter where name='audit_file_dest';"
                BDUMP="select DISPLAY_VALUE from v\$parameter where name='background_dump_dest';"
                CDUMP="select DISPLAY_VALUE from v\$parameter where name='core_dump_dest';"
                UDUMP="select DISPLAY_VALUE from v\$parameter where name='user_dump_dest';"
                TRACE_FILE="select DISPLAY_VALUE from v\$parameter where name='diagnostic_dest';"

#-- Calls the sql_plus function with the parameters as the logname and SQL query

                sql_plus "adump_${ORACLE_SID}" "${ADUMP}"
                sql_plus "bdump_${ORACLE_SID}" "${BDUMP}"
                sql_plus "cdump_${ORACLE_SID}" "${CDUMP}"
                sql_plus "udump_${ORACLE_SID}" "${UDUMP}"
                sql_plus "trace_${ORACLE_SID}" "${TRACE_FILE}"

#-- Remove any empty lines after the log location

                ADUMP_LOC=`cat ${LOG_LOCATION}/adump_${ORACLE_SID}.log | sed 's/[[:blank:]]*$//'`
                BDUMP_LOC=`cat ${LOG_LOCATION}/bdump_${ORACLE_SID}.log | sed 's/[[:blank:]]*$//'`
                CDUMP_LOC=`cat ${LOG_LOCATION}/cdump_${ORACLE_SID}.log | sed 's/[[:blank:]]*$//'`
                UDUMP_LOC=`cat ${LOG_LOCATION}/udump_${ORACLE_SID}.log | sed 's/[[:blank:]]*$//'`
                TRACE_LOC=`cat ${LOG_LOCATION}/trace_${ORACLE_SID}.log | sed 's/[[:blank:]]*$//'`

#-- If the Database is not in idle state or without any errors, start housekeeping

                if [ -z "${DB_CHECK}" ]
                then
                        echo -e "\t\t\t\t HOUSEKEEPING for database : ${ORACLE_SID}" >>${LOG_FILE}
                        echo -e "\t\t\t\t ============ === ======== = =============" >>${LOG_FILE}

#-- Cleans .aud files older than 60 days in ADUMP location

                        if [ ! -z "${ADUMP_LOC}" ]
                        then
                                        echo -e "\t\t\tAdump cleanup" >> ${LOG_FILE}
                        fi

#-- Cleans .trm or .trc files older than 60 days in BDUMP location

                        if [ ! -z "${BDUMP_LOC}" ]
                        then
                                        echo -e "\n\n\t\t\tBdump cleanup" >> ${LOG_FILE}
                        fi

#-- Cleans .trm or .trc files older than 60 days in CDUMP location

                        if [ ! -z "${CDUMP_LOC}" ]
                        then
                                        echo -e "\n\t\t\tCdump cleanup" >> ${LOG_FILE}
                        fi

#-- Cleans .trm or .trc files older than 60 days in UDUMP location

                        if [ ! -z "${UDUMP_LOC}" ]
                        then
                                        echo -e "\n\t\t\tUdump cleanup" >> ${LOG_FILE}
                        fi

#-- Rotates the Database alert log on 01st of every month.

                        if [ `date +%d` -eq 01 ]
                        then
                                if [ ! -z "${TRACE_LOC}" ]
                                then
                                        echo -e "\n\t\t\tALERT LOG ROTATION" >> ${LOG_FILE}
                                fi
                        fi

#-- Rotates the Listener log on 01st of every month.

                        if [ `date +%d` -eq 01 ]
                                        if [ ! -z "${TRACE_LOC}" ]
                                        then
                                                        echo -e "\n\t\t\tLISTENER LOG ROTATION" >> ${LOG_FILE}
                                        fi
                        fi
                else
                        echo -e "ERROR : Please fix the below error in database - ${ORACLE_SID} on host - ${HOST} \n ${DB_CHECK}" >> ${LOG_LOCATION}/house_keeping_fail_${ORACLE_SID}_${DATE}.log
                fi
        elif [ ${DB_VERSION} -eq 10 -o ${DB_VERSION} -eq 9 ]
        then
                dbcheck
                DB_CHECK=`cat ${LOG_LOCATION}/dbcheck.out | egrep "ORA|SP2|idle"`

#-- Queries to fetch the proper log location from database

                ADUMP="select DISPLAY_VALUE from v\$parameter where name='audit_file_dest';"
                BDUMP="select DISPLAY_VALUE from v\$parameter where name='background_dump_dest';"
                CDUMP="select DISPLAY_VALUE from v\$parameter where name='core_dump_dest';"
                UDUMP="select DISPLAY_VALUE from v\$parameter where name='user_dump_dest';"

#-- Calls the sql_plus function with the parameters as the logname and SQL query

                sql_plus "adump_${ORACLE_SID}" "${ADUMP}"
                sql_plus "bdump_${ORACLE_SID}" "${BDUMP}"
                sql_plus "cdump_${ORACLE_SID}" "${CDUMP}"
                sql_plus "udump_${ORACLE_SID}" "${UDUMP}"

#-- Remove any empty lines after the log location

                ADUMP_LOC=`cat ${LOG_LOCATION}/adump_${ORACLE_SID}.log | sed 's/[[:blank:]]*$//'`
                BDUMP_LOC=`cat ${LOG_LOCATION}/bdump_${ORACLE_SID}.log | sed 's/[[:blank:]]*$//'`
                CDUMP_LOC=`cat ${LOG_LOCATION}/cdump_${ORACLE_SID}.log | sed 's/[[:blank:]]*$//'`
                UDUMP_LOC=`cat ${LOG_LOCATION}/udump_${ORACLE_SID}.log | sed 's/[[:blank:]]*$//'`

#-- If the Database is not in idle state or without any errors, start housekeeping

                if [ -z "${DB_CHECK}" ]
                then
#-- Cleans .aud files older than 60 days in ADUMP location

                        if [ ! -z "${ADUMP_LOC}" ]
                                        echo -e "\t\t\tAdump cleanup" >> ${LOG_FILE}
                        fi

#-- Cleans .trm or .trc files older than 60 days in BDUMP location

                        if [ ! -z "${BDUMP_LOC}" ]
                        then
                                        echo -e "\n\n\t\t\tBdump cleanup" >> ${LOG_FILE}
                        fi

#-- Cleans .trm or .trc files older than 60 days in CDUMP location

                        if [ ! -z "${CDUMP_LOC}" ]
                        then
                                        echo -e "\n\t\t\tCdump cleanup" >> ${LOG_FILE}
                        fi

#-- Cleans .trm or .trc files older than 60 days in UDUMP location

                        if [ ! -z "${UDUMP_LOC}" ]
                        then
                                        echo -e "\n\t\t\tUdump cleanup" >> ${LOG_FILE}
                        fi

#-- Rotates the ${DB_VERSION} version Database alert log on 01st of every month.

                        if [ `date +%d` -eq 01 ]
                        then
                                if [ ! -z "${BDUMP_LOC}" ]
                                then
                                        echo -e "\n\t\t\tALERT LOG ROTATION" >> ${LOG_FILE}
                                fi
                        fi
               else
                       echo -e "ERROR : Please fix the below error in database - ${ORACLE_SID} on host - ${HOST} \n ${DB_CHECK}" >> ${LOG_LOCATION}/house_keeping_fail_${ORACLE_SID}_${DATE}.log
               fi
        fi
done
exit $?
#---------------------------------------------------------------------END-----------------------------------------------------------------------------------#
  • 3
    I'm voting to close this question as off-topic because this is asking for a general code review rather than a specific answerable question. Stack Exchange has a code review site for asking this kind of question. – cas Feb 23 '18 at 9:14
  • that said, here's some general advice: 1. use $() rather than backticks for command substitution. 2. you (almost) never need to pipe grep in to awk. e.g. instead of ps -eaf | grep pmon | grep -v grep | awk '{print $8}', you can do ps -eaf | awl '/pmon/ && ! /grep/ {print $8}'. 3. you don't need to do that anyway, you can just do ps h -o cmd -C pmon instead. 4. sed can read files by itself, you don't need to pipe cat into sed. 5. [ -n "$var" ] is the same as [ ! -z "$var" ]. -z tests for empty string, -n tests for non-empty string. – cas Feb 23 '18 at 9:24
  • @cas I consider your advice, I will make changes accordingly. And I don't think this comes under code review. I have a doubt of how to use if statements more efficiently, instead of repeating the code in script. I don't know how to do that efficiently. – sabarish jackson Feb 23 '18 at 9:35
  • The #!-line has to be the very first line in the script. – Kusalananda Feb 23 '18 at 10:59
2

This question probably belongs on https://codereview.stackexchange.com/ instead of here, but here are my recommendations:

  1. use $() rather than backticks for command substitution.

  2. you (almost) never need to pipe grep in to awk. For example, instead of:

    ps -eaf | grep pmon | grep -v grep | awk '{print $8}'
    

    you can do:

    ps -eaf | awk '/pmon/ && ! /grep/ {print $8}'
    

    Similarly, piping grep into cut is usually better done with awk. e.g instead of:

    cat /etc/oratab | grep ^$ORACLE_SID | cut -d":" -f2
    

    use

    awk -F: "/^$ORACLE_SID/ {print \$2}" /etc/oratab
    

    (normally you wouldn't escape the $ of $2 in an awk script because it's more usual to single-quote the entire awk script. In this case, we're double-quoting the awk script so that we can use the bash variable $ORACLE_SID in awk, so we need to backslash-escape awk's $2 to prevent the shell from replacing it with its own $2)

  3. you don't need to do pipe ps into grep or awk anyway. You can just do ps h -o cmd -C pmon instead. Or use pgrep.

  4. sed can read files by itself, you don't need to pipe catinto sed. So can grep and awk and perl and cut and every other standard text-processing tool.

  5. [ -n "$var" ] is the same as [ ! -z "$var" ].

    -z tests for empty string, -n tests for non-empty string.

  6. there are several occasions where you haven't double-quoted your variables. you should (almost) always double-quote variables when you use them.

  7. single-quotes are for fixed, literal strings. double-quotes are for when you want to interpolate a variable or command substitution into a string.

  8. indenting with 8 characters is excessive. use 2 or 4 spaces per indent level. or set the tab stop in your editor to 2 or 4 spaces.

  9. it's a good habit to use lowercase or MixedCase for your own variables, leaving ALLCAPS variable names for standard utilities and common programs.

  10. tools like sqlplus, mysql, psql, etc are very convenient for doing scripted database queries in sh or bash etc but you have to be extremely careful about any variables you use with SQL commands - especially if the values in the variables come from user-supplied data, or other "untrusted" sources. It is very easy to break a script if the input data is unvalidated and unsanitised. It is just as easy to create an SQL injection bug.

    For non-trivial SQL queries, you should probably learn perl or python or some other language with a database library that supports placeholders to avoid any issues with quoting of variables in sql commands.

Not the answer you're looking for? Browse other questions tagged or ask your own question.