[오라클] 오라클 점검 스크립트(Oracle Check Script)

 

 

오라클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