Feeds:
Posts
Comments

Archive for the ‘Oracle scripts’ Category

Guys as discussed at my last post oracle-performance-tuning-queries I have promised that I will be back with some DBA monitoring shell scripts , So there they are. Below shell scripts can be used for daily Database monitoring by DBA, Some are created by my fellow DBA .

Please edit the configuration as per your environment

  • This scripts checks for tablespace usage. If tablespace is 10 percent free,  it will send an alert e-mail.

#####################################################################
## check_tablespace.sh ##
##
#####################################################################
###!/bin/bash
##
####### Start of configuration
##
######## Oracle Enviorment variables ##########
##
export ORACLE_BASE=/u01/app
export ORACLE_HOME=/rac/app/oracle/product/11.2.0
export ORACLE_UNQNAME=TEST01
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/rdbms/admin:$ORACLE_HOME/lib
export alrt=/rac/app/oracle/diag/rdbms/test01/TEST01/trace/alert_TEST01.log
export asmalrt=/rac/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log
export TNS_ADMIN=$ORACLE_HOME/network/admin
export MONITOR_DIR=$ORACLE_HOME/dba-scripts/monitor

export ORACLE_SID=TEST01
##
######## Other variables #################
##
DBA=nimai.karmakar@hotmail.com
DATABASE=TEST01
datevar=$(date)
datevar2=$(date ‘+%Y-%m-%d-%H-%M’)
##
####### End of configuration
##
sqlplus -s “/ as sysdba” << SQL1
set feed off
set linesize 100
set pagesize 200
spool /rac/app/oracle/product/11.2.0/dba-scripts/monitor/tablespace.alert
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,99999′)||’ %’ PERCENT_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 WHERE TABLESPACE_NAME NOT IN (‘SYSTEM’,’SYSAUX’,’TEMP’,’USERS’,’UNDOTBS1′,’UNDOTBS2′)
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES WHERE TABLESPACE_NAME NOT IN (‘SYSTEM’,’SYSAUX’,’TEMP’,’USERS’,’UNDOTBS1′,’UNDOTBS2′)
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 10;
spool off
exit
SQL1
if [ `cat /rac/app/oracle/product/11.2.0/dba-scripts/monitor/tablespace.alert|wc -l` -gt 0 ]
then
echo Tablespace less than 10% free on ${DATABASE}.Please add space as necassary >> /rac/app/oracle/product/11.2.0/dba-scripts/monitor/tablespace.tmp
cat /rac/app/oracle/product/11.2.0/dba-scripts/monitor/tablespace.alert >> /rac/app/oracle/product/11.2.0/dba-scripts/monitor/tablespace.tmp
mailx -s “Tablespace percent usage for ${DATABASE} at $datevar” $DBA < /rac/app/oracle/product/11.2.0/dba-scripts/monitor/tablespace.tmp
#####mv /disk1/tablespace.tmp /disk1/tablespace_$datevar2.alert
rm /rac/app/oracle/product/11.2.0/dba-scripts/monitor/tablespace.alert
rm /rac/app/oracle/product/11.2.0/dba-scripts/monitor/tablespace.tmp
fi

  • Script to gather SGA stats in timely manner

####################################################
##
##SGA.sh
##
####################################################
export ORACLE_HOME=/rac/app/oracle/product/11.2.0
export ORACLE_SID=TEST01
export ORACLE_UNQNAME=TEST01
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/rdbms/admin:$ORACLE_HOME/lib
export alrt=/rac/app/oracle/diag/rdbms/test01/TEST01/trace/alert_test011.log
export asmalrt=/rac/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log
export TNS_ADMIN=$ORACLE_HOME/network/admin
MAILID=nimai.karmakar@hotmail.com
#
#
#
sqlplus -s “/ as sysdba” << SQL1
set feed off
set linesize 100
set pagesize 200
spool /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/sga_stats.log
col inst_id format 9999999 heading “INSTANCE ID”
col sga_size/1024 format 9999999 heading “SGA SIZE”
col sga_size_factor format 9999999 heading “SGA SIZE FACTOR”
col estd_physical_reads format 9999999 heading “PHYSICAL READ”
select inst_id, sga_size/1024, sga_size_factor, estd_db_time, estd_db_time_factor, estd_physical_reads from gv\$sga_target_advice
order by inst_id, sga_size_factor;
spool off
exit;
SQL1

if [ `cat /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/sga_stats.log|wc -l` -gt 0 ]; then
cat /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/sga_stats.log
mailx -s “Statistics at `date +%H+%M` for `hostname`” $MAILID << /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/sga_stats.log
fi
exit

  • Script to check Server Process (RAC)

#!/bin/bash
export PATH=$PATH:/grid/app/bin/

SUBJECT=”Server Process failed for – Server `hostname` on `date ‘+%m/%d/%y %X %A ‘`”
REPSUBJECT=”Server `hostname` health check report at `date +%H:%M` hours on `date +%d-%m-%Y`”
ERRLOG=$MONITOR_DIR/logs/server_process_check.log
REPORT=$MONITOR_DIR/logs/server_process_report.txt
BODY=$MONITOR_DIR/server_process_report_email_body.txt

## Delete the errorlog file if found
/usr/bin/find  $ERRLOG -type f -exec rm {} \; 2>&1 > /dev/null

##Report recipients
MAILID=’nimai.karmakar@hotmail.com’
chour=`date +%H“date +%M`

if [ `grep -i “TEST01  HEALTH CHECK FOR” $REPORT | wc -l` -eq 0 ]; then
echo “—————————-TEST01 HEALTH CHECK FOR `date +%m_%d_%Y`———————————” > $REPORT
fi

echo ” ” >> $REPORT

echo “—————————-CRS process status `date +%m_%d_%Y_%H:%M`—————————-” >> $REPORT
crsctl check crs | while read outputline
do
if test `echo $outputline | grep ‘online’ | wc -l` -eq 0 ## This will check if the CRS process is online or not
then
echo “Date        : “`date ‘+%m/%d/%y %X %A ‘` >> $ERRLOG
echo “Details     :”`crsctl check  crs` >> $ERRLOG
echo ”  ” >> $ERRLOG
echo ” Details     :”`crsctl check  crs` >> $REPORT
echo ” ” >> $REPORT
echo ” Skiiping other tests ” >> $REPORT
echo ”  ” >> $ERRLOG
##mutt -s “$SUBJECT” $MAILTO < $ERRLOG
/bin/mail -s “$SUBJECT” “$MAILID”
exit
else
echo ” Details     :”`crsctl check  crs` >> $REPORT
echo ” ” >> $REPORT
fi
done

echo “—————————-PMON process Status count on `date +%m_%d_%Y_%H:%M`—————————-” >> $REPORT
if test `ps -ef|grep pmon|grep -v grep |wc -l` -ne 2 ## This will check the no of pmon process for each of the server
then
echo “Date        : “`date ‘+%m/%d/%y %X %A ‘` >> $ERRLOG
echo “Details     :”`ps -ef|grep pmon|grep -v ‘grep’ |wc -l` >> $ERRLOG
echo ” ” >> $ERRLOG
echo ” PMON process not found. Oracle Instance on `hostname` may be down . Require immediate attention” >> $ERRLOG
echo ” ” >> $REPORT
echo ” Skiiping other tests ” >> $REPORT
echo ”  ” >> $ERRLOG
/bin/mail -s “$SUBJECT” “$MAILID”
exit
else
echo ” Details     :”`crsctl check  crs` >> $REPORT
echo ” ” >> $REPORT
fi

echo “—————————-Listener Status on `date +%m_%d_%Y_%H:%M`—————————-” >> $REPORT

##Check whether listener is running. Output should be 1
if test `ps -ef|grep tnslsnr | grep -v “grep” |wc -l` -ne 1 ##Check the no of listener running.
then
echo “Date        : “`date ‘+%m/%d/%y %X %A ‘` >> $ERRLOG
echo “Details     :”`ps -ef|grep tnslsnr |grep -v ‘grep’ |wc -l` >> $ERRLOG
echo ” ” >> $ERRLOG
echo ” Listener on `hostname` may be down . Require immediate attention” >> $ERRLOG
echo ” ” >> $REPORT
echo ”  ” >> $ERRLOG
/bin/mail -s “$SUBJECT” “$MAILID”
else
echo ” Details     :”`crsctl check  crs` >> $REPORT
echo ” ” >> $REPORT
fi

echo “—————————-Checking number of oracle processes `date +%m_%d_%Y_%H:%M`—————————-” >> $REPORT

##Check Process count of “oracle” user. Output should be less than or equal to 1500
if test `ps -ef|grep -i oracle |wc -l` -ge 1000
then
echo “Date        : “`date ‘+%m/%d/%y %X %A ‘` >> $ERRLOG
echo “Details     : “`ps -ef|grep -i oracle|wc -l` >> $ERRLOG
echo ”  ”
echo ” Count of processes exceeded 1000. Require immediate attention” >>  $ERRLOG
echo ”  ” >> $ERRLOG
echo ”  ” >> $ERRLOG
/bin/mail -s “$SUBJECT” “$MAILID”
else
echo “Number of oracle processes: ” `ps -ef|grep -i oracle |wc -l` >> $REPORT
echo ” ” >> $REPORT
fi

##Send the report at particular times (e.g 1500 hours or 2300 hours)
if [ $chour -ge 1500 -a $chour -lt 1502 ]; then
mutt  -s “$REPSUBJECT” -a $REPORT $MAILID < $BODY
if [ $? -eq 0 ]; then
cp $REPORT $MONITOR_DIR/logs/server_process_report_`date +%d-%m-%Y`.txt
> $REPORT
fi
fi

if [ $chour -ge 2350 -a $chour -lt 2355 ]; then
mutt -s “$REPSUBJECT” -a $REPORT $MAILID < $BODY
if [ $? -eq 0 ]; then
cp $REPORT $MONITOR_DIR/logs/server_process_report_`date +%d-%m-%Y`.txt
> $REPORT
fi
fi

exit

  • Script for purging old files

echo ‘Setting your environment’

###ORACLE_SID=#replace with your SID
ORACLE_SID=TEST01
export ORACLE_SID
###
###BDUMP=#replace with your BDUMP path
BDUMP=/rac/app/oracle/diag/rdbms/test01/TEST01/trace
export BDUMP
###
###ADUMP=#replace with your ADUMP path
ADUMP=/rac/app/oracle/admin/TEST01/adump
export ADUMP
###
###UDUMP=#replace with your UDUMP path
UDUMP=/rac/app/oracle/diag/rdbms/test01/TEST01/trace
export UDUMP

DT=`date “+%d%m%y”`
export DT
PID=${$}
export PID
FSEQ=${PID}
export FSEQ

################## Creating Backup Dir if not exist #############################
echo ‘Creating Backup Dir if not exist’
mkdir -p $BDUMP/bdump_oldfiles
mkdir -p $UDUMP/udump_oldfiles
mkdir -p $ADUMP/adump_oldfiles

#### Deleting old Alert log files and trace files################################
echo ‘Deleting old Alert log files and trace files’
cd $BDUMP/bdump_oldfiles
find . -name “*.trc.gz”  -mtime +5 -exec rm {} \ ;
find . -name “*.log.gz”  -mtime +5 -exec rm {} \ ;

cd $BDUMP
ls -lrt | grep “.trc” | awk ‘{print “mv  “$9 ” $BDUMP/bdump_oldfiles “}’ > /tmp/mv$FSEQ.sh
sh /tmp/mv$FSEQ.sh
rm /tmp/mv$FSEQ.sh

#### Backup and Purging of Alert logfile #######################################
echo ‘Backup and Purging of Alert logfile’
cd $BDUMP
cp alert_$ORACLE_SID.log $BDUMP/bdump_oldfiles/alert_$ORACLE_SID.log

cd $BDUMP
>  alert_$ORACLE_SID.log

#### Compression of old Alert log files ########################################
gzip -f $BDUMP/bdump_oldfiles/*.log

#### Deleting old user trace files #############################################
cd $UDUMP/udump_oldfiles
find /$UDUMP/udump_oldfiles -name “*.trc.gz”  -mtime +5 -exec rm {} \ ;

cd $UDUMP
ls -lrt | grep “.trc” | awk ‘{print “mv  “$9 ” $UDUMP/udump_oldfiles “}’ > /tmp/mv$FSEQ.sh
sh /tmp/mv$FSEQ.sh
rm /tmp/mv$FSEQ.sh

cd $UDUMP/udump_oldfiles
ls -lrt | grep “.trc” | grep -v “.gz” | awk ‘{print “gzip -f  ” $9 }’ > /tmp/gzip$FSEQ.sh
sh  /tmp/gzip$FSEQ.sh
rm  /tmp/gzip$FSEQ.sh

#### Deleting old audit files ##################################################
cd $ADUMP/oldfiles
find /$ADUMP/oldfiles -name “*.aud.gz” -mtime +5 -type f -exec rm {} \

cd $ADUMP
ls -lrt | grep “.aud” | awk ‘{print “mv  “$9 ” $ADUMP/oldfiles “}’ > /tmp/mv$FSEQ.sh
sh /tmp/mv$FSEQ.sh
rm /tmp/mv$FSEQ.sh

cd $ADUMP/oldfiles
ls -lrt | grep “.aud” | grep -v “.gz” | awk ‘{print “gzip -f  ” $9 }’ > /tmp/gzip$FSEQ.sh
sh  /tmp/gzip$FSEQ.sh
rm  /tmp/gzip$FSEQ.sh

############################# END ######################################

  • This scripts checks for database recovery area usage and send an  alert e-mail.

#####################################################################
## db_recovery_area_usage.sh ##
##
## Author : Nimai Karmakar
#####################################################################
###!/bin/bash
##
#######
####### Start of configuration
#######
######## Oracle Enviorment variables ##########
##
export ORACLE_BASE=/u01/app
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/test01
export JAVA_HOME=/usr/java/jdk1.6.0_30/
export PATH=${ORACLE_HOME}/bin:${JAVA_HOME}/bin:$PATH:.:
export ORACLE_SID=TEST01
##
######## Other variables #################
##
DBA=nimai.karmakar@hotmail.com
DATABASE=TEST01
datevar=$(date)
datevar2=$(date ‘+%Y-%m-%d-%H-%M’)
##
#######
####### End of configuration
#######
##
sqlplus -s “/ as sysdba” << SQL1
set feed off
set linesize 100
set pagesize 200
spool /disk1/scripts/archive.alert
set lines 100
col Location format a60
select     name “Location”
,  floor(space_limit / 1024 / 1024/1024) “Size GB”
,  ceil(space_used  / 1024 / 1024/1024) “Used GB”
from       v\$recovery_file_dest
order by name
/
spool off
exit
SQL1
if [ `cat /disk1/scripts/archive.alert|awk ‘{print $3}’|wc -l` -gt 0 ]
then
cat /disk1/scripts/archive.alert > /disk1/scripts/archive.tmp
mail -s “DB Recovery area usage for ${DATABASE} at $datevar” $DBA < /disk1/scripts/archive.tmp
rm /disk1/scripts/archive.tmp
rm /disk1/scripts/archive.alert
fi

  • This scripts checks for any ORA errors at alert log file and if found any ORA error send an  alert e-mail.

#####################################################################
## check_alert_log.sh ##
##
## Author : Nimai Karmakar
#####################################################################
###!/bin/bash
##
#######
####### Start of configuration
#######
######## Oracle Enviorment variables ##########
##
export ORACLE_BASE=/u01/app
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/test01
export JAVA_HOME=/usr/java/jdk1.6.0_30/
export PATH=${ORACLE_HOME}/bin:${JAVA_HOME}/bin:$PATH:.:
export ORACLE_SID=TEST01
##
######## Other variables #################
##
DBA=nimai.karmakar@hotmail.com
datevar=$(date)
##
#######
#######
#######
####### End of configuration
#######
#######
##
cd $ORACLE_BASE/diag/rdbms/test01/$ORACLE_SID/trace/
if [ -f alert_${ORACLE_SID}.log ]
then
tail -200 alert_${ORACLE_SID}.log > /disk1/scripts/alert_work.log
grep ORA- /disk1/scripts/alert_work.log >> /disk1/scripts/alert.err
grep Shut /disk1/scripts/alert_work.log >> /disk1/scripts/alert.err
fi
export error << `cat /disk1/scripts/alert.err`

if[ `cat /disk1/scripts/alert.err|wc -l` -gt 0 ]
then
mailx -s “${ORACLE_SID} ORACLE ALERT ERRORS $datevar” $DBA < /disk1/scripts/alert_work.log
fi
fi
rm -f /disk1/scripts/alert.err
rm -f /disk1/scripts/alert_work.log

exit

  • SCRIPT FOR CHECKING MOUNT POINT SPACE IN UNIX

######################################
#!/bin/ksh
#rm /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/ERROR_LOG.txt
echo “df -k output for `date` `uname -n`” > /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/ERROR_LOG.txt
echo ” ” >> /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/ERROR_LOG.txt
echo “File system usage exceeded the threshold on `uname -n` server- `date`” >> /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/ERROR_LOG.txt
echo ” ” >> /tmp/dfk.txt
i=1
while [ $i -le `df -k | grep -v proc | grep -v capacity | wc -l` ] ;do
if [ `df -k | grep -v proc | grep -v capacity | head -n $i | tail -1 | awk ‘{print $5}’ | \sed -e ‘s/%//’` -gt 90 ] ; then
echo “File system usage exceeded the threshold on `uname -n` server- `date`” >> /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/ERROR_LOG.txt
echo ” ” >> /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/ERROR_LOG.txt
df -k | grep -v proc | grep -v capacity | head -n $i | tail -1 >> /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/ERROR_LOG.txt
fi
((i=i+1))

done
if [ `cat /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/ERROR_LOG.txt | wc -l` -gt 2 ] ; then
#cat /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/ERROR_LOG.txt | mailx -s “File system full alert” nimai.karmakar@hotmail.com
cat /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/ERROR_LOG.txt
else
exit
fi

  • Script to check the node Eviction (RAC)

#!/bin/bash
. ~/.bash_profile

mailid=’nimai.karmakar@hotmail.com’
date=`date  +%Y-%m-%d” “%H:`

alertlog=/rac/app/oracle/product/11.2.0/dba-scripts/logs/ioerr.log
errlog=err_`date –date=”0 days ago” +_%d_%m_%y_%H_%M`.txt

err1=`grep -B 2 “$date” $alertlog | grep -A 2 “An I/O error” | wc -l`
err2=`grep -B 2 “$date” $alertlog | grep -A 2 “Network communication with node” | wc -l`

if [ $err1 -ge 1 -o $err2 -ge 1 ]
then
echo “Node eviction condition found in server `hostname`. Immediately check DB alert log for further action”  >> $errlog
echo “” >> $errlog
echo `grep -B 2 “$date” $alertlog | grep -A 2 “An I/O error”` >> $errlog
echo “” >> $errlog
echo =`grep -B 2 “$date” $alertlog | grep -A 2 “Network communication with node”` >> $errlog
mutt -s “Node evition type condition found in `hostname`” $mailto < $errLog
rm $errlog
fi

Thats it, Hope it’s helpful.

Thanks & Regards

Nimai Karmakar

Read Full Post »

Friends as I have told in my last post ” oracle-monitoring-scripts ” that soon I will be back with some performance related queries. So, it’s been a little late but here they are. 

  • High water mark status for a given table

SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
CURSOR cu_tables IS
SELECT a.owner,
a.table_name
FROM all_tables a
WHERE a.table_name = Decode(Upper(‘&&1′),’ALL’,a.table_name,Upper(‘&&1’))
and a.owner = Upper(‘&&2’);

op1 NUMBER;
op2 NUMBER;
op3 NUMBER;
op4 NUMBER;
op5 NUMBER;
op6 NUMBER;
op7 NUMBER;
BEGIN

Dbms_Output.Disable;
Dbms_Output.Enable(1000000);
Dbms_Output.Put_Line(‘TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK’);
Dbms_Output.Put_Line(‘—————————— ————— ————— —————‘);
FOR cur_rec IN cu_tables LOOP
Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,’TABLE’,op1,op2,op3,op4,op5,op6,op7);
Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,’ ‘) ||
LPad(op3,15,’ ‘) ||
LPad(op1,15,’ ‘) ||
LPad(Trunc(op1-op3-1),15,’ ‘));
END LOOP;

END;
/

SET VERIFY ON;

  • To see the server metrics on OS level (Best for RAC)

SELECT ‘Time now is ‘ METRIC_NAME,
To_char(sysdate, ‘dd/mm/yyyy hh24:mi:ss’) VALUE
FROM DUAL
UNION
SELECT to_char(inst_id) || ‘_’ || METRIC_NAME METRIC_NAME,
to_char(VALUE) value
FROM gV$SYSMETRIC
WHERE METRIC_NAME IN (‘Database CPU Time Ratio’,’Database Wait Time Ratio’)
AND INTSIZE_CSEC = (SELECT MAX(INTSIZE_CSEC) FROM gV$SYSMETRIC where inst_id=1)
and inst_id=1
UNION
SELECT to_char(inst_id) || ‘_’ || METRIC_NAME METRIC_NAME,
to_char(VALUE) value
FROM gV$SYSMETRIC
WHERE METRIC_NAME IN (‘Database CPU Time Ratio’,’Database Wait Time Ratio’)
AND INTSIZE_CSEC = (SELECT MAX(INTSIZE_CSEC) FROM gV$SYSMETRIC where inst_id=2)
and inst_id=2 ;

  • To see the long running sqls

SELECT s.SQL_TEXT,
vw.inst_id,
vw.username,
vw.osuser
–vw.machine ,
–vw.terminal ,vw.sid,vw.serial# , vw.spid,
–‘alter system kill session’ ||””||vw.sid||’,’||vw.serial#||”’;’ dbsession,
— ‘!kill -9 ‘||vw.spid osprocess
FROM
(
SELECT s.inst_id,
s.sid,
s.serial#,
s.username,
sl.sql_hash_value,
sl.sql_address,
s.program,
s.osuser,
s.machine,
s.terminal,
p.spid,
ROUND(sl.elapsed_seconds/60) || ‘:’ || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ‘:’ || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM gv$session s,
gv$session_longops sl ,
gv$process p
WHERE s.sid = sl.sid
AND s.inst_id = sl.inst_id
AND s.serial# = sl.serial#
AND ROUND(sl.elapsed_seconds/60) > 1 AND ROUND(sl.sofar/sl.totalwork*100, 2) < 100
and p.addr = s.paddr
and s.inst_id = p.inst_id
)VW LEFT OUTER JOIN gv$sql s ON
s.hash_value = VW.sql_hash_value
AND s.address = VW.sql_address
and vw.inst_id = s.inst_id
–and trim(vw.program) = ‘SQL Developer’
AND s.child_number = 0

  • Database metrics for everything

select 1 INST_ID,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ‘SQL Service Response Time (secs)’
WHEN ‘Response Time Per Txn’ then ‘Response Time Per Txn (secs)’
ELSE METRIC_NAME
END METRIC_NAME,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ROUND((MINVAL / 100),2)
WHEN ‘Response Time Per Txn’ then ROUND((MINVAL / 100),2)
ELSE MINVAL
END MININUM,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ROUND((MAXVAL / 100),2)
WHEN ‘Response Time Per Txn’ then ROUND((MAXVAL / 100),2)
ELSE MAXVAL
END MAXIMUM,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ROUND((AVERAGE / 100),2)
WHEN ‘Response Time Per Txn’ then ROUND((AVERAGE / 100),2)
ELSE AVERAGE
END AVERAGE
FROM GV$SYSMETRIC_SUMMARY
where METRIC_NAME in (‘CPU Usage Per Sec’,
‘CPU Usage Per Txn’,
‘Database CPU Time Ratio’,
‘Database Wait Time Ratio’,
‘Executions Per Sec’,
‘Executions Per Txn’,
‘Response Time Per Txn’,
‘SQL Service Response Time’,
‘User Transaction Per Sec’)
AND INTSIZE_CSEC = (SELECT MAX(INTSIZE_CSEC) FROM GV$SYSMETRIC_SUMMARY WHERE INST_ID=1)
AND INST_ID=1
UNION
select 2 INST_ID,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ‘SQL Service Response Time (secs)’
WHEN ‘Response Time Per Txn’ then ‘Response Time Per Txn (secs)’
ELSE METRIC_NAME
END METRIC_NAME,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ROUND((MINVAL / 100),2)
WHEN ‘Response Time Per Txn’ then ROUND((MINVAL / 100),2)
ELSE MINVAL
END MININUM,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ROUND((MAXVAL / 100),2)
WHEN ‘Response Time Per Txn’ then ROUND((MAXVAL / 100),2)
ELSE MAXVAL
END MAXIMUM,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ROUND((AVERAGE / 100),2)
WHEN ‘Response Time Per Txn’ then ROUND((AVERAGE / 100),2)
ELSE AVERAGE
END AVERAGE
FROM GV$SYSMETRIC_SUMMARY
where METRIC_NAME in (‘CPU Usage Per Sec’,
‘CPU Usage Per Txn’,
‘Database CPU Time Ratio’,
‘Database Wait Time Ratio’,
‘Executions Per Sec’,
‘Executions Per Txn’,
‘Response Time Per Txn’,
‘SQL Service Response Time’,
‘User Transaction Per Sec’)
AND INTSIZE_CSEC = (SELECT MAX(INTSIZE_CSEC) FROM GV$SYSMETRIC_SUMMARY WHERE INST_ID=2)
AND INST_ID=2
ORDER BY 1

  • Displays segment advice for the specified segment.

SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 200
SET VERIFY OFF

DECLARE
l_object_id NUMBER;
l_task_name VARCHAR2(32767) := ‘SEGMENT_ADVISOR’;
l_object_type VARCHAR2(32767) := UPPER(‘&object_type’);
l_attr1 VARCHAR2(32767) := UPPER(‘&object_owner’);
l_attr2 VARCHAR2(32767) := UPPER(‘&object_name’);
BEGIN
IF l_attr2 = ‘NULL’ THEN
l_attr2 := NULL;
END IF;

DBMS_ADVISOR.create_task (
advisor_name => ‘Segment Advisor’,
task_name => l_task_name);

DBMS_ADVISOR.create_object (
task_name => l_task_name,
object_type => l_object_type,
attr1 => l_attr1,
attr2 => l_attr2,
attr3 => NULL,
attr4 => ‘null’,
attr5 => NULL,
object_id => l_object_id);

DBMS_ADVISOR.set_task_parameter (
task_name => l_task_name,
parameter => ‘RECOMMEND_ALL’,
value => ‘TRUE’);

DBMS_ADVISOR.execute_task(task_name => l_task_name);

FOR cur_rec IN (SELECT f.impact,
o.type,
o.attr1,
o.attr2,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name = l_task_name
ORDER BY f.impact DESC)
LOOP
DBMS_OUTPUT.put_line(‘..’);
DBMS_OUTPUT.put_line(‘Type : ‘ || cur_rec.type);
DBMS_OUTPUT.put_line(‘Attr1 : ‘ || cur_rec.attr1);
DBMS_OUTPUT.put_line(‘Attr2 : ‘ || cur_rec.attr2);
DBMS_OUTPUT.put_line(‘Message : ‘ || cur_rec.message);
DBMS_OUTPUT.put_line(‘More info : ‘ || cur_rec.more_info);
END LOOP;

DBMS_ADVISOR.delete_task(task_name => l_task_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(‘Error : ‘ || DBMS_UTILITY.format_error_backtrace);
DBMS_ADVISOR.delete_task(task_name => l_task_name);
END;
/

  • Getting current PGA status

SELECT 1 instnum,
pga_aggr /(1024 * 1024 * 1024),
tot_pag_alloc /(1024 * 1024 * 1024),
(tot_pag_used)/ (1024* 1024* 1024)
FROM
(select value tot_pag_used
from gv$pgastat where name = ‘total PGA inuse’ and inst_id = 1),
(select value tot_pag_alloc
from gv$pgastat where name = ‘total PGA allocated’ and inst_id = 1 ),
(select value pga_aggr from gv$parameter where UPPER(NAME)=’PGA_AGGREGATE_TARGET’ and inst_id = 1)
UNION
SELECT 2 instnum,
pga_aggr /(1024 * 1024 * 1024),
tot_pag_alloc /(1024 * 1024 * 1024),
(tot_pag_used)/ (1024* 1024* 1024)
FROM
(select value tot_pag_used
from gv$pgastat where name = ‘total PGA inuse’ and inst_id = 2),
(select value tot_pag_alloc
from gv$pgastat where name = ‘total PGA allocated’ and inst_id = 2 ),
(select value pga_aggr from gv$parameter where UPPER(NAME)=’PGA_AGGREGATE_TARGET’ and inst_id = 2);

  • Getting current SGA info

select inst_id,  ROUND(SUM(BYTES)/1024/1024/1024,2) from gv$sgainfo WHERE RESIZEABLE=’Yes’ group by inst_id;

  • To see sessions per node

select count(sid) Total_session,
count(case status when ‘ACTIVE’ THEN 1 ELSE NULL END) Active_session,
count(case status when ‘INACTIVE’ THEN 1 ELSE NULL END ) Inactive_Session,
inst_id
from gv$session
WHERE USERNAME IN ( ‘SEFPLLIVE’) –> Use this clause for particular schemas
— AND LOGON_TIME >= SYSDATE – INTERVAL ‘5’ MINUTE –> use this clause for more current information
group by inst_id order by 4;

  • Dictionary hit ratio

SELECT inst_id,
(1 – (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100 Dict_Hit_Ratio
FROM gv$rowcache
group by inst_id;

  • db_buffer cache hit ratio

SELECT 1 inst_id,
(1 – (phys.value / (db.value + cons.value))) * 100 Buff_Cache_Hit_Ratio
FROM gv$sysstat phys,
gv$sysstat db,
gv$sysstat cons
WHERE phys.name = ‘physical reads’
AND db.name = ‘db block gets’
AND cons.name = ‘consistent gets’
and phys.inst_id = 1
and db.inst_id = 1
and cons.inst_id = 1
UNION
SELECT 2 inst_id,
(1 – (phys.value / (db.value + cons.value))) * 100 Buff_Cache_Hit_Ratio
FROM gv$sysstat phys,
gv$sysstat db,
gv$sysstat cons
WHERE phys.name = ‘physical reads’
AND db.name = ‘db block gets’
AND cons.name = ‘consistent gets’
and phys.inst_id = 1
and db.inst_id = 1
and cons.inst_id = 1

/

  • Latch hit ratio

SELECT inst_id,
(1 – (Sum(misses) / Sum(gets))) * 100 Latch_Hit_Ratio
FROM gv$latch
group by inst_id;

  • Displays a list of SQL statements that are using the most resources.

SELECT *
FROM (SELECT Substr(a.sql_text,1,100) sql_text,
NVL(p.username, ‘(PROD)’) AS username,
p.osuser,
p.sid,
p.serial#,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a,
v$session p
ORDER BY 2 DESC)
WHERE rownum <= 5;

  • DB CACHE ADVICE

select inst_id,
SIZE_FOR_ESTIMATE,
BUFFERS_FOR_ESTIMATE,
ESTD_PHYSICAL_READ_FACTOR,
ESTD_PHYSICAL_READS
FROM GV$db_cache_advice
WHERE NAME = ‘DEFAULT’
AND BLOCK_SIZE = (SELECT value from V$PARAMETER WHERE name = ‘db_block_size’)
AND ADVICE_STATUS = ‘ON

  • SGA TARGET ADVICE

select inst_id, sga_size, sga_size_factor, estd_db_time, estd_db_time_factor, estd_physical_reads from gv$sga_target_advice
order by inst_id, sga_size_factor;

  • TABLESPACE Fragmentation details

SELECT
TABLESPACE_NAME,COUNT(*) FREE_CHUNKS,decode( round((max(BYTES) / 1024000),2),
null,0, round((max(BYTES) / 1024000),2)) largest_chunk,
nvl(round(sqrt(max(BLOCKS)/sum(BLOCKS))*(100/sqrt(sqrt(count(BLOCKS)) )),2), 0) FRAG_INDEX
FROM
SYS.DBA_FREE_SPACE
GROUP BY
TABLESPACE_NAME
ORDER BY 2 DESC, 1;

Thats it. These scripts are few which I use to monitor for one of my project which was on RAC (Real Application Clusters).

You can also follow Tim Hall blog for more.

Soon I will be back with some shell scripts which You can you for monitoring.

Thanks & Regards

Nimai Karmakar

Read Full Post »

Hello friends

Some of my friends always ask for such scripts through which they can monitor the Instance,Database and all that related to oracle.
So, after a long a research i have came with some handy scripts which you can find very handy to help you in real situations.

Note: Please test the scripts before applying it to the Production database server.and you must have privilege as a sysdba.

Some Monitoring Scripts are as below…….

1. Select Poor Performing sql queries….

SELECT sql_text, executions,
ROUND(disk_reads / executions, 2) reads_per_run,
disk_reads, buffer_gets,
ROUND((buffer_gets-disk_reads)/
buffer_gets, 2) hit_ratio,
SQL_TEXT
FROM v$sqlarea
WHERE executions > 0
AND buffer_gets > 0
AND (buffer_gets-disk_reads) / buffer_gets < 0.80
ORDER by 3 desc ;

2. Find sql query being executed by the os.

First find the Process Id of the The OS via “top” command in linux.
Process ID (PID)
prompt “Enter The Linux Process ID”
set long 999999
set pagesize 50000
set linesize 30000
set long 500000
set head off
select
s.username su,
substr(sa.sql_text,1,1000) txt
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and spid=&SPID;
3. To Find out the blocked,waiting sessions

col high_event format a40
col wait_event format a40
select s.sid blocker,
s.event high_event,
w.event wait_event,
w.sid blocked
from v$session s, v$session w
where w.blocking_session = s.sid
and w.blocking_session_status=’VALID’;

4. Watch out session

select sid,serail# from v$session;

(After getting the sid and serial# for example– 42,67)

Long operations

select sid,username Username,serial#,opname,target,sofar,totalwork total_work,units,
time_remaining remaning_time,elapsed_seconds elapsed_time,last_update_time last_time_updated
from v$session_longops where sid=42
order by last_update_time desc;

All active sessions
select * from v$session where status=’ACTIVE’
and sid in (42,67)
order by sid;

Find session’s sid or process id by it’s sid or process id

select sid, a.serial#, spid, a.username,
status, taddr, a.program
from v$session a, v$process b
where a.paddr=b.addr and a.username is not null
and (sid=42 or spid=28179)
order by status, sid;

5. Check the size of db

Prompt “Total Size Of Database Including all”

SELECT a.data_size + b.temp_size + c.redo_size + d.controlfile_size
“total_size in GB”
FROM (SELECT SUM (bytes) / 1024 / 1048756 data_file_size FROM dba_data_files) a,
(SELECT NVL (SUM (bytes), 0) / 1024 / 1048756 temp_file_size
FROM dba_temp_files) b,
(SELECT SUM (bytes) / 1024 / 1048756 redo_log_size FROM sys.v_$log) c,
(SELECT SUM (BLOCK_SIZE * FILE_SIZE_BLKS) / 1024 / 1048756
controlfile_size
FROM v$controlfile) d;

6. Sga (System Global Area) free memory

select name,
sgasize/1024/1024 “Allocated (MEG)”,
bytes/1024 “Free (KB)”,
round(bytes/sgasize*100, 2) “% Space Free”
from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
where f.name = ‘free memory’;

7. Archive Generation Monitoring 

Archive log Hourly Generation Monitoring

set pages 2000
select trunc(COMPLETION_TIME,’HH’) Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by trunc(COMPLETION_TIME,’HH’),thread# order by 1 ;

Archive log Weekly Generation Monitoring

SELECT to_date(first_time) DATE,

to_char(sum(decode(to_char(first_time,’HH24′),’00’,1,0)),’99’) “00”,
to_char(sum(decode(to_char(first_time,’HH24′),’01’,1,0)),’99’) “01”,
to_char(sum(decode(to_char(first_time,’HH24′),’02’,1,0)),’99’) “02”,
to_char(sum(decode(to_char(first_time,’HH24′),’03’,1,0)),’99’) “03”,
to_char(sum(decode(to_char(first_time,’HH24′),’04’,1,0)),’99’) “04”,
to_char(sum(decode(to_char(first_time,’HH24′),’05’,1,0)),’99’) “05”,
to_char(sum(decode(to_char(first_time,’HH24′),’06’,1,0)),’99’) “06”,
to_char(sum(decode(to_char(first_time,’HH24′),’07’,1,0)),’99’) “07”,
to_char(sum(decode(to_char(first_time,’HH24′),’08’,1,0)),’99’) “08”,
to_char(sum(decode(to_char(first_time,’HH24′),’09’,1,0)),’99’) “09”,
to_char(sum(decode(to_char(first_time,’HH24′),’10’,1,0)),’99’) “10”,
to_char(sum(decode(to_char(first_time,’HH24′),’11’,1,0)),’99’) “11”,
to_char(sum(decode(to_char(first_time,’HH24′),’12’,1,0)),’99’) “12”,
to_char(sum(decode(to_char(first_time,’HH24′),’13’,1,0)),’99’) “13”,
to_char(sum(decode(to_char(first_time,’HH24′),’14’,1,0)),’99’) “14”,
to_char(sum(decode(to_char(first_time,’HH24′),’15’,1,0)),’99’) “15”,
to_char(sum(decode(to_char(first_time,’HH24′),’16’,1,0)),’99’) “16”,
to_char(sum(decode(to_char(first_time,’HH24′),’17’,1,0)),’99’) “17”,
to_char(sum(decode(to_char(first_time,’HH24′),’18’,1,0)),’99’) “18”,
to_char(sum(decode(to_char(first_time,’HH24′),’19’,1,0)),’99’) “19”,
to_char(sum(decode(to_char(first_time,’HH24′),’20’,1,0)),’99’) “20”,
to_char(sum(decode(to_char(first_time,’HH24′),’21’,1,0)),’99’) “21”,
to_char(sum(decode(to_char(first_time,’HH24′),’22’,1,0)),’99’) “22”,
to_char(sum(decode(to_char(first_time,’HH24′),’23’,1,0)),’99’) “23”
from
v$log_history
where to_date(first_time) > sysdate -8
GROUP by
to_char(first_time,’YYYY-MON-DD’), to_date(first_time)
order by to_date(first_time)
/

Hope it is useful, in a short time I will come with some “Oracle Performance Monitoring Scripts”,
Thanks & Regards
Nimai Karmakar

Read Full Post »

  How to crontab a schedule job in linux of oracle database

first create a scheduler at the location 
vi /home/oracledb/Desktop/Nimai/export.sh

now in insert mode write the lines of you export backup
(in my enviorment my script to export backup is)

edate=`date +%d_%m_%Y` #this is declaring a variable edate for today date
export ORACLE_SID=PRODDB #My System Identifier for Oracle
export ORACLE_HOME=/apps/oracledb/product/10.2.0/orahome #export the oracle home
export PATH=/apps/oracledb/product/10.2.0/orahome/bin #export bin path 
expdp Newton/password directory=DUMP_DIR dumpfile=schedule_nimai_$edate.dmp logfile=schedule_$edate.log schemas=newton;

now save it through esc+:w
and then exit

now we have to schedule this schedule at any specific time when you need.

In my enviorment I have set the schedule at everyday 2 pm in weekdays.

00 14 * * 1-6 sh /home/oracledb/Desktop/export.sh >> /dev/null

to edit the cronjob crontab -e
to list cronjob crontab -l

note-
00 defines minute 
14 defines 2 pm
* means every day
* means every month
1-6 defines from monday to saturday (sunday-0)

Scheduling explained 
As you can see there are 5 stars. The stars represent different date parts in the following order:
minute (from 0 to 59) 
hour (from 0 to 23)
day of month (from 1 to 31)
month (from 1 to 12)
day of week (from 0 to 6) (0=Sunday)

Execute every minute
If you leave the star, or asterisk, it means every. Maybe that's a bit unclear. Let's use the the previous example again:

* * * * * sh /bin/execute/this/script.sh
They are all still asterisks! So this means execute /bin/execute/this/script.sh:

every minute of every hour

of every day of the month

of every month

and every day in the week.

In short: This script is being executed every minute. Without exception.

Execute every Friday 1AM

So if we want to schedule the script to run at 1AM every Friday, we would need the following cronjob:

0 1 * * 5 sh /bin/execute/this/script.sh

Get it? The script is now being executed when the system clock hits:

minute: 0

of hour: 1

of day of month: * (every day of month)

of month: * (every month)

and weekday: 5 (=Friday)

Execute on workdays 1AM

So if we want to schedule the script to Monday till Friday at 1 AM, we would need the following cronjob:

0 1 * * 1-5 sh /bin/execute/this/script.sh

Get it? The script is now being executed when the system clock hits:

minute: 0

of hour: 1

of day of month: * (every day of month)

of month: * (every month)

and weekday: 1-5 (=Monday til Friday)

Execute 10 past after every hour on the 1st of every month

Here's another one, just for practicing

10 * 1 * * sh /bin/execute/this/script.sh

Fair enough, it takes some getting used to, but it offers great flexibility.

Neat scheduling tricks

What if you'd want to run something every 10 minutes? Well you could do this:

0,10,20,30,40,50 * * * * sh /bin/execute/this/script.sh

But crontab allows you to do this as well:

*/10 * * * * sh /bin/execute/this/script.sh

Special words

If you use the first (minute) field, you can also put in a keyword instead of a number:

@reboot     Run once, at startup

@yearly     Run once  a year     "0 0 1 1 *"

@annually   (same as  @yearly)

@monthly    Run once  a month    "0 0 1 * *"

@weekly     Run once  a week     "0 0 * * 0"

@daily      Run once  a day      "0 0 * * *"

@midnight   (same as  @daily)

@hourly     Run once  an hour    "0 * * * *

Leave the rest of the fields empty so this would be valid:

$ @daily sh /bin/execute/this/script.sh

*/10 * * * * sh /bin/execute/this/script.sh | mail -s "Cronjob ouput" yourname@yourdomain.com

trashing the output

* * * * * sh /apps/oracle/scripts/cloning.sh >> /dev/null

This  will for the output to this logfile

* * * * * sh /apps/oracle/scripts/cloning.sh >> /apps/oracle/scripts/file_name.log

Read Full Post »

Oracle Application DBA

sharing the information about realtime experience

My Oracle Notes

Emiliano Fusaglia RAC DBA/Data Architect

Johribazaar's Blog

Just another WordPress.com weblog

Ayurveda and Yoga

Site for Ayurveda and Yoga articles

SanOraLife

Few adventures in my slow running life...

pavankumaroracledba

4 out of 5 dentists recommend this WordPress.com site

ORACLE WRITES

Technical Articles by Kanchana Selvakumar

SAP Basis Cafe

SAP Basis Cafe - Exploring SAP R/3 Basis World

Life Is A Mystery

This Is Wihemdra's Blog...I Share What I Have

ursvenkat's Blog

Oracle Applications DBA Blog