카테고리 없음
tablespace 생성 및 조회
새똥 마이쪙
2014. 10. 21. 18:49
1.개요
Oracle 이라는 프로그램은 대용량의 데이터를 저장하고 관리하는데에 사용하는 하나의 프로그램.
하지만 이러한 대용량의 데이터를 어떻게 저장하고 어떻게 관리하는가에 대한 건 아주 중요한 이슈중에 하나다.
Oracle은 데이터를 저장하고 불러오거나 작업등을 할때 논리적으로 Tablespace라는 공간을 만들어서 작업하고
물리적으로는 Disk에 Data File을 만들어서 작업을 한다.
SQL(손님)에서 데이터를 호출할려고 Tablespace(매장)에 요청하면 쭈욱 찾아보다가 메모리에 데이터가 존재하지 않는다면
Sever Process가 DataFile(창고)로 옮겨가서 다시 한번 호출된 데이터를 찾아본다.
만약 데이터가 있다면, 데이터가 있는 해당 블록을 Server Process가 Tablespace로 가져온후 사용자에게 데이터를 전달한다.
2. Tablespace의 종류 및 특징들
system tablespace
system tablespace에는 데이터에 관련된 딕셔너리들이 들어있다.
따라서 위의 system tablespace가 손상이되면 Oracle 서버가 작동하지 않는다.
한마디로 운용에 관련된 tablespace고,Oracle 9i는 system tablespace에 운용딕셔너리와, 튜닝 딕셔너리가 같이 묶여있다.
10G부터는 튜닝 관련 딕셔너리가 system tablespace에서 개별적으로 나와서 sysaux tablespace라는 새로운 tablespace가 생긴다.
9i의 딕셔너리 수
10g의 딕셔너리 수
11g의 딕셔너리 수
3.데이터 딕셔너리의 종류와 특징
데이터 딕셔너리는 보통 크게 두가지로 나뉘는데 1.base table과 2.data dictionary view로 나뉜다.
1) base table : 데이터베이스를 생성할때 (dbca,create database등) 생성된다.
원본데이터가 들어있는곳이고 dba조차 이곳에 접근할 수 없다.
2)data dictionary view : 위의곳을 접근할 수 없어 추가적으로 조회는 가능하게끔 나온게 데이터 딕셔너리 뷰이다.
dbca를 사용해서 데이터베이스를 생성 할 경우엔 자동으로 생성되지만 수동으로 생성할땐 생성이 안됨
그래서 create database를 수행 한 후에 꼭 catalog.sql 스크립트를 추가로 실행해 주어야 한다.
data dictionary view를 또 두개로 나눌수 있는데 1)Static dictionary와 2)Dynamic dictionary로 나뉜다.
Static dictionary는 딕셔너리에 저장 된 정보들이 자동으로 변동되지 않는다.
이렇게 생성되는 딕셔너리엔 아래와 같은 중요정보들이 들어있다.
1.데이터베이스의 논리적인 구조와 물리적인 구조 정보들
2.객체의 정의와 공간 사용 정보들
3.제약조건에 관련된 정보들
4.사용자에 관련된 정보들
5.role,privilege 등에 관련된 정보들
6.감사 및 보안등에 관련된 정보들
4.Analyze 명령어
딕셔너리의 내용을 수동으로 업데이트 하기 위한 방법으로는 analyze명령어가 있다.
이 명령어 말고 DBMS_STATS라는 패치지도 많이 사용한다만, 주의할 점은 위 두개의 명령어는 해당 테이블들을 전체 스캔하는 명령어이므로 서버의 성능에 막대한 영향을 주는 경우가 많다.
오라클에서 사용하는 딕셔너리는 너무나도 많아서 제일 많이 사용하는 4개의 딕셔너리만 나열해보겠다.
1)USER_XXX : 해당 사용자가 생성한 내용만 볼 수 있는 딕셔너리
EX) USER_TABLES :해당 사용자가 생성한 테이블만 볼 수 있는 딕셔너리
2)ALL_XXX : 해당 사용자가 생성한 것과 그렇지 않는 것도 접근이 가능한 내용을 열람할수 있는 딕셔너리
EX)ALL_TABLES : 접근 가능한 모든 테이블 내역을 볼 수 있는 딕셔너리
3)DBA_XXX : 데이터베이스 내에 있는 모든 내용을 다 볼 수 있는 딕셔너리
EX) DBA_TABLES : 데이터베이스에 있는 모든 TABLE을 내역을 볼 수 있는 딕셔너리
위의 살펴본 딕셔너리 3개 모두 Static dictionary이다.
5.일반 Tablespace 생성 및 조회하기
6.각 Datafile의 실제 사용량 확인하는 방법
Oracle이 64bit일 경우엔 32gb까지 가능하다.
9.각 data file들의 autoextend 유무 확인하기
Tablespace를 Off 한다는것은 사용자가 더 이상 해당 Tablespace에 접근하지 못한다는 의미가 된다.
사실상 Tablespace를 shutdown 시킨다는 의미고, Tablespace가 장애가 나서 복구를 해야할때 유용하게 사용된다.
Tablespace를 Off 방법은 총 3가지가 있다.
1)Tablespace Normal Mode : Tablespace가 문제 없을 시 정상적으로 수행하는 방법
no archive mode 기준 Offline 후에 online을 다시 수행하면 Recovery라는 메세지가 나온다.
어떤 Data filedl Offline인지 는 아래와 같이 v$datafile을 조회하면 알 수 있다.
no archive mode 확인후에 offline 수행을 실행해보겠다.
이제 오프라인을 수행하고 상태를 출력해보겠다.
그 파일들에는 새로운 정보가 저장되지 않는다. 그래서 반드시 offline후에 online하게 된다면
반드시 checkpoint를 발생시켜서 data file간의 동기화를 시켜주어야 한다.
아래 내용을 참고해서 확인해보자.
다름을 알 수 있다. 이 상태에서 online을 수행해도 결과는 같다.
위에서 설명했듯이 checkpoint를 강제로 일으켜 scn을 맞춰주고, 맞춘후엔 반드시 backup을 받아놓으면 된다.
SCN번호를 동기화 시켜야 된다는점 잊지 말길 바란다
Oracle 이라는 프로그램은 대용량의 데이터를 저장하고 관리하는데에 사용하는 하나의 프로그램.
하지만 이러한 대용량의 데이터를 어떻게 저장하고 어떻게 관리하는가에 대한 건 아주 중요한 이슈중에 하나다.
Oracle은 데이터를 저장하고 불러오거나 작업등을 할때 논리적으로 Tablespace라는 공간을 만들어서 작업하고
물리적으로는 Disk에 Data File을 만들어서 작업을 한다.
SQL(손님)에서 데이터를 호출할려고 Tablespace(매장)에 요청하면 쭈욱 찾아보다가 메모리에 데이터가 존재하지 않는다면
Sever Process가 DataFile(창고)로 옮겨가서 다시 한번 호출된 데이터를 찾아본다.
만약 데이터가 있다면, 데이터가 있는 해당 블록을 Server Process가 Tablespace로 가져온후 사용자에게 데이터를 전달한다.
2. Tablespace의 종류 및 특징들
system tablespace
system tablespace에는 데이터에 관련된 딕셔너리들이 들어있다.
따라서 위의 system tablespace가 손상이되면 Oracle 서버가 작동하지 않는다.
한마디로 운용에 관련된 tablespace고,Oracle 9i는 system tablespace에 운용딕셔너리와, 튜닝 딕셔너리가 같이 묶여있다.
10G부터는 튜닝 관련 딕셔너리가 system tablespace에서 개별적으로 나와서 sysaux tablespace라는 새로운 tablespace가 생긴다.
9i의 딕셔너리 수
SQL>select count(*) from dictionary; ----------------- count(*) ----------------- 1284 //9.2.0.4버전 기준
10g의 딕셔너리 수
SQL>select count(*) from dictionary; ----------------- count(*) ----------------- 1882 //10.2.0.5버전 기준
11g의 딕셔너리 수
SQL>select count(*) from dictionary; ----------------- count(*) ----------------- 2600 //11.2.0.2버전 기준
3.데이터 딕셔너리의 종류와 특징
데이터 딕셔너리는 보통 크게 두가지로 나뉘는데 1.base table과 2.data dictionary view로 나뉜다.
1) base table : 데이터베이스를 생성할때 (dbca,create database등) 생성된다.
원본데이터가 들어있는곳이고 dba조차 이곳에 접근할 수 없다.
2)data dictionary view : 위의곳을 접근할 수 없어 추가적으로 조회는 가능하게끔 나온게 데이터 딕셔너리 뷰이다.
dbca를 사용해서 데이터베이스를 생성 할 경우엔 자동으로 생성되지만 수동으로 생성할땐 생성이 안됨
그래서 create database를 수행 한 후에 꼭 catalog.sql 스크립트를 추가로 실행해 주어야 한다.
data dictionary view를 또 두개로 나눌수 있는데 1)Static dictionary와 2)Dynamic dictionary로 나뉜다.
Static dictionary는 딕셔너리에 저장 된 정보들이 자동으로 변동되지 않는다.
이렇게 생성되는 딕셔너리엔 아래와 같은 중요정보들이 들어있다.
1.데이터베이스의 논리적인 구조와 물리적인 구조 정보들
2.객체의 정의와 공간 사용 정보들
3.제약조건에 관련된 정보들
4.사용자에 관련된 정보들
5.role,privilege 등에 관련된 정보들
6.감사 및 보안등에 관련된 정보들
4.Analyze 명령어
딕셔너리의 내용을 수동으로 업데이트 하기 위한 방법으로는 analyze명령어가 있다.
이 명령어 말고 DBMS_STATS라는 패치지도 많이 사용한다만, 주의할 점은 위 두개의 명령어는 해당 테이블들을 전체 스캔하는 명령어이므로 서버의 성능에 막대한 영향을 주는 경우가 많다.
오라클에서 사용하는 딕셔너리는 너무나도 많아서 제일 많이 사용하는 4개의 딕셔너리만 나열해보겠다.
1)USER_XXX : 해당 사용자가 생성한 내용만 볼 수 있는 딕셔너리
EX) USER_TABLES :해당 사용자가 생성한 테이블만 볼 수 있는 딕셔너리
2)ALL_XXX : 해당 사용자가 생성한 것과 그렇지 않는 것도 접근이 가능한 내용을 열람할수 있는 딕셔너리
EX)ALL_TABLES : 접근 가능한 모든 테이블 내역을 볼 수 있는 딕셔너리
3)DBA_XXX : 데이터베이스 내에 있는 모든 내용을 다 볼 수 있는 딕셔너리
EX) DBA_TABLES : 데이터베이스에 있는 모든 TABLE을 내역을 볼 수 있는 딕셔너리
위의 살펴본 딕셔너리 3개 모두 Static dictionary이다.
5.일반 Tablespace 생성 및 조회하기
SQL>create tablespace youngkyu
datafile '/app/oracle/oradata/testdb/youngkyu01.dbf' size 1m;
Tablespace crerated.
SQL>select tablespace_name,status,contents,extent_management,
segment_space_management
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
EXAMPLE 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
------------------------------ ---------- --------------------------------------------------
USERS 10 /app/oracle/oradata/testdb/users01.dbf
UNDOTBS1 10 /app/oracle/oradata/testdb/undotbs01.dbf
SYSAUX 600 /app/oracle/oradata/testdb/sysaux01.dbf
SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf
EXAMPLE 345.625 /app/oracle/oradata/testdb/example01.dbf
6 row selected.
6.각 Datafile의 실제 사용량 확인하는 방법
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 ;
FILE# TS_NAME MB TOTAL_BLOCKS USED_BLOCKS PCT_USED
---------- ------------------------------ ---------- ------------ ----------- --------
1 SYSTEM 710 90880 90712 99.82 %
2 SYSAUX 600 76800 70680 92.03 %
3 UNDOTBS1 10 1280 1104 86.25 %
4 USERS 10 1280 768 60.00 %
5 EXAMPLE 345.625 44240 39560 89.42 %
7row selected.
7.datafile 크기 수동 증가시키기
SYS>alter database datafile '/app/oracle/oradata/testdb/youngkyu01.dbf' resize 20m; Database altered.8.datafile 크기 자동 증가시키기
SYS>alter database datafile '/app/oracle/oradata/testdb/youngkyu01.dbf' autoextend on; Database alterd.
Oracle이 64bit일 경우엔 32gb까지 가능하다.
9.각 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 AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- USERS 7.5 /app/oracle/oradata/testdb/users01.dbf YES ONLINE UNDOTBS1 95 /app/oracle/oradata/testdb/undotbs01.dbf YES ONLINE SYSAUX 530 /app/oracle/oradata/testdb/sysaux01.dbf YES ONLINE SYSTEM 700 /app/oracle/oradata/testdb/system01.dbf YES SYSTEM EXAMPLE 313.125 /app/oracle/oradata/testdb/example01.dbf YES ONLINE10.Tablespace Offline
Tablespace를 Off 한다는것은 사용자가 더 이상 해당 Tablespace에 접근하지 못한다는 의미가 된다.
사실상 Tablespace를 shutdown 시킨다는 의미고, Tablespace가 장애가 나서 복구를 해야할때 유용하게 사용된다.
Tablespace를 Off 방법은 총 3가지가 있다.
1)Tablespace Normal Mode : Tablespace가 문제 없을 시 정상적으로 수행하는 방법
SQL>alter tablespace youngkyu offline; Database altered.2)Tablespace Temporary Mode : offline 시키고자 하는 Tablespace의 Data file이 하나라도 이상이 생겨 offline normal mode를 수행하지 못할때
SQL>alter tablespace datafile '/app/oracle/oradata/testdb/youngkyu01.dbf' offline; //이 모드는 DB가 archive mode일때만 가능//만약에 no archive mode라면
SQL>alter tablespace datafile '/app/oracle/oradata/testdb/youngkyu01.dbf' offline drop;11. Tablespace no archive mode 후에 Online을 수행할 경우
no archive mode 기준 Offline 후에 online을 다시 수행하면 Recovery라는 메세지가 나온다.
어떤 Data filedl Offline인지 는 아래와 같이 v$datafile을 조회하면 알 수 있다.
SQL> col name for a50
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/youngkyu01.dbf ONLINE
6 rows selected.
위의 내용을 보면 전부 online인걸 확인 할 수 있다.no archive mode 확인후에 offline 수행을 실행해보겠다.
SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 6 Current log sequence 8 SQL> //현제 No Archive Mode//위의 결과는 no archive mode로 확인되었다.
이제 오프라인을 수행하고 상태를 출력해보겠다.
SQL> alter database datafile '/app/oracle/oradata/testdb/youngkyu01.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/youngkyu01.dbf RECOVER
6 rows selected.
위와 같이 recovery모드로 변경이 되어있는데 Tablespace를 offline하게 되면그 파일들에는 새로운 정보가 저장되지 않는다. 그래서 반드시 offline후에 online하게 된다면
반드시 checkpoint를 발생시켜서 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#;
FILE# TS# NAME STATUS CHECKPOINT_CHANGE#
---------- ---------- ------------ ------- ------------------
1 0 SYSTEM SYSTEM 926358
2 1 SYSAUX ONLINE 926358
3 2 UNDOTBS1 ONLINE 926358
4 4 USERS ONLINE 926358
5 6 EXAMPLE OFFLINE 941046
6 7 YOUNGKYU RECOVER 940157
6 rows selected.
위 내용을 보면 example tablespace가 offline 상태여서 checkpoint_change(checkpoint scn)이 다른 파일들과다름을 알 수 있다. 이 상태에서 online을 수행해도 결과는 같다.
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 926358
2 1 SYSAUX ONLINE 926358
3 2 UNDOTBS1 ONLINE 926358
4 4 USERS ONLINE 926358
5 6 EXAMPLE ONLINE 941102
6 7 YOUNGKYU RECOVER 940157
6 rows selected.
scn이 다른 이런상태에서 datafile backup을 받는다면 당장 문제가 없더라도 후에 복구나 여러 기타 장애가 일어날 수 있다.위에서 설명했듯이 checkpoint를 강제로 일으켜 scn을 맞춰주고, 맞춘후엔 반드시 backup을 받아놓으면 된다.
SQL> alter system checkpoint; //checkpoint 발생
System altered.
SQL> alter tablespace youngkyu offline; //youngkyu tablespace offline시켜보지만 이미 2개중 1개가 offline이라 normal mode안됨
alter tablespace youngkyu offline
*
ERROR at line 1:
ORA-01191: file 6 is already offline - cannot do a normal offline
ORA-01110: data file 6: '/app/oracle/oradata/testdb/youngkyu01.dbf'
SQL> alter tablespace youngkyu offline temporary;
Tablespace altered.
SQL> recover tablespace youngkyu;
Media recovery complete.
SQL>
SQL> alter tablespace youngkyu 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 941286
2 1 SYSAUX ONLINE 941286
3 2 UNDOTBS1 ONLINE 941286
4 4 USERS ONLINE 941286
5 6 EXAMPLE ONLINE 941286
6 7 YOUNGKYU ONLINE 941507 //복구가 완료 되었지만 scn번호가 다르다
6 rows selected.
SQL> alter system checkpoint; //다시 한번 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 941635
2 1 SYSAUX ONLINE 941635
3 2 UNDOTBS1 ONLINE 941635
4 4 USERS ONLINE 941635
5 6 EXAMPLE ONLINE 941635
6 7 YOUNGKYU ONLINE 941635 //모든 Datafile들의 번호가 같아졌다.
6 rows selected.
Tablespace를 offline후 online을 하게 되면 반드시 수동으로 checkpoint를 수행해서SCN번호를 동기화 시켜야 된다는점 잊지 말길 바란다