Flush the SHARED POOL
-----------------------------------------------
#set -ux
#This script is used for flushing the shared pool
SCRIPT_DIR=/stage/scripts/otmprod
export SCRIPT_DIR
EMAIL_TO=<your Mail Group>
. $SCRIPT_DIR/.sysvars --- <your own directory path>
. /home/oracle/OTMPROD.env
LOGFILE=<your log location>flush_shared_pool.log
sqlplus -s system/${SYSTEMPWD}@$ORACLE_SID <<EOF 2>&1
spool ${LOGFILE}
set echo off
set pagesize 100
set linesize 100
set copytypecheck on
set long 4000
set trimspool on
set pagesize 100
set heading on
set pages 0
set termout off
alter system flush shared_pool;
spool off;
exit;
EOF
REV_STS=$?
if [ $REV_STS -gt 0 ]; then
echo " Previous statement is failed check the log file for DB connection " >$LOGFILE
fi
mailx -s "Flushed the shared pool in $ORACLE_SID at `date`" ${EMAIL_FROM} ${EMAIL_TO} < ${LOGFILE}
=======================================================================
TABLE SPACE Report
------------------------------------
col TABLESPACE_NAME for a12
SELECT F.TABLESPACE_NAME,
TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",
TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)",
TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE
FROM (
SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V$PARAMETER
WHERE NAME = 'db_block_size')/1024)
) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 8;
=====================================================================
Monitor Alert Log
----------------------------
-----------------------------------------------
#set -ux
#This script is used for flushing the shared pool
SCRIPT_DIR=/stage/scripts/otmprod
export SCRIPT_DIR
EMAIL_TO=<your Mail Group>
. $SCRIPT_DIR/.sysvars --- <your own directory path>
. /home/oracle/OTMPROD.env
LOGFILE=<your log location>flush_shared_pool.log
sqlplus -s system/${SYSTEMPWD}@$ORACLE_SID <<EOF 2>&1
spool ${LOGFILE}
set echo off
set pagesize 100
set linesize 100
set copytypecheck on
set long 4000
set trimspool on
set pagesize 100
set heading on
set pages 0
set termout off
alter system flush shared_pool;
spool off;
exit;
EOF
REV_STS=$?
if [ $REV_STS -gt 0 ]; then
echo " Previous statement is failed check the log file for DB connection " >$LOGFILE
fi
mailx -s "Flushed the shared pool in $ORACLE_SID at `date`" ${EMAIL_FROM} ${EMAIL_TO} < ${LOGFILE}
=======================================================================
TABLE SPACE Report
------------------------------------
col TABLESPACE_NAME for a12
SELECT F.TABLESPACE_NAME,
TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",
TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)",
TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE
FROM (
SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V$PARAMETER
WHERE NAME = 'db_block_size')/1024)
) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 8;
=====================================================================
Monitor Alert Log
----------------------------
#! /bin/bash
#
# Filename: alertFileMonitoring.sh
#
# Description: Monitor database alert log
#
# Author: Akash Pramanik
#
# Version Name Date Changes
# v1.0 Akash Pramanik 24/09/2013 Inital revision
mailgrp=<Your Mail Group>
LASTNEWERR="" # current error found in alert log
LASTERR=""
ERRLINE=0 # line number of the current error
ORACLE_SID=<Your SID>
ALERTDIR="<Your alert log location>"
ALERTDIR=`echo $ALERTDIR | sed 's/^ //'` # strip off leading spaces
ALERTDIR=`echo $ALERTDIR | sed 's/^ //'` # strip off leading tabs
ALERTLOG=$ALERTDIR/alert_$ORACLE_SID.log
LASTERR=$ALERTDIR/alert_$ORACLE_SID.last # file where last alert log
# Look for last new error
#
LASTNEWERR=`egrep -n "DBA-|ORA-|ORA-" $ALERTLOG | tail -1`
# If error found, save error information
# Exit if no errors were found
#
LAST=$LASTNEWERR
if [ `echo $LASTNEWERR | wc -w` -eq 0 ]
#if [ ${LAST:-1} -eq 0 ]
then
exit 0
fi
# Determine whether this error was already identified
#
if [ -s $LASTERR ]
then
LASTERRLINE=` cat $LASTERR | awk -F: '{print $1}'`
if [ "`echo $LASTNEWERR`" != "`cat $LASTERR`" ]
then
# This is a new error, replace last error found with this one
#
echo $LASTNEWERR > $LASTERR
else
# Already identified, nothing new to report
#
exit 0
fi
else
# No previous error to look at, save this error
#
LASTERRLINE=0
echo $LASTNEWERR > $LASTERR
fi
# Now create a file with all new error #s
CURRERRFILE=$ALERTDIR/$ORACLE_SID.currerrs
sed "1,${LASTERRLINE}d" $ALERTLOG | egrep "^DBA-|^ORA-|ORA-" > $CURRERRFILE
mailx -s "$ORACLE_SID Db Alert Log Notification - `date`" $mailgrp < $CURRERRFILE
exit 0
#
# Filename: alertFileMonitoring.sh
#
# Description: Monitor database alert log
#
# Author: Akash Pramanik
#
# Version Name Date Changes
# v1.0 Akash Pramanik 24/09/2013 Inital revision
mailgrp=<Your Mail Group>
LASTNEWERR="" # current error found in alert log
LASTERR=""
ERRLINE=0 # line number of the current error
ORACLE_SID=<Your SID>
ALERTDIR="<Your alert log location>"
ALERTDIR=`echo $ALERTDIR | sed 's/^ //'` # strip off leading spaces
ALERTDIR=`echo $ALERTDIR | sed 's/^ //'` # strip off leading tabs
ALERTLOG=$ALERTDIR/alert_$ORACLE_SID.log
LASTERR=$ALERTDIR/alert_$ORACLE_SID.last # file where last alert log
# Look for last new error
#
LASTNEWERR=`egrep -n "DBA-|ORA-|ORA-" $ALERTLOG | tail -1`
# If error found, save error information
# Exit if no errors were found
#
LAST=$LASTNEWERR
if [ `echo $LASTNEWERR | wc -w` -eq 0 ]
#if [ ${LAST:-1} -eq 0 ]
then
exit 0
fi
# Determine whether this error was already identified
#
if [ -s $LASTERR ]
then
LASTERRLINE=` cat $LASTERR | awk -F: '{print $1}'`
if [ "`echo $LASTNEWERR`" != "`cat $LASTERR`" ]
then
# This is a new error, replace last error found with this one
#
echo $LASTNEWERR > $LASTERR
else
# Already identified, nothing new to report
#
exit 0
fi
else
# No previous error to look at, save this error
#
LASTERRLINE=0
echo $LASTNEWERR > $LASTERR
fi
# Now create a file with all new error #s
CURRERRFILE=$ALERTDIR/$ORACLE_SID.currerrs
sed "1,${LASTERRLINE}d" $ALERTLOG | egrep "^DBA-|^ORA-|ORA-" > $CURRERRFILE
mailx -s "$ORACLE_SID Db Alert Log Notification - `date`" $mailgrp < $CURRERRFILE
exit 0
====================================================================
Author - Akash Pramanik
Mobile - 9804944189
Very informative... Keep it up.
ReplyDeleteThanks...
DeleteGood....
ReplyDeleteUseful post you have shred with us.Keep updating.
ReplyDeleteDBA course syllabus | DBA training courses
Everybody is talking about microservices. Industry veterans may remember monolithic or SOA-based solutions being the way of doing things. Times have changed. New tools have allowed developers to focus on specific problems without adding excessive complexity to deployment or other administrative tasks that are usually associated with isolated services.
ReplyDeleteMicroservices training in chennai
After reading your post I understood that last week was with full of surprises and happiness for you. Congratz! Even though the website is work related, you can update small events in your life and share your happiness with us too.
ReplyDeletepython training in chennai | python training in bangalore
python online training | python training in pune
python training in chennai | python training in bangalore
python training in tambaram
I am really impressed with your efforts and really pleased to visit this post.
ReplyDeletepython training in rajajinagar
Python training in btm
Python training in usa
Hmm, it seems like your site ate my first comment (it was extremely long) so I guess I’ll just sum it up what I had written and say, I’m thoroughly enjoying your blog. I as well as an aspiring blog writer, but I’m still new to the whole thing. Do you have any recommendations for newbie blog writers? I’d appreciate it.
ReplyDeleteBest Selenium Training in Chennai | Selenium Training Institute in Chennai | Besant Technologies
Selenium Training in Bangalore | Best Selenium Training in Bangalore
AWS Training in Bangalore | Amazon Web Services Training in Bangalore
I simply wanted to write down a quick word to say thanks to you for those wonderful tips and hints you are showing on this site.
ReplyDeletesafety course in chennai
Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
ReplyDeleteJava training in Chennai | Java training in Omr
Oracle training in Chennai
Java training in Chennai | Java training in Annanagar
Java training in Chennai | Java training institute in Chennai | Java course in Chennai
I have been meaning to write something like this on my website and you have given me an idea. Cheers.
ReplyDeleteJava training in Bangalore | Java training in Jaya nagar
Java training in Bangalore | Java training in Electronic city
Java training in Chennai | Java training institute in Chennai | Java course in Chennai
Java training in USA
This is a nice article here with some useful tips for those who are not used-to comment that frequently. Thanks for this helpful information I agree with all points you have given to us. I will follow all of them.
ReplyDeletepython course institute in bangalore
python Course in bangalore
python training institute in bangalore
Thanks for sharing these effective tips. It was very helpful for me.
ReplyDeleteIELTS Coaching in Adyar
IELTS Class in Thiruvanmiyur
IELTS Class in Triplicane
IELTS Coaching in Anna Nagar
IELTS Coaching Centre in Koyambedu
IELTS Coaching Centres in Chennai Mogappair
IELTS Classes near me
I love the blog. Great post. It is very true, people must learn how to learn before they can learn. lol i know it sounds funny but its very true. . .
ReplyDeletePython Online training
python Training in Chennai
Python training in Bangalore
Great post! I am actually getting ready to across this information, It’s very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.
ReplyDeletePython Online certification training
python Training institute in Chennai
Python training institute in Bangalore
This is very good content you share on this blog. it's very informative and provide me future related information.
ReplyDeleteData Science Training in Chennai
Data Science course in anna nagar
Data Science course in chennai
Data science course in Bangalore
Data Science course in marathahalli
Data science course in bangalore
We are a group of volunteers and starting a new initiative in a community. Your blog provided us valuable information to work on.You have done a marvellous job!
ReplyDeleteMicrosoft Azure online training
Selenium online training
Java online training
uipath online training
Python online training
Thanks For sharing the Information The Information Shared Is Very valuable please Keep Updating Us The InFormation Shared Is Very Valuable Python Online Training Hadoop Online Training <a href="https://nareshit.com/data-science-online-training/>DataScience Online Training</a>
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteI am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging to share the message!!
ReplyDeleteAndroid Training in Chennai | Certification | Mobile App Development Training Online | Android Training in Bangalore | Certification | Mobile App Development Training Online | Android Training in Hyderabad | Certification | Mobile App Development Training Online | Android Training in Coimbatore | Certification | Mobile App Development Training Online | Android Training in Online | Certification | Mobile App Development Training Online
I am really impressed with your blog article, such great & useful knowledge you mentioned here. oracle training in chennai
ReplyDeleteThis post is so interactive and informative.keep update more information...
ReplyDeleteGerman Classes in Velachery
German Classes in chennai
This post is so interactive and informative.keep update more information…
ReplyDeletePHP Training in Anna nagar
PHP Training in Anna nagar
kralbet
ReplyDeletebetpark
tipobet
slot siteleri
kibris bahis siteleri
poker siteleri
bonus veren siteler
mobil ödeme bahis
betmatik
SJWR7C
تسليك مجاري جده
ReplyDeleteتسليك مجاري بجده