[temp]
정렬을 위한 공간.
1차적으로 PGA(instance 내 존재)에서 정렬하다가 공간이 부족할 경우에 temp 테이블스페이스에서 정렬 수행.
여러 temp 테이블스페이스 생성 가능.
default temp 테이블스페이스를 유저별로 딱 1개씩 설정할 수 있다.
system default temp 테이블스페이스도 존재.
default temp 테이블스페이스 삭제 불가.
복구 영역이 아님. scn정보 같은 것이 없다.
1. 확인
select tablespace_name,
file_name,
bytes,
autoextensible
from dba_temp_files;
-- 1) datafile 관련 뷰
select * from dba_data_files; -- dba_ = 관리와 관련됨.
select * from v$datafile; -- v$뷰 = controlfile과 관련됨. 복구 목적
-- 2) temp 관련 뷰
select * from dba_temp_files;
select * from v$tempfile;2. default temp tablespace 조회
1) DB 전체
select *
from database_properties
where property_name = 'DEFAULT_TEMP_TABLESPACE';2) 유저별
DB전체와 temp name이 다르다면 유저별 temp가 우선순위
select username,
default_tablespace,
temporary_tablespace
from dba_users;[실습 – temp 테이블스페이스 생성 및 변경]
1. 새 temp 테이블스페이스 생성
select *
from dba_temp_files;
create temporary tablespace temp2
tempfile '/home/oracle/oradata/db1/temp02.dbf' size 50m;2. default temp 테이블스페이스 변경
1) DB전체 temp 변경
alter database default temporary tablespace temp2;2) 유저별 temp 변경
alter user scott temporary tablespace temp2;[실습 – temp full]
정렬 수행 시 temp 테이블스페이스 공간 부족 현상 발생
select owner, segment_name, bytes
from dba_segments
where owner='SCOTT'
order by 3 desc;1. orcl dblink를 사용한 대용량 테이블 생성
create table scott.price
as
select * from price@dblink_orcl;2. default temp 테이블스페이스 변경
alter database default temporary tablespace temp;
alter user scott temporary tablespace temp2;3. 대용량 정렬 발생(다른 세션에서 수행)
alter system set "_PGA_MAX_SIZE" = 10m; -- pga에서 정렬이 끝나버리면 temp영역을 사용하지 않으므로 pga 영역을 줄여줌.
-- system에서 수행) temp를 사용
drop index scott.index1;
create index scott.index1 on scott.price(상품명, 조사일, 판매가격, 판매업소, 제조사, 세일여부, 원플러스원);
-- scott에서 수행) temp2를 사용
drop index scott.index1;
create index scott.index1 on scott.price(상품명, 조사일, 판매가격, 판매업소, 제조사, 세일여부, 원플러스원);4. temp 사용량 조회
select tablespace_name,
round(allocated_space/1024/1042,2) as alloc_mb,
round(decode(sign(allocated_space-free_space), -1, 0,
allocated_space-free_space)/1024/1024,2) as used_mb,
round(free_space/1024/1024,2) as free_mb,
round(decode(sign(allocated_space-free_space), -1, 0,
allocated_space-free_space)/allocated_space*100,2) as "used(%)"
from dba_temp_free_space;
-- 쉘 프로그램
#!/bin/dash
while [ 1 ]
do
clear
sqlplus -s system/oracle << _eof_
set pages 1000
set lines 1000
col tablespace_name format a20
select tablespace_name,
round(allocated_space/1024/1042,2) as alloc_mb,
round(decode(sign(allocated_space-free_space), -1, 0,
allocated_space-free_space)/1024/1024,2) as used_mb,
round(free_space/1024/1024,2) as free_mb,
round(decode(sign(allocated_space-free_space), -1, 0,
allocated_space-free_space)/allocated_space*100,2) as "used(%)"
from dba_temp_free_space
/
_eof_
sleep 1
done-> temp 사용량 증가, temp2 사용량은 증가하지 않음. index나 table 소유자의 temp를 사용하지 않고, 실행자(server process가 결정)의 temp 영역을 사용하기 때문.
[실습 – temp 용량 관리]
1. datafile 추가
select * from dba_temp_files;
alter tablespace temp2 add tempfile '/home/oracle/oradata/db1/temp02_2.dbf' size 10m;2. resize
alter database tempfile '/home/oracle/oradata/db1/temp02_2.dbf' resize 50m;3. autoextend on
alter database tempfile /home/oracle/oradata/db1/temp02_2.dbf' autoextend on;[실습 – temp 테이블스페이스 삭제]
1. 삭제 시도
drop tablespace temp2 including contents and datafiles;2. default temp 테이블스페이스 변경
alter database default temporary tablespace temp;
-- 또는
alter user scott temporary tablespace temp;3. 세션별 temp 사용 현황 확인
SELECT s.sid,
s.serial#,
s.username,
s.program,
su.tablespace,
'alter system kill session' ||' '''|| s.sid ||','|| s.serial# ||''' ;' as "kill_ddl"
FROM v$session s JOIN v$sort_usage su
ON RAWTOHEX(s.saddr) = RAWTOHEX(su.session_addr)
WHERE su.tablespace = 'TEMP2';
-- 결과로 나오는 alter system kill session query 수행하기.
-- orange도 세션이니깐 꺼주기.
-- 처음엔 orange를 안 꺼서 무한대기만 되고 drop이 안됐었음.4. 삭제 재시도
drop tablespace temp2 including contents and datafiles; [참고 – dblink 생성(db1->orcl)]
1. db1서버의 tnsnames.ora 파일에 orcl 정보 기록
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.13.16)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
** 192.168.13.16는 접속할 db서버(windows의 oracle11)의 ip
** 1521은 접속할 db서버(windows)의 리스너 port(cmd -> lsnrctl status로 확인 가능)
2. db1 서버 db에 dblink 생성
create public database link dblink_orcl
connect to scott identified by oracle
using 'orcl';
drop database link dblink_orcl;3. orcl 데이터 조회
select *
from price@dblink_orcl;4. db1서버에 orcl의 delivery 테이블 복제
create table scott.price
as
select * from delivery@dblink_orcl;[archive log mode]
아카이브 로그 파일을 생성하는 모드.
redo log file 내용 유실 방지를 위해 미리 복사본(archive log) 생성.
archive hang이 안 발생하도록 하려고 log switch 발생 시 archive log file 생성됨.
1. archive log mode 확인
> archive log list2. archive log mode 전환(mount 단계에서 가능)
mount단계에서 수정하는 이유는 archive log mode에 대한 내용은 control file에 저장되어 있어서.
archive log file 저장하는 경로는 spfile에 들어가있지만.
그래서
1) archive directory 생성
$ su - root
$ mkdir /arch
$ chown oracle:dba /arch
$ chmod g+w /arch
$ exit2) archive directory parameter 변경 (동적 변경 불가)
select *
from v$parameter
where name like '%arch%';
alter system set log_archive_dest_1 = 'location=/arch' scope=spfile;3) db shutdown & startup mount
shutdown immediate
startup mount4) archive log mode로 전환
alter database archivelog;5) DB open 후 확인
alter database open
archive log list;3. archive log file 생성되는지 확인
> alter system switch logfile;
> / -- sqlplus에서 방금실행한 명령어 또 실행하는 명령어.
> /
> /
> !ls -l /arch -- file 생긴 거 확인[flashback 기능]
물리적 손상이 아닌 사용자의 작업 실수(잘못된 데이터 삭제=논리 장애)로 인해 데이터에 문제가 생겼을 경우 빠르게 복구할 수 있는 기능.
1. 확인
select name,
log_mode, -- archive log mode 확인
flashback_on -- flashback mode 확인
from v$database;2. flashback 기능 활성화 (동적 변경 불가)
전제조건 : archive log mode 활성화.
select name, value
from v$parameter
where name in ('db_recovery_file_dest','db_recovery_file_dest_size');
-- flashback 관련 파라미터 수정(동적 변경 불가)
alter system set db_recovery_file_dest='/arch' scope=spfile;
alter system set db_recovery_file_dest_size=5g scope=spfile;
-- db shutdown & startup
shutdown immediate
startup
-- 기능 활성화
alter database flashback on;
-- 확인
select name,
log_mode, -- archive log mode 확인
flashback_on -- flashback mode 확인
from v$database;[DB 복구]
1. flashback
– 현재에서 과거로 돌아가는 느낌.
– 보다 빠른 복구 지원.
– 논리 장애 복구 지원(물리적 장애 복구 불가)
– 조건 : archive log mode, flashback 기능 활성화
– 모든 변경 내용이 redo와 undo에 저장되어 있으므로 redo와 undo 내용을 참고하여 복구 진행. 따라서 일부 오래된 장애는 복구 불가할 수 있음.
– 종류 : row/table/database level
– archive log mode 가 필요한 이유는 redo, undo에 내용이 없을 때 좀 더 장기적으로 복구를 도와주기 위해서. 만약 redo, undo에 내용이 없다면 dbms가 자동으로 archive log file을 확인해서 flashback 해준다.
– 여기까지가 이론적인 메뉴얼적인 내용이고, 실제로는 지나간 archive log file을 사용하려면 사람이 미디어 리커버리 관련한 명령어를 직접 쳐줘야 하기 때문에 그렇게 되면 간단하게 복구할 수 있는 flashback의 개념과 멀어진다. 그래서 사실은 오래된 장애는 flashback으로 복구 불가능하다가 맞는 말임.
- row level : 특정 행에 대한 이전 이미지를 참고하여 복구 진행.
- table level : delete, drop 시
- database level : truncate 시
-> DDL 을 쓰면 테이블 구조가 변하는 거라 그 이전 구조의 스냅샷을 저장하고 있지 않는다. 따라서 이전 구조로 돌리고 싶으면 DB 내려서 복구하고 다시 DB올려야됨.
2. recovery
– 현재에다가 미래(archive log file)를 붙이는 느낌.
– archive와 현재 redo를 참고하여 논리, 물리 모두 복구 진행.
– 조건 : archive log mode, 올바른 백업본 존재.
– 종류 : datafile, database level
[row level flashback]
특정 row에 대한 장애가 발생했을 경우 이전 이미지를 참고하여 복구하는 방식.
redo와 undo에 대한 과거 이미지를 참고.
1. flashback version query
변경이력 조회.
undo 데이터 참고.
2. flashback transaction query
undo, redo 이미지를 참고하여 특정 시점으로의 복구를 위한 dml 제공.
supplemental logging 기능 활성화 되어있어야 함.
하지만 supplemental logging의 부하가 크기 때문에 현업에선 안 쓴다.
[실습 – 잘못된 update 복구]
0. supplemental logging 활성화 여부 조회
select name,
log_mode,
supplemental_log_data_min, -- supplemental logging 기능 활성화 여부
flashback_on
from v$database;1. supplemental logging 기능 활성화
transaction query(변경 DML 조회)를 얻기 위해서.
DML이 추출된다. = 나중에 DB migration 시에 CDC(change data capture)(ex. ogg) 때도 활성화함.
alter database add supplemental log data;2. 테이블 생성 및 데이터 입력/수정
create table flashback_test(
no number,
name varchar2(20),
sal number);
insert into flashback_test values(1000,'홍길동',1000);
insert into flashback_test values(1001,'김길동',2000);
commit;
update flashback_test set sal = 2000 where no = 1000;
commit;
update flashback_test set sal = 3000 where no = 1000;
commit;
update flashback_test set sal = 0 where no = 1000;
commit; -- 잘못된 update 상황3. 변경 이력 조회 (version query)
찾고 싶은 테이블명을 쓰고 versions로 scn정보를 제공하여서 version query.
select versions_startscn,
versions_endscn,
versions_xid,
versions_operation,
sal
from flashback_test versions between scn minvalue and maxvalue
where no=1000
order by versions_startscn;4. 변경 dml 조회 (transaction query)
flashback_transaction_query라는 뷰가 따로 있고 그곳에서 찾는다. supplemental logging 기능 활성화 상태를 요구.
select table_name,
start_scn,
operation,
undo_sql
from flashback_transaction_query
where table_name='FLASHBACK_TEST'
order by start_scn;5. 복구 진행
update "SYSTEM"."FLASHBACK_TEST" set "SAL" = '3000' where ROWID = 'AAASY5AABAAAZa5AAA';
commit;
select * from FLASHBACK_TEST;[table level flashback]
너무 많은 row에 대한 논리장애 발생 시 복구 방식.
시점 복구 진행(논리장애 발생 시점 이전의 데이터 확인 -> 복구)
step1) snap shot data query : 장애 이전 시점에 이전 이미지를 확인. 복구할 데이터 선별하여 반영하는 방식.
step2) flashback table 명령어 : 이전 이미지를 참고하여 테이블 전체를 특정 시점의 과거 데이터로 돌려놓음. row movement 기능 활성화가 요구됨.
[실습 – delete 장애 복구]
1. flashback 기능 활성화 여부 확인
select flashback_on
from v$database;2. test table 확인
select count(*) -- 119189
from scott.delivery;3. 복구 시점(장애 발생 전) 확인
select sysdate from dual; -- 2024/12/03 16:15:394. 장애 발생시키기
delete from scott.delivery;
commit;5. 장애 발생 이전 시점 데이터 조회
select count(*) -- 119189
from SCOTT.DELIVERY
as of timestamp to_date('2024/12/03 16:15:39', 'YYYY/MM/DD HH24:MI:SS');6. 복구 진행 (현업에서는 table을 따로 생성해서 개발자에게 준다)
insert into SCOTT.DELIVERY
select *
from SCOTT.DELIVERY
as of timestamp to_date('2024/12/03 16:15:39', 'YYYY/MM/DD HH24:MI:SS');
commit;[실습 – delete 장애 복구(flash back table)]
1. flashback 모드 확인
select name, flashback_on from v$database;2. 테이블 생성 및 데이터 입력
create table flashback_test2
(no number,
name varchar2(20),
sal number);
insert into flashback_test2 values(1000,'홍길동',1000);
insert into flashback_test2 values(1001,'김길동',2000);
insert into flashback_test2 values(1002,'박길동',3000);
commit;3. 시점 확인
select sysdate from dual; -- 2024/12/04 11:00:484. 장애 발생
delete flashback_test2;
commit;
select * from flashback_test2;5. flashback 복구 시도
select *
from flashback_test2
as of timestamp to_date('2024/12/04 11:00:48', 'YYYY/MM/DD HH24:MI:SS');
-- 방법1)
flashback table flashback_test2 to timestamp
to_date('2024/12/04 11:00:48', 'YYYY/MM/DD HH24:MI:SS');
-- 방법2)
flashback table flashback_test2 to timestamp(systimestamp-interval '1' hour);
-- ORA-08189: cannot flashback the table because row movement is not enabled
-- row movement 비활성화로 복구 불가6. row movement 활성화(테이블 단위)
select table_name, row_movement
from dba_tables
where table_name = 'FLASHBACK_TEST2';
alter table flashback_test2 enable row movement;7. flashback 복구 재시도
즉시 반영됨. commit 필요없음.
-- 방법1)
flashback table flashback_test2 to timestamp
to_date('2024/12/04 11:00:48', 'YYYY/MM/DD HH24:MI:SS');
-- 방법2)
flashback table flashback_test2 to timestamp(systimestamp-interval '1' hour);
select * from flashback_test2;
-- 복구 완료.다중화 대상
redo log file, control file, archive log file
보통 archive log file은 한 dest로만 지정해놓고 받자마자 다른 저장공간에 백업해놓고 그 dest에 있던 archive file은 바로 삭제한다. 그러면 해당 공간이 꽉 차서 archive hang이 생길 일이 없고, archive file도 백업 잘 되고, 싼 저장공간(ex 테이프?)에다 보관하면 비용절감도 된다.