[일일점검] 쿼리 및 Shell 파일 - Linux& Unix

제가 일일점검 시 사용하는 Shell 파일을 올리고 사용법에 대해서 포스팅하겠습니다.

 

1.일일점검용 디렉토리를 생성해줍니다.

(저의 경우에는 /u01/app/oracle 아래에 생성하였습니다.)

 

$mkdir dailycheck

$mkdir dailycheck/sql

 

2. dailycheck아래에 daily_chk.sh 파일을 생성합니다.

(각자의 폴더에 맞게 수정하여 사용하면됩니다.)

cd /u01/app/oracle/diag/rdbms/xe/XE/trace
clear
tail -1000 al* | awk '{if (($5=="2021") && $6 =="") {vdate = $0} else {print vdate,"|", $0} }' | grep -E                    -i "ORA-|error"

##
sqlplus sys/oracle@XE as sysdba <<EOF

@/u01/app/oracle/dailycheck/sql/bchr.sql
@/u01/app/oracle/dailycheck/sql/proc_cnt.sql
@/u01/app/oracle/dailycheck/sql/tbs.sql
exit
EOF

 

3.sql 디렉토리에 bchr(Buffer Cache Hit Ratio) ,Resouce Count, Tablespace Usage 쿼리를 추가합니다.

 

vi bchr.sql
 SELECT 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) || '%' "Buffer Cache Hit Ratio", to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') today
   FROM V$SYSSTAT
   /
   
vi proc_cnt.sql
col RESOURCE_NAME for a20
col INITIAL_ALLOCATION for a10
col LIMIT_VALUE for a10
SELECT * from v$resource_limit where resource_name in ('processes', 'sessions', 'transactions');


vi tbs.sql
col TABLESPACE for a20

select   substr(a.tablespace_name,1,30) tablespace,
         round(sum(a.total1)/1024/1024,1) "TotalMB",
         round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) "UsedMB",
         round(sum(a.sum1)/1024/1024,1) "FreeMB",
         round((round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1))/round(sum(a.total1)/1024/1024,1)*100,2) "Used%"
from
         (select   tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt
          from     dba_free_space
          group by tablespace_name
          union
          select   tablespace_name,sum(bytes) total1,0,0,0
          from     dba_data_files
          group by tablespace_name) a
group by a.tablespace_name
order by tablespace
/

 

4.프로파일에 명령어를 추가하면 끝.(저는 alias dchk로 설정하였습니다.)

alias dchk='sh /u01/app/oracle/dailycheck/daily_chk.sh'

 

 

5.확인

-bash-4.2$ dchk
...생략
Mon Jan 25 20:31:12 2021 | Errors in file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_j002_18603.trc:
Mon Jan 25 20:31:12 2021 | ORA-12012: error on auto execute of job 8
Mon Jan 25 20:31:12 2021 | ORA-06550: line 1, column 96:
Mon Jan 25 20:31:12 2021 | ORA-06550: line 1, column 96:

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jan 25 23:53:31 2021

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> SQL>
Buffer Cache Hit Ratio                    TODAY
----------------------------------------- -------------------
97.04%                                    2021/01/25 23:53:31

SQL>
RESOURCE_NAME        CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
-------------------- ------------------- --------------- ---------- ----------
processes                             36              41        100        100
sessions                              37              44        172        172
transactions                           0               0        189  UNLIMITED

SQL>
TABLESPACE              TotalMB     UsedMB     FreeMB      Used%
-------------------- ---------- ---------- ---------- ----------
ORA_SQL_TEST_TS            1024     1023.1         .9      99.91
SYSAUX                      730      690.9       39.1      94.64
SYSTEM                      530      520.5        9.5      98.21
UNDOTBS1                    280       27.5      252.5       9.82
USERS                       100        2.6       97.4        2.6

SQL> Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production