Mysql error-log monitoring using Shell script with mail alert

#!/bin/bash
SERNAME=xyz.kmi.com
SERPUBIP=192.168.72.50
SERVER=`hostname`
WEEKDAY=`date '+%w%H%M'`
DATE_VAR=`date '+%Y_%m_%d'`

Error_log_loc=/var/log
egrep 'Error' $Error_log_loc/mysqld.log |sort -u > $Error_log_loc/mysqld_ALERTLOG.txt
cat $Error_log_loc/mysqld.log >> $Error_log_loc/mysqld_archived.log

cat /dev/null > $Error_log_loc/mysqld.log

if [ -s "$Error_log_loc/mysqld.log" ] ; then
cat $Error_log_loc/mysqld_ALERTLOG.txt | mail -s "URGENT -ERROR in Oracle Alert Log File for $SERNAME ($SERPUBIP) at `date` " ssdas@gmail.com
fi

# Weekly alert log datestamp and compress (Sunday 00:15)

sysdate=`date | awk '{ print $3}'`
if [[ $sysdate -eq 4 || $sysdate -eq 11 || $sysdate -eq 18 || $sysdate -eq 25 ]] ; then
mv $Error_log_loc/mysqld_archived.log $Error_log_loc/mysqld_archived_ALERTLOG_${DATE_VAR}.log
gzip $Error_log_loc/mysqld_archived_ALERTLOG_${DATE_VAR}.log
fi
exit 0

Monitoring percona Error-log using shell script

vi /backups/alertlog_monotor.sh


#!/bin/bash
SERNAME=srv1.xyz.com
SERPUBIP=192.168.72.50
SERVER=`hostname`                      #### Sets the server name for the email
WEEKDAY=`date '+%w%H%M'`               #### Sets the number value of the day of the week
DATE_VAR=`date '+%Y_%m_%d'`

Error_log_loc=/data/mysql
egrep 'Error' $Error_log_loc/$SERNAME.err |sort -u >  $Error_log_loc/$SERNAME_ALERTLOG.txt
cat $Error_log_loc/$SERNAME.err >> $Error_log_loc/$SERNAME_Archived_ALERTLOG.txt

cat /dev/null > $Error_log_loc/$SERNAME.err

if [ -s "$Error_log_loc/$SERNAME_ALERTLOG.txt" ] ; then
cat $Error_log_loc/$SERNAME_ALERTLOG.txt | mail -s "URGENT -ERROR in Alert Log File for $SERNAME ($SERPUBIP) at `date` " ssdas@24x7-itsupport.com
fi

# Weekly alert log datestamp and compress (Sunday 00:15)

if [[ $WEEKDAY -eq 00015 ]]; then
        mv $Error_log_loc/$SERNAME_Archived_ALERTLOG.txt $Error_log_loc/$SERNAME_Archived_ALERTLOG_${DATE_VAR}.log
        gzip $Error_log_loc/$SERNAME_Archived_ALERTLOG_${DATE_VAR}.log
fi

exit 0

:wq (save & exit)

Now Schedule the above script in crontab for every 15mins.

crontab -l

*/15 * * * * /backups/alertlog_monotor.sh

Oracle Alert log monitoring using shell script

Vi /backups/alertlog_monotor.sh

#!/bin/bash
export ORACLE_SID=kmioradb
export ORAENV_ASK=NO
. /home/oracle/.bash_profile
SERNAME=oradb.kminfosystems.com
SERPUBIP=192.168.72.50
SERVER=`hostname`                      #### Sets the server name for the email
WEEKDAY=`date '+%w%H%M'`               #### Sets the number value of the day of the week
DATE_VAR=`date '+%Y_%m_%d'`

Alert_log_loc=/u01/app/oracle/diag/rdbms/kmioradb/kmioradb/trace/
# Check for the existence of ORA- in the alert log and email/page on error
egrep 'ORA-|error|TNS' $Alert_log_loc/alert_$ORACLE_SID.log |sort -u >  $Alert_log_loc/$ORACLE_SID_OUT_ALERTLOG.txt  #### Output file with ORA- errors
cat $Alert_log_loc/alert_$ORACLE_SID.log >> $Alert_log_loc/archived_alert_$ORACLE_SID.log
cat /dev/null >  $Alert_log_loc/alert_$ORACLE_SID.log

if [ -s "$Alert_log_loc/$ORACLE_SID_OUT_ALERTLOG.txt" ] ; then
cat $Alert_log_loc/$ORACLE_SID_OUT_ALERTLOG.txt | mail -s "URGENT -ERROR in Oracle Alert Log File for $SERNAME ($SERPUBIP) at `date` " soumya@gmail.com
fi

# Weekly alert log datestamp and compress (Sunday 00:15)

if [[ $WEEKDAY -eq 00015 ]]; then
        mv $Alert_log_loc/archived_alert_${ORACLE_SID}.log $Alert_log_loc/archived_alert_${ORACLE_SID}_${DATE_VAR}.log
        gzip $Alert_log_loc/archived_alert_${ORACLE_SID}_${DATE_VAR}.log
fi

exit 0

:wq (save & exit)


Now Schedule the above script in crontab for every 15mins.

crontab -l

*/15 * * * * /backups/alertlog_monotor.sh

Mysql up & down status check script with email alert

create the shell-script which will monitor the up/down status of mysql service:-

[oracle@server1 ~]$ vi /home/script/alert.sh

#!/bin/bash
servername="server1.example.com"
publicip=192.168.72.5
servicename="mysql"

for i in $servicename
do
/sbin/service $i status
if [ $? -ne 0 ]; then
/sbin/service $i status | mailx -s "Check Status of $i in $servername ($publicip) at `date`"  yourmailid@gmail.com
fi
done

:wq (save & exit)

We schedule the script on crontab which will check in every 5 mins.
[oracle@server1 ~]$ crontab -e
*/5 * * * * /home/script/alert.sh > /dev/null

P.S.Make sure mailx rpm is installed and any  mailing service such as sendmail/postfix is running on the server.
To  install sendmail:-
#yum -y install sendmail

To install mailx:-
#yum -y install mailx

Monitoring Temporary tablespace usage using shell script

The following script will report if temporary tablespace  hits 80% of its usage.

From oracle user:
[oracle@server1 ~]$ vi /home/oracle/check_temp_tbs.sql

set head off
set wrap off
select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks
from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks
from dba_temp_files where tablespace_name='TEMP') f;
exit;


Now lets create the shell-script which will monitor the space usage of temp tablespaces:-
From oracle user

[oracle@server1 ~]$ vi /home/oracle/alert_temp.sh
#!/bin/bash
export ORACLE_SID=prim
sqlplus -S / as sysdba @/home/oracle/check_temp_tbs.sql > /home/oracle/temptbs.txt
space=`cat /home/oracle/temptbs.txt`
if [ $space -gt 80 ]; then
mail -s echo "Attention!! Low space in  Temporary Tablespaces in $ORACLE_SID instance!" yourmailid@gmail.com
fi

:wq(save & exit)

We schedule the script which will check in every 5 mins.
[oracle@server1 ~]$ crontab -e
*/5 * * * * /home/oracle/alert_temp.sh > /dev/null


P.S.Make sure mailx rpm is installed and any  mailing service such as sendmail/postfix is running on the server.
To  install sendmail:-
#yum -y install sendmail

To install mailx:-
#yum -y install mailx

Monitoring Tablespace usage using shell script

The following script will report if any tablespace (except temporary tablespace ) hits 85% of its usage.


From oracle user:
[oracle@server1 ~]$ vi /home/oracle/checktbs.sql

select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from
(
select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 15
order by pct_free;
exit;

:wq(save & exit)


Now lets create the shell-script which will monitor the space usage of tablespaces:-
From oracle user
[oracle@server1 ~]$ vi /home/oracle/alert.sh
#!/bin/bash
export ORACLE_SID=prim
sqlplus -S / as sysdba @/home/oracle/checktbs.sql > /home/oracle/test.txt
file1=cat /home/oracle/test.txt
space=`cat /home/oracle/test.txt | wc -l`
if [ $space -gt 3 ]; then
$file1 | mail -s echo "Attention!! Low space Certain Tablespaces in $ORACLE_SID instance!" yourmailid@gmail.com
fi

:wq(save & exit)

We schedule the script which will check in every 5 mins.
[oracle@server1 ~]$ crontab -e
*/5 * * * * /home/oracle/alert.sh > /dev/null


P.S.Make sure mailx rpm is installed and any  mailing service such as sendmail/postfix is running on the server.
To  install sendmail:-
#yum -y install sendmail

To install mailx:-
#yum -y install mailx

How to take RMAN INCREMENTAL LEVEL 1 Cumulative Backup using Shell script

Create the following backup path:-
[oracle@server1 ~]$ mkdir -p /u01/backups/rman_backup/inc_level_1_cum
[oracle@server1 ~]$ mkdir -p /u01/backups/scripts/


Make sure database is in archivelog mode , if its not put it on archivelog mode.

SQL> sqlplus / as sysdba
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;


[oracle@server1 ~]$  vi /u01/backups/scripts/IncLevel_1_Cum_rman_backup.sh

#!/bin/bash
export PS1="`/bin/hostname s`> " 
export ORACLE_SID=prim
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
export path1=/u01/backups/rman_backup/inc_level_1_cum
date1=`date +%d%m%y_%H%M%S`
mkdir $path1/$date1
chown oracle:oinstall -R $path1/$date1
su - oracle -c "$ORACLE_HOME/bin/rman target / " <<eof
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
report obsolete;
delete Noprompt obsolete;
configure CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$path1/$date1/control_%d_%F';
backup incremental level 1 cumulative database FORMAT '$path1/$date1/inc_level_1_cum_%d_%T_%t_%s_%p'; 
eof



[oracle@server1 ~]$ chmod 755 /u01/backups/scripts/IncLevel_1_Cum_rman_backup.sh

Now schedule the script using crontab from oracle user:-
The  script will run every 6 hours

[oracle@server1 ~]$ crontab -e

0 */6 * * * /u01/backups/scripts/IncLevel_1_Cum_rman_backup.sh > /dev/null

How to take RMAN INCREMENTAL LEVEL 1 Backup using Shell script

Create the following backup path:-
[oracle@server1 ~]$ mkdir -p /u01/backups/rman_backup/inc_level_1
[oracle@server1 ~]$ mkdir -p /u01/backups/scripts/


Make sure database is in archivelog mode , if its not put it on archivelog mode.

SQL> sqlplus / as sysdba
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;


[oracle@server1 ~]$ vi  /u01/backups/scripts/IncLevel_1_rman_backup.sh

#!/bin/bash
export PS1="`/bin/hostname s`> " 
export ORACLE_SID=prim
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
export path1=/u01/backups/rman_backup/inc_level_1
date1=`date +%d%m%y_%H%M%S`
mkdir $path1/$date1
chown oracle:oinstall -R $path1/$date1
$ORACLE_HOME/bin/rman target / <<eof
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
report obsolete;
delete Noprompt obsolete;
configure CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$path1/$date1/control_%d_%F';
backup incremental level 1 database FORMAT '$path1/$date1/inc_level_1_%d_%T_%t_%s_%p'; 

eof

cd $path1
file1=`ls -ltrh | tail -1 | awk '{print $9}'`
tar -zcvf $file1.tar.gz $file1


:wq (save & exit)


Now schedule the script using crontab from oracle user:-
#The  script will run every 30mins

[oracle@server1 ~]$ chmod 755 /u01/backups/scripts/IncLevel_1_rman_backup.sh
[oracle@server1 ~]$ crontab -e
15,45 * * * * /u01/backups/scripts/IncLevel_1_rman_backup.sh > /dev/null

How to take RMAN Full backup using Shell script

Create the following backup path:-
[oracle@server1 ~]$ mkdir -p /u01/backups/rman_backup/full_backup
[oracle@server1 ~]$ mkdir -p /u01/backups/scripts/


Make sure database is in archivelog mode , if its not put it on archivelog mode.

SQL> sqlplus / as sysdba
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;


[oracle@server1 ~]$ vi  /u01/backups/scripts/full_backup.sh

#!/bin/bash
export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
export path1=/u01/backups/rman_backup/full_backup
date1=`date +%d%m%y_%H%M%S`
mkdir $path1/$date1
chown oracle:oinstall -R $path1/$date1
$ORACLE_HOME/bin/rman target / <<eof
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
report obsolete;
delete Noprompt obsolete;
configure CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$path1/$date1/control_%d_%F';
run
{
backup incremental level 0 database FORMAT '$path1/$date1/full_%d_%T_%t_%s_%p';
backup archivelog all FORMAT '$path1/$date1/archive_%d_%T_%t_%s_%p' ;
}
eof

cd $path1
file1=`ls -ltrh | tail -1 | awk '{print $9}'`
tar -zcvf $file1.tar.gz $file1


:wq (save & exit)


Now schedule the script using crontab from oracle user:-
#The  script will run everynight at 12 A.M


[oracle@server1 ~]$ crontab -e
0 0 * * * /u01/backups/scripts/full_backup.sh > /dev/null

Monitoring Data Guard Log Shipping using Shell Script with Mail alert

Version:- Oracle 11g
All the below scripts are stored and will run from oracle user & from Primary Database.

[oracle@server1 ~]$ vi /home/oracle/dg.sql
Set linesize 140
set head off
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,
LOG_ARCHIVED-LOG_APPLIED LOG_GAP
FROM
(SELECT NAME DB_NAME FROM V$DATABASE),
(SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME),(INSTR(HOST_NAME,'.')-1))))) HOSTNAME FROM V$INSTANCE
),
(SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' ),
(SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' ),
(SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES');
exit;

:wq

Now lets create the shell-script which will monitor the log gap in between of primary and standby database:-
From oracle user.
[oracle@server1 ~]$ vi /home/oracle/dgmonitor.sh
sqlplus -S / as sysdba @/home/oracle/dg.sql > /home/oracle/loggap.txt
loggap=`cat /home/oracle/loggap.txt | awk '{print $6}'`
if [ $loggap -ne 0 ]; then
mail -s "Attention!! There is a loggap between Primary and Standby Database! " yourmailid@gmail.com
fi
exit 0

:wq

[oracle@server1 ~]$ chmod 777 dgmonitor.sh

Now schedule it on crontab to execute the script for every 15mins :-
[oracle@server1 ~]$  crontab -e
*/15 * * * * /home/oracle/dgmonitor.sh > /dev/null

Shell script for Webfile Backup for webserver

#  mkdir /backups/web_backup/

#  vi /backups/webbackup.sh 
#!/bin/bash

export path1=/backups/web_backups
date1=`date +%y%m%d_%H%M%S`

/usr/bin/find /backups/web_backups/* -type d -mtime +3 -exec rm -r {} \; 2> /dev/null

mkdir $path1/$date1

cp -r /var/www/html $path1/

cd $path1/html

for i in */; do /bin/tar -zcvf "$path1/$date1/${i%/}.tar.gz" "$i"; done

if [ $? -eq 0 ] ; then
cd
rm -r /backups/web_backups/html
fi
done

:wq (save & exit)

Now schedule the script inside crontab:-
#The  script will run every night at 12 A.M
#crontab -e
0 0 * * * /backups/webbackup.sh > /dev/null


Please share your ideas and opinions about this topic. 
If you like this post, then please share with others.

Please subscribe on email for every updates on mail.

Shell script for Mysql Database Backup

#  mkdir /backups/db_backup/

# vi /backups/mysqlbackup.sh 

#!/bin/bash
export path1=/backups/db_backup
date1=`date +%y%m%d_%H%M%S`
/usr/bin/find /backups/db_backup/* -type d -mtime +30 -exec rm -r {} \; 2> /dev/null
cd $path1/
mkdir $date1
USER="root"
PASSWORD="redhat123"
OUTPUTDIR="$path1/$date1"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
HOST="localhost"
databases=`$MYSQL --user=$USER --password=$PASSWORD --host=$HOST \
-e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
echo "` for db in $databases; do
   echo $db

       if [ "$db" = "performance_schema" ] ; then
       $MYSQLDUMP --force --opt --single-transaction --lock-tables=false --skip-events --user=$USER --password=$PASSWORD --host=$HOST --routines \
        --databases $db | gzip > "$OUTPUTDIR/$db.gz"
        else

$MYSQLDUMP --force --opt --single-transaction --lock-tables=false --events --user=$USER --password=$PASSWORD --host=$HOST --routines \
   --databases $db | gzip > "$OUTPUTDIR/$db.gz"
fi
done `"

:wq



Now schedule the script inside crontab:-
#The  script will run every night at 12 A.M
#crontab -e
0 0 * * * /backups/mysqlbackup.sh > /dev/null


Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

AWS AMI Backup shell script

Prerequisites:
=============

Step: 1. Install Java :

# yum -y install java-1.7.0-openjdk
# export JAVA_HOME=/usr/lib/jvm/jre-1.7.0-openjdk.x86_64
# java -version

Step: 2. Download & Unzip Amazon EC2 CLI Tools :

# yum -y install wget zip unzip
# cd /tmp
# wget http://s3.amazonaws.com/ec2-downloads/ec2-api-tools.zip
# unzip ec2-api-tools.zip

Step: 3. Install the Amazon EC2 CLI Tools :

# mkdir /usr/local/ec2
# mv ec2-api-tools-1.7.5.0 /usr/local/ec2/apitools/

Step: 4. Set variables :

# export EC2_HOME=/usr/local/ec2/apitools
# export PATH=$PATH:$EC2_HOME/bin

Step: 5. Add variables to Startup Script :

# cd etc/profile.d/
# vi aws.sh

export JAVA_HOME=/usr/lib/jvm/jre-1.7.0-openjdk.x86_64
export EC2_HOME=/usr/local/ec2/apitools
export PATH=$PATH:$EC2_HOME/bin

-- Save & Quit (:wq)

# chmod +x aws.sh
# sounce aws.sh

Step: 6. Logged in into AWS Web Panel.

Step: 7. Go to IAM Panel.

-- Click on Users (tab)
-- Create New Users.
-- Give User Name.
-- Click on Create.
-- Download the Credential.
-- Close.
-- Click on Newly Created User.
-- Permission (tab)
-- Click on Attach Policy.
-- Search (AmazonEC2FullAccess) & Select it.
-- Attach Policy.

Access Key ID:  Provide your Access Key Id
Secret Access Key:  Provide your Secret Access Key

Step: 8. Finally Create AMI Auto Backup Script :

# vi /backups/scripts/aws-ami-backup.sh

#!/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/ec2/apitools/bin

# Please use env | grep EC2_HOME to find out your system's setting
EC2_HOME=/usr/local/ec2/apitools

# Please use env | grep JAVA_HOME to find out your system's setting
JAVA_HOME=/usr/lib/jvm/jre-1.7.0-openjdk.x86_64
date=`date +%d-%m-%Y_%H-%M-%S`
export EC2_HOME JAVA_HOME
export AWS_ACCESS_KEY=Provide your Access Key Id
export AWS_SECRET_KEY=Provide your Secret Access Key

# Regions reference: http://docs.aws.amazon.com/general/latest/gr/rande.html
region="ap-southeast-1"

# You can find your instance ID at AWS Manage Console
instanceID="i-c706e305"

# Your prefer AMI Name prefix
amiNamePrefix="SOUMYA-SOUMYATEST-AMI_$date"

# Your prefer AMI Description

amiDescription="Daily AMI backup"

# If you want to keep 7 days AMI backups, please set routine true otherwise set it false
routine=true

if [ $routine = true ]; then
    # Setup AMI Name
    amiName=$amiNamePrefix

    # Get AMI ID
    amiIDs=$(ec2-describe-images --region $region | grep 'ami-[a-z0-9]' | grep "$amiName" |cut -f 2)

    # Get Snapshot ID
    if [[ ! -z $amiIDs ]]; then
        snapshotIDs=$(ec2-describe-snapshots --region $region | grep $amiIDs | cut -f 2)
    fi
else
    # Setup AMI Name
    amiName=$amiNamePrefix

    # Get AMI ID
    amiIDs=$(ec2-describe-images --region $region | grep 'ami-[a-z0-9]' | cut -f 2)

    # Get Snapshot ID
    if [[ ! -z $amiIDs ]]; then
        snapshotIDs=$(ec2-describe-snapshots --region $region | cut -f 2)
    fi
fi

if [[ ! -z $amiIDs ]]; then
    # Deregister AMI
    for amiID in $amiIDs
    do
        ec2-deregister --region $region $amiID
    done

    # Delete snapshot
    for snapshotID in $snapshotIDs
    do
        ec2-delete-snapshot --region $region $snapshotID
    done
fi

# Create AMI
ec2-create-image $instanceID --region $region --name "$amiName" -d "$amiDescription" --no-reboot > /tmp/AMIBackup.txt

# Name Tag
amiid=`cat /tmp/AMIBackup.txt | cut -f2`
ec2addtag $amiid --tag Name=$amiName --region $region

-- Save & Quit (:wq)

# chmod 755 /backups/scripts/aws-ami-backup.sh

Step: 9. Schedule in Crontab :

# crontab -e

0 0 * * * /backups/scripts/aws-ami-backup.sh

-- Save & Quit (:wq)

Step: 10. Retstart the Cron Service :

# service crond restart

Done...!!!




Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

Shell script for redirecting output of rman command for oracle 11g

vi /backups/scripts/rmanoutput.sh

#!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=prim
export PATH=$PATH:$ORACLE_HOME/bin
export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
export DATE=$(date +%Y-%m-%d)

rman target sys/sys@prim msglog /u01/rman_full_backup_db_online_TEST1_${DATE}.log <<EOF

backup database plus archivelog;

exit;

EOF

:wq

P.S Output for currently executing RMAN jobs is also stored in the V$RMAN_OUTPUT view,
which reads only from memory (that is, the information is not stored in the control file).
The V$RMAN_STATUS view contains metadata about jobs in progress as well as completed jobs.
The metadata for completed jobs is stored in the control file.

Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

bash_profile setting for different oracle versions installed in a single server.

vi /home/oracle/.bash_profile

umask 022
export ORACLE_BASE=/u01/app/oracle
export TNS_ADMIN=$ORACLE_BASE/product/11.2.0/db_1/network/admin

10g()
{       export ORACLE_SID=prim10g
        export ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
        export LD_LIBRARY_PATH=$ORACLE_HOME/lib
        export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
        export PS1='[\u@\h\W]\$ '
}

11g()
{       export ORACLE_SID=prim11g
        export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
        export LD_LIBRARY_PATH=$ORACLE_HOME/lib
        export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
        export PS1='[\u@\h\W]\$ '
}

12c()
{       export ORACLE_SID=prim12c
        export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1
        export LD_LIBRARY_PATH=$ORACLE_HOME/lib
        export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
        export PS1='[\u@\h\W]\$ '
}

--save and quit (:wq)

[oracle@server1 ~]$ . .bash_profile

Simply type "10g" to set 10g environment, "11g" for 12c, and so on.

MongoDB backup using shell script for all databases

vi /backups/mongodb_backup.sh

#!/bin/bash
date1=`date +%d%m%Y_%H%M%S`
export path1=/backups/mongodb_backup
OUTPUTDIR="$path1/$date1"
/usr/bin/find /backups/mongodb_backup/* -type d -mtime +30 -exec rm -r {} \; 2> /dev/null
mongodump -o $path1/$date1
/bin/tar -zcvf  /backups/mongodb_backup/backup_$date1.tar.gz $path1/$date1
rm -rf $path1/$date1
exit

#Schedule in crontab:-
 crontab -e

0 0 * * * /backups/mongodb_backup.sh > /dev/null

Postgresql backup shell script for all databases in linux

vi /backups/postgre_backup.sh

#!/bin/bash
date1=`date +%d%m%Y_%H%M%S`
export path1=/backups/postgre_backup
OUTPUTDIR="$path1/$date1"
/usr/bin/find /backups/postgre_backup/* -type d -mtime +32 -exec rm -r {} \; 2> /dev/null
su postgres -c 'pg_dumpall' > "$OUTPUTDIR.sql"
/bin/tar -zcvf /backups/postgre_backup/backup_$date1.tar.gz $OUTPUTDIR.sql
rm $OUTPUTDIR.sql
exit

:wq

Schedule in crontab:-
# crontab -e

0 0 * * * /backups/postgre_backup.sh > /dev/null

Alert mail script for checking oracle database up/down status

Step 1. create the shell script for checking dbstatus

vi /home/oracle/dbcheck.sh

#!/bin/bash
hostname=server1.soumya.com
oracle_sid=prim
export instance_name=ora_pmon_prim
status=`ps -ef |grep pmon | head -1 | awk '{print $8}'`
if [ "$status" == "$instance_name" ]; then
echo "DB IS UP"
else
echo "DB down"
mail -s "Attention!! $oracle_sid Database is Down at $hostname!! " yourmailid@gmail.com
fi
exit 0

:wq

Step 2. Schedule it for checkup in every 5 minutes
crontab -e
*/5 * * * * /home/oracle/dbcheck.sh > /dev/null

p.s.Make sure mailx rpm is installed and sendmail or other mail service is running on server.

Shell script for auto start of oracle and listener at boot time

Step 1. Edit /etc/oratab file.
# vi /etc/oratab
prim:/u01/app/oracle/product/11.2.0/db_1:Y

:wq

Step 2. Create a file called "/etc/init.d/dbora" as the root user

vi /etc/init.d/dbora

#!/bin/bash
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
ORA_HOME=/u01/app/oracle/product/11.2.0/db_1
ORA_OWNER=oracle
if [ ! -f $ORA_HOME/bin/dbstart ]
then
    echo "Oracle startup: cannot start"
    exit
fi
case "$1" in
    'start')
        su $ORA_OWNER -c $ORA_HOME/bin/dbstart &
        su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start" &
        touch /var/lock/subsys/dbora
        ;;
    'stop')
        su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
        su $ORA_OWNER -c $ORA_HOME/bin/dbshut
        rm -f /var/lock/subsys/dbora
        ;;
esac

:wq

Step 3. Give dbora file proper permission .
chmod 750 /etc/init.d/dbora
chkconfig --add dbora

Monitoring Flash Recovery Area space using shell script

This script will check the flash recovery area and will shoot a mail if the space is over 80% 

filled up.

From oracle user:

[root@server1 ~]# vi /home/oracle/flashback.sql

col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999

col round(space_used/1048576) heading "Space Used (MB)" format 99999

col round((space_used/space_limit)*100) heading " % used "  format 99999

col name format a30

set head off

select name, round(space_limit/1048576),round(space_used/1048576),round ((space_used/space_limit)*100) as "% used"

from  v$RECOVERY_FILE_DEST;

exit;

:wq

Now lets create the shell-script which will monitor the space usage of flash recovery area:-

From root user

[root@server1 ~]# vi /root/spacecheck.sh

su - oracle -c "sqlplus -S / as sysdba @/home/oracle/flashback.sql" > /home/oracle/test.txt

space=`cat /home/oracle/test.txt | awk '{print $4}'`

if

[ $space -gt 80 ]; then

mail -s "Attention!! Low space in Flash recovery area! " yourmailid@gmail.com

fi

exit 0

:wq

We schedule the script which will check in every 5 mins.

[root@server1 ~]crontab -e

*/5 * * * * /root/spacecheck.sh > /dev/null