[정리] 오라클 관리 실무

이 포스팅은 2015년 10월 19일에 작성된 예전 블로그 글을 새로이 옮긴 것입니다. 참고바랍니다.

참고도서
서진수. 『쉬운 설명과 실전 예제가 가득한 오라클 관리 실무』. 생능출판사. 2013

1. 오라클 아키텍처

1) 오라클 인스턴스와 데이터베이스

Instance는 Memory에 생성되는 구조이고, Database는 Disk에 생성되는 구조이다.

2) Oracle Instance의 할당 및 관리

최초 SGA는 Oracle Server Process가 요청하여 생성되고, 이후에는 OS Kernel이 관리한다. Server Process가 종료되어도 SGA는 종료되지 않고, Instance가 종료되어야 SGA가 공유메모리에서 사라진다.

SGA: System Global Area, 거의 대부분의 작업들이 수행되는 공간.

OS Kernel은 RAM의 일부를 Oracle에게 할당하고 관리한다. 이 때 하나의 메모리 Block을 여러 프로그램이 동시에 사용하는 것을 막기 위해 세마포어(Semaphore)와 Kernel Parameter를 사용한다.

세마포어: 깃발(flag)를 의미하며, 어떤 자원의 사용 여부를 set/unset으로 표현한다.
Kernel Parameter: Linux의 경우 /etc/sysctl.conf, Solaris의 경우 /etc/system

Server에서 동작하는 모든 Process는 메모리 Block을 사용하기 전에 그 Block을 다른 Process가 쓰고 있는지 확인하기 위해 해당 메모리 Block의 세마포어의 상태를 확인한다. 만약 사용 중(set)이라면, Process는 대기 후, 해당 Block이 unset이 되면 즉시 세마포어를 set으로 세팅하고 자원을 사용하게 된다.

Kernel Parameter를 기초로 SGA로 사용할 공유 메모리를 Oracle에 할당해 줄 때 다음과 같은 방법으로 할당한다.

공유 메모리로 사용할 물리적 메모리가 충분할 경우, 하나의 segment에 전체 SGA가 할당된다. 하나의 segment에 다 할당할 수 없다면, 연속된 여러 segment로 분산시켜 할당할 수 있다. 위 두 방법이 불가능하면, 여러 segment에 분산시켜 할당할 수 있다. 단, SGA 내 fixed area 부분은 반드시 전체가 1 segment에 할당되어야 한다.

3) SGA의 주요 구성 요소

A. Database Buffer Cache

데이터의 조회와 변경 등의 실제 작업이 일어나는 공간, 여러 명의 사용자가 공유한다.

조회나 변경할 데이터들은 모두 Database Buffer Cache에 그 내용이 존재해야 하며, 없을 경우에는 데이터 파일에서 해당 내용이 들어있는 Block을 복사해서 가져와야 한다.

이 때 Oracle은 Database Buffer Cache의 Block을 여러 사용자가 동시에 사용하는 것을 막기 위해, Buffer Block의 상태를 확인할 수 있는 LRU(Least Recently Used) LIST를 만들어서 관리한다.

공유 메모리에 생성되는 구조들은 서로 중복 사용되지 않도록 관리해야 한다.
Database Buffer Cache Block의 상태의 종류는 다음과 같다.
- Pinned Buffer: 현재 사용 중인 Buffer.
- Dirty Buffer: 현재 사용 중이지는 않지만, 사용자가 내용을 변경한 후, 아직 데이터 파일에 저장하지 않은 Buffer.
- Free Buffer: 사용되지 않은(Unused) Buffer 또는 Dirty Buffer였다가 데이터 파일에 저장이 완료되어 재사용 가능한 Buffer.
Working Data Set(Working Set = LRU List + LRUW List) 은 다음과 같다.
- LRU List
  - 메인 리스트: 사용된 Buffer. Hot/Cold 영역으로 나뉜다.
  - 보조 리스트 : 미 사용된 Buffer. DBWR에 의해 기록된 Buffer(Free List)
    - LRU 보조 리스트에서 가장 먼저 Free Buffer를 찾는다. 만약 보조 리스트의 Free Buffer가 모두 사용되었다면, LRU 메인 리스트의 Cold 영역에서 Free Buffer를 찾는다. 그러나 특정 개수(10g 기준 40%)만큼 찾아도 Free Buffer를 못 찾으면, 스캔을 멈추고 DBWR에게 Dirty Buffer를 내려쓰라고 요청한다. 이후 Dirty Buffer가 Free Buffer로 바뀌어 LRU 보조 리스트에 추가되면, Free Buffer를 확보하고 데이터 파일에서 Block을 복사해 온다.
- LRUW List
  - 메인 리스트: 변경된 Buffer(Dirty List).
  - 보조 리스트: 현재 DBWR에 의해 기록 중인 Buffer.

B. Redo Log Buffer

데이터에 변경사항(DDL, DML)이 생길 경우, 장애 복구를 위해 해당 변경 내용을 기록한다.

Redo Log Buffer: 변경된 내용을 기록하는 메모리 공간.

Redo Log File: Redo Log Buffer의 내용을 디스크에 저장해 주는 파일.

C. Shared Pool

다른 사용자와 어떤 대상을 공유해서 사용하기 위한 공간.

Library Cache: Soft Parse에 사용되는 공간

Data Dictionary Cache: 구문분석이나 Optimizer가 실행계획을 세울 때 사용하는 주요 Dictionary들이 Row 단위로 Cache되어 있는 공간.

Server Result Cache: SQL 결과 값을 Cache해 두는 공간.

D. Large Pool

Large Pool을 사용하는 주요 경우는 다음과 같다.

Shared Server Mode로 Oracle Server를 운영할 경우, UGA가 이곳에 생성된다.
Parallel Execution(병렬처리) 작업을 할 경우, 각 Process들 간의 Message Buffer가 이곳에 생성된다.
RMAN으로 백업/복구를 할 경우, RMAN이 사용하는 I/O용 Buffer가 이곳에 생성된다.

4) Dynamic SGA 기능

관리자가 _SGA의 구성요소의 크기_를 변경한 후, Oracle Instance의 재 시작 없이 즉시 적용할 수 있는 기능.

Oracle에서 메모리를 할당할 때 사용하는 단위는 그래뉼(Granule)이다. 10g 이후 버전 기준으로, SGA_MAX_SIZE가 1GB 이하면 1Granule은 4MB, 1GB 초과면 16MB가 된다.

주요 Parameter는 다음과 같다.

SGA_MAX_SIZE: 현재 사용 중인 SGA 크기.
SHARED_POOL_SIZE: Shared Pool의 크기.
DB_CACHE_SIZE: Database Buffer Cache 크기.

5) Program Global Area(PGA)의 주요 구성 요소.

각 Process들이 개별적으로 사용하는 메모리 공간.

A. Private SQL Area

SQL 문장에 Bind 변수 등이 있을 경우, 해당 Bind 변수 값을 보관(Persistent Area)하고, 쿼리의 실행 상태 정보와 쿼리를 수행하면서 임시로 정보를 저장(Runtime Area)해야 하는 경우에 사용하는 공간.

B. SQL Work Area

Sort 관련 작업(Sort Area)이나 Hash 관련 작업(Hash Area)을 수행하는 공간.

9i부터는 초기화 파라미터 파일에 PGA의 총량(PGA_AGGREGATE_TARGET)을 설정 한 후, WORKAREA_SIZE_POLICY 파라미터를 AUTO로 설정하면, Oracle Server가 자동으로 PGA의 크기를 관리한다.

2. SQL 문장의 실행

1) Select 문장의 실행 원리

A. Parse(구문 분석 단계)

Server Process는 전달 받은 SQL 문장을 SQL Parser를 통해 문법 검사(Syntax Check), 의미 검사(Semantic Check), 권한 검사(Permission Check)를 한다.

검사 후 오류가 없다면, SQL 문장을 Hash 함수를 사용하여 Hash Value를 얻은 후, Shared Pool의 Library Cache에 있는 Hash Value와 비교하여 동일한 값이 있는지 확인한다. 이 과정을 커서 공유 또는 88라 한다.

Hash Value들이 저장되어 있는 공간을 해쉬 버킷(Hash Bucket)이라 한다.
커서 공유: 메모리에 어떤 데이터를 저장하기 위해 만드는 임시 저장 공간으로 공유 커서, 세션 커서, 어플리케이션 커서가 있다. 공유 커서의 역할은 이미 한번 수행되었던 SQL 문장의 실행계획과 관련 정보를 보관한 후, 재활용하여 Hard Parse의 부담을 줄여 SQL 문장의 수행 속도를 빠르게 하는 것이다. 주의할 점은 부모 커서(Parent Cursor)와 자식 커서(Child Cursor) 모두가 완전히 동일해야 한다.

공유 커서는 크게 부모 커서(Parent Cursor)와 자식 커서(Child Cursor)로 나뉜다. SQL 문장 자체에 대한 값은 부모 커서에 저장되고, 사용자 정보나 Optimizer Mode 정보는 자식 커서에 저장된다.

커서에 어떤 데이터가 들어있는지 리스트로 관리하는데 이를 88라 한다. Hash List 안의 정보는 체인(Chain) 구조로 다음 데이터가 연결되어 있다.

Oracle에서 데이터는 Heap 구조(순서 상관없이 빈 곳에 저장)로 저장된다. 따라서 List에는 같은 정보는 계속 이어서 확인 할 수 있도록 체인 구조로 연결되어 있다.
Library Cache 안에서 원하는 SQL 문과 실행계획이 들어있는 커서를 찾기 위해서는 반드시 Hash List를 읽어야 하는데, 1개 밖에 존재하지 않아 경합이 발생한다. 이 때 Library Cache Latch를 획득해야 한다.

그러나 동시에 여러 사용자가 Latch를 획득하려는 경우 대기현상으로 성능저하가 발생하므로, Oracle에서는 Hash List를 조회만 할 경우에는 동시에 여러 명이 볼 수 있는 기능을 제공하고, 또 SESSION_CACHED_CURSORS 파라미터 제공한다.

Hash List를 조회만 한다는 것은, 사용자가 Select 문을 수행했다는 뜻이 아니다. 어떤 SQL 문이든 Hash List에 해당 내용이 있는지 확인하는 경우를 말한다. 단, Hash List의 내용을 변경(Update)할 때에는 Exclusive로 동작한다.

커서 공유가 실패하면 옵티마이저(Optimizer)는 Dictionary Cache의 Data Dictionary를 참고하여 새로운 실행 계획을 생성한다. 이를 Hard Parse라고 한다. Hard Parse 단계는 Soft Parse 단계에 비해 시간이 많이 소요되므로 가능한 Soft Parse를 할 수 있도록 쿼리를 작성해야 한다.

옵티마이저: Rule Based Optimizer(RBO)와 Cost Based Optimizer(CBO)로 나눌 수 있다. 11g 버전부터는 CBO만 사용한다.
Data Dictionary: 옵티마이저가 참고하는 Dictionary의 대부분은 Static Data Dictionary이다. 10g 버전부터는 Data Dictionary 정보를 자동으로 업데이트하지만 항상 확인해야 한다.

B. Bind(바인드)

1,000개의 데이터 중 특정 값만 바꾸어서 조회하는 SQL 문장의 경우, 1,000번의 Parsing와 1,000개의 실행계획을 생성해야 한다.

이 때 특정 값을 바인드 변수로 설정하여, 1번의 Parsing과 1개의 실행계획만으로 쿼리를 수행할 수 있도록 하는 것을 Bind라고 한다. 따라서 SQL을 작성할 때 Bind 기능을 사용할 수 있도록 작성하는 것이 좋다.

Bind를 사용할 경우 Soft Parse를 많이 하므로 성능 상에 이점이 있지만, 데이터가 편중(Skewed)되어 있는 경우에는 Index가 정상적으로 작동하지 않는다. 즉 분포도가 균일하지 못한 경우에는 Bind 변수를 사용할 수 없다.

C. Execute(실행)

Parse와 Bind를 거치고 나면, Server Process는 Database Buffer Cache에서 해당 데이터가 들어있는 Block를 찾는다. 만약 없다면, 데이터 파일에서 데이터가 들어있는 Block을 찾아 Database Buffer Cache로 복사해 오는데, 이 과정을 Execute라고 한다.

D. Fetch(인출)

실행 단계가 끝나면 Database Buffer Cache에 원하는 데이터와 다른 데이터가 포함된 Block이 존재하게 된다. 여기서 원하는 데이터만 선별하는 과정을 Fetch라고 한다. 만약 사용자가 정렬(Sort)을 요구한 경우 Fetch 과정에서 Sort를 완료해서 데이터를 보내게 된다.

데이터의 최소 I/O 단위가 Block이기 때문에, 복사한 Block에 원하는 데이터뿐만 아니라 다른 데이터도 존재하게 된다.

2) Update 문장의 실행 원리

Parse 과정까지는 Select 문장과 동일하게 진행된다.

Execute 단계에서 원하는 데이터가 들어있는 Block을 Database Buffer Cache로 가져 온 후, Server Process는 변경되는 데이터의 변경 내역을 Redo Log Buffer에 먼저 기록한다.

그 후, Undo Segment에 원본 데이터를 기록하고 Database Buffer Cache의 내용을 변경한다.

3. Oracle Background Processes

1) DBWR(Database Writer)

Database Buffer Cache의 Dirty Buffer의 내용을 다음과 같은 경우에 데이터 파일에 저장한다.

Checkpoint 신호가 발생했을 때.
Dirty Buffer가 임계 값을 지났을 때.
Time Out이 발생했을 때(3초).
RAC Ping이 발생했을 때.
Tablespace가 Read-only 상태로 변경될 때.
Tablespace가 Offline될 때.
Tablespace가 Begin Backup 상태가 될 때.
Drop table이나 Truncate Table될 때.
Direct Path Read/Write가 진행될 때.
일부 Parallel Query 작업이 진행될 때.

2) LGWR(Log Writer)

Redo Log Buffer에 있는 내용을 다음과 같은 경우에 Redo Log File로 저장한다.

Commit이 발생했을 때.
1/3이 찼을 때.
변경 량이 1MB가 되었을 때.
3초 마다.
DBWR이 내려 쓰기 전에.

3) PMON(Process Monitor)

모든 Server Process들을 감시하고 비정상적으로 종료된 Process가 있다면 관련 복구 작업 등을 한다.

4) SMON(System Monitor)

Instance Crash인 경우, Instance Recovery를 수행한다.
Instance Recovery 과정에서 누락된 Transaction을 Recovery한다.
비정상 종료된 Transaction이 사용 중이던 Temporary Segment를 Clean Up한다.
Dictionary Managed Tablespace에서 Free Extents들을 모은다.

5) CKPT(Checkpoint Process)

DBWR에게 Checkpoint 신호를 전달하며, Control File과 Data File Header에 해당 Checkpoint 정보를 기록한다.

4. Oracle 시작/종료하기

1) 초기화 Parameter File

A. Parameter란?

SGA를 생성할 때 필요한 각 구성요소에 대한 사항들을 기록하고 있는 파일.

정적 파라미터인 Pfile(initSID.ora)과 정적 파라미터인 Spfile(spfileSID.ora)이 있다. $ORACLE_HOME/dbs/에 위치한다.

Pfile과 Spfile이 둘 다 존재할 경우 Spfile의 내용만 사용한다.

B. Parameter File 내용 변경.

Pfile은 vi 에디터 등의 OS 편집기로 관리자가 직접 수정하면 된다.

Spfile은 ALTER SYSTEM SET 변경할_파라미터 SCOPE=옵션 으로 파라미터 값을 수정할 수 있다.

옵션은 다음과 같다.

MEMORY: Spfile의 내용을 변경하지 말고, 현재 작동 중인 Instance에만 적용. 테스트용.
SPFILE: 현재 운영 중인 Instance에는 적용하지 않고, Spfile의 내용만 변경. 재 부팅 후에 적용된다.
BOTH: 현재 운영 중인 Instance와 Spfile 내용 모두 변경.

C. 주요 Parameter

CONTROL_FILES: Control File의 경로 지정. 최대 8개까지 등록 가능.
DB_BLOCK_SIZE: Database에서 사용될 Standard Block Size 지정. Database를 생성하기 전에 지정해야 한다. 9i 버전까지는 4KB, 10g 이후 버전부터 기본 값은 8KB.
DB_CACHE_SIZE: Default Database Buffer Cache 크기를 결정.
DB_nK-CACHE_SIZE: Non-Standard Block Size 지정.
LOG_ARCHIVE_DEST_n: Archive Redo Log File의 저장 경로 지정. 최대 10개까지 등록 가능.
LOG_ARCHIVE_DEST_STATE_n: LOG_ARCHIVE_DEST_n에 지정된 디렉터리의 사용 여부 상태를 지정. 사용되는 값으로 ENABLE, DEFER, ALTERNATE가 있다.
PGA_AGGREGATE_TARGET: 하나의 Instance에 접속한 전체 Server Process가 사용 가능한 총 PGA 크기를 결정.
SGA_TARGET: ASMM을 사용할 경우 SGA의 전체 사이즈를 지정. 이 파라미터를 사용할 경우 다음의 값들이 자동 튜닝의 대상이 된다.
- Buffer Cache(DB_CACHE_SIZE)
- Shared Pool(SHARED_POOL_SIZE)
- Large Pool(LARGE_POOL_SIZE)
- Java Pool(JAVA_POOL_SIZE)
- Streams Pool(STREAMS_POOL_SIZE)
UNDO_TABLESPACE: Instance에서 사용할 UNDO Tablespace 이름 지정.

2) Instance Open하기

오라클의 시작 과정은 NOMOUNT → MOUNT → OPEN의 단계를 거친다.

NOMOUNT 단계에서 Parameter File의 내용을 읽고, MOUNT 단계에서 Control File을 읽고, OPEN 단계에서 Data File과 Redo Log File을 읽는다.

NOMOUNT 단계까지만 시작한 후 나머지 단계 진행하기.

SYS> STARTUP NOMOUNT;
SYS> ALTER DATABASE MOUNT;
SYS> ALTER DATABASE OPEN;

MOUNT 단계까지 시작한 후 나머지 단계 진행하기

SYS> STARTUP MOUNT;
SYS> ALTER DATABASE OPEN;

읽기전용인 상태로 OPEN하기

SYS> STARTUP MOUNT;
SYS> ALTER DATABASE OPEN READ ONLY;

Restricted Mode(제한된 모드)로 OPEN하기

SYS> STARTUP RESTRICT;

현재 OPEN되어 있는 Instance를 Restricted Mode로 변경하기

SYS> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SYS> ALTER SYSTEM DISABLE RESTRICTED SESSION;

3) Instance Shutdown하기

Startup의 역순으로 종료된다. Shutdown의 옵션은 다음과 같다.

NORMAL(기본 옵션): 사용자들이 모두 스스로 접속을 종료할 때까지 기다렸다가 종료.

TRANSACTIONAL: 사용자가 수행중인 Transaction이 끝나는 시점에 강제로 접속을 중단 시키고, Instance를 종료.

IMMEDIATE: 사용자의 행동에 상관없이 즉시 접속 종료.

ABORT: IMMEDIATE와 동일하나, 사용자가 수행한 작업을 저장하지도 ROLLBACK하지도 않고, 즉시 Instance를 종료한다. 다른 말로 Instance Crash라고 한다. 이렇게 종료된 Instance는 Startup될 때 SMON이 Instance Recovery를 수행해서 복구해야 한다.

4) Parameter File 생성 및 관리

사용중인 Parameter File 확인. VALUE 부분에 값이 있으면 Spfile, 없으면 Pfile이다.

SYS> SHOW PARAMETER SPFILE;
SYS> SHOW PARAMETER PFILE;

Pfile, Spfile 만들기

Pfile과 Spfile이 둘 다 존재할 경우에는, Spfile을 사용한다.

SYS> CREATE PFILE FROM SPFILE;
SYS> CREATE SPFILE FROM PFILE;

Parameter File이 모두 삭제된 경우에는, 원본 Pfile을 복사해서 사용한다. 원본 Pfile의 위치는 다음과 같다.

[oracle@localhost ~]$ cp $ORACLE_BASE/admin/SID/pfile/init.ora.num $ORACLE_HOME/dbs/initSID.ora

5. Control File 관리하기

1) Control File

Control File에는 Oracle Server를 운영하는 데 필수적인 정보들이 저장되어 있다.

Instance가 Mount 이상의 상태부터는 Control File의 내용이 실시간으로 변경되고 조회된다.

만약, 삭제 될 경우 심각한 장애가 발생하므로 삭제되지 않도록 주의해야 하며, 혹시나 삭제되더라고 복구 할 수 있도록 다중화(Multiplexing) 해야 한다.

다중화: Control File에는 변경되는 정보가 실시간으로 저장되기 때문에, 이동이나 복사를 할 때에는 가장 최근에 사용한 Control File을 사용해야 한다.

Control File의 위치는 Parameter File에서 지정할 수 있다.

2) Control File 관리하기

A. Spfile일 경우 다중화 하는 방법

SYS> ALTER SYSTEM SET CONTROL_FILES=‘/disk1/control01.ctl’,
                                    ‘/disk2/control02.ctl’,
                                    ‘/disk3/control03.ctl’ SCOPE=SPFILE;
SYS> SHUTDOWN IMMEDIATE;
SYS> !cp /app/oracle/oradata/SID/control01.ctl /disk1/control01.ctl
SYS> !cp /app/oracle/oradata/SID/control01.ctl /disk1/control02.ctl
SYS> !cp /app/oracle/oradata/SID/control01.ctl /disk1/control03.ctl
SYS> STARTUP

B. pfile일 경우 다중화 하는 방법

SYS> SHUTDOWN IMMEDIATE;
SYS> !vi $ORACLE_HOME/dbs/initSID.ora
-- initSID.ora 파일의 control_files 수정
*.control_files=‘/disk1/control01.ctl’, ‘/disk2/control02.ctl’, ‘/disk3/control03.ctl’

SYS> !cp /app/oracle/oradata/SID/control01.ctl /disk1/control01.ctl
SYS> !cp /app/oracle/oradata/SID/control01.ctl /disk1/control02.ctl
SYS> !cp /app/oracle/oradata/SID/control01.ctl /disk1/control03.ctl
SYS> STARTUP

6. Redo Log 관리하기

1) Redo Log 생성 원리

Oracle에서 데이터의 변경(DDL, DML, TCL)이 발생한 경우, Write Log Ahead, Log force at Commit이라는 두 가지 메커니즘에 의해 Redo Log에 기록된다.

Write Log Ahead: 실제 데이터를 변경하기 전에 Redo Log에 먼저 기록한 후 데이터를 변경한다.
Log force at Commit: 사용자로부터 Commit 요청이 들어오면, 관련된 모든 Redo Record들을 Redo Log File에 저장한 후 Commit을 완료한다. 단, 대량의 데이터를 변경하고 한꺼번에 Commit을 할 경우, 성능에 악영향을 줄 수 있다. 이를 해결하기 위해 지연된 커밋(Delayed Commit), 그룹 커밋(Group Commit)을 사용한다.

Redo Log가 생성되고 기록되는 순서는 다음과 같다.

(특정 데이터의 변경 시) Server Process가 원하는 Block이 Database Buffer Cache에 있는 확인하고 없으면 Data File에서 찾아서 Database Buffer Cache로 가져온다. 그리고 해당 Block의 해당 row 부분을 다른 사용자가 변경할 수 없도록 Lock을 설정(Page Fix)한다.
PGA에서 Redo Change Vector를 생성한 후, Redo Log Buffer에 필요한 용량을 계산한다. 그리고 복사하기 위해 필요한 Redo Copy Latch를 확보해야 한다.
- Redo Change Vector: Redo Log에 기록할 변경된 데이터에 대한 모든 정보의 세트. UNDO 관련 내용도 함께 저장된다.
- Redo Copy Latch: Redo Copy Latch는 Change Vector가 모두 Redo Log Buffer에 기록될 때까지 확보하고 있어야 하기 때문에 여러 개가 존재한다. _log_simultaneous_copies 히든 파라미터를 사용하여 Redo Copy Latch의 개수를 조정할 수 있다.
Redo Copy Latch를 확보한 Server Process는 다음 단계인 Redo Allocation Latch를 확보하고, Redo Log Buffer에 Change Vector의 내용을 기록한다.
- Redo Allocation Latch: Redo Allocation Latch는 1개 밖에 없어서 경합이 발생할 수 있다. 9i 버전부터는 이를 해결하기 위해 Redo Log Buffer를 여러 공간으로 나누어서 각각 Redo Allocation Latch를 할당해주는 Shared Redo Strand를 도입했다. 10g 버전부터는 더 확장된 개념인 Private Redo Strand 기능이 도입되었는데, 각 Server Process는 Shared Pool에 자신만의 독립적인 Private Strand 공간을 만들고 그곳에 Change Vector를 생성한 후, 필요하면 LGWR이 Redo Log File에 바로 기록할 수 있다.
특정 상황(LGWR 작동 조건)이 되면, LGWR이 Redo Entries(Redo Log Buffer에 기록된 Change Vector)의 일부를 Redo Log File에 기록한다. 그리고 기록된 Redo Entries들은 Redo Log Buffer에서 삭제(Flush)된다.
- LGWR이 Redo Log File에 내려쓰는 방법에는 4가지(WAIT, NOWAIT, IMMEDIATE, BATCH)가 있다. 보통 2개씩 조합해서 사용한다. BATCH와 NOWAIT와 같이 Redo Log Buffer의 내용이 아직 Redo Log File에 기록이 완료되지 않아도 다른 작업을 할 수 있도록 성능을 높이는 방식을 비동기식 커밋(Asynchronous Commit)이라 하고, 반대로 IMMEDIATE와 WAIT와 같이 Redo Log File에 기록을 완료해야 후속 작업을 할 수 있는 방식을 동기식 커밋(Synchronous Commit)이라 한다.

2) Redo Log File 구성 및 관리하기

Redo Log File은 최소 3개의 그룹과 각 그룹별로 최소 2개 이상의 멤버로 구성하는 것을 권장한다.

LGWR이 Redo Log Buffer의 내용을 Redo Log File에 내려쓰다가, 해당 파일이 가득 차게 되면 다음 그룹으로 자동으로 넘어가서 마저 내려쓴다. 이 과정을 _Log Switch(로그 스위치)_라고 한다.

Log Switch가 일어나는 그룹의 순서는 Round Robin 방식으로 결정된다.

Log Switch가 발생하면 _Checkpoint 신호_가 발생하는데, 이 때 DBWR이 해당 Redo Log File에 있는 내용 중 Database Buffer Cache에서 Data File로 저장하지 못한 변경사항들을 내려쓴다. 그리고 이 정보들을 Data File와 Control File에 반영한다.

만약 Alert Log File에 Checkpoint Not Completed라는 오류가 나온다면, 이는 DBWR이 이전에 발생한 Checkpoint의 내용을 Data File에 다 기록하지 못한 상황에서 다시 Log Switch가 발생하여 Checkpoint 신호가 들어온 경우에 발생한다. Redo Log File의 크기나 Redo Log Group의 개수를 증가시키면 된다.

신규 그룹 생성

SYS> ALTER DATABASE ADD LOGFILE GROUP 4 ‘/app/oracle/oradata/SID/redo04_a.log’ SIZE 5M;

멤버 추가하기

SYS> ALTER DATABASE ADD LOGFILE MEMBER ‘/app/oracle/oradata/SID/redo04_b.log’ TO GROUP 4;

멤버 삭제하기

SYS> ALTER DATABASE DROP LOGFILE MEMBER ‘/app/oracle/oradata/SID/redo04_b.log’;

그룹 삭제하기

SYS> ALTER DATABASE DROP LOGFILE GROUP 4;

강제로 Log Switch 발생시키기

SYS> ALTER SYSTEM SWITCH LOGFILE;

강제로 Checkpoint 발생시키기

SYS> ALTER SYSTEM CHECKPOINT;

3) SCN과 CHECKPOINT

A. SCN(System Commit Number)

Commit이 발생할 때 마다 모든 Transaction에 생성되는 번호로 장애 복구 시에 사용한다.

SCN base(4bytes) + SCN Wrap(2bytes)로 구성.

SCN이 기록되는 장소는 다음과 같다.

Control File Header: Checkpoint 발생 때, Resetlogs 발생 때, Incomplete Recovery 수행 때.
Data Blocks(Cache Layer): Block Cleanout시 마지막 SCN을 각 Block에 기록.
Data Block에 걸려있던 Lock이 해제되어야 Commit이 완전히 수행된다.
- Data Blocks(ITL Entries): Data Block의 Transaction Layer 안에 있는 ITL(Interested Transaction List) Entries에 Commit된 SCN 정보를 기록(Delayed Block Cleanout).
Data File Headers: 마지막 Checkpoint 발생 때, Begin Backup 수행 때, 복구 완료 후 마지막으로 사용된 SCN을 기록.
Redo Records / Log Buffer: Commit이 수행되면 Commit Record에 SCN을 포함하여 기록.
Undo Segment, Tablespace Headers

B. SCN(System Change Number)

Data File, Redo Log File, Control File 간의 동기화 정보를 맞추기 위해 사용.

SCN_Base(4bytes) + SCN_Wrap(2bytes) + SCN_Sequence(1byte)로 구성.

Data Block Header, Redo Records, Segment Header에 기록된다.

C. Checkpoint

Commit된 데이터를 어디까지 저장했는지 확인하기 위해 사용.

Control File과 Data File의 Checkpoint 정보를 비교하여, 서로 정보가 다르면 해당 부분을 Online Redo Log나 Archived Redo Log를 참고하여 복구를 진행한다.

Checkpoint가 발생하면 Database Buffer Cache의 Dirty Buffer를 Data File로 저장하게 된다. 그리고 저장된 SCN 중 가장 큰 SCN 번호(Checkpoint SCN)를 Control File와 Data File Header에 기록한다.

7. Tablespace와 Data File 관리하기

1) Tablespace의 정의

하나 또는 여러 개의 데이터파일로 구성되어 있는 논리적인 데이터 구조.

2) SYSTEM Tablespace

Data Dictionary들이 저장되어 있다. SYS 사용자도 조회만 가능할 뿐 변경할 수는 없다.

Data Dictionary: Oracle에서 사용하는 거의 모든 메타 정보나 운영과 관련된 정보들이 들어있다.

Data Dictionary는 Base Table과 Data Dictionary View로 나뉜다.

Base Table: Database를 생성할 때 생성된다. 원본 데이터가 있는 곳이라 접근할 수 없다.
Data Dictionary View: 이곳을 통해 Base Table의 내용을 조회할 수 있다.

Data Dictionary View는 다시 Static Dictionary와 Dynamic Dictionary로 나뉜다.

Statis Dictionary: Dictionary에 저장된 정보들이 자동으로 변경되지 않는다. 따라서 ANALYZE, DBMS_STATS를 사용하여 수동으로 내용을 업데이트해야 한다. 10g 버전부터는 AWR을 사용하여 자동으로 업데이트 된다. Instance가 Open일 경우에만 조회할 수 있다.

Dictionary 수동 업데이트

ANALYZE 명령어는 사용하지 않는 것이 좋다.

-- scott 계정의 emp 테이블을 대상으로 통계 정보를 수집하되, 인덱스 및 컬럼 통계가 수집되지 않도록.
DBMS_STATS.GATHER_TABLE_STATS('scott', 'emp',
                              CASCADE => false,
                              METHOD_OPT => 'for columns')

Static Dictionary 종류

OPEN 상태에서만 조회 가능.

USER_XXX: 해당 사용자가 생성한 내용만 조회 가능.
ALL_XXX: 해당 사용자가 생성한 것과 생성하지 않았더라도 접근 가능한 내용 조회 가능.
DBA_XXX: 데이터베이스에 생성되어 있는 모든 내용 조회 가능. DBA 권한 필요.

Dynamic Performance View

v$controlfile과 같이 v$로 시작. NOMOUNT 상태부터 조회 가능.

3) SYSAUX Tablespace

튜닝 관련 딕셔너리들이 저장되어 있다.

(10g 기준)AWR, ADDM, ASH 등과 같은 자동 튜닝 기능들이 이 곳의 정보를 사용.

4) 일반 Tablespace

관리자가 필요에 의해 생성

일반 Tablespace 생성 및 조회

SYS> CREATE TABLESPACE haksa DATAFILE ‘/app/oracle/oradata/testdb/haksa01.dbf’ SIZE 1M;
SYS> SELECT tablespace_name, status, contents,
            extent_management, segment_space_management
 2   FROM dba_tablespaces;
SYS> SELECT tablespace_name, bytes/1024/1024 MB, file_name
 2   FROM dba_data_files;

5) Tablespace 관리

Tablespace에 Data file 추가

SYS> ALTER TABLESPACE haksa ADD DATAFILE ‘/app/oracle/oradata/testdb/haksa02.dbf’ SIZE 20M;

Data File 크기 수동 조정

SYS> ALTER DATABASE DATAFILE ‘/app/oracle/oradata/testdb/haksa01.dbf’ RESIZE 20M;

Data File 크기 자동 증가

SYS> ALTER DATABASE DATAFILE ‘/app/oracle/oradata/testdb/haksa01.dbf’ AUTOEXTEND ON;

Tablespace Offline

Normal Mode

SYS> ALTER TABLESPACE haksa OFFLINE;

Temporary Mode: Offline 시키려는 Tablespace의 Data File에 이상이 있을 경우에 사용.

SYS> ALTER TABLESPACE haksa OFFLINE TEMPORARY;

Immediate Mode: Data File에 장애가 발생하여 데이터를 내려쓰지 못하는 상황에서 Tablespace를 Offline 해야 할 경우에 사용. ARCHIVE LOG MODE일 경우에만 사용.

SYS> ALTER TABLESPACE haksa OFFLINE IMMEDIATE;

Data file Offline

ARCHIVE LOG MODE

SYS> ALTER DATABASE DATAFILE ‘/app/oracle/oradata/testdb/haksa01.dbf’ OFFLINE;

NO ARCHIVE LOG MODE

SYS> ALTER DATABASE DATAFILE ‘/app/oracle/oradata/testdb/haksa01.dbf’ OFFLINE DROP;

Tablespace Online

Offline 후 Online 할 때에는 반드시 Checkpoint를 발생시켜 SCN 번호를 동기화 시켜야 함.

ARCHIVE LOG MODE

SYS> ALTER TABLESPACE haksa ONLINE;

NO ARCHIVE LOG MODE

SYS> RECOVER TABLESPACE haksa;
SYS> ALTER TABLESPACE haksa ONLINE;

Offline되는 Tablespace의 Data File 이동

해당 Tablespace Offline
Data File을 대상 위치로 복사
Control File 내의 해당 Data File 위치 변경
해당 Tablespace Online

SYS> ALTER TABLESPACE haksa RENAME
 2   DATAFILE ‘/app/oracle/oradata/testdb/haksa01.dbf’
 3   TO ‘/app/oracle/disk1/haksa01.dbf’;

Offline 안 되는 Tablespace의 Data File 이동

Redo Log File 이동도 같은 방법으로 진행, SYSTEM Tablespace, 사용중인 UNDO Tablespace, Default Temporary Tablespace

DB 종료
MOUNT 상태로 시작
Data File 복사
Control File 내용 변경
DB OPEN

SYS> ALTER DATABASE RENAME
 2   FILE ‘/app/oracle/oradata/testdb/system01.dbf’
 3   TO ‘/app/oracle/disk3/system01.dbf’;

Tablespace 삭제

SYS> DROP TABLESPACE haksa [including contents and datafiles];

6) UNDO Tablespace

Undo Data 만을 저장하는 Segment를 Undo Segment라 하고, Undo Segment를 저장하고 있는 Tablespace가 UNDO Tablespace이다.

Undo Data: 사용자가 DML을 수행할 경우에 발생하는 원본 데이터

Undo Tablespace 사용 목적

Transaction Rollback: Undo Segment에 저장된 Undo Data를 사용하여 원래의 값으로 복원하는 것. 사용자가 Rollback 명령을 실행하거나, Transaction이 비정상 종료되어 PMON이 자동 Rollback 처리하는 경우에 실행.
Read Consistency(읽기 일관성): Transaction이 진행되는 동안, 데이터베이스의 다른 사용자는 CR작업을 통해 변경 전 원본 데이터를 보게 된다.
Transaction Recovery(Instance Recovery): DB가 비정상 종료되었을 때 Roll Forward, Roll Backward 작업을 수행하여, Dirty Database를 Clean Database로 고치는 과정에서 Undo Data가 사용.

Commit을 수행하면 다른 Server Process가 덮어 쓸 수 있을 뿐, Undo Segment 안의 Undo Data를 Delete하는 것은 아니다.

UNDO Tablespace 조회

SYS> SHOW PARAMETER UNDO;

UNDO Tablespace 생성

SYS> CREATE UNDO TABLESPACE undo01
 2   DATAFILE ‘/app/oracle/oradata/testdb/undo01.dbf’ SIZE 10M
 3   AUTOEXTEND ON;

UNDO Tablespace 변경

SYS> ALTER SYSTEM SET undo_tablespace = undo01;

UNDO 관련 주요 Parameter

UNDO_RETENTION: 단위는 초. Commit 수행 후에도 해당 Undo Segment 내의 데이터를 다른 Server Process가 덮어쓰지 못하도록 막아주는 시간. ORA-01555. Snapshot too old 에러 예방 시 사용.

SYS> ALTER SYSTEM SET undo_retention = 시간;

UNDO_RETENTION_GUARANTEE: Undo Segment 용량이 부족해도, Undo Retention에 설정된 시간 동안 재활용 하지 않는다.

SYS> ALTER TABLESPACE undotbs1 RETENTION [NO]GUARANTEE;

UNDO Tablespace 삭제

SYS> DROP TABLESPACE undotbs1;

7) Temporary Tablespace

정렬 작업 등을 수행할 경우, PGA 공간이 부족하면 이 곳을 사용한다.

성능 향상을 위해 각 사용자 별로 하나씩 각각 할당하는 것이 좋다.

기존 Temporary Tablespace 조회

SYS> SELECT file_id, tablespace_name, bytes/1024/1024 mb, file_name
 2   FROM dba_temp_files;

신규 Temporary Tablespace 생성

SYS> CREATE TEMPORARY TABLESPACE temp2
 2   TEMPFILE ‘/app/oracle/oradata/testdb/temp02.dbf’ SIZE 10M
 3   AUTOEXTEND ON;

Default Temporary Tablespace 설정 및 조회

SYS> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
SYS> SELECT * FROM database_properties
 2   WHERE property_name like ‘DEFAULT_TEMP%’;

Temporary Tablespace 크기 조정

SYS> ALTER DATABASE TEMPFILE ‘/app/oracle/oradata/testdb/temp02.dbf’ RESIZE 100M;

Temporary Tablespace 삭제

삭제하기 전에 Default Temporary Tablespace 지정 해제.

SYS> DROP TABLESPACE temp2;

(11g) Temporary Tablespace Group

그룹 생성

SYS> CREATE TEMPORARY TABLESPACE temp_scott1
 2   TEMPFILE ‘/app/oracle/oradata/testdb/temp_scott01.dbf’ size 10M
 3   TABLESPACE GROUP temp_scott_group;

그룹에 Tablespace 추가

SYS> ALTER TABLESPACE temp_scott2 TABLESPACE GROUP temp_scott_group;

그룹 조회

SYS> SELECT * FROM dba_tablespace_groups;

그룹에서 탈퇴

SYS> ALTER TABLESPACE temp_scott2 TABLESPACE GROUP ‘ ’;

8. Oracle 저장 구조

1) Oracle Block 개요

OS Block → Oracle Block → Extent → Segment → Tablespace → Database

Standard Block Size: DB_BLOCK_SIZE로 지정. 기본 값은 8KB. Database 생성 시 한 번 지정되면, Database를 재생성 하기 전에는 변경할 수 없다.

Non-Standard Block Size: Standard Block Size를 제외한 나머지.

2) Oracle Data Block 상세 구조

Block Header 중 Common(Cache Layer) 부분은 KCBH 영역이며 20Bytes이다. 다음의 정보가 들어있다.

Data Block Address(DBA)
Block Type(Table, Index, Undo 등)
Block Format(v6, v7, v8 등)
System Change Number(SCN, 복구 작업에 사용)

Block Header 중 Transaction Layer의 Fixed 영역은 KTBBH 영역이며 48Bytes이다. 다음의 정보가 들어있다.

Block Type
마지막으로 수행된 Block Cleanout 시간
ITL Entries 정보
Free List Link 정보
Free Space Lock 정보

Block Header 중 Transaction Layer의 Variable 영역은 KTBIT 영역이며 24Bytes이다. 다음의 정보가 들어있다.

해당 Block에 있는 Row를 변경하기를 원하는 실제 ITL 관련 정보
- ITL: Oracle에서 특정 Block에 데이터를 변경해야 할 경우 해당 Block의 사용자 명단에 자신의 정보(TxID 등)을 적고 변경해야 한다. 이 때 사용하는 대기자 명단을 ITL이라 한다.

Data Layer에는 Table Index 항목의 내용과 Row Index 항목의 내용이 들어있다. 실제 데이터가 저장되는 곳이라서 Free Space 관리가 중요하다. Row Index에서 첫 번째 빈 공간의 포인터 주소와 빈 공간의 시작과 끝나는 Offset 주소, 사용 가능 한 빈 공간의 정보를 가지고 있다.

3) PCTFREE와 PCTUSED

PCTFREE: 해당 Block에 입력되어 있는 데이터들이 Update될 경우를 대비하여 예약해 두는 공간.

PCTUSED: Dirty Block에서 지워진 후 남은 용량이 얼마가 되어야 Free Block이 되는가를 결정하는 파라미터.

4) Row Data와 Row Chaining & Row Migration

Row는 크게 Row Header와 Column Data로 나뉜다. Row Header에는 해당 Row에 동시에 Transaction을 일으키는 것을 막기 위한 Lock 정보 부분과 해당 Row Piece에 들어있는 Column Data의 개수 등이 기록된다. Column Data 부분은 실제 데이터가 저장되는 곳이다.

Row Chaining: Block의 크기보다 큰 데이터가 들어와서, 기존 Block와 신규 Block에 걸쳐서 데이터가 저장되는 현상.
Row Migration: 특정 Block에 위치하던 Row가 Update 등의 이유로 해당 Block에 공간이 부족해서 다른 Block으로 이동하는 것. 원래 Block에는 신규 Block의 포인터 주소를 남겨둔다.

5) Extent와 Segment

A. Extent

연속적으로 있는 Block을 모은 것.

Extent 수동 할당

SYS> ALTER TABLE ex_table01
 2   ALLOCATE EXTENT ( SIZE 100k
 3   DATAFILE ‘/app/oracle/oradata/testdb/ex_test01.dbf’ );

사용 안 한 Extent 반환

SYS> ALTER TABLE ex_table01 DEALLOCATE UNUSED;

데이터가 지워졌더라도 이미 사용 된 Extent는 반환을 할 수 없다. 이럴 때에는 Table을 Reorg 하면 된다.

SYS> ALTER TABLE ex_table01 MOVE TABLESPACE USERS;

B. Segment

여러 개의 Extent가 모여 Segment가 된다.

Oracle Object 중 독자적인 저장 공간을 가지는 것들을 말한다.

Oracle Object: Oracle에서 관리자나 사용자가 데이터를 관리하기 위해 만드는 모든 것

여러 개의 Extent 중 가장 첫 번째 Extent의 첫 번째 Block에 Segment의 전체 요약정보인 Segment Header 정보가 들어간다. Segment Header에는 해당 Segment의 종류에 대한 정보와, Extent의 할당 상태, 공간 사용 내역 등의 정보들이 저장된다.

6) Free List Management(FLM) 기법을 사용한 Extent 관리

Free Block은 FET$(Free Extent Table, DBA_FREE_SPACE에서 조회), Dirty Block은 UET$(Used Extent Table, DBA_EXTENTS에서 조회)에서 관리.

만약 사용자가 Drop Tablespace나 Alter Tablespace…Coalesce 등의 작업을 해서 Dirty Extent에서 Free Extent가 된다면 FET$와 UET$를 업데이트 해야 한다.

위와 같이 Block들의 정보들을 딕셔너리를 사용해서 일괄적으로 관리하는 방식의 Tablespace를 DMT(Dictionary Management Tablespace)라고 한다.

DMT는 FET$와 UET$를 동시에 여러 명이 사용 할 수 없다(딕셔너리 ST락). 이를 해결한 것이 LMT(Locally Managed Tablespace) 방식이다. 9i부터 Tablespace 생성 시 자동적으로 LMT로 생성된다.

LMT인 경우, 9i에는 FLM(Free List Management) 방식으로 관리되고, 10g부터는 ASSM(Automatic Segment Space Management) 방식으로 생성되고 관리된다.

FLM 방식의 경우 Free List를 사용한다.

Master Free List(Segment Free List): 해당 Segment에 새롭게 할당된 Free Block이나 트랜잭션이 종료되어 Dirty에서 Free로 반환된 Block들의 목록이 저장되어 있다.
Process Free List: 해당 Segment에 트랜잭션이 끝나서 Free Block이 된 명단을 가지고 있다. Master Free List의 경합을 막기 위해 사용.
Transaction Free List: 현재 세션이 트랜잭션을 수행하는 도중 Free Block으로 변한 블록들의 목록을 관리.

Process Free List의 Free Extent가 전부 소진되어 Master Free List에 요청을 했는데 Master Free List에도 Free Extent가 없을 경우, Oracle은 HWM(High Water Mark)를 오른쪽으로 이동(HWM Bump UP)시킨 후 Free Extent를 새로 생성해서 할당한다.

7) Automatic Segment Space Management(ASSM) 기법을 사용한 Extent 관리

FLM이 수동으로 Extent를 관리했다면, ASSM은 Oracle이 자동으로 Extent를 관리한다.

Free List가 없고 Bit Map Block(BMB)이 Free Block의 상태들을 관리한다. B-Tree 인덱스와 비슷하다. 모든 Data Block에 해당 Block의 여유 상태를 Bitmap으로 나타낸다.

Segment에 새로운 데이터를 입력하기 위해 Free Block을 찾아야 할 경우, 가장 먼저 L3 BMB를 읽어서 Free Block의 정보를 가진 L2 BMB를 찾고, L2 BMB에서 해당 Block을 관리하는 L1 BMB를 찾는다. L1 BMB까지 찾은 후 해당 블록에 새로운 내용을 추가하고 만약 블록의 용량이 변경되면 블록의 헤더 부분의 Bitmap 정보를 변경한다.

FLM에서는 HWM 기준으로 왼쪽은 사용 중, 오른쪽은 Free Block이었다면, ASSM은 Block을 순서대로 쓰지 않기 때문에 중간에 빈 Block이 생긴다. 이때 High HWM과 Low HWM을 사용한다.

High HWM 이상의 블록들은 모두 비 포맷 상태 블록으로, 아직 사용되지 않은 블록들이다.
Low HWM 이하의 블록들은 모두 포맷 상태 블록으로 현재 사용중인 블록들이다.

8) 메모리 관리 기법

A. 9i 버전에서의 메모리 관리

SGA의 전체 크기는 SGA_MAX_SIZE로 지정하며, 이 값을 초과할 수 없다.

PGA의 전체 크기는 PGA_AGGREGATE_TARGET 파라미터로 지정할 수 있다.

WORKAREA_SIZE_POLICY 파라미터를 AUTO로 설정할 경우 PGA 전체 크기 범위 안에서 PGA 값을 오라클이 자동으로 관리한다.

B. 10g 버전에서의 메모리 관리

Automatic Shared Memory Management(ASMM) 등장.

Redo Log Buffer를 제외한, Shared Pool, Default Database Buffer Cache, Large Pool, Java Pool, Streams Pool을 자동으로 변경한다.

ASMM 기능을 사용하려면 STATISTICS_LEVEL 파라미터를 TYPICAL 또는 ALL로 설정하고, SGA_TARGET 파라미터의 값을 0보다 큰 값으로 설정한다. 다른 파라미터(DB_CACHE_SIZE 등)는 0으로 설정.

C. 11g 버전에서의 메모리 관리

SGA뿐만 아니라 PGA까지 한번에 자동으로 관리하는 Automatic Memory Management(AMM) 등장.

MEMORY_TARGET 파라미터에 값을 설정하면 해당 범위 안에서 자동으로 관리한다.

AMM 기능을 사용하면 SGA_TARGET이나 PGA_AGGREGATE_TARGET 파라미터를 설정할 필요가 없다.

저작자표시 비영리 변경금지

'IT' 카테고리의 다른 글

[정리] JAVA 기본 개념 (0)	2020.10.04
[정리] IT 인프라 구조 (0)	2020.10.04
[정리] 데이터베이스 개론과 실습 (0)	2020.10.04
[워드프레스] crayon syntax highlighter에 bitstream vera sans mono 폰트 (0)	2020.10.04
OPatch Users's Guide (0)	2020.10.04