[DB/Oracle] Oracle Architecture 이해하기 (11g) [2]

Oralce은 많은 기업이랑 개발자가 사용하는 강력한 관계형 데이터베이스 관리 시스템이다.
하지만 유로 버전이기 때문에 직장을 다니지 않는다면 접하지 않는 경우가 많고 입사 후에 처음 접하는 일이 많다.

처음 접하는 사람도 따라가기 쉽게 글을 작성해 보려고 한다.

요번 글에서 진행할 내용은 Architecture이해이다.

11g 이후 버전의 차이점이 있으니 유의해서 읽자 다음 기능에 대해서는 다루지 못할 확률이 높다.

• 멀티테넌트 아키텍처
  플러그형 데이터베이스 (PDB)  컨테이너 데이터베이스 (CDB)
• SGA & PGA 관리 방식
  11g : 수동 혹은 자동 메모리 관리 (Automatic Shared Memory Management, ASMM)
  12c 이상 : 통합 메모리 관리 (Automatic Memory Management, AMM)
• 데이터 복구 및 관리
  플래시백 데이터 아카이브

추가적인 설명은 접은 글에서 번호를 따라가자 (*1) 이런 식으로 적는다. 가장 아래에 있다.

---

1. Oracle Architecture

오라클 데이터베이스는 크게 2가지가 있다.

1. 인스턴스 (Instance) :
   메모리와 프로세스를 통해 데이터베이스를 운영한다.
2. 데이터베이스 (Database) :
   실제 데이터가 저장되는 물리적 저장소이다.

1 프로세스

1. User Process : SQL을 작성하기 위해서 연결하는 process를 말한다. Orange, Sqldeveloper, JDBC 같은 애플리케이션을 뜻한다.
2. Oracle Server Process : User Process가 Listener에 접속을 하고 접속에 성공하면 Oracle Server Process가 하나 생성된다. User Process와 1:1 관계로 생성된다.
3. Oracle Background Process : SMON, PMON, DBWR, CKPT, RECO, LGWR 등 많은 프로세스 종류가 존재한다.

왜 oracle에는 process가 주로 언급되고 thread가 언급되지 않나요?

이는 아키텍처 설계와 운영 체제 독립성 때문이다.

1. 운영체제 독립성
   프로세스 기반 설계를 한 oracle은 운영 체제의 스레드 구현방식에 의존하지 않아서 여러 플랫폼에서 성능과 안정성을 보장한다. process는 대부분의 운영체제에서 동일한 방식으로 동작할 확률이 높다.
2. 안정성과 고립성
   프로세스 기반 모델은 각 프로세스가 독립된 메모리 공간을 사용함으로 하나의 프로세스에서 문제가 발생해도 다른 곳에 영향을 주지 않는다.
3. 병렬 처리 모델
   오라클은 멀티프로세싱 아키텍처를 기반으로 설계되었다. 그래서 주로 process를 사용한다.
4. thread를 쓰긴 함
   필요에 따라서 thread 기반 모델도 있다. Windows 같은 운영체제에서는 thread를 사용해서 경량화된 병렬처리를 수행할 수 있다. Threaded Execution Mode라고 부른다.

2 메모리

1. PGA (Program Global Area) : User Process의 연결로 생성된 Server Process에서 사용하는 Non Shared 메모리이다.
2. SGA (System Global Area) : 모든 Server Process가 접근할 수 있는 Shared Memory이다.

3 데이터베이스 파일

1. Datafile : 오라클 DB를 구성하는 주요 구성 파일이며 사용자 데이터가 저장된다. 즉 테이블과 인덱스 같은 것들이 저장된다. tablespace에 의해서 논리적으로 관리된다.
2. Redo Log File : DML(*1), DDL(*2)과 같은 데이터의 변경사항을 실시간 기록한다. 이는 데이터베이스 복구 시의 데이터 무결성을 보장한다.  또한 순환방식(*3)으로 저장된다. 여러 파일이 있을 수 있으며 다중화(*4)를 통해서 장애 복구 가능성을 높인다. 주로 장애 복구에 사용한다.
3. Control File : 데이터베이스 구조에 대한 주요 정보를 저장한다. 데이터파일, Redo Log파일, 테이블 스페이스 등 메타데이터를 관리한다. 데이터베이스가 시작되거나 종료될 때 참조되며 손상될 시 데이터베이스 운영이 불가능해짐으로 백업 관리가 매우 중요하다.

---

2. Oracle Database의 동작 흐름

오라클이 클라이언트의 SQL 요청에 따라서 데이터를 읽고 쓰는 과정에 대해서 알아보자 예시를 참고해서 알아보자

시나리오

• 데이터베이스에 EMPLOYEES라는 테이블이 있고 테이블에는 직원들의 정보가 있다.
• 사용자 ID가 101인 직원이 이름과 급여를 조회하는 SQL을 실행한다.

1. SQL 작성 (User Process)

SELECT EMP_NAME, SALARY 
FROM EMPLOYEES 
WHERE EMP_ID = 101;

• 사용자는 DB 툴을 통해서 SQL을 작성해서 실행한다.
• User Process는 이 SQL을 Listener에 전달해서 실행을 요청한다.

2. 접속 요청 처리 (Listener)

• Oracle의 Listener는 User Process의 접속 요청을 수신한다.
• 절차는 다음과 같다
  1. 데이터베이스 주소, 포트, 서비스 이름등을 확인해서 연결을 시도한다.
  2. 사용자 ID와 비밀번호를 확인해서 접속 권한을 검증한다.
  3. 권한 검증이 성공하면 Server Process를 생성한다.

3. 요청 처리 (Server Process)

• SQL 확인 (Library Cache)
  Server Process는 SQL이 Library Cache(*5)에 있는지 확인한다.
  1. SQL이 Cache에 있다면 이미 있는 실행 계획을 재사용한다.
  2. SQL이 없다면, Parsing을 통해서 실행 계획을 새로 만든다.
• 데이터 확인 (Buffer Cache)
  Buffer Cache(*6)에서 직원아이디가 101인 직원의 데이터 블록을 찾는다.
  1. 데이터가 있다면, 디스크 접근 없이 메모리에서 데이터를 반환한다.
  2. 데이터가 없다면, Datafile에서 필요한 블록을 읽어와서 Buffer Cache에 로드한 후 데이터를 반환한다.

4. 결과 반환

• Server Process는 결과를 User Process에 반환한다.
• User Process에 결과물이 표시되기 때문에 사용자는 확인이 가능하다.

---

3. Oracle의 주요 Background process

1. DBWR (Database Writer)

• 역할 :
  변경된 데이터를 Datafile에 기록해서 데이터를 영구적으로 저장한다.
• 동작 방식 :
  Buffer Cache에서 변경된 데이터 블록을 디스크에 쓰는 작업을 수행하며, 주기적으로 또는 특정 이벤트 (CheckPoint) 시에 발생된다.
• 특징 :
  성능을 위해서 여러 블록에 분산 기록이 가능하며 디스크에 기록이 안 돼도 Redo Log를 통해서 복구가 가능하다.

2. LGWR (Log Writer)

• 역할 :
  변경된 데이터 대한 Redo Log를 Redo Log File에 기록한다.
• 동작 방식 :
  사용자 트랜잭션이 커밋될 때 트랜잭션 변경 내용은 Redo Log File에 기록한다. 주기적 혹은 특정 이벤트(Log Buffer가 가득 찾을 때 실행된다.
• 특징 :
  사용자 데이터 (실제 값)을 저장하는 방식이 아닌, 변경 내용을 기록한다.

3. SMON (System Monitor)

• 역할 :
  데이터베이스의 시스템 복구 작업을 수행한다.
• 동작 방식 :
  데이터베이스가 비정상적인 종료 시 Redo Log를 사용해 변경사항 복구한다.
  복구 중 손상된 테이블스페이스나 데이터 파일을 복구한다.(Instance Recovery(*7), Red Log 활용, UNDO(*8) 데이터 활용)
  사용되지 않는 공간을 병합해 재사용 가능하도록 정리

4. PMON (Process Monitor)

• 역할 :
  비정상 종료된 사용자 process, server process를 감지하고 자원을 정리한다.
• 동작 방식 :
  연결이 끊어진 session(*9)의 Lock을 해제하고, 사용 중이던 메모리나 자원을 회수한다.
  관련 프로세스(사용자, 서버)를 종료한다.
• 특징 :
  비정상 종료된 세션으로 인해 발생할 수 있는 Deadlock(*10)을 방지한다.
  장애가 발생한 Listener나 기타 프로세스를 감지하고 재시작할 수 있다.

5. 그 외

CKPT : checkpoint를 관리하는 프로세스

ARCn : Archive Log File로 복사해서 장기 보관

---

4. 장애 복구 실습 (Redo)

장애를 일으킬 것이기 때문에 test환경에서 진행애야 한다.

1. 시나리오

1. 데이터베이스 테이블에 데이터를 삽입한다.
2. 데이터 파일을 의도적으로 손상시켜서 장애를 유도한다.
3. Redo 로그를 사용해서 데이터베이스를 복구한다.

2. 사전 준비

일단 oracle에 sysplus로 들어가야 한다.

docker로 설치를 진행했음으로 doker의 bash부터 들어가보겠다. (docker로 설치를 하지 않은 사람은 건너뛰어도 된다.)

인텔리제이 bash나 아무 bash로 접속

2.1 dokcer 컨테이너 접속

(root 권한으로 접속해야한다. docker로 만들때 root로 사용자를 해두지 않은 사람은 root로 접속하자)

docker exec -it oracle bash

2.2 oracle 권한 사용자로 변경 후 sqlplus 접속

root@oracle:/# su - oracle
oracle@oracle:~$ sqlplus / as sysdba

2.3 ARCHIVELOG 모드 설정

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

 

이제 bash에서 해도되고 다른 곳에서 해도된다. bash는 끄지말자 이따가 복구할때 다시 사용한다. sql창으로 돌아오자

SELECT LOG_MODE FROM V$DATABASE;

 

이렇게 나오면 mode가 제대로 켜진것이다.

2.4 테이블 생성 및 데이터 삽입

CREATE TABLE EMPLOYEES (
    EMP_ID NUMBER PRIMARY KEY,
    EMP_NAME VARCHAR2(50),
    SALARY NUMBER
);

INSERT INTO EMPLOYEES (EMP_ID, EMP_NAME, SALARY) VALUES (101, 'John', 55000);
INSERT INTO EMPLOYEES (EMP_ID, EMP_NAME, SALARY) VALUES (102, 'Smith', 60000);
COMMIT;

2.5 데이터 파일 

-- 데이터 파일 경로 확인
SELECT NAME FROM V$DATAFILE;

빨간줄을 삭제

 

다른 방식을 써도되지만 지우자

ALTER SYSTEM FLUSH BUFFER_CACHE; --캐시를 지워서 sql명령을 실제 데이터베이스에서 보게함
SELECT * FROM EMPLOYEES; --에러를 출력해서 실제로 지워졌는지 확인

에러를 보자

2.6 데이터 파일 복구

다시 bash로 들어오자

SHUTDOWN IMMEDIATE; --database 중지
STARTUP MOUNT; --database mount 상태로 시작

데이터베이스를 mount로 실행하는 이유는 실제 데이터 파일에 접근하지 않는 모드이기 떄문이다.

지금 tablespace를 강제 삭제 했기 때문에 실제 데이터 파일에 접근하는 경우 에러가 날것이다.

SELECT FILE#, NAME, STATUS 
FROM V$DATAFILE
WHERE NAME LIKE '%mo_dev_dat01.dbf';

삭제한 table space의 상태를 확인하자
아마도 online으로 되어있을 것이다. 이는 제어 파일에 데이터 파일의 메타데이터가 남아있기 때문이다.

ALTER DATABASE DATAFILE '/u01/app/oracle/mo_dev_dat01.dbf' OFFLINE;

테이블 스페이즈를 OFFLINE으로 전환해서 해당 데이터 파일의 접근을 중지할 수 있다.

ALTER DATABASE CREATE DATAFILE '/u01/app/oracle/mo_dev_dat01.dbf' AS '/u01/app/oracle/mo_dev_dat01.dbf';

손상된 데이터 파일을 재생성한다. 손상된 데이터 파일이 물리적으로 존재하지 않기 떄문에 임시로 빈 데이터 파일을 생성한다.

RECOVER DATAFILE '/u01/app/oracle/mo_dev_dat01.dbf';

redo 로그를 사용해서 데이터 파일을 복구한다.

ALTER DATABASE DATAFILE '/u01/app/oracle/mo_dev_dat01.dbf' ONLINE;
ALTER DATABASE OPEN;

데이터 파일을 온라인 상태로 전환하고

복구가 완료됐으니 데이터베이스를 정상적으로 OPEN한다.

SELECT * FROM EMPLOYEES;

이제 다시 select문을 실행하면 정상적으로 데이터가 복구된 것을 볼수 있다.

1. DML : 데이터를 조회 삽입, 수정, 삭제하는 SQL 문장들 (SELECT, INSERT, UPDATE, DELETE)
2. DDL : 데이터베이스 구조를 정의하거나 변경하는 SQL 문장들 (CREATE, ALTER, DROP, TRUNCATE)
3. Redo Log File의 순환(recycling) : 오라클은 REDO 로그파일 그룹을 순서대로 사용하며 하나의 그룹이 가득 차면 자동으로 다음 REDO 로그 그룹으로 전환한다. 모든 로그 파일 그룹을 사용하면 처음 그룹으로 다시 돌아와 덮어쓰기 시작하며 이전 데이터는 checkpoint를 통해서 데이터 파일에 기록한 우 덮어쓴다. 이는 ARCHIVE 모드를 통해서 설정할 수 있다.
4. REDO 로그 다중화(redo log multiplexing) : 여러개의 로그 파일(복사본)을 여러개 만들어서 장애 상황에서도 데이터 손실을 방지하는 방식이다.
5. Library Cache : SGA의 일부로 SQL 문장과 PL/SQL 객체에 대한 정보를 저장하고 관리하는 메모리 공간이며
   SQL 문장의 재사용성을 높이고 성능을 최적화하는데 사용된다.
6. Buffer Cache : SGA의 일부로 데이터 파일에서 읽은 데이터 블록을 메모리에 저장하고 관리하는 공간이다. 이를 통해서 디스크 I/O를 줄이고 성능을 최적화한다. 데이터가 캐싱되어있다면 sql을 내부적으로 실행하지 않는다.
7. Instance Recovery : oracle이 비정상적으로 종료되는 경우 데이터의 일관성을 복구하는 과정
8. UNDO : transaction이 변경한 데이터를 원래 상태로 복구할 수 있도록 저장하는 공간
9. Oracle의 Session : 사용자와 데이터베이스 간의 연결을 의미 하나의 session은 하나의 사용자 연결 process는 작업을 실제로 처리하는 단위임으로 하나의 session이 여러 process를 생성할 수 있다는걸 기억하자 이는 Dedicated Server vs Shared Server에 따라 달라진다
10. DeadLock : 주로 자원에 대한 경쟁에 의해서 생긴다. 예를 들자면 두 trasaction이 동시에 동일한 resource에 접근하려고 할때, 서로에 행에 대해서 ROW LOCK을 기다릴 때 같을떄 생긴다. 즉 병렬처리에 의해서 생긴다.