🤔 dead lock이란 무엇인가?
A session (SCOTT) B session (SCOTT)
1. update emp
set sal = 9000
where ename='SCOTT'; 2. update emp
set sal = 0
where ename='ALLEN';
*행에 lock을 검
3. update emp
set job='aaaa'
where ename='ALLEN';
2번에 락걸려있는데 update해서 락 웨이팅 됨
4. update emp
set job='bbbb'
where enam='SCOTT';위경우 양쪽다 락이 걸린다.
ORA-00060: deadlock detected while waiting for.....
위 에러메세지가 나면 DBA는 커밋이나 롤백을 하라고 권고해야한다. 그리고 alert log file을 분석해야 한다. 만약 너무 많이 데드락이 있으면 시간을 나눠서 하라한다.[orcl:~]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace [orcl:trace]$ vi alert*✅ 하루에 몇건 있다고 얘기 해야한다. 숫자로 뽑아내기
문제 deadlocl detected 메세지가 alert log file에서 몇번 출현했는지
[orcl:trace]$ grep -i 'dead' alert* <-'dead'가 들어간 문장 출력
[orcl:trace]$ grep -i 'dead' alert* | wc -l
0
[orcl:trace]$ grep 'ORA-00060' alert* | wc -l
0 <- 0건✅ dba가 운영팀에 이야기 해줘야 한다. emp table을 동시에 update하게되면 데드락 발생 확률이 높으니까 시간대를 나눠서 update하세요
💡 alert log file에 dead lock과 관련한 update문이 있는 trace file을 알려준다.
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_15011.trc.
💡 나는 없는데 혹시 dead lock이 있는 경우 거기 경로가 나온다. 그 경로로 이동해서 해당 파일을 vi로 열면, sid 번호도 알 수 있고 어떤 테이블을 조회했는지, 어떤 쿼리문때문에 dead lock이 걸린건지 알 수 있다.
💟 alert log file 관련해서 dba를 위한 tip !
- alias 를 이용해서 alert log file이 있는 위치로 빠르게 갈수 있도록 설정하기
- alias는 컴퓨터를 끄면 사라져서 매일 만들어줘야한다. 계속 남아잇게 하려면? oracle이 접속될 때 마다 항상 강제로 수행되는 스크립트인 .bash_profile에 등록해준다.
$ alias trace='cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace'
💡 .bash_profile에 등록
$ cd
$ vi .bash_profile
여기 맨 아래에 아래 등록하고 저장하기
alias trace='cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace'
이후에
[orcl:~]$ source .bash_profile
이렇게 한번 돌려준다. trace file 이란?
alert log file
위치 확인하기
SQL> show parameter background_dump_destorcl_ora_23061.trc
↑ ↑
인스턴스이름 프로세서 번호✅ 인스턴스 이름 확인
SQL> select instance_name from v$instance;
⭐ 19c OCP문제!
A,C
ADR (Automatic Diagnostic Repository) p.4-41
💡 오라클에서 발생하는 모든 에러에 대한 로그 정보를 한군데서 관리하겠다 라고 만든 Repository
1. database -> 에러메세지(database, instance)
2. network -> 에러메세지(listener)
3. storage -> 에러메세지(ASM)
- ADR의 위치 확인하는 방법
SQL> show parameter diagnostic_destSQL> exit;
[orcl:~]$ cd /u01/app/oracle
[orcl:oracle]$ ls
admin cfgtoollogs checkpoints diag product
[orcl:oracle]$ cd diag
[orcl:diag]$ ls
asm rdbms tnslsnr
↑ ↑ ↑
스토리지 database 네트워크💡 오라클에 문제가 있을 때 쉽게 문제를 확인할 수 있도록 만든 툴 adrci
[orcl:diag]$ adrci
ADRCI: Release 11.2.0.1.0 - Production on Fri Sep 8 11:12:01 2023
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
adrci>
adrci> help;
adrci> show problem
ADR Home = /u01/app/oracle/diag/asm/+asm/+ASM:
*************************************************************************
0 rows fetched
ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl: -> database 관련
*************************************************************************
0 rows fetched
ADR Home = /u01/app/oracle/diag/tnslsnr/edydr1p0/listener:
*************************************************************************
0 rows fetched
✅ help 치면 나오는 명령어들. 여기서 show problem을 했다.
➡️ metalink.oracle.com <- 오라클과 유지보수 계약을 맺고있으면 이 아이디를 준다. 전세계의 모든 오라클 이슈들과 해결방법을 게시판 형태로 볼 수 있다.
[orcl:~]$ cd $ORACLE_BASE
[orcl:oracle]$ pwd
/u01/app/oracle
[orcl:oracle]$ show parameter diagnostic_dest
-bash: show: command not found
[orcl:oracle]$ cd $ORACLE_HOME
[orcl:dbhome_1]$ cd log
[orcl:log]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/logTrace file 사용(계속)
critical 오류가 발생하면 incident 번호가 오류에 할당되고 Trace file 등 오류의 진단 데이터가
즉시 캡처되며 이 번호로 태그가 지정됩니다. 그런 다음 데이터는 데이터베이스 외부에 있는
파일 기반 Repository인 ADR(Automatic Diagnostic Repository)에 저장되므로 나중에 incident
번호별로 검색하여 분석할 수 있습니다.
ADR은 Trace, Alert Log, 상태 모니터 보고서 등 데이터베이스 진단 데이터를 저장하는
데 사용하는 시스템 중앙 추적 및 로깅 Repository입니다.
ADR 루트 디렉토리는 ADR 기준이라고도 하며, 해당 위치는 DIAGNOSTIC_DEST 초기화
파라미터에 의해 설정됩니다. 이 파라미터를 생략하거나 null로 유지하면 데이터베이스가
다음과 같이 시작 시에 DIAGNOSTIC_DEST를 설정합니다.
• ORACLE_BASE 환경 변수를 설정하는 경우 DIAGNOSTIC_DEST는 ORACLE_BASE가
지정하는 디렉토리로 설정됩니다.
• ORACLE_BASE 환경 변수를 설정하지 않은 경우 DIAGNOSTIC_DEST는
ORACLE_HOME/log로 설정됩니다.
ADR 홈의 위치는 ADR 기준 디렉토리에서 시작되는 다음 경로에 의해 지정됩니다.
./diag/product_type/db_id/instance_id
위 관련 19c OCP문제
💡오라클을 진단하기 위해 확인해야하는 파일과 사전
1. alert log file : 주요 장애 이슈 에러 번호
2. trace file : 상세한 에러 메세지와 이유
3. ADR : DB, ASM, NETWORK 관련 에러 로그 확인
4. dba_outstanding_alerts 사전 : 현재 db에 주목할만한 큰 이슈를 확인
dba_outstanding_alerts 을 쉽게 모니터링 할 수 있게 쉘 스크립트 작성
1. risk2.sh를 만들기
$ vi risk2.sh
sqlplus -s sys/oracle_4U as sysdba << EOF
set lines 5000
set pages 4000
set heading off
set feed off
select to_char(systimestamp, 'HH24:MI:SS'),
reason from dba_outstanding_alerts;
EOF2. while loop 문을 이용해서 risk2.sh가 계속 수행되게 하시오
$ vi risk.sh
#!/bin/bash
echo ":::::::::::::::::::::::::::::::::::::::::::::::::::::::"
echo " risk monitoring ........."
echo ":::::::::::::::::::::::::::::::::::::::::::::::::::::::"
echo ""
while [ 1=1 ];
do
sh ./risk2.sh
sleep 5
done;이렇게 하면 5초마다 한번씩 나올것.
sysdba로 들어가서 테이블스페이스 만든다
scott으로 들어가서 테이블 생성
SQL> connect / as sysdba
Connected.
SQL>
SQL>
SQL>
SQL>
SQL> create tablespace ts01 datafile size 10m;
Tablespace created.
SQL>
SQL> connect scott/tiger
Connected.
SQL> create table emp05
( empno number(10),
ename varchar2(10) )
tablespace ts01;
Table created.
SQL> insert into emp05 values(333,'aaa');
1 row created.
SQL> insert into emp05
select *
from emp05;
SQL> / 삭제했다!
SQL> drop table emp05 purge;
Table dropped.논리적 및 물리적 데이터베이스 구조
논리적 단위
1. 데이터 베이스 : 가장 큰 저장 단위
오라클을 설치하고 database 를 먼저 생성을 합니다.
✔️ db이름 학인하기
select name from v$database;
➡️ db이름은 처음에 잘 생각해서 정해야합니다. 나중에 변경하기 어려움!
2. 테이블 스페이스(tablespace)
하나의 데이터베이스는 여러개의 테이블스페이프로 구성되어있다.
✔️ 테이블 스페이스를 조회합니다.(리눅스)
col tablespace_name for a15 col file_name for a45 select tablespace_name, file_name from dba_data_files order by tablespace_name;
문제 19c의 테이블 스페이스가 뭐가있는지 조회하기
❓ 오라클에서 테이블 스페이스라는 논리적 저장 단위를 왜 두었는가?
: 데이터가 많아지면 데이터를 별도로 구분해서 관리해야하기 때문에 필요하다!
ex) 집에 방이 여러개 있는것을 생각하면 됨!!
💡 테이블 스페이스의 종류
1. system예: data dictionary는 user_tables와 같은 db의 객체정보가 들어있는 view입니다. 오라클을 운영하기 위한 아주 중요한 데이터가 들어있기 때문에 만약 system tablespace가 손상되면 db운영이 안됩니다.2. sysaux : 오라클 성능 데이터가 들어있는 테이블 스페이스 입니다.
3. undotbs1 : 롤백을 위한 데이터가 들어있는 테이블 스페이스 입니다.(여기에 취소할 수 있는 데이터가 있기때문에 우리가 롤백하는 것이 가능한것이다.)
4. users : emp테이블과 같은 사용자 데이터가 들어있는 테이블 스페이스
5. examples (11g) : hr계정과, sh계정의 테이블들이 저장되어있는 테이블 스페이스✅ users까지 동일하고 examples만 예전에 있던것.
3. 세그먼트(segment)
emp테이블과 같은 저장 단위를 세그먼트라고 합니다.
하나의 테이블 스페이스에 여러개의 sgment가 존재합니다.
✔️ users테이블 스페이스에는 어떤 세그먼트가 있는지 리눅스에서 조회하기(리눅스)
col tablespace_name for 15
col segment_name for a25
select tablespace_name, segment_name
from dba_segments
where tablespace_name='USERS';
문제 리눅스에서 EXAMPLE테이블 스페이스에 어떤 세그먼트가 있는지 조회
col tablespace_name for 10 col segment_name for a20 select tablespace_name, segment_name, segment_type from dba_segments where tablespace_name='EXAMPLE' and segment_type='TABLE';
4. 익스텐트(extent)
💡 조각케이크 생각하기! 케이크가 세그먼트라면, 케이크의 조각 하나는 익스텐트이다.
실습1. ts01 테이블 스페이스에 emp02라는 테이블을 생성하기
SQL> connect scott/tiger
SQL> create table emp02
(empno number(10),
ename varchar(10),
sal number(10) )
tablespace ts01;
SQL> select segment_name, extent_id, bytes
from user_extents
where segment_name='EMP02';
#no rows selected ✅ create table 명령어 수행시 맨 아래에 tablespace 테이블스페이스 이름을 안쓰면 default tablespace에 테이블이 만들어집니다. default tablespace는 user아니면 example 입니다.
1. 방금 만든 emp02테이블이 어느 테이블 스페이스에 있는지 확인하는 쿼리문
select table_name, tablespace_name from user_tables where table_name='EMP02';
2. emp02 테이블 공간이 어떻게 되는지 확인하는 쿼리?
emp02테이블 크기 확인하는 쿼리문insert into emp02 values(1111,'scott',3000); select segment_name, extent_id, bytes/1024 --kb로 from user_extents where segment_name='EMP02';
- 64를 다 채우면 새로운 익스텐트가 또 할당된다.
문제 emp02테이블에 부지런히 데이터를 입력하고 새로운 익스텐트가 또 할당되는지 확인해보기
SQL> insert into emp02 select * from emp02; SQL> /
truncate table emp02; --- select segment_name, extent_id, bytes/1024 , blocks from user_extents where segment_name='EMP02';
✅ 하나의 익스텐트 안에 블럭이 8개가 있다. 하나의 인스텐트의 크기가 64kb인데 그렇다면 하나의 블럭의 크기는? 그러면64kb / 8해보기 정답은 8kb!
: 8kb의 block이 8개로 구성되어있는것이 익스텐트(하나)!
5. block
💡 오라클에 데이터를 저장하는 최소 저장단위! 이 블럭에 emp테이블과 같은 데이터가 순서대로 저장된다. 클럭의 크기를 확인하는 명령어는 다음과 같다.
SQL> show parameter db_block_size 
✅ block에 데이터가 저장되어 있는데 특정 블럭이 깨지면 특정 블럭의 데이터를 담고 있는 테이블을 select 할 수 없습니다. 그러면 dba, dba엔지니어가 복구를 해주어야 한다.
테이블스페이스 및 데이터 파일
✔️ 테이블 스페이스의 종류 2가지
1. big file tablespace : 하나의 테이블 스페이스에 하나의 데이터 파일만 저장할 수 있는데, 하나의 파일의 크기를 최대 128TB까지 생성할 수 있다.
2. small file tablespace : 하나의 테이블 스페이스에 여러개의 데이터 파일들을 저장할 수 있는 테이블 스페이스 입니다.
현업의 예: 하나의 데이터 파일의 크기가 보통 최대 10gb 보통 1gb로 구성합니다. 크기가 너무 크면 백업, 복구할 때 시간이 너무 많이듭니다 !
실습1. (19c윈도우) cuppang_oltp으로 테이블 스페이스를 생성하기
select file_name from dba_data_files; #데이터 파일들의 위치 조회
create tablespace cuppang_oltp datafile 'C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\cuppang01.dbf' size 100m; #Tablespace CUPPANG_OLTP이(가) 생성되었습니다.select tablespace_name, status from dba_tablespaces;
문제 19c에 테이블 스페이스를 생성하는데 테이블 스페이스 이름은 ts라고 하고 데이터 파일명은 ts01.dbf로 하고 사이즈는 100m로 생성
create tablespace ts datafile 'C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\ts01.dbf' size 100m;조회하기
select tablespace_name, status from dba_tablespaces;
실습2. 테이블 스페이스와 테이블 스페이스의 용량을 확인하는 쿼리문을 실행하시오
col tablespace_name for a10
col total_size for a10
col free_size for a10 #얘네는 디벨로퍼에서 안할때 예쁘게 보려고 하는 것!
select t.tablespace_name,
t.total_size,
f.free_size
from (select tablespace_name, sum(bytes)/1024/1024||'MB' total_size
from dba_data_files
group by tablespace_name) t,
(select tablespace_name, sum(bytes) /1024/1024||'MB' free_size
from dba_free_space
group by tablespace_name) f
where t.tablespace_name = f.tablespace_name;
✅ UNDOTBS1, USERS 테이블 스페이스가 여유공간이 얼마 없다. 공간을 늘려주어야 한다.
테이블 스페이스 공간 늘리기(공간 추가, resize)
💡 테이블 스페이스에 공간을 추가하는 방법은 두가지가 있다.
1. 테이블 스페이스에 새로운 데이터 파일을 추가
2. 테이블 스페이스에 기존에 존재하는 파일의 사이즈를 RESIZE하는 방법
1. 새로운 데이터 파일을 추가하기
실습3. UNDOTBS1에 공간을 추가하기(데이터파일 추가)
1. 확인을 먼저하기
--기존에 파일명을 확인한다. 왜 ? 새로 추가하는 데이터 파일이 --기존 파일명과 동일하면 안되기 때문에!!! select tablespace_name, file_name from dba_data_files where tablespace_name='UNDOTBS1'; # C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\UNDOTBS01.DBF # 기존 파일명이 UNDOTBS01 니까 UNDOTBS02로 추가해야한다.
2. 공간 추가하기alter tablespace undotbs1 add datafile 'C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\UNDOTBS02.DBF' size 10m;✔️ 근데 나는 C드라이브에 오라클을 깔아서 그냥 용량추가 안했다. 다음에 ......!
3. USERS도 추가하기
#1. 파일명 확인 select tablespace_name, file_name from dba_data_files where tablespace_name='USERS'; #C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\USERS01.DBF#2. 용량추가 alter tablespace undotbs1 add datafile 'C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\USERS02.DBF ' size 10m;
2. RESIZE
실습5. users 테이블 스페이스에 기존 파일인 users01.dbf의 사이즈를 더 크게 늘리시오
select tablespace_name, file_name, bytes/1024/1024/1024
from dba_data_files
where tablespace_name='USERS';
alter database datafile 'C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\USERS01.DBF'
resize 100m;
오늘의 마지막 문제 users 테이블 스페이스의 users02.dbf의 사이즈를 200m로 늘리기
alter database datafile 'C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\USERS02.DBF'
resize 200m;