[Oracle] 데이터베이스 모니터링 스크립트(Monthly)
오늘 글은 월간 점검해야 하는 항목들을 확인하는 스크립트입니다. 일/주간/월간 점검항목을 구분하지 않고 매일 점검해도 누가 뭐라할 사람은 없는데, 그래도 쓸데없는 곳에 시간, 에너지, 전력 낭비를 하지 않으려고 구분해 봤습니다. 이 글을 읽고 계시는 분들은 항목을 조정해도 괜찮습니다.
- 점검항목 타이틀 출력(printTitle.sh)
- 데이터베이스 기본 정보 확인
- 일일 점검항목
- 주간 점검항목
- 월간 점검항목
- M01.Display_Diag_Log.sql
- M02.Check_Segments_Reached_Max_Extents.sql
- M03.Gather_Information_of_Schema_Statistics.sql
- M04.Check_Number_of_Tables_wo_block.sql
- M05.Check_Fragmented_Tables.sql
- M06.Check_Database_Resource_Limit.sql
- Check_Monthly_Item.sh
- crontab 등록 및 이메일 전송
월간 점검항목
M01.Display_Diag_Log.sql: v$diag_alert_ext 뷰로부터 전월에 발생한 데이터베이스 인스턴스의 작동 상태와 관련된 경고 및 오류 메시지를 확인합니다.
$ vi M01.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, 'MM') = TRUNC(TRUNC(systimestamp, 'MM') - 1, 'MM') AND message_level <> 16 ORDER BY originating_timestamp, addr, indx;
M02.Check_Segments_Reached_Max_Extents.sql: 세그먼트의 확장 한도에 도달한 경우, 데이터 삽입 또는 업데이트가 실패할 수 있으므로 적절한 조치를 취해야 합니다.
$ vi M02.Check_Segments_Reached_Max_Extents.sqlcol segment_name for a30; SELECT owner , segment_name , segment_type , tablespace_name , next_extent , max_extents FROM dba_segments WHERE max_extents <> -1 AND next_extent >= max_extents ORDER BY owner, segment_name;
M03.Gather_Information_of_Schema_Statistics.sql: dba_tables로부터 스키마의 테이블 통계가 최신 상태인지 확인하고, 필요시 통계를 갱신하기 위한 SQL 명령어를 제공합니다.
$ vi M03.Gather_Information_of_Schema_Statistics.sqlcol owner for a15 heading "Owner"; col LAST_DATE for a10 heading "Last Date"; col EXEC_DBMS_STATS for a150 heading "EXEC DBMS_STATS.GATHER_TABLE_STATS"; SELECT owner , TO_CHAR(MAX(last_analyzed), 'yyyy-mm-dd') AS LAST_DATE , 'EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>''' || owner || ''', estimate_percent=>10, degree=>4);' AS EXEC_DBMS_STATS FROM dba_tables GROUP BY owner ORDER BY 1 ASC;
M04.Check_Number_of_Tables_wo_block.sql: dba_tables로부터 테이블의 블록 수가 0이거나 NULL인 경우를 점검하여 불필요한 테이블을 정리하고, 데이터베이스의 전반적인 효율성과 안정성을 향상시킬 수 있습니다.
$ vi M04.Check_Number_of_Tables_wo_block.sqlcol owner for a15 heading "Owner"; col tablespace_name for a30 heading "Tablespace"; col CNT for 9999 heading "# of no block"; SELECT owner , tablespace_name , COUNT(*) AS CNT FROM dba_tables WHERE (blocks IS NULL OR blocks = 0) GROUP BY owner, tablespace_name ORDER BY owner ASC;
M05.Check_Fragmented_Tables.sql: 테이블의 조각화 정도를 분석하여 재구성할 필요가 있는 테이블을 확인합니다. 아래 쿼리는 블록수 1000개 이상, 조각화 비율 20% 이상인 테이블을 조회합니다.
$ vi M05.Check_Fragmented_Tables.sqlcol owner for a10 heading "Owner"; col table_name for a30 heading "Table Name"; col TOTAL_SIZE_MB for 999999999999 heading "Total Size|in MB"; col ACTUAL_SIZE_MB for 999999999999 heading "Actual Size|in MB"; col FRAGMENTED_SPACE_MB for 999999999999 heading "Fragmented Space|in MB"; col FRAGMENTED_RATIO for 999.90 heading "Fragment(%)"; SELECT owner , table_name , blocks , num_rows , avg_row_len , ROUND(((blocks*8*1024)/1024/1024),0) AS TOTAL_SIZE_MB , ROUND((num_rows*avg_row_len/1024/1024),0) AS ACTUAL_SIZE_MB , ROUND(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),0) AS FRAGMENTED_SPACE_MB , ROUND(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024))/((blocks*8*1024)/1024/1024)*100, 2) AS FRAGMENTED_RATIO FROM dba_tables WHERE owner NOT IN ('SYS','SYSTEM','FDBA','PERFSTAT','DBMON') AND blocks > 1000 AND ROUND(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024))/((blocks*8*1024)/1024/1024)*100, 2) > 20; ORDER BY 1 ASC, 9 DESC; SELECT 'EXEC dbms_stats.gather_table_stats(ownname=>''' || owner || '''' || ', tabname=>''' || table_name || ''', cascade=>true, estimate_percent=>10' || ', method_opt=>''for all indexed columns size 1'', granularity=>''ALL'', degree=>1);' AS query FROM dba_tables WHERE owner NOT IN ('SYS','SYSTEM','FDBA','PERFSTAT','DBMON') AND blocks > 1000 AND ROUND(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024))/((blocks*8*1024)/1024/1024)*100, 2) > 20;
M06.Check_Database_Resource_Limit.sql: 자원의 현재 사용량이 최대 한도에 가까워지면 프로세스나 세션의 수를 늘리거나, 애플리케이션의 자원 사용을 최적화하는 등의 조치를 취할 수 있습니다.
$ vi M06.Check_Database_Resource_Limit.sqlSELECT resource_name , current_utilization , max_utilization , initial_allocation , limit_value FROM v$resource_limit WHERE resource_name IN ( 'processes', 'sessions' );
Check_Monthly_Item.sh:
$ vi Check_Monthly_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 "M01. Display Critical and Severe Events for Previous Month" @${SCRIPT_PATH}/M01.Display_Diag_Log.sql HOST ${SCRIPT_PATH}/printTitle.sh 1 "M02. Check Segments Reached Max Extents" @${SCRIPT_PATH}/M02.Check_Segments_Reached_Max_Extents.sql HOST ${SCRIPT_PATH}/printTitle.sh 1 "M03. Gather the Information of Schema Statistics" @${SCRIPT_PATH}/M03.Gather_Information_of_Schema_Statistics.sql HOST ${SCRIPT_PATH}/printTitle.sh 1 "M04. Check the Number of Tables without Any Block" @${SCRIPT_PATH}/M04.Check_Number_of_Tables_wo_block.sql HOST ${SCRIPT_PATH}/printTitle.sh 1 "M05. Check the Fragmented Tables more than 1000 blocks and 20% of fragmented ratio" @${SCRIPT_PATH}/M05.Check_Fragmented_Tables.sql HOST ${SCRIPT_PATH}/printTitle.sh 1 "M06. Check Database Resource Limit" @${SCRIPT_PATH}/M06.Check_Database_Resource_Limit.sql exit; EOF1
crontab 등록 및 이메일 전송
crontab 등록 규칙
- 데이터베이스 기본 정보 확인(Check_Basic_Info.sh) 매일 실행
- 일일 점검항목(Check_Daily_Item.sh) 매일 실행
- 주간 점검항목(Check_Weekly_Item.sh) 매주 월요일 실행
- 월간 점검항목(Check_Monthly_Item.sh) 매월 1일 실행
$ crontab -e0 1 * * * su - oracle -e /path/to/Check_Basic_Info.sh 0 2 * * * su - oracle -e /path/to/Check_Daily_Item.sh 0 3 * * 1 su - oracle -e /path/to/Check_Weekly_Item.sh 0 4 1 * * su - oracle -e /path/to/Check_Monthly_Item.sh
이메일 전송
쉽게 따라하는 Linux 서버에서 메일 보내기(postfix 사용) 글을 보시면 자세하게 설명되어 있습니다. 점검결과를 이메일로 받아보면 매번 로그인해서 결과를 확인할 필요가 없습니다.
