News

  1. Home
  2. All posts
All posts, Linux, Oracle, Shell by Team Ajara

Script for Monitoring Oracle Database

Script for Monitoring Oracle databases.

Script to monitor sessionlocks,current session and tablespace usage details on RAC database
Main script calls 3 SQL queries and sends the output in a mail .

## Main Script RAC_monitor_queries.sh Start ##
#Script Name: /u01/app/oracle/scripts/RAC_monitor_queries.sh
#
. /u01/app/oracle/.SetEnv19c
LOGPATH=/u01/app/oracle/scripts/output
TODAY=`date +’%Y%m%d%H:%M:%S’`
OUTPUT_FILE=$LOGPATH/output-$TODAY.log
ALERT_FILE=$LOGPATH/alert–$TODAY.log
touch $OUTPUT_FILE
cp /u01/app/oracle/diag/rdbms/orclp1/ORCLP1/trace/alert_ORCLP1.log $ALERT_FILE

$ORACLE_HOME/bin/sqlplus -s ‘/as sysdba’ << EOF
spool $OUTPUT_FILE
select instance_name from v\$instance;
prompt Checking Locks:;
@/u01/app/oracle/scripts/sessionlock.sql
prompt Checking current session:;
@/u01/app/oracle/scripts/currentsession.sql
prompt Checking Tablespaces:;
@/u01/app/oracle/scripts/tablespace.sql >>
spool off
exit
EOF

cd $LOGPATH

echo MonitoringQueries |mailx -s ” Monitoring report every two hours – ALERT LOG – Node 1 – ” -r “oracle_Node1@ajara.tech” -a $ALERT_FILE dba@ajara.tech

echo MonitoringQueries |mailx -s ” Monitoring report every two hours” -r “oracle_Node1@ajara.tech” -a $OUTPUT_FILE dba@ajara.tech

##Script RAC_monitor_queries.sh End##

 

Sub script1 : /u01/app/oracle/scripts/sessionlock.sql

— SQL Query start
–Script Name: /u01/app/oracle/scripts/sessionlock.sql
break on BLOKING
select l2.sid BLOKING, ‘ –> ‘, l1.sid BLOCKED
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
order by 1,3;

— SQL Query sessionlock.sql end

 

Sub script2: /u01/app/oracle/scripts/currentsession.sql

–SQL Query Start

–SQL Query Name: /u01/app/oracle/scripts/currentsession.sql
set head on
set linesize 300
SELECT inst_id,resource_name, current_utilization, max_utilization, limit_value
FROM gv$resource_limit
WHERE resource_name in (‘processes’,’sessions’);
–SQL Query currentsession.sql End

 

Sub script 3: /u01/app/oracle/scripts/tablespace.sql

–SQL Query Start
— To Get the tablespace Sizes
–SQL Query Name: /u01/app/oracle/scripts/tablespace.sql
set linesize 100
set pagesize 100

select
a.tablespace_name,
round(SUM(a.bytes)/(1024*1024*1024)) CURRENT_GB,
round(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),
b.maxextend*8192/(1024*1024*1024)))) MAX_GB,
(SUM(a.bytes)/(1024*1024*1024) – round(c.Free/1024/1024/1024)) USED_GB,
round((SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),
b.maxextend*8192/(1024*1024*1024))) – (SUM(a.bytes)/(1024*1024*1024) –
round(c.Free/1024/1024/1024))),2) FREE_GB,
round(100*(SUM(a.bytes)/(1024*1024*1024) –
round(c.Free/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),
b.maxextend*8192/(1024*1024*1024))))) USED_PCT
from
dba_data_files a,
sys.filext$ b,
(SELECT
d.tablespace_name ,sum(nvl(c.bytes,0)) Free
FROM
dba_tablespaces d,
DBA_FREE_SPACE c
WHERE
d.tablespace_name = c.tablespace_name(+)
group by d.tablespace_name) c
WHERE
a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name, c.Free/1024
ORDER BY tablespace_name;

–SQL Query tablespace.sql End

 

shell Sub script .SetEnv19c

#Script name /u01/app/oracle/.SetEnv19c #
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=ORCLP1
. $HOME/.bash_profile

# .SetEnv19c End #

 

Content of .bash_profile

# Bash $HOME/.bash_profile Start#
#!/bin/bash

stty erase ^?
set -o vi
PS1=”\h ORCLP1 $ ”
# Set Display
DISPLAY=`who am i | cut -f2 -d”(” | sed ‘s/).*//g’ | sed ‘s/:0\.0//g’`:0.0
export DISPLAY
echo “set DISPLAY to $DISPLAY”

unset LC_CTYPE
LANG=en_US.ISO8859-1
export LANG

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=/u01/app/oracle/product/19.0.0/dbhome_1/network/admin
export ORACLE_SID=ORCLP1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_TERM=hft
export TEMP=/tmp
export TMDIR=/tmp
export TS_ALERTLOG=/u01/app/oracle/diag/rdbms/orclp1/ORCLP1/trace/alert_ORCLP1.log
alias sq=’sqlplus sys as sysdba’
alias sm=’sqlplus / as sysdba’
alias ORCLP1=’ ‘
cd $ORACLE_BASE
umask 0022
# File .bash_profile End

How to run Manually?

sh /u01/app/oracle/scripts/RAC_monitor_queries.sh

crontab entry

## Monitoring every two hours
00 0,2,4,6,8,10,12,14,16,18,20,22 * * * /u01/app/oracle/scripts/RAC_monitor_queries.sh 2>/dev/null
#

 

Sample Output

INSTANCE_NAME
—————-
ORCLP1

Checking Locks:

no rows selected

Checking current session:

INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
———- ————————– ——————- ————— ————-
1 processes 1015 1405 3000
1 sessions 1066 1604 6016
2 processes 1005 1421 3000
2 sessions 1055 1528 6016

Checking Tablespaces:

TABLESPACE_NAME CURRENT_GB MAX_GB USED_GB FREE_GB USED_PCT
—————————— ———- ———- ———- ———- ———-

PROD_JOB 103 153 23.7109375 129.29 15
BACX 118 131 111.610291 19.39 85
SYSAUX 53 122 34.9189453 87.08 29
SYSTEM 3 5 2.41796875 2.58 48
ARC 14 32 3.4140625 28.59 11
UNDOTBS1 49 61 3.15039063 57.85 5
UNDOTBS2 38 72 4.04882813 67.95 6
USERS 1 5 1.3671875 3.63 27

 

See Also: