[디스크 교체 작업]
디스크 노후화로 인해 디스크를 교체할 경우 oracle database 파일들의 위치를 변경할 필요가 있음(rename)
1. control file
1) 위치 확인
select name from v$controlfile;
2) parameter file 확인
2-1) pfile일 때
shutdown immediate -> vi로 수정 -> cp controlfile -> db open
2-2) spfile일 때
alter system set control_files = ~ 명령어로 spfile 수정 -> db shutdown immediate -> cp controlfile -> db open
2. datafile / redo log file
1) 확인
select name from v$datafile;
select member from v$logfile
2) 변경작업
db shutdown immediate -> cp -> startup mount -> alter database rename file old경로 to new경로; -> alter database open;
[동시 작업]
1. controlfile 위치 변경 (parameter file 수정)
alter system set control_files = … scope = spfile;
2. db shutdown
3. 파일 물리적 이동 (cp)
4. startup mount
5. alter database rename file old경로 to new경로
6. alter database open;
[undo tablespace]
필수 tablespace.
offline 불가.
과거 이미지를 보관하기 위한 디스크 영역.
rollback을 위해 존재(rollback 시 이전 데이터를 다시 DB buffer cache에 올려둬야 함)
여러 undo tablespace 생성 가능 (반드시 default undo tablespace는 1개)
** undo tablespace 할당 원리
1. A 유저 update 시도 (800 -> 1600)
2. update에 필요한 데이터를 db buffer cache에서 찾음. 없다면 해당 block을 datafile에서 찾아 db buffer cache에 올려둠 (pinned buffer)
3. buffer cache 내용을 1600으로 변경 (A 유저만 접근 가능). 과거 이미지를 undo tablespace에 보관 (disk 영역)
4. B 유저가 해당 데이터 조회 시도 시 undo에서 과거 이미지(800)를 보여줌 (읽기 일관성 : consistent read) undo의 데이터를 DB buffer cache에 올려둠 (CR block)
5. A 유저 commit 시도
6. CR block 해제
7. pinned buffer -> dirty buffer가 되어 DBWR 동작 대기
8. long query 처리를 위해서 한동안 과거 이미지(800)를 undo에 보관 (undo retention만큼)
** undo segment 할당 원리
1. 유저별(트랙잭션)로 segment 할당 (segment는 여러 개의 extent로 구성)
2. commit 되더라도 그 즉시 덮어쓰지 않음(재사용 가능 공간으로 바뀔 뿐)
3. 새로운 세션이 dml 수행 시 그 즉시 새로운 segment를 할당하지 않고 기존 segment 중 재사용 가능한 공간을 먼저 사용함.
4. 재사용 공간이 없을 경우 새로운 segment를 할당.
5. 재사용 공간도 없고 늘릴 segment도 여유가 없다면 기존의 segment를 다른 세션이 공유하여 사용.
6. 더이상 어떤 segment도 공유나 할당이 불가능하다면 기존 dml은 모두 rollback처리, 새로운 dml 수행 불가.
** 관련 파라미터
1) undo_retention :
– commit 후 그 즉시 재사용 공간으로 바뀌어 덮어쓰지 못하도록 한동안 과거 이미지를 보관하는 유지 기간.
– 모든 segment가 사용 중일 경우 undo_retention이 지나지 않은 segment를 재사용할 수 있음.
2) undo_retention_guarantee (default=noguarantee) :
– undo_retention 만큼을 무조건 보장할 지의 여부
– noguareantee : undo_retention이 지나지 않아도 모든 segment가 사용 중일 경우 해당 segment를 재사용할 수 있음.
– guarantee : 어떤 경우에도 undo_retention만큼을 보장
[undo 관리]
1. default undo tbs 조회
select name, value
from v$parameter
where name = 'undo_tablespace';2. undo retention 조회
select name, value
from v$parameter
where name = 'undo_retention';
-- default : 900초 = 15분3. undo tbs guarantee 여부
select tablespace_name, retention
from dba_tablespaces
where tablespace_name like 'UNDO%';
-- NOGUARANTEE4. undo tbs 사이즈 조회(할당량)
select tablespace_name, sum(bytes)/1024/1024 as "SIZE(MB)"
from dba_data_files
group by tablespace_name;5. undo 상태 확인
select segment_name,
tablespace_name,
bytes,
status
from dba_undo_extents
order by status;** status
1) ACTIVE : 현재 사용 중인 segment 상태.
2) UNEXPIRED : commit/rollback이 수행되긴 했지만 아직 덮어쓸 수 없는 상태 = undo_retention이 지나지 않은 상태.
3) EXPIRED : commit/rollback이 수행되고 undo_retention이 지나서 재사용할 수 있는 상태.
[실습 – undo segment 할당]
1. 현재 undo segment 조회
select segment_name,
tablespace_name,
bytes,
status
from dba_undo_extents
order by status;
-- active 존재 X2. update 시도
update scott.student set height=170;3. 현재 undo segment 조회
select segment_name,
tablespace_name,
bytes,
status
from dba_undo_extents
order by status;
-- 세그먼트 active 하나 뜬 거 확인 가능4. rollback 시도
rollback;5. 할당된 세그먼트 조회
select segment_name,
tablespace_name,
bytes,
status
from dba_undo_extents
order by status;
-- UNEXPIRED로 바뀐 걸 볼 수 있다.6. 세션별 undo seg 사용량 조회
select s.sid,
s.serial#,
s.username,
r.name "UNDO SEG",
s2.tablespace_name,
s2.bytes/1024/1024 "UNDO SEG SIZE(MB)",
'alter system kill session' ||' '''|| s.sid ||','|| s.serial# ||''' ;' as "kill_ddl"
from v$session s,
v$transaction t,
v$rollname r,
dba_segments s2
where s.taddr=t.addr
and t.xidusn=r.usn
and r.name=s2.segment_name
-- and s2.tablespace_name='UNDOTBS1';alter system kill session 'SID,SERIAL#';
-- 현업에서 undo를 모니터링하다가 특정 세션이 과하게 점유하면, 담당자와 얘기 후, 위 명령을 써서 undo tablespace가 꽉 차기 전에 세션을 죽여서 undo segment를 관리함.[ undo 용량 관리]
undo tbs 용량이 부족한 경우 새로운 DML 수행 불가 -> 용량 증설
방법1. tbs에 datafile 추가
select tablespace_name,
file_name,
bytes/1024/1024 as "SIZE(MB)",
autoextensible
from dba_data_files;
alter tablespace undotbs1 add datafile '/home/oracle/oradata/db1/undotbs02.dbf' size 200M;방법2. 기존 datafile resize
alter database datafile '/home/oracle/oradata/db1/undotbs02.dbf resize 200M;방법3. 기존 datafile에 autoextend 설정
alter database datafile '/home/oracle/oradata/db1/undotbs02.dbf' autoextend on;[새로운 undo tbs 생성 및 변경]
1. undo tbs 생성
create undo tablespace undotbs2 datafile '/home/oracle/oradata/db1/undotbs2_01.dbf' size 200m;2. 확인
select * from dba_tablespaces;
select value
from v$parameter
where name = 'undo_tablespace';
-- UNDOTBS13. default undo 변경(동적 변경 가능)
alter system set undo_tablespace = undotbs2 scope=both;
select value
from v$parameter
where name = 'undo_tablespace';
-- UNDOTBS24. undo retention 변경(동적 변경 가능)
select value
from v$parameter
where name = 'undo_retention';
-- 900초
alter system set undo_retention = 1800 scope=both;
select value
from v$parameter
where name = 'undo_retention';
-- 1800초[실습 – undo tbs full]
1. default undo 확인
select value
from v$parameter
where name = 'undo_tablespace';
-- UNDOTBS22. undo tbs size 확인
select tablespace_name,
sum(bytes)/1024/1024 as "SIZE(MB)"
from dba_data_files
group by tablespace_name;3. autoextend 확인
select tablespace_name,
file_name,
bytes/1024/1024,
autoextensible
from dba_data_files
where tablespace_name = 'UNDOTBS2';4. undo tbs segment 확인
select *
from dba_undo_extents
where tablespace_name='UNDOTBS2'
order by status;5. 대용량 dml 수행
세션1)
update scott.professor set pay = 500;세션2)
create table system.undo_test1(no number, name varchar2(10)) tablespace users;
begin
for i in 1..1000000000 loop
insert into system.undo_test1 values(i,'AAAA');
end loop;
commit;
end;
/6. 모니터링(undo segment 상태별 사이즈 확인)
select status, sum(bytes)/1024/1024 as "SIZE(MB)"
from dba_undo_extents
where tablespace_name = 'UNDOTBS2'
group by status
order by status;대용량 dml 발생 시 active 사이즈가 계속 증가, 일정 시간이 지나면 expired와 unexpired 영역을 재사용(사이즈가 줄어듦). 더이상 segment를 할당할 수 없을 경우 unable to extend segment 메세지 발생하면서 기존 트랜잭션 rollback되고 active -> unexpired 상태로 바뀜.
위에 5번 6번 해보니깐 세션1에서 수행한 update한 거는 나중에 commit이든 rollback이든 찍으면 반영 잘 되는 상관없는 상태고. 세션2에서 수행한 쿼리는 싹 다 rollback되는 거임.
[undo tbs 삭제]
default undo tbs 삭제 불가
1. 삭제 시도
drop tablespace undotbs2; -- error
ORA-30013 : default undo라 삭제 불가2. default undo tbs 변경
alter system set undo_tablespace = undotbs1 scope=both;3. undotbs2 삭제
drop tablespace undotbs2;[undo 관련 error]
1) unable to extend segment …
: 더이상 undo에 어떤 segment 할당이나 기존 segment 사이즈를 증가시킬 수 없는 상태.
-> 기존 dml rollback(active -> unexpired)
-> 새로운 dml 수행 불가
2) snap shot too old(ORA-01555)
– 현상 : long select query 수행 중 해당 데이터의 변경이 있을 경우 주로 발생.
변경 전 데이터를 undo에 기록하지만, 더이상 undo에 과거 이미지가 남아있지 않는 경우 long query 는 기존 이미지를 찾을 수 없으므로 수행되지 못하고 rollback 됨.
– 원인 : 1. undo tbs size가 작아서.
2. undo retention(유지기간)이 짧아서.
3. noguarantee라서. 2번3번은 세트 느낌.
4. query 수행시간이 너무 길어서.
– 해결 : 1. undo tbs size 늘리기, 2. retention 증가, 3. noguarantee를 변경(별로 좋은 선택은 아님), 4. long query 튜닝, 5. long query 와 충돌나는 dml의 시간을 분리.