UNIX

Installation of Virtual Box Guest additions on Oracle Enterprise Linux 8.1

1 month ago by Karan RajpootOracle DB Admin

Dear Readers, In this article, we will see Installation of Virtual Box Guest additions on Oracle Enterprise Linux 8.1 Today we will understand the step by step Installation of Virtual Box Guest additions on Oracle Enterprise Linux 8.1. To complete this configuration we need to… 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

BASIC SQL CLASS (Vimeo Links).

2 years ago by Ajay KumarUNIX

BASIC SQL CLASS (Vimeo Links). S.no Video Vimeo Link Password Topic Description 1 Class-1 https://vimeo.com/285140850 sql Introduction to DBMS. What is SQL? History of SQL? Features of SQL? What are Codd Rules? 2 Class-2 https://vimeo.com/285330531 sql Oracle installation on windows platform. Explanation about codd rules.… Read More

Oracle : Shell script for Health Check in HTML Format

2 years 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

2 years 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;

Unix Shell Scripting For DBA’s (PART – 09)

2 years ago by Vinod SiramUNIX

reference of this article from : www.orskl.com CONDITIONS & INTERACTIVE SCRIPTS IF clause Advanced IF usage CASE statements Display user messages User input 1.IF clause General At times you need to specify different courses of action to be taken in a shell script, depending on the… Read More

Unix Shell Scripting For DBA’s (PART – 08)

2 years ago by Vinod SiramUNIX

Unix Shell Scripting For DBA’s (PART – 08) Continuing from previous article. Unix Shell Scripting For DBA’s (PART – 07) 3.AWK programming This filter is among the most powerful filter which in itself a scripting language this filter was invented by three authors A-aho W-whenenberg… Read More

Unix Shell Scripting For DBA’s (PART – 07)

2 years ago by Vinod SiramUNIX

Unix Shell Scripting For DBA’s (PART – 07) GNU GNU stand for “GNU’s Not Unix!” reference of this article from : www.orskl.com It is a recursive acronym chosen because GNU’s design is Unix-like, but differs from Unix by being free software and containing no Unix code. Here we… Read More