(4) Undo Tablespace
Undo Data 란 사용자가 DML 을 수행할 경우 발생하는 원본데이터를 말한다.
이런 Undo Data 만을 저장하는 Segment 를 Undo Segment 라고 하는데
Undo Segment 를 저장하고 있는 특별한 Tablespace 가 Undo Tablespace 이다.
Undo Tablespace 는 관리자가 생성할 수 있고, 관리할 수 있지만
이 곳에는 Undo Data 외의 다른 데이터는 저장될 수 없으며
Undo Data 라고 하더라도 사용자가 관여할 수 없고 Oracle Server Process 가 직접 관리한다.
1) Undo Tablespace 의 특징
1. Oracle Server Process 는 이 Tablespace 에 undo segment 를 생성하고
기본적으로 각 사용자 별로 undo segment 를 할당해서 undo data 를 관리하며 사용자는 관여할 수 없다.
2. Undo tablespace 는 Instance 당 여러 개가 동시에 존재할 수 있지만 사용되는 것은 한번에 1개뿐이다.
(단 특별한 경우 동시에 여러개가 사용되는 경우도 있다.)
3. 관리 방법은 자동 Mode 와 수동 Mode 가 있으며 9i 버전부터는
AUM (Automatic Undo Managament) 방법을 권장하고 있다.
AUM 을 사용하려면 초기화 파라미터 파일 (pfile, spfile) 에 Undo_management=auto 로 설정하고
수동 Mode 로 관리하려면 이 값을 Manual 로 설정하면 된다.
이 값을 설정 안하면 9i 이후 부터는 자동모드가 기본이며
Manual 값은 주로 Undo 관련 장애가 발생했을 경우 복구 용도로 사용된다.
파라미터 값을 변경할 경우 Instance 를 재시작 해야 한다.
2) Undo Tablespace 의 사용 목적
1. Transaction Rollback - 사용자가 rollback 을 수행할 경우 이곳에 저장된 undo data 를 사용해서 rollback 하게 된다.
2. Read Consistency (읽기 일관성) - CR 작업을 통해 트랜잭션이 끝나지 않은 데이터는 변경 전 데이터를 보여준다.
3. Transaction Recovery (Instance Recovery) - 운영중이던 DB 서버가 비정상적으로 종료되었을 때 Roll Forward 와 Roll Backward 작업을 수행해서
Dirty Database 를 Clean Database 로 만들어주는 과정에서 Undo data 가 사용된다.
3) Undo segment 할당되는 원리
Undo Tablespace 의 독특한 특징 중 하나는 Undo Data File 의 크기가 증가만 되고 줄어들지 않는다는 점이다.
Undo Tablespace 안에 할당된 Undo Segment 는
사용자가 DML 을 수행할 경우 해당 사용자의 Server Process 가 가장 먼저 Undo Segment 를 확보하게 되는데,
다른 사용자가 Commit 을 수행하여 트랜잭션이 끝났다면,
다음 사용자의 Server Process 가 해당 Undo Segment 를 덮어쓰고,
모든 사용자가 트랜잭션을을 완료하지 않았다면
Undo Segment 를 더 생성하여 기록하게 된다.
Undo Segment 는 Data File 의 저장 공간이 허용하는 범위까지 늘어나다가
Data File 에 더 공간이 없다면 하나의 Segment 에 2개 세션 이상의 Undo Data 를 함께 기록한다.
Undo Data 가 많아져 Undo Tablespace 가 늘어나도 데이터 파일의 크기는 줄일 수 없다.
Commit 을 수행해도 해당 Undo Segment 안에 Undo Data 는 지워지지 않고 남아있기 때문이다.
사용하지도 않는 Undo Tablespace 의 크기가 비정상적으로 클 경우에는
관리자가 다른 작은 크기의 Undo Tablespace 를 신규로 만들고
신규 Undo Tablespace 로 변경시킨 후 기존 Undo Tablespace 를 삭제해주어야 한다.
- 1번 터미널 -
SCOTT>update emp set sal=5000 where empno=7902 ;
SYS>set line 200
SYS>col username for a10
SYS>select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
2 from v$session a, v$transaction b
3 where a.saddr = b.ses_addr ;
USED_UREC 는 undo 사용 record 수
USED_UBLK 는 사용 중인 undo block 개수
4) Undo Tablespace 관리 실습
실습1. 현재 상태 파악하기
SYS>set line 200
SYS>col name for a10
SYS>show parameter undo ;
실습2. 신규 undo tablespace 생성하기
SYS>create undo tablespace undo01
2 datafile '/app/oracle/oradata/testdb/undo01.dbf' size 10M
3 autoextend on ;
SYS>col tablespace_name for a10
SYS>col file_name for a50
SYS>select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files ;
실습3. Undo tablespace 변경하기 (UNDOTBS1 → UNDO01)
SYS>show parameter undo ;
SYS>alter system set undo_tablespace=undo01;
재부팅 없이 즉시 변경 가능.
pfile 을 사용할 경우 반드시 파라미터 파일의 내용도 변경해야 DB 재시작 후 장애가 없다.
SYS>show parameter undo ;
5) 각 세션 별로 사용중인 undo segment 확인하기
(아래 작업을 하기 전에 터미널 여러 개 확인해서 DML 수행하고 commit 하지 말고 조회하세요)
SYS>select s.sid, s.serial#, s.username, r.name "ROLLBACK SEG"
2 from v$session s, v$transaction t, v$rollname r
3 where s.taddr = t.addr
4 and t.xidusn = r.usn ;
6) Undo 관련 주요 parameter
* UNDO_RETENTION (단위는 초다.)
commit 수행 후에도 해당 undo segment 내의 데이터를 다른 서버 프로세스가 덮어 쓰지 못하도록 막아주는 시간이다.
이 파라미터는 주로 CR 작업으로 Undo Segment 에 있는 데이터를 조회해야 하는 경우에 많이 발생한다.
A가 집계용 Select 수행 중
B가 Update 후 Commit
C가 Undo segment 덮어 씀
아래 에러 발생
ORA-01555. Snapshot too old
이 에러를 막기 위해 사용되는 파라미터가 UNDO_RETENTION 이다
commit 이 완료된 Undo segment 는 다른 사용자에 의해 덮어 쓰여질 수 있지만
이 파라미터로 시간을 설정하면 설정 시간동안 덮어 쓰지 않는다.
그러나 이 파라미터는 Undo segment 여분이 있을 경우에만 적용된다.
* UNDO_RETENTION_GUARANTEE
Undo segment 의 여분의 개수가 없더라도
Undo retention 파라미터로 설정된 시간 동안 재활용하지 않고 지켜준다.
단 commit 을 수행해도 특정 시간동안 재활용하지 않기 때문에
undo tablespace 의 사용량이 증가될 수 있다.
그래서 기본값은 noguarantee 이다.
SYS>select tablespace_name, retention from dba_tablespaces ;
SYS>show parameter undo ;
SYS>alter tablespace undotbs1 retention guarantee ;
SYS>select tablespace_name, retention from dba_tablespaces ;
위의 방법으로 변경하게 되면 Undo_retention 으로 지정한 값을 Guarantee 하게 된다.
SYS>alter tablespace undo01 retention noguarantee ;
SYS>select tablespace_name, retention from dba_tablespaces ;
7) undo tablespace 삭제하기
SYS> drop tablespace undo01 ;
사용중인 undo segment 를 포함하고 있는 undo tablespace 는 삭제할 수 없다.
사용하지 않는데 사용중이라고 뜨면
undo segment 를 _offline_rollback_segments 라는 히든 파라미터를 사용하여 강제 offline 시킨 후 삭제하면 된다.
(5) temporary table space
임시 자료를 저장하는 tablespace 로
db 가 재시작되면 이 곳에 있던 내용은 모두 사라지며
일반적으로는 정렬작업 등을 수행할 경우 pga 공간이 부족하면 이곳을 이용하여 정렬작업 등을 하게 된다.
export / import 등에도 사용한다.
하나의 instance 에 여러개 만들 수 있고
성능 향상을 위해 각 사용자별로 하나씩 할당해 주는 것이 좋다.
정렬 작업이 크게 일어나는 작업 전에는 temporary tablespace 의 크기를 크게 만들어주고 작업하는 것이 성능향상에 좋다
11g 이상이라면
temporary tablespace group 기능 활용 권장
1) 실습
step1. 기존 temporary tablespace 조회하기
SYS>set line 200
SYS>col tablespace_name for a10
SYS>col file_name for a50
SYS>select file_id, tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_temp_files ;
step2. 신규 생성하기
SYS>create temporary tablespace temp2
2 tempfile '/app/oracle/oradata/testdb/temp02.dbf' size 10M
3 autoextend on ;
step3. Default Temporary Tablespace 설정하기
여러 개를 만들어 사용자별로 할당하여 동시에 사용하게 하는 것이 성능에 좋다.
반드시 1개는 Default 로 지정해야 한다.
독자적인 Temporary Tablespace 를 할당받지 못한 사용자나
System tablespace 가 사용하는 temporary tablespace 를 의미한다.
SYS>set line 200
SYS>col property_name for a30
SYS>col property_value for a10
SYS>col description for a50
SYS>select * from database_properties
2 where property_name like 'DEFAULT_TEMP%' ;
SYS>alter database default temporary tablespace temp2 ;
SYS>select * from database_properties
2 where property_name like 'DEFAULT_TEMP%' ;
step4. Temporary Tablespace 크기 변경하기
SYS> alter database tempfile '/app/oracle/oradata/testdb/temp02.dbf' resize 100M ;
step5. Temporary Tablespace 삭제하기
SYS>drop tablespace temp2 ;
default temporary tablespace 는 삭제가 안된다
다른 temporary tablespace 생성 후
SYS>create temporary tablespace temp3
2 tempfile '/app/oracle/oradata/testdb/temp03.dbf' size 10M ;
default 로 변경
SYS>alter database default temporary tablespace temp3 ;
삭제 됨
SYS>drop tablespace temp ;
2) Temporary tablespace group (11g New Feature)
일반적인 DB 접속 환경을 보면 여러 명의 유저가 1개의 schema 를 통해 DB 에 접속하는 경우가 많다.
여러 사용자가 Order by 를 하였을 때 PGA 가 부족하면 temporary tablespace 를 사용할 텐데 작업이 늦어지게 될 수 가 있다.
여러 개의 temporary tablespace 를 만든 후 그룹으로 묶어 주고 특정 schema 에게 temporary tablespace group 을 지정해주면
하나의 스키마로 여러 명의 사용자가 동시에 로그인 하더라도 temporary tablespace group 안에 있는 여러개의 temp tablespace 를
각각 별도로 사용해서 성능 향상이 이루어질 수 있다.
SYS>create temporary tablespace temp_scott1
2 tempfile '/app/oracle/oradata/testdb/temp_scott01.dbf' size 10M
3 tablespace group temp_scott_group ;
이미 만들어져 있는 tempoary tablespace 를 그룹에 추가하려면
SYS>alter tablespace temp_scott2 tablespace group temp_scott_group ;
할당되어 있는 그룹을 조회하려면
SYS>select * from dba_tablespace_groups ;
만들어진 그룹을 사용자에게 할당하려면
SYS>alter user scott temporary tablespace temp_scott_group ;
SYS>alter database default temporary tablespace temp_scott_group ;
그룹에서 탈퇴하려면
SYS>alter tablespace temp_scott2 tablespace group ' ' ;
공백을 주면 그룹이 없는 것으로 인식되어 탈퇴하게 된다.