Undo의 원리와 운영: Rollback·Read Consistency·AUM·ORA-01555

핵심 요약

Undo는 Database Block을 변경하기 전 상태를 되돌리거나 과거 버전을 재구성할 수 있도록 Oracle이 남기는 내부 기록입니다. 핵심 역할은 다음과 같습니다.

1. Transaction Rollback
   → COMMIT하지 않은 변경을 이전 상태로 되돌림

2. Transaction Recovery
   → Instance Recovery의 Roll Forward 후 미완료 Transaction을 취소

3. Read Consistency
   → Statement 또는 Transaction Snapshot 시점의 과거 Block 버전을 재구성

4. Flashback 지원
   → 보존된 Undo 범위 안에서 과거 시점 조회·논리적 복구 기능을 지원

Redo는 변경을 다시 적용할 Change Vector를 기록하고, Undo는 변경 전 상태를 복원할 정보를 기록합니다. Redo에는 Data·Index Block뿐 아니라 Undo Block과 Undo Segment의 Transaction Table 변경도 포함되므로, Recovery에서는 Redo로 Undo까지 재생성한 뒤 미완료 Transaction을 Undo로 취소할 수 있습니다.

Automatic Undo Management(AUM)에서는 Oracle이 Undo Segment의 생성·할당·순환 재사용을 관리합니다. 운영에서는 UNDO_RETENTION 설정값만 보지 않고 Undo Tablespace 크기·Autoextend 한도, 실제 Undo 발생률, 가장 긴 Query, TUNED_UNDORETENTION, Unexpired Undo 재사용, ORA-01555와 공간 부족 횟수를 같은 시간 구간으로 연결해야 합니다.

학습 목표

이 이론을 학습한 뒤에는 다음을 설명할 수 있어야 합니다.

1. Undo와 Redo의 목적을 구분한다.
2. INSERT·UPDATE·DELETE가 어떤 종류의 Undo를 만드는지 설명한다.
3. Query SCN과 Undo를 이용한 Read Consistency의 원리를 이해한다.
4. Active·Unexpired·Expired Undo의 재사용 가능성을 구분한다.
5. UNDO_RETENTION과 TUNED_UNDORETENTION의 차이를 설명한다.
6. RETENTION GUARANTEE의 이점과 DML 실패 위험을 함께 판단한다.
7. ORA-01555가 발생하는 과정을 단계별로 설명한다.
8. V$UNDOSTAT과 V$TRANSACTION으로 Undo 보존 능력과 Active Transaction을 진단한다.
9. Temporary Undo와 Permanent Undo의 적용 대상을 구분한다.

1. Undo가 필요한 이유

계좌 이체 업무를 다음과 같이 처리한다고 가정합니다.

UPDATE account
SET    balance = balance - 10000
WHERE  account_id = 1;

UPDATE account
SET    balance = balance + 10000
WHERE  account_id = 2;

COMMIT;

두 UPDATE는 하나의 업무 단위입니다. 두 번째 UPDATE에서 오류가 발생했다면 첫 번째 UPDATE도 취소해야 계좌 합계가 유지됩니다. Oracle은 변경 전 상태를 Undo에 기록하므로 ROLLBACK을 이용해 Transaction 전체를 이전 상태로 돌릴 수 있습니다.

현재 값 변경
→ 변경 전 정보를 Undo에 기록
→ Table·Index Block 변경
→ COMMIT 또는 ROLLBACK까지 Transaction 유지

Undo에는 변경된 Block과 Row를 복원하는 데 필요한 내부 변경 정보가 저장됩니다.

2. Undo와 Redo의 차이

Instance 장애 후 복구는 개념적으로 다음 순서로 이해할 수 있습니다.

Redo 적용
→ Datafile에 반영되지 않았던 변경 재현
→ 이 과정에는 Commit된 변경과 미완료 변경이 함께 포함될 수 있음

Undo 적용
→ 장애 시점에 끝나지 않은 Transaction의 변경 취소

Redo는 Undo Segment Block과 Transaction Table의 변경도 보호합니다. 따라서 Roll Forward 과정에서 필요한 Undo 구조도 재생성할 수 있습니다. 그 뒤 Transaction Recovery가 Undo를 적용해 미완료 변경을 취소합니다. Redo만으로는 미완료 Transaction을 제거할 수 없고, Undo만으로는 장애 후 Commit된 변경을 재현할 수 없습니다.

3. DML별 Undo의 의미

INSERT

새로 추가한 Row를 Rollback할 때 제거할 수 있도록 Row와 관련된 내부 정보를 남깁니다.

INSERT INTO orders(order_id, customer_id, amount)
VALUES (1001, 10, 50000);

Rollback 시에는 방금 추가한 Row와 관련 Index Entry를 제거해야 합니다.

UPDATE

변경 전 값인 Before Image를 복원할 수 있는 정보를 남깁니다.

UPDATE orders
SET    amount = 70000
WHERE  order_id = 1001;

AMOUNT가 인덱스 컬럼이라면 Table Row뿐 아니라 Index Entry 변경에도 Undo가 필요합니다.

DELETE

삭제 전 Row를 되살릴 수 있는 정보를 남깁니다.

DELETE FROM orders
WHERE  order_id = 1001;

Table과 관련 Index에서 발생한 변경 모두 Undo와 Redo를 생성할 수 있습니다. 같은 행 수를 변경하더라도 인덱스 수, 변경 컬럼, Row 크기에 따라 Undo 발생량은 달라집니다.

4. Query SCN과 Read Consistency

Oracle의 일반 조회는 한 시점에 일관된 결과를 반환합니다. 기본 READ COMMITTED 격리 수준에서는 Query가 시작될 때 기준 SCN을 정합니다.

Query 시작
→ Query SCN 결정
→ 필요한 Data Block 확인
→ Block이 Query SCN 이후에 변경되었다면 Undo 적용
→ Query SCN 시점의 CR(Consistent Read) Block 재구성
→ 결과 반환

다음 상황을 가정합니다.

-- Session A
UPDATE account
SET    balance = balance - 10000
WHERE  account_id = 1;
-- COMMIT하지 않음

-- Session B
SELECT balance
FROM   account
WHERE  account_id = 1;

Session B의 일반 SELECT는 Session A의 미완료 값을 읽지 않습니다. Oracle은 Undo를 이용해 Session B의 Query SCN에 맞는 이전 버전을 구성합니다. 이 때문에 일반적인 Reader와 Writer는 서로를 직접 막지 않고 동시에 작업할 수 있습니다.

동일 Session은 자신이 Transaction 안에서 변경한 값은 볼 수 있습니다.

UPDATE account
SET    balance = balance - 10000
WHERE  account_id = 1;

SELECT balance
FROM   account
WHERE  account_id = 1;
-- 자신의 미완료 변경 결과를 확인

Consistent Read와 Current 처리

동시 Transaction이 같은 Row를 변경하면 대기, 조건 재평가, Statement Restart 같은 추가 동작이 나타날 수 있습니다. 상세 Lock과 동시 갱신 제어는 다음 Lock, 동시성 제어 단원에서 다룹니다.

5. Transaction ID·ITL·UBA의 연결

Oracle은 활성 Transaction을 Undo Segment 내부의 Transaction Table Entry로 관리합니다. Transaction ID는 개념적으로 다음 요소로 식별됩니다.

Transaction ID
= Undo Segment Number
+ Transaction Table Slot
+ Sequence Number

Data Block Header의 ITL(Interested Transaction List)은 해당 Block을 변경한 Transaction 정보를 기록합니다. Undo Block Address(UBA)는 변경을 되돌리거나 과거 버전을 만들 때 따라갈 Undo Record의 위치를 나타냅니다.

Data Block의 ITL
→ Transaction ID 확인
→ Undo Segment의 Transaction Table 확인
→ UBA를 따라 Undo Record 탐색
→ 이전 Row·Block 버전 재구성

활성 Transaction의 Undo 사용량은 다음과 같이 확인할 수 있습니다.

SELECT s.sid,
       s.serial#,
       t.xidusn,
       t.xidslot,
       t.xidsqn,
       t.used_ublk,
       t.used_urec,
       t.start_time
FROM   v$transaction t
JOIN   v$session s
  ON   s.taddr = t.addr
ORDER BY t.used_ublk DESC;

이 정보는 어떤 Session이 큰 Transaction을 유지하고 있는지 확인하는 출발점입니다.

6. Automatic Undo Management와 Undo 상태

Automatic Undo Management에서는 일반적으로 다음 설정을 사용합니다.

UNDO_MANAGEMENT = AUTO
UNDO_TABLESPACE = 현재 사용 Undo Tablespace
UNDO_RETENTION  = Undo 보존의 Low Threshold 값(초)

Undo Extent는 사용 상태에 따라 다음처럼 이해할 수 있습니다.

Undo Segment는 순환 방식으로 공간을 재사용합니다. Commit된 Undo를 영구 보관하는 Backup 영역이 아니므로, 오래된 버전이 계속 남는다는 전제로 설계하면 안 됩니다.

Permanent Undo와 Temporary Undo

일반 Table·Index 변경의 Undo는 Undo Tablespace의 Permanent Undo Segment에 저장됩니다. TEMP_UNDO_ENABLED=TRUE인 환경에서는 Temporary Table 변경의 Undo를 Temporary Tablespace의 Temporary Undo Segment에 저장할 수 있습니다. Temporary Object 변경은 복구용 Online Redo가 필요하지 않으므로, Temporary Undo를 사용하면 Permanent Undo와 Redo 사용량을 줄일 수 있습니다.

Permanent Object DML
→ Undo Tablespace의 Undo Segment

Temporary Table DML + TEMP_UNDO_ENABLED=TRUE
→ Temporary Tablespace의 Temporary Undo Segment

Temporary Undo도 해당 Session의 Rollback과 Read Consistency에는 필요하지만, Permanent Object의 ORA-01555 진단과 동일한 저장 구조로 단정하지 않습니다.

현재 설정은 다음과 같이 확인할 수 있습니다.

SELECT name, value
FROM   v$parameter
WHERE  name IN (
         'undo_management',
         'undo_tablespace',
         'undo_retention'
       )
ORDER BY name;

SELECT tablespace_name,
       status,
       contents,
       retention
FROM   dba_tablespaces
WHERE  contents = 'UNDO';

7. UNDO_RETENTION과 TUNED_UNDORETENTION

UNDO_RETENTION은 초 단위의 Low Threshold·Best-Effort 목표값입니다. 설정값은 Undo 공간을 새로 만들지 않으며, Guarantee가 없으면 실제 보존시간을 보장하지 않습니다.

설정 목표
UNDO_RETENTION

실제 구간별 보존 능력
V$UNDOSTAT.TUNED_UNDORETENTION

Fixed-Size Undo Tablespace

Fixed-Size Undo Tablespace에서는 Database가 현재 크기와 Undo 발생량을 바탕으로 가능한 최선의 보존시간을 자동 조정합니다. UNDO_RETENTION을 크게 설정해도 공간이 늘어나지 않으므로, 설정값보다 Tablespace 크기와 실제 TUNED_UNDORETENTION이 중요합니다.

AUTOEXTEND Undo Tablespace

AUTOEXTEND가 가능하면 Oracle은 필요 시 Datafile을 확장하면서 UNDO_RETENTION 목표를 지키려고 합니다. 그러나 MAXSIZE나 Storage 한도에 도달하고 Active Transaction이 새 Undo를 요구하면, NOGUARANTEE 환경에서는 Unexpired Undo가 재사용될 수 있습니다.

AUTOEXTEND 여유 있음
→ 공간 확장으로 Retention 목표를 지키려 시도

MAXSIZE·Storage 한도 도달
→ NOGUARANTEE이면 Unexpired Undo 재사용 가능
→ GUARANTEE이면 새 DML의 Undo 할당 실패 가능

TUNED_UNDORETENTION은 Oracle이 Undo Tablespace 구성과 Workload를 고려해 계산한 실제 보존시간 지표입니다. V$UNDOSTAT은 일반적으로 10분 구간 통계를 최근 4일 동안 보관하며, 더 오래된 구간은 AWR 사용 환경에서 DBA_HIST_UNDOSTAT으로 확인할 수 있습니다.

SELECT begin_time,
       end_time,
       maxquerylen,
       maxqueryid,
       tuned_undoretention,
       undoblks,
       txncount,
       unxpstealcnt,
       unxpblkrelcnt,
       unxpblkreucnt,
       ssolderrcnt,
       nospaceerrcnt
FROM   v$undostat
ORDER BY begin_time DESC
FETCH FIRST 24 ROWS ONLY;

단순히 MAXQUERYLEN > TUNED_UNDORETENTION이면 반드시 실패한다고 단정하지는 않습니다. Query가 실제로 접근한 Block의 필요한 Undo Chain이 남아 있으면 성공할 수 있습니다. 반대로 Query가 짧아도 높은 Undo 발생률과 공간 압박으로 필요한 Undo가 재사용되면 ORA-01555가 발생할 수 있습니다. 따라서 시간 비교는 위험 신호이며, 재사용·오류 지표와 함께 해석합니다.

8. RETENTION GUARANTEE

Undo Tablespace에 RETENTION GUARANTEE를 설정하면 현재 Retention 기간 안의 Unexpired Undo를 새 Transaction 공간 확보 목적으로 덮어쓰지 않습니다. 이는 Query·Flashback 보호를 강화하지만, Active Transaction이 사용할 Free·Expired 공간을 얻지 못하면 DML이 실패할 수 있습니다.

ALTER TABLESPACE undotbs1
RETENTION GUARANTEE;

NOGUARANTEE
→ Active Transaction을 위해 Unexpired Undo를 재사용할 수 있음
→ Query가 ORA-01555를 만날 가능성 증가

GUARANTEE
→ Unexpired Undo 보존
→ Undo 공간이 부족하면 새로운 DML이 실패할 수 있음

Guarantee는 현재 공간 안에서 과거 버전 보호와 새로운 DML 성공 가능성의 우선순위를 바꾸는 설정이며, Tablespace 용량 자체는 늘리지 않습니다.

9. ORA-01555: Snapshot Too Old

ORA-01555는 Reader가 Consistent Read를 위해 따라가야 하는 Rollback Record가 다른 Writer의 Undo 생성으로 이미 덮어써졌을 때 발생합니다. “긴 Query” 자체가 직접 원인은 아니며, Query가 필요로 하는 Undo Chain과 실제 재사용 시점의 충돌이 핵심입니다.

1. 긴 Query가 Query SCN을 확보
2. 다른 Transaction이 같은 데이터 영역을 계속 변경·Commit
3. Undo 공간 압박으로 필요한 과거 Undo가 재사용
4. Query가 늦게 해당 Block에 도달
5. Query SCN 버전을 재구성하지 못함
6. ORA-01555 발생

대표 위험 조건은 다음과 같습니다.

• Query 실행 시간이 실제 Undo 보존 시간보다 김
• 대량 DML이 짧은 시간에 많은 Undo를 생성함
• Undo Tablespace가 Workload에 비해 작음
• Autoextend 한도 또는 Storage 여유가 부족함
• Cursor를 오래 열어 둔 채 같은 데이터 집합을 반복 갱신·Commit함
• Flashback 조회가 보존된 Undo 범위를 넘어감

잦은 Commit이 일반 해결책이 되지 않는 이유

Commit 빈도 증가
→ 업무 Transaction 원자성과 재시작 경계가 더 작게 분할
→ Commit Call·Redo Flush 부담 증가
→ 완료된 Transaction의 Undo가 Active 상태에서 벗어나 순환 재사용 대상이 될 수 있음
→ 이미 열린 Cursor가 필요한 과거 버전을 자동으로 더 오래 보호하지는 못함

Commit을 자주 한다고 Undo 생성량 자체가 사라지는 것은 아닙니다. Row별 Commit은 오히려 Cursor를 오래 열어 둔 Fetch-While-Update 형태에서 필요한 Undo가 반복적으로 재사용될 가능성을 높일 수 있습니다. Commit Unit은 업무 원자성·Lock·Undo·재시작 요구로 정하고, ORA-01555는 Query 시간과 Plan, Undo 발생률, Tablespace 용량을 먼저 진단합니다.

대량 Batch에서 Commit 단위를 조정할 수는 있지만, ORA-01555를 해결하기 위한 첫 조치는 다음 순서가 적절합니다.

1. 실패한 SQL의 실제 실행시간과 Fetch 완료시간 확인
2. V$UNDOSTAT의 MAXQUERYLEN, TUNED_UNDORETENTION, SSOLDERRCNT 확인
3. 해당 시간대 Undo 생성량과 대량 DML 확인
4. Query Plan과 불필요한 작업량 개선
5. Undo Tablespace 크기·Autoextend·Storage 검토
6. Flashback 요구시간과 UNDO_RETENTION 검토
7. 필요성과 공간을 확인한 뒤 Guarantee 검토

10. Undo를 이용한 Flashback과의 관계

Oracle Flashback Query도 과거 버전을 재구성하기 위해 Undo를 사용할 수 있습니다.

SELECT account_id, balance
FROM   account
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE)
WHERE  account_id = 1;

조회하려는 시점의 Undo가 이미 재사용됐다면 Flashback Query도 원하는 과거 데이터를 만들 수 없습니다. 따라서 Flashback 요구시간은 다음과 함께 설계해야 합니다.

필요한 과거 조회 시간
+ 실제 Undo 발생률
+ Undo Tablespace 크기
+ UNDO_RETENTION
+ RETENTION GUARANTEE 정책

Undo는 Backup을 대체하지 않습니다. 장기간 보존과 장애 복구는 Backup·Archive Log·Flashback Data Archive 등 요구 목적에 맞는 별도 기능을 사용해야 합니다.

혼동하기 쉬운 판단과 정확한 기준

실전 진단 순서

1. 오류가 발생한 SQL ID와 정확한 시작·종료 시간을 확인합니다.
2. SQL이 결과를 모두 Fetch할 때까지 걸린 시간을 확인합니다.
3. 같은 구간의 V$UNDOSTAT를 조회합니다.
4. MAXQUERYLEN과 TUNED_UNDORETENTION을 비교합니다.
5. SSOLDERRCNT, UNXPSTEALCNT, UNXPBLKRELCNT, UNXPBLKREUCNT, NOSPACEERRCNT를 확인합니다.
6. 대량 UPDATE·DELETE·MERGE·Batch가 Undo를 급증시켰는지 확인합니다.
7. Active Transaction의 USED_UBLK, USED_UREC를 확인합니다.
8. SQL Plan을 개선해 Query 시간을 줄일 수 있는지 검토합니다.
9. Undo Tablespace 크기·Autoextend·Storage 한도를 점검합니다.
10. Fixed Size·AUTOEXTEND·MAXSIZE·Storage 한도와 Guarantee 정책을 함께 결정합니다.
11. 4일 이전 분석이 필요하면 AWR의 DBA_HIST_UNDOSTAT 가용성을 확인합니다.