Oracle Archive log 사용량 확인(GV$ARCHIVED_LOG)

DB Version : Oracle 11.2.0.2

OS Version : AIX 6.1

* Oracle Archive log 사용량 및 갯수 확인

  - DATE Format : YY/MM/DD (DY)

  - 시간 단위 분석 : (TO_CHAR(NEXT_TIME,'YY/MM/DD(DY) HH24')

  - 4Node RAC 기준으로 Script 작성

  - Archive log Size 확인(GB) : ROUND(SUM(DISTINCT BLOCKS*BLOCK_SIZE)/1024/1024/1024,2)

  - 최근 10일 전부터 분석 : NEXT_TIME >= TRUNC(SYSDATE) -10

SELECT
  TO_CHAR(NEXT_TIME,'YY/MM/DD(DY) HH24') "NEXT_TIME"
, COUNT(DISTINCT NAME) "TOTAL_ARC_COUNT"
, ROUND(SUM(DISTINCT BLOCKS*BLOCK_SIZE)/1024/1024/1024,2) "TOTAL_SIZE_GB"
, SUM(CASE WHEN INST_ID = 1 AND THREAD# = 1 THEN 1 ELSE 0 END) "RAC1"
, SUM(CASE WHEN INST_ID = 2 AND THREAD# = 2 THEN 1 ELSE 0 END) "RAC2"
, SUM(CASE WHEN INST_ID = 3 AND THREAD# = 3 THEN 1 ELSE 0 END) "RAC3"
, SUM(CASE WHEN INST_ID = 4 AND THREAD# = 4 THEN 1 ELSE 0 END) "RAC4"
FROM GV$ARCHIVED_LOG
WHERE NEXT_TIME >= TRUNC(SYSDATE) -10
GROUP BY TO_CHAR(NEXT_TIME,'YY/MM/DD(DY) HH24')
ORDER BY 1;