1. 개요
오라클은 데이터를 저장하고 작업을 할 때 메모리에
논리적으로는 Tablespace 라는 공간을 만들어서 작업하고
물리적으로는 디스크에 Data file 을 만들어서 저장한다.
예를 들어 매장은 Tablespace, 창고는 datafile
사용자가 SQL 문장을 수행하면 해당 데이터(Table 등) 는 반드시 메모리에 있는 Tablespace 에 존재해야 한다.
즉, 사용자가 찾는 데이터는 반드시 메모리에 있어야 한다는 뜻이다.
(단, Direct Path 와 같은 예외도 있다.)
만약 사용자가 찾는 데이터가 메모리에 존재하지 않으면 Server Process 가
데이터가 저장되어 있는 창고인 Data File 에 가서 해당 데이터가 들어있는 블록을 찾아서
Tablespace 로 가져온 후 사용자가 원하는 데이터를 꺼내서 전달해주게 된다.
Oracle 도 Database Buffer Cache 에 Tablespace 를 생성하며
이 Tablespace 가 사용하는 메모리 공간이 클수록 일반적으로 수행 속도가 빨라지게 된다.
어디에 어떤 데이터가 있는지 목록으로 관리하는 것이 오브젝트 인덱스 이다.
shutdown 하면 Database Buffer Cache 에 있는 데이터를 파일로 저장하고 매장 문을 닫는다.
2. Tablespace 의 종류 및 특징
(1) SYSTEM tablespace
SYSTEM tablespace 에는 데이터 딕셔너리들이 저장되어 있다.
데이터 딕셔너리란 오라클 서버의 모든 정보를 저장하고 있는 아주 중요한 테이블이나 뷰들을 말한다.
버전 별 딕셔너리 수
SQL> select count(*) from dictionary ;
9i 1284
10g 1882
11g 2600
데이터 딕셔너리는 크게 나누면 Base Table 과 Data Dictionary View 로 나눌 수 있다.
Base Table 은 데이터베이스를 생성할 때 생성된다.
DBA 도 접근할 수 없다. 그래서 Data Dictionary View를 제공한다.
DBCA 를 사용하면 Data Dictionary View 가 생성되지만
Create Database 를 수행할 경우는 catalog.sql 스크립트를 추가로 수행해주어야 생성된다.
데이터 딕셔너리의 주요 내용
1. 데이터베이스의 논리적인 구조와 물리적인 구조 정보들
2. 객체의 정의와 공간 사용 정보들
3. 제약조건에 관련된 정보들
4. 사용자에 관련된 정보들
5. Role, Privilege 등에 관련된 정보들
6. 감사 및 보안 등에 관련된 정보들
Data Dictionary View 는 크게
Static Dictionary (자동 변경되지 않음) 와 Dynamic Dictionary 로 나눌 수 있다.
테이블에 있는 데이터 건수 확인하기
Step1. 테스트용 테이블 생성 후 데이터 입력
SYS>create table stest (no number) ;
SYS>begin
2 for i in 1..100 loop
3 insert into stest values (i) ;
4 end loop ;
5 commit ;
6 end ;
7 /
SYS>select count(*) from stest ;
step2. USER_TABLES 라는 딕셔너리의 내용을 조회한다.
SYS>select table_name, num_rows
2 from user_tables
3 where table_name='STEST' ;
SYS>analyze table stest compute statistics ;
SYS>select table_name, num_rows
2 from user_tables
3 where table_name='STEST' ;
정확한 결과가 보인다.
딕셔너리 내용을 수동으로 업데이트 할 때 Analyze 명령어나 DBMS_STATS 라는 패키지를 사용하는데
해당 테이블을 전체 스캔하는 명령어로 서버 성능에 아주 나쁜 영향을 주는 경우가 많다.
절대 함부로 사용해서는 안되는 명령어이다.
위에서 실습한 딕셔너리는 USER_TABLES 이다.
딕셔너리는 그 종류가 너무 많아서 오라클에서 많이 사용되는 딕셔너리를
4개의 카테고리로 분류해 놓고 있다.
USER_XXX
해당 사용자가 생성한 내용만 볼 수 있다.
ALL_XXX
해당 사용자가 생성한 것과 접근 가능한 내용을 볼 수 있다.
DBA_XXX
모든 내용을 다 볼 수 있다. DBA 권한을 가진 사용자만 조회 가능
위의 3가지 모두 Static Dictionary 다. 내용이 실시간으로 변경이 되지 않는다.
V$로 시작하는 딕셔너리는
실시간으로 변경되는 내용을 볼 수 있는 Dynamic Performance View 는
조회하는 시점의 데이터를 Control file 이나 메모리에서 가져와서 보여준다.
(2) SYSAUX tablespace
10g 버전부터 추가됨
성능 튜닝과 관련된 딕셔너리들이 저장되고,
AWR, ADDM, ASH 등 자동 튜닝 기능들이 이곳의 정보를 사용한다.
(3) 일반 Tablespace
가장 일반적으로 많이 쓰이고 관리자가 필요에 의해서 만드는 Tablespace 다.
실습1. 일반 Tablespace 생성 및 조회하기
SYS>create tablespace haksa
2 datafile '/app/oracle/oradata/testdb/haksa01.dbf' size 1M ;
SYS>select tablespace_name, status, contents, extent_management,
2 segment_space_management
3 from dba_tablespaces ;
SYS>select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files ;
실습2. 각 Data file 의 실제 사용량을 확인하는 방법
SYS>set line 200 ;
SYS>col file# for 999 ;
SYS>col ts_name for a10 ;
SYS>col total_blocks for 9999999 ;
SYS>col used_blocks for 9999999 ;
SYS>col pct_used for a10
SYS>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 ;
실습3. Tablespace 용량 관리하기
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 ;
SYS>create table scott.iphak (studno number) tablespace haksa ;
SYS>begin
2 for i in 1..50000 loop
3 insert into scott.iphak values (i) ;
4 end loop ;
5 commit ;
6 end ;
7 /
SYS>/
용량 부족으로 에러 발생
조치방법 1. 수동으로 Tablespace 에 Datafile 추가하는 방법
SYS>alter tablespace haksa
2 add datafile '/app/oracle/oradata/testdb/haksa02.dbf' size 20M ;
SYS>select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files ;
조치방법2. Data file 크기 수동 증가시키기
SYS> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' resize 20M ;
조치방법3. Data file 크기 자동 증가시키기
SYS>alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' autoextend on ;
# 각 Data File 들의 autoextend 유무 확인하기
SYS>set line 200
SYS>col tablespace_name for a10
SYS>select tablespace_name, bytes/1024/1024 MB, file_name, autoextensible "Auto", online_status
2 from dba_data_files ;
SYS>alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' autoextend on ;
SYS>select tablespace_name, bytes/1024/1024 MB, file_name, autoextensible "Auto", online_status
2 from dba_data_files ;
## autoextend 되는지 테스트 하기
SYS>begin
2 for i in 1..500000 loop
3 insert into scott.iphak values (i) ;
4 end loop ;
5 commit ;
6 end ;
7 /
SYS>select tablespace_name, bytes/1024/1024 MB, file_name, autoextensible "Auto", online_status from dba_data_files ;
실습4. Tablespace Offline
Tablespace 를 Offline 한다는 것은 사용자가 더이상 해당 Tablespace 에 접근하지 못한다는 의미다.
해당 Tablespace 만 shutdown 시키는 것과 같은 의미이다.
이 기능은 특정 Tablespace 의 데이터 파일의 위치를 이동한다던지
혹은 특정 Tablespace 가 장애가 나서 복구를 해야 할 때 유용하게 사용되는 방법이다.
Tablespace offline 3가지 방법
* Nomal Mode
Tablespace 에 아무런 문제가 없을 때 정상적으로 수행
SYS> alter tablespace haksa offline ;
* Temporary Mode
현재 offline 시키고자 하는 Tablespace 의 Data file 이 하나라도 이상이 생기게 되면 offline nomal 이 수행되지 못한다.
이럴 때 쓰는 것이 offline temporary 이다.
* Immediate Mode
archive log mode 일 경우에만 사용해야 한다.
이 옵션은 Data file 에 장애가 나서 데이터를 내려쓰지 못하는 상황에서 Tablespace 를 offline 해야 할 경우에 사용한다.
SYS> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' offline ;
이 명령어는 DB가 archive log mod 일 경우에 사용가능하다.
만약 DB가 no archive log mode 라면
SYS> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' offline drop ;
을 수행해서 Data file 을 offline 시킬 수 있다.
그러나 노 아카이브 모드에서 offline drop 한 후 online 을 할 경우 recovery 하라는 메시지가 나온다.
어떤 Data file 이 offline 인지는 아래와 같이 v$datafile 을 조회하면 알 수 있다.
SYS>col name for a50
SYS>select file#, name, status from v$datafile ;
SYS>alter database datafile '/app/oracle/oradata/testdb/haksa02.dbf' offline drop ;
SYS>select file#, name, status from v$datafile ;
archive log mode 에서 data file 을 강제로 offline 시키면 저렇게 복구가 필요한 상태가 된다.
SYS>alter tablespace users offline ;
SYS>select file#, name, status from v$datafile ;
SYS>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# ;
위 내용을 보면 offline 상태여서 checkpoint scn 이 다른 파일들과 다름을 알 수 있다.
이 상태에서 online 을 시켜도 아래처럼 scn 은 여전히 다른 상태이다.
SYS>alter tablespace users online ;
SYS>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# ;
이 상태에서 만약 Data file 을 백업을 받는다면 백업 파일 자체가 문제가 생기고 향후 복구에 문제가 될 수 도 있다.
이럴 경우에 수동으로 체크포인트를 발생시키면 모두 동기화가 된다. 그 후에 백업을 받아야 한다.
SYS>alter tablespace haksa online ;
SYS>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# ;
SYS>alter system checkpoint ;
SYS>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# ;
실습5. Data file 이동시키는 작업
특정 디스크의 Data file 들의 용량이 부족하여
새로운 디스크를 추가한 후 몇 개의 Data file 을 신규 디스크로 이동시키는 등의 작업을 할 때나
장애 발생시 복구 할 때 사용되는 중요한 방법이다.
※ Data file 을 사용 중일 때는 절대로 이동하거나 복사를 하면 안된다.
특정 데이터 파일을 사용하지 않게 만드는 방법은 Offline 과 Shutdown 이다.
1) Offline 되는 Tablespace 의 Data file 이동하기
Offline 기능은 여러 Tablespace 중에서 특정 Tablespace 만을 종료시키는 기능으로
DB 를 종료하지 않고 작업할 수 있으므로 아주 좋은 방법이다.
해당 Tablespace 만 shutdown 했다는 의미와 동일하다.
haksa tablespace 의 Data file 을 /app/oracle/disk1/haksa01.dbf, /app/oracle/disk2/haksa02.dbf 로 이동
SYS>!mkdir /app/oracle/disk1
SYS>!mkdir /app/oracle/disk2
순서요약
1. 해당 Tablespace offline !!
2. Data file 을 대상 위치로 복사
3. 컨트롤 파일 내의 해당 Data file 위치 변경
4. 해당 Tablespace online
SYS>alter tablespace haksa offline ;
SYS>!cp /app/oracle/oradata/testdb/haksa01.dbf /app/oracle/disk1/
SYS>!cp /app/oracle/oradata/testdb/haksa02.dbf /app/oracle/disk2/
SYS>select name from v$datafile ;
SYS>alter tablespace haksa rename
2 datafile '/app/oracle/oradata/testdb/haksa01.dbf'
3 to '/app/oracle/disk1/haksa01.dbf' ;
SYS>alter tablespace haksa rename
2 datafile '/app/oracle/oradata/testdb/haksa02.dbf'
3 to '/app/oracle/disk2/haksa02.dbf' ;
2) offline 안 되는 Tablespace 의 Data file 이동하기
offline 이 안되는 tablespace 는 아래 3가지다.
- system tablespace
- 사용 중인 undo tablespace
- default temporary tablespace
offline 이 안되기 때문에 어쩔 수 없이 DB를 종료하고 작업해야 한다.
순서요약
1. DB 종료
2. 마운트 상태로 시작
3. Data file 복사
4. 컨트롤 파일 내용 변경
5. DB 를 open
system01.dbf 파일을 /app/oracle/disk3/system01.dbf 로 이동
SYS>shutdown immediate ;
SYS>startup mount ;
SYS>!mkdir /app/oracle/disk3
SYS>!cp /app/oracle/oradata/testdb/system01.dbf /app/oracle/disk3/
SYS>select name from v$datafile ;
원래 경로로 남아 있다.
SYS>alter database rename
2 file '/app/oracle/oradata/testdb/system01.dbf'
3 to '/app/oracle/disk3/system01.dbf' ;
SYS>select name from v$datafile ;
SYS>alter database open ;
이 방법은 system 과 undo 만 옮길 수 있다는 것이 아니라 DB 가 마운트 상태이면
모든 Data file 및 Redo log 파일도 이 방법으로 이동시킬 수 있다.
3) Redo log file 이동하기
Redo log file 은 Offline 이 안되기 때문에 반드시 사용안하게 하기 위해
Database 를 Mount 상태로 두고 작업해야만 한다.
step1. 현재 상태 확인
SYS>col member for a50
SYS>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 ;
SYS>select status from v$instance ;
SYS>shutdown immediate ;
SYS>startup mount ;
SYS>!mkdir /app/oracle/disk4
SYS>!mkdir /app/oracle/disk5
SYS>select member from v$logfile ;
SYS>!cp /app/oracle/data1/redo01_a.rdo /app/oracle/disk4/redo01_a.log
SYS>!cp /app/oracle/data2/redo01_b.rdo /app/oracle/disk4/redo02_a.log
SYS>!cp /app/oracle/data3/redo01_c.rdo /app/oracle/disk4/redo03_a.log
SYS>alter database rename
2 file '/app/oracle/data1/redo01_a.rdo'
3 to '/app/oracle/disk4/redo01_a.log' ;
SYS>alter database rename
2 file '/app/oracle/data2/redo01_b.rdo'
3 to '/app/oracle/disk4/redo02_a.log' ;
SYS>alter database rename
2 file '/app/oracle/data3/redo01_c.rdo'
3 to '/app/oracle/disk4/redo03_a.log' ;
SYS>select member from v$logfile ;
SYS>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 ;
SYS>select member from v$logfile ;
SYS>alter database open ;
6. Tablespace 삭제하기
SYS>drop tablespace haksa ;
Table 이 하나라도 있으면 안 지워진다.
SYS>drop tablespace haksa including contents and datafiles ;
SYS>select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files ;
연습문제1. Data file, Redo log file, Control file 을 아래처럼 이동시키세요. pfile 로
/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/disk5/users01.dbf, example01.dbf
연습문제2. spfile 을 사용하여 아래와 같이 구성하세요.
/data/disk1/control01.ctl, redo01_a.rdo, redo02_a.rdo, redo03_a.rdo
disk2/control02.ctl, redo01_b.rdo, redo02_b.rdo, redo03_b.rdo
disk3/control03.ctl, sysaux01.dbf
disk4/system01.dbf, undotbs01.dbf
disk5/users01.dbf, example01.dbf
연습문제3. 20G HDD 추가한 후
/data2 로 마운트한 후
pfile 을 사용해서 아래와 같이 구성하세요.
/data2/disk1/system01.dbf, sysaux01.dbf
disk2/undotbs01.dbf, users01.dbf, example01.dbf
disk3/control01.ctl, redo01_a.log, redo02_a.log, redo03_a.log
disk4/control02.ctl, redo01_b.log, redo02_b.log, redo03_b.log
disk5/control03.ctl, redo01_c.log, redo02_c.log, redo03_c.log
리두 로그 그룹번호는 1, 2, 3 으로 하고 각 멤버 크기는 10M 로 하세요.
Undo Tablespace 는 다음 장에....