[이론] chapter 07. Tablespace / Data File 관리

- 데이터를 저장하고 작업을 할 때 메모리에 논리적으로 Tablespace라는 공간을 만들어 작업하고 --DB Buffer cache에 Tablespace를 생성하여 작업

물리적으로는 디스크에 Data File을 만들어서 저장

- 작업은 메모리에서, 데이터의 저장은 디스크에

메모리 : 속도 good, 휘발성메모리

디스크 : 속도 bad, 영구저장가능

---

1. Tablespace의 종류 및 특징

(1) SYSTEM tablespace

- 데이터 딕셔너리, 운영+튜닝 정보들 저장

- SYSTEM Tablespace는 SYS 계정의 소유 but, 소유자 조차도 테이블 내용 변경 불가, 조회만 가능

데이터딕셔너리 (Data Dictionary)

- 오라클 서버의 모든 정보를 저장하고 있는 아주 중요한 테이블 or 뷰

- 오라클에서 사용하는 거의 모든 메타 정보나 운영관련된 중요한 정보들이 들어있음

- 이곳에서 조회되는 정보들을 이용해서 서버가 운영됨

버전별 딕셔너리 테이블 개수

SQL > SELECT count (*) FROM dictionary;

- 9i : 1284 (9.2.0.4 기준)

- 10g : 1882 (10.2.0.5 기준)

- 11g : 2600 (11.2.0.2 기준)

- 데이터 딕셔너리

* Static Dictionary 테스트 : STATIC DICTIONRY 중 하나인 USER_TABLES 테이블에 자동으로 업데이트가 되는지 확인

(2) SYSAUX tablespace

- Oracle 10g 버전부터 새로 등장한 Tablespace

- 9i 버전까지는 튜닝관련 딕셔너리들이 모두 SYSTEM Tablespace에 들어있었지만

- 10g 버전부터 성능튜닝과 관련된 딕셔너리들이 이곳에 별도로 저장

- 10g에 새로등장한 자동튜닝기능들(AWR, ADDM, ASH 등)이 이 딕셔너리의 정보를 사용

(3) 일반 Tablespace

- 일반적으로 가장 많이 사용되는 Tablespace

- 관리자가 필요에 의해서 만드는 Tablespace >> DBA의 뜻대로 얼마든지 생성하고 삭제 가능

(4) Undo Tablespace

- Undo Data : 사용자가 DML을 수행할 경우에 발생하는 원본 데이터 (A->B로 변경시 A에 해당하는 데이터)

- Undo Segment : Undo Data만 저장하는 세그먼트

- Undo Tablespace : Undo segment를 저장하고있는 특정 Tablesapce, 관리자가 생성가능, Oralce server process가 관리

1) Undo Tablespace 특징

- Oralce server process가 Undo Tablespace에 Undo segment 생성

- 각 사용자별로 Undo segment 할당해서 Undo Data 관리 >> 사용자는 관여 불가

- Instance당 여러개가 동시에 존재 가능 but 사용되는 것은 한번에 1개!

- 관리방법 : 수동모드, 자동모드(9i 부터 Automatic Undo Management 권장 >> AUM)

* Automatic Undo management (AUM)

- 자동 관리 모드를 사용하기 위해서는 초기화 파라미터 파일(pfile, spfile)에 값이 설정되어 있어야 한다.

- Undo_management = auto / manual

- 9i 부터는 auto가 기본값

- manual값은 주로 Undo 관련 장애 발생시 복구용도로 사용

- 파라미터값 변경시에는 Instance 재시작해야 적용됨

2) Undo Tablespace 사용목적

① Transaction Rollback

- 사용자가 rollback 명령어 수행할 경우 undo data를 사용해서 수행

② Read Consistency (읽기 일관성)

- A사용자가 변경작업을 수행하는중(commit 전) B사용자가 해당 블록의 데이터를 조회할때 Undo data를 DB Buffer cache로 복사해와서 제공하는것

- DML이 진행중인 데이터의 변경전 값을 사용자가 볼수 있게 함.

③ Transaction Recovery (Instance Recovery)

- 운영중이던 DB가 정상적으로 종료되었을때 (ex.정전) Roll forward와 Roll Backward 작업을 수행하는 과정에서 Undo Data 사용됨

3) Undo segment 할당되는 원리

- Undo Data File의 크기는 증가만 되고 줄어들지 않음

- commit완료된 세그먼트는 새로운 사용자에게 할당되어 overwrite

- 새로운 사용자의 세그먼트 확보시 모두 commit이 완료되지 않았다면 신규 undo segment 할당 (용량에 한해서, autoextend)

- Undo Tablespace의 크기가 비정상적으로 클경우 작은크기의 Undo Tablespace를 신규로 생성후 기존 Undo Tablespace를 삭제 

4) Undo Tablespace 관리 >> 클릭

5) 각 세션별로 사용중인 undo segment 확인

- v$session, v$transaction, v$rollname >> 딕셔너리들을 활용(조인)하여 조회

- 오라클 관리실무 p.212 참고

6) Undo 관련 주요 parameter

① UNDO_RETENTION (단위 : 초)

- commit 수행 후에도 해당 undo segment 내의 데이터를 다른 서버 프로세스가 덮어쓰지 못하도록 막아주는 시간

- A 사용자가 데이터 조회중 B사용자가 DML작업 후 commit을 한 후에 C사용자가 B의 undo segment를 overwrite 했을때 오류발생

ORA-01555. Snapshot too old

- commit이 완료된 Undo Segment라도 파라미터에 설정되어 있는 시간동안은 overwrite 불가 (but, undo segment에 여분이 있을경우에만)

① UNDO_RETENTION_GUARANTEE

- undo_retention 파라미터는 undo segment의 양이 부족하면 다른 사용자에 의해 재사용되어 버림

- undo_retention_guarantee는 설정된 시간동안 재활용을 하지않고 데이터를 지켜줌

- 기본값 noguarantee >> 설정한 시간전에 commit을 수행해도 undo segment를 특정시간동안은 사용할수 없기때문에 bad.

7) Undo Tablespace 삭제

- 사용중인 undo segment를 포함하고 있는 Undo Tablespace는 삭제 불가

- 사용중이지 않는데 삭제가 되지 않을 경우 _offline_rollback_segments라는 Hidden parameter를 사용하여 강제로 offline시킨후 삭제

- SQL > drop tablespace 테이블스페이스명;

(5) Temporary Tablesapce

- 임시자료를 저장하는 Tablespace

- PGA 공간이 부족하면 이 공간을 이용하여 작업 (sort, import, export 등)

- 여러개 생성가능 -> 각 사용자 별로 할당해 주는것이 좋음

- 큰 정렬작업시 Temporary T/S를 크게 만들어 작업하면 성능 good.

* Temporary Tablespace Group

- Temporary Tablespace를 여러개 만들어서 그룹으로 묶어줌

- 특정 스키마에 여러 사용자가 접속하여 작업을 한다고 했을때 (ex. 정렬작업) 

   여러명의 사용자가 각각 그룹안에있는 여러개의 temporary tablespace를 별도로 사용해서 성능향상이 됨.

[실습] 일반 Tablespace

1. 일반 Tablespace 생성 및 조회

① 생성

SQL > create tablespace 테이블스페이스명 '경로 및 파일명' size 크기;

② 조회

SQL > select tablespace_name, status, contents, extent_management, segment_space_management

from dba_tablespaces;

SQL > select tablespace_name, bytes/1024/1024 "MB", file_name

from dba_data_files;

2. 각 Data File의 실제 사용량 확인 --실제로 얼마나 사용하고 있는지?

SQL > select distinct d.file_id "file#",

d.tablespace_name "ts_name",

d.bytes/1024/1024 "MB",

d.bytes/8192 "total_blocks",

sum(e.blocks) "used_block",

to_char( nvl(round(sum(e.blocks)/(d.bytes/8192), 4), 0)*100, '09.99') || ' %' "pct_used"

from dba_extents e, dba_data_files d

where d.file_id = e.file_id(+)

group by d.file_id, d.tablespace_name, d.bytes

order by 1,2;

[보기쉽게 컬럼 등 사이즈 조절]

set line 200;

col file# for 999;

col ts_name for a10;

col total_blocks for 9999999;

col used_blocks for 9999999;

col pct_used for a10;

3. Tablespace 용량관리

SQL > select tablespace_name, bytes/1024/1024 "MB", file_name

from dba_data_files;

[보기쉽게 컬럼 등 사이즈 조절]

col tablespace_name for a10

col file_name for a50

<용량 부족으로 에러 발생시 조치방법>

① 수동으로 Tablespace에 Data File 추가

SQL > alter tablespace 테이블스페이스명 add datafile '경로 및 파일명' size 크기;

② Data File 크기를 수동으로 증가

SQL > alter database datafile '경로 및 파일명' resize 크기;

③ Data File 크기를 자동으로 증가

SQL > alter database datafile '경로 및 파일명' autoextend on;

- oracle이 32비트용 일경우 최대 파일 1개의 크기는 16GB까지 자동 증가 가능

- oracle이 64비트용 일경우 최대 크기가 32GB까지 자동 증가 가능

# 각 Data File 들의 autoextend 유무 확인

SQL > select tablespace_name, bytes/1024/1024 "MB", file_name, autoextensible "Auto", online_status

 from dba_data_files;

 

4. Tablespace Offline

- Tablespace를 Offline 한다는 것은 사용자가 더이상 접근하지 못한다는 의미

- 해당 Tablespace만 shutdown 시키는 것과 동일함

- 특정 Tablespace의 데이터파일의 위치 이동, 장애발생시 복구를 해야할때 사용

- data file의 offline 여부 확인 : v$datafile

- Offline 방법 3가지 

① Normal mode : Tablespace에 아무런 문제가 없을 때 정상적으로 수행하는 방법

② Temporary mode : Tablespace의 Data File이 하나라도 이상이 생길때 수행하는 방법 --normal mode 수행불가

③Immediate mode : archive log mode 일경우에만 사용

- offline 한 후 online 할때 반드시 체크포인트를 발생시켜서 data file간의 동기화 작업 필요 

5. Data File 이동시키는 작업

- 특정 디스크에 있는 Data File 들의 용량이 점점 증가하여 더 큰 용량의 디스크를 설치한 후 몇개의 Data File을 신규 디스크로 이동할때

- 장애 발생시 복구할때

- ★ Data File 사용중일때 이동이나 복사 하면 안됨!

- Offline과 shutdown 후 작업

① Offline 되는 tablespace의 data file 이동하기

- 해당 tablespace offline

- Data file을 대상위치로 복사

- 컨트롤 파일내의 해당 data file 위치 변경

- 해당 tablespace online

② Offline 안되는  tablespace의 data file 이동하기

- DB 종료

- 마운트 상태로 시작 --> 모든 data file 및 redo log 파일도 이동 가능

- Data file 복사

- 컨트롤 파일의 내용 변경

- DB open

[OFFLINE 안되는 3가지 Tablespace] -- 반드시 shutdown하고 작업

① SYSTEM tablspace

② UNDO tablspace

③ DEFAULT TEMPORARY tablspace

③ Redo log file 이동하기

- Redo log file은 offline이 안되기 때문에 접근 불가하게 하기위해서는 DB를 mount 상태로 두고 작업해야함

6. Tablespace 삭제

- drop tablespace 테이블스페이스명;

- drop tablespace 테이블스테이스명 including contents and datafiles; --내용이 있어도 지우는 옵션