[Script] Oracle RAC 監控scripts for ASM disks

--查詢Oracle RAC nodes 半天內的archive 個別異動量

col day for a15

SELECT A.*,

Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb

FROM

(

SELECT THREAD#, To_Char(First_Time,'YYYY-MM-DD') DAY, Count(1) Count#, Min(RECID) Min#, Max(RECID) Max#

FROM v$log_history where First_Time > (sysdate - 0.5) GROUP BY THREAD#, To_Char(First_Time,'YYYY-MM-DD') ORDER BY 1,2 ASC) A,

(SELECT Avg(BYTES) AVG#,Count(1) Count#,Max(BYTES) Max_Bytes,Min(BYTES) Min_Bytes FROM v$log) B ;

--查詢Oracle RAC nodes 半天內的archive 總異動量

col day for a15

select DAY , SUM(DAILY_AVG_MB) from

(

SELECT A.*,

Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb

FROM

(

SELECT THREAD#, To_Char(First_Time,'YYYY-MM-DD') DAY, Count(1) Count#, Min(RECID) Min#, Max(RECID) Max#

FROM v$log_history where First_Time > (sysdate - 0.5) GROUP BY THREAD#, To_Char(First_Time,'YYYY-MM-DD') ORDER BY 1,2 ASC) A,

(SELECT Avg(BYTES) AVG#,Count(1) Count#,Max(BYTES) Max_Bytes,Min(BYTES) Min_Bytes FROM v$log) B

) group by DAY;