■ 9장. lock 메커니즘
▣ 예제77. 락(lock) 이 database 에서 왜 필요한지 알아야해요.
■ 락(lock) 이 걸리는 때
update emp
set sal = 0
where ename = 'KING'; --- 행에 lock 을 건다 (tm)
- update 문을 수행하면 ---- > 1. TX (Transaction Exclusive) : 해당 row 에 거는 락
2. TM (Transaction Management) : 테이블에 거는 락
update emp
set sal = 9000
where ename = 'ALLEN'; ----- 락에 걸리지 않음
상황 : A 세션 에서는 KING 의 월급을 0으로 변경하고
B 세션에서는 ALLEN 의 월급을 9000 으로 변경한다.
서로 다른 행을 갱신할 때는 LOCK 이 안걸립니다.
Exclusive : 독점적 이라는 것은 king 의 행에 대해서만 독점적이라는 것.
KING 의 행 만 갱신 못할 뿐, 'ALLEN 의 행은 갱신한다.
상황 2: A 세션에서는 KING 의 월급을 0으로 변경하고
B 세션에서는 KING 의 부서번호를 30 으로 변경한다.
락이 걸린다. KING 의 행에 락이 걸리기 때문에.
상황3 : A 세션에서는 KING 의 월급을 0 으로 변경하고
B 세션에서는 emp 테이블을 drop 하려고 한다.
resource busy and acquire~ 에러가 발생하면서 안된다.
왜냐하면 drop 하지 못하도록 A 세션이 TM 락을 걸었기 때문이다.
정리하면 update 문을 수행하면 두가지 락 모두 걸리는데
- TX (Transaction Exclusive) : UPDATE 하려는 row 에 거는 락
- TM (Transaction Management) : UPDATE 하려는 테이블에 거는 락
상황 4 : A 세션이 emp 테이블을 select 하고 있다.
B 세션이 emp 테이블을 drop 하려고 한다.
중간에 drop 된다. select 를 할 때는 lock 을 걸지 않기 때문에.
lock 는 update
TX LOCK 이 걸리는 경우 : update
TM LOCK 이 걸리는 경우 : insert, update, delete, merge
락이 필요한 이유 : 동시에 여러 사용자가 같은 데이터를 수정 할 때 발생할 수 있는 문제들을 해결하기 위함.
▣ 예제78. dead lock 이 뭔지 알아야 해요
1 에서 dead lock 감지 - 위에 실행한 update 문은 실행되지 않는다.
update emp
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
A 세션이 아래의 에러 메세지를 보면서 update 문이 실패하게 됩니다.
ORA-00060: deadlock detected while waiting for resource
그러면 누군가 lock waiting 하고 있는 상황이 되고 있다는 것이므로 commit 이나 rollback 을 해줘야 합니다.
※ 우리 회사에 datebase 에서 dead lock 이 많이 발생하면서 작업이 안되고 있다면 dba 가 이 상황을 해결해야한다.
어떻게 해결할 것인가 ?
팀 별로 특정 테이블에 update 하는 시간대를 나눈다.
dba 는 수시로 alert log file 을 보면서 이 dead lock 이 발생하고 있는지 확인 해야한다
문제 1. PROD DB 의 alert log file 을 열어서 dead lock 에러 메세지가 있는지 확인하시오
sys 로 접속
PROD(SCOTT) > connect / as sysdba
Connected.
PROD(SYS) > show parameter background
이 주소 중요함 : /u01/app/oracle/diag/rdbms/prod/PROD/trace
PROD(SYS) > exit;
나간다
[PROD:trace]$ ls -l alert
-rw-r----- 1 oracle dba 147226 2월 15 10:21 alert_PROD.log
[PROD:trace]$ grep 'dead' alert
[PROD:trace]$ grep -i 'dead' alert*
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_6141.trc.
vi 창을 열고 /update ( 검색기능 )
[PROD:trace]$ vi /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_6141.trc
lock 메커니즘의 기본값은 하위단계의 행 레벨 lock 모드 이다
-참
하위레벨의 락 / 상위 레벨의 락이 있음.
하위레벨의 락 : 행(row)
상위레벨의 락 : table
lock table emp in exclusive mode; 가장 높은 레벨의 락.
ocp 시험 문제 틀린 답 : 하위 레벨의 락이 상위 레벨의 락으로 자동으로 올라간다 escalate : 틀린말
한 세션에서 오류가 있는 명령문 한 개만 종료 됨. (실패했다)
■ 10장. updo 관리
빅데이터 환경에서 db 작업 시 반드시 알고 있어야하는 중요한 내용
▣ 예제79. undo data 가 무엇인지 알고 있어야 해요
undo : 취소하다
수정되기 전 원래 데이터
rollback 을 하기 위한 데이터
update 하면 수정되기 이전의 데이터가 모두 저장됨
commit 이나 rollback 을 할 때 까지 보존된다.
A 세션 COMMIT 전, B 세션에서 KING 의 SAL 은 5000 으로 select 된다.
Flashback - emp 테이블을 전부 지우고 commit 을 했어도 Flashback 을 할 수 있는 이유는
undo segment 가 지운 데이터를 가지고 있기 때문이다.
기본적으로 15분 동안 보관하고 있다.
시간은 늘릴 수 있다.
실패한 transaction recovery
▣ 예제80. undo data 와 redo data 차이
-undo data : 취소를 하기 위한 data
방금 한 행동을 없애고 싶을 때
-redo date : 복구를 하기 위한 data
방금 안좋게 된 것을 복구하고 싶을 때.
KING 의 월급을 5000 에서 0 으로 변경하고 아직 COMMIT 를 하지 않은 상태입니다
그리고 이 상태에서 dbwr 메모리의 dirty buffer 를 table segment 에 내려썼고
undo buffer 의 내용을 undo segment 에 내려썼습니다.
그리고 lgwr 는 이미 king 의 데이터를 변경했다는 redo data 를 redo log file 에 내려 쓴 상태입니다
이 상태에서 갑자기 shutdown abort 를 했다면 king 의 data 는 어떻게 복구될까 ?
redo log file 에 redo data 도 있고 undo segment 에 undo data 도 있으므로 원래 5000으로 완전히 복구할 수 있습니다.
※ dba 를 위한 팁 ! 걱정 말고 shutdown abort 하세요 자동으로 복구 되니까 !!
shutdown immediate 를 주로 쓰세요...
■ 실습
- 나의 undo tablespace 가 무엇인지 확인합니다.
undo_tablespace string UNDOTBS
- 지금 undo tablespace 에 활성화된 undo segment 가 몇 개 있는지 확인합니다.
select * from v$rollname;
- undo segment 가 어느 테이블 스페이스에 있는 지 확인합니다.
select segment_name, status, tablespace_name from dba_rollback_segs;
- 새로운 undo tablespace 생성
왜 새로운 undo tablespace 를 만드는가 ?
지금 임시로 대량의 DML 작업을 수행해야 합니다
기존 UNDO TABLESPACE 로는 못버틸 것 같다.
그럴 때 크게 하나 만들어주고 사용하면 됩니다.
데이터 마이그레이션 얘기하심 !!!!
포트폴리오 제출 해야함 ㅠㅠ !!!
하다가 여러번 실패할거야 언두가 작으면 실패할거야
언두를 크게 만들어야돼 !!
create undo tablespace undotbs2
datafile '/home/oracle/undotbs3.dbf' size 50m;
5/. 현재 사용할 undo tablespace 를 4번에서 생성한 undo tablespace 로 지정합니다.
- 현재 활성화된 undo segment 가 어느 테이블 스페이스에 있는지 조회한다
select segment_name, status, tablespace_name from dba_rollback_segs;
점심시간문제 다시 새로운 undo tblae undotbs3 생성
create undo tablespace undotbs3
datafile '/home/oracle/undotbs4.dbf' size 50m;
show parameter undo_tablespace
alter system set undo_tablespace=undotbs3 scope=both;
show parameter undo_tablespace
select segment_name, status, tablespace_name from dba_rollback_segs;
- undo data 와 redo data 가 어떻게 다른지 책에 나온 설명
롤포워드 :
commit 을 했는데 disk 에 반영 안됨
메모리에서 사라진 data 복구하는 것
▣ 예제81. 언두 data 관리는 자동으로 되고 있어요
자동 언두 관리
ORA=01333 snapshot too old : undo 관리를 잘못했을 때 발생하는 에러
undo 관리가 다 자동화되고 있으므로 예전처럼 undo 관련해서 많은 오류는 발생하지 않습니다.
dba 는 딱 3가지만 잘 세팅해두고 undo 관리를 하면 된다.
3가지
- undo_management
- undo_retention : dba 가 설정함
- undo_tablespace
.
■ 실습 :
- undo_retention 을 1시간 (3600초) 으로 변경합니다.
alter system set undo_retention=3600 scope=both;
- 잘 변경 되었는지 확인합니다.
...
▣ 예제82. undo_retention 을 1시간으로 변경했다고 해서 그것을 확실히 보장하지는 않아요
기본값은 보장하지 않습니다.
.보장하게끔 다음과 같은 명령어를 써야 합니다.
SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
select tablespace_name, retention from dba_tablespaces;
alter tablespace undotbs retention guarantee;
select tablespace_name, retention from dba_tablespaces;
문제 undotbs7 을 사이즈 50 으로 생성하고 undotbs7을 현재 사용할 undo tablespace
로 지정한후 undo retention 에 나온데로 시간을 보장할 수 있도록 guarantee
하게 하시오 !
create undo tablespace undotbs7 datafile '/home/oracle/undotbs6.dbf' size 50m;
alter system set undo_tablespace=undotbs7 scope=both;
select tablespace_name, retention from dba_tablespaces;
alter tablespace undotbs7 retention guarantee;
select tablespace_name, retention from dba_tablespaces;
※ 쿠팡처럼 지금 실시간으로 주문이 들어오는 insert 문이나 update 문이 실패하지 않게 하려면
위와 같이 guarantee 하면 안됩니다.
사용 가능한 공간보다 더 많은 언두를 생성하는 트랜잭션이 나오게 되고 이를 실패하게 되니깐.
그럼 언제 guarantee 를 해야하는가 ?
select 문장 실행 시 ora-01555 snap shot too old 에러가 안나는게 중요한 경우
▣ 예제83. ora-01555 snap shot too old 에러
guarantee 가 있거나 없을때 select 가 되거나 update 가 되거나 둘 중 하나로 결정이 된다.
ora-01555 snap shot too old 에러 발생
※ ora-01555 snap shot too old 에러 란 ?
긴 시간으로 수행되는 select 문장이 실패할 때 발생하는 오류인데
내가 select 하기 이전에 commit 되었던 데이터를 봐야하는데
그 데이터가 누군가에 의해서 덮어써져서 없어졌을때 발생하는 오류입니다.
이 에러가 발생 하지 않게 하려면
: undo tablespace 의 retention 을 guarantee 하면 된다.
dba 는 우리 회사에서 가장 길게 도는 쿼리문의 사간을 알고 있어야 합니다.
만약 한시간이라고 한다면 undo_retention 을 한시간 (3600초) 로 설정하고
undo tablespace 를 retention 을 guarantee 하면 된다.
■실습. ORA-01555 snap shot too old 에러를 일으켜 보시오 !
#1. 10m 짜리 언두 테이블 스페이스를 생성합니다.
create undo tablespace small_undo
datafile '/home/oracle/small_undo.dbf' size 10m autoextend off;#2. 10m 언두 테이블 스페이스를 현재 사용중인 undo tablespace 로 변경합니다.
alter system set undo_tablespace = small_undo;
#3. 테이블을 생성하고 데이터를 10000 건을 입력합니다.
create table t1(c1 int, c2 char(300));
insert /*+ append */ into t1
select level, 'dummy'
from dual
connect by level <= 10000
;
commit;
#4. 인덱스를 생성합니다.
create index t1_n1 on t1(c1);
#5. 사용자 정의 함수를 생성합니다.
create or replace function fsleep(v1 int, vsleep int)
return number
is
begin
dbms_lock.sleep(vsleep);
return 1;
end;
/
6. 버퍼 캐쉬 사이즈를 1m 로 변경합니다.
alter system set db_cache_size = 1m;
#7. 위와 같이 환경구성을 하고 아래의 쿼리를 수행한다.
select /*+ index(t1) */
c1, substr(c2,1,10)
from t1
where fsleep(c1, 0.01) = 1
and c1 > 0
;
-- session 2 -------
1. 현재 언두 세그먼트가 뭐가있는지 확인합니다.
-- get rollback segment name
col rollback_seg new_value v_rollback_seg
select '_SYSSMU'||max(segment_id)||'$' as rollback_seg
from dba_rollback_segs
where segment_name <> 'SYSTEM'
;
-- do very frequent commit on t1#2. t1 테이블에 update 를 계속 반복
begin
for idx in 1 .. 1000 loop
for idx2 in 1 .. 10000 loop
set transaction use rollback segment "&v_rollback_seg";
update t1 set c2 = 'dummy'||idx
where c1 = idx2;
commit;
end loop;
end loop;
end;
/
▣ 예제84. Undo Advisor 를 이용해서 undo tablespace 의 적정 크기를 알아내세요
dba 가 undo 관련해서 설정해야할 3가지 ?
- undo_management 는 무조건 auto 로 설정합니다
- undo_retention 은 우리 회사에서 가장 길게 도는 쿼리문의 시간을 기준으로 설정
- undo tablespace 의 크기는 undo advisor 를 이용해서 알아냅니다.
undo tablespace 의 retention 을 guarantee 해줘야 합니다.
메모리에도 있고 disk 에도 있다
언두버퍼 언두세그먼트에
.
.
■ 11장. 감사
데이터베이스 보안
보안 시스템은 내부 데이터의 기밀성을 보장합니다.
보안
1. 팀 , 직원
2. 보안 솔루션
인터넷 안되는 pc 로 db 에 접속
오라클에서 확실하게 dba 가 막아버릴 수 있음 !! 보안 !!
누군가 db 에 접속해서 특정 data 를 select 를 하게 되면 다 감사(audit) 에 걸려서
db 에 로그가 남게 됩니다.
그러므로 특정 데이터를 select 할 때 업무적인 목적 외에 하면 안된다.
sys 유져에서 실행
no rows selected
나오게끔 설정하겠습니다.
▩ db 감사 (p 11-7)
- 특정 테이블을 DROP 하고 SELECT 했을때 감사
- 로그인과 로그오프 할 때 감사실습1. 감사 기능을 활성화 시킵니다.
SYS @ orcl > show parameter audit_trail
PROD(SYS) > alter system set audit_trail=db scope=spfile;
PROD(SYS) > shutdown immediate
PROD(SYS) > startup
PROD(SYS) > alter session set nls_date_format='RRRR/MM/DD HH24:MI:SS';
select username, timestamp, action_name, terminal
from dba_audit_trail
where username ='SCOTT' and timestamp LIKE '2024%'
order by timestamp;
★ 감사 옵션 지정:
1. audit table; -> 테이블을 drop 하면 감사됩니다.
2. audit select on scott.emp; -> scott 의 emp 테이블을 select
할 때 마다 감사
3. audit create any trigger; -> 트리거를 생성하면 감사합니다.
4. audit select any table by hr by session;
--> hr 계정에 의해서 특정 테이블이 select 되면 감사하겠다. ※ 감사 옵션을 주고 감사 기능을 활성화 할 때 주의할 사항 !
감사로드가 쌓이는 공간이 system tablespace 인데 system tablespace 가
금방 full 나 버리기 때문에 full 나지 않도록 여유공간 확보하고 해줘야합니다.
실습2. 테이블 감사를 실습합니다.
-
SYS> audit table;
-
SCOTT> drop table emp;
-
sqldeveloper>
select username, timestamp, action_name, terminal
from dba_audit_trail
where username ='SCOTT' and timestamp LIKE '2024%'
and action_name like '%DROP%';
오늘의 마지막 문제:
사이즈 100m 로 undo tablespace 를 생성하고 undo tablespace 이름은 undotbs9
라고 합니다. 그리고 이 undo tablespace 를 현재 undo tablespace 로 지정합니다.
undo retention 을 30분으로 설정합니다.
show parameter undo 했을 때 나오는 화면을 캡쳐해서 올리세요 ~~
create undo tablespace undotbs9
datafile '/home/oracle/undotbs9.dbf' size 100m;
alter system set undo_tablespace=undotbs9 scope=both;
900 이 15분 이니까 1800 로 설정
alter system set undo_retention=1800 scope=both;
alter tablespace undotbs9 retention guarantee;
