Tablespace & Datafiles

Tablespace & Datafiles

- 오라클은 데이터를 저장하고 작업할 때 메모리에 논리적으로는 Tablespace라는 공간을 만들어서 작업하며, 물리적으로는 Data File을 만들어서 저장

- 사용자가 SQL을 수행하면 해당데이터는 반드시 메모리에 있는 Tablespace에 존재해야 한다.

- 메모리에 존재하지 않을 경우 Server Process는 데이터가 저장되어있는 Data File에 가서 해당 데이터가 있는 Block을 찾아서

   Tablespace로 가져온 후  사용자가 원하는 데이터를 전달

- Oracle에서는 Database Buffer cache에 Tablespace를 생성하며 사용하는 메모리 공간이 클수록 일반적으로 수행속도가 빨라짐

 

1. Tablespace의 종류 및 특징

1) SYSTEM Tablespace

- Data Dictionary들이 저장되어있음

- SYS 계정의 소유 Tablespace이지만 SYS 사용자도 이 테이블의 내용을 변경할 수 없음

- System table space 사이즈가 100%이면 어떤 오브젝트도 등록 불가능

- Data Dictionary 테이블 개수 조회

SQL> select count(*) from dictionary;

- SYSTEM Tablespace가 풀 차면 Auto extend가 on 되어 있다면 자동으로 공간이 늘어남

 

□  Data Dictionary

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

 

□ Data Dictionary에 있는 정보

1) 데이터베이스의 논리적인 구조와 물리적인 구조정보

2) 객체의 정의와 공간 활용 정보

3) 제약조건에 관련된 정보

4) 사용자에 관련된 정보

5) Role, Privilege 등에 관련된 정보

6) 감사 및 보안에 관련된 정보

 

□ Data Dictionary는 Base Table과 Data Dictionary View로 나눔

- Base Table : 데이터베이스를 생성할 때 생성되는 테이블로 dictionary의 원본 데이터가 존재

- Data Dictionary View : dbca로 database 생성 시 자동생성 되지만,

create database로 수동 생성 경우 catalog.sql을 수행해야 생성됨

- Base Table의 내용을 직접 보지 못하도록 Data Dictionary View를 제공해 주는 것

- Data Dictionary View는 다시 Static Dictionary와 Dynamic Dictionary로 나눔

 

□ Static Dictionary

- 수동으로 업데이트를 해야 정보가 갱신

- 인스턴스가 OPEN 되었을 경우 조회가능

- USER_XXX, ALL_XXX, DBA_XXX 뷰들

 

□ Dynamic Performance View

- 실시간으로 변경되는 내용을 볼 수 있는 뷰

- 사용자가 조회할 경우 그 시점의 Control File 이나 메모리에서 데이터를 가져와 보여준다

- v$로 시작되며, Database가 Nomount 상태일 때부터 조회가능

 

2) SYSAUX Tablespace

- 주로 오라클 서버의 성능 튜닝을 위한 데이터들이 저장 되어 있음

- 9i 버전까지는 튜닝관련 Dictionary들이 SYSTEM Tablespace에 존재

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

- 10g에 새로 등장한 자동튜닝 기능들 AWR, ADDM, ASH등이 이곳의 정보를 사용

 

3) 일반 Tablespace

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

- 관리자가 필요에 의해 만드는 Tablespace

 

TableSpace 실습

CREATE TABLESPACE haksa

datafile '/oracle11/oracle/oradata/testdb/haksa01.dbf' size 1M(autoextend on);

* autoextend on : 옵션 추가시 공간이 차면 자동으로 사이즈를 늘려줌

 

 

* 이건 segment로 조회 시 안 나옴 왜냐면 빈껍데기는 데이터용량 할당이 안 되기 때문에

다음 block에서 이유 자세히 배움

create table iphak2 (stduno number)

tablespace haksa; -- scott 계정에서

 

-- 대량의 데이터 insert

 

begin

for i in 1..50000 loop

insert into iphak values(i);

end loop

;

commit;

end;

/

 

select count(*) from iphak;

***************** Datafile이 가득 차서 더 이상 insert가 안될 때 **************************

1) 테이블스페이스에 Datafile 추가 -> 디스크 추가

alter tablespace haksa

add datafile '/oracle11/oracle/oradata/testdb/haksa02.dbf' size 20M;

--> 테이블 스페이스에 datafile을 추가함으로 디스크를 늘려줌

 

2) 테이블스페이스의 기존 Datafile 리사이즈

alter database datafile '/oracle11/oracle/oradata/testdb/haksa01.dbf'

resize 20M;

--> datafile 리사이즈

 

3) Autoextend 설정을 on 으로 변경

alter database datafile '/oracle11/oracle/oradata/testdb/haksa2_01.dbf'

autoextend on;

--SELECT * FROM DBA_DATA_FILES; 조회시 autoextensible yes로 바뀐거 확인

======> 파일 추가시 자동으로 사이즈가 늘어남

 

select file_name, tablespace_name, bytes/1024/1024 MB

from dba_data_files;

Tablespace Offline

- 더 이상 해당 Tablespace에 접근하지 않겠다는 의미로 읽기도 쓰기도 되지 않는 상태

- Tablespace의 위치 이동, 장애 복구 시 주로 사용

- Normal, Temporary, Immediate Mode 존재

1) Normal Mode

- 가장 일반적인 offline 방법

- Tablespace가 정상일 경우만 가능

- alter tablespace tablespace_name offline;

2) Temporary Mode

- offline 시키고자 하는 Tablespace의 Data file에 하나라도 이상이 생기게 될 경우 수행

- alter tablespace tablespace_name offline temporary;

3) Immediate Mode

- Data file에 장애가 나서 데이터를 내려쓰지 못하는 상황에서 offline을 해야 하는 경우

- 반드시 archive log mode일 경우에만 사용

- 나중에 online시 복구하라고 메시지 나옴

- alter tablespace haksa offline immediate;

 

--haksa테이블 스페이스 offline

alter tablespace haksa offline;

 

--scn 정보 확인

select a.file#

,a.ts#

,b.name

,a.status

,a.checkpoint_change#

from v$datafile a

,v$tablespace b

where a.ts# = b.ts#;

 

 

--haksa테이블 스페이스 online

alter tablespace haksa online;

 

 ----> 테이블 스페이스 off,online 작업 후에는 여유 있다면 checkpoint 갱신 --alter system checkpoint;

-- 정상적인 오프라인 후 온라인 시킬 시 안된다면 recover 적용

 

Data File Offline

- Data File 단위로도 offline이 가능

- archive mode 경우

  : alter database datafile '.dbf 경로' offline;

- No archive mode 일 경우에는 offline drop

  : alter database datafile 'dbf 경로' offline drop;

- Data file offline은 online 전에 recover 필요

- Data file은 Tablespace에 여러 개 존재하므로 Data File Offline은 비효율

 

alter database

datafile '/oracle11/oracle/oradata/testdb/haksa01.dbf'

offline; -- no archive mode가 아닐 때 하면 에러남

 

alter database

datafile '/oracle11/oracle/oradata/testdb/haksa01.dbf'

offline drop;

 

--scn 정보 확인

select a.file#

,a.ts#

,b.name

,a.status

,a.checkpoint_change#

from v$datafile a

,v$tablespace b

where a.ts# = b.ts#; 

 

alter tablespace haksa offline temporary;

 

alter tablespace haksa online;

--> dbf 파일 자체를 오프라인했기 때문에 recovery 작업을 해야 함

 

<sysdba> recover tablespace haksa;

 

Data File Rename ( = 파일 위치 옮기는 것 )

- 특정 디스크에 있는 데이터 파일들의 용량이 점점 증가하여 다른 더 큰 용량의 디스크를 설치 한 후 Data file을 이동 시 주로 사용

- Datafile이 사용 중일 때는 절대로 이동시키거나 복사하면 안 된다.

- Tablespace를 Offline 하거나, Shutdown 한 후 작업해야 함

- 일반 Tablespace는 Offline후 작업

- Offline이 불가능한 SYSTEM, UNDO, Default Temp Tablespace는 Shutdown 후 작업 가능

 

◆ 일반 Tablespace Rename 절차

1) 해당 Tablespace Offline

alter tablespace haksa offline; <ORANGE>

 

2) Datafile 물리적 rename(mv보다 cp로 하는 것이 안전)

cp /oracle11/oracle/oradata/testdb/haksa01.dbf /home/oracle/oradata/testdb/haksa01.dbf <XShell>

 

3) Control file 정보 변경

--controlfile 상의 datafile rename

alter tablespace haksa

rename datafile '/oracle11/oracle/oradata/testdb/haksa01.dbf'

to '/home/oracle/oradata/testdb/haksa01.dbf';

 

alter tablespace haksa

rename datafile '/oracle11/oracle/oradata/testdb/haksa02.dbf'

to '/home/oracle/oradata/testdb/haksa02.dbf';

 

4) 해당 Tablespace Online

alter tablespace haksa online;

alter system checkpoint;

 

◆ SYSTEM Tablespace Rename 절차

1) shutdown

2) Startup mount

3) Data file 물리적 rename

cp /oracle11/oracle/oradata/testdb/system01.dbf /home/oracle/oradata/testdb/system01.dbf <XShell>

4) Control file 정보 변경

alter database rename file '/oracle11/oracle/oradata/testdb/system01.dbf' to '/home/oracle/oradata/testdb/system01.dbf';

5) Open

select * from dba_data_files where tablespace_name='SYSTEM';

 

 

◆ 실습 : 리두 로그 파일 이동

--목적

--각 group의 member를 /oracle11 -> /home 영역으로 이동

--각 group에 /home영역에 member 생성

--리두 로그 확인

select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status

from v$logfile a, v$log b

where a.group# = b.group#;

--로그 파일 확인

select * from v$logfile;

 

SQL> shutdown immediate

SQL> startup mount

--cp

cp /oracle11/oracle/oradata/testdb/redo01.log /home/oracle/oradata/testdb/redo01_b.log

cp /oracle11/oracle/oradata/testdb/redo02.log /home/oracle/oradata/testdb/redo02_b.log 

 

cp /oracle11/oracle/oradata/testdb/redo03.log /home/oracle/oradata/testdb/redo03_b.log

--control파일 변경

alter database rename file '/oracle11/oracle/oradata/testdb/redo01.log' to '/home/oracle/oradata/testdb/redo01_b.log';

alter database rename file '/oracle11/oracle/oradata/testdb/redo02.log' to '/home/oracle/oradata/testdb/redo02_b.log'; 

 

alter database rename file '/oracle11/oracle/oradata/testdb/redo03.log' to '/home/oracle/oradata/testdb/redo03_b.log';

-- member 추가

alter database add logfile member

'/home/oracle/oradata/testdb/redo1_2.log' to group 1,

'/home/oracle/oradata/testdb/redo2_2.log' to group 2,

'/home/oracle/oradata/testdb/redo3_2.log' to group 3;

 

--member 확인

select member from v$logfile;

 

◆ Tablespace 삭제

- drop tablespace tablespace_name;

- Tablespace에 Table이 하나라도 있으면 삭제되지 않는다

- drop tablespace tablespace_name including contents and datafiles; 로 삭제 가능

- ASM이 아니라면 물리적으로도 data file 삭제 필요

 

◆ Undo Tablespace

- DML 작업 수행 시 원본 데이터(Undo data)를 저장하는 장소

- Undo Data를 저장하는 실질적 공간을 Undo Segment라 하고 이 Segment를 저장하는 Tablespace가 Undo Tablespace

- 기본적으로 하나의 세션 당 하나의 Undo segment를 할당

- Undo Tablespace에는 Undo Data 외의 다른 데이터는 저장될 수 없다

- 사용자가 관여하거나 수정할 수 없고 Oracle Server Process가 직접관리

- Undo Tablespace는 Instance당 여러 개가 존재할 수 있으나, 사용되는 것은 한번에 1개뿐

- 관리방법으로는 자동 모드와 수동모드가 있으며, Default는 자동모드

- undo_management= auto | manual 로 관리모드 변경 가능하지만 DB 재기동 필요

- Segment의 개수를 수동으로 관리하는 수동모드와는 달리 자동모드에서는 자동적으로 Segment 할당을 관리, 새로운 Segment를 생성

□ Undo Tablespace 사용 목적

1) Transaction Rollback : 최종 commit 하지 않고 Rollback 하고자 할 때 Undo 데이터를 이용하여 과거 이미지 전달

2) Transaction Recovery (instance Recovery시 rollback 단계)

: Instance Crash 발생 후 Redo를 이용해 Roll forward 단계가 완료되면 최종 Commit 되지 않은 변경사항까지

모두 복구할 때 쓰임

3) Read Consistency : 읽기 일관성을 위해 사용

 

 

*** select는 before이미지만 조회하는데 undo segment에 할당된 before 이미지가 사라지게되면 -> select 절은 조회자료가 사라져

 

Rollback

◆ Undo Segment 할당원리

1. 현재 아래와 같이 4개의 Segment 할당되어 4개의 사용자가 사용 중

A

B

C

D

2. 새로운 사용자 E가 DML 수행

3. E의 Server Process가 Undo Segment 할당을 위해 기존 Segment중에 재사용 할 것이 있는지 찾음

▶ 새로운 세션의 DML이 발생할 때마다 신규로 Undo Segment를 할당 받지 않음

▶ 기존 Segment는 Commit이나 Rollback이 되면 재사용 가능한 상태가 되고

즉시 Segment의 내용이 flush 되는 것이 아니고 재사용 되면서 flush

4. A가 그 시점에 Commit 수행

5. E는 A가 쓰고 있던 Segment를 재사용

 

E

B

C

D

 

6. 새로운 사용자 F가 접속해 DML 수행

7. 아무도 Commit이나 Rollback을 하지 않음

E

B

C

D

F

8. F는 새로운 Segment를 할당 받음

9. 계속해서 새로운 Segment를 할당 받다가 Datafile의 허용범위까지 늘어나면

하나의 Segment를 2개 세션 이상의 Undo Data를 함께 기록

10. 그 이후에도 공간이 없으면 해당 트랜잭션은 에러 발생 -> Commit되지 않은 모든 데이터들은 Rollback

 

◆ Undo Segment 특징

- Commit을 수행해도 Undo Segment안에 Undo Data는 지워지지 않고 남아있기 때문에 해당 Data file 사이즈를 줄일 수는 없음

- Commit 수행 시 다른 서버 프로세스가 덮어 쓸 수 있게 해주는 것일 뿐 Undo Segment 안의 자료를 지우는 것은 아님

- Undo Tablespace 크기가 비정상적으로 작을 경우에는 관리자가 다른 큰 undo Tablespace를 신규로 만들고,

Undo Tablespace를 신규 Undo Tablespace로 변경시킨 후 기존 Undo Tablespace를 삭제해 주어야 함

 

◆ Undo 관련 주요 파라미터

1. UNDO_RETENTION(초 단위)

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

- 그러나, Undo segment 여분이 있을 경우에만 적용되며, 만약 모든 Undo Segment가 사용 중일 경우에는 적용되지 않고

Undo Segment가 재사용되어 진다.

- 관련 에러 : Snap shot too old

* Ora-01555 : Snap shot too old

- 사용자 A : 1시간 정도 수행되는 특정 데이터의 집계작업을 수행 중

- 사용자 B : 사용자 A가 집계중인 데이터를 update 한 후 commit할 경우

- 사용자 A : 사용자 B가 변경전의 데이터를 undo Segment에서 찾아서 집계하게 됨

- 만약 B가 Commit 한 후 사용자 C가 사용자 B의 Undo Segment를 덮어 썼다면 사용자 A는 집계 쿼리를

완성할 수 없어 에러 발생

- 이때 발생하는 에러가 Snap shot too old

2. UNDO_RETENTION_GUARANTEE

- Undo_retention으로 설정된 값 만큼 Segment가 재사용 되지 않도록 보장해주는 파라미터

- 즉 Undo Segment가 부족하면 undo retention가 설정되어 있더라도 무시하고 재사용하는데 이를 재사용되지 않도록 보장

- commit을 수행해도 특정 기간 동안 Undo Segment를 재사용하지 않기 때문에 Undo Tablespace가 커짐

- default : NOGUARANTEE

- alter tablespace undotbs1 retention guarantee;

 

Undo 사용 현황 조회하기

1번 터미널

SQL > update emp set sal=5000 where empno=7902;

 

undo 사용량(record, block) 조회

SQL > select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk

from v$session a, v$transaction b

where a.saddr = b.ses_addr;

 

각 세션 별로 사용 중인 Undo Segment 확인하기

SQL > select s.sid, s.serial#, s.username, r.name "UNDO SEG",

s2.bytes/1024/1024 "UNDO SEG SIZE(MB)"

from v$session s, v$transaction t, v$rollname r, dba_segments s2

where s.taddr=t.addr

and t.xidusn=r.usn

and r.name=s2.segment_name;

***delete가 update보다 undo사이즈를 많이 잡는 이유 : update는 행을 지정해서 하지만 delete는 포함 행 모두를 undo segment에 저장

 

-- undo_retention에 의해 expired(undo_retention이 지나서 재사용 가능 공간)된 영역과

-- unexpired(아직 undo_retention이 지나지 않은)된 영역

-- active(실제 사용 중)인 영역

-- free(아직 할당받지 않은) 영역

select tablespace_name,

status,

sum(bytes)

from dba_undo_extents

group by tablespace_name,

status

order by 1;

 

-- 새로운 undo영역 생성 후 할당

create undo tablespace undo01

datafile '/oracle11/oracle/oradata/testdb/undo01.dbf'

size 100M;

select BYTES/1024/1024

from dba_data_files

where tablespace_name like '%UNDO%';

select * from dba_data_files;

alter system set undo_tablespace=undo01;

---> 신규 세션들에 대해서는 undo01에서 작업하고 기존은 그 스페이스에서 계속 작업함

 

drop tablespace UNDOTBS1; -- ACTIVE를 물고있으면 삭제가 안됨! 먼저 ACTIVE인지 확인하고 삭제진행

 

◆ TEMP Tablespace

- Sort Operation(PGA영역) 시에 임시로 사용됨

- 영구 테이블 스페이스로 변경 안 됨

- 성능을 위해 사용자 별로 1개씩 할당 권장

- default temporary tablespace 는 삭제 안 됨 --> 다른 temp tablespace 생성 후 default로 변경 후 삭제

 

--TEMP TableSpace 생성

create temporary tablespace temp2

tempfile '/oracle11/oracle/oradata/testdb/temp02.dbf' size 50M;

select * from dba_data_files; *dbf파일들을 모아 놓은 뷰

select FILE_NAME, BYTES/1204/1024 from dba_temp_files; *temp dbf파일들을 모아 놓은 뷰

select sum(bytes)/1024/1024/1024

from dba_segments

where 1=1

and SEGMENT_NAME like 'TEMP%';

select * from dba_users;

alter database default temporary tablespace temp2;

create temporary tablespace temp3

tempfile '/oracle11/oracle/oradata/testdb/temp03.dbf' size 50M;

 

alter user hr temporary tablespace temp3;