자동으로 UNDO 세그먼트 구성

☞ 자동으로 Undo 세그먼트 구성

1) 초기화 파라미터 환경구성

 

  ① UNDO_MANAGEMENT

    - 테이타베이스의 Undo 모드를 자동 모드로 사용 할지 수동 모드를 사용할지 여부를 결정 합니다.   

    - AUTO 또는 MANUAL 값 중 하나로 설정할 수 있으며 초기화 파라미터 파일에서 설정해야 합니다.

    - 데이터베이스가 운영중에는 UNDO_MANAGEMENT 를 동적으로 변경할 수 없습니다.

    - AUTO로 설정하게 되면 데이터베이스는 자동 Undo 관리 모드로 설정되며 undo 테이블스테이스가 필요합니다.

 

  ② UNDO_TABLESPACE

    - 사용할 특정 UNDO 테이블스페이스를 지정 합니다.

    - 최소한 하나의 UNDO 테이블 스페이스 생성이 필요 합니다.

    - 초기화 파일에서 설정하거나 데이터베이스의 운영중에도 ALTER SYSYTEM 명령을 사용하여 동적으로 변경할 수 있습니다.

 

  ③ UNDO_RETENTION

    - 일관성 읽기를 위해 제공되는 Undo 데이타의 보유 기간을 결정합니다.

    - 초기화 파일에서 설정하거나, ALTER SYSTEM 명령을 사용하여 동적으로 수정할 수 있습니다.

    - 이 parameter는 초 단위로 지정됩니다. 기본값은 900초이며, 이는 Undo 데이타를 15분 동안 보유합니다.

   ※ UNDO_RETENTION을 설정한 후에도 UNDO 테이블스페이스의 크기가 너무 작으면 지정한 시간 동안 Undo 데이타가 보유되지 않습니다.

      UNDO_RETENTION 파라미터는 현재 Undo 테이블스페이스에 UNDO_RETENTION 기간 동안 발생하는 모든 트랜잭션을 수용할 수 있을 만큼 충분한 커야 합니다.

 

  ④ UNDO_SUPPRESS_ERRORS

    - 이 파라미터를 사용하면 자동 Undo 관리 모드에서 수동 관리 모드 작업 시 에러가 발생하는 것을 방지합니다.

      즉, ALTER ROLLBACK SEGMENT ONLINE, SET TRANSACTION USE ROLLBACK SEGMENT 문을 수행하면 ORA-30019 에러가 발생하는 것을 방지합니다.

 

2) Undo 테이블스페이스 생성
자동 Undo 관리에는 undo 테이블스페이스가 필요합니다.
undo 테이블스페이스가 데이타베이스에 두개 이상 가능하지만, 그 중에 하나만 활성화되어 있습니다.

UNDO 테이블스페이스를 생성하는 방법은 CREATE DATABASE문에 절을 추가하여 데이타베이스와 함께 생성하는 방법과,  데이터베이스가 생성된 후에도 CREATE UNDO TABLESPACE 명령을 사용하여 생성할 수 있습니다.

 

  ① 데이터 베이스 생성시 지정..

 

   CREATE DATABASE db01
   . . .
   UNDO TABLESPACE UNDOTBS1
   DATAFILE 'D:\oracle\oradata\oracle\UNDOTBS1.dbf' SIZE 20M
   AUTOEXTEND ON

 

  ② 데이터베이스 생성 후 별도 생성

   SQL>CREATE UNDO TABLESPACE UNDOTBS2
       DATAFILE 'D:\oracle\oradata\oracle\UNDOTBS2.dbf'
       SIZE 30M;

 

3) Undo 테이블스페이스변경

 

  ① 테이블스페이스 전환

 

   - 현재 사용하고 있는 Undo 테이블스페이스를 다른 테이블스페이스로 변경 할 수 있습니다.
     (활성 Undo 테이블스페이스에 있는 Undo 세그먼트를 오프라인 상태로 변경할 수는 없습니다.)

   - 인스턴스당 하나의 Undo 테이블스페이스만 활성 Undo 테이블스페이스로 지정될 수 있습니다.

 

   - ALTER SYSTEM 명령을 이용하면 됩니다.
     SQL>ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2

 

   - Undo 테이블스페이스를 전환하면 새로운 트랜잭션은 새로 지정한 UNDO 테이블스페이스를 사용하게 됩니다.

     하지만 모든 현재 트랜잭션, 즉 이미 존재하던 UNDO 테이블스페이스에 할당된 트랜잭션은 완료될 때까지 계속해서 기존의 Undo 테이블스페이스를 사용합니다.

 

  ② Undo 테이블스페이스 삭제

   - UNDO 테이블스페이스 삭제는 인스턴스에서 현재 사용되지 않을 때 , 커밋되지 않은 트랜잭션이 포함되어 있지 않아야 가능합니다.  

   - 삭제하려는 UNDO 테이블스페이스가 데이타베이스의 현재 Active한 UNDO 테이블스페이스인 경우는 먼저 새 UNDO 테이블스페이스를 설정해야 합니다.

   - UNDO tablespace를 삭제하려면, 테이블스페이스내의 모든 트랜잭션이 완료되어야 합니다.

 

    SQL> SELECT a.name,b.status ,b.xacts
        FROM v$rollname a, v$rollstat b
        WHERE a.name IN (SELECT segment_name FROM dba_segments) AND a.usn = b.usn;

 

   - 쿼리 결과 만약 PENDING OFFLINE 상태의 Undo 세그먼트가 존재한다면 이 UNDO 세그먼트에는 Active 트랜잭션이 아직 포함되어 있는 것입니다.

 

    SQL> DROP TABLESPACE UNDOTBS1;

 

☞ Undo 테이블스페이스 크기 결정

 

  ① Undo 세그먼트 통계조회

 

   - V$UNDOSTAT 뷰를 사용하여 Undo에 대한 공간 할당과 사용을 모니터 합니다.
     (각 행은 10분 간격으로 인스턴스에서 수집된 통계가 저장됩니다. 시간 간격은 10분이라고 하였지만 10분 미만의 시간이 반환 될 때도 있습니다. )

   - 이 뷰를 사용하면 현재 작업 로드에 필요한 Undo 공간의 크기를 예측할 수 있으며 Undo 사용을 튜닝 할 수 있습니다   (자동 모드와 수동 모드에서 모두 사용할 수 있습니다.)

 

SQL> SELECT end_time, begin_time, undoblks, txncount, maxquerylen FROM V$UNDOSTAT;

 

END_TIME BEGIN_TI   UNDOBLKS   TXNCOUNT MAXQUERYLEN
-------- -------- ---------- ---------- -----------
05/04/06 05/04/06          9        889           3
05/04/06 05/04/06         33        812           3

 

  ② 크기 결정에 필요한 정보

 

   - (UR) UNDO_RETENTION (초)
   - (UPS) 초당 생성되는 Undo 데이터 블록 수
   - (DBS) 익스텐트 및 파일크기에 따라 달라지는 오버헤드 (db_block_size)

UNDO 테이블스페이스의 크기를 조정 하려면 세 가지 가 필요 합니다.
두 가지는 초기화 파일에서 얻을 수 있는 UNDO_RETENTION과 DB_BLOCK_SIZE입니다.
세번째는 초당 생성되는 Undo block의 수를 V$UNDOSTAT에서 얻을 수 있습니다.

-- 아래는 초당 생성되는 undo block수를 알 수 있는 공식입니다.
-- 생성되는 전체 block의 수를 계산하여 모니터되는 시간(초)으로 나눕니다.

SQL> SELECT (SUM(undoblks)/SUM (((end_time-begin_time)*86400)))  FROM v$undostat;

 

※ END_TIME 과 BEGIN_TIME 은 DATE형이기 때문에 뺄셈을 수행하면 결과가 날짜로 표시됩니다. 그러므로 날짜를 초로 변환하려면 하루를 초로 계산한 86400을 곱합니다.

-- 완성된 SQL문장

SQL>SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
    FROM (SELECT value AS UR
          FROM v$parameter
          WHERE name = 'undo_retention'),
         (SELECT (SUM(undoblks)/SUM(((end_time-begin_time)*86400))) AS UPS
          FROM v$undostat),
         (SELECT value AS DBS
          FROM v$parameter
          WHERE name = 'db_block_size');

 

※ 화면상의 표시된 공식에 의한 결과를 가지고 undo tablespace크기를 결정할수 있습니다.
    좋은 결과를 얻으려면 데이타베이스의 작업 로드가 가장 많은 시간에 계산하는 것이 좋습니다.

 

☞ Undo segment 정보 조회

 

SQL> SELECT segment_name,owner,tablespace_name,status
        FROM dba_rollback_segs;

 

SQL> SELECT n.name, s.extents, s.rssize,s.hwmsize,s.xacts, s.status
        FROM v$rollname n, v$rollstat s
        WHERE n.usn = s.usn;

 

SQL> SELECT s.username, t.xidusn, t.ubafil,t.ubablk, t.used_ublk
        FROM v$session s, v$transaction t
        WHERE s.saddr = t.ses_addr;