实战:oracle巡检脚本v1(十一)
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 522.txt
SELECT SUBSTR(A.FILE#,1,2) "#", SUBSTR(A.NAME,1,30) "NAME",
A.STATUS, A.BYTES, B.PHYRDS, B.PHYBLKRD PBR, B.PHYWRTS, B.PHYBLKWRT PBW
FROM V\$DATAFILE A,
V\$FILESTAT B
WHERE A.FILE# = B.FILE#;
spool off
exit;
!01
cechon "5.22 file system I/O ratio is: " red
echo
cat 522.txt
echo
rm -rf 522.txt
#5.23 file system I/O 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 523.txt
SELECT SUBSTR(A.FILE#,1,2) "#", SUBSTR(A.NAME,1,30) "NAME",
A.STATUS, A.BYTES, B.PHYRDS, B.PHYBLKRD PBR, B.PHYWRTS, B.PHYBLKWRT PBW
FROM V\$DATAFILE A,
V\$FILESTAT B
WHERE A.FILE# = B.FILE#;
spool off
exit;
!01
cechon "5.23 file system I/O ratio is: " red
echo
cat 523.txt
echo
rm -rf 523.txt
##########5.31-5.40 about REDO##########
#5.31 The current redo log file usage
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 531.txt
select le.leseq "Current log sequence No",
100 * cp.cpodr_bno / le.lesiz "Percent Full",
(cpodr_bno - 1) * 512 "bytes used exclude header",
le.lesiz * 512 - cpodr_bno * 512 "Left space",
le.lesiz *512 "logfile size"
from x\$kcccp cp, x\$kccle le
where LE.leseq = CP.cpodr_seq
and bitand(le.leflg, 24) = 8;
spool off
exit;
!01
cechon "5.31 The current redo log file usage is: " red
echo
cat 531.txt
echo
rm -rf 531.txt
#5.32 redo log generation in hour
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 532.txt
WITH times AS
(SELECT /*+ MATERIALIZE */
hour_end_time
FROM (SELECT (TRUNC(SYSDATE, 'HH') + (2 / 24)) - (ROWNUM / 24) hour_end_time
FROM DUAL
CONNECT BY ROWNUM <= (1 * 24) + 3),
v\$database
WHERE log_mode = 'ARCHIVELOG')
SELECT hour_end_time, NVL(ROUND(SUM(size_mb), 3), 0) size_mb, i.instance_name
FROM(
SELECT hour_end_time, CASE WHEN(hour_end_time - (1 / 24)) >
lag_next_time THEN(next_time + (1 / 24) - hour_end_time) * (size_mb / (next_time - lag_next_time)) ELSE 0 END + CASE WHEN hour_end_time < lead_next_time THEN(hour_end_time - next_time) * (lead_size_mb / (lead_next_time - next_time)) ELSE 0 END + CASE WHEN lag_next_time > (hour_end_time - (1 / 24)) THEN size_mb ELSE 0 END + CASE WHEN next_time IS NULL THEN(1 / 24) * LAST_VALUE(CASE WHEN next_time IS NOT NULL AND lag_next_time IS NULL THEN 0 ELSE(size_mb / (next_time - lag_next_time)) END IGNORE NULLS) OVER(
ORDER BY hour_end_time DESC, next_time DESC) ELSE 0 END size_mb
FROM(
SELECT t.hour_end_time, arc.next_time, arc.lag_next_time, LEAD(arc.next_time) OVER(
ORDER BY arc.next_time ASC) lead_next_time, arc.size_mb, LEAD(arc.size_mb) OVER(
ORDER BY arc.next_time ASC) lead_size_mb
FROM times t,(
SELECT next_time, size_mb, LAG(next_time) OVER(
ORDER BY next_time) lag_next_time
FROM(
SELECT next_time, SUM(size_mb) size_mb
FROM(
SELECT DISTINCT a.sequence#, a.next_time, ROUND(a.blocks * a.block_size / 1024 / 1024) size_mb
FROM v\$archived_log a,(
SELECT /*+ no_merge */
CASE WHEN TO_NUMBER(pt.VALUE) = 0 THEN 1 ELSE TO_NUMBER(pt.VALUE) END VALUE
FROM v\$parameter pt
WHERE pt.name = 'thread') pt
WHERE a.next_time > SYSDATE - 3 AND a.thread# = pt.VALUE AND ROUND(a.blocks * a.block_size / 1024 / 1024) > 0)
GROUP BY next_time)) arc
WHER