이전 포스트: [Oracle] Redo Log 관리하기
다음 포스트: [Oracle] Oracle 저장 구조
1. Tablespace의 종류 및 특징
1) SYSTEM tablespace
데이터 딕셔너리들이 저장되어 있습니다. 데이터 딕셔너리란 Oracle Server의 모든 정보를 저장하고 있는 아주 중요한 Table이나 View를 말합니다. 모든 메타 정보, 운영 관련 정보들이 들어 있습니다
이 Tablespace는 SYS 계정의 소유이지만 이곳에 들어 있는 정보들이 너무 중요해서 SYS 조차도 변경하지 못하고 조회만 가능합니다.
-- 딕셔너리 수 조회
SQL> select count(*) from dictionary;
COUNT(*)
----------
4624
데이터 딕셔너리는 크게 나누면 Base Table과 Data Dictionary View로 나눌 수 있습니다. Base Table은 데이터베이스를 생성할 때 생성되고, 원본 데이터가 들어 있어 관리자는 이곳에 접근할 수 없습니다. 그래서 이곳의 내용을 조회할 수 있도록 Data Dictionary View를 제공합니다. Data Dictionary View는 DBCA를 사용해 데이터베이스를 생성할 경우 자동으로 생성되지만, Create Database 명령어로 수동으로 생성할 경우 자동으로 생성되지 않습니다. 이때는 catalog.sql 스크립트를 추가로 실행해 주어야 합니다.
딕셔너리에는 아래와 같은 주요 내용의 정보들이 들어 있습니다.
- 데이터베이스의 논리적인 구조와 물리적인 구조 정보
- 객체의 정의와 공간 사용 정보
- 제약조건와 관련된 정보
- 사용자와 관련된 정보
- Role, Privillege 등에 관련된 정보
- 감사 및 보안 등에 관련된 정보
Data Dictionary View는 다시 크게 Static Dictionary와 Dynamic Dictionary로 나눌 수 있습니다. Static Dictionary는 딕셔너리에 저장된 정보들이 자동으로 변경되지 않는다는 뜻입니다.
-- 테스트용 테이블을 생성합니다.
SQL> create table stest (no number);
Table created.
-- 100개 행의 데이터를 입력합니다.
SQL> begin
2 for i in 1..100 loop
3 insert into stest values (i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
-- 테스트 테이블의 행 개수를 조회합니다.
SQL> select count(*) from stest;
COUNT(*)
----------
100
-- USER_TABLES라는 딕셔너리를 사용해 행 개수를 조회해봅니다.
SQL> select table_name, num_rows
2 from user_tables
3 where table_name='STEST';
TABLE_NAME NUM_ROWS
-------------------------------------------------- ----------
STEST
USER_TABLES는 Static Dictionary이기 때문에 자동으로 업데이트 되지 않습니다. 따라서 수동으로 딕셔너리를 업데이트 해야 합니다.
SQL> analyze table stest compute statistics;
Table analyzed.
-- 다시 USER_TABLES 딕셔너리를 사용해 행 개수를 조회해봅니다.
SQL> select table_name, num_rows
2 from user_tables
3 where table_name='STEST';
TABLE_NAME NUM_ROWS
-------------------------------------------------- ----------
STEST 100
여기서 업데이트할 때 analyze 명령어를 사용했습니다. 이것 말고 DBMS_STATS라는 패키지도 많이 사용하는데 이 두 가지 방법은 테이블을 전체 스캔하기 때문에 서버의 성능에 악영향을 주는 경우가 많습니다. 따라서 함부로 사용하면 안됩니다.
딕셔너리는 4개의 카테고리로 분류되어 있습니다. USER_XXX, ALL_XXX, DBA_XXX는 Static Dictionary이고, V$XXX는 Dynamic Dictionary입니다. Static Dictionary는 Instance가 Open 단계부터 조회 가능하고, Dynamic Dictionary는 No Mount 단계부터 조회 가능합니다.
USER_XXX
: 현재 사용자가 생성한 내용만 볼 수 있습니다.
USER_TABLES: 현재 사용자가 생성한 테이블만 볼 수 있는 딕셔너리USER_INDEXES: 현재 사용자가 생성한 인덱스만 볼 수 있는 딕셔너리
ALL_XXX
: 사용자와 관계없이 접근 가능한 모든 내용을 볼 수 있습니다.
ALL_TABLES: 접근 가능한 모든 테이블을 볼 수 있는 딕셔너리ALL_INDEXES: 접근 가능한 모든 인덱스를 볼 수 있는 딕셔너리
DBA_XXX
: 현재 사용자가 DBA 권한을 가지고 있다면 데이터베이스에 생성되어 있는 모든 내용을 볼 수 있습니다.
DBA_TABLES: 데이터베이스에 생성되어 있는 모든 테이블을 볼 수 있습니다.DBA_INDEXES: 데이터베이스에 생성되어 있는 모든 인덱스를 볼 수 있습니다.
V$XXX
: 실시간으로 변경되는 내용을 볼 수 있습니다. 사용자가 조회를 하면 Control File이나 Memory에서 데이터를 가져와서 보여줍니다.
2) SYSAUX tablespace
Oracle Server의 성능 튜닝을 위한 데이터들이 저장되어 있습니다. AWR, ADDM, ASH 등이 이곳의 정보를 사용하게 됩니다.
3) 일반 tablespace
가장 일반적으로 많이 사용하는 tablespace로 관리자의 필요에 의해서 만드는 tablespace입니다.
Tablespace 생성 및 조회
SQL> create tablespace khyup
2 datafile '/ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf' size 1M;
Tablespace created.
SQL> select tablespace_name, status, contents, extent_management, segment_space_management
2 from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS EXTENT_MAN SEGMEN
------------------------------ -------- --------------------- ---------- ------
SYSTEM ONLINE PERMANENT LOCAL MANUAL
SYSAUX ONLINE PERMANENT LOCAL AUTO
UNDOTBS1 ONLINE UNDO LOCAL MANUAL
TEMP ONLINE TEMPORARY LOCAL MANUAL
USERS ONLINE PERMANENT LOCAL AUTO
KHYUP ONLINE PERMANENT LOCAL AUTO
6 rows selected.
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
TABLESPACE_NAME MB FILE_NAME
------------------------------ ---- ------------------------------------------------------------
SYSTEM 700 /ORA19/app/oracle/oradata/ORACLE19/system01.dbf
SYSAUX 550 /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
UNDOTBS1 200 /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
USERS 5 /ORA19/app/oracle/oradata/ORACLE19/users01.dbf
KHYUP 1 /ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf
각 Data File의 실제 사용량 확인 방법
SQL> !vi datafile.sql
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;
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_blocks,
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;
:wq!
SQL> @datafile
FILE# TS_NAME MB TOTAL_BLOCKS USED_BLOCKS PCT_USED
----- ---------- ---- ------------ ----------- ----------
1 SYSTEM 700 89600 55704 62.17
2 SYSAUX 550 70400 29136 41.39
3 UNDOTBS1 200 25600 1920 07.50
4 USERS 5 640 00.00
5 KHYUP 1 128 00.00
Tablespace 용량 관리하기
SQL> col tablespace_name for a10;
SQL> col file_name for a50;
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;
TABLESPACE_NAME MB FILE_NAME
------------------------------ ---- ------------------------------------------------------------
SYSTEM 700 /ORA19/app/oracle/oradata/ORACLE19/system01.dbf
SYSAUX 550 /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
UNDOTBS1 200 /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
USERS 5 /ORA19/app/oracle/oradata/ORACLE19/users01.dbf
KHYUP 1 /ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf
-- khyup 테이블스페이스에 iphak Table을 만들고 일부러 가득 차게 해서 장애를 유발해보겠습니다.
SQL> create table test (studno number) tablespace khyup;
Table created.
SQL> begin
2 for i in 1..50000 loop
3 insert into scott.iphak values (i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
-- 다시 신규 데이터를 추가하려 하면 아래와 같은 에러가 발생합니다.
SQL> /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.TEST by 8 in tablespace KHYUP
ORA-06512: at line 3
이런 경우에는 Data File의 용량을 늘려주거나 Data File을 하나 더 추가하는 2가지 방법이 있습니다.
-- Data File을 추가하는 방법
SQL> alter tablespace khyup add datafile
2 '/ORA19/app/oracle/oradata/ORACLE19/khyup02.dbf' size 20M;
Tablespace altered.
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
TABLESPACE_NAME MB FILE_NAME
------------------------------ ---- ------------------------------------------------------------
SYSTEM 700 /ORA19/app/oracle/oradata/ORACLE19/system01.dbf
SYSAUX 550 /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
UNDOTBS1 200 /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
USERS 5 /ORA19/app/oracle/oradata/ORACLE19/users01.dbf
KHYUP 1 /ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf
KHYUP 20 /ORA19/app/oracle/oradata/ORACLE19/khyup02.dbf
6 rows selected.
-- Data File의 용량을 늘려주는 방법
SQL> alter database datafile
2 '/ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf' resize 20M;
Database altered.
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
TABLESPACE_NAME MB FILE_NAME
------------------------------ ---- ------------------------------------------------------------
SYSTEM 700 /ORA19/app/oracle/oradata/ORACLE19/system01.dbf
SYSAUX 550 /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
UNDOTBS1 200 /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
USERS 5 /ORA19/app/oracle/oradata/ORACLE19/users01.dbf
KHYUP 20 /ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf
KHYUP 20 /ORA19/app/oracle/oradata/ORACLE19/khyup02.dbf
6 rows selected.
한편, data file의 크기가 자동으로 증가(autoextend)하도록 설정할 수도 있습니다.
-- Data File의 크기 자동 증가 설정
SQL> alter database datafile '/app/oracle/oradata/testdb/khyup01.dbf' autoextend on;
Database altered.
SQL> select tablespace_name, bytes/1024/1024 MB, file_name, autoextensible "Auto", online_status
2 from dba_data_files;
TABLESPACE_NAME MB FILE_NAME aut ONLINE_
-------------------- ---- -------------------------------------------------- --- -------
SYSTEM 700 /ORA19/app/oracle/oradata/ORACLE19/system01.dbf YES SYSTEM
SYSAUX 550 /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf YES ONLINE
UNDOTBS1 200 /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf YES ONLINE
USERS 5 /ORA19/app/oracle/oradata/ORACLE19/users01.dbf YES ONLINE
KHYUP 20 /ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf YES ONLINE
KHYUP 20 /ORA19/app/oracle/oradata/ORACLE19/khyup02.dbf NO ONLINE
6 rows selected.
-- autoextend 되는지 테스트
SQL> begin
2 for i in 1..500000 loop
3 insert into scott.iphak values (i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
-- 이젠 용량을 초과해서 데이터를 입력해도 에러가 발생하지 않습니다.
SQL> /
PL/SQL procedure successfully completed.
SQL> /
PL/SQL procedure successfully completed.
-- 용량을 확인해보면 늘어난 것을 확인할 수 있습니다.
SQL> select tablespace_name, bytes/1024/1024 MB, file_name, autoextensible "Auto", online_status
2 from dba_data_files;
TABLESPACE_NAME MB FILE_NAME aut ONLINE_
-------------------- ---- -------------------------------------------------- --- -------
SYSTEM 700 /ORA19/app/oracle/oradata/ORACLE19/system01.dbf YES SYSTEM
SYSAUX 550 /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf YES ONLINE
UNDOTBS1 455 /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf YES ONLINE
USERS 5 /ORA19/app/oracle/oradata/ORACLE19/users01.dbf YES ONLINE
KHYUP 66 /ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf YES ONLINE
KHYUP 20 /ORA19/app/oracle/oradata/ORACLE19/khyup02.dbf NO ONLINE
6 rows selected.
Tablespace Offline
Tablespace가 Offline이라는 뜻은 사용자가 더 이상 해당 Tablespace에 접근하지 못한다는 의미입니다. 즉, 해당 Tablespace만 Shutdown 시키는 것과 같습니다. 이 기능은 특정 Tablespace의 Data File의 위치를 이동한다든지, 특정 Tablespace에 장애가 나서 복구를 해야 할 때 유용하게 사용합니다.
Tablespace를 Offline 시키는 경우는 3가지가 있습니다.
Normal Mode는 Tablespace에 아무런 문제가 없을 때 수행하는 방법입니다.
-- 가장 일반적인 방법인 Normal Mode
SQL> alter tablespace example offline;
Tablespace altered.
Data File에 문제가 발생했을 땐(예를 들어, 하나의 tablespace에 2개의 data file이 존재하고, 그 중 하나의 파일만 offline일 경우) Normal Mode로 Offline 시킬 수 없습니다. 이럴 때 쓰는 것이 2번째 방법인 offline temporary입니다.
3번째 방법은 Immediate Mode입니다. 반드시 Archive Log Mode일 경우에만 사용 가능합니다. 이 옵션은 Data File에 장애가 나서 데이터를 내려쓰지 못하는 상황에 Tablespace를 Offline 시켜야 할 때 사용합니다.
특정 Data File만 Offline하는 방법도 있습니다.
-- DB가 archive log mode일 경우
SQL> alter database datafile '/app/oracle/oradata/testdb/khyup01.dbf' offline;
-- DB가 no archive log mode일 경우
SQL> alter database datafile '/app/oracle/oradata/testdb/khyup01.dbf' offline drop;
Database altered.
어떤 Data File이 Offline인지는 아래의 쿼리를 통해 확인 가능합니다.
SQL> select tablespace_name, bytes/1024/1024 MB, file_name, autoextensible, online_status
2 from dba_data_files;
TABLESPACE_NAME MB FILE_NAME AUT ONLINE_
-------------------- ---- -------------------------------------------------- --- -------
SYSTEM 700 /ORA19/app/oracle/oradata/ORACLE19/system01.dbf YES SYSTEM
SYSAUX 550 /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf YES ONLINE
UNDOTBS1 455 /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf YES ONLINE
USERS 5 /ORA19/app/oracle/oradata/ORACLE19/users01.dbf YES ONLINE
KHYUP 66 /ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf YES ONLINE
KHYUP /ORA19/app/oracle/oradata/ORACLE19/khyup02.dbf RECOVER
EXAMPLE /ORA19/app/oracle/oradata/ORACLE19/example01.dbf OFFLINE
7 rows selected.
특정 Tablespace를 offline한 후 다시 online으로 한다면 반드시 checkpoint를 발생시켜서 data file 간의 동기화 작업을 진행해주어야 합니다.
SQL> alter tablespace example offline;
Tablespace altered.
-- example tablespace가 offline 되면서 관련 data file들이 offline 되었습니다.
SQL> select a.file#, a.ts#, b.name, a.status, a.checkpoint_change#
2 from v$datafile a, v$tablespace b
3 where a.ts#=b.ts#;
FILE# TS# NAME STATUS CHECKPOINT_CHANGE#
----- ---------- ----------------------------------- -------- ------------------
1 0 SYSTEM SYSTEM 607980
2 1 SYSAUX ONLINE 607980
3 2 UNDOTBS1 ONLINE 607980
4 4 USERS ONLINE 607980
5 5 KHYUP ONLINE 607980
6 5 KHYUP RECOVER 607980
7 6 EXAMPLE OFFLINE 608885
7 rows selected.
SQL> alter tablespace example online;
Tablespace altered.
SQL> select a.file#, a.ts#, b.name, a.status, a.checkpoint_change#
2 from v$datafile a, v$tablespace b
3 where a.ts#=b.ts#;
FILE# TS# NAME STATUS CHECKPOINT_CHANGE#
----- ---------- ----------------------------------- -------- ------------------
1 0 SYSTEM SYSTEM 607980
2 1 SYSAUX ONLINE 607980
3 2 UNDOTBS1 ONLINE 607980
4 4 USERS ONLINE 607980
5 5 KHYUP ONLINE 607980
6 5 KHYUP RECOVER 607980
7 6 EXAMPLE ONLINE 609101
7 rows selected.
SQL> alter system checkpoint;
System altered.
SQL> select a.file#, a.ts#, b.name, a.status, a.checkpoint_change#
2 from v$datafile a, v$tablespace b
3 where a.ts#=b.ts#;
FILE# TS# NAME STATUS CHECKPOINT_CHANGE#
----- ---------- ----------------------------------- -------- ------------------
1 0 SYSTEM SYSTEM 609150
2 1 SYSAUX ONLINE 609150
3 2 UNDOTBS1 ONLINE 609150
4 4 USERS ONLINE 609150
5 5 KHYUP ONLINE 609150
6 5 KHYUP RECOVER 607980
7 6 EXAMPLE ONLINE 609150
7 rows selected.
khyup tablespace의 경우 2개의 data file이 존재하는데 하나는 online, 다른 하나는 recover로 되어 있어 tablespace를 normal mode로 offline할 경우 에러가 발생합니다. 이럴 땐 temporary mode를 사용합니다.
SQL> alter tablespace khyup offline temporary;
Tablespace altered.
SQL> recover tablespace khyup;
Media recovery complete.
SQL> alter tablespace khyup online;
Tablespace altered.
SQL> select a.file#, a.ts#, b.name, a.status, a.checkpoint_change#
2 from v$datafile a, v$tablespace b
3 where a.ts#=b.ts#;
FILE# TS# NAME STATUS CHECKPOINT_CHANGE#
----- ---------- ----------------------------------- -------- ------------------
1 0 SYSTEM SYSTEM 609150
2 1 SYSAUX ONLINE 609150
3 2 UNDOTBS1 ONLINE 609150
4 4 USERS ONLINE 609150
5 5 KHYUP ONLINE 609201
6 5 KHYUP ONLINE 609201
7 6 EXAMPLE ONLINE 609150
7 rows selected.
SQL> alter system checkpoint;
System altered.
SQL> select a.file#, a.ts#, b.name, a.status, a.checkpoint_change#
2 from v$datafile a, v$tablespace b
3 where a.ts#=b.ts#;
FILE# TS# NAME STATUS CHECKPOINT_CHANGE#
----- ---------- ----------------------------------- -------- ------------------
1 0 SYSTEM SYSTEM 609235
2 1 SYSAUX ONLINE 609235
3 2 UNDOTBS1 ONLINE 609235
4 4 USERS ONLINE 609235
5 5 KHYUP ONLINE 609235
6 5 KHYUP ONLINE 609235
7 6 EXAMPLE ONLINE 609235
7 rows selected.
Data File 이동 작업
특정 디스크에 있는 Data File들의 용량이 증가해 더 큰 용량의 디스크로 이동시키는 작업이 필요해질 수 있습니다. 이때 Data File이 사용 중일 때 이동이나 복사를 하게 되면 그 파일은 사용 못하게 되며 심각할 경우 원본 파일에도 장애가 생길 수 있습니다. 그래서 반드시 작업하려는 Data File을 Offline 시켜주는 작업이 필요합니다.
Offline되는 Tablespace의 Data File 이동
작업 순서는 아래와 같습니다.
1. data file이 속한 tablespace를 offline
2. data file을 대상 위치로 복사
3. control file내 해당 data file 위치 변경
4. 해당 tablespace online
-- 1. data file이 속한 tablespace를 offline
SQL> alter tablespace khyup offline;
Tablespace altered.
-- 2. data file을 대상 위치로 복사
SQL> !mkdir /ORA19/app/oracle/disk1 /ORA19/app/oracle/disk2
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf /ORA19/app/oracle/disk1/
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/khyup02.dbf /ORA19/app/oracle/disk2/
SQL> select name from v$datafile;
NAME
------------------------------------------------------------
/ORA19/app/oracle/oradata/ORACLE19/system01.dbf
/ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
/ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
/ORA19/app/oracle/oradata/ORACLE19/users01.dbf
/ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf
/ORA19/app/oracle/oradata/ORACLE19/khyup02.dbf
/ORA19/app/oracle/oradata/ORACLE19/example01.dbf
7 rows selected.
-- 3. control file내 해당 data file 위치 변경
SQL> alter tablespace khyup rename
2 datafile '/app/oracle/oradata/testdb/khyup01.dbf'
3 to '/app/oracle/disk1/khyup01.dbf';
Tablespace altered.
SQL> alter tablespace khyup rename
2 datafile '/app/oracle/oradata/testdb/khyup02.dbf'
3 to '/app/oracle/disk2/khyup02.dbf';
Tablespace altered.
SQL> select name from v$datafile;
NAME
------------------------------------------------------------
/ORA19/app/oracle/oradata/ORACLE19/system01.dbf
/ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
/ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
/ORA19/app/oracle/oradata/ORACLE19/users01.dbf
/ORA19/app/oracle/disk1/khyup01.dbf
/ORA19/app/oracle/disk2/khyup02.dbf
/ORA19/app/oracle/oradata/ORACLE19/example01.dbf
7 rows selected.
-- 4. 해당 tablespace online
SQL> alter tablespace khyup online;
Tablespace altered.
SQL> alter system checkpoint;
System altered.
SQL> select a.file#, a.ts#, b.name, a.status, a.checkpoint_change#
2 from v$datafile a, v$tablespace b
3 where a.ts#=b.ts#;
FILE# TS# NAME STATUS CHECKPOINT_CHANGE#
----- ---------- --------------------------------------------- -------- ------------------
1 0 SYSTEM SYSTEM 609957
2 1 SYSAUX ONLINE 609957
3 2 UNDOTBS1 ONLINE 609957
4 4 USERS ONLINE 609957
5 5 KHYUP ONLINE 609957
6 5 KHYUP ONLINE 609957
7 6 EXAMPLE ONLINE 609957
7 rows selected.
offline 되지 않는 tablespace의 data file 이동
offline이 되지 않는 tablespace가 있습니다. system tablespace, undo tablespace, default temporary tablespace 입니다. 언급한 3가지 tablespace는 offline이 안되기 때문에 DB를 종료하고 작업을 진행해야 합니다.
그렇다고 이 방법이 위의 3가지 tablespace에만 국한되는 방법은 아닙니다. DB를 종료하고 mount 단계까지 startup을 하게 되면 data file, redo log file까지 이 방법을 이용해 이동이 가능합니다.
작업 순서는 아래와 같습니다.
1. DB를 종료합니다.
2. mount 상태로 시작합니다.
3. data file을 복사합니다.
4. control file의 내용을 변경합니다.
5. DB를 open합니다.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1660940992 bytes
Fixed Size 8897216 bytes
Variable Size 956301312 bytes
Database Buffers 687865856 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> !mkdir /ORA19/app/oracle/disk3
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/system01.dbf /ORA19/app/oracle/disk3/
SQL> select name from v$datafile;
NAME
--------------------------------------------------
/ORA19/app/oracle/oradata/ORACLE19/system01.dbf
/ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
/ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
/ORA19/app/oracle/oradata/ORACLE19/users01.dbf
/ORA19/app/oracle/disk1/khyup01.dbf
/ORA19/app/oracle/disk2/khyup02.dbf
/ORA19/app/oracle/oradata/ORACLE19/example01.dbf
7 rows selected.
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/system01.dbf'
3 to '/ORA19/app/oracle/disk3/system01.dbf';
Database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------
/ORA19/app/oracle/disk3/system01.dbf
/ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
/ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
/ORA19/app/oracle/oradata/ORACLE19/users01.dbf
/ORA19/app/oracle/disk1/khyup01.dbf
/ORA19/app/oracle/disk2/khyup02.dbf
/ORA19/app/oracle/oradata/ORACLE19/example01.dbf
7 rows selected.
SQL> alter database open;
Database altered.
Redo Log File 이동
redo log file 역시 mount 단계로 startup을 한 뒤 파일을 이동 시킬 수 있습니다.
SQL> col member for a50;
SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
2 from v$logfile a, v$log b
3 where a.group# = b.group#
4 order by 1, 2;
GROUP# MEMBER MB ARC STATUS
------ -------------------------------------------------- ---- --- --------
1 /ORA19/app/oracle/oradata/ORACLE19/redo01.log 200 NO CURRENT
2 /ORA19/app/oracle/oradata/ORACLE19/redo02.log 200 YES INACTIVE
3 /ORA19/app/oracle/oradata/ORACLE19/redo03.log 200 YES INACTIVE
SQL> select status from v$instance;
STATUS
--------
OPEN
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1660940992 bytes
Fixed Size 8897216 bytes
Variable Size 956301312 bytes
Database Buffers 687865856 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> !mkdir /ORA19/app/oracle/disk4
SQL> !mkdir /ORA19/app/oracle/disk5
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------
/ORA19/app/oracle/oradata/ORACLE19/redo01.log
/ORA19/app/oracle/oradata/ORACLE19/redo02.log
/ORA19/app/oracle/oradata/ORACLE19/redo03.log
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/redo01.log /ORA19/app/oracle/disk4/
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/redo02.log /ORA19/app/oracle/disk4/
SQL> !cp //ORA19/app/oracle/oradata/ORACLE19/redo03.log /ORA19/app/oracle/disk4/
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo01.log'
3 to '/ORA19/app/oracle/disk4/redo01.log';
Database altered.
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo02.log'
3 to '/ORA19/app/oracle/disk4/redo02.log';
Database altered.
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo03.log'
3 to '/ORA19/app/oracle/disk4/redo03.log';
Database altered.
SQL> select member from v$logfile;
SQL> alter database add logfile member
2 '/app/oracle/disk5/redo01_b.log' to group 1,
3 '/app/oracle/disk5/redo02_b.log' to group 2,
4 '/app/oracle/disk5/redo03_b.log' to group 3;
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------
/ORA19/app/oracle/disk4/redo01.log
/ORA19/app/oracle/disk4/redo02.log
/ORA19/app/oracle/disk4/redo03.log
SQL> alter database open;
Database altered.
tablespace 삭제
만약 tablespace에 table이 하나라도 있다면 drop tablespace 명령으로 지워지지 않습니다.
이때는 including contents and datafiles를 추가해주어야 합니다.
-- table이 하나라도 있으면 지워지지 않습니다.
SQL> drop tablespace khyup;
drop tablespace khyup
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
SQL> drop tablespace khyup including contents and datafiles;
Tablespace dropped.
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
TABLESPACE_NAME MB FILE_NAME
-------------------- ---- --------------------------------------------------
SYSTEM 700 /ORA19/app/oracle/disk3/system01.dbf
SYSAUX 550 /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
UNDOTBS1 455 /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
USERS 5 /ORA19/app/oracle/oradata/ORACLE19/users01.dbf
EXAMPLE 5 /ORA19/app/oracle/oradata/ORACLE19/example01.dbf
연습문제: data file, redo log file, control file을 아래처럼 이동시키세요
/app/oracle/disk1/ -> control01.ctl, redo01_a.log, redo02_a.log, redo03_a.log /app/oracle/disk2/ -> control02.ctl, redo01_b.log, redo02_b.log, redo03_b.log /app/oracle/disk3/ -> control03.ctl, undo01.dbf /app/oracle/disk4/ -> system01.dbf, sysaux01.dbf /app/oracle/disk5/ -> user01.dbf, example01.dbf
4) Undo Tablespace
사용자가 DML을 수행할 때 Undo data가 발생합니다. Undo data는 변경 전 데이터입니다. 예를 들어, 홍길동 -> 개구리로 변경한다면 홍길동이 Undo data입니다.
undo data를 저장하는 segment를 undo segment라고 합니다. 그리고 undo segment를 저장하고 있는 특별한 tablespace가 undo tablespace입니다. undo tablespace는 관리자가 생성 및 관리할 수 있고, undo data 외 다른 데이터는 저장될 수 없으며, undo data는 사용자가 관여할 수 없고, oracle server process가 직접 관리합니다.
undo tablespace 특징
oracle server process는 undo tablespace에 undo segment를 생성합니다. 기본적으로 사용자 별로 undo segment를 할당해서 undo data를 관리합니다. 사용자는 관여할 수 없습니다.
undo tablespace는 instance에 여러 개 존재할 수 있지만 한 번에 사용되는 것은 1개 입니다.
(특별한 경우에는 동시에 여러 개가 사용되기도 합니다)
관리 방법은 자동/수동 Mode가 있는데 9i 버전부터는 AUM(Automatic Undo Management)를 권장하고 있습니다. 초기화 파라미터 파일(pfile, spfile)에 UNDO_MANAGEMENT=auto로 설정하면 자동, manual로 설정하면 수동입니다. 파라미터의 값을 변경한 경우에는 instance를 재기동 해주어야 합니다.
undo tablespace의 사용 목적
transaction rollback
: 사용자가 rollback이라는 명령어를 수행할 경우 이곳에 저장된 undo data를 사용해서 rollback을 수행합니다.
Consistent Read(읽기 일관성)
: CR 작업을 통해 트랜잭션이 끝나지 않은 데이터는 변경 전 데이터를 보여줍니다.
CR 작업의 자세한 설명은 아래에 있습니다.
Transaction Recovery(Instance Recovery)
L 운영 중이던 DB 서버가 비정상적으로 종료 되었을 때 Roll Forward와 Roll Backward 작업을 수행해서 Dirty Database -> Clean Database로 만드는 과정에서 Undo data가 사용됩니다.
CR 작업(Consistent Read)
개구리 -> 홍길동으로 변경하는 update문을 수행한다고 합시다.
1. data file에서 해당하는 block을 DB Buffer cache로 가지고 와서 해당 block을 lock 합니다.
(해당 사용자가 commit이나 rollback을 수행하기 전까지 이 block의 변경된 데이터를 다른 사용자가 볼 수 없습니다)
2. Redo Log Buffer에 변경 전/후 내용을 기록합니다.
3. Undo Segment에 변경 전 내용을 기록합니다.
4. DB Buffer Cache에 데이터를 변경합니다.
만약 commit/rollback 전에 다른 사용자가 해당 내용을 조회한다면 어떻게 될까요? 이 경우에는 다른 사용자의 Server Process가 undo segment에 있는 데이터를 DB Buffer Cache로 복사해 와서 제공합니다.
사용자 B가 사용자 A가 변경한 내용을 트랜잭션 종료 전에 조회하려고 하면 undo segment에서 가져온 개구리가 보이게 됩니다.
undo segment 할당 원리
Undo Data File의 크기는 증가만 되고 줄어들진 않습니다.
Undo Tablespace 안에 Undo Segment 4개가 있다고 해봅시다. 각 segment는 저마다의 Server Process(A, B, C, D)에 할당되어 있습니다.
이 상황에서 신규 사용자 E가 DML을 수행할 경우 E의 Server Process는 Undo Segment를 할당 받게 되는데, 무조건 신규 Undo Segment를 할당 받는 것은 아닙니다. 만약 A가 그 시점에 commit을 수행해 트랜잭션이 완료되었다면 A가 할당 받은 Undo Segment는 E에게 할당됩니다.
반대로 어떤 Server Process도 트랜잭션을 완료하지 않은 상황이라면 새로운 Segment를 하나 더 생성합니다.
기존 Undo Segment를 할당받게 된다면 안에 Undo Data는 그대로 남아있는 상태로 새로운 Server Process가 덮어쓸 수 있게 해주기 때문에 Data File 크기를 줄일 순 없습니다. 만약 사용하지도 않는 Undo Tablespace의 크기가 비정상적으로 커지게 되면 관리자는 다른 작은 크기의 Undo Tablespace를 새로 만들고 신규 Undo Tablespace로 지정한 후 기존 Undo Tablespace를 삭제합니다.
Undo Tablespace 관리 실습
-- 현재 Undo Tablespace 상태 파악하기
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
-- 신규 Undo Tablespace 생성하기
SQL> create undo tablespace undo01
2 datafile '/ORA19/app/oracle/oradata/ORACLE19/undo01.dbf' size 10M
3 autoextend on;
Tablespace created.
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
TABLESPACE_NAME MB FILE_NAME
-------------------- ---- --------------------------------------------------
SYSTEM 700 /ORA19/app/oracle/disk3/system01.dbf
SYSAUX 550 /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
UNDOTBS1 455 /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
USERS 5 /ORA19/app/oracle/oradata/ORACLE19/users01.dbf
UNDO01 10 /ORA19/app/oracle/oradata/ORACLE19/undo01.dbf
EXAMPLE 5 /ORA19/app/oracle/oradata/ORACLE19/example01.dbf
6 rows selected.
-- Undo Tablespace 변경하기
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_tablespace=undo01;
System altered.
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO01
각 세션 별로 사용 중인 Undo Segment 확인하기
SQL> 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;
Undo 관련 주요 Parameter
UNDO_RETENTION
: commit 수행 후에도 해당 undo segment 내의 데이터를 다른 서버 프로세스가 덮어 쓰지 못하도록 막아주는 시간입니다. 예를 들어, A가 1시간 걸리는 select를 수행하고 있다고 해봅시다. 그런데 B가 특정 데이터를 update하고 commit하게 되면 A는 Undo Segment에서 변경 전 데이터를 가져와야 합니다. 그런데 C가 나타나서 B가 사용했던 Undo Segment를 할당 받아 덮어쓰기 시작한다면 A는 데이터를 찾지 못하게 되는 현상이 발생합니다. 이때 발생하는 에러가 ORA-01555, Snapshot too old입니다. 그래서 등장한 것이 UNDO_RETENTION 파라미터로, 시간을 설정하게 되면 설정한 시간 동안은 트랜잭션이 완료된 Segment에 덮어쓰지 않습니다. 하지만 모든 Undo Segment가 사용 중일 경우에는 동작하지 않습니다.
UNDO_RETENTION_GUARANTEE
: UNDO_RETENTION 파라미터는 Undo Segment 양이 부족하면 동작하지 않습니다. 이때 UNDO_RETENTION_GUARANTEE 파라미터를 설정하게 되면 Undo Retention 파라미터로 설정된 시간 동안 재활용을 하지 않고 데이터를 지켜줍니다.
SQL> select tablespace_name, retention from dba_tablespaces;
TABLESPACE_NAME RETENTION
-------------------- -----------
SYSTEM NOT APPLY
SYSAUX NOT APPLY
UNDOTBS1 NOGUARANTEE
TEMP NOT APPLY
USERS NOT APPLY
EXAMPLE NOT APPLY
UNDO01 NOGUARANTEE
7 rows selected.
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO01
SQL> alter tablespace undotbs1 retention guarantee;
Tablespace altered.
SQL> select tablespace_name, retention from dba_tablespaces;
TABLESPACE_NAME RETENTION
-------------------- -----------
SYSTEM NOT APPLY
SYSAUX NOT APPLY
UNDOTBS1 GUARANTEE
TEMP NOT APPLY
USERS NOT APPLY
EXAMPLE NOT APPLY
UNDO01 NOGUARANTEE
7 rows selected.
Undo Tablespace의 경우만 RETENTION의 값이 NOGUARANTEE / GUARANTEE를 가질 수 있고, 다른 Tablespace들은 Not Apply로 적용 대상이 아닙니다.
SQL> alter tablespace undotbs1 retention noguarantee;
Tablespace altered.
SQL> select tablespace_name, retention from dba_tablespaces;
TABLESPACE_NAME RETENTION
-------------------- -----------
SYSTEM NOT APPLY
SYSAUX NOT APPLY
UNDOTBS1 NOGUARANTEE
TEMP NOT APPLY
USERS NOT APPLY
EXAMPLE NOT APPLY
UNDO01 NOGUARANTEE
7 rows selected.
Undo Tablespace 삭제
SQL> drop tablespace undotbs1;
한편 사용 중인 Undo Segment가 있는 Undo Tablespace는 삭제할 수 없습니다. 그런데 가끔 사용하지 않고 있는데 사용 중이라면서 삭제가 되지 않는 경우가 있습니다. 이럴 때는 해당 Undo Segment를 _offline_rollback_segments라는 Hidden Parameter를 사용하여 강제로 offline 시킨 후 삭제하면 됩니다.
5. temporary tablespace
임시자료를 저장하는 tablespace입니다. DB가 재시작되면 이 안의 내용은 전부 사라지게 됩니다. 정렬 작업, export/import 등을 수행할 때 PGA 공간이 부족하면 이곳을 이용해서 작업을 수행 하게 됩니다.
성능을 위해 각 사용자 별로 하나씩 할당해 주는게 좋고, 만약 정렬 작업이 크게 일어나는 작업이 있다면 작업 수행 전에 temporary tablespace를 크게 만들어주고 작업하면 성능 향상에 좋습니다.
temporary tablespace 관리 실습
-- 기존 temporary tablespace 조회
SQL> select file_id, tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_temp_files;
FILE_ID TABLESPACE_NAME MB FILE_NAME
---------- -------------------- ---- --------------------------------------------------
1 TEMP 20 /ORA19/app/oracle/oradata/ORACLE19/temp01.dbf
-- 신규 temporary tablespace 생성
SQL> create temporary tablespace temp2
2 tempfile '/ORA19/app/oracle/oradata/ORACLE19/temp02.dbf' size 10M
3 autoextend on;
Tablespace created.
여러 개의 Temporary Tablespace 중 반드시 1개를 Default Temporary Tablespace로 지정해야 합니다. Default Temporary Tablespace는 독자적인 Temporary Tablespace를 할당 받지 못한 사용자나 System Tablespace가 사용하는 Temporary Tablespace를 의미합니다.
SQL> select * from database_properties
2 where property_name like 'DEFAULT_TEMP%';
PROPERTY_NAME
--------------------------------------------------------------------------------------------------------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE
TEMP
Name of default temporary tablespace
SQL> alter database default temporary tablespace temp2;
Database altered.
SQL> select * from database_properties
2 where property_name like 'DEFAULT_TEMP%';
PROPERTY_NAME
--------------------------------------------------------------------------------------------------------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE
TEMP2
Name of default temporary tablespace
temporary tablespace의 용량이 부족할 경우 sort나 exp/imp 등의 작업 수행 중에 에러가 발생할 수 있습니다. tempfile의 용량을 늘려주는 명령어는 아래와 같습니다.
-- Temporary Tablespace 크기 변경
SQL> alter database tempfile
2 '/ORA19/app/oracle/oradata/ORACLE19/temp02.dbf' resize 20M;
Database altered.
한편 Temporary Tablespace를 삭제할 때 Default Temporary Tablespace는 삭제할 수 없습니다.
-- Temporary Tablespace 삭제(default temporary tablespace를 삭제하려 해서 에러 발생)
SQL> drop tablespace temp2;
drop tablespace temp2
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
SQL> create temporary tablespace temp3
2 tempfile '/ORA19/app/oracle/oradata/ORACLE19/temp03.dbf' size 10M;
Tablespace created.
SQL> alter database default temporary tablespace temp3;
Database altered.
SQL> drop tablespace temp2;
Tablespace dropped.
Temporary Tablespace Group
일반적인 DB 접속 환경은 아래 그림처럼 여러 명의 유저가 1개의 스키마를 통해 DB에 접속하는 경우가 많습니다.
이때, 여러 명의 유저가 모두 정렬 작업을 수행하면 PGA가 부족해 temporary tablespace를 이용하게 될텐데, 한 개의 스키마에는 하나의 temporary tablespace가 할당되어 작업이 지연됩니다. 그래서 Temporary Tablespace Group이라는 개념이 도입되었습니다. 여러 개의 Temporary Tablespace를 하나의 그룹으로 묶어서 스키마에 할당해줍니다. 그럼 여러 명의 사용자가 1개의 스키마를 이용하더라도 각 사용자가 Temporary Tablespace를 할당 받을 수 있어 성능이 향상될 수 있습니다.
SQL> create temporary tablespace temp_scott1
2 tempfile '/app/oracle/oradata/testdb/temp_scott01.dbf' size 10M
3 tablespace group temp_scott_group;
Tablespace created.
이미 만들어져 있는 Temporary Tablespace를 그룹에 추가하려면 아래와 같이 명령하면 됩니다.
-- temp_scott_group은 이미 만들어져 있는 그룹이어야 합니다.
SQL> alter tablespace temp_scott2 tablespace group temp_scott_group;
-- 할당되어 있는 그룹 조회
SQL> select * from dba_tablespace_groups;
-- 만들어져 있는 그룹을 사용자에게 할당하는 명령어
SQL> alter user scott temporary tablespace temp_scott_group;
-- 그룹 탈퇴
SQL> alter tablespace temp_scott2 tablespace group ' ';
참고 도서
- <오라클 관리 실무> - 서진수
