오라클 상세 모니터링 스크립트

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 상세 모니터링 스크립트

점검스크립트에는 아래 정보를 수집함

instance name, hostname, version, 기동시간, 상태, db name, 아카이브여부, 아카이브경로,

sga advisor, pga advisor, redo 상태 및 용량, 최근한달 redo log switch 제일 많이 발생한 시간대 확인

ocr 다중화 확인, vote 다중화 확인

점검용 폴더 생성

$ mkdir -p /home/oracle/script/SQL
$ mkdir -p /home/oracle/script/RESULT

/home/oracle/script 폴더에 생성

dmon.sh

1 2 3 4 5 6 7 8 9 10

#! /bin/bash   export ORACLE_HOME=/app/oracle/product/19c export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH     sqlplus ' /as sysdba ' @/home/oracle/script/SQL/dmon.sql <<EOF exit; EOF

/home/oracle/script/SQL 폴더에 생성

dmon.sql

--dmon.sql
SET    ECHO OFF
SET    FEEDBACK OFF
CLEAR COLUMNS
SET    LINE 200
SET    PAGES 1000
col instance_name new_value o_sid noprint
col today_date new_value td noprint
col current_time new_value ct noprint
col spool_extension new_value se noprint
 
select instance_name
from   v$instance;
 
select to_char(sysdate, 'yyyymmdd') today_date,
       '_'||to_char(sysdate, 'hh24mi')||'_F_' current_time,
       '.log_detail' spool_extension
from   dual;
 
spool /home/oracle/script/RESULT/&&td&&ct&&o_sid&&se
alter session set nls_date_format = 'YYYY/MM/DD hh24:mi:ss';
 
col instance_name new_value o_sid print
col today_date new_value td print
col current_time new_value ct print
col spool_extension new_value se print
--*-----------------------------------------------------------------------------*
--*          #1. Information of Instance
--*-----------------------------------------------------------------------------*
col instance_name for a13
col host_name for a30
col version for a11
col status for a7
select instance_name,
       host_name,
       version,
       startup_time,
       status
from   v$instance;
--*-----------------------------------------------------------------------------*
--*          #2. Information of Database
--*-----------------------------------------------------------------------------*
col db_name for a13
col optimizer_mode for a14
col log_mode for a12
col destination for a42
select d.name db_name,
       p.value optimizer_mode,
       d.log_mode,
       a.destination
from   v$database d,
       v$archive_dest a ,
       v$parameter p
where  p.name='optimizer_mode'
and    a.status='VALID';
--*-----------------------------------------------------------------------------*
--*          #3. SGA Advisor
--*-----------------------------------------------------------------------------*
select sga_size_factor, sga_size, estd_db_time 
from v$sga_target_advice;
--*-----------------------------------------------------------------------------*
--*          #4. PGA Advisor
--*-----------------------------------------------------------------------------*
--select pga_target_factor, round(pga_target_for_estimate/1024/1024) target_mb, estd_time
--from v$pga_target_advice;
set serveroutput on;
DECLARE
    TYPE t_refcur IS REF CURSOR;
 
    FUNCTION squery11g
        RETURN t_refcur 
    IS
        v_returncursor t_refcur;
        v_sqlstatement VARCHAR2(500);
    BEGIN
        v_sqlstatement := 'SELECT pga_target_factor
                                , round(pga_target_for_estimate/1024/1024) target_mb
                                , estd_time 
                           from v$pga_target_advice where pga_target_factor>=1';
        --DBMS_OUTPUT.PUT_LINE('11g:' ||v_sqlstatement);
        OPEN v_returncursor FOR v_sqlstatement;
        RETURN v_returncursor;
    END squery11g;  
        
    FUNCTION squery10g
        RETURN t_refcur 
    IS
        v_returncursor t_refcur;
        v_sqlstatement VARCHAR2(500);
    BEGIN
        v_sqlstatement := 'SELECT pga_target_factor
                                , round(pga_target_for_estimate/1024/1024) target_mb
                                , estd_overalloc_count 
                           from v$pga_target_advice where pga_target_factor>=1'; 
        --DBMS_OUTPUT.PUT_LINE('10g:' ||v_sqlstatement);                       
        OPEN v_returncursor FOR v_sqlstatement ;
        RETURN v_returncursor;
    END squery10g;                  
 
BEGIN
    DECLARE
        TYPE r_res IS RECORD 
        ( result1   NUMBER, 
          result2   NUMBER, 
          result3   NUMBER);
        v_res  r_res;
        v_strcur   t_refcur; 
        tn         dba_tab_columns.table_name%type;
    BEGIN
        select min(table_name) INTO tn from dba_tab_columns
        where table_name='V_$PGA_TARGET_ADVICE'
        and column_name='ESTD_TIME';
         
       IF tn = 'V_$PGA_TARGET_ADVICE' THEN 
            v_strcur := squery11g;
            DBMS_OUTPUT.PUT_LINE('factor' ||'   '||'target_mb'||'   '||'estd_time');
            DBMS_OUTPUT.PUT_LINE('--------------------------------');
            LOOP
            FETCH v_strcur INTO v_res;
                EXIT WHEN v_strcur%NOTFOUND;
                DBMS_OUTPUT.PUT_LINE(v_res.result1||'      '||v_res.result2||'      '||v_res.result3);
            END LOOP;
                null;
            CLOSE v_strcur;
        ELSIF tn is null THEN
            v_strcur := squery10g;
            DBMS_OUTPUT.PUT_LINE('factor' ||'   '||'target_mb'||'   '||'estd_cnt');
            DBMS_OUTPUT.PUT_LINE('-------------------------------');
            LOOP
            FETCH v_strcur INTO v_res;
                EXIT WHEN v_strcur%NOTFOUND;
                DBMS_OUTPUT.PUT_LINE(v_res.result1||'      '||v_res.result2||'      '||v_res.result3);
            END LOOP;
            CLOSE v_strcur;    
                null;        
        END IF;
    END;
END;
/
--*-----------------------------------------------------------------------------*
--*          #5. Redo Log Check
--*-----------------------------------------------------------------------------*
col member for a70
col status for a10
select l.group#, member, archived, l.status, (bytes/1024/1024) MB
from v$log l, v$logfile f
where f.group# = l.group#
order by 1;
--*-----------------------------------------------------------------------------*
--*          #6. Redo Log Switch Check
--*-----------------------------------------------------------------------------*
col "Day with peak Log Switch" for a25
select * from (
select trunc(first_time, 'MI') as "Day with peak Log Switch", count(*) from v$log_history
where first_time between sysdate-30 and sysdate
group by trunc(first_time, 'MI')
order by 2 desc)
where rownum <= 1;
--*-----------------------------------------------------------------------------*
--*          #7. Controlfile Check
--*-----------------------------------------------------------------------------*
col name for a80
select name from v$controlfile;
--*-----------------------------------------------------------------------------*
--*          #8.  OCR, VOTE Check
--*-----------------------------------------------------------------------------*
!crsctl query css votedisk
!ocrcheck
--SET    heading on
--SET    FEEDBACK ON
--SET    ECHO ON
CLEAR COLUMNS
SPOOL OFF
 
exit

스크립트 중 #4. PGA Advisor 는 10g 이상이라면 64,65번째 줄 주석을 해제하고 66~139번째 줄은 모두 지운뒤 사용하면됨

10g 일경우에는 위 쿼리로 사용하면됨

쉘파일 권한 부여

$ chmod +x /home/oracle/script/dmon.sh

실행

$ sh /home/oracle/script/dmon.sh
 
INSTANCE_NAME HOST_NAME              VERSION     STARTUP_TIME         STATUS
------------- ------------------------------ ----------- ------------------- -------
ttdb2          ora19db2                 19.0.0.0.0  2020/12/11 23:15:33 OPEN
 
DB_NAME       OPTIMIZER_MODE LOG_MODE      DESTINATION
------------- -------------- ------------ ------------------------------------------
TTDB          ALL_ROWS         NOARCHIVELOG /app/oracle/product/19c/dbs/arch
 
SGA_SIZE_FACTOR   SGA_SIZE ESTD_DB_TIME
--------------- ---------- ------------
        .75        696      80673
          1        928       1036
       1.25       1160        952
        1.5       1392        952
       1.75       1624        952
          2       1856        952
 
factor     target_mb   estd_time
--------------------------------
1      656    7819325
1.2     787      7819325
1.4     918      7819325
1.6     1050       7819325
1.8     1181       7819325
2      1312     7819325
3      1968     7819325
4      2624     7819325
6      3936     7819325
8      5248     7819325
 
    GROUP# MEMBER                                  ARC STATUS         MB
---------- ---------------------------------------------------------------------- --- ---------- ----------
     1 +DATA/TTDB/ONLINELOG/group_1.268.1049582081                  NO  INACTIVE        200
     2 +DATA/TTDB/ONLINELOG/group_2.257.1049582085                  NO  CURRENT        200
     3 +DATA/TTDB/ONLINELOG/group_3.262.1049584181                  NO  CURRENT        200
     4 +DATA/TTDB/ONLINELOG/group_4.261.1049584183                  NO  INACTIVE        200
 
Day with peak Log Switch    COUNT(*)
------------------------- ----------
2020/12/28 20:35:00           6
 
NAME
--------------------------------------------------------------------------------
+DATA/TTDB/CONTROLFILE/current.256.1049582079
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   71adbf69955a4f4ebf151b72f97e1fb6 (/dev/oracleasm/disks/OCR_VOTE1) [OCR_VOTE]
 2. ONLINE   be3b3f4684354f98bf4d0d7251a8aa41 (/dev/oracleasm/disks/OCR_VOTE2) [OCR_VOTE]
 3. ONLINE   7a512f4f2fd74f19bf73117aa6cca2e6 (/dev/oracleasm/disks/OCR_VOTE3) [OCR_VOTE]
Located 3 voting disk(s).
 
Status of Oracle Cluster Registry is as follows :
     Version                  :          4
     Total space (kbytes)     :     491684
     Used space (kbytes)      :      84384
     Available space (kbytes) :     407300
     ID                       : 1758446194
     Device/File Name         :  +OCR_VOTE
                                    Device/File integrity check succeeded
 
                                    Device/File not configured
 
                                    Device/File not configured
 
                                    Device/File not configured
 
                                    Device/File not configured
 
     Cluster registry integrity check succeeded
 
     Logical corruption check bypassed due to non-privileged user
 
 
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

참조 : https://cafe.naver.com/dbian/3564