251203 TIL

데이터 파일 관리

데이터 파일 크기 수동 설정

-- insa_tab 테이블스페이스 생성 (데이터 파일 크기 5MB)
create tablespace insa_tab
datafile '/u01/app/oracle/oradata/ORA19C/insa_tab01.dbf' size 5m;

-- 데이터 파일 크기 10MB로 수정
alter database datafile '/u01/app/oracle/oradata/ORA19C/insa_tab01.dbf' resize 10m;

데이터 파일 크기 자동 설정

데이터 파일 자동 확장 기능 활성화

• autoextend on

alter database datafile '/u01/app/oracle/oradata/ORA19C/insa_tab01.bf' autoextend on;

데이터 파일 자동 확장 기능 비활성화

• autoextend off

alter database datafile '/u01/app/oracle/oradata/ORA19C/insa_tab01.dbf' autoextend off;

자동 확장 기능 활성화 시 크기 제어

alter database datafile '/u01/app/oracle/oradata/ORA19C/insa_tab01.dbf' autoextend on next 2m maxsize 10m;

테이블스페이스에 데이터 파일 추가

alter tablespace insa_tab add datafile '/u01/app/oracle/oradata/ORA19C/insa_tab02.dbf' size 5m;

테이블스페이스 공간 관리

https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/logical-storage-structures.html#GUID-5E81D8A9-2D4C-4991-9D64-B23DD476E22F

Extent 공간 관리

Dictionary Managed Tablespace(딕셔너리로 관리되는 테이블스페이스)

• 사용 가능한 extent에 대해서 딕셔너리 테이블에서 관리
  • fet$ : free extent 정보
  • uet$ : used extent 정보
• extent를 할당하거나 할당이 해제될 때 딕셔너리 테이블에 대해서 조회 갱신이 발생
  • REDO, UNDO 발생량이 많아짐
  • enq:ST-contention 이벤트 발생
• SYSTEM 테이블스페이스가 LMT 방식으로 관리되기 때문에 현재는 이 방식 불가

Locally Managed Tablespace(로컬로 관리되는 테이블스페이스)

• 사용 가능한 extent에 대해서 테이블스페이스(각자 스스로)에서 관리하는 방식
• 비트맵 방식으로 사용 가능한 extent 정보를 관리
• 데이터 딕셔너리 테이블 경합을 줄임(FET$, UET$)
• 공간을 할당하거나 해제할 때 UNDO 정보를 생성하지 않음
• autoallocate : extent 크기는 처음은 64K로 생성한 후 다음 extent 크기는 오라클이 자동으로 결정 (기본값)

create tablespace insa_tab
datafile '/u01/app/oracle/oradata/ORA19C/insa_tab01.dbf' size 5m autoextend on next 2m maxsize 10m
extent management local autoallocate;

-- extent management를 지정하지 않아도 동일
create tablespace insa_tab
datafile '/u01/app/oracle/oradata/ORA19C/insa_tab01.dbf' size 5m autoextend on next 2m maxsize 10m;

• uniform : extent 크기의 기본값은 1MB로 설정

create tablespace insa_tab
datafile '/u01/app/oracle/oradata/ORA19C/insa_tab01.dbf' size 5m autoextend on next 2m maxsize 10m
extent management local uniform;

• uniform size : extent 크기를 수동으로 설정
  • KB, MB 단위의 extent 크기 수동 결정

create tablespace insa_tab
datafile '/u01/app/oracle/oradata/ORA19C/insa_tab01.dbf' size 5m autoextend on next 2m maxsize 10m
extent management local uniform size 1m;

Extent 크기가 클 때 문제점

• 테이블에 입력될 데이터가 많지 않을 때 유휴 공간이 많아 공간이 낭비됨

Segment 공간 관리

https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/logical-storage-structures.html#GUID-5050DCC5-DBBD-4B57-AB14-D83A480B9AAE

Database Concepts

FLM(FreeList Management), MSSM(Manual Segment Space Management)

create tablespace insa_tab
datafile '/u01/app/oracle/oradata/ORA19C/insa_tab01.dbf' size 5m autoextend on next 2m maxsize 10m
extent management local
segment space management manual;

select tablespace_name, segment_space_management from dba_tablespaces;

-- 테이블 생성
create table hr.insa
tablespace insa_tab
as
select * from hr.employees;

-- 테이블 정보 조회
select table_name, pct_used, freelists, freelist_groups 
from dba_tables 
where table_name = 'INSA';

• pctused, freelists, freelists groups를 사용하는 방식
• freelists 는 free block을 linked list 알고리즘으로 관리하는 기법
  • free block : pctused 블록 파라미터로 설정된 값보다 블록 안에 있는 행이 줄어 들어 있는 블록
• SYSTEM, UNDO, TEMP 테이블스페이스가 사용하고 있는 방식
• 단점
  • 공간의 재활용률이 떨어짐
    • pctused 이상의 행을 가지고 있는 블록에도 free 공간이 남아있을 수 있지만 재활용하지 않기 때문
  • 동시 insert 작업 시에 문제(freelists 동시 접근 불가) → buffer busy waits

ASSM(Automatic Segment Space Management)

create tablespace insa_tab
datafile '/u01/app/oracle/oradata/ORA19C/insa_tab01.dbf' size 5m autoextend on next 2m maxsize 10m
extent management local
segment space management auto;

select tablespace_name, segment_space_management from dba_tablespaces;

-- 테이블 생성
create table hr.insa
tablespace insa_tab
as
select * from hr.employees;

-- 테이블 정보 조회
select table_name, pct_used, freelists, freelist_groups 
from dba_tables 
where table_name = 'INSA';

• 각 블록의 상태를 비트맵(bitmap) 값으로 관리하는 방식
• 공간 관리 자동화
• 블록의 상태를 총 6단계로 관리
  • unformated
  • full
  • 0 ~ 25% free
  • 25 ~ 50% free
  • 50 ~ 75% free
  • 75 ~ 100% free

테이블스페이스 READ ONLY

• read only 테이블스페이스 안에 있는 테이블은 데이터 읽기만 가능
• DML 작업 불허
• partial checkpoint 발생

초기화

drop tablespace insa_tab including contents and datafiles;

create tablespace insa_tab
datafile '/u01/app/oracle/oradata/ORA19C/insa_tab01.dbf' size 5m autoextend on next 2m maxsize 10m
extent management local;

create table hr.insa
tablespace insa_tab
as
select * from hr.employees;

select d.file_id, d.tablespace_name, d.file_name, v.checkpoint_change#, v.enabled
from v$datafile v, dba_data_files d
where v.file# = d.file_id;

READ ONLY 모드로 변경

alter system checkpoint;

-- read only 모드로 변경 -> partial checkpoint 발생
alter tablespace insa_tab read only;

READ ONLY 모드에서 가능한 작업

-- 조회 가능
select * from hr.insa where employee_id = 100;

-- dml 불가
update hr.insa set salary = salary * 1.1 where employee_id = 100;
delete from hr.insa where employee_id = 100;
insert into hr.insa select * from hr.employees;

ORA-00372: 파일 5는 지금 수정될 수 없습니다
ORA-01110: 5 데이터 파일: '/u01/app/oracle/oradata/ORA19C/insa_tab01.dbf'

-- create 불가
create table hr.insa_dept
tablespace insa_tab
as select * from hr.departments;

ORA-01647: 'INSA_TAB' 테이블스페이스는 읽기 전용입니다. 영역을 할당할 수 없습니다.

desc hr.insa

이름             널?       유형           
-------------- -------- ------------ 
EMPLOYEE_ID             NUMBER(6)    
FIRST_NAME              VARCHAR2(20) 
LAST_NAME      NOT NULL VARCHAR2(25) 
EMAIL          NOT NULL VARCHAR2(25) 
PHONE_NUMBER            VARCHAR2(20) 
HIRE_DATE      NOT NULL DATE         
JOB_ID         NOT NULL VARCHAR2(10) 
SALARY                  NUMBER(8,2)  
COMMISSION_PCT          NUMBER(2,2)  
MANAGER_ID              NUMBER(6)    
DEPARTMENT_ID           NUMBER(4)

-- 컬럼 수정 가능
alter table hr.insa modify last_name varchar2(30);

Table HR.INSA이(가) 변경되었습니다.

desc hr.insa

이름             널?       유형           
-------------- -------- ------------ 
EMPLOYEE_ID             NUMBER(6)    
FIRST_NAME              VARCHAR2(20) 
LAST_NAME      NOT NULL VARCHAR2(30) 
EMAIL          NOT NULL VARCHAR2(25) 
PHONE_NUMBER            VARCHAR2(20) 
HIRE_DATE      NOT NULL DATE         
JOB_ID         NOT NULL VARCHAR2(10) 
SALARY                  NUMBER(8,2)  
COMMISSION_PCT          NUMBER(2,2)  
MANAGER_ID              NUMBER(6)    
DEPARTMENT_ID           NUMBER(4)

-- 컬럼 추가 가능
alter table hr.insa add dept_name varchar2(30);

Table HR.INSA이(가) 변경되었습니다.

desc hr.insa

이름             널?       유형           
-------------- -------- ------------ 
EMPLOYEE_ID             NUMBER(6)    
FIRST_NAME              VARCHAR2(20) 
LAST_NAME      NOT NULL VARCHAR2(30) 
EMAIL          NOT NULL VARCHAR2(25) 
PHONE_NUMBER            VARCHAR2(20) 
HIRE_DATE      NOT NULL DATE         
JOB_ID         NOT NULL VARCHAR2(10) 
SALARY                  NUMBER(8,2)  
COMMISSION_PCT          NUMBER(2,2)  
MANAGER_ID              NUMBER(6)    
DEPARTMENT_ID           NUMBER(4)    
DEPT_NAME               VARCHAR2(30) 

-- 컬럼 삭제 불가
alter table hr.insa drop column dept_name;

ORA-12985: 'INSA_TAB' 테이블스페이스는 읽기 전용이고 열을 삭제할 수 없습니다

-- truncate 불가
truncate table hr.insa;

ORA-00372: 파일 5는 지금 수정될 수 없습니다
ORA-01110: 5 데이터 파일: '/u01/app/oracle/oradata/ORA19C/insa_tab01.dbf'

-- drop 가능
drop table hr.insa;

Table HR.INSA이(가) 삭제되었습니다.

READ WRITE 모드로 변경

-- read write 모드로 변경
alter tablespace insa_tab read write;

select d.file_id, d.tablespace_name, d.file_name, v.checkpoint_change#, v.enabled
from v$datafile v, dba_data_files d
where v.file# = d.file_id;

테이블스페이스 OFFLINE

• 테이블스페이스에 속한 객체들을 사용할 수 없도록 함
• partial checkpoint 발생
• offline 옵션
  • normal(기본값) : partial checkpoint 발생
  • temporary : 가능한 데이터 파일에 속한 drity buffer만 디스크로 쓰는 작업 수행, 복구 작업 수행해야 함
  • immediate : checkpoint를 발생하지 않고 offline으로 수행, 복구 작업 수행해야 함, archivelog 모드에서 수행하는 옵션
• offline으로 설정할 수 없는 테이블스페이스
  • system
  • active undo segment가 있는 undo tablespace
  • default temporary tablespace

OFFLINE으로 변경

-- offline으로 변경
alter tablespace insa_tab offline;
-- 동일
alter tablespace insa_tab offline normal;

select d.file_id, d.tablespace_name, d.file_name, v.checkpoint_change#, v.enabled, d.online_status
from v$datafile v, dba_data_files d
where v.file# = d.file_id;

ONLINE으로 변경

-- online으로 변경
alter tablespace insa_tab online;

select d.file_id, d.tablespace_name, d.file_name, v.checkpoint_change#, v.enabled, d.online_status
from v$datafile v, dba_data_files d
where v.file# = d.file_id;

데이터 파일 이관 작업

drop tablespace insa_tab including contents and datafiles;

create tablespace insa_tab
datafile '/home/oracle/insa_tab01.dbf' size 5m autoextend on next 2m maxsize 10m;

create table hr.insa
tablespace insa_tab
as select * from hr.employees;

select d.file_id, d.tablespace_name, d.file_name, v.checkpoint_change#, v.enabled, d.online_status
from v$datafile v, dba_data_files d
where v.file# = d.file_id;

• /home/oracle/insa_tab01.dbf → /u01/app/oracle/oradata/ORA19C/insa_tab01.dbf 이관하고자 함

1. 테이블스페이스 offline

-- 1. 테이블스페이스 offline
alter tablespace insa_tab offline normal;

select d.file_id, d.tablespace_name, d.file_name, v.checkpoint_change#, v.enabled, d.online_status
from v$datafile v, dba_data_files d
where v.file# = d.file_id;

2. 물리적으로 데이터 파일 이동

-- 2. 물리적으로 데이터 파일 이동
SYS@ora19c> ! mv -v /home/oracle/insa_tab01.dbf /u01/app/oracle/oradata/ORA19C/
‘/home/oracle/insa_tab01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/insa_tab01.dbf’

SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/insa_tab01.dbf
/u01/app/oracle/oradata/ORA19C/insa_tab01.dbf

3. 기존 파일을 새로운 파일 위치로 수정

-- 3. 기존 파일을 새로운 파일 위치로 수정
alter tablespace insa_tab rename datafile '/home/oracle/insa_tab01.dbf' to '/u01/app/oracle/oradata/ORA19C/insa_tab01.dbf';

select d.file_id, d.tablespace_name, d.file_name, v.checkpoint_change#, v.enabled, d.online_status
from v$datafile v, dba_data_files d
where v.file# = d.file_id;

4. 테이블스페이스 online 설정

-- 4. 테이블스페이스 online 설정
alter tablespace insa_tab online;

select d.file_id, d.tablespace_name, d.file_name, v.checkpoint_change#, v.enabled, d.online_status
from v$datafile v, dba_data_files d
where v.file# = d.file_id;

모든 데이터 파일 이관

SYS@ora19c> select name from v$datafile;

NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/system01.dbf
/u01/app/oracle/oradata/ORA19C/sysaux01.dbf
/u01/app/oracle/oradata/ORA19C/undotbs01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf

SYS@ora19c> select name from v$tempfile;

NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/temp01.dbf

SYS@ora19c> select name from v$controlfile;

NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/control01.ctl

SYS@ora19c> select member from v$logfile;

MEMBER
-------------------------------------------
/u01/app/oracle/oradata/ORA19C/redo03.log
/u01/app/oracle/oradata/ORA19C/redo02.log
/u01/app/oracle/oradata/ORA19C/redo01.log

1. 오라클 데이터베이스 정상 종료

shutdown immediate

2. close backup

[oracle@ora19c ~]$ cp -v /u01/app/oracle/oradata/ORA19C/* /home/oracle/backup/
‘/u01/app/oracle/oradata/ORA19C/control01.ctl’ -> ‘/home/oracle/backup/control01.ctl’
‘/u01/app/oracle/oradata/ORA19C/redo01.log’ -> ‘/home/oracle/backup/redo01.log’
‘/u01/app/oracle/oradata/ORA19C/redo02.log’ -> ‘/home/oracle/backup/redo02.log’
‘/u01/app/oracle/oradata/ORA19C/redo03.log’ -> ‘/home/oracle/backup/redo03.log’
‘/u01/app/oracle/oradata/ORA19C/sysaux01.dbf’ -> ‘/home/oracle/backup/sysaux01.dbf’
‘/u01/app/oracle/oradata/ORA19C/system01.dbf’ -> ‘/home/oracle/backup/system01.dbf’
‘/u01/app/oracle/oradata/ORA19C/temp01.dbf’ -> ‘/home/oracle/backup/temp01.dbf’
‘/u01/app/oracle/oradata/ORA19C/undotbs01.dbf’ -> ‘/home/oracle/backup/undotbs01.dbf’
‘/u01/app/oracle/oradata/ORA19C/users01.dbf’ -> ‘/home/oracle/backup/users01.dbf’

[oracle@ora19c ~]$ ls /home/oracle/backup
control01.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf

3. 모든 데이터 파일을 새로운 디스크 위치로 이동

[oracle@ora19c ~]$ mkdir userdata
[oracle@ora19c ~]$ cd userdata
[oracle@ora19c userdata]$ pwd
/home/oracle/userdata

mv -v /u01/app/oracle/oradata/ORA19C/system01.dbf /home/oracle/userdata/
mv -v /u01/app/oracle/oradata/ORA19C/sysaux01.dbf /home/oracle/userdata/
mv -v /u01/app/oracle/oradata/ORA19C/undotbs01.dbf /home/oracle/userdata/
mv -v /u01/app/oracle/oradata/ORA19C/users01.dbf /home/oracle/userdata/
mv -v /u01/app/oracle/oradata/ORA19C/temp01.dbf /home/oracle/userdata/

[oracle@ora19c userdata]$ ls /u01/app/oracle/oradata/ORA19C/*.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/*.dbf: No such file or directory
[oracle@ora19c userdata]$ ls /home/oracle/userdata/*.dbf
/home/oracle/userdata/sysaux01.dbf  /home/oracle/userdata/temp01.dbf     /home/oracle/userdata/users01.dbf
/home/oracle/userdata/system01.dbf  /home/oracle/userdata/undotbs01.dbf

4. 오라클 데이터베이스를 mount 까지만 시작

startup mount

SYS@ora19c> select status from v$instance;

STATUS
------------
MOUNTED

5. 기존 데이터 파일을 새로운 데이터 파일로 수정

SYS@ora19c> select name from v$datafile;

NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/system01.dbf
/u01/app/oracle/oradata/ORA19C/sysaux01.dbf
/u01/app/oracle/oradata/ORA19C/undotbs01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf

SYS@ora19c> select name from v$tempfile;

NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/temp01.dbf

alter database rename file '/u01/app/oracle/oradata/ORA19C/system01.dbf' to '/home/oracle/userdata/system01.dbf';
alter database rename file '/u01/app/oracle/oradata/ORA19C/sysaux01.dbf' to '/home/oracle/userdata/sysaux01.dbf';
alter database rename file '/u01/app/oracle/oradata/ORA19C/undotbs01.dbf' to '/home/oracle/userdata/undotbs01.dbf';
alter database rename file '/u01/app/oracle/oradata/ORA19C/users01.dbf' to '/home/oracle/userdata/users01.dbf';
alter database rename file '/u01/app/oracle/oradata/ORA19C/temp01.dbf' to '/home/oracle/userdata/temp01.dbf';

SYS@ora19c> select name from v$datafile;

NAME
--------------------------------------------------
/home/oracle/userdata/system01.dbf
/home/oracle/userdata/sysaux01.dbf
/home/oracle/userdata/undotbs01.dbf
/home/oracle/userdata/users01.dbf

SYS@ora19c> select name from v$tempfile;

NAME
--------------------------------------------------
/home/oracle/userdata/temp01.dbf

6. 데이터베이스 open

alter database open;

SYS@ora19c> select status from v$instance;

STATUS
------------
OPEN

SYS@ora19c> select name from v$datafile;

NAME
--------------------------------------------------
/home/oracle/userdata/system01.dbf
/home/oracle/userdata/sysaux01.dbf
/home/oracle/userdata/undotbs01.dbf
/home/oracle/userdata/users01.dbf

SYS@ora19c> select name from v$tempfile;

NAME
--------------------------------------------------
/home/oracle/userdata/temp01.dbf

백업본으로 원상복구

# 데이터베이스 강제 종료
SYS@ora19c> shutdown abort;
ORACLE instance shut down.

#  이관한 데이터 파일 전체 삭제
SYS@ora19c> !
[oracle@ora19c ~]$ cd userdata/
[oracle@ora19c userdata]$ rm -f *
[oracle@ora19c userdata]$ ls

# 데이터 파일 원래 위치에 백업 데이터로 덮어쓰기
[oracle@ora19c userdata]$ cd ~/backup/
[oracle@ora19c backup]$ ls
control01.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[oracle@ora19c backup]$ cp -v *.* /u01/app/oracle/oradata/ORA19C
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ORA19C/control01.ctl’
‘redo01.log’ -> ‘/u01/app/oracle/oradata/ORA19C/redo01.log’
‘redo02.log’ -> ‘/u01/app/oracle/oradata/ORA19C/redo02.log’
‘redo03.log’ -> ‘/u01/app/oracle/oradata/ORA19C/redo03.log’
‘sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/sysaux01.dbf’
‘system01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/system01.dbf’
‘temp01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/temp01.dbf’
‘undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/undotbs01.dbf’
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/users01.dbf’

[oracle@ora19c backup]$ ls /u01/app/oracle/oradata/ORA19C
control01.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf

[oracle@ora19c backup]$ exit
exit

# 데이터베이스 재시작
SYS@ora19c> startup
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             574619648 bytes
Database Buffers          239075328 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.

# 원상복구 완료
SYS@ora19c> select name from v$datafile;

NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/system01.dbf
/u01/app/oracle/oradata/ORA19C/sysaux01.dbf
/u01/app/oracle/oradata/ORA19C/undotbs01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf

SYS@ora19c> select name from v$tempfile;

NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/temp01.dbf