48 ORACLE ASSM, Tablespace, Undo

■ ASSM (Automatic Segment Space Management)
자동 세그먼트 공간 관리
- 각 블록의 상태를 비트맵(bitmap)값으로 관리하는 방식
- 공간관리가 자동화한다.
- ASSM은 공간관리를 오라클이 담당하기때문에 튜닝을 위해 속성을 지정하거나 히든 파라미터값을 변경할 필요가 없다.
- 데이터 블록의 여유 공간을 총 6단계로 관리
    - full
    - unformated
    - 0%~25% free
    - 25~50% free
    - 50%~75% free
    - 75%~100% free
- 동시에 insert 성능이 향상된다.
 

---

■ 테이블스페이스 크기 조정

# 테이블스페이스 생성시에 데이터파일 자동으로 확장기능 설정

CREATE TABLESPACE insa DATAFILE '/u01/app/oracle/oradata/ora11g/insa01.dbf' size 5M AUTOEXTEND ON NEXT 2M MAXSIZE 200M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO;

테이블스페이스 생성 datafile 사이즈 5MB datafile 자동 확장 활성화 5MB 사용 후 maxsize 될 때까지 자동으로 2MB 씩 증가 EXTENT를 bitmap으로 관리, 1MB 씩 생성 세그먼트 공간 자동관리

SELECT * FROM dba_tablespaces;
- SEGMENT_SPACE_MANAGEMENT : AUTO

SELECT * FROM dba_data_files;
- AUTOEXTENSIBLE : 자동증가여부 [ YES | NO(기본값) ]

SELECT * FROM dba_free_space;
> 관리하는 space의 프리공간★



CREATE TABLESPACE customer
DATAFILE '/u01/app/oracle/oradata/ora11g/customer01.dbf' size 5M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

SELECT * FROM dba_tablespaces;
SELECT * FROM dba_data_files;
SELECT * FROM dba_free_space;

# 테이블스페이스 생성 후에 AUTOEXTEND를 지정하여 자동으로 확장기능 설정
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ora11g/customer01.dbf'
AUTOEXTEND ON NEXT 2M;

SELECT * FROM dba_data_files;
- AUTOEXTENSIBLE : YES
- MAXBYTES : 34359721984 (무한)


# 데이터 파일 수동으로 RESIZE 조절
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ora11g/customer01.dbf' RESIZE 10M;

SELECT * FROM dba_data_files;
- BYTES : 10485760


# 테이블스페이스에 데이터 파일 추가
ALTER TABLESPACE customer ADD DATAFILE '/u01/app/oracle/oradata/ora11g/customer02.dbf' SIZE 10M;

SELECT * FROM dba_data_files;
SELECT * FROM dba_free_space;

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ora11g/customer02.dbf' AUTOEXTEND ON NEXT 2M;

SELECT * FROM dba_data_files;


# 테이블스페이스 삭제
DROP TABLESPACE customer INCLUDING CONTENTS AND DATAFILES;
> CONTENTS 같이 삭제, 물리적 데이터파일 같이 삭제

SELECT * FROM dba_tablespaces;
SELECT * FROM dba_data_files;
SELECT * FROM dba_free_space;
[oracle@oracle ora11g]$ ls $ORACLE_BASE/oradata/ora11g





CREATE TABLESPACE insa_tab
DATAFILE '/u01/app/oracle/oradata/ora11g/insa_tab01.dbf' SIZE 5M
AUTOEXTEND ON NEXT 2M MAXSIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLE hr.emp
TABLESPACE insa_tab
AS
SELECT * FROM hr.employees;

SELECT * FROM dba_tablespaces;
SELECT * FROM dba_data_files;
SELECT * FROM dba_segments WHERE owner = 'HR' AND segment_name = 'EMP';
SELECT * FROM dba_extents WHERE owner = 'HR' AND segment_name = 'EMP';




■ 테이블스페이스 READ ONLY
- 부분 CHECKPOINT 발생
- 데이터를 읽을 수만 있다. (select문 수행)
- DML 불허
- 테이블스페이스에 객체 삭제 가능

ALTER TABLESPACE insa_tab READ ONLY;

SELECT * FROM dba_tablespaces;
- STATUS : READ ONLY

SELECT * FROM hr.emp;
UPDATE hr.emp SET salary = 2000 WHERE employee_id = 100;
>>> ORA-01644: 'INSA_TAB' 테이블스페이스는 이미 읽기 전용입니다.

SELECT * FROM v$datafile;



# FULL CHECKPOINT
ALTER SYSTEM CHECKPOINT;
>> checkpoint 전체 맞추기

SELECT * FROM v$datafile;

불안전한 복구 시 체크포인트가 전부 일치되어야 복구가 가능하다.
>> read only -> read write 변경 후 db shutdown - 백업


ALTER TABLESPACE insa_tab READ WRITE;
SELECT * FROM v$datafile;

ALTER SYSTEM CHECKPOINT;
SELECT * FROM v$datafile;

■ 테이블스페이스 OFFLINE
- 부분 CHECKPOINT 발생
- 테이블스페이스에 속한 객체들을 사용할 수 없다.
- OFFLINE으로 설정할 수 없는 테이블스페이스
    - SYSTEM
    - 활성화되어 있는 UNDO
    - 기본 TEMP TABLESPACE

SELECT * FROM hr.emp;
>>> ORA-00376: 현재 파일 6를 읽을 수 없습니다

ALTER TABLESPACE insa_tab OFFLINE [NORMAL];
- NORMAL : 부분 CHECKPOINT 발생. 기본값
- TEMPORARY : 가능한 데일리파일에 속한 DIRTY BUFFER만 디스크로 쓰는 작업 수행
- IMMEDIATE : CHECKPOINT 발생하지 않고 OFFLINE으로 수행된다. 후에 복구작업을 수행해야한다. ARCHIVELOG mod에서 수행하는 옵션

ALTER TABLESPCE insa_tab ONLINE;
ALTER TABLESPCE insa_tab OFFLINE IMMEDIATE;



# ONLINE BACKUP (ARCHIVELOG mode)
ALTER TABLESPACE insa_tab BEGIN BACKUP;

ALTER TABLESPACE insa_tab END BACKUP;





■ BIGFILE 테이블스페이스
- Bigfile 테이블스페이스에 단일 파일만 포함한다.
- 최대 파일의 크기 8TB ~ 125GB

CREATE BIGFILE TABLESPACE big_tab
DATAFILE '/u01/app/oracle/oradata/ora11g/big_tab01.dbf' SIZE 1G    -- 크게
AUTOEXTEND ON NEXT 2M MAXSIZE 2G    -- 크게 또는 무제한
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;





■ 데이터파일 이관 작업

# TableSpace 삭제
DROP TABLESPACE insa_tab INCLUDING CONTENTS AND DATAFILES;

# TableSpace 생성
CREATE TABLESPACE insa_tab
DATAFILE '/u01/app/oracle/oradata/insa_tab01.dbf' SIZE 5M
AUTOEXTEND ON NEXT 2M MAXSIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

# Table 생성
CREATE TABLE hr.emp
TABLESPACE insa_tab
AS
SELECT * FROM hr.employees;

SELECT * FROM hr.emp;

운영중이던 디스크에 문제가 생겼다! -> 이관작업

1. 
ALTER TABLESPACE insa_tab OFFLINE;

SELECT * FROM v$datafile;
- STATUS : OFFLINE
- ENABLED : DISABLED


2. 물리적 데이터 파일을 이동
기존 : /u01/app/oracle/oradata/insa_tab01.dbf
신규 : /u01/app/oracle/oradata/ora11g/insa_tab01.dbf

[oracle@oracle ~]$ mv -v /u01/app/oracle/oradata/insa_tab01.dbf /u01/app/oracle/oradata/ora11g/insa_tab01.dbf

SELECT * FROM v$datafile;
- NAME : /u01/app/oracle/oradata/insa_tab01.dbf


3. 기존 파일을 새로운 파일 위칙로 수정
ALTER TABLESPACE insa_tab RENAME DATAFILE
'/u01/app/oracle/oradata/insa_tab01.dbf' TO
'/u01/app/oracle/oradata/ora11g/insa_tab01.dbf';

SELECT * FROM v$datafile;
- NAME : /u01/app/oracle/oradata/ora11g/insa_tab01.dbf


4.
ALTER TABLESPACE insa_tab ONLINE;

SELECT * FROM v$datafile;
- STATUS : ONLINE
- ENABLED : READ WRITE

---

■ UNDO
- DML작업시에 이전값을 저장하는 공간
- 트랜잭션이 종료(commit, rollback)될 때까지는 이전값을 보존해야한다.
- 목적
    - rollback
    - read consistent (읽기일관성)
    - flashback query
    - 실패한 트랜젝션 recovery


# undo monitorimg
- undo space가 부족한 경우 ORA-01650 : unable to extend rollback segment
- 읽기 일관성이 어긋난 log query문에서 ORA-01555 : snapshot too old
- enq : US - contention wait envet
>>> undo 공간 관리 및 모니터링을 잘하자.

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO       -- (9i)
undo_retention                       integer     900        -- 15분
undo_tablespace                      string      UNDOTBS1


undo_retention
- 트랜젝션이 종료가 되었더라도 이전값을 undo_retention 파라미터에 설정되어있는 초 시간까지는 보존하자.
- read consistent (읽기일관성)
- flashback query

ALTER SYSTEM SET undo_retention = 1800;

# 트랜잭션이 종료 되었더라도 이전값을 undo_retention 꼭 보장하자.
# undo_retentionDMF 보장하기 위해서 새로운 트랜잭션이 실패할 수도 있다.
ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;
 
ALTER TABLESPACE UNDOTBS1 RETENTION NOGUARANTEE;


SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

xacts : 트랜잭션이 어디에 붙었나

<<oracle : hr >>
UPDATE hr.emp
SET salary = salary * 1.1
WHERE employee_id = 100;
<->

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

<<oracle : hr >>
UPDATE hr.emp
SET salary = salary * 1.1
WHERE employee_id = 110;
<->

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

SQL> delete from hr.emp where employee_id=200;

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

SELECT s.username, t.xidusn, t.ubafil, t.ubablk, t.used_ublk
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr;
>> 현재 열린 세션과 현재 트랜젝션의 정보 조인
xidusn : undo segment 번호 / SEGMENT_ID
ubafil : 파일번호 / FILE_ID
ubablk : block 번호 / BLOCK_ID
used_ublk : 사용중인 undo block 수. 누가 과도하게 사용하는가

SELECT * FROM dba_rollback_segs;
> 현재 만들어져있는 시스템 undo목록
sys : 딕셔너리 갱신 할때
PUBLIC : 유저
트랜젝션 수행 - 
TABLESPACE_NAME : UNDOTBS1

각 계정에서 rollback;

SELECT s.username, t.xidusn, t.ubafil, t.ubablk, t.used_ublk
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr;


SELECT * FROM dba_data_files;
FILE_NAME : /u01/app/oracle/oradata/ora11g/undotbs01.dbf
BYTES : 110100480
AUTOEXTENSIBLE : YES

ALTER TABLESPACE UNDOTBS1 ADD DATAFILE
'/u01/app/oracle/oradata/ora11g/undotbs02.dbf' SIZE 10M AUTOEXTEND ON;

SELECT * FROM dba_data_files;



# 새로운 undo tablespace 생성한 후 지정
CREATE UNDO TABLESPACE UNDO1
DATAFILE '/u01/app/oracle/oradata/ora11g/UNDO1.dbf' SIZE 5M AUTOEXTEND ON;

SELECT * FROM dba_tablespaces;
SEGMENT_SPACE_MANAGEMENT : MANUAL

SQL> show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1


ALTER SYSTEM SET undo_tablespace=UNDO1;
> 지정

SQL> show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDO1


select * from dba_rollback_segs;
> 트랜잭션이 진행중이라면 OFFLINE 지연중 -> 삭제불가 -> commit 또는 rollback 하자

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

select * from dba_rollback_segs;

SELECT * FROM dba_tablespaces;