글
Chap 7. Tablespace 와 Data file 관리하기
1. Tablespace
- Oracle 내에서 데이터가 논리적으로 저장되는 공간이다. 이 공간은 메모리 상에서 생성되며, 모든 작업 데이터는 이 곳에 존재하여야 한다.
- Oracle 내부에서는 data block, extend, segment, tablespace 라는 논리적인 개념으로 데이터를 관리한다.
- Oracle 에서 data를 저장하는 가장 최소 단위가 data block 이며, data block 이 모여서 extend 가 되고, extend 가 모여서 segment, 그리고 segment 가 모여서 tablespace 가 된다.
- 디스크의 물리적인 data file (.dbf 같은) 은 tablespace 와 대응된다.
2. Tablespace 의 종류
1) SYSTEM tablespace
- 데이터 dictionary 들이 저장되어 있는 곳이다. 아래와 같은 중요한 내용들이 담겨져 있다.
a) Database 의 논리적인 구조와 물리적인 구조 정보들
b) 객체의 정의와 공간 사용 정보들
c) 제약조건에 관련된 정보들
d) 사용자에 관련된 정보들
e) Role, Privilege 등에 관련된 정보들
f) 감사 및 보안등에 관련된 정보들
- Oracle 서버의 모든 정보들을 담고 있는 중요한 곳이므로 이 tablespace 의 소유자인 SYS 계정 역시 이 안의 데이터들을 바꾸지 못하고 조회만 가능하다.
- Oracle 에서는 dictionary 종류가 너무 많기 때문에 아래처럼 4개의 카테고리로 분류한다.
|
Static Dictionary (Instance 가 OPEN 일 경우만 조회 가능.) |
USER_XXX |
해당 사용자가 생성한 내용만 볼 수 있는 Dictionary 이다. 예) USER_TABLES : 해당 사용자가 생성한 테이블만 볼 수 있음. |
|
ALL_XXX |
권한만 있다면 볼수 있는 Dictionary 이다. 예) ALL_TABLES : 접근 가능한 모든 테이블 내역을 볼 수 있음. | |
|
DBA_XXX |
DB 에 생성되어 있는 모든 내용을 다 볼 수 있는 Dictionary 이다. 예) DBA_TABLES : DB의 모든 테이블 내역을 볼 수 있음. | |
|
Dynamic Dictionary (Instance 가 NOMOUNT 상태일 때 부터 조회 가능) |
v$xxxx |
실시간으로 변경되는 내용을 조회해 볼 수 있다. v$~ 로 시작하는 것들은 모두 Dynamic 이다. |
2) SYSAUX tablespace
- Oracle 10g 버전부터 새로 등장했다. 주로 서버의 성능 튜닝을 위한 데이터 들이 저장되어 있다.
3) Undo tablespace
- 사용자가 DML 을 수행할 경우 원본 데이터를 저장해 두는 tablespace 이다.
- 사용자가 생성/관리 할 수 있다.
- Undo tablespace 의 사용 목적
** Transaction Rollback : 사용자가 rollback 을 할 경우 이 곳에 저장된 undo data를 사용해서 rollback 을 수행,
** Read Consistency (CR) : 아직 transaction 이 끝나지 않은 데이터는 변경 전 데이터로 보여줍니다. 즉,
A 라는 사용자가 DB에서 장동건을 원빈으로 변경하기 위해 update 문을 수행 했을 경우,
일단 DB buffer cache 에 장동건을 불러다 놓고 -> 변경내용들을 Redo log buffer 에 기록 -> Undo segment 에 원본 데이터인 장동건 기록 -> DB buffer cache 에 업데이트 되는 데이터 원빈을 저장, Lock 이 설정된다.
commit 이나 rollback 이 아직 안된 상태에서, 사용자 B가 장동건을 조회하려고 하면, 현재 DB buffer cahce 에는 원빈이 저장된 채 lock 이 걸린 상태이므로, Undo segment 에서 장동건을 불러다가 다시 DB buffer cache 에 올려두고 사용자에서 제공한다. 이 과정을 CR 작업이라 한다.
** Transaction Recovery (Instance Recovery) : 운영중이던 DB 가 비정상적으로 종료되었을 때 roll forward 와 roll backward 작업을 수행 할 때 undo data가 사용됨.
- Undo tablespace 관리 방법
## 현재 상태 조회하기
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
## 신규 undo tablespace 생성하기
SQL> create undo tablespace undo01 datafile '/app/oracle/oradata/testdb/undo01.dbf' size 10M autoextend on;
Tablespace created.
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;
TABLESPACE MB FILE_NAME
---------- ---------- --------------------------------------------------
USERS 5 /app/oracle/oradata/testdb/users01.dbf
UNDOTBS1 95 /app/oracle/oradata/testdb/undotbs01.dbf
SYSAUX 510 /app/oracle/oradata/testdb/sysaux01.dbf
SYSTEM 700 /app/oracle/oradata/testdb/system01.dbf
EXAMPLE 345.625 /app/oracle/oradata/testdb/example01.dbf
UNDO01 10 /app/oracle/oradata/testdb/undo01.dbf
6 rows selected.
## undo tablespace 변경하기
SQL> alter system set undo_tablespace=undo01;
System altered.
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO01
- Undo segment 할당 원리
Undo Tablespace 의 특징 중 하나는 undo data file 의 크기는 증가만 되고 줄어들지는 않는다.
이유인 즉,
Undo tablespace 안에 undo segment 4개가 할당되어 각각 A, B, C, D 사용자에 의해 사용되고 있다고 가정하고, 새로운 E 사용자가 신규로 접속해서 DML 문장을 수행할 경우, E server process 는 undo segment 를 확보하게 된다.
이 때 무조건 새로운 공간을 할당 받는게 아니라, 기존에 만들어져 있던 segment 중에서 transaction 이 완료 된 것이 있는지 없는지를 확인 한 후 (즉, commit 이나 rollback 을 한 사용자가 있는지 없는지 확인) 만약 A라는 사용자가 commit 을 수행해서 transaction 이 끝났다면, A가 쓰고 있던 공간을 덮어쓰게 된다.
하지만, 덮어쓸 수 있는 공간이 없다면, 그냥 새로운 공간을 하나 만들게 된다.
이런 식으로 계속 undo segment 가 늘어나면서 undo tablespace 가 늘어나게 되고, 모든 사용자가 commit 을 해서 transaction 이 완료 되었다고 하더라도, undo segment 안에 undo data는 지워지지 않는다.
그래서 undo data file 의 크기는 증가만 될 뿐이다.
- Undo 관련 주요 parameter
** UNDO_RETENTION : commit 수행 후에도 해당 undo segment 내의 데이터를 다른 서버 프로세스가 덮어 쓰지 못하도록 막아주는 시간. 하지만 Undo Segment 의 여분이 있을 경우에만 적용된다.
** UNDO_RETENTION_GUARANTEE : guarantee 로 설정하면 무조건 undo retention 을 지켜줌. (기본 값은 noguarantee)
- Snapshot too old (ORA-0155) 에러 및 줄이는 방법
Undo_retention 부분이 잘못되면 나오는 에러가 Snapshot too old 라는 에러이다. 이 에러의 발생 원인은 위의 undo tablespace 의 사용목적중 CR 부분과 관계가 있다.
예를 들어,
A라는 사용자가 emp 테이블에서 장동건 이라는 사원을 원빈으로 변경한다고 하면, 일단 장동건을 DB buffer cache 에 올리고, Redo log buffer 에 변경 내용 기록하고, Undo segment 을 할당해서 원본 데이터인 장동건을 기록할 것이다. 그리고 나서 DB buffer cache 에 원빈으로 변경 된 내용이 lock 이 걸린 상태가 될 것이다.
이 때 B라는 사용자가 장동건을 emp 테이블에서 꺼내오려고 한다면, DB buffer cache 가 아닌 Undo segment 에서 장동건을 가져다가 B 사용자에게 보여주게 된다. 이게 CR 과정이라고 하였다.
하지만 만약!
B 사용자가 장동건을 undo segment 에서 꺼내려는데 A 사용자가 commit 을 날리고, 그 상황에서 새로운 DML 문장이 실행되서 새로운 데이터가 이 장동건이라는 undo segment 부분을 덮어 써 버렸다면 어떻게 될까?
B 사용자는 undo segment 장동건이라는 사원을 찾지 못하고 에러를 발생 할 것이다.
이 에러가 바로 ORA-0155 Snapshot too old 라는 에러이다.
이 에러를 줄이는 방법으로는,
** UNDO_RETENTION 을 셋업할 수 있다. 하지만 이 역시 commit 이 많이 발생하면 그 만큼 undo segment 가 덮어써 지는 경우가 늘어나고 이렇게 되면 또 다시 Snapshot too old 에러가 늘어날 수 있으므로, 불필요한 commit 은 자제한다.
** Oracle 10g 버전 부터는 이 에러를 줄이기 위해 undo retention 을 자동으로 관리하는 기능이 제공된다.
4) Temporary tablespace
- 임시자료를 저장하는 tablespace 로 DB 가 재시작되면 들어있던 내용은 모두 사라진다.
- 정렬작업 등을 수행할 때 PGA 공간이 부족하면 이곳을 이용하므로, 정렬작업이 크게 일어나는 작업 전에 임시 tablespace 를 크게 만들어주고 작업하는 것이 성능향상에 도움이 된다.
- Temporary tablespace 관리
## 기존 temporary tablespace 조회
SQL> select file_id, tablespace_name, bytes/1024/1024 MB, file_name from dba_temp_files;
FILE_ID TABLESPACE MB FILE_NAME
---------- ---------- ---------- --------------------------------------------------
1 TEMP 29 /app/oracle/oradata/testdb/temp01.dbf
## 신규 temporary tablespace 생성
SQL> select file_id, tablespace_name, bytes/1024/1024 MB, file_name from dba_temp_files;
FILE_ID TABLESPACE MB FILE_NAME
---------- ---------- ---------- --------------------------------------------------
1 TEMP 29 /app/oracle/oradata/testdb/temp01.dbf
SQL>
SQL> create temporary tablespace temp2 tempfile '/app/oracle/oradata/testdb/temp02.dbf' size 10M;
Tablespace created.
SQL> select file_id, tablespace_name, bytes/1024/1024 MB, file_name from dba_temp_files;
FILE_ID TABLESPACE MB FILE_NAME
---------- ---------- ---------- --------------------------------------------------
1 TEMP 29 /app/oracle/oradata/testdb/temp01.dbf
2 TEMP2 10 /app/oracle/oradata/testdb/temp02.dbf
## Default temporary tablespace 변경하기
SQL> select * from database_properties where property_name like 'DEFAULT%';
PROPERTY_NAME PROPERTY_V DESCRIPTION
------------------------------ ---------- --------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
DEFAULT_EDITION ORA$BASE Name of the database default edition
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
SQL> alter database default temporary tablespace temp2;
Database altered.
SQL> select * from database_properties where property_name like 'DEFAULT%';
PROPERTY_NAME PROPERTY_V DESCRIPTION
------------------------------ ---------- --------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP2 Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
DEFAULT_EDITION ORA$BASE Name of the database default edition
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
## Temporary tablespace 크기 변경하기
SQL> alter database tempfile '/app/oracle/oradata/testdb/temp02.dbf' resize 100M;
Database altered.
## Temporary tablespace 삭제하기
SQL> drop tablespace temp;
Tablespace dropped.
SQL> select file_id, tablespace_name, bytes/1024/1024 MB, file_name from dba_temp_files;
FILE_ID TABLESPACE MB FILE_NAME
---------- ---------- ---------- --------------------------------------------------
2 TEMP2 100 /app/oracle/oradata/testdb/temp02.dbf
- Temporary tablespace group 개념 (11g 부터 새로나온 기능)
Oracle DB 접속 시 여러명의 사용자가 동시에 같은 계정을 (예, scott) 사용해서 접속 할 수가 있는데, 이렇게 동시에 접속해서 order by 같은 정렬작업을 한꺼번에 한다고 하면 PGA 가 부족할 경우 scott schema 의 temporary tablespace 를 사용할 것이다.
이럴 경우 temporary tablespace 가 하나밖에 없을 경우 작업 속도는 늦어 질 수 있다.
11g 에서는 이 부분을 해결하기 위해 temporary tablespace group 이라는 개념을 도입했다.
temporary tablespace 를 여러 개 만든 후 group 으로 묶고 특정 schema 에게 이 group 을 지정해 주면 하나의 schema 로 여러명이 사용한다 하더라도 성능향상에 도움이 될 수있다.
- Temporary tablespace group 사용 방법
## Temporary tablespace group 생성
SQL> create temporary tablespace temp_scott1 tempfile '/app/oracle/oradata/testdb/temp_scott01.dbf' size 10M tablespace group temp_scott_group;
Tablespace created.
SQL> select file_id, tablespace_name, bytes/1024/1024 MB, file_name from dba_temp_files;
FILE_ID TABLESPACE_NAME MB FILE_NAME
---------- -------------------- ---------- --------------------------------------------------
1 TEMP_SCOTT1 10 /app/oracle/oradata/testdb/temp_scott01.dbf
2 TEMP2 100 /app/oracle/oradata/testdb/temp02.dbf
## 이미 존재하는 temporary tablespace 를 group 에 추가 (TEMP_SCOTT2 를 하나 더 만들자)
SQL> create temporary tablespace temp_scott2 tempfile '/app/oracle/oradata/testdb/temp_scott02.dbf' size 10M;
Tablespace created.
SQL> select file_id, tablespace_name, bytes/1024/1024 MB, file_name from dba_temp_files;
FILE_ID TABLESPACE_NAME MB FILE_NAME
---------- -------------------- ---------- --------------------------------------------------
1 TEMP_SCOTT1 10 /app/oracle/oradata/testdb/temp_scott01.dbf
2 TEMP2 100 /app/oracle/oradata/testdb/temp02.dbf
3 TEMP_SCOTT2 10 /app/oracle/oradata/testdb/temp_scott02.dbf
SQL> alter tablespace temp_scott2 tablespace group temp_scott_group;
Tablespace altered.
## group 조회
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ --------------------
TEMP_SCOTT_GROUP TEMP_SCOTT1
TEMP_SCOTT_GROUP TEMP_SCOTT2
## 사용자 할당 (scott 에게 할당한다고 하자)
SQL> alter user scott temporary tablespace temp_scott_group;
User altered.
## default temporary tablespace 로 지정하기
SQL> alter database default temporary tablespace temp_scott_group;
Database altered.
## group 에서 탈퇴하기
SQL> alter tablespace temp_scott2 tablespace group '';
Tablespace altered.
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ --------------------
TEMP_SCOTT_GROUP TEMP_SCOTT1
5) 일반 tablespace
- 관리자가 필요에 의해 만드는 tablespace 이다. 가장 일반적이다.
- 일반 tablespace 관리
## 생성
SQL> create tablespace haksa datafile '/app/oracle/oradata/testdb/haksa01.dbf' size 1M;
Tablespace created.
## 조회
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;
TABLESPACE_NAME MB FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS 5 /app/oracle/oradata/testdb/users01.dbf
UNDOTBS1 95 /app/oracle/oradata/testdb/undotbs01.dbf
SYSAUX 510 /app/oracle/oradata/testdb/sysaux01.dbf
SYSTEM 700 /app/oracle/oradata/testdb/system01.dbf
EXAMPLE 345.625 /app/oracle/oradata/testdb/example01.dbf
UNDO01 10 /app/oracle/oradata/testdb/undo01.dbf
HAKSA 1 /app/oracle/oradata/testdb/haksa01.dbf
7 rows selected.
## 각 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_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;
FILE# TS_NAME MB TOTAL_BLOCKS USED_BLOCKS PCT_USED
----- ---------- ---------- ------------ ----------- ----------
1 SYSTEM 700 89600 89232 99.59%
2 SYSAUX 510 65280 61792 94.66%
3 UNDOTBS1 95 12160 2480 20.39%
4 USERS 5 640 392 61.25%
5 EXAMPLE 345.625 44240 39568 89.44%
6 UNDO01 10 1280 488 38.13%
7 HAKSA 1 128 00.00%
7 rows selected.
- Tablespace 용량 관리하기
haksa tablespace 에 iphak table 을 만들고 일부로 가득차게 해서 장애를 만들어 본 후, 아래의 3가지 방법으로 장애 처리를 해보겠다.
## haksa tablespace 조회
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;
TABLESPACE MB FILE_NAME
---------- ---------- --------------------------------------------------
USERS 5 /app/oracle/oradata/testdb/users01.dbf
UNDOTBS1 95 /app/oracle/oradata/testdb/undotbs01.dbf
SYSAUX 510 /app/oracle/oradata/testdb/sysaux01.dbf
SYSTEM 700 /app/oracle/oradata/testdb/system01.dbf
EXAMPLE 345.625 /app/oracle/oradata/testdb/example01.dbf
UNDO01 10 /app/oracle/oradata/testdb/undo01.dbf
HAKSA 1 /app/oracle/oradata/testdb/haksa01.dbf
## iphak table 생성
SQL> create table scott.iphak (studno number) tablespace haksa;
Table created.
## 장애 발생
SQL> !vi ts_insert.sql
begin
for i in 1..5000000 loop
insert into scott.iphak values (i);
end loop;
commit;
end;
/
그리고 나서 일반 vi 처럼 :wq! 저장.
SQL> @ts_insert
begin
*
ERROR at line 1:dy
ORA-01653: unable to extend table SCOTT.IPHAK by 8 in tablespace HAKSA
ORA-06512: at line 3
==> 설정되어 있는 tablespace 용량이 너무 작을 떄 나오는 error 이다. 아래와 같은 3가지 방법으로 복구 가능한다.
## 복구 방법 1 - 수동으로 tablespace 에 data file 추가 하는 방법
SQL> alter tablespace haksa add datafile '/app/oracle/oradata/testdb/haksa02.dbf' size 20M;
Tablespace altered.
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;
TABLESPACE MB FILE_NAME
---------- ---------- --------------------------------------------------
USERS 5 /app/oracle/oradata/testdb/users01.dbf
UNDOTBS1 95 /app/oracle/oradata/testdb/undotbs01.dbf
SYSAUX 510 /app/oracle/oradata/testdb/sysaux01.dbf
SYSTEM 700 /app/oracle/oradata/testdb/system01.dbf
EXAMPLE 345.625 /app/oracle/oradata/testdb/example01.dbf
UNDO01 10 /app/oracle/oradata/testdb/undo01.dbf
HAKSA 1 /app/oracle/oradata/testdb/haksa01.dbf
HAKSA 20 /app/oracle/oradata/testdb/haksa02.dbf
8 rows selected.
## 복구 방법 2 - Data file 크기 수동 증가시키기
SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' resize 20M;
Database altered.
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;
TABLESPACE MB FILE_NAME
---------- ---------- --------------------------------------------------
USERS 5 /app/oracle/oradata/testdb/users01.dbf
UNDOTBS1 95 /app/oracle/oradata/testdb/undotbs01.dbf
SYSAUX 510 /app/oracle/oradata/testdb/sysaux01.dbf
SYSTEM 700 /app/oracle/oradata/testdb/system01.dbf
EXAMPLE 345.625 /app/oracle/oradata/testdb/example01.dbf
UNDO01 10 /app/oracle/oradata/testdb/undo01.dbf
HAKSA 20 /app/oracle/oradata/testdb/haksa01.dbf
HAKSA 20 /app/oracle/oradata/testdb/haksa02.dbf
8 rows selected.
## 복구 방법 3 - Data file 크기 자동 증가시키기
SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' autoextend on;
SQL> select tablespace_name, bytes/1024/1024 MB, file_name, autoextensible "Auto", online_status from dba_data_files;
TABLESPACE MB FILE_NAME Aut ONLINE_
---------- ---------- -------------------------------------------------- --- -------
USERS 5 /app/oracle/oradata/testdb/users01.dbf YES ONLINE
UNDOTBS1 95 /app/oracle/oradata/testdb/undotbs01.dbf YES ONLINE
SYSAUX 510 /app/oracle/oradata/testdb/sysaux01.dbf YES ONLINE
SYSTEM 700 /app/oracle/oradata/testdb/system01.dbf YES SYSTEM
EXAMPLE 345.625 /app/oracle/oradata/testdb/example01.dbf YES ONLINE
UNDO01 10 /app/oracle/oradata/testdb/undo01.dbf YES ONLINE
HAKSA 20 /app/oracle/oradata/testdb/haksa01.dbf YES ONLINE
HAKSA 20 /app/oracle/oradata/testdb/haksa02.dbf NO ONLINE
8 rows selected.
SQL> alter database datafile '/app/oracle/oradata/testdb/haksa02.dbf' autoextend on;
Database altered.
SQL> select tablespace_name, bytes/1024/1024 MB, file_name, autoextensible "Auto", online_status from dba_data_files;
TABLESPACE MB FILE_NAME Aut ONLINE_
---------- ---------- -------------------------------------------------- --- -------
USERS 5 /app/oracle/oradata/testdb/users01.dbf YES ONLINE
UNDOTBS1 95 /app/oracle/oradata/testdb/undotbs01.dbf YES ONLINE
SYSAUX 510 /app/oracle/oradata/testdb/sysaux01.dbf YES ONLINE
SYSTEM 700 /app/oracle/oradata/testdb/system01.dbf YES SYSTEM
EXAMPLE 345.625 /app/oracle/oradata/testdb/example01.dbf YES ONLINE
UNDO01 10 /app/oracle/oradata/testdb/undo01.dbf YES ONLINE
HAKSA 20 /app/oracle/oradata/testdb/haksa01.dbf YES ONLINE
HAKSA 20 /app/oracle/oradata/testdb/haksa02.dbf YES ONLINE
8 rows selected.
## autoextend 되는지 테스트 하기 (loop insert DML 을 다시 수행해본다.)
SQL> @ts_insert
PL/SQL procedure successfully completed.
SQL> select tablespace_name, bytes/1024/1024 MB, file_name, autoextensible "Auto", online_status from dba_data_files;
TABLESPACE MB FILE_NAME Aut ONLINE_
---------- ---------- -------------------------------------------------- --- -------
USERS 5 /app/oracle/oradata/testdb/users01.dbf YES ONLINE
UNDOTBS1 95 /app/oracle/oradata/testdb/undotbs01.dbf YES ONLINE
SYSAUX 510 /app/oracle/oradata/testdb/sysaux01.dbf YES ONLINE
SYSTEM 700 /app/oracle/oradata/testdb/system01.dbf YES SYSTEM
EXAMPLE 345.625 /app/oracle/oradata/testdb/example01.dbf YES ONLINE
UNDO01 349.5 /app/oracle/oradata/testdb/undo01.dbf YES ONLINE
HAKSA 34.1875 /app/oracle/oradata/testdb/haksa01.dbf YES ONLINE
HAKSA 33.1875 /app/oracle/oradata/testdb/haksa02.dbf YES ONLINE
8 rows selected.
==> haksa01, 02 data file 의 크기가 20M 에서 34 M 정도로 자동으로 증가한 것이 확인 될 것이다.
- Tablespace Offline Mode
Tablespace offline 한다는 것은 해당 tablespace 만 shutdown 한다는 의미이다.
Tablespace offline 에는 3가지 mode 가 있다.
** Normal Mode : tablespace 에 아무런 문제가 없을 때 하는 정상적인 방법이다.
** Temporary Mode : 현재 Offline 시키고자 하는 tablespace 의 data file 이 하나라도 이상이 생기게 되면 쓰는 방법이다.
** Immediate Mode : archive log mode 일 경우에만 사용 가능하다. 이 옵션은 Data file 에 장애가 나서 데이터를 내려 쓰지 못하는 상황에서 쓴다.
- Tablespace offline 방법
## Tablespace 전체를 offline 할 때
SQL> alter tablespace haksa offline;
Tablespace altered.
## Tablespace 상태 조회
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/undo01.dbf ONLINE
7 /app/oracle/oradata/testdb/haksa01.dbf OFFLINE
8 /app/oracle/oradata/testdb/haksa02.dbf OFFLINE
8 rows selected.
## Tablespace 전체 online
SQL> alter tablespace haksa online;
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 ONLINE
6 /app/oracle/oradata/testdb/undo01.dbf ONLINE
7 /app/oracle/oradata/testdb/haksa01.dbf ONLINE
8 /app/oracle/oradata/testdb/haksa02.dbf ONLINE
8 rows selected.
## 특정 data file 만 offline 시키기
SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' offline;
alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
===> 이 명령어는 DB 가 archive log mode 일 때만 가능하다. no archive log mode 라면 drop을 적어주어야 한다.
SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.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/undo01.dbf ONLINE
7 /app/oracle/oradata/testdb/haksa01.dbf RECOVER
8 /app/oracle/oradata/testdb/haksa02.dbf ONLINE
8 rows selected.
==> 7번 째 파일을 보면 RECOVER 상태가 되었다. no archive log mode 에서 data file 을 강제로 offline 시키면 저렇게 복구가 필요한 상태가 된다.
## Tablespace offline 과 checkpoint 관계
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/undo01.dbf ONLINE
7 /app/oracle/oradata/testdb/haksa01.dbf RECOVER
8 /app/oracle/oradata/testdb/haksa02.dbf ONLINE
8 rows selected.
SQL> select a.file#, a.ts#, b.name, a.status, a.checkpoint_change# from v$datafile a, v$tablespace b where a.ts#=b.ts#;
FILE# TS# NAME STATUS CHECKPOINT_CHANGE#
----- ---------- -------------------------------------------------- ------- ------------------
1 0 SYSTEM SYSTEM 907205
2 1 SYSAUX ONLINE 907205
3 2 UNDOTBS1 ONLINE 907205
4 4 USERS ONLINE 907205
5 6 EXAMPLE OFFLINE 907926
6 7 UNDO01 ONLINE 907205
7 12 HAKSA RECOVER 907734
8 12 HAKSA ONLINE 907734
8 rows selected.
==> 위의 내용을 보면, EXAMPLE 과 HAKSA 만 CHECKPOINT_CHANGE# 다르나. 이 상태에서 example 을 online 시켜 보겠다.
SQL> alter tablespace example online;
Tablespace altered.
SQL> select a.file#, a.ts#, b.name, a.status, a.checkpoint_change# from v$datafile a, v$tablespace b where a.ts#=b.ts#;
FILE# TS# NAME STATUS CHECKPOINT_CHANGE#
----- ---------- -------------------------------------------------- ------- ------------------
1 0 SYSTEM SYSTEM 907205
2 1 SYSAUX ONLINE 907205
3 2 UNDOTBS1 ONLINE 907205
4 4 USERS ONLINE 907205
5 6 EXAMPLE ONLINE 907926
6 7 UNDO01 ONLINE 907205
7 12 HAKSA RECOVER 907734
8 12 HAKSA ONLINE 907734
8 rows selected.
==> EXAMPLE 을 online 시켰음에도 불구하고 여전히 다르다. 이럴 경우엔 alter system checkpoint 로 수동으로 data file 동기화를 시켜야 한다.
SQL> alter system checkpoint;
System altered.
SQL> select a.file#, a.ts#, b.name, a.status, a.checkpoint_change# from v$datafile a, v$tablespace b where a.ts#=b.ts#;
FILE# TS# NAME STATUS CHECKPOINT_CHANGE#
----- ---------- -------------------------------------------------- ------- ------------------
1 0 SYSTEM SYSTEM 908218
2 1 SYSAUX ONLINE 908218
3 2 UNDOTBS1 ONLINE 908218
4 4 USERS ONLINE 908218
5 6 EXAMPLE ONLINE 908218
6 7 UNDO01 ONLINE 908218
7 12 HAKSA RECOVER 907734
8 12 HAKSA ONLINE 908218
8 rows selected.
==> 이제 example 의 CHECKPOINT_CHANGE# 도 동일하다.
## HAKSA tablespace 을 offline 시켜 보자. (현재 data file 하나만 내려가 있는 상태이다.)
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
ORA-01110: data file 7: '/app/oracle/oradata/testdb/haksa01.dbf'
==> 앞의 tablespace offline mode 에서 보았듯이, 하나의 data file 에 문제가 있는 경우, 정상적인 offline 방법으로는 내려가지 않는다. 이런 경우 temporary mode 를 사용하여야 한다.
SQL> alter tablespace haksa offline temporary;
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 ONLINE
6 /app/oracle/oradata/testdb/undo01.dbf ONLINE
7 /app/oracle/oradata/testdb/haksa01.dbf RECOVER
8 /app/oracle/oradata/testdb/haksa02.dbf OFFLINE
8 rows selected.
SQL> recover tablespace haksa;
Media recovery complete.
SQL>
SQL> alter tablespace haksa online;
Tablespace altered.
SQL> select a.file#, a.ts#, b.name, a.status, a.checkpoint_change# from v$datafile a, v$tablespace b where a.ts#=b.ts#;
FILE# TS# NAME STATUS CHECKPOINT_CHANGE#
----- ---------- -------------------------------------------------- ------- ------------------
1 0 SYSTEM SYSTEM 908218
2 1 SYSAUX ONLINE 908218
3 2 UNDOTBS1 ONLINE 908218
4 4 USERS ONLINE 908218
5 6 EXAMPLE ONLINE 908218
6 7 UNDO01 ONLINE 908218
7 12 HAKSA ONLINE 908369
8 12 HAKSA ONLINE 908369
SQL> alter system checkpoint;
System altered.
SQL> select a.file#, a.ts#, b.name, a.status, a.checkpoint_change# from v$datafile a, v$tablespace b where a.ts#=b.ts#;
FILE# TS# NAME STATUS CHECKPOINT_CHANGE#
----- ---------- -------------------------------------------------- ------- ------------------
1 0 SYSTEM SYSTEM 908393
2 1 SYSAUX ONLINE 908393
3 2 UNDOTBS1 ONLINE 908393
4 4 USERS ONLINE 908393
5 6 EXAMPLE ONLINE 908393
6 7 UNDO01 ONLINE 908393
7 12 HAKSA ONLINE 908393
8 12 HAKSA ONLINE 908393
8 rows selected.
- Data file 이동시키는 작업
** Offline 이 되는 tablespace 의 data file 이동 순서
1, 해당 Tablespace offline
2. Data file 을 대상 위치로 복사
3. Control file 내의 해당 Data file 위치 변경
4. 해당 Tablespace online
** Offline 이 안되는 tablespace 의 data file 이동 순서
1. DB 종료 (shutdown)
2. DB 를 mount 상태로 startup
3. Data file 복사
4. Control file 의 내용 변경
5. DB open
- 연습 1. HAKSA Tablespace 의 Data file 을 /app/oracle/disk1/haksa01.dbf, /app/oracle/disk2/haksa02.dbf 로 이동해 보자
## 1. 해당 Tablespace offline
SQL> alter tablespace haksa 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 ONLINE
6 /app/oracle/oradata/testdb/undo01.dbf ONLINE
7 /app/oracle/oradata/testdb/haksa01.dbf OFFLINE
8 /app/oracle/oradata/testdb/haksa02.dbf OFFLINE
8 rows selected.
## 2. Data file 을 대상 위치로 복사
SQL> !ls /app/oracle/disk1
ls: /app/oracle/disk1: No such file or directory
SQL> !mkdir /app/oracle/disk1
SQL> !ls /app/oracle/disk2
ls: /app/oracle/disk2: No such file or directory
SQL> !mkdir /app/oracle/disk2
SQL> !cp /app/oracle/oradata/testdb/haksa01.dbf /app/oracle/disk1/
SQL> !cp /app/oracle/oradata/testdb/haksa02.dbf /app/oracle/disk2/
## 3. Control file 내의 해당 Data file 위치 변경
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/undo01.dbf ONLINE
7 /app/oracle/oradata/testdb/haksa01.dbf OFFLINE
8 /app/oracle/oradata/testdb/haksa02.dbf OFFLINE
8 rows selected.
SQL> alter tablespace haksa rename datafile '/app/oracle/oradata/testdb/haksa01.dbf' to '/app/oracle/disk1/haksa01.dbf';
Tablespace altered.
SQL> alter tablespace haksa rename datafile '/app/oracle/oradata/testdb/haksa02.dbf' to '/app/oracle/disk2/haksa02.dbf';
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 ONLINE
6 /app/oracle/oradata/testdb/undo01.dbf ONLINE
7 /app/oracle/disk1/haksa01.dbf OFFLINE
8 /app/oracle/disk2/haksa02.dbf OFFLINE
8 rows selected.
## 4. 해당 Tablespace online
SQL> alter tablespace haksa online;
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 ONLINE
6 /app/oracle/oradata/testdb/undo01.dbf ONLINE
7 /app/oracle/disk1/haksa01.dbf ONLINE
8 /app/oracle/disk2/haksa02.dbf ONLINE
8 rows selected.
- 연습 2, system01.dbf 파일을 /app/oracle/disk3/system01.dbf 로 이동시켜 보자
## 1. DB 종료
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
## 2. MOUNT mode 로 DB open
SQL> startup mount;
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1344616 bytes
Variable Size 276827032 bytes
Database Buffers 138412032 bytes
Redo Buffers 6086656 bytes
Database mounted.
SQL>
SQL> select status from v$instance;
STATUS
------------
MOUNTED
## 3. Data file 복사
SQL> !mkdir /app/oracle/disk3
SQL> !cp /app/oracle/oradata/testdb/system01.dbf /app/oracle/disk3/
## 4. Control file 의 내용변경
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/undo01.dbf ONLINE
7 /app/oracle/disk1/haksa01.dbf ONLINE
8 /app/oracle/disk2/haksa02.dbf ONLINE
8 rows selected.
SQL> alter database rename file '/app/oracle/oradata/testdb/system01.dbf' to '/app/oracle/disk3/system01.dbf';
Database altered.
SQL> select file#, name, status from v$datafile;
FILE# NAME STATUS
----- -------------------------------------------------- -------
1 /app/oracle/disk3/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/undo01.dbf ONLINE
7 /app/oracle/disk1/haksa01.dbf ONLINE
8 /app/oracle/disk2/haksa02.dbf ONLINE
8 rows selected.
## 5. DB open
SQL> alter database open;
Database altered.
- 연습 3. Redo log file 을 아래와 같이 만들어 보자. (group 1, 2, 3)
/app/oracle/disk4/redo01_a.log, redo02_a.log, redo03_a.log
/app/oracle/disk5/redo01_a.log, redo02_a.log, redo03_a.log
## 현재 Redo log 상태 확인
SQL> 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# order by 1, 2;
GROUP# MEMBER MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
1 /app/oracle/oradata/testdb/redo01.log 50 NO CURRENT
2 /app/oracle/oradata/testdb/redo02.log 50 NO INACTIVE
3 /app/oracle/oradata/testdb/redo03.log 50 NO INACTIVE
## DB 종료
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
## Mount mode 로 DB open
SQL> startup mount;
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1344616 bytes
Variable Size 276827032 bytes
Database Buffers 138412032 bytes
Redo Buffers 6086656 bytes
Database mounted.
## Data file 복사
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/redo02_a.log
SQL> !cp /app/oracle/oradata/testdb/redo03.log /app/oracle/disk4/redo03_a.log
## Control file 내용 변경
SQL> alter database rename file '/app/oracle/oradata/testdb/redo01.log' to '/app/oracle/disk4/redo01_a.log';
Database altered.
SQL> alter database rename file '/app/oracle/oradata/testdb/redo02.log' to '/app/oracle/disk4/redo02_a.log';
Database altered.
SQL> alter database rename file '/app/oracle/oradata/testdb/redo03.log' to '/app/oracle/disk4/redo03_a.log';
Database altered.
## member 추가
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------
/app/oracle/disk4/redo03_a.log
/app/oracle/disk4/redo02_a.log
/app/oracle/disk4/redo01_a.log
SQL> alter database add logfile member '/app/oracle/disk5/redo01_b.log' to group 1, '/app/oracle/disk5/redo02_b.log' to group 2, '/app/oracle/disk5/redo03_b.log' to group 3;
Database altered.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------
/app/oracle/disk4/redo03_a.log
/app/oracle/disk4/redo02_a.log
/app/oracle/disk4/redo01_a.log
/app/oracle/disk5/redo01_b.log
/app/oracle/disk5/redo02_b.log
/app/oracle/disk5/redo03_b.log
6 rows selected.
## DB Open
SQL> alter database open;
Database altered.
- 연습 4. Tablespace 삭제하기
SQL> drop tablespace haksa;
drop tablespace haksa
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
==> table 이 하나라도 있다면 삭제 불가능.
SQL> drop tablespace haksa including contents and datafiles;
Tablespace dropped.
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;
TABLESPACE MB FILE_NAME
---------- ---------- --------------------------------------------------
USERS 5 /app/oracle/oradata/testdb/users01.dbf
UNDOTBS1 95 /app/oracle/oradata/testdb/undotbs01.dbf
SYSAUX 520 /app/oracle/oradata/testdb/sysaux01.dbf
SYSTEM 700 /app/oracle/disk3/system01.dbf
EXAMPLE 345.625 /app/oracle/oradata/testdb/example01.dbf
UNDO01 349.5 /app/oracle/oradata/testdb/undo01.dbf
6 rows selected
'Oracle > Admin' 카테고리의 다른 글
| [Oracle Admin] Chap 9. Oracle 메모리 관리 기법들 (0) | 2013.09.17 |
|---|---|
| [Oracle Admin] Chap 8. Oracle 저장구조 (0) | 2013.09.14 |
| [Oracle Admin] Chap 6. Redo Log 관리하기 (0) | 2013.09.10 |
| [Oracle Admin] Chap 5. Control File 관리하기 (0) | 2013.09.09 |
| [Oracle Admin] Chap 4. Oracle 시작하기와 종료하기 (0) | 2013.09.08 |
