Shell_Scripting – KTEXPERTS

Shell Script to get EMAIL alerts for ABENDED PROCESSES in Oracle GoldenGate

Hi Readers, Below article will help you on Shell Script to get EMAIL alerts for ABENDED Processes in Oracle GoldenGate. Script :

[oracle@AWS1 ~]# cat GG_Abend.sh
export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH:.
export ORACLE_NET=$ORACLE_HOME/network/admin
export GGS_HOME=/u01/app/oracle/product/ogg_src
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
DBA_EMAIL=ktexperts@gmail.com

logpath="/tmp"
cd $logpath
logfile="$logpath/output.txt"
# Output GG information to log file
echo 'INFO ALL' | /u01/app/oracle/product/ogg_src/ggsci > $logfile
#------------------------#
# Check for ABEND errors #
#------------------------#
if [ `cat $logfile | grep ABENDED | grep -v grep | wc -l` -eq 0 ]
then
    echo "No ABENDED process."
else
    echo "GoldenGate Process ABENDED on $ORACLE_HOST_NAME for database $ORACLE_SID"
    cat $logfile | mailx -s "GoldenGate Process ABENDED on $ORACLE_HOST_NAME for database $ORACLE_SID" $DBA_EMAIL
    #cat $logfile | mailx -s "TEST MAIL on $ORACLE_HOST_NAME for database $ORACLE_SID" $DBA_EMAIL

fi

[oracle@AWS1 ~]# cat GG_Abend.sh

export ORACLE_SID=orcl

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH:.

export ORACLE_NET=$ORACLE_HOME/network/admin

export GGS_HOME=/u01/app/oracle/product/ogg_src

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

DBA_EMAIL=ktexperts@gmail.com

logpath="/tmp"

cd $logpath

logfile="$logpath/output.txt"

# Output GG information to log file

echo 'INFO ALL' | /u01/app/oracle/product/ogg_src/ggsci > $logfile

#------------------------#

# Check for ABEND errors #

#------------------------#

if [ `cat $logfile | grep ABENDED | grep -v grep | wc -l` -eq 0 ]

then

echo "No ABENDED process."

else

echo "GoldenGate Process ABENDED on $ORACLE_HOST_NAME for database $ORACLE_SID"

cat $logfile | mailx -s "GoldenGate Process ABENDED on $ORACLE_HOST_NAME for database $ORACLE_SID" $DBA_EMAIL

#cat $logfile | mailx -s "TEST MAIL on $ORACLE_HOST_NAME for database $ORACLE_SID" $DBA_EMAIL

Keep Script in Crontab :

[root@AWS! ~]# crontab -l

5 * * * * /oracle/home/GG_Abend.sh > /dev/null 2>&1

[root@AWS! ~]# crontab -l

5 * * * * /oracle/home/GG_Abend.sh > /dev/null 2>&1

The above script will execute for every 5 minutes Now check Output file in… Read More

Automating drop partitions for set of Partition tables(Retention based on Selected Dates)

6 months ago by Ajay KumarOracle DB Admin

Automating drop partitions for set of Partition tables(Retention based on Selected Dates) We care for your speed. Below is an easy way to perform Automating drop partitions for set of Partition tables(Retention based on Selected Dates). To Automate the script follow below steps Step 1 :… Read More

Shell script to EXPDP / SCP and IMPDP

6 months ago by Ajay KumarOracle DB Admin

Shell script to EXPDP / SCP and IMPDP We care for your speed. Below is an easy way to perform Shell script to EXPDP / SCP and IMPDP. To Automate the script follow below steps Export script : The below script will export only few data… Read More

Automating drop partitions for set of Partition tables(Retention based on No OF DAYS)

7 months ago by Ajay KumarOracle DB Admin

Automating drop partitions for set of Partition tables(Retention based on No OF DAYS) We care for your speed. Below is an easy way to perform Automating drop partitions for set of Partition tables(Retention based on No OF DAYS) To Automate the script follow below steps Step… Read More

Shell :EXPORTING DATA FROM REMOTE DATABASE USING NETWORK_LINK

1 year ago by Ajay KumarOracle DB Admin

SHELL : EXPORTING DATA FROM REMOTE DATABASE USING NETWORK_LINK In this article , we will how to automate the script for export data from remote database and import in source database. Check below link for previous article on same topic. HOW TO EXPORT DATA FROM REMOTE… Read More

How to kill Long Running Query using scripts(Version – 2).

1 year ago by Ajay KumarOracle DB Admin

How to kill Long Running Query using scripts(Version – 2). In this article , we will see how to write script to kill running query on Oracle database. Aim : To kill Long Running Query Step 1 : Create a table

DBMON>> CREATE TABLE LONG_RUNNING_TABLE(sno number,hiredate date);
Table Created.

1 2	DBMON>> CREATE TABLE LONG_RUNNING_TABLE(sno number,hiredate date); Table Created.

Step 2 :… Read More

How to kill Long Running Query using scripts.

1 year ago by Ajay KumarOracle DB Admin

How to kill Long Running Sessions using scripts. In this article , we will see how to write script to kill running query on Oracle database. Aim : To kill Long Running Query Step 1 : Create a table

DBMON>> CREATE TABLE LONG_RUNNING_TABLE(sno number,hiredate date);
Table Created.

1 2	DBMON>> CREATE TABLE LONG_RUNNING_TABLE(sno number,hiredate date); Table Created.

Step 2 : Insert huge… Read More

Shell : Automate killing Blocking Sessions

1 year ago by Vinod SiramOracle DB Admin

Shell : Automate killing Blocking Sessions In this article we will learn how to automate killing Blocking sessions using shell script. Please check below video link : Example : AIM : Automate killing user session which is block for more than 15 mins(900 sec).Step 1… Read More

Oracle : Shell script for Health Check in HTML Format

1 year ago by Vinod SiramOracle DB Admin

0 03,08,16,21 * * * /u01/app/oracle/dba/scripts/DBA_MON/Dailycheck.sh > /dev/null 2>&1

cat /u01/app/oracle/dba/scripts/DBA_MON/Dailycheck.sh
#!/bin/bash
set -x
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
export ORACLE_SID=ORCL11
export PATH=/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/u01/app/oracle/product/12.1.0.2/dbhome_1/bin
cd /u01/app/oracle/dba/scripts/DBA_MON
sqlplus -s -m "HTML ON TABLE 'BORDER="2"'" / as sysdba @Dailycheck.sql>DailyCheck.html
/bin/sed 's/PL\/SQL procedure successfully completed./ /' DailyCheck.html >DailyCheck2.html
/bin/sed 's/336699/000000/' DailyCheck2.html > DailyCheck3.html
/bin/cat DailyCheck3.html | /bin/sed '/rows selected/d' > DailyCheck4.html
(echo "Subject: ORCL11 Test Database Health Check"; echo "MIME-Version: 1.0";  echo "Content-Type: text/html";  echo "Content-Disposition: inline"; /bin/cat DailyCheck4.html; )   | /usr/sbin/sendmail -F kumar@ktexperts.com -f "kumar@ktexperts.com" kumar@ktexperts.com
exit


cat Dailycheck.sql
SET ECHO OFF
SET SERVEROUTPUT ON
SET PAGESIZE 500
exec dbms_output.put_line(' ORCL11 Test Database Health Check Report ');

select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role as Database_Details from v$database, v$instance;

exec dbms_output.put_line('DATABASE NODE INFORMATION');
select INST_ID,INSTANCE_NAME,host_name,to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') startup_Time, INSTANCE_ROLE from gv$instance order by inst_id;

exec dbms_output.put_line('1.TABLESPACE USAGE');
select tablespace_name tablespace,
round(sum(a.total1)/1048576, 0) Total,
round(sum(a.total1)/1048576, 0)-round(sum(a.sum1)/1048576, 0) used,
round(sum(a.sum1)/1048576, 0) free,
round(((sum(a.sum1)/1048576)/(sum(a.total1)/1048576))*100, 0) pct_free,
round(sum(a.maxb)/1048576, 0) largest,
max(a.cnt) fragment
from
(select tablespace_name, 0 total1, sum(bytes) sum1,
max(bytes) MAXB,
count(bytes) cnt
from dba_free_space
group by tablespace_name
union
select tablespace_name, sum(bytes) total1, 0, 0, 0 from dba_data_files
group by tablespace_name) a
group by a.tablespace_name
order by 5 desc;

exec dbms_output.put_line('2.DATABASE SIZE HISTORY');
SELECT * FROM (SELECT DATE_ID AS SIZE_DATE ,SUM(SIZE_GB) DB_SIZE FROM ktexperts.DB_SIZE_HISTORY GROUP BY DATE_ID ORDER BY 1 DESC) WHERE ROWNUM<5;

exec dbms_output.put_line('3.ASM USAGE');
select NAME, ALLOCATION_UNIT_SIZE, STATE, TYPE, round((TOTAL_MB/1024),0) TOTAL_GB ,round((FREE_MB/1024),0) FREE_GB,round(((FREE_MB/TOTAL_MB)*100),0) PCt_FREE from v$asm_diskgroup order by PCt_FREE ; 

exec dbms_output.put_line('4.REDO LOG GENERATION');
select * from (select trunc(first_time) logdate, 
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN", 
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM", 
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM", 
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM", 
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM", 
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM", 
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM", 
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM", 
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM", 
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM", 
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM", 
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM", 
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon", 
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM", 
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM", 
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM", 
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM", 
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM", 
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM", 
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM", 
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM", 
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM", 
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM", 
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM" 
from v$log_history 
group by trunc(first_time)
order by 1 desc) where rownum<7;  

exec dbms_output.put_line('5.BACKUP DETAILS');
select
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.time_taken_display
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-3
order by j.start_time;



select * from (
select ctime "Date"
       , decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type
       , bsize "Size MB"
  from (select trunc(bp.completion_time) ctime
      , backup_type
      , round(sum(bp.bytes/1024/1024/1024),2) bsize
     from v$backup_set bs, v$backup_piece bp
     where bs.set_stamp = bp.set_stamp
     and bs.set_count  = bp.set_count
     and bp.status = 'A'
     group by trunc(bp.completion_time), backup_type)
  order by 1 desc
  ) where rownum<6;

exec dbms_output.put_line('6.ARCHIVE GENERATION HISTORY');
SELECT GENERATION_DATE ,SUM(TOTAL_DAY_GB) TOTAL_DAY_GB FROM ktexperts.ARCHIVE_USAGE_HISTORY
WHERE TRUNC(GENERATION_DATE)<=TRUNC(SYSDATE-1)
AND TRUNC(GENERATION_DATE)>=TRUNC(SYSDATE-4)
GROUP BY GENERATION_DATE ORDER BY GENERATION_DATE;

EXIT

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

0 03,08,16,21 * * * /u01/app/oracle/dba/scripts/DBA_MON/Dailycheck.sh > /dev/null 2>&1

cat /u01/app/oracle/dba/scripts/DBA_MON/Dailycheck.sh

#!/bin/bash

set -x

export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1

export ORACLE_SID=ORCL11

export PATH=/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/u01/app/oracle/product/12.1.0.2/dbhome_1/bin

cd /u01/app/oracle/dba/scripts/DBA_MON

sqlplus -s -m "HTML ON TABLE 'BORDER="2"'" / as sysdba @Dailycheck.sql>DailyCheck.html

/bin/sed 's/PL\/SQL procedure successfully completed./ /' DailyCheck.html >DailyCheck2.html

/bin/sed 's/336699/000000/' DailyCheck2.html > DailyCheck3.html

/bin/cat DailyCheck3.html | /bin/sed '/rows selected/d' > DailyCheck4.html

(echo "Subject: ORCL11 Test Database Health Check"; echo "MIME-Version: 1.0"; echo "Content-Type: text/html"; echo "Content-Disposition: inline"; /bin/cat DailyCheck4.html; ) | /usr/sbin/sendmail -F kumar@ktexperts.com -f "kumar@ktexperts.com" kumar@ktexperts.com

exit

cat Dailycheck.sql

SET ECHO OFF

SET SERVEROUTPUT ON

SET PAGESIZE 500

exec dbms_output.put_line(' ORCL11 Test Database Health Check Report ');

select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role as Database_Details from v$database, v$instance;

exec dbms_output.put_line('DATABASE NODE INFORMATION');

select INST_ID,INSTANCE_NAME,host_name,to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') startup_Time, INSTANCE_ROLE from gv$instance order by inst_id;

exec dbms_output.put_line('1.TABLESPACE USAGE');

select tablespace_name tablespace,

round(sum(a.total1)/1048576, 0) Total,

round(sum(a.total1)/1048576, 0)-round(sum(a.sum1)/1048576, 0) used,

round(sum(a.sum1)/1048576, 0) free,

round(((sum(a.sum1)/1048576)/(sum(a.total1)/1048576))*100, 0) pct_free,

round(sum(a.maxb)/1048576, 0) largest,

max(a.cnt) fragment

from

(select tablespace_name, 0 total1, sum(bytes) sum1,

max(bytes) MAXB,

count(bytes) cnt

from dba_free_space

group by tablespace_name

union

select tablespace_name, sum(bytes) total1, 0, 0, 0 from dba_data_files

group by tablespace_name) a

group by a.tablespace_name

order by 5 desc;

exec dbms_output.put_line('2.DATABASE SIZE HISTORY');

SELECT * FROM (SELECT DATE_ID AS SIZE_DATE ,SUM(SIZE_GB) DB_SIZE FROM ktexperts.DB_SIZE_HISTORY GROUP BY DATE_ID ORDER BY 1 DESC) WHERE ROWNUM<5;

exec dbms_output.put_line('3.ASM USAGE');

select NAME, ALLOCATION_UNIT_SIZE, STATE, TYPE, round((TOTAL_MB/1024),0) TOTAL_GB ,round((FREE_MB/1024),0) FREE_GB,round(((FREE_MB/TOTAL_MB)*100),0) PCt_FREE from v$asm_diskgroup order by PCt_FREE ;

exec dbms_output.put_line('4.REDO LOG GENERATION');

select * from (select trunc(first_time) logdate,

sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN",

sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM",

sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM",

sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM",

sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM",

sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM",

sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM",

sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM",

sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM",

sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM",

sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM",

sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM",

sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon",

sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM",

sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM",

sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM",

sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM",

sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM",

sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM",

sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM",

sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM",

sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM",

sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM",

sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM"

from v$log_history

group by trunc(first_time)

order by 1 desc) where rownum<7;

exec dbms_output.put_line('5.BACKUP DETAILS');

select

to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,

to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,

(j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,

decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',

3, 'Tuesday', 4, 'Wednesday',

5, 'Thursday', 6, 'Friday',

7, 'Saturday') dow,

j.time_taken_display

from V$RMAN_BACKUP_JOB_DETAILS j

left outer join (select

d.session_recid, d.session_stamp,

sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,

sum(case when d.controlfile_included = 'NO'

and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,

sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,

sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,

sum(case when d.backup_type = 'L' then d.pieces else 0 end) L

from

V$BACKUP_SET_DETAILS d

join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count

where s.input_file_scan_only = 'NO'

group by d.session_recid, d.session_stamp) x

on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp

left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id

from GV$RMAN_OUTPUT o

group by o.session_recid, o.session_stamp)

ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp

where j.start_time > trunc(sysdate)-3

order by j.start_time;

select * from (

select ctime "Date"

, decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type

, bsize "Size MB"

from (select trunc(bp.completion_time) ctime

, backup_type

, round(sum(bp.bytes/1024/1024/1024),2) bsize

from v$backup_set bs, v$backup_piece bp

where bs.set_stamp = bp.set_stamp

and bs.set_count = bp.set_count

and bp.status = 'A'

group by trunc(bp.completion_time), backup_type)

order by 1 desc

) where rownum<6;

exec dbms_output.put_line('6.ARCHIVE GENERATION HISTORY');

SELECT GENERATION_DATE ,SUM(TOTAL_DAY_GB) TOTAL_DAY_GB FROM ktexperts.ARCHIVE_USAGE_HISTORY

WHERE TRUNC(GENERATION_DATE)<=TRUNC(SYSDATE-1)

AND TRUNC(GENERATION_DATE)>=TRUNC(SYSDATE-4)

GROUP BY GENERATION_DATE ORDER BY GENERATION_DATE;

EXIT

Oracle : Script for Sending email for Blocking Sessions

1 year ago by Vinod SiramOracle DB Admin

*/1 * * * * sh /u01/app/oracle/dba/scripts/DBA_MON/Blocking_Sessions.sh ORCL11 >/dev/null 2>&1
 
cat /u01/app/oracle/dba/scripts/DBA_MON/Blocking_Sessions.sh 

#!/bin/ksh
set -x
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
export ORACLE_SID=$1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u01/app/oracle/product/12.1.0.2/dbhome_1/bin

export LOG_FILE=/u01/app/oracle/dba/log/ORCL_Blocking_Sess_`date +%F%s.log`

sqlplus -s / as sysdba <<EOF >> $LOG_FILE
@/u01/app/oracle/dba/scripts/sql/ReportBlockingSess.sql
EOF

chk_count=`grep "BLOCKING user" $LOG_FILE | wc -l`

if [ $chk_count -gt 0 ]; then
mailx -s "ORCL11: Blocking Session from APP TEL" team@ktexperts.com < $LOG_FILE
fi

find /u01/app/oracle/dba/log -name 'ORCL_Blocking_Sess_*.log' -mmin +5 -exec rm -rf {} \;
exit


cat /u01/app/oracle/dba/scripts/sql/ReportBlockingSess.sql
set serveroutput on long 99999 lines 131 pages 2000 feedback off head off pagesize 0 linesize 200 long 99999
DECLARE
B_USERNAME GV$SESSION.USERNAME%TYPE;
B_SID GV$SESSION.SID%TYPE;
B_SERIAL GV$SESSION.SERIAL#%TYPE;
B_INST_ID GV$SESSION.INST_ID%TYPE;
B_MACHINE GV$SESSION.MACHINE%TYPE;
B_STATUS GV$SESSION.STATUS%TYPE;
B_LAST_CALL_ET GV$SESSION.LAST_CALL_ET%TYPE;
B_PROCESS GV$SESSION.PROCESS%TYPE;
B_SQL_ID GV$SESSION.SQL_ID%TYPE;
B_SQLFULLTEXT GV$SQLAREA.SQL_FULLTEXT%TYPE;
B_MODULE GV$SESSION.MODULE%TYPE;
W_USERNAME GV$SESSION.USERNAME%TYPE;
W_SID GV$SESSION.SID%TYPE;
W_SERIAL GV$SESSION.SERIAL#%TYPE;
W_INST_ID GV$SESSION.INST_ID%TYPE;
W_MACHINE GV$SESSION.MACHINE%TYPE;
W_SECONDS_IN_WAIT GV$SESSION.SECONDS_IN_WAIT%TYPE;
W_PROCESS GV$SESSION.PROCESS%TYPE;
W_SQL_ID GV$SESSION.SQL_ID%TYPE;
W_MODULE GV$SESSION.MODULE%TYPE;
W_SQLFULLTEXT GV$SQLAREA.SQL_FULLTEXT%TYPE;
CURSOR BLOCKING_SESS 
IS
SELECT B.USERNAME,B.SID,B.SERIAL#,B.INST_ID,B.MACHINE,B.MODULE,B.STATUS,B.LAST_CALL_ET,B.PROCESS,NVL(B.SQL_ID,B.PREV_SQL_ID),
W.USERNAME,W.SID,W.SERIAL#,W.INST_ID,W.MACHINE,W.MODULE,W.SECONDS_IN_WAIT,W.PROCESS,W.SQL_ID
FROM 
(SELECT INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET,MACHINE,MODULE,SECONDS_IN_WAIT,PROCESS,SQL_ID FROM GV$SESSION WHERE BLOCKING_SESSION >0) W,
(SELECT INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET,MACHINE,MODULE,TYPE,PROCESS,SQL_ID,PREV_SQL_ID FROM GV$SESSION) B
where W.USERNAME not in ('SYS','SYSTEM','ktexperts')
and W.BLOCKING_INSTANCE=B.INST_ID
and W.BLOCKING_SESSION=B.SID
and W.SECONDS_IN_WAIT > 300
and B.TYPE not in ('BACKGROUND');
BEGIN
OPEN BLOCKING_SESS;
LOOP
FETCH BLOCKING_SESS INTO B_USERNAME,B_SID,B_SERIAL,B_INST_ID,B_MACHINE,B_MODULE,B_STATUS,B_LAST_CALL_ET,B_PROCESS,B_SQL_ID, W_USERNAME,W_SID,W_SERIAL,W_INST_ID,W_MACHINE,W_MODULE,W_SECONDS_IN_WAIT,W_PROCESS,W_SQL_ID;
EXIT WHEN BLOCKING_SESS%NOTFOUND;
SELECT SQL_FULLTEXT INTO B_SQLFULLTEXT from GV$SQLAREA WHERE SQL_ID=B_SQL_ID and INST_ID=B_INST_ID;
SELECT SQL_FULLTEXT INTO W_SQLFULLTEXT from GV$SQLAREA WHERE SQL_ID=W_SQL_ID and INST_ID=W_INST_ID;
DBMS_OUTPUT.PUT_LINE('BLOCKER '||B_USERNAME ||' Session('||B_SID||','||B_SERIAL||',@'||B_INST_ID||') from SERVER/MODULE: '||B_MACHINE||'/'||B_MODULE||') EXECUTING SQL_ID : '||B_SQL_ID||' is Currently '||B_STATUS||' from last '||B_LAST_CALL_ET||' Secs'||CHR(10));
DBMS_OUTPUT.PUT_LINE('BLOCKERs CLIENT SYSTEM PROCESS/MODULE : '||B_PROCESS||'/'||B_MODULE||CHR(10));
DBMS_OUTPUT.PUT_LINE('And its BLOCKING user '|| W_USERNAME|| ' ('||W_SID||','||W_SERIAL||',@'||W_INST_ID||') EXECUTING SQL_ID : '||W_SQL_ID||' from SERVER '||W_MACHINE||' Since '|| W_SECONDS_IN_WAIT || ' Secs'||CHR(10));
DBMS_OUTPUT.PUT_LINE('BLOCKED CLIENT SYSTEM PROCESS/MODULE : '||W_PROCESS||'/'||W_MODULE||CHR(10));
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT_LINE('********************************************************');
DBMS_OUTPUT.PUT_LINE('SQL Being Executed By The BLOCKER Session : '||B_SQL_ID|| CHR(10));
DBMS_OUTPUT.PUT_LINE('********************************************************');
DBMS_OUTPUT.PUT_LINE(B_SQLFULLTEXT);
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT_LINE('========================================================');
DBMS_OUTPUT.PUT_LINE('SQL Being Executed By The BLOCKED Session : '||W_SQL_ID||CHR(10));
DBMS_OUTPUT.PUT_LINE('========================================================');
DBMS_OUTPUT.PUT_LINE(W_SQLFULLTEXT);
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT(CHR(10));
END LOOP;
CLOSE BLOCKING_SESS;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error : '|| dbms_utility.format_error_stack() || CHR(10) || dbms_utility.format_error_backtrace());
end;
/

*/1 * * * * sh /u01/app/oracle/dba/scripts/DBA_MON/Blocking_Sessions.sh ORCL11 >/dev/null 2>&1

cat /u01/app/oracle/dba/scripts/DBA_MON/Blocking_Sessions.sh

#!/bin/ksh

set -x

export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1

export ORACLE_SID=$1

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u01/app/oracle/product/12.1.0.2/dbhome_1/bin

export LOG_FILE=/u01/app/oracle/dba/log/ORCL_Blocking_Sess_`date +%F%s.log`

sqlplus -s / as sysdba <<EOF >> $LOG_FILE

@/u01/app/oracle/dba/scripts/sql/ReportBlockingSess.sql

EOF

chk_count=`grep "BLOCKING user" $LOG_FILE | wc -l`

if [ $chk_count -gt 0 ]; then

mailx -s "ORCL11: Blocking Session from APP TEL" team@ktexperts.com < $LOG_FILE

find /u01/app/oracle/dba/log -name 'ORCL_Blocking_Sess_*.log' -mmin +5 -exec rm -rf {} \;

exit

cat /u01/app/oracle/dba/scripts/sql/ReportBlockingSess.sql

set serveroutput on long 99999 lines 131 pages 2000 feedback off head off pagesize 0 linesize 200 long 99999

DECLARE

B_USERNAME GV$SESSION.USERNAME%TYPE;

B_SID GV$SESSION.SID%TYPE;

B_SERIAL GV$SESSION.SERIAL#%TYPE;

B_INST_ID GV$SESSION.INST_ID%TYPE;

B_MACHINE GV$SESSION.MACHINE%TYPE;

B_STATUS GV$SESSION.STATUS%TYPE;

B_LAST_CALL_ET GV$SESSION.LAST_CALL_ET%TYPE;

B_PROCESS GV$SESSION.PROCESS%TYPE;

B_SQL_ID GV$SESSION.SQL_ID%TYPE;

B_SQLFULLTEXT GV$SQLAREA.SQL_FULLTEXT%TYPE;

B_MODULE GV$SESSION.MODULE%TYPE;

W_USERNAME GV$SESSION.USERNAME%TYPE;

W_SID GV$SESSION.SID%TYPE;

W_SERIAL GV$SESSION.SERIAL#%TYPE;

W_INST_ID GV$SESSION.INST_ID%TYPE;

W_MACHINE GV$SESSION.MACHINE%TYPE;

W_SECONDS_IN_WAIT GV$SESSION.SECONDS_IN_WAIT%TYPE;

W_PROCESS GV$SESSION.PROCESS%TYPE;

W_SQL_ID GV$SESSION.SQL_ID%TYPE;

W_MODULE GV$SESSION.MODULE%TYPE;

W_SQLFULLTEXT GV$SQLAREA.SQL_FULLTEXT%TYPE;

CURSOR BLOCKING_SESS

SELECT B.USERNAME,B.SID,B.SERIAL#,B.INST_ID,B.MACHINE,B.MODULE,B.STATUS,B.LAST_CALL_ET,B.PROCESS,NVL(B.SQL_ID,B.PREV_SQL_ID),

W.USERNAME,W.SID,W.SERIAL#,W.INST_ID,W.MACHINE,W.MODULE,W.SECONDS_IN_WAIT,W.PROCESS,W.SQL_ID

FROM

(SELECT INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET,MACHINE,MODULE,SECONDS_IN_WAIT,PROCESS,SQL_ID FROM GV$SESSION WHERE BLOCKING_SESSION >0) W,

(SELECT INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET,MACHINE,MODULE,TYPE,PROCESS,SQL_ID,PREV_SQL_ID FROM GV$SESSION) B

where W.USERNAME not in ('SYS','SYSTEM','ktexperts')

and W.BLOCKING_INSTANCE=B.INST_ID

and W.BLOCKING_SESSION=B.SID

and W.SECONDS_IN_WAIT > 300

and B.TYPE not in ('BACKGROUND');

BEGIN

OPEN BLOCKING_SESS;

LOOP

FETCH BLOCKING_SESS INTO B_USERNAME,B_SID,B_SERIAL,B_INST_ID,B_MACHINE,B_MODULE,B_STATUS,B_LAST_CALL_ET,B_PROCESS,B_SQL_ID, W_USERNAME,W_SID,W_SERIAL,W_INST_ID,W_MACHINE,W_MODULE,W_SECONDS_IN_WAIT,W_PROCESS,W_SQL_ID;

EXIT WHEN BLOCKING_SESS%NOTFOUND;

SELECT SQL_FULLTEXT INTO B_SQLFULLTEXT from GV$SQLAREA WHERE SQL_ID=B_SQL_ID and INST_ID=B_INST_ID;

SELECT SQL_FULLTEXT INTO W_SQLFULLTEXT from GV$SQLAREA WHERE SQL_ID=W_SQL_ID and INST_ID=W_INST_ID;

DBMS_OUTPUT.PUT_LINE('BLOCKER '||B_USERNAME ||' Session('||B_SID||','||B_SERIAL||',@'||B_INST_ID||') from SERVER/MODULE: '||B_MACHINE||'/'||B_MODULE||') EXECUTING SQL_ID : '||B_SQL_ID||' is Currently '||B_STATUS||' from last '||B_LAST_CALL_ET||' Secs'||CHR(10));

DBMS_OUTPUT.PUT_LINE('BLOCKERs CLIENT SYSTEM PROCESS/MODULE : '||B_PROCESS||'/'||B_MODULE||CHR(10));

DBMS_OUTPUT.PUT_LINE('And its BLOCKING user '|| W_USERNAME|| ' ('||W_SID||','||W_SERIAL||',@'||W_INST_ID||') EXECUTING SQL_ID : '||W_SQL_ID||' from SERVER '||W_MACHINE||' Since '|| W_SECONDS_IN_WAIT || ' Secs'||CHR(10));

DBMS_OUTPUT.PUT_LINE('BLOCKED CLIENT SYSTEM PROCESS/MODULE : '||W_PROCESS||'/'||W_MODULE||CHR(10));

DBMS_OUTPUT.PUT(CHR(10));

DBMS_OUTPUT.PUT_LINE('********************************************************');

DBMS_OUTPUT.PUT_LINE('SQL Being Executed By The BLOCKER Session : '||B_SQL_ID|| CHR(10));

DBMS_OUTPUT.PUT_LINE('********************************************************');

DBMS_OUTPUT.PUT_LINE(B_SQLFULLTEXT);

DBMS_OUTPUT.PUT(CHR(10));

DBMS_OUTPUT.PUT_LINE('========================================================');

DBMS_OUTPUT.PUT_LINE('SQL Being Executed By The BLOCKED Session : '||W_SQL_ID||CHR(10));

DBMS_OUTPUT.PUT_LINE('========================================================');

DBMS_OUTPUT.PUT_LINE(W_SQLFULLTEXT);

DBMS_OUTPUT.PUT(CHR(10));

END LOOP;

CLOSE BLOCKING_SESS;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('Error : '|| dbms_utility.format_error_stack() || CHR(10) || dbms_utility.format_error_backtrace());

end;