IT이야기
[오라클] 오라클 점검 스크립트(Oracle Check Script)
너머스고(numusgo)
2024. 2. 15. 18:27
오라클DBMS를 운영을 하다 보면 모니터링 툴이 있으면 실시간으로 상태를 조회할 수 있다.
해당 스크립트는 유지보수 하면서 중요한 항목을 체크하기 위한 스크립트이다.
점거 항목으로는 DB Instance 정보, 언어셋, SGA, hit율, rman 백업, hotbackup, fra, 리소스, 테이블스페이스 정보 등을 조회하여 DB 운영 상태를 확이하여 정상적으로 작동을 하는지 아님 백업상태, 테이블스위스 부분을 확인하여 조치를 할 수 있다.
오라클 점검 스크립트(Oacle Check Script)
# vi oracle_check.sql
spool check.log
REM ------------------------------------------------------------------------
REM Instance Info
REM ------------------------------------------------------------------------
ttitle left ' ***** Current Instance Summary ***** '
column host_name format a15
select host_name, instance_name, version, archiver,
to_char(startup_time, 'yyyy-mm-dd:hh24:mi') STARTUP_TIME from v$instance
/
tti off
ttitle left ' ***** database Summary ***** '
set line 200
col value$ for a40
select name, value$ from sys.props$
where name in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');
archive log list;
tti off
REM ------------------------------------------------------------------------
REM sga Info
REM ------------------------------------------------------------------------
ttitle left ' ***** Current SGA Storage Summary ***** '
select decode(name, 'buffer_cache','buffer_cache'
, 'log_buffer', 'log_buffer'
, 'fixed_sga','fixed_sga'
, pool ) Name
, round(sum(bytes)/1024/1024 ) "size(m)"
from v$sgastat
group by decode(name, 'buffer_cache','buffer_cache'
, 'log_buffer', 'log_buffer'
, 'fixed_sga','fixed_sga'
, pool )
union all
select 'SGA Total', round(sum(bytes)/1024) "size(K)"
from v$sgastat
/
col "Shared Pool Reserved(MB)" format a40
set line 200
select s.total "Shared Pool Total(MB)",
s.free - r.r_free "Shared Pool Free(MB)",
'Free : ' || r.r_free || ', Used : ' ||r.r_used "Shared Pool Reserved(MB)",
s.other - r.r_used "Shared Pool Other(MB)"
from (select round(sum(bytes/1024/1024),2) total,
round(sum(decode(name, 'free memory', bytes))/1024/1024,2) free,
round(sum(decode(name, 'free memory',null, bytes))/1024/1024,2) other
from v$sgastat
where pool = 'shared pool' ) s,
(select round(nvl(sum(free_space),0)/1024/1024,2) r_free,
round(nvl(sum(used_space),0)/1024/1024,2) r_used
from v$shared_pool_reserved ) r
/
tti off
REM ------------------------------------------------------------------------
REM hit
REM ------------------------------------------------------------------------
ttitle left '***** buffer cache hit (Upper 90%, DB_BLOCK_BUFFERS+) *****'
select (1-((phy.value)/(cur.value+con.value)))*100 "CACHE HIT RATIO"
from v$sysstat cur, v$sysstat con, v$sysstat phy
where cur.name = 'db block gets'
and con.name = 'consistent gets'
and phy.name='physical reads'
/
tti off
ttitle left ' ***** Library Cache Miss Ratio(%) (Under 1%,SHARED_POOL_SIZE+) *****'
col "Library Cache Miss Ratio(%)" format a40;
select to_char(sum(reloads)/sum(pins)*100,'990.99')"Library Cache Miss Ratio(%)"
from v$librarycache
/
tti off
ttitle left '***** Dic. Cache Miss Ratio (%)(Under 15%, SHARED_POOL_SIZE+) *****'
col "Dic. Cache Miss Ratio (%)" format a40;
select to_char(sum(getmisses)/sum(gets)*100,'990.99')"Dic. Cache Miss Ratio (%)"
from v$rowcache
/
tti off
REM ------------------------------------------------------------------------
REM Sorting Memory:Disk (Under 5%,SORT_AREA_SIZE+)
REM ------------------------------------------------------------------------
ttitle left '***** Sorting Memory:Disk *****'
select mem.value "Mem", disk.value "Disk",
round((mem.value/(mem.value+disk.value))*100,2) "Mem Sort Ratio"
from v$sysstat disk, v$sysstat mem
where mem.name = 'sorts (memory)'
and disk.name = 'sorts (disk)'
/
tti off
REM ------------------------------------------------------------------------
REM backup check
REM ------------------------------------------------------------------------
ttitle left '***** hot backup check *****'
alter session set nls_date_format = 'yyyy-mm-dd:hh24:mi:ss';
select ' not have hotbackup ' from dual
where (select count(*) from dba_data_files) = (select count(*) from v$backup where change#=0);
select * from v$backup
where (select count(*) from dba_data_files) != (select count(*) from v$backup where change#=0);
tti off
set lines 132 pages 100
col KEY for 9,999
col INPUT_TYPE for a12
col STATUS for a10
col JOB_TIME for a8
col RATIO for 999
col INPUT_SEC for a10
col OUTPUT_SEC for a10
ttitle left '***** rman check *****'
select SESSION_KEY KEY,
INPUT_TYPE,
STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
TIME_TAKEN_DISPLAY job_time,
OPTIMIZED,
round(COMPRESSION_RATIO, 0) Ratio,
INPUT_BYTES_PER_SEC_DISPLAY Input_Sec,
OUTPUT_BYTES_PER_SEC_DISPLAY Output_Sec
from V_$RMAN_BACKUP_JOB_DETAILS
order by 1
/
tti off
REM ------------------------------------------------------------------------
REM tablespace check
REM ------------------------------------------------------------------------
ttitle left '***** tablespace check *****'
SELECT ddf.tablespace_name "Tablespace Name",
ddf.bytes/1024/1024 "Bytes(MB)",
(ddf.bytes - dfs.bytes)/1024/1024 "Used(MB)",
ROUND(((ddf.bytes - dfs.bytes) / ddf.bytes) * 100, 2) "Percent(%)",
dfs.bytes/1024/1024 "Free(MB)",
ROUND((1 - ((ddf.bytes - dfs.bytes) / ddf.bytes)) * 100, 2) "Free(%)"
FROM (SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) ddf,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space
GROUP BY tablespace_name) dfs
WHERE ddf.tablespace_name = dfs.tablespace_name
ORDER BY 4 DESC;
tti off
ttitle left '***** tablespace usage 90% over info *****'
col file_name for a60
select tablespace_name, file_name, bytes/1024/1024, autoextensible from dba_data_files
where tablespace_name in (SELECT ddf.tablespace_name "Tablespace Name"
FROM (SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) ddf,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space
GROUP BY tablespace_name) dfs
WHERE ddf.tablespace_name = dfs.tablespace_name
and 90 <= ROUND(((ddf.bytes - dfs.bytes) / ddf.bytes) * 100, 2))
order by 1, 2;
tti off
ttitle left '***** tablespace datafile 32gb over info *****'
col file_name for a60
select tablespace_name, file_name, bytes/1024/1024, autoextensible from dba_data_files where bytes/1024/1024 > 30000 order by 1,2;
tti off
REM ------------------------------------------------------------------------
REM ASM Disk Check
REM ------------------------------------------------------------------------
ttitle left '***** asm check *****'
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, free_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/
tti off
REM ------------------------------------------------------------------------
REM Fra Check
REM ------------------------------------------------------------------------
ttitle left '***** Fra check *****'
prompt
prompt Fra Check
prompt ===============
set pages 100
set line 120
col name form a50
col SPACE_LIMIT form 9999999999999
col SPACE_USED form 9999999999
select * from v$recovery_file_dest;
tti off
REM ------------------------------------------------------------------------
REM Total size
REM ------------------------------------------------------------------------
ttitle left '***** DB Total size check *****'
select (select sum(bytes)/1024/1024/1024 from dba_segments) "used(gb)", (select sum(bytes)/1024/1024/1024 from dba_data_files) "Total(gb)" from dual;
REM ------------------------------------------------------------------------
REM Resource limit info
REM ------------------------------------------------------------------------
ttitle left '***** Resource limit info *****'
col resource_name for a20
set line 200
select * from v$resource_limit;
select * from gv$resource_limit;
tti off
spool off