Oracle 31일차

db_health_check 쉘프로그래밍

#db_health_check.sh
#!/bin/sh

# env
todate=$(date +%Y%m%d)
totime=$(date +%H:%M:%S)
maindir=/home/oracle
. $maindir/.color.env

# 1. 리스너 정상 기동 여부
ps -ef | grep lsnr | grep -v grep > .health_check.log
grep lsnr .health_check.log > /dev/null
if [ $? -eq 0 ]
then
  echo "리스너 ${blue}정상${default}"
else
  echo "리스너 ${red}비정상${default}"
fi

# 2. 리스너 서비스 여부
lsnrctl status | grep "Services Summary..." >> .health_check.log
grep "Services Summary..." .health_check.log > /dev/null
if [ $? -eq 0 ]
then
  echo "서비스${blue}정상${default}"
else
  echo "서비스 ${red}비정상${default}"
fi

# 3. DB 정상 open 여부
sqlplus -s / as sysdba << _eof_ >> .health_check.log 2> /dev/null
set head off
select status from v\$instance;
_eof_
grep OPEN .health_check.log > /dev/null
if [ $? -eq 0 ]
then
  echo "DB ${blue}OPEN${default}"
else
  echo "DB ${red}NOT OPEN${default}"
fi

# 4. DB 정상 서비스 여부
sqlplus -s system/oracle@db1 << _eof_ >> .health_check.log 2> /dev/null
set head off
select 'X' from dual;
_eof_
grep X .health_check.log > /dev/null
if [ $? -eq 0 ]
then
  echo "DB ${blue}정상${default}"
else
  echo "DB ${red}비정상${default}"
fi

sqlplus -s 옵션 : silent 모드. 쓸데없는 배너나 프롬프트를 출력하지 않고 결과만.
set head off : 컬럼명 같은 거 출력하지 않고 결과만 출력.
2> : 표준오류만 출력리다이렉션
<< _eof_ ~ _eof_ : 입력리다이렉션. 시작과 끝 사이에 있는 것들을 앞의 명령어로 실행.
/dev/null : grep 실행 시 출력이 알아서 되는데 그걸 보지 않을 때 사용

[기타 SGA 영역]

필수 메모리 영역이 아니므로 크기를 0으로 설정 가능.
필요 시 적절한 사이즈 할당

1. Large pool

대용량 임시의 메모리 영역의 할당이 필요한 경우 사용.
병렬 작업(parallel) 수행 시
RMAN(oracle 백업복구 tool) 사용 시
large_pool_size로 관리(default 0)

ex) insert select 시 parallel hint 적용 사례
insert /*+ parallel(table1 8) */ into table1
select /*+ parallel(table2 8) */ — hint
*
from table2@orcl

2. Java pool

oracle 에서 java 사용 시 필요한 메모리 영역.
java_pool_size로 관리(default 24MB(10g), 64MB(11gR2)).

3. Streams pool

10g new feature.
Migration(데이터 이관) 시 사용하는 영역.
CDC 솔루션 사용 시 필수.
streams_pool_size로 관리(default 0)
streams_pool_size 0인 경우 기본적으로 shared pool size의 10%를 할당해서 사용.

[Fixed SGA]

백그라운드 프로세스들이 사용하는 메모리 영역.
관리자 변경 불가.

> startup
실행 시 fixed size 같은 느낌으로 나오는데 그게 fixed 영역 크기임.

[실습]

1. 할당량 조회

select name, value, display_value
from v$parameter
where name in ('large_pool_size','java_pool_size','streams_pool_size');
# ASMM 환경이므로 0으로 조회된다.

2. 실제 사용량 조회

#java pool, large pool의 크기
select pool, sum(bytes)/1024/1024 as "SIZE(MB)"
from v$sgastat
where pool in ('java pool','large pool')
group by pool;

#stream pool 사용량은 위의 코드로 나오지 않아서 따로.
select component, current_size
from v$sga_dynamic_components
where component = 'streams pool';

[sqlplus 편집기 수정]

sqlplus 프롬프트 내에서 화살표 이동 불가 -> rlwrap 프로그램 설치

$ su – root
$ yum -y install https://dl.fedoraproject.org/pub/archive/epel/7/x86_64/Packages/e/epel-release-7-14.noarch.rpm
$ yum -y install rlwrap

$ exit (oracle 계정 사용)
$ cd

$ vi .bash_profile
alias sqlplus=’rlwrap sqlplus’

$ . .bash_profile

[Background process]

ORACLE SERVER = INSTANCE + DATABASE
INSTANCE = SGA + Background process
DATABASE = control files + data files + redo log files
SGA = shared pool, DB buffer cache, redo log buffer, large pool, java pool, streams pool
shared pool = library cache, dictionary cache
DB 기동에 필수적인 프로세스.

1. DBWR

DB Buffer Cache 에 있는 dirty buffer를 datafile에 기록하는 프로세스.
1~10개의 DBWR 구성 가능.
db_writer_process 파라미터로 변경 가능

-- sql로 조회
select name, value
from v$parameter
where name = 'db_writer_processes';
-- 기본적으로 1개

-- 리눅스서버에서 조회
$ ps -ef | grep dbw | grep -v grep
#oracle   18462     1  0 12:54 ?        00:00:00 ora_dbw0_db1

** DBWR 동작시점
1) dirty buffer 양이 임계값을 지나면
2) 세션 timeout 발생 시
3) CKPT(checkpoint) 발생 시
4) RAC ping(RAC 상황에서의 동기화)
5) tablespace read only
6) tablespace offline
7) tablespace begin backup
8) drop / truncate table

[실습 – 테이블 생성 및 세그먼트 생성 과정]

1. 테이블 생성

create table tab_seg1
(no number,
name varchar2(10));

2. 테이블 사이즈 조회

select segment_name as table_name,
       blocks,
       bytes/1024 as "size(kb)"
  from dba_segments
 where segment_name = 'TAB_SEG1';
# SYSTEM 에서 테이블 만들었을 경우
   8블럭, 64kb 나옴.
# SCOTT 같은 일반계정에서 테이블 만들었을 경우
  아무것도 안 나옴. Deffered Segment Creation (지연할당)때문에

** extent : 테이블이나 인덱스와 같은 객체가 데이터를 저장하기 위해 할당받는 논리적, 연속적인 데이터 블록 집합.
** segment : 데이터를 저장하는 논리적 저장소 단위. 테이블, 인덱스, 파티션 등의 데이터베이스 객체가 물리적으로 데이터를 저장하기 위해 사용하는 구조.
** tablespace : DB에서 데이터가 저장되는 최상위 논리적 저장단위. C드라이브 같은 느낌.
** block이 모여서 extent. extent가 모여서 segment가 된다. segment가 모여서 tablespace가 된다.
** Deffered Segment Creation : 일반계정(시스템계정은 해당X)이 table create 시에 바로 segment를 주지 않고 실제로 사용할 때(insert 시) segment할당해주는 개념.

2. LGWR

redo log buffer에 있는 내용을 redo log file에 기록. (buffer는 전원꺼지면 내용 날아가니깐)
server process에 의해 각 user process가 수행하는 모든 변경 내용을 redo log buffer에 기록. (commit되지 않은 내용도 기록)
commit 수행 시 LGWR가 동작.(instance recovery를 대비하기 위해)

** 동작 시점
1) commit 수행 시 (DBWR는 동작하지 않음)
2) buffer의 1/3 초과 시
3) 매 3초마다
4) 변경량이 1MB 초과 시
5) DBWR 동작 전에 먼저 동작 (8가지 경우 있음)

** 잦은 commit의 장단점?
장점 : 데이터 손실 걱정 없음.
단점 : LGWR의 잦은 동작=디스크 I/O의 증가 -> DB 성능 감소.

[실습]

1. 프로세스 조회

$ ps -ef | grep lgwr | grep -v grep
oracle 18464 1 0 12:54 ? 00:00:00 ora_lgwr_db1

2. redo log file 조회

select *
from v$logfile;
-- redo log file 이름, 위치, 그룹번호 확인가능

select *
from v$log;
-- redo log file 크기, 상태, 변경시점 확인가능

-- 위의 2개 뷰 조인
select a.group#,
       a.member,
       b.bytes/1024/1024 as "SIZE(MB)",
       b.archived,
       b.status
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;

3. PMON (Process Monitor)

server process 를 생성 / 관리.
server process 가 비정상 종료됐을 경우(session timeout) commit 된 데이터를 저장, 그렇지 않은 데이터를 rollback 함.
PMON이 DBWR에 해당 데이터의 동기화 명령erver process 를 생성 / 관리.server process 가 비정상 종료됐을 경우(session timeout) commit 된 데이터를 저장, 그렇지 않은 데이터를 rollback 함.PMON이 DBWR에 해당 데이터의 동기화 명령.

4. SMON (System Monitor)

DB 비정상 종료 시(shutdown abort) instance recovery 수행. 즉, mount->open으로 갈 때 활성화.
비정상 종료 시, data files, redo log files, control files 의 SCN(System Change Number) 정보 불일치.
-> instance recovery로 SCN 정보 일치 시킴.

우선적으로 commit 데이터 위주로 시점 일치 작업 진행(roll forward) 후 DB OPEN, OPEN 뒤에 rollback 데이터를 시점 일치 작업 진행 (roll backward).

단, 시점 일치시키는 과정에서 필요한 정보가 redo log file에 없다면 instance recovery 실패, mount 단계에 머무름
-> media recovery 가 필요하다는 에러 발생

5. CKPT (Check Point)

checkpoint event(동기화 이벤트)가 발생하는 것을 DBWR에게 알리는 프로세스.

checkpoint 정보에는 SCN정보(data files, redo log files), 위치, 내용 등을 저장.

6. etc…