복습
오라클 데이터 베이스 아키텍쳐의 구조
인스턴스:
SGA
PGA(정렬) +
유저프로세서 -> 서버프로세서 background process
DBWR, LGWR, CKPT, SMON, RECO, ARCH (09.05)
데이터베이스
PGA에서 정렬작업이 이루어지는데 과도한 정렬작업이 일어나면 에러가 난다.
복구자 프로세서 (RECO: recovery process)
✔️ 분산 데이터베이스 구성에 사용
✔️ In-Doubt 분산 트랜잭션과 관련된 다른 데이터베이스에 자동으로 연결
✔️ 모든 In-Doubt 트랜잭션을 자동으로 해결
✔️ In-Doubt 트랜잭션에 해당하는 모든 행 제거
dba
Oracle mySql
OLTP용 DB <---db링크로 연결---> DW용 DB
✅ OLTP : online transaction processing - 주문이 일어나는 디비
✅ DW : data warehouse - 과거 주문 데이터들이 모두 여기에 저장
일년전과 지금 어떤 상품이 팔렸는지 비교해보려면 디비링크로 연결되어있는 oltp, dw에서 에서
select distnct 주문상품_20220906 from 주문@dw_link
minus
select distinct 주문상품_20230906 from 주문;만약 OLTP 에서조회중에 dbLink가 끊기면 데이터가 고아가 된다. 이런 애들은 PMON이 해결해주는데, 이거말고 OLTP쪽에서 만약 update나 insert into 주문상품_20220906@dw_link 이렇게 하다가 죽어버리면 얘는 위험하다. 락도 걸려있고 주인을 기다리고있다. (주인은 컴터 꺼짐) 이런 애들을 In-Doubt 분산 트랜잭션이라고 부르고 RECO가 얘네를 정리해준다.
insert into 주문상품_20220906@dw_link
values (...);
update 주문상품_20220906@dw_link
set ...;💡 DB링크를 사용해서 원격지의 db 테이블에 update를 하는 세션이 비정상적으로 종료가 되면, 원격지에서 update를 실행하는 서버 프로세서는 고아가 된다. 이 프로세서를
In-Doubt 트랜젝션이라고 합니다. 이 프로세서를 자동으로 정리해주는 백그라운드 프로세서가 RECO입니다.
그런데 이 RECO가 CPU를 과도하게 사용하면서 시간이 오래 걸리도록 아무 조점이 안보이면 즉, 계속 고아가 된 프로세서가 LOCK을 잡고있으면 그 누구도 그 테이블의 그 행에 update를 할 수 없게됩니다.
⭐ DBA의 tip!
in-doubt 트랜잭션의 sid와 serial#을 찾아내서 그 세션을 kill시키면 된다. kill이 안된다면 sqid를 알아내서 os의 kill 명령어로 kill시키면 된다.
alter system kill session '29,29391';
↑ ↑
sid 번호 serial#번호✅정확한 in-doubt 트랜잭션의 sid와 serial#을 알아내야한다.
실습1.
db링크를 이용해서 짝꿍의 emp table을 조회하기
1. 윈도우 db에서 내가 가지고 있는 db link 확인해보기
select * from dba_db_links;
2. 짝꿍의 emp테이블 조회
select * from emp@dblink_swg;실습2. DB link를 통해 짝꿍의 emp 테이블의 데이터 중 하나를 update
update emp@dblink_swg
set sal = 0
where ename='KING';실습3. 강제로 클라이언트 PC를 껐다고 가정하고 RECO 프로세서를 조회하기
select *
from v$process
where pname like 'RECO%';
✅ 짝꿍의 피씨가 갑자기 꺼졌다면 이 프로세서가 바쁘게 움직이고 있을겁니다.
실습4. RECO 프로세서가 바쁜지 확인해봅니다
select SPID
from v$process
where pname like 'RECO%';
$ top -p 19936
실습5. 지금 현재 db에 접속한 세션들이 어떤 SQL을 수행하고있는지 확인하기
SQLDEVELOPER -> 도구 -> 세션모니터
✔️ 짝꿍이 아래처럼 악성 날리면
select count(*)
from emp@dblink_swg, emp@dblink_swg, emp@dblink_swg;✔️ 아래로 확인
set heading off
set verify off
set pagesize 60
accept ospid prompt 'Enter OS PID : '
col txt format a60 wrap
col ex format 99,999,990
col ov format 99,999,990
col ld format 99,999,990
col lv format 99,999,990
col sm format 99,999,990
col pm format 99,999,990
col rm format 99,999,990
col uo format 99,999,990
col pc format 99,999,990
col dr format 99,999,990
col bg format 99,999,990
col hdg format a30 newline
set heading off
Select '[SQL]' hdg, a.sql_text txt,
' ' hdg,
'Execution Count' hdg, a.executions ex,
'User Opening' hdg, a.users_opening uo,
'Parse Calls' hdg, a.parse_calls pc,
'Disk Reads' hdg, a.disk_reads dr,
'Buffer Gets' hdg, a.buffer_gets bg,
'Open Versions' hdg, a.open_versions ov,
'Loads' hdg, a.loads ld,
'Loaded Versions' hdg, a.loaded_versions lv,
'Sharable Memory' hdg, a.sharable_mem/1024 sm,
'Persistent Memeory' hdg, a.persistent_mem/1024 pm,
'Runtime Memory' hdg, a.runtime_mem/1024 rm
from v$sqlarea a, v$session b, v$process c
where c.addr = b.paddr
and b.sql_address = a.address
and b.sql_hash_value = a.hash_value;
✔️ sid, 시리얼 번호 찾기
Select b.sid, b.serial#, '[SQL]' hdg, a.sql_text txt,
' ' hdg,
'Execution Count' hdg, a.executions ex,
'User Opening' hdg, a.users_opening uo,
'Parse Calls' hdg, a.parse_calls pc,
'Disk Reads' hdg, a.disk_reads dr,
'Buffer Gets' hdg, a.buffer_gets bg,
'Open Versions' hdg, a.open_versions ov,
'Loads' hdg, a.loads ld,
'Loaded Versions' hdg, a.loaded_versions lv,
'Sharable Memory' hdg, a.sharable_mem/1024 sm,
'Persistent Memeory' hdg, a.persistent_mem/1024 pm,
'Runtime Memory' hdg, a.runtime_mem/1024 rm
from v$sqlarea a, v$session b, v$process c
where c.addr = b.paddr
and b.sql_address = a.address
and b.sql_hash_value = a.hash_value;
✔️ 악성 SQL 돌고있는 세션 죽이기!
alter system kill session '124,10750' immediate;실습6. in-doubt 트랜잭션으로 만들기 위해서 다음과 같이 짝꿍 emp를 update 하세요!
-> 재확인 후 수행하기로
ARCn(아카이버 프로세스)
💡 리두 로그 파일을 백업하는 프로세서
n이 붙는이유는 아카이버 프로세서를 여러개 띄울 수 있기때문!
✔️ 로그 스위치가 발생한 후에 리두 로그 파일을 지정된 기억 장치로 복사합니다.
✔️ 트랜잭션 리두 데이터를 수집하여 대기 대상으로 전송할 수 있습니다.
✅ db설치 이후에 기본값은 arch프로세서가 활성화 되어있지 않은 데이터 베이스 모드입니다.
개발 dba가 아카이브 모드로 변경해줍니다.
실습1. 현재 database 모드가 아카이브 모드인지, 노 아카이브 모드인지 확인합니다.
SQL> archive log list실습2. 아카이브 모드로 변경합니다.
SQL> shutdown immediate실습3. startup 단계에서 mount 단계로 db를 올립니다.
SQL> startup mount실습4. 아카이브 모드로 변경 (19x OCP보기중 하나로 출제)
SQL> alter database archivelog;실습5. db를 open 단계로 올립니다.
SQL> alter database open;실습6. 아카이브 모드로 변경되었는지 확인합니다.
SQL> archive log listSQL> show parameter db_recovery_file_dist string +FRA <- ASM 디스크에 저장
SQL> db_recovery_file_dist_size big integer 3852M문제. (점심) 19c database의 db 모드를 아카이브 모드로 변경하시오!
오라클 19c / scott 계정 만들기 세팅
1. scott 유저 생성
create user scott
identified by tiger;2. 권한 부여
grant dba to scott;3. 연결
connect scott/tiger4. emp 테이블 생성
alter session set nls_Date_format='RR/MM/DD';
drop table emp;
drop table dept;
CREATE TABLE DEPT
(DEPTNO number(10),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE EMP (
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4) ,
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) );
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'81-11-17',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'81-05-01',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'81-05-09',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'81-04-01',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'81-09-10',1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'81-02-11',1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'81-08-21',1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'81-12-11',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'81-02-23',1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'81-12-11',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'80-12-09',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'82-12-22',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'83-01-15',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'82-01-11',1300,NULL,10);
commit;5. sqldeveloper에서 19c에서 만든 user 추가하기
💡 리스너 상태 확인하는 명령어(여기서 포트번호 확인하기)
lsnrctl status
6. 19c database에 sys유저로 sqldeveloper에 접속하기
실습1. arch 프로세서가 떠있는지 확인하기(리눅스에서)
select pname, spid
from v$process
where pname like 'ARC%';실습2. 아카이브 프로세서를 최대 몇개까지 띄울 수 있는지 확인
show parameter log_archive_max_processes실습3. 실습 1번의 spid번호 하나를 가지고 리눅스 os에서 조회하기
$ ps -ef | grep 8273
$ ps -ef | grep 8273 | grep -v grep실습4. 현재 로그 시퀀스 번호 확인
$ sqlplus "/as sysdba"
SQL> archive log listSQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 24
Next log sequence to archive 26
Current log sequence 26
✅ 현재 로그 시퀀스 번호 26
실습5. 리두 로그 파일 확인
SQL> select member from v$logfile;
SQL> select group#, member from v$logfile;
✅ lgwr가 리두정보를 내려쓸 때 리두 그룹에 내려쓰는데, 멤버가 2개가 있으면 두군데 같은 내용을 동시에 씁니다. (왜? 너무 중요해서) 그룹 1 다쓰면 로그스위치가 일어나서 그룹2에, 다쓰면 로그스위치가 일어나 그룹3으로. 다 쓰면 로그스위치로 다시 그룹 1
실습6. 로그스위치를 수동으로 일으켜보자
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 24
Next log sequence to archive 26
Current log sequence 26
SQL> alter system switch logfile;
System altered.
✅ 26 -> 27로 바뀐것을 확인할 수 있다.
로그스위치는 자동으로 일어나는데, 일부러 수동으로 일으킨 이유는 로그스위치가 일어날 때 자동으로 아카이브 로그 파일이 생성되기 때문입니다. (아카이브 로그 파일 보려고)
실습7. 아카이브 로그 파일을 확인합니다.
SQL>
select table_name from dictionary where table_name like '%ARCH%';
select name from v$archived_log;
+FRA 는 ASM(Automatic Storage Management)으로 데이터베이스의 파일들이 구성되어있다는 것
실습8. 로그스위치를 수동으로 일으키고 아카이브 로그파일이 또 생성되었는지 확인하기
SQL> alter system switch logfile;
select name from v$archived_log;
💡dba를 위한 tip
dba는 반드시 db모드를 아카이브 로그 모드로 운영해야한다. 그래야 장애가 났을때 복구가 가능!
아카이브 로그 파일이 있는 디렉토리가 100%로 full이 되면 더이상 아카이브 로그 파일이 생성되지 않으면서 모든 DML작업이 실패한다. (select만 가능) -> 수시로 확인하기
⭐아카이브 로그파일이 있는 디스크 그룹인 +FRA의 용량이 어떻게되는지 확인하는 쿼리⭐
FRA 디스크 용량 확인SELECT name AS "FRA Name", space_limit /1024/1024 AS "Space Limit (MB)", space_used /1024/1024 AS "Space Used (MB)", space_reclaimable AS "Space Reclaimable (MB)", (space_limit - space_used) /1024/1024 as "Free Space (MB)" FROM v$recovery_file_dest;
문제 위의 SQL을 fra_space.sql이라는 이름으로 저장하고 다음과 같이 실행되게 하시오
SQL> @fra_space.sql
------------------------------
SQL>
ed fra_space.sql
col "FRA Name" for a10
SELECT
name AS "FRA Name",
space_limit /1024/1024 AS "Space Limit (MB)",
space_used /1024/1024 AS "Space Used (MB)",
space_reclaimable AS "Space Reclaimable (MB)",
(space_limit - space_used) /1024/1024 as "Free Space (MB)"
FROM
v$recovery_file_dest;문제. dba.sh 스크립트에 7번에 위의 스크립트를 수행하는 코드를 추가하시오
$ vi dba.sh
# /bin/bash
echo -e "
aaa a aa aaa aaa a aaaa
a a a aa a a a a a a
a a aa a a a aaaaaa
a a a aaaaa a a a
a a a a a a a a
aaa a aaa aaa a aaaa
"
echo -e "======================="
echo " "
echo " [1] 테이블 스페이스 공간 확인 "
echo " [2] 디스크 i/o 확인하는 sar 명령어 수행 "
echo " [3] 현재 데이터베이스 이슈 확인 "
echo " [4] 테이블 스페이스 공간 확보(80% 이상) "
echo " [5] 현재 오라클 메모리 상태 확인 "
echo " [6] 체크포인트를 수동으로 일으키기 "
echo " [7] 아카이브 로그 파일의 디스크 용량을 확인하기"
echo -n "원하는 작업 선택"
read aa
echo " "
case $aa in
1) sh /home/oracle/t.sh;;
2) sh /home/oracle/sar.sh;;
3) sh /home/oracle/o.sh;;
4) sh /home/oracle/add_t.sh;;
5) sqlplus scott/tiger @/home/oracle/sga.sql ;;
6) sqlplus scott/tiger @/home/oracle/ckpt.sql ;;
7) sqlplus "/ as sysdba" @/home/oracle/fra_space.sql ;;
esac
echo " "프로세스 시작 시퀀스
💡 리눅스를 켜면 init이라는 데몬이 먼저 시작이 되어서 다른 오라클 관련 프로세서들을 순서대로 깨웁니다.
그리드 Infrastructure는 소프트웨어 회사였던 오라클이 하드웨어 회사인 sun을 인수하면서 소프트웨어 + 하드웨어 회사로 성장하기 시작했다. 그러면서 하드웨어 회사의 대항마인 HP에 대항하기 위해서 만든 프로그램이다.
- 회사에 디스크 스토리지 3가지
- file system : 윈도우의 OS 디스크
- raw device : CD 디스크의 일부분이라고 생각하면 됨
예 : /raw/raw1 /raw/raw2 /raw/raw3 . . /raw/raw100
- ASM : 오라클에서 스토리지 관리를 알아서 해주겠다는 것!
ASM 스토리지로 구성하고 ASM디스크 그룹에 DISK만 추가하면 오라클이 알아서 ROUND ROBIN 방식으로 어느 한쪽 disk에 작업이 몰리지 않도록 balancing을 해주겠다!
실습1. 리눅스 서버에는 인스턴스가 2개가 있다 하나는 오라클을 관리하는 오라클 인스턴스, 다른 하나는 스토리지를 관리하는 ASM인스턴스! 백그라운드 프로세서도 똑같이 오라클 인스턴스처럼 ASM 인스턴스에 있습니다. 이것을확인
$ ps -ef | grep pmon
DB instance: data빨리 셀렉트 하게
ASM instance: 스토리지 디스크에 로드발랜싱 해주는 역할 -> grid infrastructure을 설치해야 얘가 생긴다.
실습2. file system을 이해하기 위해 19c의 윈도우 오라클에 sys유저로 접속해서 리두로그 파일의 위치를 확인합니다.
-- 리두로그 파일 위치 확인 SQL> col member for a50 SQL> select member from v$logfile;
✅ 위 경로로 가보면 파일 3개 찾을 수 있다.
실습3. 로그 스위치를 수동으로 일으킨다.
alter system switch logfile;실습4. 아카이브 로그파일이 있는곳에 가서 생성되었는지 확인해봅니다.
archive log list
💡 파일시스템 구조
/home/oracle
C:\Users\ITWILL\Desktop\WINDOWS.X64_193000_db_home\RDBMS데이터베이스 저장 영역 구조
instance, DB 중 위 파일들은 DB에 있는 애들이다.
1. datafile이란? data가 들어있는 파일!
- 오라클은 data의 종류가 2가지가 있다.
- system data: 오라클 db를 운영하기 위해 필요한 데이터
예: user_tables와 같은 데이터 사전 - non system data: 사용자 데이터,
예: emp table
- system data: 오라클 db를 운영하기 위해 필요한 데이터
실습 1. data file의 위치, 이름을 19c에서 확인
select file_name from dba_data_files; ------------------------------------------- FILE_NAME --------------------------------------------- C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\SYSTEM01.DBF <- system data C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\SYSAUX01.DBF <- system data C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\USERS01.DBF <- 사용자 data C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\UNDOTBS01.DBF <- system data
select file_name from dba_temp_files;
✅ 임시테이블, with절의 임시테이블, 정렬작업할때 사용한 데이터
문제 리눅스에서 data file의 위치를 확인
SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- +DATA/orcl/datafile/users.259.796857625 +DATA/orcl/datafile/undotbs1.258.796857625 +DATA/orcl/datafile/sysaux.257.796857623 +DATA/orcl/datafile/system.256.796857621 +DATA/orcl/datafile/example.265.796857803 +DATA/orcl/datafile/sysaux.267.1145464529 +DATA/orcl/datafile/users.268.1145464531 +DATA/orcl/datafile/system.269.1145464533 +DATA/orcl/datafile/system.270.1145464597 9 rows selected.✅ 위는 ASM디스크에 있다.
2. control file: database를 컨드롤 하기 위한 데이터가 있는 파일. database의 구조정보가 들어있고 각각의 파일들의 위치, 이름정보가 들어있다.
실습1.컨트롤 파일의 위치를 확인해보기
19c, 11g에서도 수행select name from V$controlfile;
