实战:oracle巡检脚本v1(十二)
E t.hour_end_time = (TRUNC(arc.next_time(+), 'HH') + (1 / 24)))
WHERE hour_end_time > TRUNC(SYSDATE, 'HH') - 1 - (1 / 24)), v\$instance i
WHERE hour_end_time <= TRUNC(SYSDATE, 'HH')
GROUP BY hour_end_time, i.instance_name
ORDER BY hour_end_time;
spool off
exit;
!01
cechon "5.32 redo log generation in hour is: " red
echo
cat 532.txt
echo
rm -rf 532.txt
#5.33 Redo log switch interval(7days)
sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 533.txt
SELECT B.RECID,B.FIRST_TIME,A.FIRST_TIME,ROUND((A.FIRST_TIME-B.FIRST_TIME)*24*60,2) MINATES
FROM V\$LOG_HISTORY A,
V\$LOG_HISTORY B
WHERE A.RECID=B.RECID +1 AND A.FIRST_TIME>SYSDATE - 20
AND ROUND((A.FIRST_TIME-B.FIRST_TIME)*24*60,2)<30
ORDER BY A.FIRST_TIME DESC;
spool off
exit;
!01
cechon "5.33 Redo log switch interval is: " red
echo
cat 533.txt
echo
rm -rf 533.txt
#5.34 Archive size
sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 534.txt
SELECT
TO_CHAR(first_time,'MM/DD') DAY
--, TO_CHAR(first_time,'YYYY/MM/DD') DAY2
, COUNT(*)||'('||trim(to_char(sum(blocks*block_size)/1024/1024/1024,'99,999.9'))||'G)' TOTAL
FROM
(select max(blocks) blocks,max(block_size) block_size,max(first_time) first_time
from
v\$archived_log a
where COMPLETION_TIME > sysdate - 7
and dest_id = 1
group by sequence#
)
group by to_char(first_time,'MM/DD'), to_char(first_time,'YYYY/MM/DD')
order by TO_CHAR(first_time,'YYYY/MM/DD') desc;
spool off
exit;
!01
cechon "5.34 Archive size is: " red
echo
cat 534.txt
echo
rm -rf 534.txt
###########5.41-5.60 about HIT RATE###########
#5.41 SGA HIT RATIO
sqlplus -S "${ora_user}/${ora_pass} as sysdba" <
/dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 541.txt
select * from
(
SELECT (1 - (phy.value-phyd.value)/( (cur.value + con.value-phyd.value)))*100 "Data Buffer Hit Ratio"
FROM v\$sysstat cur,
v\$sysstat con,
v\$sysstat phy,
v\$sysstat phyd
WHERE cur.name = 'db block gets' AND con.name = 'consistent gets'
AND phy.name = 'physical reads' and phyd.NAME = 'physical reads direct'
),
(
select (sum(pinhits)/sum(pins))*100 "Shared pool Hit Ratio" from v\$librarycache
),
(
select (1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 " Data Dictionary Hit Ratio"
from v\$rowcache
),
(
select ((sum(pins) / (sum(pins) + sum(reloads))) * 100) "Reload Hit Ratio"
from v\$librarycache
);
spool off
exit;
!01
cechon "5.41 SGA HIT RATIO is: " red
echo
echo "--------------------------------------------------------------------"
cechon "Data Buffer Hit Ratio should >95 " yellow
echo
cechon "Shared pool Hit Ratio should >95 " yellow
echo
cechon "Data Dictionary Hit Ratio >95% " yellow
echo
echo "--------------------------------------------------------------------"
cat 541.txt |xargs |awk '{print "Data Buffer Hit Ratio:"$1, \
"\nShared pool Hit Ratio:"$2,"\nData Dictionary Hit Ratio:"$3,"\nReload Hit Ratio:"$4}'
echo
rm -rf 541.txt
#5.42 REDOLOG HIT RATIO
sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 542.txt
SELECT name,
100 - Decode(gets,0,0,misses/(gets+misses))*100 ratio1,
100 - Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses))*100 ratio2
FROM v\$latch WHERE name IN ('redo allocation', 'redo copy');
spool off
exit;
!01