01 Oracle Architecture
1. DBMS 와 Oracle 이야기
메모리에서 작업하고 디스크에 저장한다.
2. Oracle Server 의 전체 구조 살펴보기
(1) Oracle Server 전체 구조
인스턴스 (Instance) - 메모리 부분에 생성되는구조
1) SGA (System Global Area) - Shared Pool (Library Cache, Data Dictionary Cache), Data Buffer Cache, Redo Log Buffer,
Large Pool, Java Pool, Streams Pool, Fixed SGA
2) Backgroud Process - DBWR, LGWR, PMON, SMON, CKPT, ETC
데이터베이스 - Data files, Control files, Redo log files
(2) Oracle Instance 의 할당 및 관리
SGA 의 생성은 최초의 Oracle Server Process 가 요청하고 만들게 되지만
일단 만들어진 후에는 OS Kernel 이 관리를 하게 되고
최초로 Kernel 에게 SGA 생성을 요청한 Oracle Server Process 가 종료되어도
SGA 는 종료되지 않고 그 Instance 가 종료되어야 SGA 가 공유메모리에서 사라지게 됩니다.
세마포어 (Semaphore) - 하나의 메모리 블록을 여러 프로그램이 동시에 사용하는 사태를 막기 위해 OS 에서 제공
SEMMSL - 하나의 세마포어 세트당 세마포어의 최대 개수
SEMMNI - 리눅스 전체에서 설정 가능한 세마포어 세트의 최대 개수
SEMMNS - 리눅스 전체에서 사용 가능한 세마포어 최대 개수
SEMOPM - 1개의 시스템 호출이 초당 호출 가능한 최대 세마포어 개수
SHMMAX - 공유 메모리 세그먼트의 최대 크기(바이트 단위)
SHMMNI - 공유 메모리 세그먼트의 최대 개수
SHMALL - 공유 메모리 최대 크기
SHMMIN - 단일 공유메모리 세그먼트의 최소 크기
SHMSEG - 1개의 프로세스에 부여될 수 있는 공유 메모리 세그먼트의 최대 개수
(3) SGA 의 주요 구성 요소
1) Database Buffer Cache
데이터의 조회와 변경 등 실제 작업이 일어나는 곳으로 사용자가 조회하거나 변경하려는 모든 데이터는 이 곳에 있어야 한다.
Database Buffer Cache Block 의 3가지 상태
Pinned Buffer - 다른 사용자가 현재 사용하고 있는 Buffer
Dirty Buffer - 다른 사용자가 내용을 변경한 후 데이터 파일에 변경된 내용을 저장하지 않은 Buffer
Free Buffer - 사용되지 않았던지 Dirty 였다가 저장완료된 Buffer
LRU 알고리즘 - 최근 가장 적게 사용한 메모리 공간을 가장 먼저 사용하는 메모리 관리 기법 (Shared pool, Database Buffer Cache)
Working Data Set
LRU List
- 메인 리스트: 사용된 버퍼들의 리스트, Hot, Cold 영역으로 나뉨
- 보조 리스트: 미사용된 버퍼들이나, DBWR 에 의해 기록된 버퍼들의 리스트 (Free list)
LRUW List
- 메인 리스트: 변경된 버퍼들의 리스트 (Dirty list)
- 보조 리스트: 현재 DBWR 에 의해 기록중인 버퍼들의 리스트
이렇게 유한한 자원을 여러 Process 가 한꺼번에 사용하려고 할 경우에 순서를 관리하기 위해 Latch 를 사용한다.
2) Redo Log Buffer
장애가 발생했을 때 복구하기 위하여 데이터에 변경사항이 생길경우 해당 변경 내용을 기록해 두는 메모리
Redo Log File - 내용을 디스크에 저장해 주는 파일
3) Shared Pool
Library Cache - Soft Parse 할 때 사용되는 공간으로 이미 수행되었던 SQL 문장이나 PL/SQL 문장의 Parse Code 와
해당 SQL, PL/SQL 문장, 실행계획 등이 저장되어 있고 LRU 알고리즘으로 관리됨
Dictionary Cache (Row Cache) - 구문분석이나 옵티마이저가 실행계획을 세울 때 사용되는 주요 딕셔너리들이 Row 단위로 Cache 되어있다.
이곳 역시 LRU 알고리즘으로 관리됨
Server Result Cache - 결과값을 Cache 해두는 공간 (result_cache_mode 파라미터 force)
SQL Query Result Cache
PL/SQL Function Result Cache
Reserved Pool - Shared Pool 에 5KB (11g 기준) 가 넘는 오브젝트가 적재되어야 할 경우에 사용하기 위해서 예약해둔 공간
SHARED_POOL_RESERVED_SIZE 파라미터로 용량 설정
4) Large Pool
- Shared Server mode 로 사용할 경우 UGA 를 이곳에 생성
- 병렬처리 시 message buffer 생성
- RMAN이 사용하는 I/O 용 Buffer 생성
5) Java Pool
Java 관련 코드나 JVM 관련 데이터
6) Streams Pool
Streams 기능을 사용할 경우 사용
7) Fixed SGA
백그라운드 Process 들이 필요한 Database 의 전반적인 공유정보나 각 Process 들끼리 공유해야만 하는 Lock 정보 같은 내용들이 저장
(4) Dynamic SGA
관리자의 필요에 의해 SGA 의 구성요소 크기를 변경 후 Oracle Instance 의 재시작 없이 즉시 적용할 수 있는 기능 (Redo log buffer 를 포함한 몇가지는 제외)
SYS> alter system set DB_CACHE_SIZE=100M ;
그래뉼 (Granule) - 오라클에서 메모리를 할당하는 단위, SGA_MAX_SIZE=128M 이하 1Granule=4M, 128MB 초과 16M, 10g부터는 SGA_MAX_SIZE=1G
10g 부터 ASMM (Automatic Shared Memory Management) - 오라클이 자동으로 SGA 의 값을 결정하게 해서 성능을 향상시키는 기법
(5) Program Global Area (PGA) 의 주요 구성요소
SGA - 모든 프로세스들이 공유해서 사용되는 메모리 공간
PGA - 각 프로세스들이 개별적으로 사용하는 메모리 공간
모든 프로세스들은 각각의 PGA 를 가지고 있다.
1) Private SQL Area
Bind 변수 값을 보관, Persistent Area 와 Runtime Area 로 구성
2) SQL Work Area
Sort 나 Hash 작업을 수행하게 되는 공간
PGA_AGREGATE_TARGET 값 설정 후 WORKAREA_SIZE_POLICY 파라미터를 AUTO 로 설정하면 오라클이 자동 관리
- 적절한 PGA 용량
OLTP (Online transaction processing) 환경 PGA_AGGREGATE_TARGET = (총물리메모리용량 * 80%) * 20%
DSS (Decision-support System) 시스템 PGA_AGGREGATE_TARGET = (총물리메모리용량 * 80%) * 50%
02 SQL 문장의 실행원리
(1) SELECT 문장의 실행원리
1) Parse (SQL Parser가 Parse Tree 생성)
Parse Tree 를 만드는 과정
문법검사 (Syntax check) - 오라클 키워드 스펠링이 틀리면 Parse Tree 단계에서 오류
의미검사 (Semantic Check) - 스펠링은 맞지만 없는 테이블을 조회했다면 Semantic Check 단계에서 오류 발생
두 단계를 위해 Shared Pool 의 Dictionary Cache에 자주사용하는 데이터 딕셔너리를 캐싱해 두고 사용한다.
해당 SQL 문장이 오류가 없다면
권한검사 후
ASCII 값으로 (숫자) 변경한 후 해당 숫자 값을 Hash 함수를 통해 특정 Hash 값으로 변경
여기서 얻은 Hash Value 를
Shared Pool 의 Library Cache 에 있는 Hash Value 들과 비교해서 동일한 값이 있는 지 확인 - 커서 공유 (Soft Parse)
실행계획이 있는지 검사하기 위해 Hash Bucket (해쉬 버킷) 을 반드시 읽어야 함
실제 내용이 들어가 있는 공간 Cursor (커서) 도 읽어야 함 (공유 커서를 말함, 세션 커서, 애플리케이션 커서 아님)
동일한 문장을 수행했더라도 (대소문자, 공백, 주석까지) 사용자가 다르면 이 문장은 서로 다른 문장이 됨, 옵티마이저 모드가 달라도
공유 커서는 크게 두가지로 나뉨
부모 커서 (Parent Cursor): SQL 문장 자체에 대한 값이 들어 있음
자식 커서 (Child Cursor): 사용자 정보나 Optimizer mode 같은 정보가 들어있음
즉, 커서 공유란 부모커서, 자식커서 모두가 완전히 동일해야만 이루어짐
Hash List : 어떤 커서에 어떤 데이터가 들어있다라는 것을 리스트로 관리, Chain 구조로 연결되어 있음
메모리는 Heap 구조라 연속적으로 저장되어 있지 않음
Library Cache 안에서 원하는 SQL 문과 실행계획이 들어 있는 커서를 찾기 위해서 반드시 Hash List 를 찾아야 함
Library Cache Latch: Library Cache 를 탐색하기 위해 반드시 가져야 함
Library Cache 의 Hash List 를 Select 할 경우에는 서로 공유해서 한꺼번에 여러명이 볼 수 있음
사용자가 Select 문을 수행했다는 의미가 아님
Hash List 를 찾아보고 원하는 SQL 과 실행계획이 없을 경우 서버 프로세스는 Hard Parse 과정을 수행한 후
실행 계획을 받아서 Library Cache 에 신규 등록한 후 다음 단계로 넘어감,
사용자는 Select 를 했지만 해당 SQL 이 처음 실행될 경우 Hash List 를 Update 해야함
(Library Cache Hash List 를 update 하거나 신규로 새로운 실행계획을 등록하려면
Library Cache Latch 를 획득한 상태에서 Shared Pool Latch 가 추가로 필요함)
반면에 사용자가 Update 를 했을 때 Hash List 를 먼저 Select 해보고 해당 SQL 문장과 실행계획이 있으면
Soft Parse 를 성공하게 됨. 이럴 경우는 Hash List 를 Select 만 해도 다음 작업이 가능
Soft Parse 가 성능이 좋다고 해도 Latch 가 부족해 동시에 작업할 수 없다면 속도가 느려지게 됨
오라클에서 이 문제를 해결하기 위해 Session_Cached_Cursors 파라미터 제공
옵티마이져 (Optimizer) - 새로운 실행계획을 생성시켜 주는 내비게이션 역할을 하는 것
RBO (Rule Based Optimizer) - 15가지 규칙안에서 실행계획을 세워야 함, 인덱스가 있으면 무조건 사용함.
CBO (Cost Based Optimizer) - 규칙보다는 규칙을 참조한 현실적인 상황이 실행계획을 세우는 주요한 요소가 됨
(DBA_TABLES, USER_TABLES 등과 같은 Data Dictionary 참고)
옵티마이져의 실행계획은 SQL 문장의 수행 속도에 절대적인 영향을 줌
Hard Parse - 옵티마이져가 새로운 실행계획을 세우는 단계
옵티마이져는 Data Dictionary 를 참고해서 실행계획을 생성하는데
참조하는 딕셔너리 중 대부분이 Static Data Dictionary 라는 딕셔너리 인데
자동으로 업데이트 되는 것이 아님
(옵티마이져가 참조하는 딕셔너리 관리는 사람이 해주어야 함)
Library Cache 와 Dictionary Cache 크기는 각각 따로 설정할 수 없으며 Parameter file 의 Shared_pool_size 라는 파라미터로 설정
10g 부터는 ASSM 기능 등장, SGA_TARGET 이라는 파라미터를 설정하면 되는데 설정 후 shared_pool_size 값을 주지 않으면 0 으로 셋팅
최저 0 byte 부터 시작해서 오라클이 자동으로 관리하겠다 는 뜻, 아래는 shared pool 용량 확인하는 쿼리
SQL> SELECT SUM(bytes/1024/1024) MB FROM v$sgastat WHERE pool='shared pool' ;
2) Bind - 파싱을 1000번 하고 실행계획을 1000번 만드는 것보다 1번만 파싱해서 실행계획을 1개만 생성한 후 학번과 이름만 바꾸어서 1000번 실행하는 것이 빠름
이 과정을 바인드라고 하며 이 작업에서 사용된 학번과 이름을 바인드 변수값이라고 부름
바인드 변수를 사용하면 Soft Parse 를 많이하게 되므로 좋아지는 부분도 있지만
테이블의 데이터들이 한쪽으로 편중되어 (Skewed) 있게 되면 index 가 정상적으로 작동을 못함
분포도가 균일하지 못하면 바인드 변수를 사용할 수 없음
Skewed 는 테이블에 있는 데이터들이 균일한 비율이 아니라 특정데이터가 집중적으로 많이 들어가 있다는 의미
이럴 때는 대안으로 특수한 통계정보인 Histogram 을 생성해 주어야 함
그러나 Histogram 을 생성하면 Bind 기능을 사용하지 못함
Bind 기능 덕분에 Hard Parse 의 부하가 많이 줄게 되었고 그로 인해 성능이 많이 향상될 수 있기에 꼭 사용하는 것이 좋음
3) Execute - 하드디스크의 데이터 파일에서 데이터가 들어있는 블록을 찾아 메모리 (Database Buffer Cache) 로 복사해 오는 과정
Database Buffer Cache 는 데이터 파일의 블록 주소를 Hash 값을 변경시켜서 리스트를 만든 후 그 리스트와 데이터 파일 블록을 관리하고 있음
서버 프로세스가 찾는 블록 주소를 Hash 함수에 넣어 Hash Value 를 만들고
Database Buffer Cache Hash List 를 비교해서 동일한 Hash Value 가 있는지 검사하여
원하는 블록이 이으면 즉시 Fetch 로 넘어가고 없다면 디스크로 가서 해당 블록을 찾아 Database Buffer Cache 로 복사해옴
Block 단위로 I/O 하는데 초기화 파라미터 파일에 DB_BLOCK_SIZE 로 크기가 결정되며
DB가 생성될 때 적용되어 다시 DB를 생성하기 전에는 변경할 수 없음 9i 4k, 10g 8k
4) Fetch - 사용자가 요청한 원하는 데이터만 골라내는 과정, 사용자가 추가작업 (sort 등) 요구했다면 Fetch 과정에서 완료해서 내보냄
정렬이 발생하는 장소는 PGA (Program Global Area)
(2) Update 문 실행원리
SQL 을 받은 서버 프로세스는 Parse 단계에서 문법검사, 의미검사, 권한검사를 한 후
Soft Parse: Shared Pool 의 Library Cache 를 살펴서 이 SQL과 실행 계획이 있는지 보게 됨
Hard Parse: Soft Parse 실패시 옵티마이져가 실행계획을 생성
실행계획 생성 후 Library Cache 에 등록한 후 Excute 단계 수행
Excute 단계에서 데이터가 있는 블록을 Database Buffer Cache 로 가져온 후
변경되는 데이터의 변경내역을 Redo log buffer 에 먼저 기록
그 후 Undo Segment 에 이전 (원본) 이미지를 기록
그 후 Database Buffer Cache 의 내용을 변경
03 Oracle Background Process
1. 필수 Background Process
(1) DBWR
Database Buffer Cache 에서 변경 완료 후 저장되어야 하는 블록 (Dirty Block) 을 데이터 파일로 저장하는 역할
1) Checkpoint 신호 발생
2) Dirty Buffer 임계값 지났을 때
3) Time out 이 발생했을 때
4) Rac Ping 이 발생했을 때
5) Tablespace 가 Read only 상태로 변경될 때
6) Tablespace 가 offline 될 때
7) Tablespace 가 begin backup 상태가 될 때
8) Drop table 이나 Truncate table 될 때
9) Direct Path Read/Write 가 진행될 때
10) 일부 Parallel Query 작업이 진행될 때
(2) LGWR
Redo log buffer 에 있는 내용을 Redo log File 로 저장
1) Commit 발생
2) 1/3 이 찼을 때
3) 변경량이 1M 가 되었을 때
4) 3초 마다
5) DBWR 이 내려쓰기 전에
(3) PMON
모든 서버 프로세스들을 감시하고 비정상적으로 종료된 프로세스가 있다면 관련 복구작업 등을 하는 역할
(4) SMON
SMON의 주요업무
1) 인스턴스가 비정상 종료되었을 경우 (Instance Crash) 인스턴스를 시작할 때 Clean Up 하는 역할 담당 (Instance Recovery)
2) 어떤 사정이 (File 에러나 Tablespace 가 Offline 상태여서) 있어서 Instance Recovery 과정에서 누락된 Transaction 을 Recovery 하는 역할 담당
3) 비정상 종료된 Transaction 이 사용 중이던 Temporary Segment 를 Clean Up 하는 역할 담당
4) Dictionary Managed Tablespace 에서 free extents 들을 모아주는 역할
Instance Recovery 과정
1) Parameter file 읽어서 Nomount 단계에서 instance를 생성
2) Mount 단계에서 Control file 의 내용을 확인해서 Instance Crash 상황임을 확인
3) Redo log file 에서 위 1,2,3,4 단계의 작업을 다시 수행 (Roll Forward) commit 안된 작업도 수행
4) Database open
5) commit 안된 4번 작업 취소 (Roll Backward)
(5) CKPT
DBWR 에게 Checkpoint 신호를 전달해주며 Control File 과 Data file Header 에 해당 Checkpoint 정보를 기록하는 역할을 담당
(6) MMON 과 MMNL (Manageablity Monitor Processes - 10g 이후 버전부터 추가)
(7) RECO (Recover Process)
2. 선택적인 Background Process
(1) ARCn (Archiver Process)
(2) CJQ0 & Jnnn (Job Queue Processes)
(3) FBDA (FlashBack Data Archiver Processes)
04 Oracle 시작하기와 종료하기
오라클 서버의 시작 순서와 파일들
1 2 3
STARTUP → NOMOUNT → MOUNT → OPEN
Parameter Control Redo log file
file file Data file
Alert Log 파일은
10g 일 경우 $ORACLE_BASE/admin/SID/bdump/alert_SID.log 파일로 존재하고
11g 일 경우 $ORACLE_BASE/diag/rdbms/SID/SID/trace/alert_SID.log 로 존재한다.
tail -f 로 확인.
오라클 서버 프로세스 NOMOUNT 단계에서 Parameter File 을 읽고 그 값들을 참고하여 인스턴스를 생성한다.
인스턴스는 SGA 와 Background Process 들로 구성되어 있으므로 NOMOUNT 단계에서 RAM 에 인스턴스가 생성되어 작업할 수 있는
메모리 공간이 확보된다. 그리고 Alert Log 파일을 열어 로깅을 시작한다.
NOMOUNT 단계를 마치면 Control File 의 내용을 읽고 MOUNT 단계로 진행한다.
Control File 의 위치정보는 Parameter File 에 기록되어 있다.
MOUNT 단계에서 Database 의 이상 유무를 확인한 후 OPEN 단계로 가게된다.
이 때 Instance Crash 로 판단되면 SMON 이 Instance Recover 를 수행한다.
복구 내용은 Redo Log File 에서 찾게 된다.
Redo Log File 에 없고 Archive Log File 에 있을 경우에는 SMON 이 Instance Recovery 를 수행하지 못하고
관리자가 직접 Recovery 를 수행해야하는 Media Recovery 상태가 된다.
1. Parameter File (초기화 파라미터 파일)
(1) Parameter(파라미터) 란?
원하는 값을 오라클에게 전해주기 위해 사용하는 변수로 정적 파라미터 파일인 Pfile 과, 동적 파라미터 파일인 Spfile 이 있다.
항목 /파일 Pfile Spfile
파일 기본경로 $ORACLE_HOME/dbs (두 파일 공통)
파일 이름 initSID.ora SpfileSID.ora
내용 변경 관리자 (사람) 서버 프로세스
파일 형태 Text (OS 편집기로 편집가능) Binary (OS 편집기로 편집 안됨)
(2) 파라미터 파일의 내용 확인하기
spfile 같은 바이너리 파일은 사용자가 수정하면 안된다.
pfile 과 spfile 이 둘 다 존재할 경우에는 spfile 만 사용한다.
(3) 파라미터 파일의 내용 변경하기
9i 부터는 pfile 이라도 Dynamic SGA 기능이 도입되어 alter system set 명령을 이용하여 재부팅 없이 즉시 적용됨
SPFILE 옵션
Memory 옵션은 Spfile 의 내용은 변경하지 말고 현재 작동 중인 인스턴스에만 적용하라는 의미로 재부팅하면 다시 Spfile의 값으로 돌아간다.
Spfile 옵션은 현재 운영중인 인스턴스에는 적용하지 말고 Spfile 의 내용만 변경하라는 뜻으로 재부팅 후부터 적용한다.
Both 옵션은 의미 그대로 두 가지 모두에 적용한다는 뜻으로 현재 운영 중인 인스턴스에도 즉시 적용하고 Spfile 에도 적용하여 재부팅 후에도 유지되게 하라는 뜻이다. Scope 옵션을 사용하지 않을 경우는 Both 가 기본모드이다.
(4) 주요 파라미터들의 의미
CURSOR_SHARING -
DB_BLOCK_SIZE - Database 에서 Standard Block Size 를 지정하는 파라미터
DB_CACHE_SIZE - Default Database Buffer Cache 의 크기를 결정하는 파라미터
DB_FILE_MULTIBLOCK_READ_COUNT - Full Scan 이나 Index Full Scan 을 할 경우 하드디스크의 데이터 파일에서 Database Buffer Cache 로
한꺼번에 읽어 올릴 수 있는 블록의 개수를 지정하는 파라미터
PGA_AGGREGATE_TARGET - 하나의 인스턴스에 접속한 전체 서버프로세스가 사용 가능한 총 PGA 크기를 결정하는 파라미터
0보다 크게 하면 WORKAREA_SIZE_POLICY 값이 자동으로 AUTO 로 설정이 되어 PGA 의 크기를 오라클에서 자동으로 설정
SGA_TARGET - 10g 부터 등장한 파라미터로 ASMM 을 사용할 경우 SGA 의 전체 사이즈를 지정하는 파라미터
이 파라미터를 사용할 경우 아래 값들이 자동 튜닝 대상이 됨
- Buffer Cache (DB_CACHE_SIZE) / Shared Pool (SHARED_POOL_SIZE)
- Large Pool (LARGE_POOL_SIZE) / Java Pool (JAVA_POOL_SIZE)
- Steams Pool (STREAMS_POOL_SIZE)
(5) 10g 설치 후 변경해야 하는 파라미터들
_DG_GROKER_SERVICE_NAMES=''
_B_TREE_BITMAP_PLANS=FALSE
_BLOOM_FILTER_ENABLED=FALSE
_CLEANUP_ROLLBACK_ENTRIES=2000
CLOSE_CACHED_OPEN_CURSORS=TRUE
_COMPLEX_VIEW_MERGING=FALSE
_CURSOR_FEATURES_ENABLED=10
_FAST_START_INSTANCE_RECOVERY_TARGET=360
_GBY_HASH_AGGREGATION_ENABLED=FALSE
_GC_AFFINITY_TIME=0
_GC_UNDO_AFFINITY=FALSE
_IN_MEMORY_UNDO=FALSE
_KKS_USE_MUTEX_PIN=FALSE
_OPTIM_PEEK_USER_BINDS=FALSE
_OPTIMIZER_COST_BASED_TRANSFORMATION=OFF
_OPTIMIZER_PUSH_PRED_COST_BASED=FALSE
_PX_USE_LARGE_POOL=TRUE
_ROW_CACHE_CURSORS=1000
(6) 11g 설치 후 변경해야 하는 파라미터들
10g 설치 후 변경해야 하는 파라미터를 모두 변경 후 아래의 파라미터 추가 변경
OPEN_LINKS - 초기값 4 인데 충분히 40 정도
OPEN_LINKS_PER_INSTANCE - 초기값 4 인데 충분히 40 정도
MEMORY_TARGET - 초기값 16G 인데 0이나 앞에 # 으로 주석
DB_WRITER_PROCESSES - 기본값 1 인데 2 로
SESSION_CACHED_CURSORS - 기본값 50 인데 충분히 500 정도
_DIAG_DAEMON - 기본값 True 인데 False 로
2. 다양한 방법으로 Instance Open 하기
사용 중인 파일은 절대로 이동 및 복사하면 안된다.
parameter file 중 pfile 은 예외다.
Restricted Mode 제한모드
허가받은 사용자만 접속할 수 있도록 하는 모드
SYS> STARTUP RESTRICT ;
3. Oracle Instance 종료하기
(1) Shutdown 의 4가지 옵션
1) NORMAL (기본옵션)
접속되어 있는 사용자들이 스스로 접속을 종료할 때까지 기다렸다가 종료하는 옵션
2) TRANSACTIONAL
사용자가 수행중인 트랜잭션이 끝나는 시점에 종료.
DML 작업을 수행 중에는 기다리고 있다가 해당 트랜잭션을 종료하는 명령어(DDL, DCL, TCL)을 수행하면
접속을 강제로 종료시킨 후 데이터를 저장하고 Instance 를 종료한다.
3) IMMEDIATE
사용자의 행동에 상관없이 즉시 접속을 강제 종료시킨다.
COMMIT 완료된 데이터는 저장, COMMIT 이 완료되지 않은 작업은 모두 ROLLBACK 시킨 후 정상적으로 Instance를 종료한다.
4) ABORT (IMMEDIATE 가 안될때만 쓴다.)
즉시 접속 강제 종료. 서버가 정전된 것과 같은 상황으로 비정상 종료이고 다른말로 Instance Crash 라고 부른다.
이렇게 꺼진 Instance는 다시 Startup 될 때 SMON이 Instance Recovery 를 수행해서 복구해야 한다.
운영중인 서버에 치명적인 위험을 줄 수 있는 명령어이므로 아주 주의해야 하는 옵션이다.
파라미터 파일 삭제되었을 때는 원본파일을 가져온다.
$ cp /app/oracle/admin/testdb/pfile/init.ora.104201314135 inittestdb.ora
현재 spfile을 사용했는지 pfile 을 사용했는지 알고 싶으면
SQL> show parameter spfile ;
SQL> show parameter pfile ;
value 부분의 값이 없으면 Pfile이고 값이 있으면 Spfile
pfile 로부터 spfile을 생성
SQL> create spfile from pfile ;
SQL> !rm -fr /app/oracle/product/11g/dbs/inittestdb.ora
spfile 로부터 pfile 을 생성
SQL> create pfile from spfile ;
pfile 과 spfile 이 모두 있을 경우 spfile 만 사용되므로 pfile을 사용하려면 spfile 을 삭제하거나 이름을 변경해야 한다.
SQL> !rm -f /app/oracle/product/11g/dbs/spfiletestdb.ora
05 Control File 관리하기
1. 각 버전별 Control File 의 내용
2. Control File 관리하기
실습1 Spfile 일 경우 다중화 하는 방법 (11g 기준)
현재 상태 확인
SYS> select status from v$instance ;
SYS> show parameter spfile ;
현재 Control File 조회하기
SYS> select name from v$controlfile ;
Spfile 의 내용을 변경한 후 Instance 를 종료한다.
SYS> alter system set control_files='/home/oracle/disk1/control01.ctl',
'/home/oracle/disk2/control02.ctl' scope=spfile ;
SYS> shutdown immediate ;
대상 디렉토리를 생성하고 파일을 복사한다.
SYS> !
$ cd /home/oracle
$ mkdir disk1 disk2
$ cp /app/oracle/oradata/testdb/control01.ctl /home/oracle/disk1/control01.ctl
$ cp /app/oracle/fast_recovery_area/testdb/control02.ctl /home/oracle/disk2/control02.ctl
$ exit
정상적으로 startup 시켜서 확인한다.
SYS> startup
SYS> select name from v$controlfile ;
실습2 Pfile 일 경우 다중화 하는 방법 (11g 기준)
SYS> show parameter pfile ;
현재 사용중인 Control File 조회하기
SYS> select name from v$controlfile ;
Instance 를 종료한다.
SYS> shutdown immediate ;
pfile 에서 Control File 의 경로를 수정한 후 저장한다.
SYS> !vi $ORACLE_HOME/dbs/inittestdb.ora
*.control_files='/home/oracle/disk3/control01.ctl',
'/home/oracle/disk4/control02.ctl'
해당 디렉토리를 생성 후 Control File 을 복사한다.
SYS> !
$ cd /home/oracle/
$ mkdir disk3 disk4
$ cp /home/oracle/disk1/control01.ctl /home/oracle/disk3/control01.ctl
$ cp /home/oracle/disk2/control02.ctl /home/oracle/disk4/control02.ctl
$ exit
정상 Open 후 확인한다.
SYS> startup
SYS> select name from v$controlfile ;
06 Redo Log 관리하기
1. Redo Log 의 생성원리
오라클에서는 장애가 발생했을 경우 복구를 하기 위해서 데이터에 변경이 생길 경우 변경되기 전의 내용과 변경된 후의 내용을 모두 기록해둔다.
기록되는 메모리는 Redo Log Buffer, 파일이 Redo Log File 이다. 이러한 기능이 Redo Log 이다.
* Write Log Ahead
실제 데이터를 변경하기 전에 Redo Log 에 먼저 기록한 후 데이터를 변경한다.
DBWR 이 작동하기 전에 LGWR 이 작동하는 것도 이 의미다.
오라클에서는 기본적으로 데이터를 먼저 기록하지 않고 Redo Log 부터 기록한다.
* Log force at Commit
사용자로부터 Commit 요청이 들어오면 관련된 모든 Redo Record 들을 Redo Log File 에 저장한 후 Commit 을 완료한다.
그러나 대량의 데이터 변경 후 한꺼번에 Commit 이 수행되면 성능에 악영향을 주는 경우가 발생하여
Delayed Commit (지연된 커밋) 이나 Group Commit (아주 짧은 시간 동안 모아서 한꺼번에 수행) 이란 기술이 등장한다.
LGWR 이 관련된 Redo Log 를 다 기록한 후 DBWR 이 데이터를 기록하는 동기식 커밋에서 성능 문제 때문에 비동기식 커밋이라는 기술도 나오게 된다.
Page Fix 해당 Block 의 해당 row 부분을 다른 사용자가 바꿀 수 없도록 해당 Block 에 Lock 을 설정
Change Vector 란 변경된 데이터를 나중에 복구할 목적으로 Redo Iog 에 기록할 변경된 데이터에 대한 모든 정보 세트를 의미한다.
오라클에서 메모리 자원을 사용할 때는 반드시 그 메모리 자원에 대한 Latch를 확보해야 합니다.
Redo Log Buffer에 내용을 쓰려면 적절한 두가지 Latch를 확보해야 하는데 먼저
Redo Copy Latch 를 획득해야 합니다.
Redo Copy Latch 수는 "_log_simultaneous_copies" 라는 히든 파라미터로 조정가능합니다. (기본값은 CPU * 2)
다음 단계는
Redo Allocation Latch 를 확보해야 합니다.
Shared Redo Strand - 9i 버전부터 Redo Log Buffer 를 여러개의 공간으로 나누어서 각 공간별로 Redo Allocation Latch 를 할당해 주는 기능
중요한 것은 Redo Log Buffer 를 몇개 로 나누는가 인데 10g 부터 LOG_PARALLELISM 파라미터가 _LOG_PARALLELISM 으로 변경되고
이 파라미터의 값을 동적으로 관리하도록 _LOG_PARALLELISM_DYNAMIC 파라미터가 추가되었음 TRUE 일경우 자동 관리
Private Redo Strand - 10g 부터 각 서버 프로세스는 Shared Pool 에 자신만의 독립적인 Private Strand 공간을 만들어서
그 곳에 Change Vector 를 생성한 후 필요할 경우 LGWR 이 Redo log Buffer 에 바로 기록하게 됩니다.
이처럼 Latch 를 확보해서 Redo log Buffer 에 기록해야 하는 과정을 줄임으로 성능을 더 향상시킬 수 있습니다.
이 기능을 다른 말로 zero copy redo 라고도 합니다.
Redo log Buffer 에 기록된 Change Vector 를 Redo Entry 라고 한다.
특정 상황이 되면 LGWR 이 일부를 Redo Log File 에 기록한 후 기록된 Redo Entries 들은 Redo Log Buffer 에서 삭제 (Flush) 합니다.
더 정확하게 말하면 서버 프로세스가 아래의 상황이 되면
Redo Write Latch 를 확보한 후 LGWR 에게 Redo Log Buffer 에 있는 내용을 Redo Log File 에 기록하라고 요청을 하게 됩니다.
1) 3초마다
2) Redo Log Buffer 의 전체 크기의 1/3 이 찼거나 1M 가 넘을 경우
3) 사용자가 Commit 또는 Rollback 을 수행할 때
4) DBWR 이 LGWR 에게 쓰기를 요청할 때
2. Redo Log File 구성 및 관리하기
(1) Redo Log Buffer 와 Redo Log File
Redo Log File 은 그룹과 멤버라는 개념으로 관리되는데 그룹은 최소 3개와 각 그룹별 최소 2개 이상의 멤버로 구성하기를 권장한다.
Log Switch - Redo Log File 에 내려쓰다가 해당 파일이 가득차면 다음 그룹으로 넘어가는 과정, 순서는 라운드로빈
Checkpoint - Log Switch 가 일어나면 발생하는 신호
ORA-00346
ORA-00340
(2) Redo Log File 관리하기
1) 신규 Group 생성하기
SQL> alter database add logfile group 4
2 '/app/oracle/oradata/testdb/redo04_a.log' size 5M ;
2) Member 추가하기
SQL> alter database add logfile member
2 '/app/oracle/oradata/testdb/redo04_b.log' to group 4 ;
3) Member 삭제하기
SQL> alter database drop logfile member
2 '/app/oracle/oradata/testdb/redo04_b.log' ;
4) Group 삭제하기
SQL> alter database drop logfile group 4 ;
5) 강제로 Log Switch 발생시키기
SQL> alter system switch logfile ;
6) 강제로 Checkpoint 발생시키기
SQL> alter system checkpoint ;
Redo Log File 의 상태
CURRENT 란 현재 LGWR 이 내용을 기록하고 있는 상태
ACTIVE 는 Redo Log File 의 내용이 아직 DB Buffer Cache 에서 Data File 로 저장이 안되어서 지워지면 안되는 상태
INACTIVE 란 Redo Log File 의 내용이 데이터 파일에도 다 저장이 되어서 삭제되어도 된다는 의미
삭제하려면 INACTIVE 상태에서 3, 4번 명령어를 수행하면 된다.
※ 주의사항은 절대 OS 명령어로 먼저 삭제하면 안된다.
3. 심화학습 SCN 과 Checkpoint
(1) SCN (System Commit Number)
Commit 이 발생하게 되면 해당 트랜잭션은 고유한 번호를 부여받아서 관리한다.
SCN 번호는 DML 문장단위로 할당되는 것이 아니라 트랜잭션 단위로 할당된다.
SCN 은 아래처럼 여러 곳에 기록된다.
1) Control File Header - Checkpoint 발생 때
- Resetlogs 발생 때
- Incomplete Recovery 수행 때
2) Data Block (cache layer) - Block Cleanout 시 마지막 SCN 을 각 Block 에 기록한다.
3) Data Block (ITL entries) - Data Block 의 Transaction Layer 안에 있는 ITL (Interested Transaction List) Entries 에 Commit 된 SCN 정보를 기록하고 있다.
(Delayed Block Cleanout).
4) Data File Headers - 모든 데이터 파일 헤더에 아래의 경우에 SCN 을 기록한다.
- 마지막 Checkpoint 발생 때
- Begin Backup 수행 때
- 복구가 되었다면 사용된 마지막 SCN 을 기록한다.
5) Redo Records / Log Buffer
- Commit 이 수행되면 Commit Record 에 SCN 을 포함하여 저장하게 된다.
6) 그 외 Rollback Segment (Undo segment) 와 Tablespace Headers 에도 기록된다.
Commit 과 관련된 파라미터
- commit_point_strength : 분산 데이터 베이스 환경에서 2-Pharse commit 에 사용한다.
- commit_write : 사용자가 commit 을 하게 되면 LGWR 이 해당 트랜잭션을 Redo Log File 에 기록하게 된다.
4가지 방식
1. WAIT : 변경된 트랜잭션이 Redo log file 에 기록될 때까지 기다린다.
2. NOWAIT : 변경된 트랜잭션이 Redo log file 에 기록될 때까지 기다리지 않는다.
3. IMMEDIATE :Commit 요청이 들어오면 즉시 Redo Log File 에 기록하기 시작한다.
4. BATCH : Commit 요청이 들어오더라도 일정 시간 동안 모아서 한꺼번에 기록한다.
비동기식 커밋: Redo Log Buffer 의 내용이 아직 Redo Log File 에 기록이 완료되지 않아도 다른 작업을 할 수 있도록 성능을 높이는 방식
동기식 커밋: Server Process 가 Commit 요청을 수행할 때 LGWR 이 Redo Log Buffer 의 내용을 Redo Log File 에 기록을 완료한 후에 후속 작업을 할 수 있는 방식
1, 3 동기식 커밋 - 안정성은 좋지만 성능이 떨어진다.
2, 4 비동기식 커밋 - 그 반대, 트랜잭션이 많은 OLTP 환경이라면 조심해야 한다.
(2) System Change Number
SCN 의 또 다른 이름으로 Data File, Redo Log File, Control File 간의 동기화 정보를 맞추기 위해 사용
Data block Header, Redo records, Segment Header 에 기록된다.
(3) Checkpoint
Commit 된 데이터를 어디까지 저장했는지 확인하기 위해 만들어 놓은 개념
07 Tablespace 와 Data File 관리하기
1. 개요
오라클은 데이터를 저장하고 작업을 할 때
메모리에 논리적으로 Tablespace 라는 공간을 만들어서 작업하고
물리적으로는 디스크에 Data File 을 만들어서 저장합니다.
2. Tablespace 종류 및 특징
(1) SYSTEM tablespace
데이터 딕셔너리는 크게 나누면 Base Table 과 Data dictionary View 로 나눌 수 있습니다.
Base Table 은 데이터베이스를 생성할 때 (dbca, create database 등) 생성됩니다.
원본데이터가 들어있는 곳이라서 DBA조차 접근할 수 없습니다.
그래서 이 곳의 내용을 조회할 수 있도록 추가적으로 Data Dictionary View 를 제공해주는 것입니다.
Data Dictionary View 는 DBCA 를 사용해서 생성할 경우는 자동으로 생성되지만
Create Database 명령어로 수동 생성할 경우 자동으로 생성되지 않습니다.
그래서 반드시 catalog.sql 을 수행해 주어야만 합니다.
딕셔너리에는 아래와 같은 주요 내용의 정보가 들어있습니다.
1. 데이터베이스의 논리적인 구조와 물리적인 구조 정보들
2. 객체의 정의와 공간 사용 정보들
3. 제약 조건에 관련된 정보들
4. 사용자에 관련된 정보들
5. Role, Privilege 등에 관련된 정보들
6. 감사 및 보안 등에 관련된 정보들
딕셔너리 내용을 수동으로 업데이트 하기 위해서는
Analyze 명령어나 DBMS_STATS 패키지를 사용하는데 해당 테이블을 전체 스캔하는 명령어이므로
서버 성능에 아주 나쁜 영향을 주므로 함부로 사용해서는 안됩니다.
Static Dictionary 내용이 실시간으로 변경이 안됨
USER_XXX 해당 사용자가 생성한 내용만 볼 수 있다.
ALL_XXX 해당 사용자가 생성한 것과 접근 가능한 내용을 볼 수 있다.
DBA_XXX 모든 내용을 다 볼 수 있다. DBA 권한을 가진 사용자만 조회 가능
Dynamic Dictionary
V$로 시작하는 딕셔너리는
실시간으로 변경되는 내용을 볼 수 있는 Dynamic Performance View 는
조회하는 시점의 데이터를 Control file 이나 메모리에서 가져와서 보여준다.
(2) SYSAUX tablespace
10g 버전부터 추가됨
성능 튜닝과 관련된 딕셔너리들이 저장되고,
AWR, ADDM, ASH 등 자동 튜닝 기능들이 이곳의 정보를 사용한다.
(3) 일반 Tablespace
가장 일반적으로 많이 쓰이고 관리자가 필요에 의해서 만드는 Tablespace 다.
Tablespace 용량 관리하기
1. 수동으로 Tablespace 에 Datafile 추가하는 방법
SYS>alter tablespace haksa
2 add datafile '/app/oracle/oradata/testdb/haksa02.dbf' size 20M ;
SYS>select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files ;
2. Data file 크기 수동 증가시키기
SYS> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' resize 20M ;
3. Data file 크기 자동 증가시키기
SYS>alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' autoextend on ;
Tablespace Offline
Tablespace 를 Offline 한다는 것은 사용자가 더이상 해당 Tablespace 에 접근하지 못한다는 의미다.
해당 Tablespace 만 shutdown 시키는 것과 같은 의미이다.
이 기능은 특정 Tablespace 의 데이터 파일의 위치를 이동한다던지
혹은 특정 Tablespace 가 장애가 나서 복구를 해야 할 때 유용하게 사용되는 방법이다.
Tablespace offline 3가지 방법
* Nomal Mode
Tablespace 에 아무런 문제가 없을 때 정상적으로 수행
SYS> alter tablespace haksa offline ;
* Temporary Mode
현재 offline 시키고자 하는 Tablespace 의 Data file 이 하나라도 이상이 생기게 되면 offline nomal 이 수행되지 못한다.
이럴 때 쓰는 것이 offline temporary 이다.
* Immediate Mode
archive log mode 일 경우에만 사용해야 한다.
이 옵션은 Data file 에 장애가 나서 데이터를 내려쓰지 못하는 상황에서 Tablespace 를 offline 해야 할 경우에 사용한다.
SYS> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' offline ;
이 명령어는 DB가 archive log mode 일 경우에 사용가능하다.
만약 DB가 no archive log mode 라면
SYS> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' offline drop ;
을 수행해서 Data file 을 offline 시킬 수 있다.
그러나 노 아카이브 모드에서 offline drop 한 후 online 을 할 경우 recovery 하라는 메시지가 나온다.
어떤 Data file 이 offline 인지는 아래와 같이 v$datafile 을 조회하면 알 수 있다.
SYS>col name for a50
SYS>select file#, name, status from v$datafile ;
SYS>alter database datafile '/app/oracle/oradata/testdb/haksa02.dbf' offline drop ;
SYS>select file#, name, status from v$datafile ;
archive log mode 에서 data file 을 강제로 offline 시키면 복구가 필요한 상태가 된다.
SYS>alter tablespace users offline ;
SYS>select file#, name, status from v$datafile ;
SYS>select a.file#, a.ts#, b.name, a.status, a.checkpoint_change#
2 from v$datafile a, v$tablespace b
3 where a.ts# = b.ts# ;
위 내용을 보면 offline 상태여서 checkpoint scn 이 다른 파일들과 다름을 알 수 있다.
이 상태에서 online 을 시켜도 아래처럼 scn 은 여전히 다른 상태이다.
SYS>alter tablespace users online ;
SYS>select a.file#, a.ts#, b.name, a.status, a.checkpoint_change#
2 from v$datafile a, v$tablespace b
3 where a.ts# = b.ts# ;
이 상태에서 만약 Data file 을 백업을 받는다면 백업 파일 자체가 문제가 생기고 향후 복구에 문제가 될 수 도 있다.
이럴 경우에 수동으로 체크포인트를 발생시키면 모두 동기화가 된다. 그 후에 백업을 받아야 한다.
SYS>alter tablespace haksa online ;
SYS>select a.file#, a.ts#, b.name, a.status, a.checkpoint_change#
2 from v$datafile a, v$tablespace b
3 where a.ts# = b.ts# ;
SYS>alter system checkpoint ;
SYS>select a.file#, a.ts#, b.name, a.status, a.checkpoint_change#
2 from v$datafile a, v$tablespace b
3 where a.ts# = b.ts# ;
※ Tablespace 를 offline 후 online 하게 되면 반드시 수동으로 checkpoint 를 수행해서 SCN 번호를 동기화 시켜야 한다.
Data file 이동시키는 작업
특정 디스크의 Data file 들의 용량이 부족하여 새로운 디스크를 추가한 후 몇 개의 Data file 을 신규 디스크로 이동시키는 등의 작업을 할 때나
장애 발생시 복구 할 때 사용되는 중요한 방법이다.
※ Data file 을 사용 중일 때는 절대로 이동하거나 복사를 하면 안된다.
특정 데이터 파일을 사용하지 않게 만드는 방법은 Offline 과 Shutdown 이다.
1) Offline 되는 Tablespace 의 Data file 이동하기
Offline 기능은 여러 Tablespace 중에서 특정 Tablespace 만을 종료시키는 기능으로
DB 를 종료하지 않고 작업할 수 있으므로 아주 좋은 방법이다.
해당 Tablespace 만 shutdown 했다는 의미와 동일하다.
haksa tablespace 의 Data file 을 /app/oracle/disk1/haksa01.dbf, /app/oracle/disk2/haksa02.dbf 로 이동
SYS>!mkdir /app/oracle/disk1
SYS>!mkdir /app/oracle/disk2
순서요약
1. 해당 Tablespace offline !!
2. Data file 을 대상 위치로 복사
3. 컨트롤 파일 내의 해당 Data file 위치 변경
4. 해당 Tablespace online
SYS>alter tablespace haksa offline ;
SYS>!cp /app/oracle/oradata/testdb/haksa01.dbf /app/oracle/disk1/
SYS>!cp /app/oracle/oradata/testdb/haksa02.dbf /app/oracle/disk2/
SYS>select name from v$datafile ;
2 datafile '/app/oracle/oradata/testdb/haksa01.dbf'
3 to '/app/oracle/disk1/haksa01.dbf' ;
SYS>alter tablespace haksa rename
2 datafile '/app/oracle/oradata/testdb/haksa02.dbf'
3 to '/app/oracle/disk2/haksa02.dbf' ;
2) offline 안 되는 Tablespace 의 Data file 이동하기
offline 이 안되는 tablespace 는 아래 3가지다.
- system tablespace
- 사용 중인 undo tablespace
- default temporary tablespace
offline 이 안되기 때문에 어쩔 수 없이 DB를 종료하고 작업해야 한다.
순서요약
1. DB 종료
2. 마운트 상태로 시작
3. Data file 복사
4. 컨트롤 파일 내용 변경
5. DB 를 open
system01.dbf 파일을 /app/oracle/disk3/system01.dbf 로 이동
SYS>shutdown immediate ;
SYS>startup mount ;
SYS>!mkdir /app/oracle/disk3
SYS>!cp /app/oracle/oradata/testdb/system01.dbf /app/oracle/disk3/
SYS>select name from v$datafile ;
SYS>alter database rename
2 file '/app/oracle/oradata/testdb/system01.dbf'
3 to '/app/oracle/disk3/system01.dbf' ;
SYS>select name from v$datafile ;
SYS>alter database open ;
이 방법은 system 과 undo 만 옮길 수 있다는 것이 아니라 DB 가 마운트 상태이면 모든 Data file 및 Redo log 파일도 이 방법으로 이동시킬 수 있다.
3) Redo log file 이동하기
Redo log file 은 Offline 이 안되기 때문에 반드시 사용안하게 하기 위해 Database 를 Mount 상태로 두고 작업해야만 한다.
step1. 현재 상태 확인
SYS>col member for a50
SYS>select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
2 from v$logfile a, v$log b
3 where a.group# = b.group#
4 order by 1, 2 ;
SYS>select status from v$instance ;
SYS>shutdown immediate ;
SYS>startup mount ;
SYS>!mkdir /app/oracle/disk4
SYS>!mkdir /app/oracle/disk5
SYS>select member from v$logfile ;
SYS>!cp /app/oracle/data1/redo01_a.rdo /app/oracle/disk4/redo01_a.log
SYS>!cp /app/oracle/data2/redo01_b.rdo /app/oracle/disk4/redo02_a.log
SYS>!cp /app/oracle/data3/redo01_c.rdo /app/oracle/disk4/redo03_a.log
SYS>alter database rename
2 file '/app/oracle/data1/redo01_a.rdo'
3 to '/app/oracle/disk4/redo01_a.log' ;
SYS>alter database rename
2 file '/app/oracle/data2/redo01_b.rdo'
3 to '/app/oracle/disk4/redo02_a.log' ;
SYS>alter database rename
2 file '/app/oracle/data3/redo01_c.rdo'
3 to '/app/oracle/disk4/redo03_a.log' ;
SYS>select member from v$logfile ;
SYS>alter database add logfile member
2 '/app/oracle/disk5/redo01_b.log' to group 1,
3 '/app/oracle/disk5/redo02_b.log' to group 2,
4 '/app/oracle/disk5/redo03_b.log' to group 3 ;
SYS>select member from v$logfile ;
SYS>alter database open ;
Tablespace 삭제하기
SYS>drop tablespace haksa ;
Table 이 하나라도 있으면 안 지워진다. 아래와 같이 쓰면된다.
SYS>drop tablespace haksa including contents and datafiles ;
SYS>select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files ;
(4) Undo Tablespace
Undo Data 란 사용자가 DML 을 수행할 경우에 발생하는 원본 데이터를 말합니다.
Undo Data 만을 저장하는 Segment 를 Undo Segment 라고 하는 데
Undo Segment 를 저장하고 있는 특별한 Tablespace 가 Undo Tablespace 입니다.
Undo Tablespace 는 관리자가 생성할 수 있고 관리할 수 있지만 이 곳에는 Undo Data 외에 다른 데이터는 저장될 수 없으며,
Undo Data 라 하더라도 사용자가 관여할 수 없고 Oracle Server Process 가 직접 관리합니다.
Undo Tablespace 의 특징
1) Oracle Server Process 는 이 Tablespace 에 undo segment 를 생성하고 기본적으로 각 사용자별로 undo segment 를 할당해서
undo data 를 관리하며 사용자는 관여할 수 없습니다.
2) Undo tablespace 는 Instance 당 여러 개가 동시에 존재할 수 있지만 사용되는 것은 한번에 1개뿐입니다. (특별한 경우 예외)
3) 관리방법은 자동, 수동이 있는데 9i 부터는 Automatic Undo Management(AUM) 방법을 권장하고 있습니다.
AUM 을 사용하려면 초기화 파라미터 파일 (pfile, spfile) 에 Undo_management=auto 로 설정하고 수동은 manual 로 설정하면 됩니다.
Undo Tablespace 의 사용 목적
1) Transaction Rollback - 사용자가 rollback 을 수행할 경우 이곳에 저장된 undo data 를 사용해서 rollback 하게 된다.
2) Read Consistency (읽기 일관성) - CR 작업을 통해 트랜잭션이 끝나지 않은 데이터는 변경 전 데이터를 보여준다.
3) Transaction Recovery (Instance Recovery) - 운영중이던 DB 서버가 비정상적으로 종료되었을 때 Roll Forward 와 Roll Backward 작업을 수행해서
Dirty Database 를 Clean Database 로 만들어주는 과정에서 Undo data 가 사용된다.
Undo Segment 할당되는 원리
Undo Tablespace 는 Undo Data File 의 크기가 증가만 되고 줄어들지 않는다
신규 undo tablespace 생성하기
SYS>create undo tablespace undo01
2 datafile '/app/oracle/oradata/testdb/undo01.dbf' size 10M
3 autoextend on ;
SYS>col tablespace_name for a10
SYS>col file_name for a50
SYS>select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files ;
Undo tablespace 변경하기 (UNDOTBS1 → UNDO01)
SYS>show parameter undo ;
SYS>alter system set undo_tablespace=undo01;
재부팅 없이 즉시 변경 가능.
pfile 을 사용할 경우 반드시 파라미터 파일의 내용도 변경해야 DB 재시작 후 장애가 없다.
SYS>show parameter undo ;
각 세션 별로 사용중인 undo segment 확인하기
(아래 작업을 하기 전에 터미널 여러 개 확인해서 DML 수행하고 commit 하지 말고 조회하세요)
SYS>select s.sid, s.serial#, s.username, r.name "ROLLBACK SEG"
2 from v$session s, v$transaction t, v$rollname r
3 where s.taddr = t.addr
4 and t.xidusn = r.usn ;
Undo 관련 주요 parameter
* UNDO_RETENTION (단위는 초다.)
commit 수행 후에도 해당 undo segment 내의 데이터를 다른 서버 프로세스가 덮어 쓰지 못하도록 막아주는 시간이다.
이 파라미터는 주로 CR 작업으로 Undo Segment 에 있는 데이터를 조회해야 하는 경우에 많이 발생한다.
A가 집계용 Select 수행 중
B가 Update 후 Commit
C가 Undo segment 덮어 씀
아래 에러 발생
ORA-01555. Snapshot too old
이 에러를 막기 위해 사용되는 파라미터가 UNDO_RETENTION 이다
commit 이 완료된 Undo segment 는 다른 사용자에 의해 덮어 쓰여질 수 있지만
이 파라미터로 시간을 설정하면 설정 시간동안 덮어 쓰지 않는다.
그러나 이 파라미터는 Undo segment 여분이 있을 경우에만 적용된다.
* UNDO_RETENTION_GUARANTEE
Undo segment 의 여분의 개수가 없더라도
Undo retention 파라미터로 설정된 시간 동안 재활용하지 않고 지켜준다.
단 commit 을 수행해도 특정 시간동안 재활용하지 않기 때문에
undo tablespace 의 사용량이 증가될 수 있다.
그래서 기본값은 noguarantee 이다.
SYS>select tablespace_name, retention from dba_tablespaces ;
SYS>show parameter undo ;
SYS>alter tablespace undotbs1 retention guarantee ;
SYS>select tablespace_name, retention from dba_tablespaces ;
위의 방법으로 변경하게 되면 Undo_retention 으로 지정한 값을 Guarantee 하게 된다.
SYS>alter tablespace undo01 retention noguarantee ;
SYS>select tablespace_name, retention from dba_tablespaces ;
undo tablespace 삭제하기
SYS> drop tablespace undo01 ;
사용중인 undo segment 를 포함하고 있는 undo tablespace 는 삭제할 수 없다.
사용하지 않는데 사용중이라고 뜨면
undo segment 를 _offline_rollback_segments 라는 히든 파라미터를 사용하여 강제 offline 시킨 후 삭제하면 된다.
(5) temporary table space
임시 자료를 저장하는 tablespace 로 db 가 재시작되면 이 곳에 있던 내용은 모두 사라지며
일반적으로는 정렬작업 등을 수행할 경우 pga 공간이 부족하면 이곳을 이용하여 정렬작업 등을 하게 된다.
export / import 등에도 사용한다.
하나의 instance 에 여러개 만들 수 있고 성능 향상을 위해 각 사용자별로 하나씩 할당해 주는 것이 좋다.
정렬 작업이 크게 일어나는 작업 전에는 temporary tablespace 의 크기를 크게 만들어주고 작업하는 것이 성능향상에 좋다
11g 이상이라면 temporary tablespace group 기능 활용 권장
기존 temporary tablespace 조회하기
SYS>set line 200
SYS>col tablespace_name for a10
SYS>col file_name for a50
SYS>select file_id, tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_temp_files ;
신규 생성하기
SYS>create temporary tablespace temp2
2 tempfile '/app/oracle/oradata/testdb/temp02.dbf' size 10M
3 autoextend on ;
Default Temporary Tablespace 설정하기
여러 개를 만들어 사용자별로 할당하여 동시에 사용하게 하는 것이 성능에 좋다.
반드시 1개는 Default 로 지정해야 한다.
독자적인 Temporary Tablespace 를 할당받지 못한 사용자나 System tablespace 가 사용하는 temporary tablespace 를 의미한다.
SYS>set line 200
SYS>col property_name for a30
SYS>col property_value for a10
SYS>col description for a50
SYS>select * from database_properties
2 where property_name like 'DEFAULT_TEMP%' ;
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 삭제하기
SYS>drop tablespace temp2 ;
default temporary tablespace 는 삭제가 안된다
다른 temporary tablespace 생성 후
SYS>create temporary tablespace temp3
2 tempfile '/app/oracle/oradata/testdb/temp03.dbf' size 10M ;
default 로 변경
SYS>alter database default temporary tablespace temp3 ;
삭제 됨
SYS>drop tablespace temp ;
Temporary tablespace group (11g New Feature)
일반적인 DB 접속 환경을 보면 여러 명의 유저가 1개의 schema 를 통해 DB 에 접속하는 경우가 많다.
여러 사용자가 Order by 를 하였을 때 PGA 가 부족하면 temporary tablespace 를 사용할 텐데 작업이 늦어지게 될 수 가 있다.
여러 개의 temporary tablespace 를 만든 후 그룹으로 묶어 주고 특정 schema 에게 temporary tablespace group 을 지정해주면
하나의 스키마로 여러 명의 사용자가 동시에 로그인 하더라도 temporary tablespace group 안에 있는 여러개의 temp tablespace 를
각각 별도로 사용해서 성능 향상이 이루어질 수 있다.
SYS>create temporary tablespace temp_scott1
2 tempfile '/app/oracle/oradata/testdb/temp_scott01.dbf' size 10M
3 tablespace group temp_scott_group ;
이미 만들어져 있는 tempoary tablespace 를 그룹에 추가하려면
SYS>alter tablespace temp_scott2 tablespace group temp_scott_group ;
할당되어 있는 그룹을 조회하려면
SYS>select * from dba_tablespace_groups ;
SYS>alter user scott temporary tablespace temp_scott_group ;
SYS>alter database default temporary tablespace temp_scott_group ;
그룹에서 탈퇴하려면
SYS>alter tablespace temp_scott2 tablespace group ' ' ;
공백을 주면 그룹이 없는 것으로 인식되어 탈퇴하게 된다.
08 Oracle 저장구조
크기 순서
OS Block - Oracle Block - Extent - Segment - Tablespace - Database
1. Oracle Block 개요
Oracle Block 은 OS Block 한 개 이상 합쳐서 생성하게 되며 DB_BLOCK_SIZE 로 지정됩니다.
DB_BLOCK_SIZE 파라미터는 CREATE DATABASE 할 때 한 번 지정이 되면 그 값은 Database 를 재생성하기 전에는 변경할 수 없으며
이 파라미터에서 지정되는 크기 값을 Standard Block size 라고 합니다.
Block 크기는 최소 2KB 부터 4KB, 8KB, 16KB, 32KB 가 제공됩니다.
Block 크기가 크면 한번에 담을 수 있는 데이터의 양이 많아서 I/O 를 줄일 수 있지만 반면에 데이터가 적을 경우 공간이 낭비되는 단점이 있습니다.
그리고 OWI 에서 중요하게 생각하는 대기현상이 많이 생길 수 있어서 전체적인 성능 저하 요인이 되므로 크기를 잘 설정해야 합니다.
권장값은 오라클에서 제공하는 기본크기를 사용하는 것입니다.
2. Oracle Data Block 상세 구조
Data Block Layout
Cache layer (common)
Transaction layer (variable)
Block header |
Data layer
Table directory |
Row directory |
Free space |
Row data |
Block 의 가장 윗부분에는 Block 의 Header 부분이 위치하고 있고
Block Header 는 Cache Layer 와 Transaction Layer 로 다시 나누어져 있습니다.
Block Header 부분 다음에 Table Dictionary 가 있고 그 아래에 Row Dictionary 가 위치하고
그 아래에 데이터가 직접 저장되는 공간으로 구성되어 있습니다.
Data Block Header : Cache Layer
common
Fixed and variable
Cache layer |
Transaction layer |
Data layer
Table directory |
Row directory |
Free space |
Row data |
Cache layer 에는 아래와 같은 주요 정보가 들어가게 됩니다.
Data Block Address (DBA)
Block Type (ex: Table, Index, Undo...)
Block Format (v6, v7, v8 ...)
System Change Number (SCN: 복구작업 등에 사용됨)
Data Block Header : Transaction Layer
Cache layer |
Transaction layer: fixed |
Transaction layer: variable |
Data layer
Table directory |
Row directory |
Free space |
Row data |
이 곳에는 Block Type, 마지막으로 수행된 Block Cleanout 시간,
ITL Entries 정보, Free List Link 정보, Free space Lock 정보 등이
저장되어 있습니다.
ITL (Interested Transaction): 특정 Block 에 데이터를 변경해야 할 경우 해당 Block 에 사용자 명단에 자신의 정보를 적고 변경하는 것
대기자 명단
3. PCTFREE 와 PCTUSED
PCTFREE: Block 에 입력되어 있는 데이터들이 Update 될 경우를 대비해서 예약해 두는 공간
PCTUSED: Block 에 내용이 지워진 후 남은 용량이 얼마가 되어야 Free Block 이 되는가를 결정하는 파라미터
4. Row Data 와 Row Chaining & Row Migration
Row Chaining: A Block 에 공간이 부족해 B Block 까지 이어서 저장되는 것
Row Migration: 특정 Block 에 위치하던 Row 가 Update 등의 이유로 해당 Block 에 공간이 부족해 다른 공간으로 이사를 가는 것
5. Extent 와 Segment
Extent: 연속적으로 있는 Block 을 묶어 둔 것을 의미하는 논리적 단위
Segment: Extent 가 여러개 모인 것으로 Object 중에서 독자적인 저장 공간을 가지는 것
Object: Oracle 에서 관리자나 사용자가 데이터를 관리하기 위해 만드는 (create로 만드는) 모든 것을 의미
HWM (High Water Mark)
저장공간을 갖는 세그먼트 영역에서 사용한 적이 있는 Block과 사용한 적이 없는 Block 의 경계점을 의미한다.
6. Free List Management (FLM) 기법을 사용한 Extent 관리
DMT (Table Dictionary Management): Block 들의 정보를 Dictionary 로 일괄적으로 관리하는 방식, 데이터가 몰릴 경우 병목 현상
LMT (Locally Managed Tablespace): 각 Tablespace 별로 Block 의 정보를 따로 관리
(1) FLM 방식에서 Free Extent 를 찾는 순서
LIFO
FLM (Free List Management):
Free List 의 관리에 따라 성능이 결정되는 수동 모드 관리 방법
사용자가 segment 를 생성할 때 Free list 의 속성이나 pctfree, pctused 값을 어떻게 주느냐에 따라 성능이 많이 차이가남
Master Free List: 해당 Segment 에 새롭게 할당된 Free Block 이나 트랜잭션이 종료되어 Dirty 에서 Free 로 반환된 Block 들의 목록이 저장되어 있음
Process Free List: Master Free List 와 마찬가지로 해당 Segment 에 트랜잭션이 끝나서 Free Block 이 된 명단을 가지고 있음
Transaction Free List: 현재 세션이 Transaction 을 수행하는 도중 Free block 으로 변한 블록들의 목록을 관리
7. Automatic Segment Space Management (ASSM) 기법을 사용한 Extent 관리
ASSM (Automatic Segment Space Management)
Free List 를 사용하는 대신 Block 의 사용정도를 Bitmap 으로 관리함
09 Oracle 메모리 관리기법
1. 9i 버전에서의 메모리 관리기법
WORKAREA_SIZE_POLICY= auto / manual: PGA 관리모드 설정
PGA_AGGREGATE_TARGET: PGA 전체크기 지정
대량의 sort 나 hash 가 발생하는 작업은
WORKAREA_SIZE_POLICY=manual, SORT_AREA_SIZE, HASH_AREA_SIZE 값 최대로 (2G 권장)
2. 10g 버전에서의 메모리 관리기법
ASMM (Automatic Shared Memory Management): MMAN 이라는 백그라운드 프로세스가 현재 SGA 의 Workloaad 를 보고
메모리의 부족함과 충분함을 판단한 후 충분한 곳에서 메모리를 회수해 부족한 곳으로 재배치 해주는 기능
ASMM 사용법
STATISTICS_LEVEL = TYPICAL or ALL 로 설정하고
SGA_TARGET 파라미터의 값을 0보다 큰 값으로 설정한 후
다른 파라미터의 크기를 0으로 설정하면 된다.
3. 11g 버전에서의 메모리 관리기법
AMM (Automatic Memory Management): SGA 뿐만 아니라 PGA 까지 자동으로 관리하는 기술
MEMORY_TARGET: AMM 으로 관리할 메모리 총량 지정
MEMORY_MAX_TARGET: MEMORY_TARGET 값이 최대로 증가될 값 지정
10 사용자 관리
1. Schema 와 User
Schema - 특정 사용자가 만들어 놓은 모든 Object 의 집합
데이터베이스를 설치한 후 SYS 계정은 반드시 암호를 넣어야 접속이 되도록 설정을 변경해야 한다.
사용자 생성 순서
default tablespace 생성 → temporary tablespace 생성 → 사용자 생성 → 적절한 프로파일과 권한, 롤 등을 생성한 후 할당
2. user 생성하기
(1) Default tablespace 생성하기
SYS> create tablespace ts_webhard
2 datafile '/app/oracle/oradata/testdb/ts_web01.dbf' size 100m ;
SYS> create tablespace ts_web_idx
2 datafile '/app/oracle/oradata/testdb/ts_web_idx01.dbf' size 100m ;
(2) Temporary tablespace 생성하기
SYS> create temporary tablespace temp_web
2 tempfile '/app/oracle/oradata/testdb/temp_web01.dbf' size 10m ;
(3) 사용자 생성
SYS> create user webuser
2 identified by webpwd
3 default tablespace ts_webhard
4 temporary tablespace temp_web
5 quato unlimited on ts_webhard 할당량 지정 무제한
6 quota 0m on system ; system tablespace 사용 못하게
(4) 권한 설정하기
SYS> grant resource, connect to webuser ;
SYS> conn webuser/webpwd ;
3. 사용자 정보 확인하기
SYS> select username, default_tablespace, temporary_tablespace
2 from dba_users
3 where username='WEBUSER' ;
4. profile 관리하기
사용자 계정에 제약사항을 두는 것
(1) Password profile 관련 파라미터
1) password 관련 profile 생성하기
- 로그인 시도 5회 실패시 계정을 5일 동안 사용 못하게 할 것
- 계정의 암호는 10일에 한번씩 변경
- 동일한 암호는 10일 동안 사용 못하게 할 것
SYS> create profile sample_prof limit
2 failed_login_attempts 5
3 password_lock_time 5
4 password_life_time 10
5 password_reuse_time 10 ;
(2) Resource profile 관련 파라미터
2) resource 관련 profile 생성하기
- 1명당 연속적으로 CPU 를 사용할 수 있는 시간을 10초로 제한할 것
- 하루 중 8시간만 DB 에 접속 가능하게 할 것
- 10분 동안 사용하지 않으면 강제로 접속 끊을 것
SYS> create profile re_sample_prof limit
2 cpu_per_session 1000
3 connect_time 480
4 idle_time 10 ;
사용자가 적용 받고 있는 profile 확인
SYS> select username, profile from dba_users where username='WEBUSER' ;
해당 profile 내용 조회
SYS> select * from dba_profiles where profile='SAMPLE_PROF' ;
사용자에게 적용시키기
SYS> alter user webuser profile sample_prof ;
삭제하기
SYS> drop profile sample_prof ;
사용자에게 할당되어 있는 프로파일은 cascade 옵션을 사용해야 된다
SYS> drop profile sample_prof cascade ;
5. privilege (권한) 관리하기
profile 은 제한하는 것, privilege 는 허락하는 것
(1) SYSTEM 관련 주요 privilege
(2) SYSOPER / SYSDBA privilege
(3) SYSTEM 관련 권한 할당하기 / 해제하기
SYS> grant create table, create session to scott ;
SYS> revoke create table from scott ;
(4) 사용자가 가지고 있는 권한 조회하기
SYS> select * from dba_sys_privs where grantee='SCOTT' ;
adm 은 with admin option 권한 위임 기능
(5) Object 관련 Privilege
(6) Object 권한 할당하기 / 해제하기
With admin option 권한을 위임한 사용자로부터 받은 권한 해제 안됨
With grant option 권한을 위임한 사용자로부터 받은 권한을 함께 해제
6. Role 관리하기
Role 이란 권한의 그룹
(1) Role 생성
SYS> create role trole ;
(2) Role 에 권한 할당
SYS> grant create session, create table to trole ;
(3) 사용자에게 role 할당
SYS> grant trole to scott ;
(4) 어떤 사용자가 어떤 Role을 사용하는지
SYS> select * from dba_role_privs where grantee='SCOTT' ;
(5) 어떤 Role 에 어떤 권한이 있는지
SYS> select * from dba_sys_privs where grantee='CONNECT' ;
SYS> select * from dba_sys_privs where grantee='RESOURCE' ;