EQUI JOIN(동등 조인) - EQUAL 연산자(=)를 사용 WHERE 절에 기술되는 JOIN 조건을 검사해서 양쪽 테이블에 같은 조건의 값이 존재할 경우 해당 데이터를 가져오는 조인 방법 Natural Join : where절에 조인조건을 사용하지 않아도 두 테이블 사이에 동일한 이름과 타입의 컬럼 값이 딱 하나 존재한다면 EQUI JOIN과 동일한 결과가 나타난다. 동일한 이름과 타입의 컬럼이 두 개 이상이면 using절에 조인할 컬럼을 써주면 된다.
NON EQUI JOIN(비등가 조인) - 같은 조건이 아닌 크거나 작거나 하는 경우 JOIN을 수행
CROSS JOIN(교차 조인)
카디션 곱이라고도 하며 조인되는 두 테이블에서 곱집합을 반환
첫 번째 테이블의 한 열에 두 번째 테이블의 모든 열이 한 번씩 결합된 열을 만들어 모두 생성
M * N 개의 열을 생성
OUTER JOIN
여러 테이블에서 한 쪽에는 데이터가 있고 다른 한 쪽에는 데이터가 없는 경우, 데이터가 있는 쪽 테이블의 내용을 전부 출력하는 방법
조인 조건에 만족하지 않아도 해당 행을 출력하고 싶을 때 사용
LEFT OUTER JOIN - 조인문의 왼쪽에 있는 테이블의 모든 결과를 가져온 후 오른쪽 테이블의 데이터를 매칭하고, 매칭되는 데이터가 없는 경우 NULL을 표시
RIGHT OUTER JOIN - 조인문의 오른쪽에 있는 테이블의 모든 결과를 가져온 후 왼쪽 테이블의 데이터를 매칭하고, 매칭되는 데이터가 없는 경우 NULL을 표시
FULL OUTER JOIN - 양쪽 모두 조건이 일치하지 않는 것들까지 모두 결합하여 출력하고 빈칸은 NULL로 표시
관계형 데이터베이스의 설계에서 중복을 최소화하게 데이터를 구조화하는 프로세스를 정규화라고 한다.
함수적 종속성을 이용해서 연관성 있는 속성들을 분류하고, 각 릴레이션들에서 이상현상이 생기지 않도록 하는 과정
제 1 정규형(1NF; First Normal Form)
릴레이션에 속한 모든 속성의 도메인이 원자 값으로만 구성되어 있으면 제 1 정규형에 속한다.
즉 여러 값들이 한 행의 도메인(한 칸)에 들어있지 않고 하나씩만 채워져 있는 테이블을 말한다.
제 2 정규형(2NF; Second Normal Form)
제 1 정규형에 속하면서, 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속되면 제 2 정규형이다.
부분 함수 종속성을 제거하는 작업
복합키에서 후보키 중 하나를 알면 키가 아닌 속성을 알 수 있는 경우 함수 종속이 존재한다고 말한다.
이러한 함수 종속을 제거하는 작업
서로 종속되는 컬럼들을 가지는 테이블을 만든다.
원래 테이블의 기본키가 아닌 속성이 5번의 테이블에 들어갔다면 원래 테이블에서 그 속성들을 제외한 테이블로 바꿔준다.
제 2 정규형을 만족한다 하더라도 삽입이상, 갱신이상, 삭제이상 등의 이상현상이 발생한다.
이는 이행적 함수 종속이 존재하기 때문이다.
삽입이상 : 하나의 새로운 값이 삽입될 때, 다른 값에 기본키에 NULL이 들어가는 경우
갱신이상 : 하나의 값이 수정이 될 때, 연관된 다른 값들도 수정이 되야하는데 수정되지 않아 불일치 하는 경우
삭제이상 : 하나의 값이 삭제될 때, 삭제를 하지 않아도 되는 값까지 함께 지워지는 경우
제 3 정규형(3NF; Third Normal Form)
제 2 정규형에 속하면서, 기본키가 아닌 모든 속성이 기본키에 이행적 함수 종속이 되지 않으면 제 3 정규형이다.
이행적 함수 종속 : X->Y 이고 Y->Z이면 X->Z가 성립하는 경우 Z가 X에 이행적으로 함수 종속되었다고 말한다.
[X,Y], [Y,Z]로 분해
BCNF (Boyce-Code Normal Form)
후보키가 1개 밖에 없고 그 후보키가 테이블의 기본키가 되면서 3NF를 만족하면 항상 BCNF를 만족한다.
후보키가 여러개인 경우에는 3NF를 만족하지만 이상현상이 발생하는 경우가 있는데, 이를 해결하기 위한 정규형이 보이스-코드 정규형이다.(strong 3NF 라고도 한다.)
모든 결정자가 KEY인 경우 BCNF 이다.
만약 후보키가 아닌 다른 컬럼이 결정자(다른 값을 결정)가 된다면 BCNF를 위반한다.(일반 컬럼이 후보키를 결정하는 경우)
제 4 정규형(4NF)
다중값 종속을 제거하는 과정을 의미
다가 종속 : 한 컬럼의 값에 대하여 대응하는 다른 컬럼의 값이 여러 개 일 때(x->->y)
예) 학생 x 는 한 학기에 여러 개의 과목 y 를 수강할 수 있다.
A, B, C 가 주어지고 A ->->B, A ->-> C 일 때
(A, B), (A, C) 로 나눈다.
제 5 정규형(5NF)
조인 종속을 없앤 것
하나의 릴레이션을 여러 개의 릴레이션으로 분해 한 후 공통 속성으로 조인하여 데이터 손실 없이 원래의 릴레이션으로 복원할 수 있으면 이를 무손실 조인이라 한다.
조인한 결과에 원래 릴레이션에 없는 데이터가 존재하지 않으면 이를 비부가적 조인이라고 한다.
필요한 데이터가 사라지지 않는 무손실 분해가 되고 필요없는 데이터가 생기지 않는 데이터가 생기지 않는 비부가적 분해가 된 릴레이션
X, Y, Z로 이루어진 릴레이션을 {X, Y}, {Y,Z}, {X,Z}로 이루어진 릴레이션들로 분해했을 때, 2개의 릴레이션을 조인하면 분해하기 전의 릴레이션을 만들 수 없고 꼭 3개의 릴레이션을 모두 조인해야 원래의 릴레이션을 만들 수 있을 때 제 5 정규형이라고 말할 수 있다.
NOT NULL - 컬럼을 정의할 때, NOT NULL 제약조건을 명시하면 해당 컬럼에는 반드시 데이터를 입력해야만 한다.
UNIQUE - 해당 컬럼의 각 값은 중복되는 값이 없어야 한다. NOT NULL과 함께 사용할 수 있다.
PRIMARY KEY - '기본키' 라고 불리는 UNIQUE + NOT NULL 의 형태를 띄며, 테이블 당 1개의 기본키만 생성할 수 있다. 여러 컬럼을 묶어 하나의 기본키로 만드는 것도 가능하다. (최대 32개 까지) 기본키는 데이터 무결성을 지켜주는 역할을 한다. 고유 인덱스가 자동으로 생성된다.
FOREIGN KEY - '외래키' 라고 불리는 제약조건이다. 테이블 간의 참조 데이터 무결성을 보장해준다. 참조 데이터 무결성 보장을 통해 참조 관계가 있는 테이블의 데이터 추가, 삭제, 수정을 통제할 수 있다.
참조하는 테이블이 먼저 생성되어 있어야 함
외래키가 참조하는 컬럼은 참조하는 테이블의 기본키(PRIMARY KEY) 이어야 함
여러 컬럼을 외래키로 할 경우, 참조하는 테이블의 기본키와 컬럼 개수 및 순서가 같아야 함
기본키와 마찬가지로, 최대 32개 컬럼까지 가능하다.
기본키와는 달리 NULL값이 들어가거나 중복된 값이 들어갈 수 있음
CHECK - 이 제약조건이 걸려있는 컬럼에는 조건에 일치하는 값들만 들어갈 수 있다.
트랜잭션
하나의 논리적 작업 단위를 구성하는 하나 이상의 SQL 문장
원자성(Atomicity) - 한 트랜잭션안의 작업들이 부분적으로 수행되다가 중단되지 않는 것을 보장하는 능력
트랜잭션의 연산은 데이터베이스에 모두 반영되든지 아니면 전혀 반영되지 않아야 한다.
트랜잭션 내의 모든 명령은 반드시 완벽히 수행되어야 하며, 모두가 완벽히 수행되지 않고 어느 하나라도 오류가 발생하면 트랜잭션 전부가 취소되어야 한다.
일관성(Consistency) - 트랜잭션이 실행을 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 유지하는 것
시스템이 가지고 있는 고정요소는 트랜잭션 수행 전과 트랜잭션 수행 완료 후의 상태가 같아야 한다.
ex) 트랜잭션을 수행한 후 변경된 정보가 제약조건에 맞지 않는다면 오류가 발생한다.
독립성/격리성(Isolation)
둘 이상의 트랜잭션이 동시에 병행 실행되는 경우 어느 하나의 트랜잭션 실행중에 다른 트랜잭션의 연산이 끼어들 수 없다.
수행중인 트랜잭션은 완전히 완료될 때까지 다른 트랜잭션에서 수행 결과를 참조할 수 없다.
ex) employees 테이블을 update하고 insert하는 작업의 트랜잭션을 수행하는 도중에는 commit이 완료될 때까지 다른 트랜잭션에서 employees의 변경중인 내용을 확인하거나 동시에 변경할 수 없다.
영속성/지속성(Durability)
성공적으로 완료된 트랜잭션의 결과는 시스템이 고장나더라도 영구적으로 반영되어야 한다.
commit 연산 - 한 개의 논리적 단위(트랜잭션)에 대한 작업이 성공적으로 끝났고 데이터베이스가 다시 일관된 상태에 있을 때, 이 트랜잭션이 행한 갱신 연산이 완료된 것을 트랜잭션 관리자에게 알려주는 연산이다.
rollback 연산 - 하나의 트랜잭션 처리가 비정상적으로 종료되어 데이터베이스의 일관성을 깨뜨렸을 때, 이 트랜잭션의 일부가 정상적으로 처리되었더라도 트랜잭션의 원자성을 구현하기 위해 이 트랜잭션이 행한 모든 연산을 취소(Undo)하는 연산이다. Rollback 시에는 해당 트랜잭션을 재시작하거나 폐기한다.
장점 : 테이블에 많은 열이 포함되어 있거나 대량의 데이터가 저장되어 있는 경우, 테이블에서 특정 데이터를 검색하려고 하면 매우 시간이 걸릴 수 있다, 이런 경우에 적절한 컬럼에 인덱스를 생성하면 검색이 빨라질 수 있다.
단점 : 테이블과는 별도로 인덱스의 저장공간이 필요하고 테이블에 데이터가 추가되면 인덱스에도 데이터가 추가된다. 또한 데이터가 추가될 때마다 인덱스의 정렬이 이루어져 데이터를 추가하는 처리속도가 느려진다.
데이터와 위치주소(ROWID) 쌍으로 저장하고 관리됨
빠르게 쿼리 검색을 하기위함
B-TREE 인덱스(Binary, Balance 의 약자)
OLTP(Online Transaction Processing : 실시간 트랜잭션 처리)
실시간으로 데이터 입력과 수정이 일어나는 환경에 많이 사용
Root block(기준 값보다 작으면 왼쪽, 크면 오른쪽) - Branch block(root block과 같은 형태로 기준점을 잡고있음) - Leaf Block(원하는 값들이 저장되어 있음) 순으로 아래로 내려가는 tree 형태
Unique Index : 인덱스 안에 있는 컬럼 key 값에 중복되는 데이터가 없다. -> unique 제약조건과 유사, unique 제약조건을 사용하면 자동으로 unique index가 만들어진다. 기본키(unique+not null)을 생성해도 자동으로 unique index가 만들어진다. 이때 UNIQUE나 기본키 객체명과 동일하게 생성된다.
Non Unique Index : 중복되는 데이터가 들어가야 하는 경우(key로 지정한 필드의 중복된 값이 들어갈 수 있다)
FBI(Function Based Index - 함수기반 인덱스) : 인덱스는 where 절에 오는 조건 컬럼이나 조인에 쓰이는 컬럼으로 만들어야 한다. 인덱스를 사용하기 위해서는 where 절의 조건을 절대로 다른 형태로 가공해서(upper, lower 등) 사용하면 안된다. where 절에서 upper나 sal+100 등 함수나 사칙연산 등을 사용하여 비교한 경우 그 함수가 쓰인 인덱스를 만들어야 한다.
Descending Index(내림차순 인덱스) : 내림차순으로 인덱스를 생성 큰 값을 많이 조회하는 SQL에 생성하는 것이 좋다.
결합 인덱스(Composite Index) : 인덱스 생성시에 두 개 이상의 컬럼을 합쳐서 인덱스를 생성 주로 where 절의 조건이 되는 컬럼이 2개 이상으로 and로 연결되는 경우 사용 컬럼의 순서에 따라 효율에 차이가 있다. -> 보통 자주 사용하는 컬럼을 앞에 위치시키는 것이 좋다.
BITMAP 인덱스
OLAP(Online Analytical Processing : 온라인 분석 처리)
대량의 데이터를 한꺼번에 입력한 뒤 주로 분석이나 통계 정보를 출력할 때 많이 사용함
데이터 값의 종류가 적고 동일한 데이터가 많을 경우에 많이 사용
Bitmap Index를 생성하려면 데이터의 변경량이 적어야 하고, 값의 종류도 적은 곳이 좋다.
어떤 데이터가 어디에 있다는 지도정보(MAP)를 Bit로 표기하게 된다.
데이터가 존재하는 곳은 1로 표시, 데이터가 없는 곳은 0으로 표기
정보를 찾을 때, 1인 값만 찾게 된다
비트맵 인덱스를 사용하고 있는 상태에서 컬럼 값이 새로 하나 더 생긴다면 기존의 Bitmap Index를 전부 수정해야 한다.
Full Table Scan
High water mark 까지 스캔하는 방법
인덱스 스캔이 아니고 인덱스가 없는 경우 발생하는 기본 스캔
인덱스가 없을 경우, full 힌트를 사용, 인덱스를 생성할 때, 테이블의 통계정보를 수집할 때 Full table scan 사용
Index Range Scan
인덱스의 일부분만 범위 스캔해서 data를 엑세스
Index Full Scan
index full scan : 인덱스를 full로 스캔 인덱스 구조에 따라 스캔 순서가 보장(정렬) single block i/o 병렬 스캔 불가능
index fast full scan : 인덱스를 full로 스캔하는데 더 빠름 세그먼트 전체를 스캔 순서가 보장되지 않음(정렬x) multi block i/o 병렬스캔이 가능
index skip scan
인덱스를 full 또는 fast full로 전체를 스캔하는 것이 아니라 중간 중간 skip을 해서 사용
index_ss 힌트 사용
인덱스의 첫번째 컬럼이 where 조건에 없어도 인덱스를 사용할 수 있게 한다.
index merge scan
두 개의 인덱스를 동시에 사용해서 하나의 인덱스만 사용했을 때보다 더 큰 시너지 효과를 보게하는 스캔 방법
table 엑세스 횟수를 줄이는 효과가 있다.
index bitmap merge scan
index merge scan과 스캔방법은 똑같은데 인덱스의 크기를 줄이기 위해서 인덱스를 bitmap으로 변환하는 작업이 추가
index join
인덱스끼리 조인해서 바로 결과를 보고 테이블 엑세스는 따로 하지 않는 스캔 방식
index unique scan
primary key나 unique 제약을 걸면 unique 인덱스가 자동으로 생성이 되는데 바로 이 unique 인덱스를 이용해서 데이터를 스캔하는 방법
해당 컬럼에 unique 제약이 있으면 자동으로 적용
AWR(Automatic Workload Repository)
자동으로 DB에 대한 통계 및 성능자료 등을 수집해 스냅샷으로 만들어 일정기간 보관하고, 이를 활용할 수 있게 해주는 기능
Buffer, CPU, Pin, Latch, Library 등의 히트율, 자원 사용률, soft/hard parse 정도, 가장 느리게 돌았던 쿼리 등 수집
hot backup은 백업중에도 DB의 가용성을 지켜야 하기 때문에 변경사항들이 Redo Log에 쌓였다가 Backup이 끝나면 DataFile에 내려쓰는 구조로 동작한다. 그러므로 Archive Log의 사용은 필수다.
alter tablespace 이름 begin backup; -> alter tablespace 이름 end backup; -> alter system archive log current;
RAC(Real Application Cluster)
하나의 database에 하나의 instance가 할당되는 구조인 single server와는 달리 2개 이상의 instance들을 하나로 묶어 database를 관리하는 서버를 말한다.
작업에 대해 병렬처리를 수행할 수 있어 성능이 좋아질 수 있다.
9i 버전부터는 서로 다른 instance에서 변경된 데이터를 디스크를 거치지 않고 바로 instance로 가져올 수 있는 기능인 cache fusion 기능을 사용 -> instance(memory)1 에서 변경된 데이터가 가장 최근 데이터라면 이 메모리 안의 데이터를 바로 가져와 instance2에서 사용
10g 부터는 ASM 방식으로 RAC를 구성하여 사용
clusterware : 클러스터용 프로그램
11g에서 ASM 기능이 clusterware에 통합 -> grid라는 명칭으로 변경
OCR(Oracle Cluster Repository)
RAC 구성의 전체 정보를 저장하고 있는 디스크로 RAC의 핵심
RAC를 시작할 때 OCR에 저장되어 있는 정보를 보고 RAC를 구성해야 하는데, RAC 시작 후 ASM instance를 시작하기 때문에 OCR을 ASM에 저장할 경우 RAC를 시작할 수 없게 됨 -> 별도의 raw device에 저장
Vote Disk
각 node들이 장애가 있는지 없는지를 구분하기 위해서 사용
CSSD(node마다 가지고 있는 신호기)가 보내는 Heartbeat에 응답을 보내면서 매초마다 vote disk에도 자신이 정상적으로 동작하고 있다는 표시를 함
CSSD는 vote disk 뿐만 아니라 연결된 가까운 node들에도 heartbeat를 보내며 다른 노드들과 신호를 주고 받았다는 정보를 vote disk에 알려줌
만약 노드들과 신호를 주고받지 못하고 연결이 끊어진 상태라면 CSSD는 vote disk에서 2차적으로 확인 후, 이상이 있는 node를 cluster에서 분리시키는 작업을 수행
File system - 파일과 그 안에 든 자료를 저장하고 찾기 쉽도록 유지, 관리하는 방법
오라클이 OS를 통해서 디스크에 접근
디렉토리 구조로 관리하므로 사용자의 편의성이 높다.
OS를 통하므로 속도와 성능이 상대적으로 떨어진다.
OS 의존도가 높다.
Raw Device
오라클이 직접 디스크에 접근하는 방식
다이렉트로 디스크에 접근하므로 디스크 I/O가 적다.
File system보다 성능, 속도면에서 우수
관리하는 방식이 까다롭다.
ASM(Automatic Storage Management)
File system과 Raw Device의 장점을 모아 스토리지를 관리하는 기술
데이터를 저장하거나 불러오는 방식에서는 File system과 동일하지만 OS가 아닌 ASM에게 요청하는 부분에서 차이가 있다.
디스크를 추가하고 삭제하는 작업이 보다 쉽게 가능
서로 다른 디스크에 균등하고 자동으로 분산 가능
File system에 비해 속도가 빠름
백업시 RMAN을 사용
Dataguard
primary DB와 standby DB를 동기화시켜, primary DB가 하드웨어 장애 등의 문제가 생겼을 경우 standby DB로 failover 또는 switchover 시킬 수 있는 시스템 구성을 말한다.
Redo apply : Physical Data Guard를 구성할 때 사용하는 방법으로 블록 기반으로 Primary Database와 동일한 on-Disk 데이터베이스 구조를 갖추고 있으며, 오라클 미디어 복구를 사용해 업데이트 된다.
SQL apply : Logical Data Guard를 구성할 때 사용하는 방법으로 SQL statement를 사용해 업데이트된다.
Oracle Net을 통해서 primary DB의 변경정보를 standby DB로 적용시켜 운영
switchover : OS 작업 또는 서버 PM 작업 시 사용(primary와 standby의 역할을 바꿔가며 사용)
failover : 디스크 fail 등 긴급상황에서 사용, dataguard 재구성 필요 -> primary가 가동중 디스크의 손실이 일어난 경우 standby DB를 primary로 변경한 후 사용, 이 후 primary가 고쳐진다면 변경사항을 적용하고 다시 dataguard 설정을 한 후 사용 가능
Physical standby database : block 대 block 기반으로 primary DB의 redo log를 적용시켜 standby DB를 동기화
archiving 되면 archived redo logs가 되고 이것을 MRP(Managed Recovery Process)가 standby DB에 적용
Logical standby database : 같은 schema 정의로 공유, primary DB의 sql 문장을 standby DB에 적용
LSP(Logical standby process)가 standby DB에 적용
공개 읽기-쓰기라는 새로운 유연성을 제공
SQL Apply에 의해 유지되는 데이터를 변경할 수 없으면서 추가 로컬 테이블이 데이터베이스에 더해지고, 로컬 인덱스 구조를 생성해 리포팅을 최적화하거나 Standby DB를 데이터 웨어하우스로 활용하거나 데이터 마트를 로딩하는데 사용하는 정보를 처리할 수 있다.
protection mode(보호 모드)
Maximum Protection - primary DB와 standby DB의 redo log를 동기화 standby DB가 네트워크 이상 등의 이유로 standby로의 전송이 안될 경우 primary DB를 중단 데이터는 서로 동기화되어 primary DB에서 commit을 하게 되면 standby DB에서 commit이 완료될 때까지 primary DB에서 commit 완료를 하지 않는다. 성능에 문제를 줄 소지가 있으나 failover 상황이 오더라도 데이터 손실이 없다. Physical standby DB에서만 가능
Maximum availability - Maximum Protection과 마찬가지로 primary DB와 standby DB를 동기화시킨다. 단, standby DB가 네트워크 문제 등의 이유로 전송이 안될지라도 중단되지는 않는다. 데이터는 maximum protection과 마찬가지로 primary DB에서 commit을 하게 되면 standby DB에서 commit이 안료될 때까지 primary DB에서 commit을 완료하지 않는다. Physical standby, logical standby 모두 가능
Maximum Performance - default protect mode이다. primary data에 대한 protection이 가장 낮다. primary DB에 transaction이 수행되면 이것을 standby DB에 적용 시킬 때, 적용이 끝날 때까지 기다리지 않는다. standby DB의 문제로 인해서 primary DB에 성능영향이 가지 않는다. 그렇지만 failover시 약간의 데이터 손실을 가져올 수 있다.
Maximum Protection과 Maximum availability 모드에서는 primary DB에서 변경된 내용을 standby DB에 적용할 때, standby DB가 정상적으로 적용을 완료했다는 신호를 primary DB가 받아야지만 다음 작업을 수행하지만 Maximum Performance 모드에서는 standby DB의 신호를 받지않고 바로 다음 작업을 수행한다.
Active Data Guard
Physical standby DB를 open한 상태에서도 primary database에서 변경된 내용을 적용 가능
읽기 전용 접속 모드 - read only with apply
snapshot standby
물리적 스탠바이 데이터베이스로부터 생성되는 새로운 유형의 스탠바이 데이터베이스
읽기-쓰기가 지원되어 테스트와 다른용도를 위해 Primary Database와 독립적인 트랜잭션을 처리할 수 있음
physical standby database가 snapshot standby database로 변환된 것에 의해 생성된 모든 업데이트가 가능한 standby database
snapshot standby database는 primary database에서 redo data를 받고 archive 하지만 apply는 하지 않음
snapshot standby database에서 발생된 모든 local update를 버림 -> snapshot standby database는 physical standby database로 전환 -> primary database에서 받은 redo data 적용
Physical Backup : DB를 구성하는 File들을 그대로 복사하는 방법 DB가 손상시에 아무런 피해 없이 또는 최소한의 피해로 Database를 Recovery하는 방법
Offline Backup(Cold Backup) - Oracle이 Close(Shutdown)된 상태에서 OS의 COPY 명령어를 통해 복사하는 방법으로서, NoArchiveLog Mode, ArchiveLog Mode 둘 다에서 가능
Oracle이 Open중인 상태에서 OS의 COPY 명령어를 통해 복사하는 방법으로서, ArchiveLog Mode일 경우만 가능하며 DB를 24시간 운영하는 System에서 사용하는 백업 방법
Logical Backup - Export Utility $ORACLE_HOME/bin/exp 명령어를 이용하여 Backup하는 방식으로 DB의 논리적인 정보(Schema 구조, 데이터 등)를 저장하는 방식
Media Recovery - Disk나 매체등의 장애가 원인일 경우 Recovery
Physical Backup으로부터의 복구
complete Recovery - 장애 시점까지 Recovery하는 방법 변경된 정보를 저장하고 있는 Redo Log 파일들이 재사용되기전에 저장되어지는 Archive Log File이 필요하므로 Archive Log Mode에서만 가능 NoArchive Log Mode에서는 백업본 이후 적용할 Archive File이 존재하지 않는 경우 백업 받은 시점으로만 데이터베이스를 복구 가능 완전 복구
incomplete Recovery - Backup본을 Restore한 이후 변경된 작업이 들어있는 Archive Log File을 찾을 수 없을 때나, DB를 특정 시점으로 돌리는 방법 archive log file이 중간에 끊겨있을때 끊기기 바로 직전까지 복구 특정 archive log file 까지만 복구 가능 불완전 복구
Logical Backup으로부터의 복구
Import Utility - $ORACLE_HOME/bin/imp를 이용하여 데이터를 복구하는 방법 exp 명령어로 export한 DB의 데이터를 다른 DB에 적용하거나 복구할 때 사용가능
Instance Recovery - 비정상적인 종료(abort, 정전, CPU 고장, 메모리 손실 등과 같은 장애)에 의해 Oracle Instance가 Error를 일으켜 fail된 경우
SMON에 의해 자동으로 이루어짐
비정상적인 종료 후 비동기화 되어있는 상태에서 Database open
롤 포워드(Mount 단계에서 수행) : commit 되었는데 data file에 반영되지 않고 없어진 data를 복구(마지막 CKPT이후부터 redo log 재실행 -> DBWR가 데이터 파일에 적용)
데이터베이스 오픈
롤백 단계 : 위 롤 포워드 작업에서 redo log의 재실행 -> DBWR의 작동으로 commit하지 않았는데 data file에 반영된 data를 이전 값으로 되돌리는 작업
데이터베이스가 동기화되어 데이터베이스 운영 가능
User Error Recovery : 사용자의 실수로 인한 Transaction으로 인해 원하지 않는 결과가 발생한 경우(Table truncation 또는 Drop 에러) 다시 복원하는 방식 imp를 이용하는 경우가 대부분
기본적인 Backup 정책
정기적으로 COLD BACKUP을 받도록 함
database에 구조적인 변화가 생기기 전 반드시 COLD BACKUP을 받도록 함
database에 장애가 발생하지 않도록 운영
장애시에는 Recovery까지의 시간이 최소한이 되도록 백업 정책을 세움
기본적인 Backup 규칙
Log file을 Disk에 Archive한 후, 추후에 다른 disk나 tape 등에 다시 복사 - 저장 공간을 여러 위치에
data file의 backup은 실제 data file과는 다른 disk에 유지
control file은 다중화 하여 여러개를 유지
Log file이나 data file을 추가하거나 Rename 삭제 하는 등 database의 구조가 변경되었을 경우 반드시 control file 백업
NOARCHIVELOG MODE
데이터베이스를 설치하면 설정되는 기본모드
체크포인트가 발생한 후 즉시 리두 로그 파일을 재사용 할 수 있음
리두 로그가 겹쳐 쓰여지면서 변경정보가 없어지므로 마지막 전체 백업에 대해서만 복구가 가능
데이터베이스가 정상 종료되었을 때만 복구 가능한 백업본 생성이 가능
백업할때마다 전체 데이터파일 및 controlfile을 백업해야 함
NOARCHIVELOG MODE의 DB는 동기화되어 있으므로 반드시 온라인 로그 파일을 백업해야 하는 것은 아님
REDO LOG 파일이 겹쳐 써지기 때문에 마지막 전체 백업 이후의 모든 데이터가 손실됨
ARCHIVELOG MODE
다 쓰여진 리두 로그 파일은 Log Switch가 일어나기 전 체크포인트가 발생하고 ARCn 프로세스에 의해 리두로그 파일을 백업할 때까지(Archivelog file 생성) Redo Log File은 재사용될 수 없음
ARCHIVE LOG FILE은 Media 장애가 발생했을때 데이터가 손실되지 않도록 데이터베이스를 보호
ARCHIVE LOG MODE는 온라인 상태에서 데이터베이스를 백업할 수 있음(HOT BACKUP)
ARCHIVELOG 모드로 변경
PFILE을 이용하여 변경 -> 파라미터 파일에서 수정 / SPFILE을 ALTER SYSTEM SET ... SCOPE=SPFILE; 명령어로 변경 LOG_ARCHIVE_START=TRUE -> 10g 부턴 사용 안됨 LOG_ARCHIVE_DEST='아카이브 로그 파일 저장할 경로' LOG_ARCHIVE_FORMAT=%S.ARC %S : redo 로그 시퀀스 번호를 표시하여 자동으로 왼쪽이 0으로 채워져 파일 이름 길이를 일정하게 만든다. %s : redo 로그 시퀀스 번호를 표시하고, 파일 이름 길이를 일정하게 맞추지 않는다. %T : redo 스레드 넘버를 표시하며, 자동으로 왼쪽이 0으로 채워져 파일 이름 길이를 일정하게 만든다. %t : redo 스레드 넘버를 표시하며, 파일 이름 길이를 일정하게 맞추지 않는다.
데이터베이스 종료 - NORMAL, IMMEDIATE, TRANSACTIONAL
데이터베이스를 MOUNT 상태로 시작
ALTER DATABASE 명령을 사용하여 데이터베이스의 모드 변경 - ALTER DATABASE ARCHIVELOG;
데이터베이스를 OPEN
ARCHIVE LOG LIST; 로 확인
데이터베이스에 대한 전체 백업 수행 -> control file 정보가 변경되어 이전의 백업본을 사용할 수 없기 때문 모든 데이터 파일 및 컨트롤 파일을 백업
Closed 백업(=Cold 백업)
Closed 백업은 데이터베이스가 Shutdown된 상태에서 백업을 하는 방법을 의미
Archive Log Mode와 Noarchive Log Mode 둘 다 가능
모든 Data File, Control File, Redo Log File이 대상
정상적인 종료일 때만 가능 - normal, transactional, immediate
초기화 파라미터 파일은 변경되었을 경우에 백업
개념적으로 단순하여 백업 및 복구방법이 용이
Noarchive Log Mode일 경우에는 백업받은 시험 이후의 데이터는 보장하지 않으므로 장애가 발생하였을 경우는 변경된 사항을 수동으로 입력해 주어야 함
Open 백업(=Hot Backup)
데이터베이스가 운영중인 상태(Open 상태)에서 백업하는 방법
Data File을 Online Backup하고 있다면 이 시점에 Data File에 저장되어야 할 사항이 Redo Log File에 저장
만약 Noarchive Log Mode라면 Online Redo Log를 재사용하게 되므로 후에 Recovery가 불가능 할 수 있기 때문에 Oracle은 Noarchive Log Mode에서는 Online Backup을 불가능하도록 해 놓음
Archive Log Mode에서만 백업이 가능
테이블 스페이스의 모든 Data File 또는 하나의 Data File을 백업할 수 있음
alter tablespace system begin backup;
host copy (본 파일 위치) (백업 받을 위치)
alter tablespace system end backup;
control file의 백업은 따로 받아햐 함
Logical Backup
Export Utility를 이용하여 Database를 백업하는 것
테이블이 DROP 되었을 경우에 많이 사용되는 방법으로 백업 본 이후의 변경사항은 수동으로 입력
Table 모드 - 지정된 테이블만 export -> exp scott/tiger tables=(테이블1, 테이블2, ...) rows=y file=epp_dept.dmp
User 모드 - 해당 유저에 속하는 모든 객체들을 백업 -> exp system/manager owner=scott rows=y file=scott.dmp
Tablespace 모드 - 지정한 테이블 스페이스 내의 모든 객체를 백업 -> exp system/manager tablespaces=(users) file=ts.dmp
전체 데이터베이스 모드 - exp system/manager file=full.dmp full=y
buffer
데이터 행들을 가져오는데 사용되는 버퍼의 크기
file
생성되는 파일의 이름
rows
행을 포함하는지 여부(Y/N)
full
전체 데이터베이스를 export할지 여부(Y/N)
owner
export할 소유자(유저)의 이름
table
export할 테이블
tablespaces
export할 테이블스페이스 이름
log
로그 파일을 저장할 이름
Recovery = Restore + Archive 적용
Restore란 Database에 장애가 발생하기 이전에 Backup본을 이용하는 방법
Recovery란 백업본을 적용한 데이터베이스에 변경사항을 기록한 Archive Log File을 적용한 것
Complete Recovery - database에 장애가 발생하기 이전 시점까지 recovery하는 것을 의미하며 database를 archive log mode로 운영해야만 가능
spfile이 없으면 mount 불가능, control file이 없으면 open 불가능
v$datafile_header 뷰를 통해 현재의 에러 상황을 확인
system file에 문제가 생긴 경우
noarchive log mode
system 데이터 파일에 I/O가 발생하는 순간 DB가 종료되어 문제 발생
다시 startup 해도 control file에 있는 경로의 system file이 문제가 생겨서 open하지 못함
기존에 백업해둔 파일들을 모두 가져와서 복구한 후 백업 이후의 변경분을 수동으로 적용
archive log mode
위에서 모든 파일을 가져온 뒤 archive log를 적용하여 오류 직전의 db로 회복
system과 같은 open하는데 주요 data file을 제외하고 일반 데이터 파일에 이상이 생긴 경우는 그 데이터 파일만 offline 시킨 후 restore한 다음 백업 이후의 변경사항을 적용
alter database '이상 있는 데이터파일 경로' offline;
!cp (백업본 위치/이상 있는 데이터파일의 백업본) (원래 데이터파일 위치)
recover datafile '복구한 데이터 파일 위치/데이터 파일';
auto 입력
archive log file들에 이상이 없는 경우 완전 복구 완료
만약 archive log file이 중간에 끊긴 경우 recover database until cancel로 복구 후 데이터베이스를 open할 때 resetlogs로 open한다.
일정 시간대의 log까지만 적용하고 싶다면 set autorecovery on -> recover database until time '원하는-시간-입력'
backup control file을 이용한 불완전 복구
데이터파일 뿐만아니라 컨트롤파일도 백업되어 있어야 한다.
만약 archive 파일이 유실되었는데 데이터가 매우 중요한 것들이라서 복구를 해야만 한다면
alert 파일에서 데이터가 삭제된 시점을 확인하고 recover database until time '시간' using backup controlfile 적용
데이터베이스를 resetlogs를 이용하여 open
Logical Recovery
imp userid=system/비밀번호 file=full.dmp(exp한 파일) full=y -> 전체 데이터베이스를 import
보통 다른 유저의 객체(테이블, 뷰, 프로시저, 함수, 패키지, 시퀀스 등)를 참조할 때 많이 사용
실제로 SYNONYM을 이용하는 이유는 다른 유저의 객체를 사용할 때 유저의 이름과 객체의 실제이름을 사용하는데 그 두개를 감춤으로써 데이터베이스의 보안을 개선하기위해 사용
시노님으로 지정한 객체의 이름을 바꾸거나 이동할 경우 객체를 사용하는 SQL문을 모두 다시 고치는 것이 아니라 시노님만 다시 정의하면 되기 때문에 매우 편리
객체의 긴 이름을 사용하기 편한 짧은 이름으로 해서 SQL코딩을 단순화 시킬 수 있다.
시노님을 사용하는 유저는 참조하고 있는 객체들에 대한 소유자, 이름, 서버이름을 모르고 시노님 이름만 알아도 사용할 수 있다.
Private Synonym : 전용 시노님, 특정 사용자만 이용할 수 있다.
Public Synonym : 공용 시노님은 공용 사용자 그룹이 소유하며 그 데이터베이스에 있는 모든 사용자가 공유한다.
시퀀스
시퀀스 캐싱
시퀀스 캐시는 전통적인 캐시와 다른 '목표'이다.
시퀀스 캐시를 100으로 설정한다고 해서 100개의 번호를 생성, 저장하지 않는다.
시퀀스는 SGA 내에서 캐시된다.
다음 시퀀스 번호(NEXTVAL)가 저장된다.
시퀀스는 SEQ$에 1개 레코드로 정의 됨
순차적으로 증가하는 순번을 반환하는 데이터베이스 객체
start with : 시퀀스의 시작 값
increment by : 시퀀스의 증가 값을 지정
maxvalue : 시퀀스 최대값
minvalue : 시퀀스 최소값
cycleinocycle : 최대값 도달시 순환 여부
cache : cache 여부, 원하는 숫자만큼 미리 만들어 Shared Pool의 Library Cache에 상주
바인딩 쿼리
바인드 변수 - SQL 문장을 실행할 때 SQL에 사용자 값을 전달할 수 있는 통로 역할을 한다. 또한 오라클 성능에 큰 영향을 미치는 SQL 공유와도 큰 연관이 있다.
선언 방법
var[riable]을 사용한 선언 - 선언시 이름만 사용, 참조시 콜론 함께 사용 세션에서 전역적으로 선언됨, 블럭 내부에서 var를 사용해서 선언할 수 없음 값 할당시 exec를 사용, 함수 호출처럼 다루어짐 var a number; exec :a := 2; select :a from dual;
Toad, SQL Developer 등의 툴에서 선언
declare 내부에서 선언
프로그램 파라미터에서 선언
테이블스페이스
하나 또는 여러개의 데이터 파일로 구성되어 있는 논리적인 데이터 저장구조
데이터파일로 구성되어 있으며, 기본적인 테이블 스페이스는 각각의 역할을 갖고 생성된다.
시스템 테이블 스페이스와 비시스템 테이블스페이스로 구분
시스템 테이블 스페이스 - 오라클 데이터베이스를 생성할 때 자동으로 생기며 오라클 데이터베이스의 기동을 위해 꼭 필요한 테이블스페이스 - 모든 데이터 사전 정보와, 저장 프로시저, 패키지, 데이터베이스 트리거 등을 저장 - 유저데이터가 포함될 수 있지만 관리 효율성 면에서 포함시키면 안됨
비 시스템 테이블 스페이스 - 롤백세그먼트, 임시세그먼트, 응용프로그램 데이터, 응용프로그램 인덱스를 저장할 수 있음 - 공간관리를 쉽게 하기 위해서 생성 - 유저에게 할당되는 공간
SGA 안에는 shared pool(library cache + dictionary cache), database buffer cache, redo log buffer, large pool, stream pool, java pool, nk buffer, keep buffer, recycle buffer 등 버퍼 캐시 영역이 존재하고 DBWR, LGWR, PMON, SMON, CKPT 의 background process가 존재한다.
많은 사용자들이 한꺼번에 사용할 수 있는 대규모 공유 메모리 영역이다.
shared pool
shared pool에는 실행했었던 SQL문장과 그 실행계획이 저장되어있는 library cache와 열람했던 dictionary 정보들이 저장되어 있는 dictionary cache가 있다.
sql문을 실행하게 되면 가장먼저 parse 과정이 진행되는데 shared pool에 실행계획이 있는지를 먼저 확인하고 있으면 그 실행계획을 바로 사용하게 된다.
database buffer cache
database buffer cache는 데이터를 수정하거나 확인할 때 디스크에 access하는 시간보다 메모리 영역에 미리 올려두고 사용하는 것이 더 빠르기 때문에 사용하는 공간이다.
default한 data block의 크기가 8KB 이므로 database buffer cache에 한 번에 할당하는 공간의 크기도 8K이다.
그렇지만 nk buffer라고 해서 다른 크기의 블럭을 읽어들이기 위해 따로 공간을 할당해 놓을수도 있다.(2K, 4K, 16K, 32K 등)
free buffer : 데이터를 적재할 수 있는 공간을 의미한다. 작업이 진행중인 공간이나 작업이 끝났지만 아직 database에 적용이 되지 않은 공간은 free한 buffer라고 할 수 없다.
pin buffer : 아직 작업이 진행중인 공간을 말한다. 이 영역은 latch가 걸려있기 때문에 동일한 데이터를 수정해야 하는 경우 작업이 끝날때까지 대기해야한다.
dirty buffer : pin buffer에서 작업이 모두 끝났지만 database에 적용이 되지 않은 상태의 buffer를 말한다.
이 버퍼들은 LRU list에 의해 관리되는데 가장 최근에 사용된 버퍼가 왼쪽으로 옮겨지고 사용하지 않은 버퍼가 오른쪽으로 밀리는 형태로 관리된다.
만약 디스크에서 데이터를 새로 적재해야 하는 상황이 되면 가장 오른쪽부터 스캔하여 free한 buffer를 찾는다.
이때 LRU list를 스캔하면서 free를 발견하기 전의 dirty buffer들을 발견하게 된다면 LRUW list에 dirty buffer들의 위치를 저장한다.
1/3(약 40%)의 LRU list를 찾았음에도 free buffer가 발견되지 않았다면 DBWR에 의해 모든 dirty buffer들을 데이터베이스에 적용하는 작업을 수행하고 그때 생성되는 free buffer를 사용하게된다.
redo log buffer
redo log file을 저장하기 전에 메모리 영역에 로그를 기록하는 공간
많은 작업들이 수행되는 SGA영역에서 리두 로그 버퍼가 꽉차서 대기해야하는 상황이 오지 않도록 하기위해 1/3이 찼을 때, commit이 되었을 때, redo log 데이터가 1MB 이상일 때, 로그 파일 switch가 발생할 때 등 자주 redo log file에 기록한다.
DML 작업(update, insert, delete 등)을 수행할 경우 예기치 못한 상황으로 인스턴스가 종료되는 상황이 왔을때, commit을 한 직후까지 인스턴스를 회복시키기 위해서 사용
또한 archive log mode로 아카이브 로그 파일이 저장되는 경우 db의 오류로 백업본을 불러와 현재의 상태까지 회복하고자 할 때 사용되기도 함
large pool : 말 그대로 대규모의 메모리 할당을 제공하기 위해 설정하여 사용하는 공간, shared pool에서 다룰 수 없을 만큼 큰 메모리 조각 할당
stream pool : 데이터베이스를 복사하여 다른 데이터베이스로 옮기고자 할 때 buffer queue message를 위해 사용하는 공간
java pool : 자바 명령을 사용하는 경우 구문 분석을 위해 사용하는 메모리 공간
keep buffer : 자주 사용할 것 같은 데이터들을 세션이 끝날때까지 메모리에 상주시키기 위한 공간
recycle buffer : 자주 사용할 것 같지 않은 데이터들을 위한 버퍼 공간, 랜덤 액세스를 하는 큰 테이블에 사용되면 좋은 버퍼 풀
DBA와 DBE의 차이
엔지니어는 설치/이관/장애처리 등 데이터베이스의 기술적인 오퍼레이션만 수행하는 사람들을 DBE 라 하고, 관리가 들어가면 DBA가 됩니다. 관리라 하면 시스템 적인 관리 (데이터베이스와 관련 된 스토리지 및 장비 관리 등등)를 하면서 기업 전산실에 많은 수의 데이터베이스 시스템을 관리하는 경우도 있고, 개발자 분들이 잘 아시는 성능관리 (튜닝, 데이터베이스 디자인 등)을 하시는 경우도 있죠. 단적인 예를 들어서 DBMS의 백업/복구 방법을 아는 정도는 엔지니어도 할 수 있지만, 전산실의 DBMS들에 대한 백업/복구 정책을 새울 수 있으면 DBA라고 할 수 있다고 봅니다.
Alert log file
oracle 데이터베이스는 운영하면서 발생하는 모든 Event를 Alert 로그 파일에 기록
발생된 모든 내부에러, 블럭 훼손 에러, 데드락 에러
create/alter/drop database/tablespace, startup, shutdown, archive log, recover 등의 sql 문장을 사용한 관리 작업
공유 서버와 디스패처 프로세스의 기능과 관련된 에러와 메시지
구체화된 뷰의 자동 갱신 시 발생하는 에러
startup 시에 사용된 비 기본 초기화 파라미터들
관리 작업이 성공한다면, 메시지는 alert log에 시간과 completed 라는 메시지를 기록
시스템 관련 에러나 정보들을 보여주고 사용자 관련 에러는 저장되지 않음
그래서 DBA 또는 엔지니어는 데이터베이스가 운영되는 동안 항상 Alert 로그 파일을 주시, 분석하여 특별한 문제가 없는지 확인을 하기도 한다.
$ORACLE_BASE/diag/rdbms/데이터베이스 이름/오라클 sid 이름/trace -> show parameter background_dump_dest v$parameter 나 v$diag_info 에서도 찾을 수 있음
alert log 파일은 지워지더라도 alert log가 입력될 시점에 자동으로 새로 생성하고 기록한다.
