Oracle DB 구조 및 트랜잭션 관리 이경화

Database 의 구조 Program Global Area (PGA) Instance Database Buffer Cache Redo Log Buffer Library Cache Shared Pool Java Pool Large Pool Row Cache Database Data Files Control Files Redo Log Files LMS PMON SMON DBWRLGWR CKPT ARCHSNPn RECOPnnn SnnnDnnn Parse Execute Fetch Shared Global Area(SGA) Server Process User Process Background Process Application Program SQL*Plus Parameter Files Backup Files Archive log Files password Files Alert log Files Trace Files Offline Storage Device Streams Pool

SGA(Shared Global Area) 3 Database 에서 일어난 모든 변화를 저장하는 메모리 공간 Redo Log Buffer 에 저장된 항목들은 LGWR 프로세스에 의해 Online Redo Log File 에 저장된다. 하나의 데이터베이스에 행해지는 모든 SQL 문을 처리하기 위하여 사용 문장과 관련된 실행계획과 구문분석 정보가 들어 있다. Library Cache + Dictionary Cache + Control Structures Shared Pool Option 성격의 메모리이며 Oracle 백업 및 복원작업에 대한 대용량 메모리 할당 시에 사용한다. Shared Server 기능을 사용하지 않는다면 최소로 설정 Large Pool Oracle JVM 에 접속해 있는 모든 세션에서 자바코드가 사용하는 메모리 영역 Java Pool 데이터파일로부터 읽어드린 데이터 블록 내용을 저장하는 영역 LRU 알고리즘으로 운영된다. Database Buffer Cache Redo Log Buffer SELECT * FROM V$SGAINFO

SGA(Shared Global Area) 4 SQL Database Buffer Cache Library Cache Shared Pool Data Dictionary Cache Data Files DBWR Shared Global Area(SGA) Database Buffer Cache Server Process User Process Data Files DATA Database

Background Process 5 Instance Database Buffer Cache Redo Log Buffer Library Cache Shared Pool Java Pool Large Pool Data Dictionary Cache Database Data Files Control Files Redo Log Files PMONSMONCKPT Shared Global Area(SGA) Streams Pool DBWRLGWR LCKn ARCH SNPn RECO Pnnn Snnn Dnnn Archive log Files DBWn

Background Process 6 LGWR DBWR CKPT PMON SMON Shared Global Area Data block buffer cache Dictionary Cache Data File Write Redo Log Buffer 의 내용을 Online Redo Log File 에 기록 변경된 모든 block 을 data file 에 쓰도록 유도 체크포인트 기록 발생 시 SGA buffer cache 에 있는 모든 dirty buffer 를 disk 에 기록한다. Online Redo Log File 이 채워지면 자동적으로 실행 System 감시 Instance 복구 Temporary Segment 제거 프로세서 감시 Fail User Process 정리 Lock 해제 Checkpoint Dirty buffer threshold reached No free buffers Timeout RAC ping request Tablespace offline Tablespace read only Table DROP or Truncate Tablespace begin backup At commit When one-third full When there is 1MB of redo Every three seconds Before DBWR Writes

Background Process 7 SNPn Snnn Pnnn Dnnn Table Snapshot 의 데이터를 자동으로 갱신하는 역할 Snapshot Process MTS 구성에서 하나의 공유서버가 여러 개의 사용자 프로세스 요구를 처리 RAC 환경에서 병렬 질의, 인덱스, 데이터 로딩 등의 기능 제공 Shared Server 환경에서 Dispatcher Process 여러 개의 사용자 프로세스 사이에서 사용자의 요구를 서버에 전달하고 응답하는 역할 ARCH LCKn Redo Log File 에 기록된 내용이 덮어쓰여 지기 전에 디스크에 파일로서 기록 Log Switch 이벤트 발생하면서 프로세스 기동 RAC(Real Application Cluster) 환경에서 내부 Instance LOCK 을 위해 사용 RECO Recovery Process 분산 데이터베이스에서 일시 정지 되어 있거나 실패한 트랜잭션 처리 DBWn Database Buffer Cache 가 가득 차 있는 상태에서 Check Point 를 수행하기 위해 Buffer Cache 에 공간을 만드는 역할 담당

물리적인 Database 구조 8 사용자 프로세스가 빈버퍼를 찾지 못하고 버퍼의 임계점에 도달하면 프로세스는 LRU 목록 검색을 중지하고 DBWR 에 신호를 보내 Dirty Buffer 를 Data File 에 기록한다. 모든 Oracle Database 는 하나 이상의 Datafile 을 가지며 DB 의 영역이 부족할 때 자동으로 확장할 수 있는 기능이 있다. Data FileControl File INSTANCE Data File 과 Redo Log File 의 위치 DB 생성시간 Instance 가 시작될 때마다 Database 와 Redo Log File 지정 일반사용자가 수정 할 수 없는 binary file Database header 정보 현재 상태 저장 Redo Log File Redo Log Buffer 의 내용을 기록 System failure 에 대한 복구수단 제공 Cycle 방식으로 사용됨 File 이 다 차면 Archive 됨 Parameter FilesBackup Files password Files Alert log Files Trace Files Archive Log Files Database name and identifier Time stamp of database creation Tablespace names Names and location of data file and redo log files Current redo log file sequence number Checkpoint information Begin and end of undo segments Redo log archive information Backup information Called a log switch Checkpoint operation also occurs Information written to the control file

9 물리적인 Database 구조 Data File - 사용자 데이터 및 시스템 데이터 ( 딕셔너리 ) 가 기록되는 파일 - 최소 하나 이상의 파일을 사용하며, 용도 및 데이터 성격에 따라 여러 개의 파일로 구분하여 사용. Redo Log File - 데이터베이스의 변경 내용 ( 트랜잭션 정보 ) 이 기록되는 파일 - 최소 2 개의 그룹을 구성하여 운영 Control File - 데이터베이스 구성 파일들에 대한 정보 파일 - 데이터베이스 명, 동기화 정보 등을 기록

물리적인 Database 구조 10 File 설명 Parameter Files 기본적으로 Oracle 을 설치하면 INIT.ORA 라는 파일 이름으로 존재 컨트롤 파일의 경로, 데이터베이스의 환경설정 등 모든 정보를 포함하고 있다. 이 파일이 없으면 데이터베이스가 구동되지 않습니다. Backup Files 데이터베이스 복구를 위해 사용함. Alert log Files Oracle Instance 가 동작할 때 발생하는 여러 Event 나 Error 가 기록된다. Trace Files Oracle Server 는 Background Process 에 대한 오류를 Trace File 에 남긴다. 이 Trace File 을 통해 진단과 문제를 해결할 수 있다. Archive log file Redo log file 에서 로그를 덮어 쓰기 전에 파일로 보관한다. Password file 관리자로서 활동을 수행하는 사용자들을 인증하는데 사용됩니다. Parameter Files Backup Files password Files Alert log Files Trace Files Archive log Files

11 데이터베이스 시작과 종료 SHUTDOWN OPEN MOUNT NOMOUNT STARTUP SHUTDOWN Instance create Control file open Data files and Log files open Init file open SQL> startup ORACLE instance started. Total System Global Area bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes Database opened. Database mounted. ALTER DATABASE MOUNT; ALTER DATABASE OPEN; STARTUP NOMOUNT

12 데이터 파일 데이터의 물리적 저장소 하나 이상의 물리적 블록 (OS 블록 ) 으로 구성 a.dbfb.dbf c.dbf d.dbf

13 테이블스페이스 데이터의 논리적 저장소 하나 이상의 물리적 파일들로 구성 SALES T.SSystem T.SPRODUCT T.S a.dbfb.dbf c.dbf d.dbf

14 세그먼트 저장영역을 필요로 하는 오브젝트 예 ) 테이블, 인덱스 SALES T.SSystem T.SPRODUCT T.S a.dbfb.dbf c.dbf d.dbf

15 익스텐트 세그먼트에 저장영역을 할당하는 단위 하나 이상의 연속된 데이터 블록으로 구성 SALES T.SSystem T.SPRODUCT T.S a.dbfb.dbf c.dbf d.dbf

16 데이터 블록 데이터를 저장하고 관리하기 위한 논리적 최소 단위 오라클의 최소 I/O 단위 SALES T.SSystem T.SPRODUCT T.S a.dbfb.dbf c.dbf d.dbf

저장 구조 17 데이터베이스 테이블스페이스 새그먼트 익스텐트 오라클 데이터 블록 데이터파일 운영체제 블록 물리적 저장구조 논리적 저장구조와 물리적 저장구조 PhysicalLogical

18 저장 영역 설정 옵션 CATEATE TABLE emp ( empno NUMBER(4) …, ………… ) TABLESPACE AAAAA STORAGE ( INITIAL 200K NEXT 200K PCTINCREASE 50 MINEXTENTS 5 MAXEXTENTS 20 FREELISTS 2 FREELIST GROUPS 2 ) PCTFREE 20 PCTUSED 50 INITRANS 5 MAXTRANS 10; 18

주요 시스템 테이블스페이스  시스템 테이블스페이스. 데이터딕셔너리 저장 데이터베이스 생성시 생성  임시 테이블스페이스 정렬 작업을 위한 임시 데이터 저장  언두 ( 롤백 ) 테이블스페이스 트랜잭션을 위한 언두 정보 저장 19

20 사용자 테이블스페이스 생성 CREATE TABLESPACE product DATAFILE ‘ / …./p1.dbf ’ SIZE 1000M; System T.S SALES T.S s1.dbfs2.dbf PRODUCT T.S p1.dbf s.dbf Temp T.S t.dbf RBS T.S r.dbf CREATE TABLESPACE sales DATAFILE ‘ / …./s1.dbf ’ SIZE 500M, ‘ / …./s2.dbf ’ SIZE 500M DEFAULT STORAGE( INITIAL 200K NEXT 200K PCTINCREASE 50 MINEXTENTS 5 MAXEXTENTS 20 FREELISTS 2 FREELIST GROUPS 2 );

Transaction 트랜잭션은 하나이상의 SQL 명령문을 포 함하는 작업의 가장 작은 논리적 단위 – 처리 과정 중 오류, 특정 상황 때에 복구기능을 할 수 있도록 해줌 트랜잭션에 있는 모든 SQL 명령문은 항상 COMMIT 나 ROLLBACK 되어야 함 SET AUTOCOMMIT [on | off]

Commit, Rollback, Savepoint Commit – 트랜잭션이 발생하는 경우 데이터들이 물리적 으로 영구히 저장되는 것을 의미 Rollback – 수행했던 데이터베이스의 변경을 모두 무효화 –Savepoint 를 지정할 경우 그 특정 Savepoint 로 복구 Rollback to …;

Auto Commit Commit 이 자동으로 일어나는 경우 –DDL –Create, Alter, Drop, Rename, Truncate 등을 사용 Commit 이 자동으로 일어나지 않는 경우 –DML –Insert, Delete, Update 를 사용할 경우

Transaction Transaction 구동 –Oracle 의 Transaction 은 첫 번째 실행 가능한 SQL 명령문을 만나면 시작 – 실행 가능한 SQL 명령문은 DML 과 DDL 명령 문을 포함하여 인스턴스에 대한 호출을 생성하 는 SQL 명령문을 말함 –Transaction 이 시작되면 오라클은 사용할 수 있는 Rollback Segment 에 Transaction 을 할 당하여 새로운 Transaction 을 위한 Rollback 입력 항목을 기록

Transaction(Cont) COMMIT 되기 전 – 오라클은 SGA 의 Rollback Segment Buffer 에 Rollback Record 를 생성. Rollback 정보에는 Transaction 의 SQL 명령문에 의해 변경된 이전 데이터 값이 들어 있음. – 오라클은 SGA 의 Redo Log Buffer 에 Redo Log 입력 항목을 생성. 변경 내용은 Transaction 이 Commit 되기 전에 디스크에 기록 –SGA 의 데이터베이스 버퍼에도 변경 작업이 일어 남. 이러한 변경 내용은 Transaction 이 실제로 Commit 되기 전에 디스크에 기록

Transaction(Cont) COMMIT 된 후 – 관련된 Rollback Segment 에 대한 내부 Transaction Table 은 Transaction 이 Commit 되 었다는 사실을 기록하고 Transaction 에 대응되는 고유한 시스템 변경 번호가 할당되며 테이블에 기 록 –LGWR 은 SGA 의 Redo Log Buffer 에 있는 Redo Log 입력 항목을 온라인 Redo Log File 에 기록. 또한 LGWR 은 Transaction 의 SCN 을 온라인 Redo Log File 에 기록 – 행과 테이블에 적용되는 Lock 을 해제. Transaction 을 “Complete” 로 표시

Transaction(Cont) 전체 Transaction Rollback – 오라클은 해당 Rollback Segment 를 사용하여 Transaction 에 있는 모든 SQL 명령문에 의해 변경된 내용을 모두 취소 – 오라클은 데이터에 대한 Transaction 의 모든 잠금을 해제 –Transaction 을 종료

Transaction(Cont) Savepoint 지정 – 중간 표시자로 Transaction 의 문맥 내에 선언 –Savepoint 를 사용한 긴 Transaction 을 작은 부분 구분 – 사용자는 Transaction 에서의 현 시점 이전과 Transaction 내에서 선언된 Savepoint 이후에 수행된 작업을 Rollback 할 수 있음. – 복잡하고 긴 일련의 갱신 작업에서 Savepoint 를 사용하면 오류가 발생했을 때 모든 명령문 을 재실행 할 필요가 없음

Transaction(Cont) Savepoint Rollback – 오라클은 Savepoint 이후에 실행된 명령문만 을 Rollback – 지정된 Savepoint 는 보존되지만 지정된 Savepoint 이후에 설정된 모든 Savepoint 는 손실 –Oracle 은 Savepoint 이후에 획득한 모든 Table 과 Row 에 대한 Lock 을 해제하지만 Savepoint 이전에 획득한 모든 Table Lock 은 유지 –Transaction 은 활성화된 상태로 유지되며 계 속 실행될 수 있음

Savepoint 예 INSERT INTO AA VALUES(1); INSERT INTO AA VALUES(2); INSERT INTO AA VALUES(3); SAVEPOINT SP1; DELETE FROM AA WHERE SEQ=3; COMMIT; (1) ?? DELETE FROM AA WHERE SEQ=2; SAVEPOINT SP2; 30 ROLLBACK TO SP1; ROLLBACK TO SP2; //3 건의 데이터 조회 (1,2,3) (1) 위치 삽입할 경우, ROLLBACK TO SP2 해도 삭제된 SEQ=3 데이터는 복원되지 않음