DB Startup
$ sqlplus / as sysdba
로 DB에 들어가면
> select status from v$instance;
Open이 뜨지 않는다면 DB가 꺼져있는 것이다.
또는
$ ps -ef | grep pmon | grep -v grep
로도 DB 기동 상태를 확인할 수 있다.
DB를 키기 위해
> startup
후 다시 status 상태 확인
리스너가 켜져 있는지 리눅스에서 체크
$ ps -ef | grep lsnr | grep -v grep
꺼져있다면 (아무 출력 X)
$ lsnrctl start
로 리스너 실행시킨다.
리스너가 Services Summary… 메시지 뜰 때까지(정상 실행 상태)
$ lsnrctl status
로 상태 확인하면서 대기.
** lsnrctl 명령어 3가지
start, status, stop
리스너 실행된 거 확인되면 그제야 Orange로 붙을 수 있음
SGA(System Global Area)
서버 프로세스들이 공유하는 메모리 공간
parameter file에 SGA 구성 정보가 담겨있음
동적 변경 가능(spfile 환경일 경우)
자동 관리 가능(ASMM)
shared pool / db buffer cache / redo log buffer /
large pool / java pool / streams pool 로 구성(윗 줄이 좀 더 중요한 애들)
Shared pool
SQL 정보(구문분석, 실행계획, 객체정보) 기록하는 공간.
hard parsing 방지를 위해 설계된 공간.
** hard parsing : 처음부터 다시 실행계획 짜서 실행하는 방식.
soft parsing : 기존에 실행해서 shared pool에 실행 계획이 존재하면 그걸 사용하는 방식.
동적 변경 가능.
수동으로 사이즈 변경 가능하지만 자동으로 관리 (ASMM)
1) library cache : 실행계획 저장하는 공간
2) dictionary cache : sql 수행에 필요한 객체 정보를 저장하는 공간
[실습]
1. 할당된 shared pool size 조회 방법
1) parameter file 확인(instance 기동과 관련된 파라미터 정보를 가지고 있는 설정 파일)
$ vi $ORACLE_HOME/dbs/initSID.ora
$ vi $ORACLE_HOME/dbs/spfileSID.ora
2) SQL prompt 확인
> show parameter shared_pool_size
0 나옴. ASMM 사용 중.
3) v$parameter
select name, value, display_value
from v$parameter
where name = ‘shared_pool_size’;
0 나옴. ASMM 사용 중.
2. 실시간 shared pool size 조회
select sum(bytes)/1024/1024 as “SIZE(MB)”
from v$sgastat
where pool = ‘shared pool’;
180 나옴.
[hit ratio(적중률) 조회]
1. library cache hit ratio 조회
select pins as 시도횟수,
reloads as hard_parsing
from v$librarycache;
select round((1 – sum(reloads)/sum(pins)) * 100, 2) as “library cache hit ratio”
from v$librarycache;
2. Dictionary cache hit ratio 조회
select getmisses as 조회실패,
gets as 시도횟수
from v$rowcache;
select round((1 – sum(getmisses)/sum(gets)) * 100, 2) as “dictionary cache hit ratio”
from v$rowcache;
Database buffer cache
모든 SQL 실행 결과를 저장하는 메모리 공간. (실제 데이터를 저장하는 공간)
Instance의 모든 공간들 중에서 가장 크다.
매번 사용자가 요청하는 데이터를 디스크에서 불러오는 경우 성능 저하 발생.
따라서 한 번 불러온 데이터를 메모리에 저장해두고 재사용함.
physical read : 최초 실행되는 sql의 경우, 해당 데이터를 Data files(디스크)에서 직접 테이블을 읽어오는 것.
logical read : database buffer cache(메모리)에 사용자가 원하는 데이터가 남아있다면, 그걸 재사용하는 것.
Database buffer cache 상태
1. pinned buffer : 디스크로부터 데이터를 조회(select)만 한 경우, 메모리 공간에 올려두고 사용 중인 상태.
2. dirty buffer : 데이터 변경이 발생하였고 commit 이 찍혀있는 block 의미.
3. free buffer : dirty buffer 가 DBWR에 의해 datafiles(디스크)에 내려써져 사용 가능한 공간인 상태. 아예 사용되지 않은 공간도 free buffer 다.
디스크 1 block 의 크기는 8k 가 최적이라고 판정됨.
모든 sql 문(insert, select, delete 등) 은 디스크의 1 block 단위로 buffer로 불러서 처리함.
메모리에 올라가기만(select) 한 block은 pinned buffer 라고 함.
메모리에 올라가서 ‘수정'(commit)되고 아직 디스크로 안 내려온 block를 dirty buffer 라고 함.
올라갔던 block이 다시 디스크로 내려오면 free buffer 라고 함. dirty buffer 만 디스크로 내려와서 free buffer가 된다. 디스크에 내려올 때 DBWR(writer)가 작업해줌.
단순 조회만 한 pinned buffer는 수정된 게 없기에 디스크로 내려오지 않는다.
[실습]
1. 할당된 db buffer cache 사이즈 조회
1) parameter file 확인
– initdb1.ora : 정적 파라미터 파일. db1.__db_cache_size=268435456 으로 고정됨.
– spfiledb1.ora : 동적 파라미터 파일. binary file 이라서 linux에서 수정하면 파일 깨짐.
2) SQL prompt 방식
> show parameter db_cache_size
결과로 0 나온다. (정상) ASMM(Automatic Shared Memory Management) 가 자동관리 한다는 뜻.
3) v$parameter 확인
select name, value, display_value
from v$parameter
where name = 'db_cache_size';2. 실제 사용량 조회
select BYTES/1024/1024 as "SIZE(MB)"
from v$sgastat
where name = 'buffer_cache';3. db buffer cache hit ratio 조회
select sum(decode(name, 'physical reads', value, 0)) as "physical reads size",
sum(decode(name, 'db block gets', value, 0)) as "db block gets size",
sum(decode(name, 'consistent gets', value, 0)) as "consistent gets size"
from v$sysstat;select round((1 - sum(decode(name, 'physical reads', value, 0)) /
(sum(decode(name, 'db block gets', value, 0)) +
sum(decode(name, 'consistent gets', value, 0)))) * 100, 2) as "db buffer cache hit ratio"
from v$sysstat;[파라미터 파일]
기본적으로 spfile이 있으면 spfile을 잡고 DB가 기동된다.
spfile이 없을 때만 init(pfile)을 잡고 올라감.
** 위치 : $ORACLE_HOME/dbs
(= /oracle12/app/oracle/product/12.2.0.1/db1)
1. 확인(pfile or spfile)
> show parameter pfile
> show parameter spfile
둘 중 아무거나 써도 똑같은 결과
2-1. 생성 및 변경 spfile -> pfile
> create pfile from spfile; # spfile 과 같은 내용의 pfile(initdb1.ora) 생성
> shutdown immediate
> exit
$ cd $ORACLE_HOME/dbs
$ ll # initdb1.ora 있는지 확인
$ rm spfiledb1.ora # pfile로 부팅을 위해 spfile 삭제
$ sqlplus / as sysdba
> startup
> show parameter pfile
2-2. 생성 및 변경 pfile -> spfile
> create spfile from pfile;
> shutdown immediate
> exit
$ ll
$ sqlplus / as sysdba
> startup
> show parameter pfile
[redo log buffer]
dbms 내 모든 변경 내용(데이터의, 메타데이터 변경) 기록하는 메모리 영역.
LGWR에 의해 주기적으로 redo log file 에 기록됨.
장애 시 이전 데이터나 commit을 완료한 데이터를 토대로 복구를 하기 위한 영역.
redo log buffer size는 동적 변경이 불가.
log_buffer 파라미터로 크기 조절 가능
[archive log mode]
redo log file 내용이 덮어쓰여지기 전에 redo log file 내용을 복사해두는데 이를 archive log file 이라고 함.
그리고 이 때는 archive log mode 여야만 함.
즉, archive log file을 만드는 상태
만약 redo log file이 꽉차서 log switch가 일어났을 때, archive 가 작성이 안되면 archive hang이 되면서, redo log file이 작성되지 않으면서 DBWR 또한 작동 안됨. 즉, 모든 동작이 멈춰버린다.
1. 확인
> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oracle12/app/oracle/product/12.2.0.1/db_1/dbs/arch
Oldest online log sequence 1
Current log sequence 22. 변경
도 있는데 지금하면 DB 고장날 거 같아서 다음에~
** instance recovery 실패 케이스
1) DB 종료 직전 대용량 변경 작업 수행 중
-> dirty buffer 다량 발생
-> 변경 작업 내용이 redo log buffer에 기록
-> dirty buffer를 DBWR가 내려쓰기 전에 항상 redo log buffer의 내용을 LGWR가 redo log file에 기록
ex)
100~120번 시퀀스를 기록해야되는 상황이라 가정.
2개의 redo log group만 존재.
100~108번을 1번 redo log file에 기록,
109~116번을 2번 redo log file에 기록,
117~120번 기록하려고 할 때 기존 1번파일의 내용을 밀어버리고 기록함.
2) DB shutdown abort
-> 100~120번을 기록하지 못하고 즉시 종료
3) DB startup
-> 100번부터 복구작업을 시작(변경된 내용을 redo log file에서 찾아 datafile에 저장)
-> 이미 1번 redo log file에 새로 117번부터의 정보를 기록했으므로
100~108번 정보는 더이상 redo log file에 존재하지 않기 때문에 instance recovery 실패.
-> needs media recovery 메시지와 함께 DB 안 올라옴.
[문제]
** redo log buffer size가 작다면 발생할 일?
redo log buffer가 1/3 초과 시 디스크에 내려쓴다.
size가 작아지면 더 잦은 디스크 I/O 발생.
-> LGWR 더 자주 동작. -> DBWR 도 더 자주 동작
-> redo log buffer 기록하기 위한 병합(wait) 발생 = log file sync wait event
** redo log file size 가 작다면 발생할 일?
log switch 가 더 자주 발생.
-> archive hang 발생 가능성 늘어남.
but, redo log file size 가 커지면 archive log file size도 커짐.
해결방법은 redo log group의 개수를 3개 이상으로 늘리면 archive hang을 발생할 확률을 줄일 수 있다.
[실습]
1. 할당된 redo log buffer 사이즈 확인
1) SQL prompt
> show parameter log_buffer
7616k 출력됨. ASMM 환경임에도 최소 사이즈 지정.
2) v$parameter
> select name, value, display_value
from v$parameter
where name = ‘log_buffer’;
7616k 출력됨.
2. 실시간 redo log buffer 사이즈 확인
select name, value
from v$sysstat
where name in (‘redo entries’, — redo buffer에 저장되는 단위. 33507 나옴.
‘redo size’); — 현재 생성된 총 redo entries 사이즈. 17155704 나옴.