by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

12장. 데이터베이스 관리

Added by 이형근, last edited by study.gurubee on 12월 03, 2012  (view change)



12. 데이터베이스 관리

12.1 데이터베이스 기동과 종료

데이터베이스의 기동

  • SHUTDOWN
  • NOMOUNT - 1단계 : 데이터베이스 인스턴스 기동
  • MOUNT - 2단계 : 인스턴스를 위한 컨트롤 파일 열림(OPEN)
  • OPEN - 3단계 : 컨트롤 파일에 기술된 모든 파일 열림(OPEN)

(1) 1단계 : 데이터베이스 NOMOUNT 단계

  • 데이터베이스의 파라미터 파일을 읽는다.
  • TRACE 파일 및 ALERT 파일을 연다. 생성 정보를 alert(SID).log 파일에 기록한다.
  • 오라클 데이터베이스에서 이용되는 공유 메모리 구조인 SGA(System Global Area)를
    할당한다.
  • 백그라운드 프로세스를 생성한다.
  • 데이터베이스는 생성된 메모리 구조화 프로세스들과 아직 연결되어 있지 않다.

(2) 2단계 : 데이터베이스 MOUNT 단계

  • 데이터베이스를 구성하는 정보가 모두 있는 컨트롤 파일의 정보를 읽어서 디스크에
    데이터파일이나 리두로그 파일 등이 올바로 존재하는지 또한 각 파일이 현재
    사용가능한지를 점검한다.
  • 데이터베이스의 백업이나 복구 시점에 많이 이용된다. 만약 데이터파일이나
    로그 파일에 문제가 발생하여 데이터베이스가 시작되지 않는다면 이 단게에서
    복구 작업을 수행한 이후에 데이터베이스를 기동한다.
  • 데이터 파일명 변경, 리두로그 파일 추가,삭제,이름변경, 아카이브 모드 옵션의
    변경, 전체 데이터베이스의 복구, 백업하여 데이터베이스 재생성, 불완전 데이터베이스
    복구 등이 가능하다.
  • MOUNT 단계에서는 일반 사용자가 데이터베이스에 접속할수 없다.

(3) 3단계:데이터베이스 OPEN 단계

  • 컨트롤 파일에 기술된 모든 데이터 파일 및 리두로그 파일 등을 연다.
  • 일반 사용자가 데이터베이스를 이용할 수 있는 상태다.
  • 만약 종료 시점에 테이블 스페이스가 오프라인이었다면 데이터베이스를 기동하여도
    해당 테이블스페이스는 오프라인 상태다.
  • 인스턴스가 데이터베이스를 연 이후에 하나 이상의 롤백 세그먼트를 확보한다.

윈도우 NT 기반에서 데이터베이스 기동 방법

  • 제어판의 서비스를 이용하여 오라클 서비스를 기동

유닉스 기반에서 데이터베이스 기동방법

1) 먼저 유닉스에서 오라클을 기동할 수 있는 구너한이 있는 사용자 ID 로 로그인한다.
대개 oracle User
2) 현재 오라클 SID 를 확인 한다.
env |grep ORACLE_SID
ORACLE_SID=ARTDOM
3) SVRMGRL 를 실행하여 오라클을 기동시킨다.
[VLDB: 9i 이후엔 svrmgrl \( oracle server manager\) 가 지원되지 않는다.]

    sqlplus ' /as sysdba'
    SQL*Plus: Release 10.1.0.4.0 - Production on Tue Jun 24 16:22:44 2008
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.

    Connected to an idle instance.

    SQL>startup
    ORACLE instance started.

    Total System Global Area 1174405120 bytes
    Fixed Size                  1329584 bytes
    Variable Size             316913232 bytes
    Database Buffers          855638016 bytes
    Redo Buffers                 524288 bytes
    Database mounted.
    Database opened.
    SQL>

4) sqlplus 를 통한 접속 테스트

    sqlplus scott/manager

    SQL*Plus: Release 10.1.0.4.0 - Production on Tue Jun 24 16:24:36 2008
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options
    
    scott@ARTDOM>select * from tab ;
    
    TNAME                          TABTYPE  CLUSTERID
    ------------------------------ ------- ----------
    DEPT                           TABLE
    EMP                            TABLE
    BONUS                          TABLE
    SALGRADE                       TABLE
    PLAN_TABLE                     TABLE
    CHAINED_ROWS                   TABLE
    CHECK_NEXT                     TABLE
    BIG_TABLE                      TABLE
    CHECK_PART                     TABLE
    CHECK_TRAN                     TABLE
    C_1                            TABLE
    CHECK_RENAME_FILES             TABLE
    CHECK_PART2                    TABLE
    CHECK_PART3                    TABLE
    DEPT_SAL                       TABLE
    MV_DEPT                        TABLE
    CHECK_DDL                      TABLE
    SCOTT_DBA_SEGMENTS             TABLE
    SCOTT_TABLES                   TABLE
    CHECK_VAR                      TABLE
    CHECK_ORANGE                   TABLE
    BIGEMP                         TABLE
    
    22 rows selected.

5) 오라클 필수 백그라운드 프로세스 확인을 통한 확인 [VLDB: smon]
ps -ef|grep ora_ |grep sm
oracle 442430 1 0 16:22:46 - 0:00 ora_smon_ARTDOM

데이터베이스 종료

(1) SHUTDOWN NORMAL

  • 정상적으로 데이터베이스를 종료하는 방법으로 데이터베이스에 연결된
    모든 사용자의 세션이 종료될 때까지 기다린다.
    [VLDB: 사용자 세션이 하나라도 존재하면 Down 이 진행 되지 않는다.]
  • 새로운 사용자의 연결은 허용하지 않는다.
  • 다음 데이터베이스를 기동할 때 별도의 복구 작업이 필요하지 않다.

(2) SHUTDOWN IMMEDIATE

  • 데이터베이스를 관리할 때 가장 많이 사용하는 정지 옵션이다.
  • 현재 커밋되지 않은 SQL 문장을 롤백시킨다.
  • 현재 데이터베이스에 접속한 사용자가 접속을 해제하기를 기다리지 않는다.
  • 만약 데이터베이스 롤백 정보가 많다면 데이버테이스를 정지하는 데 시간이
    많이 소요된다.
  • 잘못된 애플리케이션에 의해 부득이 데이터베이스를 정지시켜야 하는 경우에
    사용된다.
  • 다음 데이버테이스를 기동할 때 별도의 복구 작업이 필요하지 않다.
    다만 해당 시점에 작업하였던 부분이 롤백되었으면 다시 실행하도록 해야 한다.

(3) SHUTDOWN TRANSACTION

  • 데이터베이스를 종료하려면 현재 작업중인 모든 트랜잭션이 정상적으로 수행되어
    종료될때까지 기다린다. [VLDB: 접속 세션의 commit/rollback 까지 대기 후 Down ]
  • 모든 작업이 정상적으로 수행되었으므로 별도로 작업해야 할 부분이 필요하지 않다.
  • 다음 데이터베시으를 기동할 때 별도의 복구 작업이 필요하지 않다.

(4) SHUTDOWN ABORT

  • 트랜잭션에 대해 정리되지않으므로 일반적으로 이용하지 않는다.
  • 현재 연결되 모든 세션을 강제로 종료시킨다.
  • 커밋되지 않은 트랜잭션은 롤백되지 않는다.
  • 백업과 복구 작업이 필요한 경우에 사용한다.
  • 다음 데이터베이스를 기동할 때 별도의 복구 작업이 필요하다.

12.2 유용한 데이터베이스 모니터링 스크립트

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);

5) CPU, 메모리, 디스크 리소스가 충분한지 확인한다.

6) 데이터베이스 메뉴얼을 하루에 한 시간씩 탐독한다.

주 단위로 점검해야 할 항복

(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';

(2) 보완관리가 잘 유지되고 있는지 확인 한다.

(3) Net8에 관련된 로그는 에러나 이슈 사항이 없는지 확인한다.

(4) 모든 alert 로그 파일을 보관한다.

월 단위로 점검해야 할 항목

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

12.3 데이터베이스 문제 발생 및 해결 방법

롤백 세그먼트 EXTENT 에러

RBS 사용 현황 체크 - User 별

 select s.username, s.sid, s.serial#, rn.name,     rs.curext
           ,rs.curblk,  t.used_ublk, t.used_urec
    from    v$transaction   t
           ,v$session       s
           ,v$rollname      rn
           ,v$rollstat      rs
    where  t.addr     = s.taddr
    and    t.xidusn   = rn.usn
    and    rn.usn     = rs.usn  ;

SHAPSHOT TOO OLD 에러(ORA-1555)

발생 원인

  • 데이터베이스에 롤백 세그먼트 크기나 개수가 적당하게 설정되어 있어도
    OLTP 업무에 배치작업이 기동되어 과도하게 롤백 세크먼트를 사용할 경우 발생한다.
  • 데이터베이스에 변경을 가하는 트랜잭션은 많고 롤백 세그먼트는 크기도 작고,
    개수도 적은 경우에 발생한다.
    해결 방법
    1) 큰 롤백 세그먼트 생성 
      CREATE ROLLBACK SEGMENT vldb_rbs 
         STORAGE( INITIAL 100M NEXT 5M )
         TABLESPACE VLDB_RBS ;

    2) 평상시에 OFFLINE 상태 유지

      ALTER ROLLBACK SEGMENT Vldb_rbs OFFLINE ;

    3) 배치작업 수행 시 ONLINE 상태로 변경

      ALTER ROLLBACK SEGMENT vldb_rbs ONLINE ;

    4) 각 작업문장이나 세션에서 롤백 세그먼트 지정

      SET TRANSACTION USE ROLLBACK SEGMENT vldb_rbs ;

    5) 작업 후 OFFLINE 으로 변경함 

      ALTER ROLLBACK SEGMENT vldb_rbs OFFLINE ;

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.