[Oracle] 데이터베이스 모니터링 스크립트(Weekly)

by 딸둘아비 · 2024년 05월 20일

오늘 글은 주간 점검해야 하는 항목들을 확인하는 스크립트입니다. 일/주간/월간 점검항목을 구분하지 않고 매일 점검해도 누가 뭐라할 사람은 없는데, 그래도 쓸데없는 곳에 시간, 에너지, 전력 낭비를 하지 않으려고 구분해 봤습니다. 이 글을 읽고 계시는 분들은 항목을 조정해도 괜찮습니다.

점검항목 타이틀 출력(printTitle.sh)
데이터베이스 기본 정보 확인
일일 점검항목
주간 점검항목
- W01.Display_Diag_Log.sql
- W02.Check_Number_of_Log_Switch_per_Hour.sql
- W03.Check_Invalid_Objects.sql
- W04.Check_Tablespace_Utilization.sql
- W05.Check_Foreign_Keys_wo_Index.sql
- Check_Weekly_Item.sh
월간 점검항목
crontab 등록 및 이메일 전송

주간 점검항목

W01.Display_Diag_Log.sql: v$diag_alert_ext 뷰로부터 전주 일주일 간의 데이터베이스 인스턴스의 작동 상태와 관련된 경고 및 오류 메시지를 확인합니다.

$ vi W01.Display_Diag_Log.sqlcol TIME_STAMP for a20 heading "Time Stamp";
col message_type for 99 heading "Message|Type";
col message_level for 99 heading "Message|Level";
col message_text for a100 heading "Message Text";
SELECT TO_CHAR(originating_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS TIME_STAMP
     , addr, indx, inst_id, message_type, message_level, message_text
FROM   v$diag_alert_ext
WHERE  TRUNC(originating_timestamp, 'IW') = TRUNC(TRUNC(systimestamp, 'IW') - 1, 'IW')
AND    message_level <> 16
ORDER BY originating_timestamp, addr, indx;

W02.Check_Number_of_Log_Switch_per_Hour.sql: v$loghist 뷰로부터 전주 일주일간 발생한 Archive Log의 갯수 및 시간당 갯수, 총 크기를 확인합니다.

$ vi W02.Check_Number_of_Log_Switch_per_Hour.sqlcol TOTAL for 99;
col PER_HOUR for 9.0 heading "Per Hour";
col TOTAL_SIZE_MB for 999.0 heading "Total Size|in GB" ;
SELECT TO_CHAR(first_time,'yyyy-mm-dd') DAY
     , COUNT(*) TOTAL
     , ROUND(COUNT (*) / 24, 1) PER_HOUR
     , ROUND(COUNT (*) * log_size /1024/1024/1024, 1) TOTAL_SIZE_GB
FROM   v$loghist
     , (SELECT AVG (bytes) LOG_SIZE FROM v$log)
WHERE  TRUNC(first_time, 'IW') = TRUNC(TRUNC(SYSDATE, 'IW') - 1, 'IW')
GROUP BY TO_CHAR(first_time,'yyyy-mm-dd'), log_size
ORDER BY DAY;

W03.Check_Invalid_Objects.sql: dba_objects 테이블로부터 스키마별 invaild object 갯수를 확인합니다. invaild object를 재컴파일 방법을 확인합니다.

$ vi W03.Check_Invalid_Objects.sqlcol owner for a10 heading "Owner";
col invalid_view for 9999 heading "Invalid|View";
col invalid_procedure for 9999 heading "Invalid|Procedure";
col invalid_function for 9999 heading "Invalid|Function";
col invalid_synonym for 9999 heading "Invalid|Synonym";
col invalid_trigger for 9999 heading "Invalid|Trigger";
col invalid_package for 9999 heading "Invalid|Package";
col invalid_body for 9999 heading "Invalid|Package Body";
col invalid_etc for 9999 heading "Invalid|ETC";
col invalid_total for 9999 heading "Invalid|Total";
SELECT owner
     , SUM(DECODE(object_type, 'VIEW', 1, 0)) AS invalid_view
     , SUM(DECODE(object_type, 'PROCEDURE', 1, 0)) AS invalid_procedure
     , SUM(DECODE(object_type, 'FUNCTION', 1, 0)) AS invalid_function
     , SUM(DECODE(object_type, 'SYNONYM', 1, 0)) AS invalid_synonym
     , SUM(DECODE(object_type, 'TRIGGER', 1, 0)) AS invalid_trigger
     , SUM(DECODE(object_type, 'PACKAGE', 1, 0)) AS invalid_package
     , SUM(DECODE(object_type, 'PACKAGE BODY', 1, 0)) AS invalid_body
     , SUM(CASE WHEN object_type IN ('VIEW', 'PROCEDURE', 'PACKAGE BODY', 'PACKAGE', 'TRIGGER', 'FUNCTION', 'SYNONYM') THEN 0
                ELSE 1
           END) AS invalid_etc
     , COUNT(*) AS invalid_total
FROM   dba_objects
WHERE  status = 'INVALID'
GROUP BY ROLLUP(owner);

W04.Check_Tablespace_Utilization.sql: 테이블스페이스별로 사용률을 점검하여 증가가 필요한지 점검합니다.

$ vi W04.Check_Tablespace_Utilization.sqlCOLUMN tablespace_name FORMAT a20 HEADING "Tablespace";
COLUMN file_count FORMAT 99 HEADING "File#";
COLUMN total_size_mb FORMAT 999,999 HEADING "Size(MB)";
COLUMN free_size_mb FORMAT 999,999 HEADING "Free(MB)";
COLUMN used_size_mb FORMAT 999,999 HEADING "Used(MB)";
COLUMN used_percentage FORMAT 999.90 HEADING "Used(%)";
COLUMN initial_extent_kb FORMAT 999,999 HEADING "Init(KB)";
COLUMN next_extent_kb FORMAT 999,999 HEADING "Next(KB)";
COLUMN max_extents FORMAT 99999999999 HEADING "Max Ext";
COLUMN pct_increase FORMAT 999 HEADING "PCT Inc";
COLUMN extent_management FORMAT a10 HEADING "Ext. Mang.";
COLUMN allocation_type FORMAT a11 HEADING "Alloc. Type";
SELECT T.tablespace_name
     , TS."file_count"
     , TS."total_size_b"/1048576 AS "total_size_mb"
     , NVL(FS."free_size_b"/1048576, 0) AS "free_size_mb"
     , NVL(US."used_size_b"/1048576, 0) AS "used_size_mb"
     , NVL(ROUND( 100 * US."used_size_b" / TS."total_size_b", 2 ), 0) AS "used_percentage"
     , ( T.initial_extent / 1024 ) AS "initial_extent_kb"
     , ( T.next_extent / 1024 ) AS "next_extent_kb"
     , T.max_extents
     , T.pct_increase
     , T.extent_management
     , T.allocation_type
FROM   dba_tablespaces T,
      (SELECT tablespace_name, COUNT(*) AS "file_count", ROUND(SUM(Bytes), 0) AS "total_size_b"
       FROM dba_data_files
       GROUP BY tablespace_name) TS,
      (SELECT tablespace_name, ROUND(SUM(Bytes), 0) AS "free_size_b"
       FROM dba_free_space
       GROUP BY tablespace_name) FS,
      (SELECT tablespace_name, ROUND(SUM(Bytes), 0) AS "used_size_b"
       FROM dba_segments
       GROUP BY tablespace_name) US
WHERE  T.tablespace_name = TS.tablespace_name
AND    T.tablespace_name = FS.tablespace_name ( + )
AND    T.tablespace_name = US.tablespace_name ( + )
ORDER BY 6 DESC;

W05.Check_Foreign_Keys_wo_Index.sql: Foreign Key에 적절한 인덱스를 생성하면 데이터 무결성 검사 및 DML 작업의 성능을 향상시키고, 잠금 경합을 줄이며, 전체적으로 데이터베이스 시스템의 성능을 최적화할 수 있습니다.

$ vi W05.Check_Foreign_Keys_wo_Index.sqlcol STATUS for a10;
col owner for a10;
col TABLE_NAME for a25;
col FK_NAME for a30;
col FK_COLUMNS for a40;
col INDEX_NAME for a20;
col INDEX_COLUMNS for a40;
DEFINE schemas = '''schema1'', ''schema1'', ''schema1''';
SELECT CASE
          WHEN b.table_name IS NULL THEN
             'unindexed'
          ELSE
             'indexed'
       END AS STATUS
     , a.owner
     , a.table_name AS TABLE_NAME
     , a.constraint_name AS FK_NAME
     , a.fk_columns AS FK_COLUMNS
     , b.index_name AS INDEX_NAME
     , b.index_columns AS INDEX_COLUMNS
FROM
(
   SELECT a.owner, a.table_name, a.constraint_name
        , LISTAGG(a.column_name, ',') WITHIN GROUP (ORDER BY a.position) AS FK_COLUMNS
   FROM   dba_cons_columns a, dba_constraints b
   WHERE  a.constraint_name = b.constraint_name
   AND    b.constraint_type = 'R'
   AND    a.owner IN (&schemas)
   AND    a.owner = b.owner
   GROUP BY a.owner, a.table_name, a.constraint_name
) a,
(
   SELECT table_name, index_name
        , LISTAGG(c.column_name, ',') WITHIN GROUP (ORDER BY c.column_position) AS index_columns
   FROM   dba_ind_columns c
   WHERE  c.index_owner IN (&schemas)
   GROUP BY table_name, index_name
) b
WHERE  a.table_name = b.table_name(+)
AND    b.index_columns(+) like a.fk_columns || '%'
ORDER BY 1 DESC, 2;

Check_Weekly_Item.sh:

$ vi Check_Weekly_Item.sh#!/bin/bash

SCRIPT_PATH=/home/oracle/scripts_for_dbcheck

sqlplus -s '/as sysdba' <<EOF1 >> ${SCRIPT_PATH}/Check_DataBase.result 2>&1

set pagesize 1000 linesize 1000;
set heading on;

HOST ${SCRIPT_PATH}/printTitle.sh 1 "W01. Display Critical and Severe Events"
@${SCRIPT_PATH}/W01.Display_Diag_Log.sql

HOST ${SCRIPT_PATH}/printTitle.sh 1 "W02. Check the Number of Log Switch per Hour"
@${SCRIPT_PATH}/W02.Check_Number_of_Log_Switch_per_Hour.sql

HOST ${SCRIPT_PATH}/printTitle.sh 1 "W03. Check Invalid Obejcts"
@${SCRIPT_PATH}/W03.Check_Invalid_Objects.sql

HOST ${SCRIPT_PATH}/printTitle.sh 1 "W04. Check Tablespace Utilization(less then 90% used)"
@${SCRIPT_PATH}/W04.Check_Tablespace_Utilization.sql

HOST ${SCRIPT_PATH}/printTitle.sh 1 "W05. Check foreign keys without index"
@${SCRIPT_PATH}/W05.Check_Foreign_Keys_wo_Index.sql

exit;
EOF1

[Oracle] 데이터베이스 모니터링 스크립트(Weekly)

주간 점검항목

You may also like...

답글 남기기 응답 취소

최신 글

최신 댓글

[Oracle] 데이터베이스 모니터링 스크립트(Weekly)

주간 점검항목

You may also like...

[Linux]Part1) 원격 파일시스템을 마운팅하기(NFS)

[Linux]FTP 서비스 설치 방법

[Linux]권한 및 소유권 관리를 위한 리눅스 명령어들

답글 남기기 응답 취소

최신 글

최신 댓글