UNDO(언두) Segment

UNDO란?

데이터베이스의 변경사항을 롤백하거나 실행 취소하는데 사용되는 정보를 생성하고 관리합니다. 이러한 정보는 주로 커밋(Commit)되기 전의 트랜잭션 작업에 대한 기록으로 구성됩니다. 예전버전에서는 Rollback Segment라고 불리기도 했습니다.

 

사용 용도

- Rollback 명령문 시 트랜잭션 Rollback

- Database 복구

- 읽기일관성 제공

- Flashback 기능을 사용하여 논리적 복구

- Flashback Query 사용하여 이전  시점의 데이터 분석

 

데이터베이스 복구중에 우선 리두 로그를 사용하여 커밋되지 않은 데이터까지 복구(Rollforward)를 수행하고, 그 후 UNDO를 사용하여 커밋된 데이터만 복구(Rollback)하여 데이터베이스 복구를 수행합니다.

 

Oracle 11g부터 Automatic Undo Management(AUM)이 기본적으로 설정되어 사용됩니다. 

DBCA로 DB설치 시 기본적으로 UNDOTBS1이 생성됩니다. 

인스턴스 시작시 사용가능한 UNDO 테이블스페이스가 없는 경우 UNDO테이블스페이스 없이 시작되고 SYSTEM 테이블스페이스가 UNDO 테이블스페이스 역할을 하게 됩니다.(이는 권장하지 않습니다. SYSTEM테이블스페이스는 DB를 구성하는 시스템관련 정보들이 있는 테이블스페이스이고, UNDO로 인해서 장애가 발생할 수 있는 가능성이 있기 때문에 분리해서 사용해야 합니다.) 이 경우 로그 파일에 DB가 UNDO 테이블스페이스없이 실행중이라는 경고 로그를 기록합니다.

 

UNDO 관련 초기화 파라미터

초기화 파라미터	설명
UNDO_MANAGEMENT	값이 AUTO or null인 경우 자동UNDO 관리모드입니다.MANUAL인 경우 수동 UNDO 관리 모드를 설정합니다.  기본값은 AUTO입니다.
UNDO_TABLESPACE	선택 사항이며 자동 UNDO 관리 모드에서만 유효합니다. UNDO 테이블 스페이스의 이름을 지정합니다. 데이터베이스에 UNDO 테이블 스페이스가 여러 개 있고 데이터베이스 인스턴스가 특정 UNDO 테이블 스페이스를 사용하도록 지정하려는 경우에만 사용합니다.

 

UNDO_RETENTION

자동 UNDO 관리모드가 활성화되면 UNDO_RETENTION이 있습니다. 이는 Oracle 데이터베이스가 덮어 쓰기 전, 이전 UNDO 데이터를 보존하려고 시도하는 최소 시간입니다. 현재 UNDO_RETENTION 기간보다 오래된 이전 UNDO 정보는 Expired상태로 되며 해당 공간은 새로운 트랜잭션으로 덮어쓸 수 있습니다.

 

Oracle Database는 자동적으로 UNDO 테이블스페이스 크기와 DB 시스템의 활동에 따라 UNDO_RETENTION을 튜닝합니다.

AUTOEXTEND 옵션이 활성화되는 경우, 데이터베이스는 지정된 최소한의 보존기간을 준수하려고 합니다. 만약 공간이 부족하면 NOT EXPIRED 된 UNDO 정보를 덮어쓰는 대신 테이블스페이스가 자동으로 확장됩니다.

 MAXSIZE 절을 구체적으로 명시해주면, 최대 사이즈까지 증가 후 그 다음에도 UNDO 정보가 계속 저장될 필요가 있다면, NOT EXPIRED 상태의 UNDO 정보를 덮어쓰게됩니다.

DBCA로 만들어진 UNTOTBS1은 AUTOEXTEND 상태입니다.

 

아래 명령어로 UNDO_RETENTION을 변경할 수 있습니다.

ALTER SYSTEM SET UNDO_RETENTION = 2400;

 

GURANTEE

오랫동안 실행되는 쿼리 또는 Flashback작업의 성공을 보장하기 위해서 Retention Gurantee를 활성화할 수 있습니다.

해당 기능이 활성화되면 지정된 UNDO 보존이 보장됩니다. 데이터베이스는 UNDO 테이블스페이스 부족으로 인해 트랜잭션이 실패하더라도 NOT EXPIRED 상태의 정보를 덮어쓰지 않습니다. 이 옵션은 기본적으로 비활성화 되어 있습니다.

(이 옵션을 사용하면 DML작업이 실패할 수 있습니다. 사용 시 주의가 필요합니다)

Create Database or Create UNDO Tablespace 시에 RETENTION GURANTEE 옵션을 추가하여 설정가능합니다.

생성 후 나중에 Alter Tablespace 절 뒤에 RETENTION GURANTEE 옵션을 추가하여 설정하여도 됩니다.

 

아래 쿼리로 설정 여부를 확인할 수 있습니다.

select tablespace_name,retention from dba_tablespaces where tablespace_name='UNDOTBS2'

TABLESPACE_NAME                RETENTION  
------------------------------ -----------
UNDOTBS2                       NOGUARANTEE

 

v$undostat 뷰로 Undo_Retention 추적

해당 뷰의 TUNED_UNDORETENTION컬럼을 조회하여 현재 보존기간을 확인할 수 있습니다.

이 View에는 4일동안 10분마다 값이 추가됩니다.(4일이 지난 데이터는 DBA_HIST_UNDOSTAT에서 확인할 수 있습니다)

TUNED_UNDORETENTION은 초단위로 제공됩니다.

SQL> select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
to_char(end_time, 'DD-MON-RR HH24:MI') end_time, tuned_undoretention
from v$undostat 
order by end_time desc;


BEGIN_TIME         END_TIME           TUNED_UNDOR
------------------ ------------------ -----------
14-10월-20 14:39   14-10월-20 14:43         10099
14-10월-20 14:29   14-10월-20 14:39          9933
14-10월-20 14:19   14-10월-20 14:29          9377
14-10월-20 14:09   14-10월-20 14:19          8820
14-10월-20 13:59   14-10월-20 14:09          8261
14-10월-20 13:49   14-10월-20 13:59          7702
...생략

 

참고 : docs.oracle.com/cd/B28359_01/server.111/b28310/undo001.htm#ADMIN11460

 

 

UNDO 사용량 조회(모니터링)

DB의 DML 작업이 많아 UNDO 테이블스페이스에 대한 사용량 조회가 필요할 때 사용할 수 있는 쿼리입니다.

 

각 값들에 대한 설명은 아래와 같습니다.

ACTIVE : 현재 UNDO를 사용하고 있는 영역
UNEXPIRED : UNDO 작업종료, UNDO_RETENTION값이 아직 지나지 않은 영역
EXPIRED :  UNDO_RETENTION값이 지난 값으로 UNDO 부족시 rewrite 될 수 있는 영역

select STATUS, sum(bytes)/1024/1024 MB from dba_undo_extents group by status;

STATUS    MB         
--------- -----------
UNEXPIRED      2.9375
EXPIRED        0.1875
ACTIVE     10236.8125

3 rows selected.

 

아래 쿼리는 어떤 세션이 UNDO를 어느정도 사용하고 있냐를 알 수 있는 쿼리입니다.

SQL> SELECT TO_CHAR(s.sid)||',' ||TO_CHAR(s.serial#) sid_serial,
         NVL(s.username, 'None') orauser,
         s.program,
         r.name undoseg,
         t.used_ublk * TO_NUMBER(x.value)/1024/1024||'M' "Undo"
FROM   sys.v_$rollname    r,
        sys.v_$session     s,
        sys.v_$transaction t,
        sys.v_$parameter   x
WHERE s.taddr = t.addr
AND r.usn   = t.xidusn(+)
AND x.name  = 'db_block_size';

SID_SERIAL       ORAUSER                        PROGRAM          UNDOSEG                        Undo                                     
---------------- ------------------------------ ---------------------------------------------- -----------------------------------------
321,1609         NEWMPDB                        sqlplus.exe      _SYSSMU48$                     1442.078125M    

 

Oracle Database 10g에서 transaction rollback이 수행될 때, 진행중인 transaction이 $SESSION_LOGNOPS view에 이벤트가 기록되기 시작했습니다.
이 view는 10g 이전 데이터베이스에도 존재하였지만, transaction을 rollback하는것은 기록하지 못했습니다. 
rollback의 경우 6초보다 길어지는 경우에 이 view에 기록되어 지며, 
$SESSION_LOGNOPS view는 모든 진행중인 작업에 대해서 보여주는 뷰인데, Transaction rollback이라는 이름으로 찍히면 rollback중이라는것입니다.

 

select OPNAME,TARGET,SOFAR,TOTALWORK,UNITS,START_TIME,TIME_REMAINING, ELAPSED_SECONDS 
from v$session_longops where username='NEWMPDB';

OPNAME         TARGET                               SOFAR       TOTALWORK   UNITS                            START_TIME          TIME_REMAIN ELAPSED_SEC
-------------- ----------------------------------------------- ----------- -------------------------------- ------------------- ----------- -----------
Table Scan     NEWMPDB.MPPDOSEGTRACK_PHY                 67567       67567 Blocks                           2020/10/14 04:30:30           0          11
Table Scan     NEWMPDB.MVALLFLOW                         425003      425003 Blocks                           2020/10/14 04:31:10           0          69
Table Scan     NEWMPDB.MVHEADERS                         859350      859350 Blocks                           2020/10/14 07:56:05           0         566
...생략       

 

인스턴스가 사용하고 있는 ROLLBACK Segment를 보여줍니다.

select * from sys.v_$rollname

USN         NAME                          
----------- ------------------------------
          0 SYSTEM                        
         43 _SYSSMU43$                    
         44 _SYSSMU44$                    
         45 _SYSSMU45$                    
         46 _SYSSMU46$                    
         47 _SYSSMU47$                    
         48 _SYSSMU48$                    
         49 _SYSSMU49$                    
         50 _SYSSMU50$                    
         51 _SYSSMU51$                    
         52 _SYSSMU52$      

 

UNDO TABLESPACE 비우는방법

 

1. 임시 undotbs02번 생성
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/oradata/undotbs02.dbf' SIZE 10M;   

2. 기본 undotbs를 undotbs2로 변경
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;

3. undotbs1 삭제
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

4. undotbs1 재생성
CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/oradata/undotbs01.dbf' SIZE 500M;

5. 기본 undotbs를 undotbs1로 변경
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1;

6. 임시 undotbs2 삭제
DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

참고 : positivemh.tistory.com/181

참고 : makebob.tistory.com/456