Skip to main content

Sergey's script of rman Incrementally Updated Backups

 

#!/bin/bash

# This script invokes rman to perform a daily

# incremental backup of db

# if ARC key is used it makes an archive log backup if a threshold is exceeded

# It checks for errors and sends email if any.

# It cleans up this script log files older then the log file retention period, Check "set up variables" section.


# set up a debug mode

##set -x


host_name=$(hostname)

opsys=$(uname)

script=$(basename $0)

if [ ${opsys} = 'Linux' ]; then

    scriptpath=$(dirname $(readlink -f $0))

else

    scriptpath=$(dirname $0)

fi

scriptNoExt=$(echo ${script} | cut -d \. -f 1)

paramFilePath=${scriptpath}/${scriptNoExt}.param

oraSetFile="${HOME}/.oraset_ss"


#----------------

# Functions

usage () {

    echo "Usage: ${script} db_instance_name type_of_backup"

    echo "Where type_of_backup = [empty|ARC|DELARC|DELBACKUPS]"

    echo "    empty - backup the whole database"

    echo "    ARC - backup and clean up archive logs"

    echo "    DELARC - remove all archive logs"

echo "    DELBACKUPS - delete backups in flash volume if flash is filled higher then ${flashVolThreshold}% (flashVolThreshold)"

}

#----------------

usage_exit () {

    echo

    usage

    echo

    echo "$1"

    exit $2

}


# Check existing some files and exit if they don not exist.

if [ ! -f ${paramFilePath} ]; then

    usage_exit "Param file(${paramFilePath}) not existing. Exit" 5

fi

if [ ! -f ${oraSetFile} ]; then

    usage_exit "Oracle settings file(${oraSetFile}) not existing. Exit" 6

fi


#----------------

# Include source file with parameters

#

. ${paramFilePath}


log_dir=${scriptpath}/logs

[ ! -d "${log_dir}" ] && ( mkdir ${log_dir} )


#-----

#

function sendEmail() {

    local locMessage=${1}

    local locSubject=${2}

    local locContent=${3}

    local locTiteColor="#610B4B"


    (

        echo "Subject: ${locSubject}"

        echo "MIME-Version: 1.0"

        echo "Content-Type: text/html"

        echo "Content-Disposition: inline"

        echo "<HTML><BODY><PRE>"


        echo "<h2 style=\"background-color:#E6E6E6; color:${locTiteColor};\">${locMessage}</h2>"


        if [ -f ${locContent} ]; then

            cat ${locContent}

        fi


        echo "</PRE></BODY></HTML>"

    ) | /usr/sbin/sendmail ${email_distribution_list}


}

#----------------

function fun_print_message () {

      loc_pad_lenght=${1}

      loc_proc=${2}

      loc_status=${3}

      loc_file_name=${4}

      if [ ${loc_file_name} ]; then

            echo -n "$loc_proc ">>${loc_file_name}

            printf "%$((${loc_pad_lenght}-${#loc_proc}))s " | tr ' ' - >>${loc_file_name}

            echo " [$loc_status]" >>${loc_file_name}

      else

            echo -n "$loc_proc "

            printf "%$((${loc_pad_lenght}-${#loc_proc}))s " | tr ' ' -

            echo " [$loc_status]"

      fi

}

#----------------

print_variables () {

    file_name=${1}

    {

    echo Timestamp ${currentdate};

    echo Instance ${db};

    echo host_name $(hostname);

##    echo os ${opsys};

    echo oratab ${oratab};

    echo rman_tag ${rman_tag};

    echo rman_log ${rman_log};

    echo bkp_ret_period ${bkp_ret_period};

    echo log_ret_period ${log_ret_period};

    echo script ${script};

    echo scriptpath ${scriptpath};

    echo backup_type ${backup_type};

    echo flag_file ${flag_file};

    echo arch_vol_threshold ${arch_vol_threshold};

    echo arch_log_path ${arch_log_path};

    echo use_arch_per ${use_arch_per};

    echo Email_distribution "${email_distribution_list}";

echo flashVolThreshold ${flashVolThreshold};

echo flashPath_${db}_${currentdate} ${flashPath};

echo useFlashPer ${useFlashPer};

echo dbVersion ${dbVersion};

} | while read proc status; do

             fun_print_message ${pad_lenght} ${proc} ${status} ${file_name}

    done

}


#----------------

function fun_get_arch_log_path {

   ##. ~/.oraset_ss ${db}

   . ${oraSetFile} ${db}

   return_get_arch_log_path=$(

   echo "set head off feed off pages 0 ver off trims on

   select value from v\$parameter where name='log_archive_dest_1';" |sqlplus -s "/ as sysdba")

   # Split by "=" and take the second item of array

   IFS="="

   set -- $return_get_arch_log_path

   array1=( $@ )

   unset IFS

   tmp_var=${array1[1]##*( )} # Trim function

   tmp_var=$(echo $tmp_var | cut -d' ' -f1)

   echo  ${tmp_var%/} #Delete slash in the end of string if exists.

}


#----------------

function funGetDbParam {

   locParamName=${1}

   ##. ~/.oraset_ss ${db}

   . ${oraSetFile} ${db}

   tmpSQL="select value from v\$parameter where name='${locParamName}'"

   returnGetDbParam=$(

   echo "set head off feed off pages 0 ver off trims on

   ${tmpSQL};" |sqlplus -s "/ as sysdba")

   # Split by "=" and take the second item of array

   IFS="="

   set -- $returnGetDbParam

   array1=( $@ )

   unset IFS

   tmp_var=${array1##*( )} # Trim function

   echo  ${tmp_var%/} #Delete slash in the end of string if exists.

}


#-----

#

function funGetSQLReturn {

   local locStrTmp=$1

   ##echo "${locStrTmp}=${locStrTmp}"

   returnFunGetSQLReturn=$(sqlplus -s "/ as sysdba" << ___EOF 2>/dev/null

      set echo off ver off pages 0 trims on head off feed off

      ${locStrTmp};

___EOF

)

} # End function funGetSQLReturn


#----------------

function fun_get_use_arch_per {

  loc_arch_log_path=${1}

  loc_use_arch_per=$(df -h | grep ${loc_arch_log_path})

  loc_use_arch_per=${loc_use_arch_per%${loc_arch_log_path}} #Delete path from the end of string.

  loc_use_arch_per=$(echo ${loc_use_arch_per} | sed 's/^.* //') # Thake the last word.

  echo  ${loc_use_arch_per%\%} #Delete % in the end of string if exists.

}


#----------------

if [ ! $1 ]; then

    usage_exit "No name of instance as an input parameter. Exit" 1

fi


second_param=$(echo $2 | tr '[:lower:]' '[:upper:]')


if [ ! ${second_param} ]; then

    backup_type=DB

elif [ ${second_param} = 'ARC' ]; then

    backup_type=ARC

elif [ ${second_param} = 'DELBACKUPS' ]; then

    backup_type=DELBACKUPS

elif [ ${second_param} = 'DELARC' ]; then

    backup_type=DELARC

else

    backup_type=NO

    usage_exit "The second parameter has a wrong value $2. Exit" 4

fi


#----------------

# Set up oratab

[ -f /etc/oratab ] && oratab=/etc/oratab || oratab=/var/opt/oracle/oratab


#----------------

# Check database name.

db=$(egrep -i ":Y|:N" ${oratab} | grep -v "^\#" | grep -v "\*" | grep "^${1}:" | cut -d":" -f1)

if [ ! ${db} ]; then

    usage_exit "No ${1} instance in ${oratab} oratab. Exit" 3

fi


#----------------

# Check if database is running.

dbAmountProcesses=$(ps -ef | grep ${db} | grep pmon | grep -v grep | wc -l | tr -d ' ')

echo "dbAmountProcesses=${dbAmountProcesses}"

if [ ${dbAmountProcesses} -eq 0 ]; then

    usage_exit "Instance ${1} is not running. Exit" 4

fi

 

. ${oraSetFile} ${db}

funGetSQLReturn "select open_mode from v\$database"

tmpStatusOfDb=${returnFunGetSQLReturn}

echo "tmpStatusOfDb=======${tmpStatusOfDb}"

if [ "${tmpStatusOfDb}" != 'READ WRITE' ]; then

    usage_exit "${1} is not open. Exit" 5

fi


funGetSQLReturn "select substr(version,1,4) from v\$instance"

dbVersion=${returnFunGetSQLReturn//.}

echo "dbVersion=======${dbVersion}"

#----------------

# Set up variables.

currentdate=$(date '+%y%m%d_%H%M%S')

upper_db=`echo ${db} | tr '[:lower:]' '[:upper:]'`


if [  ${backup_type} = 'DB'  ]; then

    rman_tag=${backup_type}_${upper_db}

elif [  ${backup_type} = 'ARC'  ]; then

    rman_tag=${backup_type}_${upper_db}_${currentdate}

fi


name_signature=rman_backup

rman_log=${log_dir}/${name_signature}_${db}_${backup_type}_${currentdate}.log

tmp_error_log=${log_dir}/${name_signature}_${db}_${backup_type}_${currentdate}_errors.log

flag_file=${scriptpath}/${script}_${db}_${backup_type}.flag

db_flag_file=${scriptpath}/${script}_${db}_DB.flag


arch_log_path=$(fun_get_arch_log_path)

use_arch_per=$(fun_get_use_arch_per ${arch_log_path})


flashPath=$(funGetDbParam "db_recovery_file_dest")

useFlashPer=$(fun_get_use_arch_per ${flashPath})


#----------------

# Check and create a flag file.

if [ -f ${flag_file} ]; then

    usage_exit "Flag file exists ${flag_file}. Exit" 6

else

    if [ ${backup_type} = 'ARC' -a -f ${db_flag_file} ]; then

        usage_exit "Cannot run ARC backup during DB backup. Exit" 7

    fi

    #----------------

    # Show variables and create a flag file with them.

    print_variables | tee ${flag_file}    

fi


#----------------

# Set up proper environment.

. ${oraSetFile} ${db}

#----------------

if [  ${backup_type} = 'DB'  ]; then

    >${rman_log}

    if [ "${dbVersion}" -gt "111" ]; then

        ##export compressionType="HIGH"

        export compressionType="LOW"

        export compressionTypeCommand="CONFIGURE COMPRESSION ALGORITHM '${compressionType}';"

    else

        export compressionType=""

        export compressionTypeCommand=""

    fi

    echo "compressionType=${compressionType}" | tee -a ${rman_log}

    print_variables ${rman_log}

    echo | tee -a ${rman_log}

    echo "Begin database backup." | tee -a ${rman_log}


    rman << ____EOF 1>> ${rman_log}

    connect target

    sql "alter database backup controlfile to trace";

    run{

    allocate channel d1 device type disk;

    allocate channel d2 device type disk;

    crosscheck copy;

    crosscheck backup;

    crosscheck archivelog all;

    cROSSCHECK datafilecopy all;

    DELETE NOPROMPT OBSOLETE;

    ${compressionTypeCommand}

    RECOVER COPY OF DATABASE WITH TAG="${rman_tag}" UNTIL TIME 'trunc(SYSDATE)- ${bkp_ret_period}';

    BACKUP AS COMPRESSED BACKUPSET

    INCREMENTAL LEVEL 1

    FOR RECOVER OF COPY WITH TAG="${rman_tag}"

    DATABASE;

    DELETE NOPROMPT OBSOLETE;

    BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

    }

____EOF

    #----------------

    # Clean up log files.

    echo | tee -a ${rman_log}

    log_file_mask="${name_signature}_${db}_*.log"

    fun_print_message ${pad_lenght} "Clean up logs" "${log_dir}/${log_file_mask}" | tee -a ${rman_log}

    find  ${log_dir} -name "${log_file_mask}" -ctime +${log_ret_period} -print -exec rm {} \; | tee -a ${rman_log}

elif [  ${backup_type} = 'ARC'  ]; then

    #----------------

    # Begin archive log backup if threshold is exceeded

    if [ "${use_arch_per}" -gt "${arch_vol_threshold}"  ]; then

        >${rman_log}

        if [ "${dbVersion}" -gt "111" ]; then

            ##export compressionType="MEDIUM"

            export compressionType="LOW"

            export compressionTypeCommand="CONFIGURE COMPRESSION ALGORITHM '${compressionType}';"

        else

            export compressionType=""

            export compressionTypeCommand=""

        fi

        echo "compressionType=${compressionType}" | tee -a ${rman_log}

        print_variables ${rman_log}

        echo | tee -a ${rman_log}

        echo "Begin archive log backup." | tee -a ${rman_log}

        rman << ________EOF 1>> ${rman_log}

        connect target

        sql "alter database backup controlfile to trace";

        crosscheck archivelog all;

cROSSCHECK datafilecopy all;

        run{

        allocate channel d1 device type disk;

        allocate channel d2 device type disk;

        ${compressionTypeCommand}

        BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL TAG="${rman_tag}" DELETE ALL INPUT ;

        }

________EOF

    fi  

elif [  ${backup_type} = 'DELARC'  ]; then

    #----------------

    # Begin archive log backup if threshold is exceeded

    if [ "${use_arch_per}" -gt "${arch_vol_threshold}"  ]; then

        >${rman_log}

        if [ "${dbVersion}" -gt "111" ]; then

            ##export compressionType="MEDIUM"

            export compressionType="LOW"

            export compressionTypeCommand="CONFIGURE COMPRESSION ALGORITHM '${compressionType}';"

        else

            export compressionType=""

            export compressionTypeCommand=""

        fi

        echo "compressionType=${compressionType}" | tee -a ${rman_log}

        print_variables ${rman_log}

        echo | tee -a ${rman_log}

        echo "Begin archive log removing." | tee -a ${rman_log}

        rman << ________EOF 1>> ${rman_log}

        connect target

        sql "alter database backup controlfile to trace";

        crosscheck archivelog all;

CROSSCHECK datafilecopy all;

        run{

        allocate channel d1 device type disk;

        allocate channel d2 device type disk;

        delete noprompt archivelog ALL;

        }

________EOF

    fi  

elif [  ${backup_type} = 'DELBACKUPS'  ]; then

    #----------------

    # Begin deleting backups in flash.

    if [ "${useFlashPer}" -gt "${flashVolThreshold}"  ]; then

        >${rman_log}

        print_variables ${rman_log}

        echo | tee -a ${rman_log}

        echo "Begin deleting backups in flash." | tee -a ${rman_log}

        rman << ________EOF 1>> ${rman_log}

        connect target

        ##sql "alter database backup controlfile to trace";

        crosscheck archivelog all;

        CROSSCHECK BACKUP;

        CROSSCHECK datafilecopy all;

   delete noprompt backup;

________EOF

    fi  

else

    usage_exit "The second parameter has a wrong value ${backup_type}. Exit" 4

fi


#----------------

# Check errors and send email if any.

if [ -f ${rman_log} ]; then

    egrep 'ORA-|RMAN-'  ${rman_log} > ${tmp_error_log}

    rows_amount_errors_in_log=`cat ${tmp_error_log} | wc -l`

    if [ ${rows_amount_errors_in_log} -gt 0 ]; then

        echo ${rows_amount_errors_in_log}

        cat ${tmp_error_log}

        emailTitle="RMAN ${backup_type} ${host_name} ${db} error [${rman_log}] "

        ##cat  "${tmp_error_log}" | mailx -s  "RMAN ${backup_type} ${host_name} ${db} error [${rman_log}] " ${email_distribution_list}

        sendEmail "${emailTitle}" "${emailTitle}" "${tmp_error_log}"

    else

        rm ${tmp_error_log}

    fi

fi

rm ${flag_file}

####### end of script  #######

Comments

Post a Comment

Popular posts from this blog

Opatch apply/lsinventory error: oneoff is corrupted or does not exist

I am applying the quarterly patch for 19c RDBMS, I tried using napply but failed, but somehow it corrupted the inventory though nothing applied. further apply and lsinventory command ran into error like this: $ ./OPatch/opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.21 Copyright (c) 2020, Oracle Corporation.  All rights reserved. Oracle Home       : /u02/app/oracle/19.0.0 Central Inventory : /u01/app/oraInventory    from           : /u02/app/oracle/19.0.0/oraInst.loc OPatch version    : 12.2.0.1.21 OUI version       : 12.2.0.7.0 Log file location : /u02/app/oracle/19.0.0/cfgtoollogs/opatch/opatch2020-09-08_13-35-59PM_1.log Lsinventory Output file location : /u02/app/oracle/19.0.0/cfgtoollogs/opatch/lsinv/lsinventory2020-09-08_13-35-59PM.txt -------------------------------------------------------------------------------- Inventory load failed... OPatch cannot load inventory ...
Post a Comment
Read more

oracle dba_hist_sysmetric_summary

found this blog is helpful to get CPU and IO statistics on oracle database. http://shob-dbadmin.blogspot.ca/2012/12/how-to-find-total-io-of-database.html courtesy to  Shomil Bansal , below are hist writing, not mine. How to find total IO of the database instance Total IO of database instance is sum of the physical reads, physical writes and redo writes. There are several views to find these values. v$sysmetric  - Reports metric values for only the most current time sample 60 secs. v$sysmetric_summary  - Reports metric values for time sample of 1 hour. v$sysmetric_history  - Reports metric values every 60 sec from the time instance is up. Better way to analyse IO using this view to take deltas between two time periods. dba_hist_sysmetric_history  - All the above views are refreshed when the instance is restarted. This view, part of AWR, stores the historical stats. I have used this view for my report. Query: ====== set lines 350...
Post a Comment
Read more

ORA_RMAN_SGA_TARGET

assume that we lost all the files of oracle database but we do have rman backup, when trying to bring up a dummy database before restore start, I get this error. RMAN> startup nomount force; WARNING: cannot translate ORA_RMAN_SGA_TARGET value startup failed: ORA-01078: failure in processing system parameters ORA-01565: error in identifying file '+DATA/PROD/spfilePROD.ora' ORA-17503: ksfdopn:2 Failed to open file +DATA/PROD/spfilePROD.ora ORA-15056: additional error message ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +DATA/prod/spfileprod.ora ORA-17503: ksfdopn:2 Failed to open file +DATA/prod/spfileprod.ora ORA-15173: entry 'spfileprod.ora' does not exist in directory 'prod' ORA-06512: at line 4 starting Oracle instance without parameter file for retrival of spfile RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =================================...
2 comments
Read more