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

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

오늘 글은 매일 점검해야 하는 항목들을 확인하는 스크립트입니다. 출력값이 아직 눈에 익지도 않고, 정상적인 값인지도 잘 모르지만 모니터링하다보면 익숙해지겠지요. 분기별로 방문/점검해 주시는 DBA분으로부터 노하우를 전수받아야겠습니다.

점검항목 타이틀 출력(printTitle.sh)
데이터베이스 기본 정보 확인
일일 점검항목
- D01-1.Check_Ojects_Locked.sql
- D01-2.Kill_Process_wo_Session.sql
- D02.Display_Diag_Log.sql
- D03-1.Dipaly_All_Scheduler.sql
- D03-2.Display_Schedule_Window.sql
- D03-3.Dispay_Schedule_Jobs.sql
- D03-4.Check_History_of_Scheduler_Job.sql
- D03-5.Check_Log_Status_of_Scheduler_Job.sql
- D04.Display_Rollback_Information.sql
- D05.Display_Top20_CPU_Consuming_Session.sql
- D06.Display_Top20_Resource_Comsuming_SQL_ID.sql
- D07-1.Display_Configuration_n_Destination_of_Archive_Log.sql
- D07-2.Check_Number_of_Log_Switch_per_Hour.sql
- D08.Dispay_Top50_Obejct_Blocks_in_Data_Buffer.sql
- D09.Display_Hit_Ratio.sql
- D10-1.Display_Summary_of_Disk_Usage.sql
- D10-2.Display_Disk_Usage_from_DBA_DATA_FILES_n_DBA_FREE_SPACE.sql
- D10-3.Display_Disk_Usage_from_DBA_TABLESPACE_USAGE_METRICS.sql
- D11.Check_Usage_of_FRA.sql
- D12.Check_Usage_of_SGA.sql
- Check_Daily_Item.sh
주간 점검항목
월간 점검항목
crontab 등록 및 이메일 전송

일일 점검항목

D01-1.Check_Ojects_Locked.sql: Lock이 걸린 오브젝트를 표시하고, 해당 세션을 수동으로 종료시키는 방법을 제공합니다.(serial#, program를 기록해 놓고, 아래 D01-2.Kill_Process_wo_Session.sql의 결과도 확인합니다.)

$ vi D01-1.Check_Ojects_Locked.sqlcol lock_time for a20 heading "Lock time";
col CMD_KILL for a100 heading "SQL Command for Killing";
col CMD_DISCONNECT for a100 heading "SQL Command for Disconnecting";
SELECT /*+ ordered */
       NVL (s.blocking_session, s.sid) AS lck_hold
     , CASE WHEN s.blocking_session IS NOT NULL THEN s.sid END AS lck_wait
     , s.username
     , s.osuser
     , s.status
     , TRUNC(l.ctime/60)||'Min '||TRUNC(MOD(l.ctime, 60))||'Sec' lock_time
     , DECODE (lmode, 0, 'NONE',
                      1, 'NULL',
                      2, 'row-S(SS)',
                      3, 'row-X(SX)',
                      4, 'share(S)',
                      5, 'S/Row-X(SSX)',
                      6, 'exclusive(X)') lmode
      , DECODE (l.request, 0, 'NONE',
                           1, 'NULL',
                           2, 'row-S(SS)',
                           3, 'row-X(SX)',
                           4, 'share(S)',
                           5, 'S/Row-X(SSX)',
                           6, 'exclusive(X)') REQUEST
      , s.serial#
      , s.program
      , s.sql_id
      , 'ALTER SYSTEM KILL SESSION '''
        || s.sid
        || ','
        || s.serial#
        || ',@'
        || s.INST_ID
        || ''';'  CMD_KILL
      , 'ALTER SYSTEM DISCONNECT SESSION '''
        || s.sid
        || ','
        || s.serial#
        || ''' IMMEDIATE;'  CMD_DISCONNECT
FROM  GV$lock l,
      GV$session s
WHERE 1 = 1
AND   l.inst_id = s.inst_id
AND   l.sid = s.sid
AND   l.TYPE IN ('TM', 'TX', 'UL')
AND   s.TYPE != 'BACKGROUND'
ORDER BY lock_time DESC, lck_hold;

D01-2.Kill_Process_wo_Session.sql: 위 스크립트에서 확인한 serial#, program애 대응하는 프로세스ID(pid)를 확인합니다. 수동으로 해당 세션을 종료시킨 이후에도 프로세스가 살이 있는지 ps -p pid 명령어로 확인해 봅니다.

$ vi D01-2.Kill_Process_wo_Session.sqlcol program for a30;
col background for a10;
SELECT p.spid, p.username, p.serial#, p.program, p.background, 'kill -9 '||p.spid AS SHELL_COMMAND
FROM   v$process p
WHERE NOT EXISTS (SELECT 1
                  FROM v$session s
                  WHERE s.paddr = p.addr) ;

D02.Display_Diag_Log.sql: v$diag_info 뷰로부터 Oracle Automatic Diagnostic Repository (ADR)의 디렉토리 경로 및 설정에 대한 정보를 확인하고, v$diag_alert_ext 뷰로부터 데이터베이스 인스턴스의 작동 상태와 관련된 경고 및 오류 메시지를 확인합니다.

$ vi D02.Display_Diag_Log.sqlcol name for a25;
col value for a70;
SELECT * FROM v$diag_info;
col 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";
col component_id for a10 heading "Component|ID";
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) = TRUNC(systimestamp - 1)
AND    message_level <> 16
ORDER BY originating_timestamp, addr, indx;

D03-1.Dipaly_All_Scheduler.sql: dba_scheduler_schedules 테이블로부터 데이터베이스에 정의된 모든 스케줄에 대한 정보를 확인할 수 있습니다.

$ vi D03-1.Dipaly_All_Scheduler.sqlcol schedule_name for a34;
col start_date for a20;
col repeat_interval for a45;
SELECT schedule_name, schedule_type, TO_CHAR(start_date, 'YYYY-MM-DD HH24:MI:SS') start_date, repeat_interval
FROM   dba_scheduler_schedules;

D03-2.Display_Schedule_Window.sql: dba_scheduler_windows 테이블을 사용하여 시간 기반으로 작업을 조정하고 스케줄에 따라 리소스를 할당하는 데 사용됩니다.

$ vi D03-2.Display_Schedule_Window.sqlcol window_name for a25;
col resource_plan for a30;
col repeat_interval for a80;
col enabled for a5;
col active for a5;
SELECT window_name, resource_plan, repeat_interval
     , TO_CHAR(last_start_date, 'YYYY-MM-DD') LAST_DATE
     , TO_CHAR(next_start_date, 'YYYY-MM-DD') NEXT_DATE
     , enabled, active
FROM   dba_scheduler_windows;

D03-3.Dispay_Schedule_Jobs.sql: dba_scheduler_jobs 테이블에는 Scheduler에 의해 생성된 모든 작업에 대한 정보가 포함되어 있습니다.

$ vi D03-3.Dispay_Schedule_Jobs.sqlcol window_name for a25;
col resource_plan for a30;
col schedule_name for a35;
col repeat_interval for a80;
col enabled for a5;
col active for a5;
SELECT window_name, resource_plan, schedule_name, repeat_interval
     , TO_CHAR(last_start_date, 'YYYY-MM-DD') LAST_DATE
     , TO_CHAR(next_start_date, 'YYYY-MM-DD') NEXT_DATE
     , enabled, active
FROM   dba_scheduler_windows;

D03-4.Check_History_of_Scheduler_Job.sql: dba_scheduler_job_run_details 테이블로부터 스케줄된 작업의 실행 기록을 확인하거나 작업의 성능 및 상태를 모니터링할 수 있습니다.

$ vi D03-4.Check_History_of_Scheduler_Job.sqlcol job_name for a35;
col status for a10;
col run_duration for a34;
SELECT job_name,status,TO_CHAR(actual_start_date, 'YYYY-MM-DD HH24:MI:SS') start_date, run_duration
FROM   dba_scheduler_job_run_details
WHERE  TO_CHAR(actual_start_date, 'YYYYMMDD') = TO_CHAR(sysdate - 1, 'YYYYMMDD')
ORDER BY actual_start_date desc;

D03-5.Check_Log_Status_of_Scheduler_Job.sql: dba_scheduler_job_log 테이블로부터 스케줄된 작업의 성공 또는 실패, 실행 문제, 오류 메시지 등을 추적할 수 있습니다.

$ vi D03-5.Check_Log_Status_of_Scheduler_Job.sqlcol job_name for a35;
col log_date for a25;
col status for a10;
col operation for a10;
SELECT job_name
     , to_char(log_date, 'YYYY-MM-DD HH24:MI:SS') log_date
     , status
     , operation
FROM   dba_scheduler_job_log
WHERE  TO_CHAR(log_date, 'YYYYMMDD') = TO_CHAR(sysdate - 1, 'YYYYMMDD')
ORDER BY log_date desc;

D04.Display_Rollback_Information.sql: dba_rollback_segs 테이블로부터 롤백 세그먼트에 대한 정보를 쿼리할 수 있습니다.

$ vi D04.Display_Rollback_Information.sqlcol username for a15;
SELECT s.username
     , s.sid
     , s.serial#
     , t.used_ublk
     , t.used_urec
     , rs.segment_name
     , r.rssize
     , r.status
FROM   v$transaction t, v$session s, v$rollstat r, dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
  AND  t.xidusn = r.usn
  AND  rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;

D05.Display_Top20_CPU_Consuming_Session.sql: CPU를 점유하는 Top 20 세션 정보를 출력합니다.

$ vi D05.Display_Top20_CPU_Consuming_Session.sqlcol program for a30;
col event for a40;
col CPUMins heading "CPU|in Mins" for 99990;
SELECT rownum as rank, a.*
FROM (
SELECT v.sid, sess.serial# , sess.program, sess.event, ROUND(v.value / (100 * 60), 1) CPUMins
FROM   v$statname s , v$sesstat v, v$session sess
WHERE s.name = 'CPU used by this session'
  AND sess.sid = v.sid
  AND v.statistic#=s.statistic#
  AND v.value > 0
ORDER BY v.value DESC) a
WHERE ROWNUM < 21;

D06.Display_Top20_Resource_Comsuming_SQL_ID.sql: v$sqlstats 뷰로부터 실행된 SQL 문의 통계 정보를 사용하여 각 SQL 문의 실행에 대한 성능 데이터를 조회할 수 있습니다.

$ vi D06.Display_Top20_Resource_Comsuming_SQL_ID.sqlcol RANK for 99;
col LAST_ACTIVE_TIME heading "Last Active Time";
col SQL_TEXT for a50 heading "SQL Text";
col disk_reads for 99999999999;
col direct_writes for 99999999999;
col buffer_gets for 99999999999;
col rows_processed for 999999999999999;
col application_wait_time heading "Application|Wait Time";
col user_io_wait_time heading "User IO|Wait Time";
col concurrency_wait_time heading "Concurrency|Wait Time";
col avg_elapsed_time_in_sec heading "AVG Elapsed|Time in Sec";
col CPU_HR heading "CPU|in HR";
SELECT ROWNUM AS RANK, a.* 
FROM (
SELECT sql_id
     , TO_CHAR(last_active_time, 'YYYY-MM-DD HH24:MI:SS') AS LAST_ACTIVE_TIME
     , LPAD(sql_text,50) AS SQL_TEXT
     , disk_reads
     , direct_writes
     , buffer_gets
     , rows_processed
     , application_wait_time
     , user_io_wait_time
     , concurrency_wait_time
     , executions
     , ROUND(DECODE(executions, 0, null, elapsed_time / executions / 1000), 0) AS avg_elapsed_time_in_sec
     , ROUND(cpu_time/1000000/60/60, 2) AS CPU_HR
FROM v$sqlstats
ORDER BY cpu_time DESC) a
WHERE ROWNUM < 21;

D07-1.Display_Configuration_n_Destination_of_Archive_Log.sql: Archive Log의 구성 환경 및 저장 위치를 확인합니다.

$ vi D07-1.Display_Configuration_n_Destination_of_Archive_Log.sqlarchive log list;
column dest_name format a20;
column destination format a40;
column status format a10;
column error format a10;
SELECT dest_name, destination, status, target, archiver, error, schedule, valid_now, db_unique_name, applied_scn
FROM   v$archive_dest
WHERE  valid_now = 'YES';

D07-2.Check_Number_of_Log_Switch_per_Hour.sql: 전일 발생한 Archive Log의 갯수 및 시간당 갯수, 총 크기를 확인합니다.

$ vi D07-2.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) = TRUNC(SYSDATE - 1)
GROUP BY TO_CHAR(first_time,'yyyy-mm-dd'), log_size
ORDER BY DAY;

D08.Dispay_Top50_Obejct_Blocks_in_Data_Buffer.sql:

$ vi D08.Dispay_Top50_Obejct_Blocks_in_Data_Buffer.sqlcolumn owner       heading "Owner" format a12;
column object_name heading "Object|Name" format a30;
column object_type heading "Object|Type" format a8;
column num_blocks  heading "Number of|Blocks in|Buffer|Cache" format 99,999,999;
column percent heading "Percentage|of object blocks|in Buffer" format 999.90;
column buffer_pool heading "Buffer|Pool" format a7;
column block_size heading "Block|Size" format 99,999;
SELECT *
FROM
(
SELECT t1.owner
     , t1.object_name,
       CASE WHEN t1.object_type = 'TABLE PARTITION' THEN 'TAB PART'
            WHEN t1.object_type = 'INDEX PARTITION' THEN 'IDX PART'
            ELSE t1.object_type END AS object_type
     , SUM(t1.num_blocks) AS num_blocks
     , (SUM(t1.num_blocks)/greatest(sum(s.blocks), .001))*100 AS percent
     , s.buffer_pool
     , SUM(s.bytes)/SUM(s.blocks) AS block_size
FROM  (SELECT o.owner owner, o.object_name object_name, o.subobject_name subobject_name,
              o.object_type object_type, COUNT(DISTINCT file# || block#) num_blocks
       FROM   dba_objects o, v$bh bh
       WHERE  o.data_object_id  = bh.objd
         AND  o.owner NOT IN ('SYS','SYSTEM')
         AND  bh.status != 'free'
       GROUP BY o.owner, o.object_name, o.subobject_name, o.object_type) t1
     , dba_segments s
WHERE  s.segment_name = t1.object_name
  AND  s.owner = t1.owner
  AND  s.segment_type = t1.object_type
  AND  NVL(s.partition_name,'-') = NVL(t1.subobject_name,'-')
GROUP BY t1.owner, t1.object_name, t1.object_type, s.buffer_pool
ORDER BY SUM(t1.num_blocks) desc
)
WHERE ROWNUM < 51;

D09.Display_Hit_Ratio.sql: Buffer Cache, Library Cache 및 Data Dictionary Cache의 Hit Ratio를 점검합니다.

$ vi D09.Display_Hit_Ratio.sqlcol INSTANCE for 9 heading "Instance ID"
col SGA for a35;
col "Hit Ratio(%)" for 999.90;
SELECT inst_id AS INSTANCE, 'Buffer Cache Hit Ratio' AS "SGA"
     , ROUND(((1-(SUM(DECODE(name, 'physical reads', value,0))/
      (SUM(DECODE(name, 'db block gets', value,0))+
      (SUM(DECODE(name, 'consistent gets', value, 0))))))*100), 2) AS "Hit Ratio(%)"
FROM   gv$sysstat
GROUP BY inst_id
UNION ALL
SELECT inst_id, 'Library Cache Hit Ratio'
     , ROUND((1-SUM (reloads)/SUM(pins))*100, 2)
FROM   gv$librarycache
GROUP BY inst_id
UNION ALL
SELECT inst_id, 'Data Dictionary Cache Hit Ratio'
     ,  ROUND((1-SUM(getmisses)/SUM(gets))*100, 2)
FROM   gv$rowcache
GROUP BY inst_id;

D10-1.Display_Summary_of_Disk_Usage.sql:

$ vi D10-1.Display_Summary_of_Disk_Usage.sqlcol "Total_in_GB" format 9999.90 heading "Total|in GB";
col "Used_in_GB"  format 9999.90 heading "Used|in GB";
col "Free_in_GB"  format 9999.90 heading "Free|in GB";
col "Temp_in_GB"  format 9999.90 heading "Temporary|in GB";
col "Log_in_GB"  format 9999.90 heading "Log|in GB";
SELECT (SELECT ROUND(SUM(bytes/1024/1024/1024), 2) FROM v$datafile) AS "Total_in_GB"
     , (SELECT ROUND(SUM(bytes/1024/1024/1024), 2) FROM v$datafile) -
       (SELECT ROUND(SUM(bytes/1024/1024/1024), 2) FROM dba_free_space) AS "Used_in_GB"
     , (SELECT ROUND(SUM(bytes/1024/1024/1024), 2) FROM dba_free_space) AS "Free_in_GB" 
     , (SELECT ROUND(SUM(bytes/1024/1024/1024), 2) FROM v$tempfile) AS "Temp_in_GB"
     , (SELECT ROUND(SUM(bytes/1024/1024/1024), 2) FROM v$log) AS "Log_in_GB"
FROM   dual;

D10-2.Display_Disk_Usage_from_DBA_DATA_FILES_n_DBA_FREE_SPACE.sql: DBA_DATA_FILES 테이블과 DBA_FREE_SPACE 테이블을 사용하여 테이블스페이스의 사용량, 여유공간, 사용률 등을 확인합니다.

$ vi D10-2.Display_Disk_Usage_from_DBA_DATA_FILES_n_DBA_FREE_SPACE.sqlcol "TABLESPACE_NAME" format a20 heading "Tablespace|Name";
col "Total_in_GB" format 9999.90 heading "Total|in GB";
col "Used_in_GB"  format 9999.90 heading "Used|in GB";
col "Free_in_GB"  format 9999.90 heading "Free|in GB";
col "Used_Percent"  format 999.90 heading "Used(%)";
SELECT A.TABLESPACE_NAME
     , ROUND(SUM(A.TOTALSIZE), 2) "Total_in_GB"
     , ROUND(SUM(NVL((A.TOTALSIZE - C.FREESIZE),0)), 2) "Used_in_GB"
     , ROUND(SUM(C.FREESIZE), 2) "Free_in_GB"
     , ROUND(SUM(NVL((A.TOTALSIZE - C.FREESIZE),0))/SUM(A.TOTALSIZE)*100, 2) "Used_Percent"
     , (SELECT COUNT(*) FROM DBA_DATA_FILES WHERE AUTOEXTENSIBLE = 'YES' AND TABLESPACE_NAME = A.TABLESPACE_NAME) "AUTO_ON"
     , (SELECT COUNT(*) FROM DBA_DATA_FILES WHERE AUTOEXTENSIBLE = 'NO' AND TABLESPACE_NAME = A.TABLESPACE_NAME) "AUTO_OFF"
FROM ( SELECT TABLESPACE_NAME,
              SUM(BYTES)/1024/1024/1024 "TOTALSIZE"
       FROM DBA_DATA_FILES
       GROUP BY TABLESPACE_NAME) A,
     ( SELECT TABLESPACE_NAME,
              SUM(BYTES)/1024/1024/1024 "FREESIZE"
       FROM DBA_FREE_SPACE
       GROUP BY TABLESPACE_NAME) C
WHERE 1=1
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+)
GROUP BY A.TABLESPACE_NAME
ORDER BY 5 desc;

D10-3.Display_Disk_Usage_from_DBA_TABLESPACE_USAGE_METRICS.sql: DBA_TABLESPACE_USAGE_METRICS 테이블의 정보를 사용하여 테이블스페이스의 용량 관리 및 공간 부족 문제를 식별하고 예방할 수 있습니다.

$ vi D10-3.Display_Disk_Usage_from_DBA_TABLESPACE_USAGE_METRICS.sqlcol "TABLESPACE_NAME" format a20 heading "Tablespace|Name";
col "Allocated_in_GB" format 9999.90 heading "Allocated|in GB";
col "Used_in_GB" format 9999.90 heading "Used|in GB";
col "Free_in_GB" format 9999.90 heading "Free|in GB";
col "Used_Percent" format 999.90 heading "Used(%)";
SELECT A.TABLESPACE_NAME
     , ROUND(TABLESPACE_SIZE*B.BSIZE/1024/1024/1024, 2) "Allocated_in_GB"
     , ROUND(USED_SPACE*B.BSIZE/1024/1024/1024, 2) "Used_in_GB"
     , ROUND((TABLESPACE_SIZE-USED_SPACE)*B.BSIZE/1024/1024/1024, 2) "Free_in_GB"
     , ROUND(USED_PERCENT, 2) "Used_Percent"
FROM DBA_TABLESPACE_USAGE_METRICS A,
    (SELECT VALUE BSIZE FROM V$PARAMETER WHERE NAME='db_block_size') B
ORDER BY 5 DESC;

D11.Dispay_Top50_Obejct_Blocks_in_Data_Buffer.sql:

D11.Check_Usage_of_FRA.sql: v$recovery_file_dest 뷰를 사용하여 Fast Recover Area가 충분한 공간을 제공하고 있는지, 필요한 파일들이 올바르게 관리되고 있는지를 모니터링할 수 있습니다.

$ vi D11.Check_Usage_of_FRA.sqlcol name for a45;
col ALLOCATED heading "Allocated|Space(GB)" for 999.0;
col USED heading "Used|Space(GB)" for 999.0;
col RECLAIMABLE heading "SPACE|RECLAIMABLE(GB)" for 999.0;
col ESTIMATED heading "Estimated|Space(GB)" for 999.0;
SELECT name
     , ROUND(space_limit/1024/1024/1024,1) AS ALLOCATED
     , ROUND(space_used/1024/1024/1024,1) AS USED
     , ROUND(space_reclaimable/1024/1024/1024,1) AS RECLAIMABLE
     , (SELECT ROUND(estimated_flashback_size/1024/1024/1024,1) FROM v$flashback_database_log) AS ESTIMATED
FROM v$recovery_file_dest;

D12.Check_Usage_of_SGA.sql: SGA(System Global Area)는 Oracle 인스턴스에서 공유되는 메모리 영역으로, v$sgastat 뷰를 사용하여 메모리 사용량을 모니터링하고 최적화할 수 있습니다.

$ vi D12.Check_Usage_of_SGA.sqlcol USED_MB for 9999999.999 heading "Used in MB";
col FREE_MB for 9999999.999 heading "Free in MB";
col TOTAL for 9999999.999 heading "Total in MB";
col FREE_RATE for 999.99 heading "Free(%)";
SELECT pool
     , ROUND(SUM(decode(name, 'free memory', 0, bytes/1024/1024)),3) AS USED_MB
     , ROUND(SUM(decode(name, 'free memory', bytes/1024/1024, 0)),3) AS FREE_MB
     , ROUND(SUM(bytes/1024/1024),3) AS TOTAL
     , ROUND(SUM(decode(name, 'free memory', bytes, 0))/SUM(bytes)*100,2) AS FREE_RATE 
FROM   v$sgastat
GROUP BY pool
UNION ALL
SELECT 'total'
     , ROUND(SUM(decode(name, 'free memory', 0, bytes/1024/1024)),3)
     , ROUND(SUM(decode(name, 'free memory', bytes/1024/1024, 0)),3)
     , ROUND(SUM(bytes/1024/1024),3)
     , ROUND(SUM(decode(name, 'free memory', bytes, 0))/SUM(bytes)*100, 2)
FROM   v$sgastat;

Check_Daily_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 "D01. Check Objects Locked, and Kill their process and session"
@${SCRIPT_PATH}/D01-1.Check_Ojects_Locked.sql

HOST ${SCRIPT_PATH}/printTitle.sh 2 "Kill Linux Processes without Session"
@${SCRIPT_PATH}/D01-2.Kill_Process_wo_Session.sql

HOST ${SCRIPT_PATH}/printTitle.sh 1 "D02. Display Critical and Severe Events" "" "1:UNKNOWN, 2:INCIDENT_ERROR, 3:ERROR, 4:WARNING, 5:NOTIFICATION, 6:TRACE" "" "1:CRITICAL, 2:SEVERE, 8:IMPORTANT, 16:NORMAL"
@${SCRIPT_PATH}/D02.Display_Diag_Log.sql

HOST ${SCRIPT_PATH}/printTitle.sh 1 "D03. Check and monitor status of DBMS Scheduler's jobs"
HOST ${SCRIPT_PATH}/printTitle.sh 2 "Display All Scheduler's Schedules"
@${SCRIPT_PATH}/D03-1.Dipaly_All_Scheduler.sql

HOST ${SCRIPT_PATH}/printTitle.sh 2 "Display Schedule Window"
@${SCRIPT_PATH}/D03-2.Display_Schedule_Window.sql

HOST ${SCRIPT_PATH}/printTitle.sh 2 "Display Scheduler's Jobs"
@${SCRIPT_PATH}/D03-3.Dispay_Schedule_Jobs.sql

HOST ${SCRIPT_PATH}/printTitle.sh 2 "Check History of Scheduler Job"
@${SCRIPT_PATH}/D03-4.Check_History_of_Scheduler_Job.sql

HOST ${SCRIPT_PATH}/printTitle.sh 2 "Check Log Status of Scheduler Job"
@${SCRIPT_PATH}/D03-5.Check_Log_Status_of_Scheduler_Job.sql

HOST ${SCRIPT_PATH}/printTitle.sh 1 "D04. Displays Rollback Information on Relevant Database Sessions"
@${SCRIPT_PATH}/D04.Display_Rollback_Information.sql

HOST ${SCRIPT_PATH}/printTitle.sh 1 "D05. Display Top 20 CPU Consuming Session"
@${SCRIPT_PATH}/D05.Display_Top20_CPU_Consuming_Session.sql

HOST ${SCRIPT_PATH}/printTitle.sh 1 "D06. Display Top 20 Resource Comsuming SQL ID"
@${SCRIPT_PATH}/D06.Display_Top20_Resource_Comsuming_SQL_ID.sql

HOST ${SCRIPT_PATH}/printTitle.sh 1 "D07. Achive Log Information"
HOST ${SCRIPT_PATH}/printTitle.sh 2 "Dispay Configuration and Destination of Archive Log"
@${SCRIPT_PATH}/D07-1.Display_Configuration_n_Destination_of_Archive_Log.sql

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

HOST ${SCRIPT_PATH}/printTitle.sh 1 "D08. Dispay Top 50 Object Blocks in Data Buffer"
@${SCRIPT_PATH}/D08.Dispay_Top50_Obejct_Blocks_in_Data_Buffer.sql

HOST ${SCRIPT_PATH}/printTitle.sh 1 "D09. Display Hit Ratio of Buffer Cache, Library Cache and Dictionary Cache" "Buffer Cache 70 up, Library Cache 99 up, Dictionary Cache 85 up"
@${SCRIPT_PATH}/D09.Display_Hit_Ratio.sql

HOST ${SCRIPT_PATH}/printTitle.sh 1 "D10. Check the Usage of Disk"
HOST ${SCRIPT_PATH}/printTitle.sh 2 "Display Summary of Disk Usage"
@${SCRIPT_PATH}/D10-1.Display_Summary_of_Disk_Usage.sql

HOST ${SCRIPT_PATH}/printTitle.sh 2 "Display Disk Usage (from DBA_DATA_FILES and DBA_FREE_SPACE)"
@${SCRIPT_PATH}/D10-2.Display_Disk_Usage_from_DBA_DATA_FILES_n_DBA_FREE_SPACE.sql

HOST ${SCRIPT_PATH}/printTitle.sh 2 "Display Disk Usage(from DBA_TABLESPACE_USAGE_METRICS)"
@${SCRIPT_PATH}/D10-3.Display_Disk_Usage_from_DBA_TABLESPACE_USAGE_METRICS.sql

HOST ${SCRIPT_PATH}/printTitle.sh 1 "D11. Check Usage of FRA(Fast Recovery Area)"
@${SCRIPT_PATH}/D11.Check_Usage_of_FRA.sql

HOST ${SCRIPT_PATH}/printTitle.sh 1 "D12. Check Usage of SGA(System Global Area)"
@${SCRIPT_PATH}/D12.Check_Usage_of_SGA.sql

exit;
EOF1

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

일일 점검항목

You may also like...

답글 남기기 응답 취소

최신 글

최신 댓글

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

일일 점검항목

You may also like...

[Linux]특수 문자가 깨져서 출력될 때 조치 방법

[네트워크] Snort와 Suricata에 대하여, 그리고 Snort Rule 필수 사용법

[Linux]Daily Health Check Script

답글 남기기 응답 취소

최신 글

최신 댓글