Tablespace = D.B Cache 내에서 데이터를 작업하는 공간
일반적으로 Tablespace가 클수록 수행 속도가 빠름
Tablespace 의 종류 및 특징
(1) SYSTEM tablespace
- 데이터 딕셔너리 정보들이 저장되어 있어서 이 Tablespace 가 손상될 경우 Oracle 서버가 시작이 안됨
데이터 딕셔너리 : 오라클 서버의 모든 정보를 저장하고 있는 아주 중요한 테이블이나 뷰들
- Static Dictionary : 내용이 실시간으로 변경 안됨
USER_XXX , ALL_XXX , DBA_XXX = DB 상태가 OPEN 일때만 조회가능
- Dynamic Performance View : 실시간으로 변경되는 내용을 볼 수 있음, Control File / 메모리로 가서 정보를
가져옴 , V $ XXX = DB상태 모든 상태에서 조회가능
데이터 딕셔너리에 들어있는 주요 정보
- 데이터베이스의 논리적인 구조와 물리적인 구조 정보들
- 객체의 정의와 공간 사용 정보들
- 제약조건에 관련된 정보들
- 사용자에 관련된 정보들
- Role, Privilege 등에 관련된 정보들
- 감사 및 보안등에 관련된 정보들
(2) SYSAUX tablespace
10g 버전부터 등장한 tablespace 로 oracle 서버의 성능 튜닝을 위한 데이터들이 저장되어 있음
(3) 일반 Tablespace
가장 일반적으로 많이 사용되는 tablespace로 관리자가 필요에 의해 만드는 tablespace
DBA 에 의해 얼마든지 생성하고 삭제할 수 있음
실습 1. 일반 Tablespace 생성 및 조회하기
SQL> create tablespace haksa
2 datafile '/app/oracle/oradata/testdb/haksa01.dbf' size 1m ;
Tablespace created.
SQL> select tablespace_name, status, contents, extent_management,
2 segment_space_management
3 from dba_tablespaces ;
TABLESPACE_NAME STATUS CONTENTS EXTENT_MANAGEMENT SEGMENT_SPAC
------------------- -------- -------- -------------------- --------------
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
EXAMPLE ONLINE PERMANENT LOCAL AUTO
HAKSA ONLINE PERMANENT LOCAL AUTO
7 rows selected.
SQL> select tablespace_name, bytes/1024/1024 MB , file_name
2 from dba_data_files ;
TABLESPACE_NAME MB FILE_NAME
-------------------- ---- ----------------------------------------
EXAMPLE 346 /app/oracle/oradata/testdb/example01.dbf
USERS 8 /app/oracle/oradata/testdb/users01.dbf
UNDOTBS1 90 /app/oracle/oradata/testdb/undotbs01.dbf
SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf
SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf
HAKSA 1 /app/oracle/oradata/testdb/haksa01.dbf
6 rows selected.
실습 2. 각 Data file 의 실제 사용량 확인하는 방법
SQL> set line 200;
SQL> col file# for 999 ;
SQL> col ts_name for a10 ;
SQL> col total_blocks for 9999999 ;
SQL> col used_blocks for 9999999 ;
SQL> col pct_used for a10 ;
SQL> select distinct d.file_id file#,
2 d.tablespace_name ts_name,
3 d.bytes /1024/1024 MB,
4 d.bytes /8192 total_blocks,
5 sum(e.blocks) used_blocks,
6 to_char(nvl(round(sum(e.blocks)/(d.bytes/8192), 4),0) *100, '09.99') || '%' pct_used
7 from dba_extents e, dba_data_files d
8 where d.file_id = e.file_id(+)
9 group by d.file_id , d.tablespace_name , d.bytes
10 order by 1,2 ;
FILE# TS_NAME MB TOTAL_BLOCKS USED_BLOCKS PCT_USED
----- ---------- ---- ------------ ----------- ----------
1 SYSTEM 710 90880 89992 99.02%
2 SYSAUX 570 72960 68784 94.28%
3 UNDOTBS1 90 11520 2720 23.61%
4 USERS 8 960 736 76.67%
5 EXAMPLE 346 44240 39568 89.44%
6 HAKSA 1 128 00.00%
6 rows selected.
실습 3. 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 MB FILE_NAME
---------- ---- --------------------------------------------------
EXAMPLE 346 /app/oracle/oradata/testdb/example01.dbf
USERS 8 /app/oracle/oradata/testdb/users01.dbf
UNDOTBS1 90 /app/oracle/oradata/testdb/undotbs01.dbf
SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf
SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf
HAKSA 1 /app/oracle/oradata/testdb/haksa01.dbf
6 rows selected.
## haksa Tablespace 에 iphak table을 만들어서 일부로 가득 차게해서 장애생기게 만듬
SQL> create table scott.iphak (studno number ) tablespace haksa ;
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 SCOTT.IPHAK by 8 in tablespace HAKSA <-- 용량부족으로 에러발생
ORA-06512: at line 3
조치방법 : 1. Data file 을 하나 더 추가해 주는 방법
2. Data file 을 크게 늘려주는 방법 (수동증가와 자동증가가 있다)
조치방법 1. 수동으로 Tablespace 에 Data file 을 추가하는 방법
SQL> alter tablespace haksa
2 add datafile '/app/oracle/oradata/testdb/haksa02.dbf' size 20M ; <-- data file 추가
Tablespace altered.
SQL> select tablespace_name, bytes/1024/1024 MB , file_name from dba_data_files ;
TABLESPACE MB FILE_NAME
---------- ---- --------------------------------------------------
EXAMPLE 346 /app/oracle/oradata/testdb/example01.dbf
USERS 8 /app/oracle/oradata/testdb/users01.dbf
UNDOTBS1 90 /app/oracle/oradata/testdb/undotbs01.dbf
SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf
SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf
HAKSA 1 /app/oracle/oradata/testdb/haksa01.dbf
HAKSA 20 /app/oracle/oradata/testdb/haksa02.dbf
7 rows selected.
조치방법 2. Data file 크기 수동 증가 시키기
SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf'resize 20M ; <--data file 크기 로증가
Database altered.
조치방법 3. Data file 크기 자동 증가 시키기
SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' autoextend on ;
Database altered.
data file 은 자동 증가하게 되며 ORACLE 이 32 비트용일 경우 최대 파일 1개의 크기는 16GB까지 가능하며
ORACLE 이 64비트용일 경우 최대 크기가 32GB 까지 가능
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files ;
TABLESPACE MB FILE_NAME
---------- ---- --------------------------------------------------
EXAMPLE 346 /app/oracle/oradata/testdb/example01.dbf
USERS 8 /app/oracle/oradata/testdb/users01.dbf
UNDOTBS1 90 /app/oracle/oradata/testdb/undotbs01.dbf
SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf
SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf
HAKSA 20 /app/oracle/oradata/testdb/haksa01.dbf <-- 용량이 증가됨
HAKSA 20 /app/oracle/oradata/testdb/haksa02.dbf
7 rows selected.
## 각 data file 들의 autoextend 유무 확인하기
SQL> set line 200
SQL> col tablespace_name for a10
SQL> col file_name for a50
SQL> select tablespace_name, bytes/1024/1024 MB, file_name, autoextensible "AUTO", online_status
2 from dba_data_files ;
TABLESPACE MB FILE_NAME AUTO ONLINE_STATUS
---------- ---- ------------------------------------ ---- ----------------
EXAMPLE 346 /app/oracle/oradata/testdb/example01.dbf YES ONLINE
USERS 8 /app/oracle/oradata/testdb/users01.dbf YES ONLINE
UNDOTBS1 90 /app/oracle/oradata/testdb/undotbs01.dbf YES ONLINE
SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf YES ONLINE
SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf YES SYSTEM
HAKSA 20 /app/oracle/oradata/testdb/haksa01.dbf YES ONLINE
HAKSA 20 /app/oracle/oradata/testdb/haksa02.dbf NO ONLINE
7 rows selected.
SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.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 MB FILE_NAME AUTO ONLINE_STATUS
---------- ---- ------------------------------------ ------ --------------
EXAMPLE 346 /app/oracle/oradata/testdb/example01.dbf YES ONLINE
USERS 8 /app/oracle/oradata/testdb/users01.dbf YES ONLINE
UNDOTBS1 90 /app/oracle/oradata/testdb/undotbs01.dbf YES ONLINE
SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf YES ONLINE
SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf YES SYSTEM
HAKSA 20 /app/oracle/oradata/testdb/haksa01.dbf YES ONLINE
HAKSA 20 /app/oracle/oradata/testdb/haksa02.dbf NO ONLINE
7 rows selected.
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.
실습 4. Tablespace Offline
Tablespace를 offline 한다는 것은 더이상 tablespace에 접근을 못한다는 의미로 해당 tablespace만 shutdown 시키는것
특정 tablespace 의 데이터파일의 위치를 이동하거나 장애가 나서 복구할때 유용하게 사용
Offline 하는 3가지 방법
1. Normal Mode - 아무런 문제가 없을때 정상적으로 수행하는 방법
SQL> alter tablespace haksa offline ;
Database altered.
2. Temporary Mode - 현재 offline 시키고자 하는 tablespace 의 data file 이 하나라도 이상이 생기게 되었을때 사용
명령어 - offline temporary
3. Immediate Mode - archive log mode 일 경우에만 사용. data file에 장애가 나서 데이터를 내려쓰지 못하는 상황에 tablespace를 offline 해야 할 경우에 사용.
archive log mode 에서 사용
SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' offline ;
no archive log mode 에서 사용 -> 노 아카이브 모드에서 오프라인할 경우 recovery 하라는 메세지가 나옴
SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' offline drop ;
data file 이 online 인지 offline 인지 확인하는 방법
SQL> select file#, name, status from v$datafile;
FILE# NAME STATUS
----- -------------------------------------- --------
1 /app/oracle/oradata/testdb/system01.dbf SYSTEM
2 /app/oracle/oradata/testdb/sysaux01.dbf ONLINE
3 /app/oracle/oradata/testdb/undotbs01.dbf ONLINE
4 /app/oracle/oradata/testdb/users01.dbf ONLINE
5 /app/oracle/oradata/testdb/example01.dbf ONLINE
6 /app/oracle/oradata/testdb/haksa01.dbf ONLINE
7 /app/oracle/oradata/testdb/haksa02.dbf ONLINE
7 rows selected.
no archive log mode 에서 offline 하고 조회
SQL> alter database datafile '/app/oracle/oradata/testdb/haksa02.dbf' offline drop ;
Database altered.
SQL> select file# , name , status from v$datafile ;
FILE# NAME STATUS
----- ------------------------------------ ---------
1 /app/oracle/oradata/testdb/system01.dbf SYSTEM
2 /app/oracle/oradata/testdb/sysaux01.dbf ONLINE
3 /app/oracle/oradata/testdb/undotbs01.dbf ONLINE
4 /app/oracle/oradata/testdb/users01.dbf ONLINE
5 /app/oracle/oradata/testdb/example01.dbf ONLINE
6 /app/oracle/oradata/testdb/haksa01.dbf ONLINE
7 /app/oracle/oradata/testdb/haksa02.dbf RECOVER
7 rows selected.
< 강제로 datafile 을 offline 시키면 위와 같이 복구가 필요한 상태로 변함 >
SQL> alter tablespace example offline ;
Tablespace altered.
SQL> select file# , name , status from v$datafile ;
FILE# NAME STATUS
----- ----------------------------------- ----------
1 /app/oracle/oradata/testdb/system01.dbf SYSTEM
2 /app/oracle/oradata/testdb/sysaux01.dbf ONLINE
3 /app/oracle/oradata/testdb/undotbs01.dbf ONLINE
4 /app/oracle/oradata/testdb/users01.dbf ONLINE
5 /app/oracle/oradata/testdb/example01.dbf OFFLINE
6 /app/oracle/oradata/testdb/haksa01.dbf ONLINE
7 /app/oracle/oradata/testdb/haksa02.dbf RECOVER
7 rows selected.
Tablespace 를 offline 하게되면 그 파일들에는 새로운 정보가 저장되지 않는다. 그래서 offline 하고 online을 한다면 반드시 체크포인트를 발생시켜 data file 간의 동기화 작업을 해줘야 한다.
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# ;
from v#datafile a, v$tablespace b
FILE# TS# NAME STATUS CHECKPOINT_CHANGE#
----- ---- ------ ------- ------------------
1 0 SYSTEM SYSTEM 1228512
2 1 SYSAUX ONLINE 1228512
3 2 UNDOTBS1 ONLINE 1228512
4 4 USERS ONLINE 1228512
5 6 EXAMPLE OFFLINE 1229265 <-- (checkpoint scn 이 다른파일과 다른것을 알수 있다
6 7 HAKSA ONLINE 1229084 여기서 online 을 해도 scn 은 여전히 다르다 )
7 7 HAKSA RECOVER 1229084
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 1228512
2 1 SYSAUX ONLINE 1228512
3 2 UNDOTBS1 ONLINE 1228512
4 4 USERS ONLINE 1228512
5 6 EXAMPLE ONLINE 1229323 <-- 여전히 다름
6 7 HAKSA ONLINE 1229084
7 7 HAKSA RECOVER 1229084
7 rows selected.
이 상태에서 data file 을 백업 받는다면 백업파일 자체가 문제가 생기게 되고, 향후 복구에 문제가 될 수 있음
이럴 경우 alter system checkpoint ; 명령어를 사용해서 수동으로 체크포인트를 발생시킨후 모두 동기화 시킨 다음 백업을 받아야 함
SQL> alter system checkpoint ; <-- 강제로 checkpoint 발생시킴
System altered.
SQL> alter tablespace haksa offline ;
alter tablespace haksa offline
*
ERROR at line 1:
ORA-01191: file 7 is already offline - cannot do a normal offline <-- 이미 하나가 offline 상태여서 에러남
ORA-01110: data file 7: '/app/oracle/oradata/testdb/haksa02.dbf'
SQL> alter tablespace haksa offline temporary ; <-- temporary offline 을 사용해서 offline 시켜줌
Tablespace altered.
SQL> recover tablespace haksa ; <-- recovery 필요한 haksa 파일을 복구
Media recovery complete.
SQL> alter tablespace haksa online ; <-- haksa 파일 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# ;
from v$data_file a, v$tablespace b
FILE# TS# NAME STATUS CHECKPOINT_CHANGE#
----- --- ------- -------- ---------------------
1 0 SYSTEM SYSTEM 1229584
2 1 SYSAUX ONLINE 1229584
3 2 UNDOTBS1 ONLINE 1229584
4 4 USERS ONLINE 1229584
5 6 EXAMPLE ONLINE 1229584
6 7 HAKSA ONLINE 1229630 <-- 복구했지만 scn이 다름
7 7 HAKSA ONLINE 1229630 <-- 복구했지만 scn이 다름
7 rows selected.
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 1229674
2 1 SYSAUX ONLINE 1229674
3 2 UNDOTBS1 ONLINE 1229674
4 4 USERS ONLINE 1229674
5 6 EXAMPLE ONLINE 1229674
6 7 HAKSA ONLINE 1229674 <-- SCN 같아짐
7 7 HAKSA ONLINE 1229674 <-- SCN 같아짐
7 rows selected.
실습 5. Data file 이동시키는 작업
data file 을 이동시킬 때 순서가 가장 중요함.
data file 이 사용중일 때 절대 이동시키거나 복사를 하면 안된다. offline 과 shutdown 을 활용해서 사용안함으로 만들자
1. Offline 되는 Tablespace 의 Data file 이동하기
<순서 요약>
1. 해당 Tablespace offline <- 반드시 해야함!!!
2. Data file 을 대상 위치로 복사
3. 컨트롤 파일 내의 해당 Data file 위치 변경
4. 해당 Tablespace online
haksa Tablespace 의 Data file haksa01.dbf 를 이동 /app/oracle/disk1/haksa01.dbf 로 이동시켜보겠음
먼저 이동시킬 디렉토리를 생성
SQL>!mkdir /app/oracle/disk1
SQL> alter tablespace haksa offline ; <-- 변경전에 반드시 offline
SQL> !cp /app/oracle/oradata/testdb/haksa01.dbf /app/oracle/disk1/
SQL>select name from v$datafile ; <-- 컨트롤 파일에 기록된 data file 의 위치확인
NAME
---------------------------------------------
/app/oracle/oradata/testdb/system01.dbf
/app/oracle/oradata/testdb/sysaux01.dbf
/app/oracle/oradata/testdb/undotbs01.dbf
/app/oracle/oradata/testdb/users01.dbf
/app/oracle/oradata/testdb/example01.dbf
/app/oracle/oradata/testdb/haksa01.dbf <-- 요놈
/app/oracle/oradata/testdb/haksa02.dbf
/app/oracle/oradata/testdb/undo01.dbf
SQL>alter tablespace haksa rename
2 datafile '/app/oracle/oradata/testdb/haksa01.dbf' <-- 원래 있던 경로와 파일명
3 to '/app/oracle/disk1/haksa01.dbf' ; <-- 바뀐 경로와 파일명
SQL>select name from v$datafile ;
NAME
---------------------------------------------
/data2/disk3/system01.dbf
/data2/disk3/sysaux01.dbf
/data2/disk4/undotbs01.dbf
/data2/disk5/users01.dbf
/data2/disk4/example01.dbf
/app/oracle/disk1/haksa01.dbf <-- 바뀐것을 확인할 수 있다
/app/oracle/oradata/testdb/haksa02.dbf
/app/oracle/oradata/testdb/undo01.dbf
SQL>alter tablespace online <-- 다시 online 해주면 완료
2. Offline 안되는 Tablespace 의 Data file 이동하기
Offline 안되는 Tablespace 3가지
- system tablespace
- undo tablespace
- default tablespace
위 3가지를 이동할 때는 offline 이 안되기 때문에 db를 종료하고 작업을 해야함
<순서요약>
1. DB를 종료
2. MOUNT 상태로 시작
3. Data file 을 복사
4. 컨트롤 파일의 내용을 변경
5. DB OPEN
system01.dbf 파일을 /app/oracle/disk3/system01.dbf 로 이동시켜보겠음
SQL> shutdown immediate ;
SQL> startup mount ;
SQL> !mkdir /app/oracle/disk3
SQL> !cp /app/oracle/oradata/testdb/system01.dbf /app/oracle/disk3/
SQL> select name from v$datafile ;
NAME
---------------------------------------------
/app/oracle/oradata/testdb/system01.dbf <-- 요놈
/app/oracle/oradata/testdb/sysaux01.dbf
/app/oracle/oradata/testdb/undotbs01.dbf
/app/oracle/oradata/testdb/users01.dbf
/app/oracle/oradata/testdb/example01.dbf
/app/oracle/disk3/haksa01.dbf
/app/oracle/oradata/testdb/haksa02.dbf
/app/oracle/oradata/testdb/undo01.dbf
SQL> alter database rename
2 file '/app/oracle/oradata/testdb/system01.dbf' <--변경전 경로와 이름
3 to '/app/oracle/disk3/system01.dbf' ; <--변경후 경로와 이름
SQL> select name from v$datafile ;
NAME
---------------------------------------------
/app/oracle/disk3/system01.dbf <-- 변경 되었다
/app/oracle/oradata/testdb/sysaux01.dbf
/app/oracle/oradata/testdb/undotbs01.dbf
/app/oracle/oradata/testdb/users01.dbf
/app/oracle/oradata/testdb/example01.dbf
/app/oracle/disk3/haksa01.dbf
/app/oracle/oradata/testdb/haksa02.dbf
/app/oracle/oradata/testdb/undo01.dbf
SQL> alter database open ;
DB MOUNT상태면 모든 Data file 및 Redo log 파일도 이동시킬 수 있다.
3. Redo log file 이동하기
Redo log file 은 offline 안되기 때문에 반드시 사용을 안하게 만들기 위해 D.B를 MOUNT 상태로 두고 작업
1. 현재상태 확인
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 /app/oracle/oradata/testdb/redo01.log 50 NO INACTIVE
2 /app/oracle/oradata/testdb/redo02.log 50 NO CURRENT
3 /app/oracle/oradata/testdb/redo03.log 50 NO INACTIVE
이 redo log 파일들을
/app/oracle/disk4/redo01_a.log , redo02_a.log , redo03_a.log
/app/oracle/disk5/redo01_b.log , redo02_b.log , redo03_b.log 로 이동
SQL> select status from v$instance <-- 시작전에 반드시 Instance 상태부터 확인
STATUS
-------
OPEN <-- redo log 파일을 옮기려면 반드시 mount 상태여야 한다
SQL> shutdown immediate ;
SQL> startup mount ;
SQL> !mkdir /app/oracle/disk4
SQL> !mkdir /app/oracle/disk5
SQL>!cp /app/oracle/oradata/testdb/redo01.log /app/oracle/disk4/redo01_a.log <-- 이름 바꾸며 복사
SQL>!cp /app/oracle/oradata/testdb/redo02.log /app/oracle/disk4/redo03_a.log
SQL>!cp /app/oracle/oradata/testdb/redo03.log /app/oracle/disk4/redo03_a.log
SQL> alter database rename
2 file '/app/oracle/oradata/testdb/redo01.log' <--변경 전 이름과 위치
3 to '/app/oracle/disk4/redo01_a.log' ; <-- 변경 후 이름과 위치
SQL> alter database rename
2 file '/app/oracle/oradata/testdb/redo02.log' <--변경 전 이름과 위치
3 to '/app/oracle/disk4/redo02_a.log' ; <-- 변경 후 이름과 위치
SQL> alter database rename
2 file '/app/oracle/oradata/testdb/redo03.log' <--변경 전 이름과 위치
3 to '/app/oracle/disk4/redo03_a.log' ; <-- 변경 후 이름과 위치
SQL> select member from v$logfile ;
MEMBER
---------------------------------------
/app/oracle/disk4/redo01_a.log <-- 바뀌었음
/app/oracle/disk4/redo02_a.log <-- 바뀌었음
/app/oracle/disk4/redo03_a.log <-- 바뀌었음
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
---------------------------------------
/app/oracle/disk4/redo01_a.log
/app/oracle/disk4/redo02_a.log
/app/oracle/disk4/redo03_a.log
/app/oracle/disk5/redo01_b.log <-- 추가되었음
/app/oracle/disk5/redo02_b.log <-- 추가되었음
/app/oracle/disk5/redo03_b.log <-- 추가되었음
SQL> alter database open ;
실습 6 . Tablespace 삭제하기
SQL> drop tablespace haksa ; <-- table이 하나라도 있으면 그냥 안지워 진다
SQL> drop tablespace haksa including contents and datafiles ; <-- 옵션을 줘서 삭제해야함
drop 은 가급적 사용하지 않음
(2) Undo Tablespace
Undo data 란 DML 을 수행할 경우 발생하는 원본 데이터. 즉 홍길동을 일지매로 업데이트 할경우 홍길동이 Undo data
Undo data 만을 저장하는 segment 를 undo segment 라고 하며, undo segment를 저장하고 있는 tablespace 를 undo tablespace 라고 한다.
1. Undo Tablespace 의 특징
- Oracle Server process 는 tablespace 에 undo segment 를 생성하고 각 사용자 별로 undo segment 를 할당해서 undo data를 관리. 단, 사용자는 관여할 수 없음
- Undo tablespace 는 instance 당 여러개가 동시에 존재 할 수 있지만 사용되는 것은 한번에 1개 뿐
- undo tablespace 관리방법에는 자동 Mode 인 Automatic Undo Management (AUM) 과 수동 모드가 있다.
자동 Mode로 관리하려면 초기화 파라미터 파일( pfile, spfile )에 undo_management=auto 로 설정
수동 Mode로 관리하려면 초기화 파라미터 파일( pfile, spfile )에 undo_management=manual 로 설정
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 로 만들어 주는데 사용
< 데이터 업데이트 순서 >
1. 데이터를 DB Buffer Cache 로 복사
2. Redo log Buffer 에 변경내용 기록
3. Undo segment 기록
4. DB Buffer Cache 의 원본 변경
< CR 작업 >
- 사용자 A 가 empno=10 번인 일지매를 홍길동으로 변경하는 Update 문을 수행해서 DB Buffer Cache 에 1번과 같이 이름이 변경 되었고, Lock 이 설정되어 사용자 A가 commit 이나 rollback 을 수행하기 전까지 아무도 1번 블록의 변경된 데이터를 볼 수 없는 상태이다.
- 이 상태에서 사용자 B가 empno=10 번의 조건으로 모든 컬럼을 가져오는 쿼리를 수행 했을 경우 B사용자의 Server Process 는 3번 Undo 에 있는 데이터를 2번과 같이 DB Buffer Cache로 복사해 와서 데이터를 조회
이와 같은 과정을 CR (Consistent Read - 읽기 일관성 ) 작업이라고 한다.
3. Undo segment 할당되는 원리
Undo Tablespace 안의 Undo Data file 의 크기는 증가만 되고, 줄어들지 않는다.
위 그림은 Undo Tablespace 안에 Undo Segment 4개가 할당되어 각각 A , B , C , D 에 의해 사용되고 있는 상태
이 상황에서 E 사용자가 새로 접속해 DML 을 수행할 경우 Undo Segment를 확보하게 되는데 이때 확보하는 Undo Segment
가 기존에 있던 segment 중에서 트랜잭션이 완료된 것을 먼저 확인후 완료된 트랜잭션에 덮어 쓰는 형식
아래 그림은 A 가 쓰던 Undo segment가 commit이 된 상태라 E가 덮어쓴 것
다음 새로 F 사용자가 접속해서 DML을 수행하려 할때 위의 모든 사용자 중에서 아무도 트랜잭션이 완료되지 않았으면
새로운 Undo segment 를 생성해서 기록
이렇게 undo segment 가 data file 의 저장공간이 허용하는 범위까지 늘어나다가 data file에 더 공간이 없게되면, 하나의
segment 에 2개 세션 이상의 undo data를 함께 기록.
그러다 그 공간마저 없으면 해당 트랜잭션은 에러가 발생
※ Undo table space의 늘어난 data file의 용량은 덮어쓸 뿐이지 삭제되지 않아서 크기는 줄어들지 않음
나중에 비정상적으로 data file 이 커지면 관리자가 다른 undo tablespace 를 신규로 만들어 변경시키고, 기존 undo tablespace 를 삭제해 주어야 한다.
실습 1. 현재 상태 파악
SQL> show parameter undo ;
실습 2. 신규 undo tablespace 생성
SQL> create undo tablespace undo01
2 datafile '/app/oracle/oradata/testdb/undo01.dbf size 10 M
3 autoextend on ;
SQL> select tablespace_name, bytes/1024/1024 mb , file_name from dba_data_files ; <--생성된 undo tablespace 경로확인
실습 3. Undo tablespace 변경 ( undotbs1 -> undo01 )
SQL> show parameter undo ;
NAME TYPE VALUE
------------------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_tablespace=undo01 ; <-- 재부팅 없이 즉시 변경 가능
-> pfile 을 사용할 경우 이 작업 후 반드시 파라미터 파일의 내용도 변경해야 DB 재시작후 장애가 없음
SQL> show parameter undo ;
NAME TYPE VALUE
------------------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO01
(5) temporary tablespace
임시 자료를 저장하는 tablespace 로 DB가 재시작되면 이 곳에 있던 내용은 모두 사라지며, 일반적으로 정렬작업을 할 때 PGA 공간이 부족하면 이곳을 이용해 정렬작업 등을 하게됨, 정렬작업 외에 Export/Import 등 여러가지 작업을 할 경우도 사용
Temporary tablespace 는 하나의 Instance에 여러개 만들 수 있으므로 성능향상을 위해 사용자별로 하나씩, 크게 할당해주는게 좋다.
< Temporary tablespace 동작 원리 >
1. 사용자가 100 건의 데이터를 출력하고자 한다.
2. Disk 에서 DB캐쉬로 데이터100 건을 복사
3. 정렬을 위해 PGA로 데이터를 옮길 때 데이터가 옮기려는 데이터가 많을 경우 Sort run 단위로 잘라서 이동
데이터 양이 적어서 PGA에서 한번에 정렬할 수 있으면 Temporary tablespace 로 안가고 바로 정렬해서 출력
4. PGA 에서 데이터를 정렬해서 Temporary Tablespace 로 이동
5. Temporary tablespace 에서 100건의 데이터를 모아서 병합(merge) 시켜 사용자에게 출력
실습 1. temporary tablespace 조회
SQL> select file_id, tablespace_name, bytes 1024/1024 MB, file_name
2 from dba_temp_files ;
FILE_ID TABLESPAE MB FILE_NAME
------ ---------- --- ----------
2 TEMP 10 /app/oracle/oradata/testdb/temo01.dbf
2. 신규 temporary tablespace 생성
SQL> create temporary tablespace temp2
2 tempfile '/app/oracle/oradata/testdb/temp02.dbf' size 10 M
3 autoextend on ;
3. Default temporary tablespace 설정
SQL> select * from databae_properties
2 where property_name like 'DEFAULT_TEMP%' ;
PROPERTY_NAME PROPERTY_V DESCRIPTION
------------------------- ----------- -------------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
SQL> alter database default temporary tablespace temp2 ;
SQL> select * from databae_properties
2 where property_name like 'DEFAULT_TEMP%' ;
PROPERTY_NAME PROPERTY_V DESCRIPTION
------------------------- ----------- -------------------------------
DEFAULT_TEMP_TABLESPACE TEMP2 Name of default temporary tablespace
4. temporary tablespace 크기 변경하기
SQL> alter database tempfile '/app/oracle/oradata/testdb/temp02.dbf' resize 100M ;
5. temporary tablespace 삭제하기 < default temporary tablespace 는 삭제 안됨 >
SQL> create temporary tablespace temp3
2 tempfile '/app/oracle/oradata/testdb/temp03.dbf' size 10M ;
SQL> alter database default temporary tablespace temp3 ; <--temp3 생성하여 default로 변경
SQL> drop tablespace temp2 ;
Temporary tablespace group ( 11g 부터 생김 )
기존에는 schema 에 할당된 temporary tablespace가 1개뿐이라서 여러명이 접속해 작업을 하면 늦어지게 됬지만
11g 부터는 temporary tablespace 를 여러개 만들어 그룹으로 묶어주고 특정 schema에 temporary tablespace group을 지정해 주는 것. 그렇게 되면 하나의 스키마로 여러명의 사용자가 동시 로그인해서 작업을 하더라도 각각 temporary tablespace group 안에 있는 여러 개의 temporary tablespace를 각각 별도로 사용해 성능이 향상됨
'오라클 > 관리 실무' 카테고리의 다른 글
| DBMS_JOB & DBMS_SCHEDULER (0) | 2014.10.27 |
|---|---|
| 사용자 관리 (0) | 2014.10.27 |
| Oracle 저장구조 (0) | 2014.10.23 |
| Redo Log 관리하기 (0) | 2014.10.20 |
| Control File 관리하기 (0) | 2014.10.20 |
| ORACLE 시작하기 & 종료하기 (0) | 2014.10.20 |
| Oracle Background Process (0) | 2014.10.17 |