검색결과 리스트
tablespace에 해당되는 글 2건
- 2015.07.30 7.Tablespace와 Datafile 관리하기 - Undo Tablespace / 1
- 2015.07.16 7. Tablespace와 Data File 관리하기.
글
Tablespace와 Datafile 관리하기.
1.Undo Tablespace
- Undo Data : 사용자가 DML을 수행할 경우 발생하는 원본데이터.
- Undo Segment: Undo Data만 저장하는 Segment
- Undo Tablespace: Undo Segment를 저장하는 Tablespace
Undo Data만 저장 가능하며, Oracle Server process가 직접관리한다.
1-1) Undo Tablespace의 특징.
- Oracle server process는 이 테이블 스페이스에 Undo Segment를 생성하고 각 사용자별로 Undo Segment를 할당하여 undo data를 관리하며 사용자는 관여할 수 없다.
- Undo Tablespace는 Instance당 여러 개가 존재할 수 있으나, 사용되는것은 한번에 1개뿐.
- 관리방법으로는 자동 모드와 수동 모드가 있으며, 9i버젼부터 Automatic Undo Management(AUM)을 권장한다.
사용하기 위해서는 초기화 파라미터 파일에 Undo_management = auto로 설정하고 수동 mode 관리를 위해서는 manual로 설정하면된다.
- 자동모드가 기본이며, manual값은 주로 장애가 발생될 경우 복구용도로 사용된다. 변경 후 적용을 위해서는 Instance를 재기동 해야한다.
SYS@PROD>show parameter undo
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 |
1-2) Undo Tablespace의 사용목적.
- Transaction Rollback : 사용자가 Rollback 명령어를 사용하면, 이곳에 저장된 Undo Data를 사용하여 Rollback 수행.
- Read Consistency : CR 작업을 통해 트랜잭션이 끝나지 않은 데이터는 변경 전 데이터를 보여준다.
* CR 작업 : 1. 데이터를 DB Buffer Cache로 복사
2. Redo Log Buffer에 변경내용기록
3. Undo Segment에 기록
4. DB Buffer cache의 원본 변경
*읽기 일관성 실습
- 터미널 1에서 EMPNO 7369의 이름을 GOOD BOY로 변경
DB BUFFER CACHE에 이름이 변경되고, LOCK이 설정되어 터미널1에서 COMMIT 이나 ROLLBACK 수행 전까지는 터미널2에서 변경된 데이터를 볼 수 없음.
- 터미널 2에서 변경된 데이터에 대한 조회를 수행
사용자의 SERVER PROCESS는 UNDO에 있는 데이터를 DB BUFFER CACHE로 복사해서 사용자에게 제공
터미널 1 | 터미널2 | ||
SCOTT@PROD> select EMPNO, ENAME from EMP;
EMPNO ENAME ---------- ---------- 7369 HELLO 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER |
| ||
SCOTT@PROD> UPDATE EMP SET ENAME ='GOODBOY' WHERE EMPNO=7369;
|
| ||
SCOTT@PROD> select EMPNO, ENAME from EMP;
EMPNO ENAME ---------- ---------- 7369 GOODBOY 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER | SCOTT@PROD> select EMPNO, ENAME from EMP;
EMPNO ENAME ---------- ---------- 7369 HELLO 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER | ||
SCOTT@PROD> COMMIT; |
| ||
SCOTT@PROD> select EMPNO, ENAME from EMP;
EMPNO ENAME ---------- ---------- 7369 GOODBOY 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER | SCOTT@PROD> select EMPNO, ENAME from EMP;
EMPNO ENAME ---------- ---------- 7369 GOODBOY 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER |
- Transaction Recovery (Instance Recovery라고도함)
운영중인 DB서버가 비정상적으로 종료가 되었을 때 Roll Forward와 Roll back 작업을 수행해서 Dirty database를 Clean Database로 만들어주는 과정에서 Undo Data 사용
1-3) Undo Segment 할당되는 원리
- Undo Tablespace의 특징 중 하나는 Undo Data File의 크기가 증가만 되고 줄어들지는 않는다.
- Undo Tablespace 안에 4개의 Undo Segment 가 할당되어 있다.
- E라는 사용자가 신규접속하여 DML을 수행할 경우 E 사용자의 Server Process는 가장먼저 Undo Segment를 할당받게된다.
기존에 만들어져 있던 Segment 중 트랜잭션이 완료된것이 있는지를 우선적으로 확인 후, 그시점에 A가 Commit 을 수행하여 트랜잭션이 끝났다면, A가 사용했던 Undo Segement를 덮어쓰게된다.
- F 라는 사용자가 접속하여, DML을 수행하려하는데 Commit 이나 Rollback을 수행하지 않아서 트랜잭션이 완료된게 없는 경우 새로운 Undo Segment를 하나 더 생성해서 기록한다.
- Undo Segment가 Data file의 저장공간이 허용하는 범위까지 늘어나다가 Data File에 더 공간이 없게되면 하나의 Segment에 두개 세션이상의 Undo Data를 함께 기록하다가 그 조차도 공간이 없으면 해당 트랜잭션은 에러가나게됨.
* Undo Data가 늘어나도 늘어난 데이터파일의 크기를 줄일 수는 없다. Commit을 수행해도 Undo Segment안에 Undo Data는 지워지지 않고 남아있기 때문.
* Commit 수행 시 다른 서버 프로세스가 덮어 쓸 수 있게 해주는 것일 뿐 Undo Segment 안의 자료를 지우는것은 아니다.
* Undo Tablespace 크기가 비정상적으로 클 경우에는 관리자가 다른 작은 Undo Tablespace를 신규로 만들고, Undo Tablespace를 신규 Undo Tablespace로 변경시킨 후 기존 Undo Tablespace를 삭제해 주어야한다.
1-4) Undo tablespace 변경하기.
1. Undo 상태확인. SYS@PROD>show parameter undo;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 => 현재 인스턴스의 Undo tablespace 는 UNDOTBS1로 지정되어 있다. 2. 신규 Undo Tablespace 생성 SYS@PROD>create undo tablespace undo01 datafile '/u01/app/oracle/oradata/PROD/undo01.dbf' size 10M autoextend on; 3. 현재 Tablespace 목록 확인 TABLESPACE MB FILE_NAME AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM 400 /home/oracle/systemdest/system01.dbf NO SYSTEM SYSAUX 200 /u01/app/oracle/oradata/PROD/sysaux01.dbf NO ONLINE UNDOTBS1 200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO ONLINE USERS 50 /u01/app/oracle/oradata/PROD/users01.dbf NO ONLINE EXAMPLE 100 /u01/app/oracle/oradata/PROD/example01.dbf NO ONLINE UNDO01 10 /u01/app/oracle/oradata/PROD/undo01.dbf YES ONLINE
6 rows selected. => UNDO01 Tablespace가 생성됨. 4. 새로 생성한 Undo Tablespace로 변경
SYS@PROD>alter system set undo_tablespace=undo01; => Pfile인 경우 이 작업 이후 파라미터파일의 내용도 변경해야 DB 재시작 후 장애가 없음. (작업환경은 SPFILE 사용) ** 참고(value에 경로 없을경우 SPFILE 사용 경로 있을 경우 Pfile사용) SYS@PROD>show parameter pfile
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string 5. 변경된 Undo Tablespace 확인. SYS@PROD>show parameter undo
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDO01 |
1-5) Undo 사용세션 확인.
1) 확인스크립트
select s.sid, s.serial#, s.username, r.name "ROLLBACK SEG" from v$session s, v$transaction t, v$rollname r where s.taddr=t.addr and t.xidusn=r.usn; |
2) 실습
터미널1 (SYS 계정) | 터미널 2 (SCOTT 계정) |
SYS@PROD>@undo_ses
no rows selected |
|
| SCOTT@PROD>update emp set job='DBA' where empno=7369;
1 row updated. |
SYS@PROD>@undo_status SID SERIAL# USERNAME USED_UREC USED_UBLK ---------- ---------- ------------------------------ ---------- ---------- 34 93 SCOTT 1 1
|
|
| SCOTT@PROD>update emp set JOB='ADMIN' where empno=7499 ; |
SYS@PROD>@undo_status
SID SERIAL# USERNAME USED_UREC USED_UBLK ---------- ---------- ------------------------------ ---------- ---------- 34 93 SCOTT 2 1 |
|
| SCOTT@PROD> commit; |
SYS@PROD>@undo_ses
no rows selected |
|
* 추가 테스트 1. 다른 세션에서 SCOTT 계정으로 변경작업을 시도할 경우
SID SERIAL# USERNAME USED_UREC USED_UBLK ---------- ---------- ------------------------------ ---------- ---------- 45 60 SCOTT 1 1 34 93 SCOTT 3 1 32 103 SCOTT 1 1
|
* 추가 테스트 2. SID 34 세션에서 Commit 이나 Rollback을 수행하지 않고 종료할 경우
SID SERIAL# USERNAME USED_UREC USED_UBLK ---------- ---------- ------------------------------ ---------- ---------- 45 60 SCOTT 1 1 32 103 SCOTT 1 1
|
1-6) Undo 관련 주요 파라미터.
- UNDO_RETENTION(초단위)
COMMIT 수행 후에도 해당 UNDO SEGMENT 내의 데이터를 다른 서버 프로세스가 덮어 쓰지 못하도록 막아주는 시간.
그러나, Undo segment 여분이 있을 경우에만 적용되며, 만약 모든Undo Segment가 사용중일 경우에는 적용되지않는다.
* ORA-01555. Snapshot too old
사용자 A: 1시간정도 수행되는 특정 데이터의 집계작업을 수행중
사용자 B: 사용자A가 집계중인 데이터를 update 한 후 commit할 경우
사용자 A: 사용자 B가 변경전의 데이터를 Undo Segment에서 찾아서 집계하게됨.
만약 B가 Commit 한 후 사용자 C가 사용자 B의 Undo Segment를 덮어 썻다면 사용자 A는 집계쿼리를 완성할 수 없고. 이때 발생하는 에러가 Snap shot too old Error 이다.
- UNDO_RETENTION_GURANTEE
UNDO_RETENTION이란 파라미터는 UNDO SEGMENT양이 부족하면 설정된 시간이 무시되고 UNDO SEGMENT가 재사용되지만, UNDO_RETENTION_GURANTEE 파라미터를 설정하면, 설정된 시간동안 재활용을 하지않고 지켜준다.
* 이 기능을 사용할 경우 COMMIT을 수행해도 UNDO SEGMENT를 특정 시간동안 재사용 하지 않음으로써, UNDO TABLESPACE의 용량이 증가될 수 있기 때문에 기본값은 NOGURANTEE 이다.
SYS@PROD>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 |
'Oracle 참고서적 정리 > 오라클 관리실무' 카테고리의 다른 글
| 7. Tablespace와 Datafile 관리하기 - Data file 이동 / 테이블스페이스 삭제 (0) | 2015.07.27 |
|---|---|
| 7. Tablespace와 Data File 관리하기. (0) | 2015.07.16 |
| 6. Redo Log 관리하기 - SCN과 Checkpoint (0) | 2015.07.16 |
| 6-3. Redo Log 관리하기 - 실습 (0) | 2015.07.16 |
| 6-2. Redo Log File 구성 및 관리하기. (0) | 2015.06.18 |
글
Tablespace와 Data File 관리하기.
1. 개요
- 오라클은 데이터를 저장하고 작업할 때 메모리에 논리적으로는 Tablespace라는 공간을 만들어서 작업하며, 물리적으로는 Data File을 만들어서 저장한다.
- 사용자가 SQL을 수행하면 해당 데이터는 반드시 메모리에 있는 Tablespace에 존재해야 한다.
- 메모리에 존재하지 않을 경우 Server Process는 데이터가 저장되어있는 Data File에 가서 해당 데이터가 있는 Block을 찾아서 Tablespace로 가져온 후 사용자가 원하는 데이터를 전달한다.
- Oracle에서는 Database Buffer chache에 Tablespace를 생성하며 사용하는 메모리 공간이 클수록 일반적으로 수해속도가 빨라진다.
2. Tablespace의 종류 및 특징.
1) SYSTEM Tablespace.
- 데이터 딕셔너리들이 저장되어있음.
* 데이터 딕셔너리: 오라클 서버의 모든 정보를 저장하고있는 아주 중요한 테이블이나 뷰를말함.
- 데이터 딕셔너리는 Base Table과 Data Dictionary View로 나눌 수 있다.
- Base Table은 데이터베이스를 생성할 때 생성됨.
- Data Dictionary View는 dbca로 database 생성 시 자동생성 되지만, 수동 생성 경우 catalog.sql을 수행해주어야 한다.
[oracle@edydr1p0-PROD dbhome_1]$ cd $ORACLE_HOME [oracle@edydr1p0-PROD dbhome_1]$ find . -type f -name catalog.sql ./rdbms/admin/catalog.sql |
- Data dictionary에 있는 정보.
1. 데이터베이스의 논리적인 구조와 물리적인 구조 정보들.
2. 객체의 정의와 공간 활용 정보 등
3. 제약조건에 관련된 정보 등
4. 사용자에 관련된 정보 등
5. Role, Priviledge 등에 관련된 정보 등
6. 감사 및 보안에 관련된 정보 등
- Data Dictionary는 크게 Static Dictionary와 Dynamic Dictionary로 나눌 수 있다.
1. Static Dictionary
- 수동으로 업데이트를 해야 정보가 갱신된다.
- 인스턴스가 OPEN 되었을 경우 조회가능
* 딕셔너리 내용 업데이트 쿼리.
SQL> analyze table stest compute statistics;
* 딕셔너리 조회쿼리
select table_name, num_rows
from user_tables
where table_name='테이블명' ;
실습.
SQL> conn scott/tiger
Connected. - Scott 계정접속. SQL> create table stest (no number); - Tablespace
SQL> begin 2 for i in 1..100 loop 3 insert into stest values (i); 4 end loop; 5 commit; 6 end;
7 / - stest 테이블에 값 추가. SQL> select count(*) from stest 100 rows selected. SQL> select table_name, num_rows 2 from user_tables 3 where table_name='STEST';
TABLE_NAME NUM_ROWS ------------------------------ ---------- STEST
- NUM_ROWS 결과가 업데이트 되지 않았음을 확인. - STATIC Dictionary는 수동으로 업데이트해야함.
SQL> analyze table stest compute statistics;
Table analyzed.
SQL> select table_name, num_rows 2 from user_tables 3 where table_name='STEST';
TABLE_NAME NUM_ROWS ------------------------------ ----------
STEST 100 통계정보 업데이트 후 조회. NUM_ROWS 업데이트 된것을 확인할 수 있다. |
* 딕셔너리 정보를 업데이트 하기위한 방법으로, DBMS_STATS라는 패키지도 많이 사용한다.
** Analyze 명령어나 DBMS_STATS 패키지는 해당 테이블을 전체 스캔하는 명령어 이므로 서버의 성능에 아주 나쁜 영향을 줄 수있으므로, 함부로 사용해서는 안되는 명령어이다.
- Static 딕셔너리 분류.
USER_XXX | 해당 사용자가 생성한 내용만 볼 수 있는 딕셔너리. ex) USER_TABLE USER_INDEXES |
ALL_XXX | 해당 사용자가 생성한 것과 생성하지 않았더라도 접근이 가능한 내용을(Select권한을 받은내용) 볼 수 있는 딕셔너리. ALL_INDEXES |
DBA_XXX | 데이터 베이스에 생성되어 있는 내용을 다 볼 수 있는 딕셔너리. ex) DBA_TABLES DBA_INDEXES |
2. Dynamic Performance View
- 실시간으로 변경되는 내용을 볼 수 있는 뷰
- 사용자가 조회할 경우 그 시점의 Control File 이나 메모리에서 데이터를 가져와 보여줌.
- v$로 시작되며, Database가 Nomount 상태일 때 부터 조회가능
2) SYSAUX Tablespace
- 주로 오라클 서버의 성능 튜닝을 위한 데이터들이 저장되어 있음.
- 9i 버전까지는 튜닝관련 딕셔너리들은 SYSTEM Tablespace에 있었으나, 10g버젼부터 성능 튜닝과 관련된 딕셔너리들이 이곳에 별도로 저장이 되었으며, 10g에 새로 등장한 자동튜닝 기능들 AWR, ADDM, ASH 등이 이곳의 정보를 사용한다.
3) 일반 Tablespace
- 일반적으로 많이 사용되는 Tablespace로 관리자가 필요에 의해 만드는 Tablespace.
*** 실습 ***
- Tablespace 생성.
SQL>create tablespace haksa datafile '/u01/app/oracle/oradata/PROD/haksa01.dbf' size 1M;
Tablespace created. |
- Tablespace 생성 확인
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 MANUAL EXAMPLE ONLINE PERMANENT LOCAL AUTO HAKSA ONLINE PERMANENT LOCAL AUTO
7 rows selected. |
- Data file 실제 사용량 확인
- 조회 스크립트 생성. set lines 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 MB, 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; |
SQL> @tablespaces
FILE# TS_NAME MB MB USED_BLOCKS PCT_USED ----- ---------- ---------- ---------- ----------- ---------- 1 SYSTEM 400 51200 32280 63.05 % 2 SYSAUX 200 25600 14264 55.72 % 3 UNDOTBS1 200 25600 25216 98.50 % 4 USERS 50 6400 120 01.88 % 5 EXAMPLE 100 12800 7320 57.19 % 6 HAKSA 1 128 00.00 % |
# HAKSA Table space Full 만들어서 장애유도.
SQL> conn scott/tiger Connected. SQL> create table iphak (stduno number) tablespace haksa; Table created. SQL> begin 2 for i in 1..50000 loop 3 insert into iphak values(i); 4 end loop 5 ; 6 commit ; 7 end; 8 /
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. Tablespace에 Datafle 추가
- 테이블 스페이스 사용량 확인. SQL> @tablespaces
FILE# TS_NAME MB MB USED_BLOCKS PCT_USED ----- ---------- ---------- ---------- ----------- ---------- 1 SYSTEM 400 51200 32280 63.05 % 2 SYSAUX 200 25600 14336 56.00 % 3 UNDOTBS1 200 25600 25216 98.50 % 4 USERS 50 6400 120 01.88 % 5 EXAMPLE 100 12800 7320 57.19 % 6 HAKSA 1 128 120 93.75 %
6 rows selected.
- 권한 없는 계정으로 테이블스페이스 용량 추가 시도 시 실패. SQL> conn scott/tiger Connected. SQL> alter tablespace haksa 2 add datafile '/u01/app/oracle/oradata/PROD/haksa02.dbf' size 20M 3 ; alter tablespace haksa * ERROR at line 1: ORA-01031: insufficient privileges
- SYSTEM 계정으로 생성. SQL> alter tablespace haksa 2 add datafile '/u01/app/oracle/oradata/PROD/haksa02.dbf' size 20M;
Tablespace altered.
- 테이블 스페이스 용량 재 확인 SQL> @tablespaces
FILE# TS_NAME MB MB USED_BLOCKS PCT_USED ----- ---------- ---------- ---------- ----------- ---------- 1 SYSTEM 400 51200 32280 63.05 % 2 SYSAUX 200 25600 14336 56.00 % 3 UNDOTBS1 200 25600 25216 98.50 % 4 USERS 50 6400 120 01.88 % 5 EXAMPLE 100 12800 7320 57.19 % 6 HAKSA 1 128 120 93.75 % 7 HAKSA 20 2560 00.00 % - 다시 데이터 Insert 후 테이블스페이스 사용률 확인
SQL> conn scott/tiger Connected. SQL> SQL> SQL> SQL> SQL> begin 2 for i in 1..50000 loop 3 insert into iphak values(i); 4 end loop; 5 commit; 6 end; 7 /
PL/SQL procedure successfully completed.
SQL> conn SYSTEM/oracle Connected. SQL> @tablespaces
FILE# TS_NAME MB MB USED_BLOCKS PCT_USED ----- ---------- ---------- ---------- ----------- ---------- 1 SYSTEM 400 51200 32280 63.05 % 2 SYSAUX 200 25600 14336 56.00 % 3 UNDOTBS1 200 25600 25472 99.50 % 4 USERS 50 6400 120 01.88 % 5 EXAMPLE 100 12800 7320 57.19 % 6 HAKSA 1 128 120 93.75 % 7 HAKSA 20 2560 136 05.31 %
7 rows selected.
|
조치 2. 데이터파일 Resize
- 93.75% 사용률의 haksa01 데이터파일 리사이즈.
SYS@PROD>@tablespaces
FILE# TS_NAME MB MB USED_BLOCKS PCT_USED FILE_DIR ----- ---------- ---------- ---------- ----------- ---------- ------------------------------------------------------------------------- 1 SYSTEM 400 51200 32280 63.05 % /u01/app/oracle/oradata/PROD/system01.dbf 2 SYSAUX 200 25600 14368 56.13 % /u01/app/oracle/oradata/PROD/sysaux01.dbf 3 UNDOTBS1 200 25600 25472 99.50 % /u01/app/oracle/oradata/PROD/undotbs01.dbf 4 USERS 50 6400 120 01.88 % /u01/app/oracle/oradata/PROD/users01.dbf 5 EXAMPLE 100 12800 7320 57.19 % /u01/app/oracle/oradata/PROD/example01.dbf 6 HAKSA 1 128 120 93.75 % /u01/app/oracle/oradata/PROD/haksa01.dbf 7 HAKSA 20 2560 136 05.31 % /u01/app/oracle/oradata/PROD/haksa02.dbf |
- 데이터파일 리사이즈 후 사용률 확인
SYS@PROD>alter database datafile '/u01/app/oracle/oradata/PROD/haksa01.dbf' resize 20M; Database altered. SYS@PROD>@tablespaces FILE# TS_NAME MB MB USED_BLOCKS PCT_USED FILE_DIR ----- ---------- ---------- ---------- ----------- ---------- -------------------------------------------------------------------------- 1 SYSTEM 400 51200 32280 63.05 % /u01/app/oracle/oradata/PROD/system01.dbf 2 SYSAUX 200 25600 14368 56.13 % /u01/app/oracle/oradata/PROD/sysaux01.dbf 3 UNDOTBS1 200 25600 25472 99.50 % /u01/app/oracle/oradata/PROD/undotbs01.dbf 4 USERS 50 6400 120 01.88 % /u01/app/oracle/oradata/PROD/users01.dbf 5 EXAMPLE 100 12800 7320 57.19 % /u01/app/oracle/oradata/PROD/example01.dbf 6 HAKSA 20 2560 120 04.69 % /u01/app/oracle/oradata/PROD/haksa01.dbf 7 HAKSA 20 2560 136 05.31 % /u01/app/oracle/oradata/PROD/haksa02.dbf 7 rows selected. |
조치 3. Data file 사이즈 자동증가
- 32 bit용 오라클 경우 파일 1개의 크기는 16GB 까지 가능
- 64 bit용 오라클 경우 파일 1개의 크기는 32GB 까지 가능
- Auto extend 옵션 추가. SYSTEM@PROD>alter database datafile '/u01/app/oracle/oradata/PROD/haksa01.dbf' autoextend on;
Database altered. - auto extend 상태 확인.
SYS@PROD>@tbs_auto_status
TABLESPACE MB FILE_NAME AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM 400 /u01/app/oracle/oradata/PROD/system01.dbf NO SYSTEM SYSAUX 200 /u01/app/oracle/oradata/PROD/sysaux01.dbf NO ONLINE UNDOTBS1 200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO ONLINE USERS 50 /u01/app/oracle/oradata/PROD/users01.dbf NO ONLINE EXAMPLE 100 /u01/app/oracle/oradata/PROD/example01.dbf NO ONLINE HAKSA 20 /u01/app/oracle/oradata/PROD/haksa01.dbf YES ONLINE HAKSA 20 /u01/app/oracle/oradata/PROD/haksa02.dbf NO ONLINE |
Tablespace Offline
- tablespace 오프라인은 더이상 해당 테이블 스페이스를 사용하지 못하게 하는 것.
- 특정 tablespace만 shutdown 시킨다는 의미.
- 데이터파일의 위치를 이동하거나, 특정 Tablespace가 장애가 나서 복구해야할 때 사용함.
1)테이블 스페이스를 Offline으로 하는 방법
1-1) 일반적으로 사용하는 Normal Mode
- haksa 라는 테이블 스페이스를 offline 으로 변경.
- 변경 결과 해당 테이블스페이스를 사용하는 오브젝트 즉, Scott의 iphak 테이블의 조회또한 불가능.
- 온라인으로 변경하면, 조회가능. (당연하지만..ㅋㅋ)
SYS@PROD>alter tablespace haksa offline;
Tablespace altered. SYS@PROD>@tbs_auto_status
TABLESPACE MB FILE_NAME AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM 400 /u01/app/oracle/oradata/PROD/system01.dbf NO SYSTEM SYSAUX 200 /u01/app/oracle/oradata/PROD/sysaux01.dbf NO ONLINE UNDOTBS1 200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO ONLINE USERS 50 /u01/app/oracle/oradata/PROD/users01.dbf NO ONLINE EXAMPLE 100 /u01/app/oracle/oradata/PROD/example01.dbf NO ONLINE HAKSA /u01/app/oracle/oradata/PROD/haksa01.dbf OFFLINE HAKSA /u01/app/oracle/oradata/PROD/haksa02.dbf OFFLINE
7 rows selected.
SYS@PROD>select * from scott.iphak; select * from scott.iphak * ERROR at line 1: ORA-00376: file 6 cannot be read at this time ORA-01110: data file 6: '/u01/app/oracle/oradata/PROD/haksa01.dbf' SYS@PROD>alter tablespace haksa online;
Tablespace altered.
SYS@PROD>@tbs_auto_status
TABLESPACE MB FILE_NAME AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM 400 /u01/app/oracle/oradata/PROD/system01.dbf NO SYSTEM SYSAUX 200 /u01/app/oracle/oradata/PROD/sysaux01.dbf NO ONLINE UNDOTBS1 200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO ONLINE USERS 50 /u01/app/oracle/oradata/PROD/users01.dbf NO ONLINE EXAMPLE 100 /u01/app/oracle/oradata/PROD/example01.dbf NO ONLINE HAKSA 20 /u01/app/oracle/oradata/PROD/haksa01.dbf YES ONLINE HAKSA 20 /u01/app/oracle/oradata/PROD/haksa02.dbf NO ONLINE
7 rows selected.
SYS@PROD>select count(*) from scott.iphak;
COUNT(*) ----------
650000 |
1-2) Temporary mode.
- 현재 오프라인 시키고자 하는 테이블스페이스의 데이터파일이 하나라도 이상이 생기게 되면 Offline 수행 불가능한데, 이럴 경우 사용하는 것이 offline Temporary
SYS@PROD>alter tablespace haksa offline temporary;
Tablespace altered.
|
1-3) Immediate Mode
- 반드시 Archive Log mode일 경우에만 사용해야함. (이유는..?)
- 데이터파일이 장애가 나서 데이터를 내려쓰지 못하는 상황에서 Tablespace를 Offline 해야할 경우 사용.
- 해당 테이블스페이스를 온라인 할 때 복구메세지가 나옴.
- Tablespace 전체를 Offline 할 수 있으나, 특정 Datafile만 Offline 할 수도 있다.
- RECOVER 모드로 변경된 후 복구 전까지는 해당 테이블 스페이스를 사용하는 테이블에 대한 조회 불가능.
- Tablespace를 오프라인 한 후 Online을 하게되면 수동으로 Check point를 수행해서 SCN 번호를 동기화 시켜야한다.
- No Archive Log 모드일 때 Offline 변경시도.
SYS@PROD>archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch Oldest online log sequence 24 Current log sequence 26 SYS@PROD>alter database open;
Database altered.
SYS@PROD> SYS@PROD> SYS@PROD> SYS@PROD>@tbs_auto_status
TABLESPACE MB FILE_NAME AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM 400 /u01/app/oracle/oradata/PROD/system01.dbf NO SYSTEM SYSAUX 200 /u01/app/oracle/oradata/PROD/sysaux01.dbf NO ONLINE UNDOTBS1 200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO ONLINE USERS 50 /u01/app/oracle/oradata/PROD/users01.dbf NO ONLINE EXAMPLE 100 /u01/app/oracle/oradata/PROD/example01.dbf NO ONLINE HAKSA 20 /u01/app/oracle/oradata/PROD/haksa01.dbf YES ONLINE HAKSA 20 /u01/app/oracle/oradata/PROD/haksa02.dbf NO ONLINE
7 rows selected.
SYS@PROD>alter database datafile '/u01/app/oracle/oradata/PROD/haksa01.dbf' offline; alter database datafile '/u01/app/oracle/oradata/PROD/haksa01.dbf' offline * ERROR at line 1: ORA-01145: offline immediate disallowed unless media recovery enabled => 해당 명령어로 Offline 변경 불가. SYS@PROD>@tbs_auto_status
TABLESPACE MB FILE_NAME AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM 400 /u01/app/oracle/oradata/PROD/system01.dbf NO SYSTEM SYSAUX 200 /u01/app/oracle/oradata/PROD/sysaux01.dbf NO ONLINE UNDOTBS1 200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO ONLINE USERS 50 /u01/app/oracle/oradata/PROD/users01.dbf NO ONLINE EXAMPLE 100 /u01/app/oracle/oradata/PROD/example01.dbf NO ONLINE HAKSA 20 /u01/app/oracle/oradata/PROD/haksa01.dbf YES ONLINE HAKSA 20 /u01/app/oracle/oradata/PROD/haksa02.dbf NO ONLINE
7 rows selected. SYS@PROD>alter database datafile '/u01/app/oracle/oradata/PROD/haksa01.dbf' offline drop;
Database altered. =>no archive log 모드에서는 drop 이라는 옵션을 추가하여 Offline 으로 변경해야한다. SYS@PROD>@tbs_auto_status
TABLESPACE MB FILE_NAME AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM 400 /u01/app/oracle/oradata/PROD/system01.dbf NO SYSTEM SYSAUX 200 /u01/app/oracle/oradata/PROD/sysaux01.dbf NO ONLINE UNDOTBS1 200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO ONLINE USERS 50 /u01/app/oracle/oradata/PROD/users01.dbf NO ONLINE EXAMPLE 100 /u01/app/oracle/oradata/PROD/example01.dbf NO ONLINE HAKSA /u01/app/oracle/oradata/PROD/haksa01.dbf RECOVER HAKSA 20 /u01/app/oracle/oradata/PROD/haksa02.dbf NO ONLINE
7 rows selected. ** 만약 체크포인트가 발생했다면 오프라인 상태의 테이블스페이스와 나머지 테이블 스페이스의 SCN이 다르게된다. 테스트환경에서 강제로 체크포인트 발생 후 확인. SYS@PROD>@chk_point_num
FILE# TS# NAME STATUS CHECKPOINT_CHANGE# ---------- ---------- ------------------------------ ------- ------------------ 1 0 SYSTEM SYSTEM 810703 2 1 SYSAUX ONLINE 810703 3 2 UNDOTBS1 ONLINE 810703 4 4 USERS ONLINE 810703 5 5 EXAMPLE ONLINE 810703 6 6 HAKSA RECOVER 810476 7 6 HAKSA ONLINE 810703
7 rows selected. ** HAKSA 테이블스페이스 데이터파일 중 온라인상태가 있는 경우 Recover 시도 SYS@PROD>recover tablespace haksa ORA-00283: recovery session canceled due to errors ORA-01124: cannot recover data file 7 - file is in use or recovery ORA-01110: data file 7: '/u01/app/oracle/oradata/PROD/haksa02.dbf'
=> 복구가 불가능 하다.
** HAKSA 테이블 스페이스 오프라인시도 SYS@PROD>alter tablespace haksa offline 2 ; alter tablespace haksa offline * ERROR at line 1: ORA-01191: file 6 is already offline - cannot do a normal offline ORA-01110: data file 6: '/u01/app/oracle/oradata/PROD/haksa01.dbf'
=> 데이터파일 한개만 오프라인 상태라 Temporary 오프라인을 수행해야한다.
SYS@PROD>alter tablespace haksa offline temporary 2 ;
Tablespace altered.
SYS@PROD>@tbs_auto_status
TABLESPACE MB FILE_NAME AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM 400 /u01/app/oracle/oradata/PROD/system01.dbf NO SYSTEM SYSAUX 200 /u01/app/oracle/oradata/PROD/sysaux01.dbf NO ONLINE UNDOTBS1 200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO ONLINE USERS 50 /u01/app/oracle/oradata/PROD/users01.dbf NO ONLINE EXAMPLE 100 /u01/app/oracle/oradata/PROD/example01.dbf NO ONLINE HAKSA /u01/app/oracle/oradata/PROD/haksa01.dbf RECOVER HAKSA /u01/app/oracle/oradata/PROD/haksa02.dbf OFFLINE
7 rows selected. => HAKSA Tablespace 오프라인 되었다. ** Haksa Tablespace 복구 진행 SYS@PROD>recover tablespace haksa; Media recovery complete. SYS@PROD>@tbs_auto_status
TABLESPACE MB FILE_NAME AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM 400 /u01/app/oracle/oradata/PROD/system01.dbf NO SYSTEM SYSAUX 200 /u01/app/oracle/oradata/PROD/sysaux01.dbf NO ONLINE UNDOTBS1 200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO ONLINE USERS 50 /u01/app/oracle/oradata/PROD/users01.dbf NO ONLINE EXAMPLE 100 /u01/app/oracle/oradata/PROD/example01.dbf NO ONLINE HAKSA /u01/app/oracle/oradata/PROD/haksa01.dbf OFFLINE HAKSA /u01/app/oracle/oradata/PROD/haksa02.dbf OFFLINE
7 rows selected.
** 복구 후 온라인 상태로 변경 SYS@PROD>alter tablespace haksa online;
Tablespace altered.
SYS@PROD>@tbs_auto_status
TABLESPACE MB FILE_NAME AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM 400 /u01/app/oracle/oradata/PROD/system01.dbf NO SYSTEM SYSAUX 200 /u01/app/oracle/oradata/PROD/sysaux01.dbf NO ONLINE UNDOTBS1 200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO ONLINE USERS 50 /u01/app/oracle/oradata/PROD/users01.dbf NO ONLINE EXAMPLE 100 /u01/app/oracle/oradata/PROD/example01.dbf NO ONLINE HAKSA 20 /u01/app/oracle/oradata/PROD/haksa01.dbf YES ONLINE HAKSA 20 /u01/app/oracle/oradata/PROD/haksa02.dbf NO ONLINE
7 rows selected.
=> 온라인상태로 변경 확인 ** SCN 정보가 같은지 확인 SYS@PROD>@chk_point_num
FILE# TS# NAME STATUS CHECKPOINT_CHANGE# ---------- ---------- ------------------------------ ------- ------------------ 1 0 SYSTEM SYSTEM 810716 2 1 SYSAUX ONLINE 810716 3 2 UNDOTBS1 ONLINE 810716 4 4 USERS ONLINE 810716 5 5 EXAMPLE ONLINE 810716 6 6 HAKSA ONLINE 810818 7 6 HAKSA ONLINE 810818
7 rows selected.
=> SCN이 다르므로 강제 체크포인트 발생 YS@PROD>alter system checkpoint 2 ;
System altered.
SYS@PROD>@chk_point_num
FILE# TS# NAME STATUS CHECKPOINT_CHANGE# ---------- ---------- ------------------------------ ------- ------------------ 1 0 SYSTEM SYSTEM 810847 2 1 SYSAUX ONLINE 810847 3 2 UNDOTBS1 ONLINE 810847 4 4 USERS ONLINE 810847 5 5 EXAMPLE ONLINE 810847 6 6 HAKSA ONLINE 810847 7 6 HAKSA ONLINE 810847
7 rows selected.
SYS@PROD>select count(*) from scott.iphak;
COUNT(*) ---------- 650000
=> 오프라인 -> 복구-> 온라인-> 조회가능 |
- Archive Log Mode일 때 Offline
SYS@PROD>@tbs_auto_status
TABLESPACE MB FILE_NAME AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM 400 /u01/app/oracle/oradata/PROD/system01.dbf NO SYSTEM SYSAUX 200 /u01/app/oracle/oradata/PROD/sysaux01.dbf NO ONLINE UNDOTBS1 200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO ONLINE USERS 50 /u01/app/oracle/oradata/PROD/users01.dbf NO ONLINE EXAMPLE 100 /u01/app/oracle/oradata/PROD/example01.dbf NO ONLINE HAKSA 20 /u01/app/oracle/oradata/PROD/haksa01.dbf YES ONLINE HAKSA 20 /u01/app/oracle/oradata/PROD/haksa02.dbf NO ONLINE
7 rows selected.
SYS@PROD>alter database datafile '/u01/app/oracle/oradata/PROD/haksa01.dbf' offline;
Database altered.
SYS@PROD>@tbs_auto_status
TABLESPACE MB FILE_NAME AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM 400 /u01/app/oracle/oradata/PROD/system01.dbf NO SYSTEM SYSAUX 200 /u01/app/oracle/oradata/PROD/sysaux01.dbf NO ONLINE UNDOTBS1 200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO ONLINE USERS 50 /u01/app/oracle/oradata/PROD/users01.dbf NO ONLINE EXAMPLE 100 /u01/app/oracle/oradata/PROD/example01.dbf NO ONLINE HAKSA /u01/app/oracle/oradata/PROD/haksa01.dbf RECOVER HAKSA 20 /u01/app/oracle/oradata/PROD/haksa02.dbf NO ONLINE
7 rows selected.
- 해당 테이블을 스페이스를 온라인으로 변경하여도 복구가 필요하다고 나온다. - 복구를 해야 조회가 가능함. SYS@PROD>alter database datafile '/u01/app/oracle/oradata/PROD/haksa01.dbf' online; alter database datafile '/u01/app/oracle/oradata/PROD/haksa01.dbf' online * ERROR at line 1: ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/PROD/haksa01.dbf' SYS@PROD>select count(*) from scott.iphak; select count(*) from scott.iphak * ERROR at line 1: ORA-00376: file 6 cannot be read at this time ORA-01110: data file 6: '/u01/app/oracle/oradata/PROD/haksa01.dbf' -해당 테이블 스페이스 복구 * 이미 데이터파일 한개가 offline 상태이기 때문에 Temporary 모드로 오프라인 시도해야함. SYS@PROD>alter tablespace haksa offline temporary;
Tablespace altered.
SYS@PROD>@tbs_auto_status
TABLESPACE MB FILE_NAME AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM 400 /u01/app/oracle/oradata/PROD/system01.dbf NO SYSTEM SYSAUX 200 /u01/app/oracle/oradata/PROD/sysaux01.dbf NO ONLINE UNDOTBS1 200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO ONLINE USERS 50 /u01/app/oracle/oradata/PROD/users01.dbf NO ONLINE EXAMPLE 100 /u01/app/oracle/oradata/PROD/example01.dbf NO ONLINE HAKSA /u01/app/oracle/oradata/PROD/haksa01.dbf RECOVER HAKSA /u01/app/oracle/oradata/PROD/haksa02.dbf OFFLINE
7 rows selected.
- 해당 테이블스페이스 복구 시도. SYS@PROD>recover tablespace haksa; Media recovery complete. SYS@PROD>@tbs_auto_status
TABLESPACE MB FILE_NAME AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM 400 /u01/app/oracle/oradata/PROD/system01.dbf NO SYSTEM SYSAUX 200 /u01/app/oracle/oradata/PROD/sysaux01.dbf NO ONLINE UNDOTBS1 200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO ONLINE USERS 50 /u01/app/oracle/oradata/PROD/users01.dbf NO ONLINE EXAMPLE 100 /u01/app/oracle/oradata/PROD/example01.dbf NO ONLINE HAKSA /u01/app/oracle/oradata/PROD/haksa01.dbf OFFLINE HAKSA /u01/app/oracle/oradata/PROD/haksa02.dbf OFFLINE
7 rows selected.
- 복구 후 ONLINE 상태로 변경
SYS@PROD>alter tablespace haksa online;
Tablespace altered.
SYS@PROD>@tbs_auto_status
TABLESPACE MB FILE_NAME AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM 400 /u01/app/oracle/oradata/PROD/system01.dbf NO SYSTEM SYSAUX 200 /u01/app/oracle/oradata/PROD/sysaux01.dbf NO ONLINE UNDOTBS1 200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO ONLINE USERS 50 /u01/app/oracle/oradata/PROD/users01.dbf NO ONLINE EXAMPLE 100 /u01/app/oracle/oradata/PROD/example01.dbf NO ONLINE HAKSA 20 /u01/app/oracle/oradata/PROD/haksa01.dbf YES ONLINE HAKSA 20 /u01/app/oracle/oradata/PROD/haksa02.dbf NO ONLINE
7 rows selected.
* 온라인이 되자 조회가 가능함. SYS@PROD>select count(*) from scott.iphak;
COUNT(*) ----------
650000 SYS@PROD>@chk_point_num
FILE# TS# NAME STATUS CHECKPOINT_CHANGE# ---------- ---------- ------------------------------ ------- ------------------ 1 0 SYSTEM SYSTEM 809510 2 1 SYSAUX ONLINE 809510 3 2 UNDOTBS1 ONLINE 809510 4 4 USERS ONLINE 809510 5 5 EXAMPLE ONLINE 809510 6 6 HAKSA ONLINE 809599 7 6 HAKSA ONLINE 809599
7 rows selected. => Check point 값이 다른것을 확인할 수 있다. 이 경우 데이터 파일 백업을 받는다면, 백업파일 자체가 문제가 있게되며 복구에 문제가 될 수 있다. 이럴경우엔 Check Point 를 발생시켜 동기화 한 후 백업을 받아야함. SYS@PROD>alter system checkpoint;
System altered.
SYS@PROD>@chk_point_num
FILE# TS# NAME STATUS CHECKPOINT_CHANGE# ---------- ---------- ------------------------------ ------- ------------------ 1 0 SYSTEM SYSTEM 809644 2 1 SYSAUX ONLINE 809644 3 2 UNDOTBS1 ONLINE 809644 4 4 USERS ONLINE 809644 5 5 EXAMPLE ONLINE 809644 6 6 HAKSA ONLINE 809644 7 6 HAKSA ONLINE 809644
7 rows selected. |
'Oracle 참고서적 정리 > 오라클 관리실무' 카테고리의 다른 글
| 7.Tablespace와 Datafile 관리하기 - Undo Tablespace / (1) | 2015.07.30 |
|---|---|
| 7. Tablespace와 Datafile 관리하기 - Data file 이동 / 테이블스페이스 삭제 (0) | 2015.07.27 |
| 6. Redo Log 관리하기 - SCN과 Checkpoint (0) | 2015.07.16 |
| 6-3. Redo Log 관리하기 - 실습 (0) | 2015.07.16 |
| 6-2. Redo Log File 구성 및 관리하기. (0) | 2015.06.18 |
RECENT COMMENT