[Oracle] Oracle Architecture(2) [Oracle 접속 과정]

👇용어정리

• SGA(System Global Area) : 공유메모리 영역으로 데이터를 빠르게 처리하기 위한 공간
• PGA(Program Global Area) : 개별 메모리 공간으로 정렬 중간 데이터, 사용자 세션 정보 등 처리
• Shared Pool: 내가 방금 실행한 SQL 문장이 어떻게 실행될지 짜놓은 '계획서'를 보관합니다. 똑같은 SQL을 던지면 다시 분석 안 하고 여기서 계획서를 꺼내 씁니다
• Library Cache: SQL 문장, 실행 계획을 저장하는 곳
• Data Dictionary Cache:  테이블 정보, 권한, 통계 (설계도)를 저장하는 곳
• Database Buffer Cache: 하드디스크에서 읽어온 실제 데이터(Table 등)를 보관하는 곳입니다. 똑같은 데이터를 또 찾을 때 디스크까지 안 가고 여기서 바로 꺼내주니 속도가 엄청나게 빨라지죠.
• Redo Log Buffer: 데이터에 변화가 생겼을 때(Insert, Update 등) 그 기록을 잠시 보관합니다. 갑자기 정전이 되어도 이 기록을 보고 복구할 수 있게 해주는 보험 같은 공간입니다.
• Large Pool: 백업이나 대용량 처리처럼 메모리를 많이 잡아먹는 작업을 위해 따로 빼놓은 보조 공간입니다.
• Java Pool: 오라클 데이터베이스 내부에서 자바(Java) 코드를 실행할 때 사용하는 전용 메모리 영역입니다.

오라클 접속 및 쿼리 실행 프로세스

 

 

1단계: Client 요청 (Connection Request)

1. 사용자가 SQL*Plus나 SQL Developer 같은 툴을 통해 ID/PW, IP, Port, SID 정보를 들고 오라클 서버의 문을 두드립니다.

2. 접속 정보(ID/PW, IP, Port, SID 등)가지고 sqlnet.ora 파일의 NAMES.DIRECTORY_PATH 설정에 따라 아래와 같은 방식으로 접속 요청을 합니다.

# sqlnet.ora 파일 내용
# 이 설정은 TNSNAMES → EZCONNECT → HOSTNAME 순서로 접속 시도를 합니다.(왼쪽 → 오른쪽순)
NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT, HOSTNAME)

• tnsnames.ora 우선 참조: 클라이언트에 설정된 tnsnames.ora 설정과 비교하여 alias가 있는지 먼저 찾습니다.
• EZCONNECT 우선 참조: 호스트 이름(IP 또는 도메인)으로 직접 접속 

👇 TNSNAMES 접속 방식

 

• tnsnames.ora 설정 (파일에 미리 적어둠)

  ORCL_SERVER =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
      (CONNECT_DATA = (SERVICE_NAME = orcl))
    )
  

   

• 접속 명령 (별명만 사용)
   

  sqlplus scott/tiger@ORCL_SERVER

 

👇 EZCONNECT 접속 방식

설정 파일을 건드리지 않았거나, 급하게 IP만 알고 접속할 때 씁니다.

• 접속 명령 (주소를 직접 입력)

  # 형식: sqlplus ID/PW@IP:PORT/SERVICE_NAME
  sqlplus scott/tiger@192.168.1.100:1521/orcl
  

  참고: 이 방식이 작동하려면 sqlnet.ora에 EZCONNECT가 포함되어 있어야 합니다.

👇 HOSTNAME 방식

DB 서버의 호스트 이름(컴퓨터 이름) 자체가 서비스 이름과 같을 때 사용합니다.

• 상황: DB 서버의 컴퓨터 이름이 db_server_01이고, DB 서비스 이름도 db_server_01인 경우.
• 접속 명령:

  sqlplus scott/tiger@db_server_01
  

  특징: 클라이언트는 db_server_01이라는 이름을 보고 이건 호스트 이름이라고 판단해서 해당 IP의 1521 포트로 접속을 시도합니다.

 

2단계: Listener의 중계 (Hand-over)

1. 서버에서 대기 중이던 Listener가 요청을 확인합니다. Listener는 정적 등록과 동적 등록으로 나뉘어 참조 하는 곳이 다릅니다.

• 정적 등록(Static): listener.ora 파일을 참조하여 lsnrctl status 명령 시 서비스 상태가 UNKNOWN로 표시됩니다.
• 동적 등록(Dynamic): DB 파라미터를 참조하여 기본값은 localhost:1521이고, lsnrctl status 명령 시 서비스 상태가 READY로 표시 됩니다.

2. 리스너는 사용자를 대신해 일을 처리해 줄 Server Process를 Fork()와 Exec() 과정을 통해 생성합니다.

• Fork() : 리스너가 자신과 똑같은 복사본 프로세스를 만들어 메모리 정보와 소켓 권한을 그대로 물려받습니다.
• Exec() : 복제된 프로세스는 리스너 역할에서 Server Process 역할로 교체됩니다.

Tip. 서버의 CPU나 Memory가 꽉 찼다면, 리스너가 Fork()를 하지 못해 접속 지연이 발생합니다.

 

3. 연결이 완료되면 리스너는 다시 대기 상태로 돌아가며, 이후 통신은 클라이언트와 서버 프로세스가 직접 주고받습니다.

 

3단계: Server Process 전담 (PGA 할당)

생성된 서버 프로세스는 사용자를 위한 전용 메모리 공간인 PGA(Program Global Area)를 확보합니다.

여기서는 정렬(Sort)이나 사용자 세션 정보를 관리합니다.

• SQL Work Area: Sort Area(정렬), Hash Area(해시 조인) 등이 포함
• Private SQL Area: 바인트 변수 값, 쿼리 실행 상태 정보가 담깁니다.

 

4단계: SQL 수행 (SGA 탐색 및 I/O)

서버 프로세스가 본격적으로 업무를 수행합니다.

• Shared Pool: SQL 문을 파싱(Parsing)하고 실행 계획이 있는지 확인합니다.
• Database Buffer Cache: 필요한 데이터가 메모리에 있는지 찾습니다.
• Data File: 메모리에 데이터가 없다면 디스크에서 직접 데이터를 읽어(Physical I/O) 메모리에 올립니다.

 이 부분이 가장 디테일이 필요한 구간입니다. 이 부분에 대해서는 다음 시간에 더 깊게 다루어 보겠습니다.

 

5단계: 응답 전달 (Result Set Return)

가공된 데이터를 서버 프로세스가 클라이언트에게 직접 전달하며 한 사이클이 종료됩니다.

모든 결과가 한 번에 전달되는 것이 아니라, 클라이언트의 Fetch Size 설정에 따라 여러 번 나누어 전달될 수 있습니다.

Tip. SELECT 작업이 끝나면 사용된 메모리(PGA 등)는 재사용을 위해 정리되거나 유지됩니다.

 

---

💡시나리오로 감 잡기

1. 서버 OS 자원 부족

• 상황: 리스너가 클라이언트의 요청을 받고 서버 프로세스를 만드는 과정에서 OS에게 fork() 명령을 내렸는데 실패한 상황
• 설명: 포트가 열려 있어도 CPU와 Memory 자원 부족으로 인해 PGA를 할당에 실패하였거나, 서버의 실제 메모리는 넉넉해도 DB 파라미터 설정값이 작으면 서비스 상태를 BLOCKED으로 표시하고 돌려보냅니다.

👇대표적인 에러

1. 리스너 로그(listener.log)에서 발견되는 에러

• TNS-12500: TNS:listener failed to start a dedicated server process
  • 의미: 가장 대표적인 에러입니다. 리스너가 서버 프로세스를 띄우려고 시도했지만 실패했다는 뜻입니다.
• TNS-12540: TNS:internal limit restriction exceeded
  • 의미: OS 레벨에서 생성할 수 있는 프로세스 개수 제한(uprc 등)에 도달했거나 메모리가 부족할 때 발생합니다.
• TNS-12560: TNS:protocol adapter error
  • 의미: OS와 통신하는 과정에서 알 수 없는 오류가 발생했음을 의미하며, 보통 자원 부족과 함께 나타납니다.

2. 클라이언트(사용자) 화면에 뜨는 에러

• ORA-12500: 위 TNS-12500이 클라이언트에게 전달된 형태입니다.
• ORA-12518: TNS:listener could not hand off client connection
  • 의미: 프로세스는 만들었을지 몰라도 클라이언트를 그 프로세스에 '인계(Hand-off)'하는 과정에서 실패했다는 뜻입니다. 메모리가 매우 부족할 때 자주 뜹니다.

3. OS 레벨에서 발생하는 장애 (주로 Unix/Linux)

• fork: Resource temporarily unavailable
  • 의미: OS가 더 이상 프로세스를 생성할 수 없을 때 던지는 비명입니다.
• Out of Memory (OOM) Killer 동작:
  • 의미: 메모리가 너무 없어서 OS가 살아남기 위해 오라클 관련 프로세스를 강제로 종료시킬 수도 있습니다.

 

2. "DB 접속이 너무 느려요" (Library Cache 경합)

• 상황: 접속은 되는데, 평소보다 sqlplus 로그인 창이 뜨는 데 시간이 한참 걸리는 경우.
• 설명:  접속 과정 중 4단계(SQL 수행)의 초기 단계인 Hard Parsing 부하일 가능성이 큽니다. 리스너가 서버 프로세스를 생성(2단계)하고 세션을 맺는(3단계) 과정은 정상이나, 로그인 시 내부적으로 수행되는 딕셔너리 조회 SQL들이 Shared Pool의 Library Cache에서 경합을 일으키면 접속 지연이 발생할 수 있습니다. 이는 동시에 너무 많은 세션이 접속을 시도할 때 자주 발생합니다."

👇대표적인 에러

1. 사용자(클라이언트)가 보게 되는 에러

• ORA-00028: your session has been shed (또는 Timeout 관련 에러):
  • 서버 프로세스가 내부 쿼리를 처리하느라 응답을 못 주면 클라이언트 툴(DBeaver 등)에서 자체적으로 연결을 끊어버릴 때 발생합니다.
• ORA-12170: TNS:Connect timeout occurred:
  • 네트워크 패킷은 주고받았으나, DB 내부에서 세션을 생성하고 초기 SQL을 수행하는 과정이 너무 오래 걸려 설정된 타임아웃 시간을 넘겼을 때 발생합니다.

2. DBA가 v$session_wait에서 보게 될 '비명' (대기 이벤트)

• latch: library cache:
  • 의미: 여러 세션이 동시에 처리하려고 Library Cache라는 좁은 문에 한꺼번에 몰릴 때 발생합니다.
• library cache pin / library cache lock:
  • 의미: 누군가 실행 계획을 수정하거나 생성하는 동안 다른 사람이 그 정보를 건드리지 못하게 잠가버린 상태입니다.
• row cache objects:
  • 의미: 테이블 구조, 권한 정보 등이 담긴 Data Dictionary Cache(Row Cache)를 조회하려고 줄을 서 있는 상태입니다.

 

3. "로컬에서는 접속되는데 외부(PC)에서만 안 돼요"

• 상황: 서버 OS 내에서는 sqlplus / as sysdba로 잘 들어가 지는데, 내 PC의 DBeaver에서는 타임아웃이 나는 경우
• 구조적 설명:접속 주체에 따른 통신 경로의 차이 때문입니다. 로컬 접속은 리스너를 거치지 않고 직접 인스턴스 메모리에 붙는 Bequeath 연결을 사용하지만, 원격 접속은 반드시 리스너(2단계)를 관문으로 통과해야 합니다. 서버 내부 접속이 된다는 것은 인스턴스(SGA)는 정상이지만, 네트워크 경로상의 방화벽 문제나 리스너의 서비스 등록(정적/동적) 설정에 문제가 있음을 시사합니다.

👇대표적인 에러

1. 주요 에러 코드 (클라이언트 측)

• ORA-12170: TNS:Connect timeout occurred
  • 의미: 패킷을 보냈으나 서버로부터 아무런 응답을 받지 못해 시간 초과가 발생한 것입니다.
  • 주요 원인: 네트워크 방화벽(Firewall)에서 오라클 포트(1521 등)를 막고 있거나, 서버의 공인/사설 IP 설정이 잘못되었을 때 나타납니다.
• ORA-12541: TNS:no listener
  • 의미: 서버 IP까지는 도달했으나, 해당 IP의 포트(1521)에서 기다리고 있는 리스너 프로세스가 없을 때 발생합니다.
  • 주요 원인: 리스너가 죽어 있거나, 사용자가 엉뚱한 포트 번호를 입력했을 경우입니다.
• ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
  • 의미: 리스너는 살아있지만, 클라이언트가 요청한 '서비스 이름(Service Name)'을 리스너가 모르고 있는 상태입니다.
  • 주요 원인: DB가 아직 LREG 보고를 안 했거나(동적 등록 전), tnsnames.ora에 서비스 이름을 오타 냈을 때 발생합니다.

 

4. "서버에 프로세스는 떠 있는데 SQL이 응답이 없어요"

• 상황: 접속은 성공해서 세션은 맺어졌는데(3단계), 간단한 SELECT 문 하나가 무한 대기에 빠진 경우
• 구조적 설명:이것은 접속 이후의 4단계(SGA 탐색 및 I/O)문제입니다. 서버 프로세스가 데이터를 읽기 위해Database Buffer Cache에 접근했으나, 해당 데이터 블록을 다른 세션이 변경 중이어서 Lock(Enqueue)이 걸렸거나, 디스크에서 데이터를 읽어오는Physical I/O과정에서 OS 레벨의 병목이 발생해 서버 프로세스가 대기 상태에 빠진 것으로 분석할 수 있습니다.

👇대표적인 에러

1. 주요 장애 현상 및 에러

• ORA-00060: deadlock detected while waiting for resource
  • 의미: 두 세션이 서로 상대방이 잡고 있는 데이터를 기다리며 영원히 빠져나오지 못하는 '교착 상태'에 빠졌을 때 오라클이 강제로 한쪽을 종료시키며 뱉는 에러입니다.
• ORA-01555: snapshot too old
  • 의미: 내 SELECT 문이 너무 오래 돌아서, 참조해야 할 과거의 데이터(Undo)를 다른 세션이 덮어써 버렸을 때 발생합니다. 지난번 업무에서 논의하셨던 Undo Retention 설정과 매우 밀접한 관련이 있는 에러입니다.
• ORA-01652: unable to extend temp segment
  • 의미: SELECT 문에 정렬(Sort) 작업이 포함되어 있는데, 메모리(PGA)가 부족해 디스크(Temp Tablespace)를 쓰다가 그 공간마저 꽉 찼을 때 발생합니다.

2. DBA가 v$session_wait에서 보게 될 대기 이벤트 (범인 찾기)

• enq: TX - row lock contention (데이터 잠금):
  • 상황: 내가 읽거나 수정하려는 행(Row)을 다른 유저가 수정하고 아직 COMMIT을 안 했을 때 발생합니다.
• db file sequential read / db file scattered read (I/O 병목):
  • 상황: 메모리(Buffer Cache)에 데이터가 없어서 디스크에서 읽어오는 중입니다. 이게 너무 길어진다면 스토리지 성능 문제거나 SQL이 너무 많은 데이터를 읽고(Full Scan) 있는 것입니다.
• free buffer waits:
  • 상황: 디스크에서 읽어온 데이터를 메모리에 올려야 하는데, 메모리가 꽉 차서 비어있는 공간(Free Buffer)이 생길 때까지 기다리는 상태입니다. DBWR 프로세스가 일을 제대로 안 하고 있거나 메모리가 부족한 증거입니다.

 

 

-