오라클DB 점검사항

구분	점검내용	점검사항	비 고
일일 점검	Oracle Instance 상태 확인	Oracle Instance service 상태 확인	SQL>select insatnce_name, status from v$instance;
	Listener 상태 확인	listener가 정상 service 중인지 확인	$ lsnrctl status
	Oracle S/W, Archive 공간 확인	Oracle 설치 Directory와 Archive Dest의 Free 공간 확인	$ df -k (HP서버일 경우 bdf)
	Resource Check	Oracle에 설정된 Parameter에 따라 resource가 부족한지 확인	SQL> select * from v$resource_limit;
	Backup 확인	Oracle Database Backup수행 여부 확인	SQL> select * from v$backup; (HotBackup)
	recover 파일 조회	recovery 가 필요한 파일을 조회	SQL> select * from v$recover_file
	Tablespace 사용량 확인	Tablespace의 여유 공간을 확인 하여 부족한 스페이스 할당 (80% 이상 사용중인 Tablespace Check)	SQL> SELECT a.tablespace_name, a.bytes/1024/1024 "AMOUNT(MB)", b.bytes/1024/1024 "USED(MB)", c.bytes/1024/1024 "FREE(MB)", (b.bytes*100)/a.bytes "% USED", (c.bytes*100)/a.bytes "% FREE" FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c WHERE a.tablespace_name=b.tablespace_name AND a.tablespace_name=c.tablespace_name AND (c.bytes*100)/a.bytes<20;
	Redo 발생량 확인	Redo 발생량이 갑자기 증가 할 경우 Application의 오작동일 경우가 많으므로 Redo 발생량이 많을 경우 logmnr을 이용하여원인 SQL 분석	SQL> select to_char(first_time,'YYYY/MM/DD')  "Date",count(thread#) "Count"  from v$loghist group by to_char(first_time,'YYYY/MM/DD') order by to_char(first_time,'YYYY/MM/DD') desc;
	Alert Log 확인	Alert_"SID".log 파일에발생하는 Error확인	$ vi $ORACLE_BASE/admin/"SID"/bdump/alert_"SID".log  또는 cd $ORACLE_BASE/admin/SID/bdump -> grep ora-alert_SID.log
수시/ 주 점검	Tablespace Fragmentation확인	Tablespace의 Fragmentation이 심한 Tablespace는 coalesce	SQL> SELECT * FROM (       SELECT tablespace_name,count(*) AS fragments,              sum(bytes) AS total,              max(bytes) AS largest        FROM dba_free_space        group by tablespace_name      ) WHERE fragments > 200;
	Job 수행여부 확인	DBA_JOBS를 확인하여 등록된 Job이 정상적으로 수행중인지 확인	SQL> select job, schema_user, last_date, last_sec, next_date, next_sec, what from dba_jobs;
	Backup 확인	물리적인 Backup 및 export Backup에 대한 정상 수행여부와 Controlfile은 Backup이 되는지 Backup 주기와 보관 중인 Backup 본이 몇 copy인지 확인	Backup 장비를 사용할경우 Backup solution에서 확인  Export의 경우 log파일 확인  Local Disk에 Backup 할 경우 물리적인 Backup 파일 확인
	Trace 확인	Oracle Process가 발생 시키는 trace파일 확인 (발생한 Trace 파일이 있을 경우 metalink를 이용한 원인 분석)	$ ls -ltr $ORACLE_BASE/admin/"SID"/bdump  $ ls -ltr $ORACLE_BASE/admin/"SID"/udump

출처: https://goalker.tistory.com/126 [오라클 스터디]

 

 

 

#################################################################################

 

오라클 기본 점검 스크립트

 

 

 

#################################################################################

== 일일점검(Daily) ==

파일시스템

데이터베이스 관련된 파일시스템이 얼마나 사용하고 있는지 등을 체크한다.

예를 들어 평상시 사용율이 50% 미만이였다면.. 체크할 기준을 50%로 잡고 점검한다.

그러다가 어느날 60%가 넘었다면.. 그 전날 무슨 작업이 있었는지를 반드시 확인한다.

 

데이터베이스

㉮ alertSID.log ,trace file을 매일 체크

-.장애의 원인은 보통 trace file이나 오라클의 alertSID.log파일에 기록되는데 이 파일을 근거로 하여 장애의 원인을 파악할 수 있는 환경이 되어야 함

-.이 화일의 내용은 무한히 늘어나므로 스크립트를 이용하여 일자별 파일을 생성하고 이전 날짜 파일들은 다른 디렉토리나 파일시스템으로 옮기거나 백업받아서 파일시스템이 FULL나는것을 방지해야함.

㉯ *_dump_dest의 free space여부를 매일 확인

InitSID.ora이나 configSID.ora에 *_dump_dest가 설정되어 있다.

㉰ 각 테이블스페이스에 free space의 fragmentation이 일어났는지 확인

-.매일 테이블스페이스의 사용율을 체크하여 테이블스페이스가 Full날것을 미리 방지

(현재 저희회사는 90%를 기준으로 체크함-반드시 이력을 남길 것)

-.Fragmentation이 많이 일어나고 Free space는 많이 존재하지 않는다면 하나의 Data file을 첨가

-.Fraementation도 높고 Free space와 Disk space가 거의 존재하지 않는다면 Table들과 free space를 각각 연속적으로 연결이 되도록 backup/export를 받은 후 다시 drop/import를 하고 재구성

 

CONTROL FILE

㉮ 매일 매일 Hot Backup

 

Online Redo Log File

㉮ 상태가 Invalid한 것을 체크(V$LOG 사용)

      select * from v$logfile;

-.INVALID log file error는 I/O error로서 alert.log에 기록되지 않으며 alert.log file을 분석함으로써 탐지가 가능

-.STALE은 shutdown abot 전에 쓰여지고 있는 log가 완전하지 않거나 그 log에 대해 걸려있는 write 상태가 알 수 없는 것일 때에 생성됨(alert.log 화일에 기록되지 않음)

㉯ Log Switch Interval 체크

 select thread#,sequence#,time,archive_name

from v$log_history;

-.Log Switch interval은 위의 time의 차이를 계산하면 알 수 있음.

-.Log Switch가 너무 자주 발생하면 혹시 hot backup 상태로 두고 있는 화일이 있는지 확인

select file#,status

from v$backup;

㉰ Checkpoint 간격 체크.

-.잦은 Checkpoint는 Crash Recovery의 기간은 줄여줌

-.Dirty Buffers를 자주 쓰는 것과 File Headers를 자주 Update시 오버헤드 발생

 

Rollback Segment tablespace

㉮ Rollback Segment가 online이 되어있는지 체크

    select segment_name,status

from dba_rollback_segs;

-.Rollback Segment가 Offline이 되어있을 수 있음

(Rollback segment를 가진 datafile에 문제가 발생시)

㉯ ORA-1555 에러가 생성되는지 여부를 체크

-.데이터베이스는 여전히 사용가능하며 application error가 일어날 수도 있음

㉰ ORA-1538,1551,1552,1553,1554,1555,1556,1557,1558,1559,1562를 체크

-.위의 error는 extent를 할당할 수 없거나 tablespace fragmentation이 일어나는 경우에 나타남

 

Archived Redo Logs

㉮ Archive File이 생성되는 파일시스템(또는 디렉토리)에 여유 공간이 있는지 체크

-.Disk에 여유공간이 없어 archive log를 write할 수 없어서 DB가 hang이 걸림

㉯ Archived log file을 특정 threshold에 도달할 때마다 backup을 받음.

   (저희는 매일 백업 받으며 Tape에 한달 보관하고 있음)

㉰ Archived redo log file의 sequence #가 순차적인지 확인

㉱ ARCH process가 움직이는지를 체크

-.ARCH process가 움직이지 않아서 DB가 hang이 걸리는 문제를 막을 수 있음

㉲ alert.log에 Archive log들에 관한 error가 있는지 체크

-.initSID.ora(parameter file)내의 *_dump_dest방향을 참조함

 

OS detection

㉮ Disk failure나 controller의 이상이 있는지 체크

㉯ OS mirroring이 되고 있는지 체크

 

기타

㉮ SQL*NET 상태를 체크

-.SQL*NET V2의 경우에는 listener의 process가 runnung인지 확인

$lsnrctl service

 

== 매주점검(Weekly) ==

㉮ 악성쿼리와 악성트램잭션에 대한 응답시간 체크

-. 갑자기 performance가 떨어지는 경우에 어떤 원인인지 알아내서 그 원인 추적을 할 필요가 있음

㉯ Temporary tablespace와 rollback segment tablespace에서 object들이 생성되지 않았는지 체크

-.Object들이 생성되었을 경우 충분히 삭제되어질 가능성이 큼

㉰ AUD$가 비워지는지 확인한다.

-.Aud$는 auditing 할 경우에 데이터량이 많기 때문에 export 받고 테이블은 비워야 함

㉱ Rollback segment tablespace에 충분한 공간이 있는지 체크

-.Full 방지

-.Fragmentation이 일어나지 않도록 주의

㉲ Data Dictionary Tables과 System Rollback Segment만이 존재해 함

㉳ OFFLINE Full backup을 받는다.

-.24/365시스템이 아니라면 가동되는 database는 Offline Full Backup을 받아야함

(경우에 따라 월단위 Offline Full Backup 받음)

㉴ OS의 운영 상태를 확인할 수 있는 통계를 만든다.

(위 경우는 SP 역할임)

 

== 매월점검(Monthly) ==

㉮ 주기적으로 recovery test를 한다.

   (저희는 1년에 한번씩 테스트합니다)

㉯ V$rollstat를 이용해서 Rollback segment 상태 체크

㉰ 월간운영 현황 보고서 작성

-.버퍼, 트랜잭션 현황, 데이터 및 인덱스 현황등을 파악하여 월별 데이터를 확보

 

 

 

 

###########################################################################

12.3 데이타베이스 운영 시 정기적으로 점검해야 할 사항

매일 점검해야 할 항목

• (1) 모든 데이터베이스 인스턴스가 기동되었는지 확인 한다.
• (2) alert Log 를 확인 한다.
• (3) 데이터베이스가 성공적으로 백업되었는지 확인 한다.
• (4) 데이터베이스의 아카이브 로그 파일이 성공적으로 백업되었는지 확인한다.
• (5) 데이터베이스 성능을 위해 충분한 리소스가 존재하는지 확인 한다.

 

1) 테이블 스페이스에 충분한 공간이 있는지 확인한다.

• 현실에 맞지 않다 - 책 내용

  more "space.sql"
  select tablespace_name,
         sum(blocks) as free_blk, trunc(sum(bytes)/(1024*1024)) AS FREE_M,
         max(bytes)/(1024) as big_chunk_k, count(*) as num_chunks
  from dba_free_space
  group by tablespace_name  ;
  

Tablespace Free 공간 체크 Script

방법1. MAXBYTES - BYTES + FREE

  select AA.*, (AA."Allocation" - AA."Used" + BB."Free" ) "Free Size", BB."Free"
  from
  (select a.tablespace_name "TABLESPACE_NAME",  sum(a.maxbytes)/1024/1024 "Allocation", sum(a.bytes)/1024/1024 "Used"
  from dba_data_Files a
  group by a.tablespace_name ) AA,
  (select b.tablespace_name, sum(b.bytes)/1024/1024 "Free"
  from dba_free_space b
  group by b.tablespace_name ) BB
  where AA.TABLESPACE_NAME = BB.TABLESPACE_NAME(+) ;
  

 

방법2. MAXBYTES - SEGMENT SIZE

  select a.tablespace_name , a.bytes - b.bytes "FREE(M)"
  from
  ( select tablespace_name,   sum(maxbytes)/1024/1024 bytes from dba_data_files group by tablespace_name ) a,
  ( select tablespace_name,   sum(bytes)/1024/1024 bytes from dba_segments group by tablespace_name) b
  where a.tablespace_name = b.tablespace_name;      
   

 

방법3. MAXBYTES - EXTENT SIZE

   select a.tablespace_name , a.bytes - b.bytes "FREE(M)"
   from
   ( select tablespace_name,   sum(maxbytes)/1024/1024 bytes from dba_data_files group by tablespace_name ) a,
   ( select tablespace_name,   sum(bytes)/1024/1024 bytes from dba_extents group by tablespace_name) b
   where a.tablespace_name = b.tablespace_name; 
   

 

2) 롤백 세그먼트를 확인한다.

more "rbs_usage.sql"
col today format a20 new_value curr_time
col today noprint
select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') today from dual ;

prompt =========================================================
prompt == rollback segment storage and status                 ==
prompt => DAte : [ &CURR_TIME ]
prompt =========================================================

set linesize 120
col rb_seg for a10
col status for a10
col tbs_nm for a10
col ini_kb for 9,999,999
col nxt_kb for 9,999,999
col tot_kb for 9,999,999

col ext for 999
col xt  for 99

select a.segment_name as rb_seg,
       a.status, c.xacts as xt,
       b.initial_extent/1024 as ini_kb,
       b.next_extent/1024 as nxt_kb,
       b.extents as ext,
       b.bytes/1024 as tot_kb,
       a.tablespace_name as tbs_nm
from dba_rollback_segs a, dba_segments b, v$rollstat c
where a.owner = b.owner
and   a.segment_name = b.segment_name
and   a.segment_id = c.usn
order by a.segment_name ;

 

3) 과도하게 증가한 세그먼트가 존재하는지 확인 한다.

3_1)우선 ananlyze 진행 한다.

 sys@ARTDOM>begin
    dbms_utility.analyze_schema('SCOTT','ESTIMATE',NULL,5);
    end ;
    /
  PL/SQL procedure successfully completed.

 

3_2) 실행 안됨

   select e.owner, e.segment_type, e.segment_name, count(*) as nr_extents,
          s.max_extents, to_char(sum(e.bytes)/(1024*1024),'999,999.90') AS MB
    from dba_extents e, dba_segments
    where e.segment_name = s.segment_name
    group by e.owner, e.segment_type, e.segment_name, s.max_extents
    having  count(*) > 10  or ( s.max_extents - COUNT(*) ) < 20 )
    order by count(*) desc ;

 

4) 스페이스-바운드 오브젝트를 식별한다. VLDB: 의미 없음

SELECT A.TABLE_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME
FROM ALL_TABLES A,
( SELECT TABLESPACE_NAME, MAX(BYTES) AS BIG_CHUNK
  FROM DBA_FREE_SPACE
  GROUP BY TABLESPACE_NAME ) F
WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME
AND   A.NEXT_EXTENT > F.BIG_CHUNK ;

ALTER TABLE <owner>.table STORAGE ( MAXEXTENTS UNLIMITED); 

 

주 단위로 점검해야 할 항복

(1) 잘못된 규칙에 의해 오브젝트가 존재하는지 확인 한다.

  more "check_next.sql"
  select segment_name, segment_type, dt.tablespace_name, ds.next_extent
  from dba_tablespaces dt, dba_segments ds
  where dt.tablespace_name = ds.tablespace_name
  and dt.next_extent != ds.next_extent
  and ds.owner ='&OWNER';

 

PK 가 존재 하지 않는 테이블 체크

  more "check_pk_exist.sql"
  select table_name from all_tables where owner='&OWNER'
  minus
  select table_name from all_constraints where owner='&&OWNER'
  and constraint_type ='P';

월 단위로 점검해야 할 항목

• (1) 데이터베이스의 비정상적인 현상이 발생하는지 확인한다.
• (2) 튜닝해야할 부분이 있는지 확인한다.
• (3) I/O 경합이 존재하는지 확인
• (4) 단편화(Fragmentation)이 존재하는지 확인