초기화 파라미터 환경구성

① 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 에러가 발생하는 것을 방지합니다.

Undo 테이블스페이스 생성

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

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

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

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

Undo 테이블스페이스 변경

① 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 테이블스페이스를 삭제하려면, 테이블스페이스내의 모든 트랜잭션이 완료되어야 합니다.

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


--  Undo 테이블스페이스 삭제
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  
    

② 크기 결정에 필요한 정보

  UNDO 테이블스페이스의 크기를 조정 하려면 세 가지 가 필요 합니다.

  두 가지는 초기화 파일에서 얻을 수 있는 UNDO_RETENTION과 DB_BLOCK_SIZE이며, 세 번째는 초당 생성되는 Undo block의 수를 V$UNDOSTAT에서 얻을 수 있습니다.

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

 
-- 아래는 초당 생성되는 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 세그먼트 정보 조회

 
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;  
    

참고문헌

  - Oracle의 Rollback Segments와 Undo Segments 세미나 문서 - 한국오라클(주) 제품 지원실