0130 ADMIN

현스·2024년 1월 30일

ADMIN

목록 보기

16/18

0130

복습 : 오라클 아키텍쳐 ?

오라클 메모리 + database
↓
instance 가 필요한 이유 ?
database 에서 읽은 데이터를 메모리에 올려놓고
다음번에 빠르게 데이터를 엑세스 하기 위해서

-- 오라클 그림 삽입 --- 복습1

▩ 10. JAVA POOL 메모리 영역

JAVA 풀이 필요한 이유 ?
1. 오라클 설치시 나오는 화면이 자바로 만들어졌습니다.
2. 오라클 네트워크 설정에 대한 유틸리티로 자바로 만들어짐

그러다보니 자바코드를 컴파일 하기 위한 메모리 공간이 필요한데
그 영역이 자바풀 입니다.

오라클의 메모리 영역의 구성 요소들의 사이즈는 상황에 따라서 자동 조절 됩니다.
공유풀이 많이 사용하는 상황이 되면 다른 메모리를 줄이고 공유풀을 늘립니다.
이와 달리 버퍼 캐쉬가 많이 사용되게 되는 상황이 되면 다른 메모리를 줄이고 버퍼캐쉬를 늘립니다.

#1. 현재 sga 영역의 구성 요소들의 사이즈를 확인합니다.

SQL> @sga.sql

#2. SQL plus 에서 sys 유져로 접속해서 자바 코드를 실행합니다.

SQL>
set echo on

DECLARE
i NUMBER;
v_sql VARCHAR2(200);
BEGIN
FOR i IN 1..200 LOOP
-- Build up a dynamic statement to create a uniquely named java stored proc.
-- The "chr(10)" is there to put a CR/LF in the source code.
v_sql := 'create or replace and compile' || chr(10) ||
'java source named "SmallJavaProc' || i || '"' || chr(10) ||
'as' || chr(10) ||
'import java.lang.*;' || chr(10) ||
'public class Util' || i || ' extends Object' || chr(10) ||
'{ int v1=1;int v2=2;int v3=3;int v4=4;int v5=5;int v6=6;int v7=7; }';
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
/

#3. 자바 풀의 사이즈가 늘어났고 다른 영역이 줄어들었는지 확인합니다.

SQL> @sga.sql

문제1. 위의 for loop 문을 500번으로 늘려서 수행되게 하고 java pool 이 더 늘어나는지
확인하시오!

SQL>
set echo on

DECLARE
i NUMBER;
v_sql VARCHAR2(200);
BEGIN
FOR i IN 1..500 LOOP
-- Build up a dynamic statement to create a uniquely named java stored proc.
-- The "chr(10)" is there to put a CR/LF in the source code.
v_sql := 'create or replace and compile' || chr(10) ||
'java source named "SmallJavaProc' || i || '"' || chr(10) ||
'as' || chr(10) ||
'import java.lang.*;' || chr(10) ||
'public class Util' || i || ' extends Object' || chr(10) ||
'{ int v1=1;int v2=2;int v3=3;int v4=4;int v5=5;int v6=6;int v7=7; }';
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
/

답 - 큰 변화가 없다.

문제2. 오라클에 접속할 때마다 메세지 나오는 것을 지우시오 !

cd $ ORACLE_HOME
cd sqlplus
cd admin
ls

glogin.sql help libsqlplus.def plustrce.sql pupbld.sql

glogin.sql : sql 의 환경설정 파일

define _editor='vi' <--- 이것만 남겨둡니다.

문제3. .bash_profile 에 오라클 sys 유져로 접속하는것을 편하게 하는 alias 를 넣으시오 !

[orcl:~]$ cd
[orcl:~] $[orcl:~]$ vi .bash_profile

alias alert='cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace'
alias net='cd $ORACLE_HOME/network/admin'
alias ss='sqlplus / as sysdba'
alias scott='sqlplus scott/tiger'
alias dbs='cd $ORACLE_HOME/dbs'

export LANG=ko_KR.UTF-8

▩ 11. PGA 메모리 영역

PGA는 Program Global Area의 약자로, 단어가 가진 의미 그대로 공유되지 않고 혼자서만 사용하는 공간이다.
그래서 Private Global Area라고 불리기도 한다. 좀더 자세히 설명하면, PGA는 프로세스에 대한 데이터와 제어정보가 포함된 비 공유 메모리 영역으로 서버 프로세스가 시작될 때 생성되며, 데이터베이스에 접속하는 모든 사용자에게 할당된 각각의 서버 프로세스가 독자적으로 사용하는 오라클 데이터베이스의 메모리 공간이다.
PGA는 서버 프로세스가 시작될 때 오라클 데이터베이스에 의해 생성되며 프로세스가 종료될 때 해제된다. PGA는 SQL들의 작업공간이기 때문에 정렬 작업을 할 때 주로 사용된다. 따라서 SQL 정렬 작업을 위해 PGA 메모리 영역을 사용할 경우, PGA 크기 내에서 정렬이 일어나면 메모리 내에서 정렬되는 것이므로 수행속도가 빠르고 PGA 크기를 초과하면 디스크에서 정렬이 일어나므로 수행속도가 느려지게 된다.

pga 메모리영역은 왜 필요한가 ? sql 의 정렬 작업을 하기 위해서.

정렬을 일으키는 SQL 에 무엇이 있을까 ?

order by
sort merge join
creat index 생성문 실행시
intersect, union, minus 가 19c 까지는 정렬을 했음. ( 취업하면 이걸 쓸거라 알아두기)

현업에서 위의 작업들을 dba 나 개발자들이 수행할 때 대량의 데이터를 정렬하는 경우
out of memory 에러가 나면서 작업이 안되는 경우가 종종 발생합니다.
이런 에러가 나지 않도록 pga 영역의 사이즈 조절을 잘 해야합니다.

pga 영역에 해쉬 area 가 해쉬 조인시 해쉬 테이블이 올라가는 공간으로 사용된다
해쉬조인 속도를 높이려면 pga 영역의 사이즈를 늘릴 필요가 있다.

■ 대량의 데이터를 정렬할 때 또는 해쉬 조인의 속도를 빠르게 할 때 pga 영역을 다루는 방법은?

pga 영역은 오라클에 의해서 사이즈가 자동조절 되고 있다.
dba 가 memory_target 이라는 파라미터 하나만 설정해 놓으면 나머지는 오라클이 알아서 합니다.

1) sga_tarhet : sga 영역의 사이즈
2) pga_aggregate_target pga 영역의 사이즈

낮 시간: sga > pga
밤 시간: sga < pga

배치작업 pga
총 매출액 (해쉬조인
상품순위 order by )
재고 상황 등

■ 실습

#1. memory_target 사이즈가 몇인지 확인하시오 !

SQL> show parameter memory_target

이 사이즈를 를릴려면 memory_max_target 을 먼져 늘려야 합니다.
memory_max_target을 늘릴려면 db 를 내렸다 올려야 합니다.

460m 내에서 오라클이 알아서 sga 사이즈와 pga 사이즈를 자동 조절 합니다.

#2. sga 영역의 사이즈를 확인하시오 !

SQL> @sga.sql
CURRENT parameter settings

NAME TYPE VALUE

sga_max_size big integer 460M
sga_target big integer 0

SGA Dynamic Component SIZE Information

COMPONENT CURRENT_SIZE MIN_SIZE

shared pool 120M 112M
large pool 4M 4M
java pool 20M 4M
DEFAULT buffer cache 136M 136M

CURRENT parameter settings IN V$PARAMETER

NAME VALUE ISDEFAULT

shared_pool_size 0 TRUE
large_pool_size 0 TRUE
java_pool_size 0 TRUE
db_cache_size 0 TRUE

어떻게 계산 한거지 ???????120M4M20M136M
SQL> show parameter pga

NAME TYPE VALUE

pga_aggregate_target big integer 0 <-------- 오라클이 자동 조절

#4. 현재 pga 영역의 사용현황을 보고 싶다면 ?

SQL> SELECT a.name, b.value "Current", a.value "Max", (a.value - b.value) "Diff"
FROM V $PGASTAT a, V$ PARAMETER b
WHERE a.name = 'total PGA inuse' AND b.name = 'pga_aggregate_target';

total PGA inuse 0 55280640 55280640

문제1. 과도한 정렬작업을 일으키고 pga 영역이 사용되는지 사용현황을 확인하시오 !

select e1.sal
from emp e1, emp e2, emp e3, emp e4, emp e5, emp e6
order by e2.sal desc;

SQL> SELECT a.name, b.value "현재셋팅", a.value "현재사용율"
FROM V $PGASTAT a, V$ PARAMETER b
WHERE a.name = 'total PGA inuse' AND b.name = 'pga_aggregate_target';

▩ 12. 프로세서 구조

오라클 프로세서 종류 3가지

1) User Process : 클라이언트 쪽에서 작동하는 프로세서
– 오라클 데이터베이스에 연결하는 응용 프로그램 또는 도구

예 : 클라이언트 ---------------------------> 서버(오라클db)
↓ ↓
유져 프로세서 ---------- SQL ----------> 서버프로세서

2) 데이터베이스 프로세스
(1) 서버 프로세스: Oracle Instance에 연결되며 유저가 세션을
설정하면 시작됩니다.
(2) 백그라운드 프로세스: Oracle Instance가 시작될 때 시작됩니다.

3) 응용 프로그램 프로세스
– 네트워킹 리스너
– 그리드 Infrastructure Daemon

실습 :

#1. 오라클 백그라운드 프로세서들을 확인하시오 !

SELECT * FROM v$bgprocess;

#2. 서버 프로세서들을 확인하시오 !

sql > select * from v$process;

sql > select * from v$process
WHERE pname IS null;

scott 으로 접속

#3. 리스너의 상태를 확인하시오 !

$lsnrctl status

점심시간 문제

lsnrctl status로 리스너 상태 확인하는 명령어를 dba.sh 스크립트에 추가하시오

echo -e "
db 관리 자동화 프로그램 입니다.
"
echo -e "========================================"
echo " "
echo "[1] 오라클에 접속하려면 1번을 누르세요
[2] 리눅스 서버에 부하를 확인하려면 2번을 누르세요
[3] 인덱스 정보를 확인하려면 3번을 누르세요
[4] 테이블 스페이스 공간을 확인하려면 4번을 누르세요
[5] db 의 이슈를 확인하려면 5번을 누르세요
[6] sga 영역내의 구성요소들의 현재 사이즈를 확인하려면 6번을 누르세요
[7] 리스너의 상태를 확인하려면 7번을 누르세요 "
echo " "
echo -n "원하는 작업을 선택하세요 "
read aa
echo " "
case $aa in
1) sqlplus sys/oracle_4U as sysdba ;;
2) top ;;
3) sh index.sh ;;
4) sh tablespace2.sh ;;
5) sh o.sh ;;
6) sqlplus sys/oracle_4U as sysdba @sga.sql
7) lsnrctl status ;;
esac
echo " "

막간 개념 짚고 넘어가기

Oracle 데이터베이스에서 메모리와 디스크는 각각 다른 목적과 역할을 가지고 있습니다.

메모리 (Memory):

목적: 메모리는 데이터베이스의 성능 향상을 위해 사용됩니다. 데이터베이스 서버의 램(Random Access Memory)을 활용하여 쿼리 실행 및 트랜잭션 처리와 같은 작업을 빠르게 수행할 수 있습니다.
역할:
Buffer Cache: 디스크에서 읽어온 데이터 블록을 메모리에 유지하여 빠른 읽기를 지원합니다.
Shared Pool: SQL 쿼리의 파싱 및 실행에 필요한 공유 메모리 영역으로서, SQL 문장의 재사용을 최적화합니다.
Large Pool, Java Pool 등: 특정 작업이나 메모리 요구사항에 따라 할당되는 다양한 메모리 구성 요소들이 있습니다.

디스크 (Disk):

목적: 디스크는 데이터베이스의 영구 저장소 역할을 합니다. 데이터베이스의 파일들(테이블스페이스, 데이터 파일 등)은 주로 디스크에 저장되며, 장애 복구와 지속적인 데이터 보존을 위해 사용됩니다.
역할:
데이터 파일(Data Files): 테이블과 인덱스 데이터 등이 저장되는 파일로, 영구적인 데이터 저장을 담당합니다.
로그 파일(Log Files): 트랜잭션 변경 내용을 로그에 기록하여 데이터베이스의 일관성과 회복성을 보장합니다.

** 차이점 요약:

메모리: 주로 성능 향상을 위해 사용되며, 빠른 데이터 액세스 및 쿼리 처리를 지원합니다.
디스크: 데이터베이스의 영구 저장소로 사용되며, 데이터의 지속적인 보존과 장애 복구를 담당합니다.
데이터베이스 시스템에서는 메모리와 디스크 간의 효율적인 데이터 이동과 관리를 통해 최적의 성능을 달성하려고 노력합니다.

▩ 13. DBWin

데이터베이스 버퍼캐쉬는 3개의 버퍼로 구성되어 있습니다.

free buffer : 비어있는 버퍼
pinned buffer : 비어있지 않은 버퍼 (데이터 변경되지 않은 버퍼)
dirty buffer : 비어있지 않은 버퍼( 데이터 변경 후 디스크의 데이터와 서로 일치하지 않음)

Oracle 데이터베이스에서 DBWR는 Database Writer를 나타내며, 주로 데이터베이스의 버퍼 캐시에 있는 변경된 블록을 디스크에 기록하는 역할을 합니다. 여러 가지 기능을 수행하여 데이터베이스의 성능과 일관성을 유지합니다.

DBWR 프로세스는 데이터베이스의 성능과 안정성을 향상시키기 위해 중요한 역할을 합니다. 설정과 튜닝을 통해 DBWR 동작을 조절하여 최적의 성능을 유지하도록 관리할 수 있습니다.

"오라클은 무조건 메모리부터"

실습
#1. DBWR 백그라운 프로세서가 있는지 조회하시오 !

select pname, spid
from v$process
where pname like 'DBW%';

PNAME |SPID|
---------+----+
DBW0 |5541|

SQL> select status
2 from v$instance
3 ;

STATUS

OPEN

SQL> select pname, spid
2 from v$process
3 where pname like 'DBW%';

PNAME SPID

DBW0 2554

▩ 15. LGWR (log writer )

LGWR 의 역할 ? 리두 로그 버퍼의 내용을 리두 로그 파일에 내려쓰는 프로세서

chatGPT 에게 질문: 오라클 LGWR 가 리두로그 버퍼의 내용을 리두로그 파일에 내려쓰는
4가지 시점을 알려줘 ?

오라클의 LGWR(LLog Writer)는 리두 로그 버퍼의 내용을 디스크에 기록하는 역할을 담당하는 백그라운드 프로세스 중 하나입니다. LGWR는 다양한 시점에 리두 로그 버퍼의 내용을 리두 로그 파일에 기록하는데, 그 중에서 주요한 4가지 시점은 다음과 같습니다:

커밋 시 (Commit Time):

트랜잭션이 커밋될 때 LGWR는 해당 트랜잭션에 대한 리두 로그 레코드를 리두 로그 버퍼에서 리두 로그 파일로 기록합니다. 이것은 트랜잭션의 변경 사항을 영구적으로 디스크에 저장하는 중요한 시점입니다.

로그 스위치 시 (Log Switch):

리두 로그 파일이 꽉 찼을 때 또는 주기적으로(3초 마다),
LGWR는 현재 사용 중인 리두 로그 파일을 닫고 새로운 리두 로그 파일을 열게 됩니다.
이때 리두 로그 버퍼의 내용도 디스크에 기록됩니다.

로그 체크포인트 시 (Log Checkpoint):

주기적으로 또는 일정한 이벤트가 발생했을 때, LGWR는 리두 로그 버퍼의 내용을 디스크에 기록하는데, 이를 "로그 체크포인트"라고 합니다. 이는 데이터베이스의 일관성을 유지하고, 복구 시점을 결정하는 데 중요합니다.

플러시 요청 시 (Flush Request):

데이터베이스 커넥션에서 명시적인 플러시 요청이 있을 경우, LGWR는 리두 로그 버퍼의 내용을 디스크에 기록합니다. 이는 일부 응용 프로그램이 데이터베이스 변경 사항을 디스크에 즉시 반영하도록 하는데 사용됩니다.
이러한 다양한 시점에서 리두 로그를 디스크에 기록함으로써 데이터베이스는 트랜잭션의 지속성과 일관성을 보장할 수 있습니다.

ckpt 프로세서 ------- > lgwr 프로세서 ------- > dbwr 프로세서

■ 실습.

#1. v$process 를 조회해서 LGWR 의 SPID 를 알아내세요 !

select pname, spid
from v$process
where pname like 'LGW%';

PNAME |SPID|
---------+----+
LGWR |2556|

#2. SPID 로 TOP 명령어를 날려서 얼마나 바쁜지 확인하시오 !

top -p 2556

문제1. LGWR 관련해서 DBA 에게 유용한 스크립트를 1개만 알려줘 라고 챗GPT 에게 물어봐라

SELECT
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS CURRENT_TIME,
NAME,
VALUE
FROM
V$SYSSTAT
WHERE
NAME LIKE 'redo%'
OR NAME = 'log%';

[선생님의 챗 지피티]
SELECT
a.name,
b.value AS "Redo Log Buffer Writes",
c.value AS "Redo Log Writes"
FROM
v $statname a, v$ sysstat b, v$sysstat c
WHERE
a.statistic# = b.statistic# AND
a.statistic# = c.statistic# AND
a.name IN ('redo buffer allocation retries', 'redo writes');

[윤호님 챗 지피티]
퍄
#!/bin/bash

Check if LGWR process is running

lgwrprocess=$(ps -ef | grep "ora_lgwr" | grep -v "grep" | wc -l)

if [ $lgwr_process -gt 0 ]; then
echo "LGWR process is running."
else
echo "LGWR process is not running. Please investigate."

You can add additional actions here, such as sending an alert email or restarting the database.

▩ 16. CKPT (checkpoint process)

체크포인트 프로세스

주기적으로 메모리(instance) 에 있는 내용을 db 로 내려쓰는 이벤트를 일으키는 프로세서

ckpt 의 역할 : 메모리의 내용을 db 로 내려쓰게 하면서 메모리와 db 간의 데이터의 일치를 맞춰주는 역할을 합니다.

이 이벤트 이름을 "checkpoint event" 라고 한다.
이 작업의 주기는 오라클에 의해서 자동으로 관리되고 있다.
메모리의 변경사항이 많으면 자주 내려쓰고 별로 없으면 덜 내려 쓴다.

실습:

#1. 체크포인트 이벤트 주기가 자동으로 관리되는지 확인하시오 !

sqlplus "/as sysdba"

SQL > show parameter fast_start_mttr_target

NAME TYPE VALUE

fast_start_mttr_target integer 0

0 이면 자동

SQL> alter system checkpoint;

System altered.

SELECT FILE#, CHECKpoint_change#
FROM v$datafile_header;

FILE# |CHECKPOINT_CHANGE#|
------+------------------+
1| 1203086|
2| 1203086|
3| 1203086|
4| 1203086|
5| 1203086|

문제1. 체크포인트를 수동으로 일으키고 데이터 파일 헤더의 체크포인트 번호가 변경되는지 확인하시오

SQL> alter system checkpoint;

SELECT FILE#, CHECKpoint_change#
FROM v$datafile_header;

FILE#|CHECKPOINT_CHANGE#|
-----+------------------+
1| 1203225|
2| 1203225|
3| 1203225|
4| 1203225|
5| 1203225| 바뀜 !!

문제2. dba.sh 스크립트에 체크포인트를 수동으로 일으키는 명령어를 추가하시오 !!

▩ 17. SMON(System Monitor process)

smon 의 역할이 무엇인가 ?

오라클 startup 시 인스턴스 복구 작업 수행 (오라클이 비정상적으로 종료 되었을 때)
사용하지 않는 temporary segment 를 정리

with 절을 사용하면 템프 테이블이 자동으로 만들어집니다.
with 절이 끝나면 누군가 정리를 해야하는데 smon 이 합니다.

복구과정 :

update emp
set sal = 0
where ename='SCOTT' ;

commit;

dbwr 는 여유가 많음 commit 을 해도 작동 안함
lgwr 는 바로바로 내려씀

컴퓨터 꺼져버렸다고 해봐
commit 했으니까 0로 뜸.
컴퓨터 다시 켤 때 (오라클 start up) smon 이 나타남 = > 3000 을 0으로 바꿔줌
이거를 instance recovery 라고 함 !

실습

#1. scott 로 접속해서 KING 의 월급을 0으로 바꿔라

터미널 창을 하나 더 열고 sys 로 접속해서 db 를 비정상적으로 내려

#3. 그리고 startup 합니다.

#4. SCOTT 으로 재접속 해본다.

문제2. 이번에는 ALLEN 의 월급을 0으로 변경하고 COMMIT 하지 않고 다른 터미널창에서 SYS 유져에서 DB 를 SHUTDOWN ABORT 로 내리고 다시 올리면 ALLEN 의 월급은 어떻게 ?

UPDATE EMP
SET SAL = 0
WHERE ENAME='ALLEN';

▩ 18. PMON 프로세서 (Process monitor)

클라이언트 -------------------------- > 서버

서버에서 돌았던 작업들을 모두 rollback

아무것고 안하고 놀고있으면 그 세션을 끊어버리는 역할

※ 오라클 12c 버젼부터 리스너에 서비스를 자동으로 등록해주는 기능이
pmon 이 아니라 LPEG(Listener Registration) 프로세서가 담당 합니다.

만약에 여러분들이 신한은행에 갔으면 여러분 노트북을 신한은행 db 에 접속할 수있게
셋팅을 해야합니다.

#1. 리스너의 상태를 확인합니다.

lsnrctl status

$ sqlplus scott/tiger@orcl tns 별칭 (4가지 정보)
호스트 포트 서비스이름 프로토콜

직접 다 써서 접속할 수 있음.
sqlplus scott/tiger@192.168.19.43:1521/orcl.us.oracle.com
호스트(ip주소):포트/서비스이름

tns 별칭이 더 쉬움.

접속되는지 볼 것
명령 프롬프트창에 실행

TNS_ADMIN <-- 환경변수 설정 <-- tnsnames.ora 파일의 위치

윈도우 탐색기를 열고

tnsnames.ora 를 수정함.

도스창에서 sqlplus scott/tiger@orcl_11g 쳐보고 접속되는지 확인 !!

db에버에서 접속 해보기 !

1. 오라클 클라인트 설치
2. TNS_ADMIN 환경변수를 설정
3. tnsnames.ora 파일을 환경변수 설정 위치에 가져다 둡니다.

[orcl:~]$ net
[orcl:admin]$ ls
samples shrept.lst tnsnames.ora
[orcl:admin]$ vi tnsnames.ora

C:\Users\itwill\Downloads\instantclient-basic-nt-21.12.0.0.0dbru\instantclient_21_12\network\admin
여기에 있는

tnsnames.ora 파일에 추가로

jyp_11g =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.19.45)(PORT =1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.us.oracle.com)
)
)

저장해준다.

dbever 로 접속할때 tns 로 접속한다.

C:\Users\itwill\Downloads\instantclient-basic-nt-21.12.0.0.0dbru\instantclient_21_12\network\admin
여기에 있는

tnsnames.ora 파일에 추가로

jyp_11g =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.19.45)(PORT =1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.us.oracle.com)
)
)

저장해준다.

dbever 로 접속할때 tns 로 접속한다.