Summer Holidays

I'm going on holiday, see you in four weeks time.

Byeeee

A complete script to look for ORA- errors in the Oracle alert log file on Linux Database Server

Title :

A complete script to look for ORA- errors in  the Oracle alert log file on Linux Database Server.

Comment: 

Every now and again ORA- errors crop up and it’s really important to take care them  as soon as possible.

This is the reason why I decided to get a shell script that does it for me automatically.

Following you’ll find the complete script file. It’s only necessary to copy and paste it onto your server .

You can execute it as you wish using the Linux cron daemon, can’t you ?

It is necessary to pass the absolute path of the Oracle alert log file and what’s more the Oracle instance name for example :

./alertlog.sh  /u01/app/oracle/diag/rdbms/dev/trace/alert_DEV.log DEV

NB : As usual it’s always better to look at it on a test environment and then enable on a production environment.  

File : alertlog.sh

#!/bin/bash

what="${1}"

format="+%a %b %d %H:%M"

pattern="ORA-"

logfile="${what}"

tempfile=/home/oracle/dbascript/tempfile/alertlog.tmp.${2}

alertlogtail=/home/oracle/dbascript/tempfile/alertlog.tail.${2}

if [ -s "${logfile}" ] ; then

    diff "${logfile}" "${alertlogtail}" > "${tempfile}"

    grep -n -a "${pattern}" "${tempfile}" > "${tempfile}".out

    if [ -s "${tempfile}".out ] ; then

        what="$(basename $0 | cut -d'.' -f1 )"

        rm "${tempfile}".page.out

        grep -n -a "${pattern}" "${tempfile}" > "${tempfile}".page.out

        if [ -s "${tempfile}".page.out ] ; then

           people="dba@world.it"

        else

           people=" dba@world.it "

        fi

        echo $(date) 'ALERTLOG problems e-mailed to: ' "${people}" >> /home/oracle/dbascript/tempfile/${2}.txt

        mail -s "ALERT: ${2} $(date)" "${people}" < "${tempfile}".out

    fi

#save last run time

    cp "${logfile}" "${alertlogtail}"

fi

See you in my next blog

Byeeee

A COMPLETE SCRIPT TO BACKUP AN ORACLE DATABASE USING RMAN AND IMAGE COPY

Title :

A COMPLETE SCRIPT TO BACKUP AN ORACLE DATABASE USING RMAN AND IMAGE COPY

Comment: 

Every now and again there is much more work than usual and to make things worse when everything is of vital importance, it’s really difficult not to get involved totally.

This is the reason why it’s taken me ages to post a new blog, anyway let's get started, shall we ?

I’ve been writing lots of shell script files and what’s more RMAN SQL commands lately and since they are slowly fading away I can catch two birds with one stone and  save it on a blog.

Making up a complete backup procedure is a really important stuff and what’s more it’s important to be able to restore it. Every now and again problems crop up and it’s necessary to cope with them.

The procedure is made up of :

• Executable Linux  files : rman_sh_command.sh

• SQL Rman command file : rman_sql_command.sql

• Template Backup Error output : template_error_rman.log

NB 1: It’s only required to copy and paste them on your server.

NB2: As usual it’s always better to look at it on a test environment and then enable on a  

       production environment.  

 

Following you’ll find the complete rman_sh_command.sh file:

#!/bin/bash

# Oracle Settings

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4.0/dbhome_1

export PATH=$ORACLE_HOME/bin:/sbin:/usr/sbin:$ORACLE_HOME/sysman/admin/emdrep/bin:$ORACLE_HOME/OPatch:$ORA_CRS_HOME/bin:$PATH

export NOW=$(date +'%Y%m%d')

export LOG_FILE=/home/oracle/dbascript/backup_script/log/backup_rman.log

export SQL_FILE=/home/oracle/dbascript/backup_script/extra_file/rman_sql_command.sql

export ORACLE_SID=DEV1

export RECIPIENTS=dba@world.com

export EMAIL_SUBJECT="RMAN BACKUP DEV DB : "

export BCK_DIR=/orafra/DEV

export BACKUP_OK="OK BACKUP RMAN - "

export BACKUP_KO="KO BACKUP RMAN - "

export DB="ORACLE - "

export ENVIRONMENT="TEST DEV"

export ERROR_LOG_FILE=/home/oracle/dbascript/backup_script/extra_file/template_error_rman.log

echo "Get Started RMAN Backup Istance :" ${ORACLE_SID}  > ${LOG_FILE}

echo " " >> ${LOG_FILE}

echo "Time : " ${NOW} >> ${LOG_FILE}

echo " " >> ${LOG_FILE}

echo "Backup Directory : ${BCK_DIR}" >> ${LOG_FILE}

echo " " >> ${LOG_FILE}

echo " " >> ${LOG_FILE}

/u01/app/oracle/product/11.2.0.4.0/dbhome_1/bin/rman @${SQL_FILE} > ${LOG_FILE} 2>&1

#Check Backup Result

if [ $? -eq 0 ]; then

        mail -s "${BACKUP_OK} ${DB} ${ENVIRONMENT}" ${RECIPIENTS} < ${LOG_FILE}

else

        mail -s "${BACKUP_KO} ${DB} ${ENVIRONMENT}" ${RECIPIENTS} < ${ERROR_LOG_FILE}

fi

Following you’ll find the complete  rman_sql_command.sql file:

 connect target /

configure controlfile autobackup on;
CONFIGURE RETENTION POLICY TO NONE;
###Level 1 Backup and Recovery Image Copy and Autobackup ControlFile
list backup summary;
run {
BACKUP device type disk INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'DEV_IMAGE' DATABASE;
RECOVER COPY OF DATABASE WITH TAG 'DEV_IMAGE';
}

list copy of database;
###Delete AutoBackup ControlFile
list backup of controlfile completed before 'SYSDATE-3';
delete noprompt backup of controlfile completed before 'SYSDATE-3';
###Delete Level 1 Backup and Autobackup ControlFile
list backup of database completed before 'SYSDATE-3';
delete noprompt backup of database completed before 'SYSDATE-3';
###Backup archivelog
backup archivelog all not backed up 1 times;
###Delete archivelogs
list archivelog all;
delete noprompt archivelog until time 'sysdate - 3' backed up 1 times to device type disk;
###Delete backup of  archivelog
list backup of archivelog all;
delete noprompt backup of archivelog until time 'sysdate -6';
###CrossCheck Backup
crosscheck copy of database;
crosscheck backup of database;
crosscheck copy;
list backup summary;
list expired copy of database;
list expired backup of database;
list expired copy;
list archivelog all;
list backup of archivelog all;
exit;

Following you’ll find the complete  template_error_rman.log file:

#######################################################

###############            CAUTION         ######################

#########   ERROR ON BACKUP EXECUTION -  RMAN TEST DEV #######

#######################################################

#######################################################

See you in my next blog.

Byeeee