Undo segment를 과도하게 사용하여
예를 들어, 개발한 배치, 개발자의 임의 작업 등이 잘못 진행되고 있다가 뒤늦게 발견하여 cancel했을 경우라던가 할 때
이를 rollback을 하는 중이지만 UNDO tablespace의 unexpire 영역이 반환되지 않아,
정규 배치 등의 서비스가 실패할 가능성이 있는 시나리오에 대응하려면
다음 쿼리들을 통해 문제를 해결하도록 한다.
Rollback 진행 중인 undo segment를 조회하는 쿼리
parallel rollback 일 경우
select * from v$fast_start_transactions where state='RECOVERING';
serial rollback 일 경우 (x$KTUXE(Kernel Transaction Undo Transaction Entry))
select ktuxesiz from sys.xm$ktuxe where ktuxesta = 'ACTIVE' and KTUXECFL = 'DEAD' ;
Undo tablespace의 사용량은 다음과 같이 확인한다.
col msg format a80
WITH TB AS (
SELECT TABLESPACE_NAME
,(SELECT ROUND(SUM(BLOCKS)*8/1024) FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=A.TABLESPACE_NAME) TOT_MB
,SUM(DECODE(A.STATUS,'ACTIVE' ,SIZE_MB,0)) ACTIVE_MB
,SUM(DECODE(A.STATUS,'UNEXPIRED',SIZE_MB,0)) UNEXPI_MB
,SUM(DECODE(A.STATUS,'EXPIRED' ,SIZE_MB,0)) EXPIRE_MB
FROM (
SELECT TABLESPACE_NAME, STATUS, ROUND(SUM(BYTES)/1024/1024) SIZE_MB
FROM DBA_UNDO_EXTENTS
--WHERE tablespace_name in ( select value from v$parameter where name='undo_tablespace')
GROUP BY TABLESPACE_NAME, STATUS
) A
GROUP BY TABLESPACE_NAME
)
SELECT to_char(sysdate,'HH24:MI:SS')||' '||TABLESPACE_NAME||' : '||round((active_mb+unexpi_mb+expire_mb)/tot_mb*100)||
' % -> Act('||active_mb||') Unexp('||unexpi_mb||') Exp('||expire_mb||') TOT('||TOT_MB||')' MSG,
round(100*(ACTIVE_MB)/TOT_MB,2),
round(100*(ACTIVE_MB+UNEXPI_MB)/TOT_MB,2)
FROM TB;
현재 진행되고 있는 rollback의 잔여시간은 다음 쿼리로 대충 추산할 수 있다.
set serveroutput on
declare
l_start number ;
l_end number ;
r_min number ;
begin
select ktuxesiz into l_start from sys.xm$ktuxe where ktuxesta = 'ACTIVE' and KTUXECFL = 'DEAD' ;
dbms_lock.sleep(60);
select ktuxesiz into l_end from sys.xm$ktuxe where ktuxesta = 'ACTIVE' and KTUXECFL = 'DEAD' ;
r_min := round(l_end / (l_start - l_end) ) ;
dbms_output.put_line('['||to_char(sysdate,'HH24:MI:SS')||'] Remain Time : '|| r_min ||'분 '|| round(r_min/60,2)||'시간 / '||(l_start-l_end)||' blocks done / '|| l_end ||' Total blocks remains' );
end ;
/
운영DB에 임시 UNDO Tablespace를 생성한 후, 향후의 트랜잭션들은 이 UNDO tbs를 사용하도록 조치한다.
create undo tablespace undotbs2 datafile '/dev/rdsk/vsp1_000_ra000/dat_000_20G_001';
alter tablespace undotbs2 add datafile '/dev/rdsk/vsp1_000_ra000/dat_000_20G_002';
...
alter tablespace undotbs2 add datafile '/dev/rdsk/vsp1_000_ra000/dat_000_20G_NNN';
alter system set undo_tablespace=undotbs2 ;
이때 UNDO tablespace를 변경하는 것 자체는 금방 수행되므로 긴장하지 않아도 된다.
다만, alert log에 'active transactions found in undo tablespace NN - moved to pending switch-out state 라는 로그가 트랜잭션 당 1 라인씩 계속 발생할 수 있는데, 어떤 사이트의 경우에는 이 메시지가 다량 발생하기도 하였다고 하므로, alert log가 존재하는 path의 가용량을 모니터링하면서 작업 수행하자.
이러한 세션들은 세션에서 commit을 찍고 알아서 종료되길 기다리거나, 아니면 kill하여 처리하면 된다.
select a.usn, a.name, b.status, c.tablespace_name, d.addr, e.sid, e.serial#, e.username, e.program, e.machine, e.osuser
from v$rollname a, v$rollstat b, dba_rollback_segs c, v$transaction d, v$session e
where a.usn=b.usn
and a.name = c.segment_name
and a.usn = d.xidusn
and d.addr = e.taddr
and b.status = 'PENDING OFFLINE';
rollback이 종료되고 상황이 정상화 되면,
기존 UNDO tablespace를 사용하도록 파라미터를 재설정해주고,
추가했었던 UNDO tablespace를 drop하면 된다.
