[ORACLE] UNDO Tablespace 관리 및 재생성

당신은 Undo Tablespace를 32TB로 만들어본 적이 있는가?

나는 있다..

 

때는.. 저번주.. 

내가 이관해논 DB를 duplicate 뜨고계시던 선임님께서..

왜이리 오래걸리지..

하고..

Datafile을 확인했는데 세상에나 마상에나 내가 언두 관리를 안해서 32테라로 만들어놨었다

ㅎㅎ ; 멋슥 ;; 재삼다 .. ;; 

 

오늘의 주제 : 이렇게 늘어난 언두 데이터파일 (TBS) 어떻게 관리 하나요 ? ? ?

 

UNDO 가 몬데..

undo data

Oracle은 Database 변경사항을 rollback 하거나 실행취소(undo) 하는데 사용되는 정보를 생성하고 관리한다.

undo segment에는 이러한 트랜잭션에 대한 이전정보와 데이터가 저장되는데, 만료(Expired)되거나 공간 재사용에 의해 덮어씌워질 수 있다.

ORA-01555 : Snapshot too old 에러는 이와 같이 undo와 관련되어 가장 빈번히 발생하는 error인데

undo segment가 덮어 씌워져서 발생하는 오류이다. 

만약 쿼리1이 막 10분동안 도는 쿼리야 근데 1분째 실행했을때 undo 세그먼트에 기록된게 다른 transaction들이 많이 들어와서 덮어씌워져부렀어 그럼 쿼리1은 마지막까지 수행 끗 ~ 하고 처음꺼를 undo가서 찾아야돼 근데 덮어씌워져서 없어졌어 그러면 저 오류 남

 

 

undo 언제 쓰이는건데 ..

- rollback transaction (사용자가 rollback 날릴때)

   -> rollback문이 실행되면 커밋되지 않은 트랜잭션에 의해 변경된 내용을 예전으로 돌려야되는데 그때 사용되는게 바로 이 undo undo 

- recover database (복구)

- Read Consistency (읽기 일관성) 제공

- Oracle Flashback Query 사용해서 이전 시점 데이터 분석

- Oracle Flashback 기능 사용해 logical 손상 복구

 

Undo Retention

: Database가 오래된 정보를 덮어쓰기 전에 유지하려고 하는 최소 시간

--> 이 시간동안에는 undo data (변경된 데이터의 전 값)이 보장된다

 

UNDO PARAMETER 관리

1. undo_tablespace

select * FROM gV$parameter 
WHERE NAME LIKE 'undo_tablespace';

또는

show parameter undo_tablespace

# 변경
alter system set undo_tablespace=UNDOTBS명 scope=both sid=ORACLESID ;

2. undo_retention

select INST_ID,NAME,VALUE
FROM gv$parameter 
WHERE NAME LIKE 'undo_retention';

또는

show parameter undo_retention

# 변경
alter system set undo_retention=10800 scope=both ;

3시간

 

현재 UNDO STATUS 조회

# UNDO TABLESPACE
select * FROM DBA_TABLESPACES 
WHERE TABLESPACE_NAME LIKE 'UNDOTBS%'
;
	
# UNDO SIZE
SELECT SUM(BYTES)/1024/1024/1024
FROM DBA_DATA_FILES
WHERE 1=1
    AND TABLESPACE_NAME LIKE 'UNDOTBS%'
;

# UNDO EXTENTS
SELECT TABLESPACE_NAME,STATUS,COUNT(1)
FROM DBA_UNDO_EXTENTS
GROUP BY TABLESPACE_NAME,STATUS
;

 

- DBA_UNDO_EXTENTS 조회

STATUS

EXPIRED	undo_retention 시간을 초과한 Extent 사용 중인 transaction이 없고 undo retention도 지나서 트랜잭션에 할당될 수 있는 extent
UNEXPIRED	undo_retention 시간을 초과하지 않은 Extent 사용 중인 transaction이 없으나, 시간이 지나지 않아서 트랜잭션에 할당되어 있고, 보존된 상태
ACTIVE	트랜잭션에 할당되어 undo data 기록 중인 상태
FREE	생성 이후 transaction에 한번도 할당되지 않았거나 SMON에 의한 주기적 정리가 완료되어 있는 상태.

Extent Stealing : 트랜잭션이 끝났지만, 언두 리텐션에 의하여 유지되고 있는 undo Extent의 reuse 기능

Extent 할당 순서

1. 자기 자신 Extent의 Free 블록 조회
2. 다음 Extent가 Expired인지 확인
3. Undo TBS에서 새로운 Extent 할당
4. offline Transaction Table에서 Expired Extent 가져옴 (Stealing)
5. Online Transaction Table에서 Expired Extent 가져옴 (Stealing)
6. autoextend = YES일 시 파일을 확장하여 Extent 할당
7. 자신의 Transaction Table에서 Unexpired Extent 재사용
8. Offline Transaction Table에서 Unexpired Extent 가져옴

 

 

그래서 어떻게 tbs 줄였냐구요 !

 

1. undo 현재 상태 조회 (위에 참조)

2. 새로운 Undo TBS 생성 (임시 사용)

# node 1이 쓸거
CREATE UNDO TABLESPACE UNDOTBS3 DATAFILE '+DATA' SIZE 30G;
# node 2가 쓸거
CREATE UNDO TABLESPACE UNDOTBS4 DATAFILE '+DATA' SIZE 30G;

3. parameter 변경

# node 1만 변경
alter system set undo_tablespace='UNDOTBS3' SCOPE=both SID='node1' ;
# node 2만 변경
alter system set undo_tablespace='UNDOTBS4' SCOPE=both SID='node2' ;

4. 재기동 (필요 시 / transaction 정리)

-> 재기동 하지 않아도 parameter 자체는 바로 반영되긴 함

# node 1 재기동
srvctl stop instance -d DBNAME -i ORACLESID1
srvctl start instance -d DBNAME -i ORACLESID1

# node 2 재기동
srvctl stop instance -d DBNAME -i ORACLESID2
srvctl start instance -d DBNAME -i ORACLESID2

---- 시간이 흐른..뒤 ..-----

# Transaction 정리 및 retention이 지난 후 expired된 기존의 undo tablespace 삭제
DROP tablespace UNDOTBS1 including contents and datafiles ;
DROP tablespace UNDOTBS2 including contents and datafiles ;

 

5. 다시 이름 1,2로 바꾸기 위해 새로 생성

CREATE  UNDO tablespace UNDOTBS1 DATAFILE '+DATA' SIZE 30G ;
CREATE  UNDO tablespace UNDOTBS2 DATAFILE '+DATA' SIZE 30G ;


# 1.2TB 씩 부여
alter undo tablespace undotbs1 add datafile '+DATA' SIZE 30g ; 
alter undo tablespace undotbs1 add datafile '+DATA' SIZE 30g ; 
alter undo tablespace undotbs1 add datafile '+DATA' SIZE 30g ; 

alter undo tablespace undotbs2 add datafile '+DATA' SIZE 30g ; 
alter undo tablespace undotbs2 add datafile '+DATA' SIZE 30g ; 
alter undo tablespace undotbs2 add datafile '+DATA' SIZE 30g ;

6. parameter 원복

alter system set undo_tablespace='UNDOTBS1' SCOPE=both SID='node1' ;
alter system set undo_tablespace='UNDOTBS2' SCOPE=both SID='node2' ;

7. 필요 시 재기동