crontab이나 스케쥴러로 daily_chk.sh 쉘을 수행하면, 쉘 내에서 check.sql을 사용하여 로그 형태로 지정한 경로에 점검 결과 저장
daily_chk.sh
#!/bin/bash
umask 022
##### 환경 변수 입력 또는 기존 환경 프로파일 로드
# export ORACLE_BASE=/u01/app/oracle
# export ORACLE_HOME=$ORACLE_BASE/product/19c/db_1
# export TNS_ADMIN=$ORACLE_HOME/network/admin
# export GRID_HOME=$ORACLE_BASE/19c/grid
# export NLS_LANG=AMERICAN_KOREA.AL32UTF8
# export ORACLE_SID=ora19chas
# export ORACLE_UNQNAME=ora19chas
# export PATH=$ORACLE_HOME/bin:$PATH
# export LIBPATH=$LIBPATH:/app/xecuredb/server/lib
# export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:$ORACLE_HOME/rdbms/lib
. ~oracle/.bash_profile
SID=$ORACLE_SID
##### 리스너명 입력 및 추가 환경 설정
export DB_LSNR=LISTENER_CORDBP
export ASM_LSNR=ASMNET1LSNR_ASM
##### 쉘 시작 알림
SHELL_START_TIME=`date "+%H:%M:%S"`
nDATE=`date "+%Y%m%d"`
echo "[${SHELL_START_TIME}] Shell has been started~!"
##### 로그 저장 경로 이동 및 환경 설정 : 사전에 로그 적재 경로 생성 필요
cd ~oracle/dba/daily_chk
nPATH=`pwd`
cd $ORACLE_SID
##### 백그라운드 프로세스 체크
echo " " > ./daily_chk_$SID_$nDATE.txt
echo "<<<<<<<<<<<<<< DBMS BACKGROUND PROCESSOR STATE CHECK STARTED >>>>>>>>>>>>>>" >> ./daily_chk_$SID_$nDATE.txt
echo " " >> ./daily_chk_$SID_$nDATE.txt
ps -ef | grep ora_ | grep $ORACLE_SID >> ./daily_chk_$SID_$nDATE.txt
##### 그리드 서비스 체크
echo " " >> ./daily_chk_$SID_$nDATE.txt
echo "<<<<<<<<<<<<<< ORACLE GRID SERVICES STATE CHECK >>>>>>>>>>>>>>" >> ./daily_chk_$SID_$nDATE.txt
$GRID_HOME/bin/crsctl stat res -t >> ./daily_chk_$SID_$nDATE.txt
##### 리스너 서비스 체크
echo " " >> ./daily_chk_$SID_$nDATE.txt
echo "<<<<<<<<<<<<<< DBMS LISTENER STATE CHECK STARTED >>>>>>>>>>>>>>" >> ./daily_chk_$SID_$nDATE.txt
# $ORACLE_HOME/bin/lsnrctl status $DB_LSNR >> ./daily_chk_$SID_$nDATE.txt
$GRID_HOME/bin/lsnrctl status $DB_LSNR >> ./daily_chk_$SID_$nDATE.txt
echo " " >> ./daily_chk_$SID_$nDATE.txt
echo "<<<<<<<<<<<<<< Grid LISTENER STATE CHECK STARTED >>>>>>>>>>>>>>" >> ./daily_chk_$SID_$nDATE.txt
$GRID_HOME/bin/lsnrctl status $ASM_LSNR >> ./daily_chk_$SID_$nDATE.txt
##### 디스크 사용률 체크
echo " " >> ./daily_chk_$SID_$nDATE.txt
echo "<<<<<<<<<<<<<< OS DISK Usage CHECK STARTED >>>>>>>>>>>>>>" >> ./daily_chk_$SID_$nDATE.txt
echo " " >> ./daily_chk_$SID_$nDATE.txt
df -k >> ./daily_chk_$SID_$nDATE.txt
###### DB 성능 체크
sqlplus / as sysdba << EOF >> ./daily_chk_$SID_$nDATE.txt
start $nPATH/check.sql
EOF
###### 쉘 종료 알림
SHELL_END_TIME=`date "+%H:%M:%S"`
echo "[${SHELL_END_TIME}] Shell has been stoped~!"
check.sql
set echo off
set feedback off
alter session set NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';
alter session set NLS_TIMESTAMP_FORMAT='YYYY/MM/DD HH24:MI:SS';
set lines 200
set pages 100
set verify off
set feed off
ttitle off
prompt
prompt
prompt **********************************************************
prompt ***** Snapshot Info
prompt **********************************************************
select SYSDATE as SNAP_TIME
from DUAL;
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Database Info
prompt **********************************************************
col PLATFORM_NAME form a30
select DBID,
NAME,
CREATED,
RESETLOGS_TIME,
LOG_MODE,
OPEN_MODE,
DATABASE_ROLE,
PLATFORM_NAME
from V$DATABASE;
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** NLS Setting Info
prompt **********************************************************
col CHARACTERSET form a60
select a.VALUE||'_'||b.VALUE||'.'||c.VALUE as CHARACTERSET
from (select VALUE
from NLS_DATABASE_PARAMETERS
where parameter='NLS_LANGUAGE')a,
(select VALUE
from NLS_DATABASE_PARAMETERS
where parameter='NLS_TERRITORY')b,
(select VALUE
from NLS_DATABASE_PARAMETERS
where parameter='NLS_CHARACTERSET')c
group by a.VALUE||'_'||b.VALUE||'.'||c.VALUE;
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Version Info
prompt **********************************************************
col COMP_NAME form a35;
col VERSION form a30;
col STATUS form a10
prompt 1. Database Version
select BANNER_FULL
from V$VERSION;
prompt
prompt 2. DB Component Version
col COMP_NAME form a50
col VERSION_FULL form a20
select CNAME as COMP_NAME,
VERSION_FULL,
SUBSTR(dbms_registry.status_name(STATUS), 1, 11) as STATUS,
MODIFIED
from SYS.REGISTRY$;
prompt
prompt 3. SQL Patch Version
select PATCH_ID,
PATCH_TYPE,
ACTION,
STATUS,
to_char(ACTION_TIME, 'YYYY/MM/DD HH24:MI:SS') as ACTION_TIME,
DESCRIPTION
from DBA_REGISTRY_SQLPATCH
order by ACTION_TIME;
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Parameter Info
prompt **********************************************************
col NAME form a50
col DISPLAY_VALUE form a100
select NAME,
DISPLAY_VALUE,
ISDEFAULT
from V$PARAMETER
where ISDEFAULT = 'FALSE'
order by 1;
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Hot Backup Info
prompt **********************************************************
col BACKUP_TIME form a30
select FILE#,
STATUS,
CHANGE#,
to_char(TIME, 'YYYY-MM-DD HH24:MI') as BACKUP_TIME
from V$BACKUP;
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Invalid Objects
prompt **********************************************************
col OWNER form a30
select OWNER,
OBJECT_TYPE,
count(*)
from DBA_OBJECTS
where STATUS = 'INVALID'
group by OWNER, OBJECT_TYPE;
clear columns;
prompt
prompt # $ORACLE_HOME/rdbms/admin/utlrp.sql
prompt # alter package schemaname.packagename compile;
prompt
prompt
prompt **********************************************************
prompt ***** Total SGA Size
prompt **********************************************************
select sum(VALUE)/1024/1024 as "ToTal SGA Size (MB)"
from V$SGA;
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Shared Pool Free Memory
prompt **********************************************************
select POOL,
NAME,
BYTES/1024/1024 "MB"
from V$SGASTAT
where NAME = 'free memory';
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Buffer Cache Hit Ratio
prompt **********************************************************
select trunc((a.VALUE+b.VALUE-c.VALUE)/(a.VALUE+b.VALUE)*100, 2) as "Buffer Cache Hit Ratio"
from SYS.V_$SYSSTAT a,
SYS.V_$SYSSTAT b,
SYS.V_$SYSSTAT c
where a.NAME='db block gets'
and b.NAME='consistent gets'
and c.NAME='physical reads';
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Data Dictionary Hit Ratio
prompt **********************************************************
select sum(GETMISSES) as TOTAL_MISSES,
sum(GETS) as TOTAL_GETS,
round(sum(GETMISSES)/sum(GETS)*100, 2) as "Dictionary Cache Miss Ratio",
100 - round(sum(GETMISSES)/sum(GETS)*100, 2) as "Dictionary Cache Hit Ratio"
from V$ROWCACHE;
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Library Cache Hit Ratio
prompt **********************************************************
select sum(PINS) as executions,
sum(RELOADS) as misses,
round(trunc(sum(RELOADS) / sum(PINS)*100, 5), 2) as "Library Cache Miss Ratio",
round(trunc(100 - (sum(RELOADS) / sum(PINS))*100, 5), 2) as "Library Cache Hit Ratio"
from V$LIBRARYCACHE;
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Parse Hit
prompt **********************************************************
select t.VALUE "Total Parse",
h.VALUE "Hard Parse",
t.VALUE-h.VALUE "Soft Parse",
round(((t.VALUE-h.VALUE)/t.VALUE)*100, 2) "Soft Parse Ratio(%)",
round((h.VALUE/t.VALUE)*100, 2) "Hard Parse Ratio(%)"
from (select VALUE
from V$SYSSTAT
where NAME = 'parse count (total)' ) t,
(select VALUE
from V$SYSSTAT
where NAME = 'parse count (hard)' ) h;
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** PGA Hit Usage
prompt **********************************************************
select VALUE as "PGA Cache Hit Ratio"
from V$PGASTAT
where NAME = 'cache hit percentage';
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Disk Sort and Memory Sort
prompt **********************************************************
col NAME form a20
select d.VALUE Disk,
m.VALUE Mem,
ROUND((d.VALUE/m.VALUE)*100, 2) as "Disk Sort Ratio"
from V$SYSSTAT m,
V$SYSSTAT d
where m.NAME = 'sorts (memory)'
and d.NAME = 'sorts (disk)';
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Rollback Segment Wait Ratio
prompt **********************************************************
col MISS_RATIO form a20
select NAME "Rollback Segment Name",
trunc(WAITS/GETS*100, 5) as "Wait Ratio (%)"
from V$ROLLSTAT s, V$ROLLNAME n
where s.USN = n.USN;
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** DBWR Free Buffer Scan Ratio
prompt **********************************************************
select round(decode(b.VALUE, 0, -1, (a.VALUE/b.VALUE)*100), 2) as "Free Buffer Scan Ratio"
from V$SYSSTAT a,
V$SYSSTAT b
where a.NAME = 'free buffer inspected'
and b.NAME = 'free buffer requested';
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** DBWR Free Buffer Wait Ratio
prompt **********************************************************
select round(((a.FREE_BUFFER_WAIT/b.VALUE)*100),2) as "Free Buffer Wait Ratio"
from V$BUFFER_POOL_STATISTICS a,
V$SYSSTAT b
where b.NAME = 'free buffer requested';
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** LGWR Log Space Request Ratio
prompt **********************************************************
select (r.VALUE*100)/e.VALUE "LGWR Log Space Request Ratio"
from V$SYSSTAT r,
V$SYSSTAT e
where r.NAME = 'redo log space requests'
and e.NAME = 'redo entries';
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Enqueue Wait Ratio
prompt **********************************************************
select round((sum(TOTAL_WAIT#)/sum(TOTAL_REQ#))*100, 2) as "Enqueue Wait Ratio"
from V$ENQUEUE_STAT;
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Chained Row Ratio
prompt **********************************************************
col "Table Scan Access" form 999,999,999,999,999,999
col "Index Access" form 999,999,999,999,999,999
col "Chained Row Access" form 999,999,999,999,999
col "Ratio" form 999.99
select sum(decode(NAME, 'table scan rows gotten', value, 0)) "Table Scan Access",
sum(decode(NAME, 'table fetch continued row', value, 0)) "Chained Row Access",
sum(decode(NAME, 'table fetch by rowid', value, 0)) "Index Access",
round(sum(decode(NAME, 'table fetch continued row', value, 0))
/ (sum(decode(NAME, 'table fetch by rowid', value, 0)) + sum(decode(NAME, 'table scan rows gotten', value, 0))) * 100, 5) "Ratio"
from V$SYSSTAT;
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Controlfile Info
prompt **********************************************************
select NAME as "Control File Locations"
from V$CONTROLFILE;
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Tablespace Usage
prompt **********************************************************
select a.TABLESPACE_NAME,
round((a.ASGN_BYTES-b.FREE_BYTES)/1024/1024, 2) as USED_MB,
round((a.ASGN_BYTES-b.FREE_BYTES)/1024/1024, 2) as USED_MB,
round(b.FREE_BYTES/1024/1024, 2) as FREE_MB,
round(a.ASGN_BYTES/1024/1024, 2) as ASGN_MB,
round((a.ASGN_BYTES-b.FREE_BYTES)/a.ASGN_BYTES, 2) as RATIO_ASGN,
round(c.MAX_BYTES/1024/1024, 2) as MAX_MB,
round((a.ASGN_BYTES-b.FREE_BYTES)/c.MAX_BYTES, 2) as RATIO_OF_MAX
from (select TABLESPACE_NAME,
sum(BYTES) as ASGN_BYTES
from DBA_DATA_FILES
group by TABLESPACE_NAME) a ,
(select NAME as TABLESPACE_NAME,
SUM_BLOCKS*BLOCKSIZE as FREE_BYTES
from (select TABLESPACE_ID,
sum(BLOCKS) SUM_BLOCKS
from DBA_LMT_FREE_SPACE
group by TABLESPACE_ID) a,
SYS.TS$ b
where a.TABLESPACE_ID = b.TS#) b,
(select TABLESPACE_NAME,
sum(decode(MAXBYTES, 0, BYTES, MAXBYTES)) as MAX_BYTES
from DBA_DATA_FILES
group by TABLESPACE_NAME) c
where 1=1
and a.TABLESPACE_NAME=b.TABLESPACE_NAME
and a.TABLESPACE_NAME=c.TABLESPACE_NAME
order by RATIO_ASGN desc;
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Datafile Read/Write I/O
prompt **********************************************************
col NAME form a80
select NAME "Name" ,
PHYRDS "Physical Reads" ,
PHYWRTS "Physical Writes" ,
PHYRDS+PHYWRTS "Total Block IO" ,
trunc(PHYRDS/READ_SUM*100, 2) "Read (%)" ,
trunc(PHYWRTS/WRITE_SUM*100, 2) "Write (%)" ,
trunc((PHYRDS+PHYWRTS)/(READ_SUM+WRITE_SUM)*100,2) "Total IO (%)"
from V$FILESTAT a,
V$DATAFILE b,
(select sum(PHYRDS) READ_SUM,
sum(PHYWRTs) WRITE_SUM
from V$FILESTAT ) c
where a.FILE# = b.FILE#
order by 7 desc;
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Monitor Session Idle Time
prompt **********************************************************
col SID form 999999
col USERNAME form a10 truncated
col STATUS form a1 truncated
col LOGON form a17
col IDLE form a9
col PROGRAM form a30 truncated
select SID,
USERNAME,
STATUS,
to_char(LOGON_TIME, 'dd-mm-yy hh:mi:ss') "LOGON Time",
floor(LAST_CALL_ET/3600)||':'|| floor(mod(LAST_CALL_ET, 3600)/60)||':'|| mod(mod(LAST_CALL_ET, 3600), 60) "IDLE",
PROGRAM
from V$SESSION
where TYPE='USER'
order by LAST_CALL_ET;
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Redo Log Info
prompt **********************************************************
col GROUP# form 9999 heading 'Group'
col MEMBER form a70 heading 'Member' justify c
col STATUS form a10 heading 'Status' justify c
col ARCHIVED form a10 heading 'Archived'
col FSIZE form 9999 heading 'Size (MB)'
select l.GROUP#,
MEMBER,
ARCHIVED,
l.STATUS,
(BYTES/1024/1024) FSIZE
from V$LOG l,
V$LOGFILE f
where f.GROUP# = l.GROUP#
order by 1, 2;
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Session using Temp
prompt **********************************************************
col USERNAME form a20
col TABLESPACE form a20
select s.SID,
s.USERNAME,
u.TABLESPACE,
u.CONTENTS,
u.EXTENTS,
u.BLOCKS,
(select SQL_TEXT
from V$SQL
where ADDRESS = s.SQL_ADDRESS) SQL
from V$SESSION s,
V$TEMPSEG_USAGE u
where s.SADDR = u.SESSION_ADDR
and u.CONTENTS = 'TEMPORARY';
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Redo Log Switch Info
prompt **********************************************************
col NAME form a20
prompt 1. Log Switch Comparison
select 'Last Month' as Name,
count(*) Counts
from V$LOG_HISTORY
where FIRST_TIME between trunc(SYSDATE-30, 'month') and last_day(trunc(SYSDATE-30))+0.99999
union all
select 'This Month',
count(*)
from V$LOG_HISTORY
where FIRST_TIME between trunc(SYSDATE, 'month') and last_day(trunc(SYSDATE))+0.99999;
prompt
prompt 2. This Week Log Switch Counts
break on report
compute sum label total of cnt on report
select THREAD#,
to_char(FIRST_TIME, 'YYYY/MM/DD') day,
count(*) Counts
from V$LOG_HISTORY
where FIRST_TIME between sysdate -6 and sysdate
group by THREAD#,
to_char(FIRST_TIME, 'YYYY/MM/DD')
order by 1, 2;
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Resource Limit
prompt **********************************************************
column resource_name form a30;
column Curr_Val form 9,999,999,999;
column Max_Val form 9,999,999,999;
column Init_Allo form a10;
column Limit_Val form a10;
select RESOURCE_NAME,
CURRENT_UTILIZATION as CURR_VAL,
MAX_UTILIZATION as MAX_VAL,
INITIAL_ALLOCATION as INIT_ALLO,
LIMIT_VALUE as LIMIT_VAL
from V$RESOURCE_LIMIT;
clear columns;
prompt
prompt
prompt **********************************************************
prompt ***** Current PGA, UGA Memory Size
prompt **********************************************************
-- set pause on
column sid heading 'sid' format 99999
column username heading 'username' format a8
column pgm heading 'program' format a25
column terminal heading 'terminal' format a8
column pga heading 'PGA session memory' format a11
column uga heading 'UGA session memory' format a11
column pga_sum heading 'SUM PGA mem' format a12
column uga_sum heading 'SUM UGA mem' format a12
column pga_max heading 'Max PGA session memory' format a15
column uga_max heading 'Max UGA session memory' format a15
column pga_m_sum heading 'Sum Max PGA session memory' format a11
column uga_m_sum heading 'Sum Max UGA session memory' format a11
prompt 1. Current pga, uga session memory
select a.sid, a.username, substr(a.program, 1, 25) as pgm, a.terminal,
max(decode(c.name, 'session pga memory', trunc(value/1000)||'K', 0)) pga,
max(decode(c.name, 'session uga memory', trunc(value/1000)||'K', 0)) uga
from v$session a, v$sesstat b, v$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and c.name like 'session%'
group by a.sid, a.username, substr(a.program, 1, 25), a.terminal;
prompt
prompt 2. Sum of current pga, uga session memory
select 'Current PGA, UGA session memory SUM:' as sum,
sum(decode(c.name, 'session pga memory', trunc(value/1000),0))||'K' pga_sum,
sum(decode(c.name, 'session uga memory', trunc(value/1000),0))||'K' uga_sum
from v$session a, v$sesstat b, v$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and c.name like 'session%';
prompt
prompt 3. Max(peak) pga, pga session memory
select a.sid, a.username, substr(a.program, 1, 25) as pgm, a.terminal,
max(decode(c.name, 'session pga memory max', trunc(value/1000)||'K', 0)) pga_max,
max(decode(c.name, 'session uga memory max', trunc(value/1000)||'K', 0)) uga_max
from v$session a, v$sesstat b, v$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and c.name like 'session%'
group by a.sid, a.username, substr(a.program, 1, 25), a.terminal;
prompt
prompt 4. Sum of max(peak) pga, uga session memory
select 'Max(peak) PGA, UGA session memory SUM:' as sum,
sum(decode(c.name, 'session pga memory max', trunc(value/1000), 0))||'K' pga_m_sum,
sum(decode(c.name, 'session uga memory max', trunc(value/1000), 0))||'K' uga_m_sum
from v$session a, v$sesstat b, v$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and c.name like 'session%';
clear columns;
PROMPT
PROMPT
