'오라클/관리 실무' 카테고리의 글 목록

오라클/관리 실무

DBMS_JOB & DBMS_SCHEDULER 2014.10.27
사용자 관리 2014.10.27
Oracle 저장구조 2014.10.23
Tablespace 와 Data File 관리하기 2014.10.21 2
Redo Log 관리하기 2014.10.20
Control File 관리하기 2014.10.20
ORACLE 시작하기 & 종료하기 2014.10.20
Oracle Background Process 2014.10.17
SQL 문장의 실행 원리 2014.10.17
Oracle Architecture 2014.10.17 2

DBMS_JOB & DBMS_SCHEDULER

김포춘 2014. 10. 27. 16:40

2014. 10. 27. 16:40

DBMS_JOB & DBMS_SCHEDULER

= 정해진 시간에 반복적으로 작업을 수행할수 있게 해주는 기능

1. DBMS_JOB

- SNP 백그라운드 프로세스가 이 패키지에 설정되어 있는 작업을 수행

패키지

job_queue_process = 1 : SNP 프로세스의 개수를 지정
job_queue_interval = 60 : SNP 프로세스가 Sleep 상태에서 깨어나는 간격을 초로 지정

패키지에 있는 프로시저

submit : 새로운 작업을 job quere 목록에 등록
remove : job queue 에 등록된 job 을 제거
change : job queue 에 등록된 job 을 변경
next_date : job queue 에 등록된 job 의 작동시간을 변경
interval : job queue 에 등록된 job 의 수행 주기를 변경
what : 수행할 procedure or package 변경
run : 등록되어 있는 특정 job 을 수동으로 수행

1) job 관리하기

- 기본 문법

DBMS_JOB.submit (

job out binary_integer,

what in varchar2,

next_date in date default sysdate,

interval in varchar2 default 'null' ,

no_parse in boolean default false )

주요 파라미터 의미

job : job 번호로 다른 프로시저에서 호출될 수 있음
what : 수행할 pl/sql or procedure or package 이름을 지정, 직접 수행하기를 원하는 sql문 적어도 됨
next_date : 다음에 수행될 시간을 지정
interval : 수행되는 주기를 지정, 초단위까지 지정가능
no_parse : parse 여부를 지정, 기본값은 false 로 parse를 수행

실습 1. 새로운 job 등록 테스트하기

job_test01 이라는 테이블을 만든 후 1분에 1번씩 해당 테이블에 데이터를 insert 하는 작업

step 1. 먼저 테스트용 테이블과 sequence , procedure 생성

SCOTT> create sequence seq_job_seq1 ;

Sequence created

SCOTT> create table job_test01

2 ( no number ,

3 name varchar2(5)) ;

Table created

SCOTT> create or replace procedure insert_job_test01

2 is

3 begin

4 insert into scott.job_test01

5 values(seq_job_seq1.nextval , dbms_random.string('a',3)) ;

6 end ;

7 /

Procedure created

step 2. job을 등록할 프로시저를 생성

SCOTT> !vi job1.sql

1 begin

2 dbms_job.submit (:jno ,

3 'scott.insert_job_test01;',

4 sysdate ,

5 'sysdate +1/24/60' , <--1 분에 한번씩 실행

6 false) ;

7 end;

8 /

※ 위 5행의 시간 설정 방법

- 10분에 한번씩 실행하도록 지정

sysdate +1/24/6 or sysdate +1/144

일/시간/분

1440 / 24 / 6 or 1440 / 144 분단위로 계산

- 1분에 한번씩 지정

sysdate +1/24/60 or sysdate +1/1440

- 5분에 한번씩 지정

sysdate +5/24/60

- 매일 새벽 2시로 지정

trunc (sysdate) +2/24 -> 다음날 새벽 2시를 지정

( trunc 함수를 지정하는 이유는 분:초 단위까지 나오지 않게 하기 위해서 사용 )

step 3. job 등록

SCOTT> variable jno number ;

SCOTT> @job1.sql

PL/SQL procedure sucessfully completed.

SCOTT/print jno ;

JNO

------------

25 <-- 현재 할당된 JOB 번호 확인, 이 번호는 random으로 지정됨

SCOTT> commit ; <-- 이 순간부터 job이 수행됨, 즉 commit 이 안되면 job 수행 안됨

RAC 환경에서 특정 노드에 job 을 할당해야 할 경우

SCOTT> exec dbms_job.instance(job=>25, instance=>1) ;

step 4. 수행되고 있는 job 내역 확인

SCOTT> select what, job, next_date, next_sec, failures, broken

2 from user_jobs

3 where what = 'scott.insert_job_test01 ;' ;

step 5. 실제 테이블에 데이터가 입력되는지 확인

SCOTT> SELECT * FROM job_test01 ;

실습을 제대로 따라 왔으면 데이터가 1분에 하나씩 늘어나는것을 확인 할 수 있음

실습 2. 등록되어 있는 job 삭제

SCOTT> exec dbms_job.remove(25) ; <-- 숫자부분에는 job 번호를 기입

SCOTT> select what, job, next_date, next_sec, failures, broken

2 from user_jobs

3 where job=25 ;

-> 사라져서 안나옴

실습 3. 등록되어 있는 job 수정

테스트를 위해 다시 job 등록

SCOTT> variable jno number ;

SCOTT> @job1.sql

SCOTT> print jno ;

JNO

-----------

26 <-- job no 가 26으로 변경되있음

SCOTT> commit ;

SCOTT> select what, job, next_date, next_sec, interval

2 from user_jobs

3 where job=26 ;

WHAT JOB NEXT_DATE NEXT_SEC INTERVAL

------------ ---- ------------- ---------- ------------------

scott.insert_job 4 15-OCT-14 21:51:30 sysdate +1/24/60

수행시간을 1분마다 -> 5분마다로 변경

SCOTT> exec dbms_job.change(:jno, 'scott.insert_job_test01;', sysdate, 'sysdate +5/24/60') ;

SCOTT> select what, job, next_date, next_sec, interval

2 from user_jobs

3 where job=26 ;

WHAT JOB NEXT_DATE NEXT_SEC INTERVAL

------------ ---- ------------- ---------- ------------------

scott.insert_job 4 15-OCT-14 21:51:30 sysdate +5/24/60

2. DBMS_SCHEDULER ( 10g이상에서만 사용가능 )

dbms_job 패키지보다 확장된 기능으로 dbms_job 에서 안되는 외부 스크립트를 실행 할 수 있음

(1) 주요특징

- 오라클에서 생성된 프로시저나 함수 외에 os에서 생성된 각종 유틸이나 프로그램까지도 실행가능

- 설정할 때 일반적으로 사용하는 자연어를 사용하여 편하게 설정 가능

- db 내부이벤트까지 추적가능하기 때문에 os나 dbms_job 보다 훨씬 다양하게 작업을 체크하고 수행가능

(2) 구성

- program : 이 부분에 dbms_scheduler가 수행할 프로그램이나 프로시저, 스크립트 등에 대한 정보를 저장해서 별도로 생성할 수 있음

- schedule : 이 부분에 dbms_scheduler 가 수행할 job 이 실제 수행할 시간이나 실행 주기등을 별도로 생성 할 수 있음

- job : 새로 생성할 job 부분을 정의

(3) dbms_scheduler 사용하기

실습 1. 신규 job 생성하기

job_tetst1 이라는 테이블에 3초에 1회씩 데이터를 insert 하는 작업을 설정

이 작업을 하기 위해선 해당 계정이 create any job 권한을 가지고 있어야 함

scott 계정에 권한 부여

SYS> grant create any job to scott ;

SYS> conn scott/tiger ;

SCOTT> create table job_test1

2 ( no number ,

3 name varchar2(5) ,

4 rdate date default sysdate ) ;

SCOTT> create sequence seq_job_test1_no ;

SCOTT> create or replace procedure insert_job_test

2 is

3 begin

4 insert into scott.job_test1 (no , name)

5 values(seq_job_test1_no.nextval, dbms_random.string('a',2)) ;

6 commit ;

7 end;

8 /

SCOTT> begin

2 dbms_scheduler.create_job( <-- 신규 JOB을 생성

3 job_name => 'insert_job_test1' , <-- dbms_scheduler 내에서 사용될 job 이름지정

4 job_type => 'plsql_block' , <-- 5번줄에 적은 프로그램의 타입을 적음

5 job_action => 'begin insert_job_test; end;' , <-- 실제 실행될 프로그램을 적는 부분

6 start_date => systimestamp , <-- 해당 job 이 처음 시작될 시간을 지정

7 repeat_interval => 'freq=secondly; interval=3' ); <-- 반복할 주기를 지정

8 end;

9 /

※ 5번줄에는 1) 실제 PL/SQL 블록

2) 프로시저 이름

3) OS에 있는 실행파일

4) program_name 으로 미리생성해둔 프로그램 이름

5) chained 값으로 생성된 체인

위 5가지가 들어갈 수 있음

※ 4번줄은 위에 pl/sql 형태이므로 PLSQL_BLOCK 로 기입

5번줄이 프로시저 이름을 적었다면 4번줄에는 STORED_PROCEDURE로,

실행프로그램을 적었다면 EXECUTABLE 로 적어야 함

※ 7줄의 주기 설정

repeat_interval => 'freq=hourly ; interval=1' <- 1시간 간격으로 수행하도록 설정

repeat_interval => 'freq=minutely ; interval=30' <- 30분 간격으로 수행하도록 설정

repeat_interval => 'freq=secondly ; interval=5' <- 5초 간격으로 수행하도록 설정

repeat_interval => 'freq=weekly ; interval=2' <- 2주 간격으로 수행

repeat_interval => 'freq=hourly ; interval=1' <- 매달 수행하도록 설정

repeat_interval => 'freq=hourly ; interval=1' <- 매년 수행하도록 설정

SCOTT> exec dbms_scheduler.enable('insert_job_test1') ;

SCOTT> exec dbms_scheduler.run_job('insert_job_test1') ;

SCOTT> alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS' ;

SCOTT> select * from job_test1 order by 3 ;

-> 확인해보면 3초마다 insert 되는것을 볼 수 있음

실습 2. 현재 작동중인 job 확인

SYS> select p.job_name, p.job_type, o.object_id, p.enabled, o.last_ddl_time, o.created,

2 cast(p.next_run_date as date) next_run_date, p.state, p.job_class, o.owner,

3 schedule_type

4 from dba_objects o , dba_scheduler_jobs p

5 where o.owner=p.owner

6 and o.object_name=p.job_name

7 and o.object_type='JOB'

8 and o.owner='SCOTT'

실습 3. 특정 job을 일시중지 또는 재시작 하기

SCOTT> exec dbms_scheduler.disable ('insert_job_test1') ; <-- 일시중지

SCOTT> exec dbms_scheduler.enable ('insert_job_test1') ; <-- 다시시작

실습 4. 특정 job 을 삭제

SCOTT> begin

2 dbms_scheduler.drrop_job('insert_job_test1') ;

3 end ;

4 /

실습 5. 외부 스크립트를 생성해서 실행

외부에서 생성된 스크립트를 오라클에서 실행하기 위해서는 해당 스크립트를 실행할 권한과 그룹이 지정되어 있는 externaljob.ora 라는 파일을 수정해야 함

step1. externaljob.ora 파일의 내용을 수정

SYS> !vi /app/oracle/product/11g/rdbms/admin/externaljob.ora

마지막줄의 내용을 아래와 같이 변경

#run_user = nobody -- 기존내용 주석처리

#run_group = nobody -- 기존 내용 주석처리

run_user = oracle

run_group = oinstall

oracle 계정의 소속 그룹을 모를 경우

->$ id oracle 조회

step2. 수정 완료 후 위 파일의 권한을 640 상태로 설정 ( 이 값이 기본적으로 설정되어 있음 )

[oracle@localhost ~] $ls- l $ORACLE_HOME /rdbms/admin/externaljob.ora

step3. 외부 스크립트를 실제로 수행하는 명령인 extjob 파일의 소유자를 변경

extjob 파일의 소유자 root -> oracle 로 변경

[oracle$localhost ~]$ ls -l $ORACLE_HOME/bin/extjob

[root$localhost ~]# chown oracle extjob

[root$localhost ~]# ls -l extjob

이 작업을 하지않을 경우 permission denied 라는 에러가 발생

저작자표시 변경금지

'오라클 > 관리 실무' 카테고리의 다른 글

사용자 관리 (0)	2014.10.27
Oracle 저장구조 (0)	2014.10.23
Tablespace 와 Data File 관리하기 (2)	2014.10.21
Redo Log 관리하기 (0)	2014.10.20
Control File 관리하기 (0)	2014.10.20
ORACLE 시작하기 & 종료하기 (0)	2014.10.20
Oracle Background Process (0)	2014.10.17

사용자 관리

김포춘 2014. 10. 27. 13:48

2014. 10. 27. 13:48

사용자 - 관리자 : sys , system DB처리, 장애처리 용도로 사용

- 사용자 : scott 등.. 작업을 수행하는 용도로 사용

새로운 사용자를 생성하는 순서

1) 생성할 사용자의 default tablespace 를 결정하고 해당 tablespace 를 생성

2) 생성할 사용자의 temporary tablespace 를 결정하고 해당 tablespace 를 생성

3) 사용자를 생성

4) 적절한 프로파일과 권한 , 롤 등을 생성한 후 할당

실습 1. 사용자 생성하기

웹하드 서비스를 위한 계정 생성

default tablespace 로 ts_webhard 라는 tablespace를 생성 ( 웹하드 관련 테이블은 모두 이곳에 저장 )

ts_web_idx tablespace 에 웹하드 관련 인덱스 저장

계정 명을 webuser , 암호는 webpwd

권한은 일반적인 사용자 권한 전부 부여

정렬 작업등을 위한 temporary tablespace 는 temp_web 으로 결정

실습시작

(1) webuser 의 default tablespace 생성

sqlplus / as sysdba

SYS> create tablespace ts_webhard

2 datafile '/app/oracle/oradata/testdb/ts_webhard01.dbf' size 100M ;

SYS> create tablespace ts_web_idx

2 datafile '/app/oracle/oradata/testdb/ts_web_idx' size 10M ;

SYS> select tablespace_name , bytes /1024/1024 MB , file_name

2 from dab_data_files ; <--생성 확인

(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 <-- default tablespace 를 지정하는 부분

4 temporary tablespace temp_web <-- temporary tablespace 를 지정하는 부분

5 quota unlimited on ts_webhard <-- 할당량을 지정하는데 무제한으로 설정

6 quota 0m on system ; <-- system tablespace 를 사용못하게 설정

(4) 권한 설정

SYS> grant resource , connect to webuser ;

SYS> conn webuser/webpwd <-- 접속 되는지 확인

사용자 정보 확인하기

SYS> select username , default_tablespace "Default TS" , temporary_tablespace "Temp TS"

2 from dba_users

3 where username='WEBUSER" ;

Profile 관리하기

Failed_login_attempts : 설정된 횟수만큼 login 을 시도한 후 실패 시 계정 잠금
Password_lock_time : 위 1번에서 계정이 잠기면 며칠 동안 잠글 것인지 기간을 정하는 파라미터
Password_life_time : 동일한 암호를 며칠간 사용할 것인지 설정하는 파라미터
Password_grace_time : 위 3번항목에서 만료되어도 이 파라미터에서 지정된 값 만큼 암호를 변경할 기간을 허용
Password_reuse_time : 동일한 암호를 다시 사용할 수 없도록 설정하는 기간, 같은 암호 사용 못하게 할때 쓰는 파라미터
Password_reuse_max : 동일한 암호를 위 5번 설정을 피해 재사용할 경우 최대 사용 가능한 횟수를 지정하는 파라미터
Password_verify_function : 암호를 복잡하게 만들기 위해 특정함수를 적용시켜 사용자가 입력한 암호가 암호로 적합한지 점검

- verify_function 함수 사용시 주의사항

암호는 최소 4글자 이상
암호는 사용자 계정과 달라야함
암호는 하나의 특수문자나 , 알파벳, 숫자가 포함되어야 함
암호는 이전 암호와 3글자 이상 달라야 함

실습 2. Password 관련 profile 생성

- 조건 1 : 로그인 시도 5회 실패시 계정을 5일동안 사용 못하게

- 조건 2 : 계정의 암호는 10일에 한 번씩 변경하게 할 것

- 조건 3 : 동일한 암호는 10일 동안 사용 못하게 할 것

SYS> create profile sample_prof limit

2 failed_login_attempts 5 <-- 로그인 실패 제한 5회

3 password_lock_time 5 <-- 로그인 실패시 5일동안 사용 제한

4 password_life_time 10 <-- 계정 암호 10일에 한번씩 변경

5 password_reuse_time 10 ; <-- 동일 암호 10일동안 사용 못함

Resource profile 관련 파라미터

CPU_PER_SESSION : SESSION 이란 1명의 접속을 의미, 하나의 SESSION 이 CPU를 연속적으로 사용할 수 있는 최대시간을 설정
SESSIONS_PER_USER : 하나의 사용자 계정으로 몇 명의 사용자가 동시에 접속할 수 있는지 설정하는 파라미터
CONNECT_TIME : 하루동안 DB Server 에 접속할 수 있는 총 시간
IDLE_TIME : 연속적으로 휴면시간이 이 값을 넘으면 접속을 해제, 접속 해제시 commit 안된 작업은 전부 rollback 됨
LOGICAL_READS_PER_SESSION : 한 session 에서 사용 가능한 최대 block수 지정
PRIVATE_SGA : MTS / shared server 일 경우 해당 session 의 SGA 사용량을 bytes 단위로 설정
CPU_PER_CALL : 하나의 call 당 cpu 를 점유할 수 있는 시간 1/100초 단위
LOGICAL_READS_PER_CALL : 하나의 call당 읽을 수 있는 block 의 개수 지정

실습 3. Resource 관련 profile 만들기

- 조건 1 : 1명당 연속적으로 cpu를 사용할 수 있는 시간 10초 제한

- 조건 2 : 하루 중 8시간만 db에 접속 가능하게 할 것

- 조건 3 : 10분 동안 사용하지 않으면 강제로 접속 끊음

SYS> create profile re_sample_prof limit

2 cpu_per_session 1000 <-- 1명당 사용할수 있는시간 10초 제한 1/100초 단위임

3 connect_time 480 <-- 60*8 8시간만 db에 접속 가능하게함

4 idle_time 10 ; <-- 10분 사용제한

사용자에게 profile 할당하기

1. 현재 사용자가 적용받고 있는 profile 확인하기

SYS> select username, profile

2 from dba_users

3 where username= "WEBUSER" ;

USERNAME PROFILE

---------- ---------

WERBUSER DEFAULT <--현재 아무profile 도 없음

2. 해당 profile 에 어떤 내용이 있는지 확인

- 위에서 만든 sample_prof 내용 조회

SYS> select * from dba_profiles

2 where profile="SAMPLE_PROF" ;

여러개 쭉 나오고 아래와 같은 옵션을 설정한 것도 나타남

failed_login_attempts 5

password_lock_time 5

password_life_time 10

password_reuse_time 10

3. 사용자에게 profile 적용시키고 확인하기

SYS> alter user webuser profile sample_prof ;

SYS> select username, profile

2 from dba_users

3 where username="WEBUSER" ;

USERNAME PROFILE

--------- --------------

WEBUSER SAMPLE_PROF

단 profile 을 적용시킬때 여러개의 profile 은 적용안되니 원하는 파라미터를 profile에 전부넣고 한번에 만들어야함

4. 사용안하는 profile 삭제하기

SYS> drop profile sample_prof ;

-> error 남 이유는 현재 사용자에게 할당된 profile 은 삭제가 안됨, 그러나 cascade 옵션으로 삭제하면 삭제가능

SYS> drop profile sample_prof cascade ;

-> 이렇게 삭제하고 나면 사용자는 default profile 을 사용하게 됨

privilege ( 권한 ) 관리하기

profile 은 사용자가 어떤 것들을 하지 못하게 제한하는 것이 목적

privilege 는 사용자에게 어떤 것들을 할 수 있게 허락해주는 것이 목적

- system 관련 privilege : system 전체에 영향을 주는 권한들을 모아놓은 것

- object 관련 privilege : 특정 object 에 관련된 privilege

SYSTEM 관련 주요 privilege
대분류	privilege	설명
Index	Create any index	소유자에 상관없이 모든 테이블에 인덱스를 생성할 수 있는 권한
	Drop any index	소유자에 상관없이 모든 인덱스를 삭제할 수 있는 권한
	Alter any index	소유자에 상관없이 모든 인덱스를 수정할 수 있는 권한
table	create table	자신의 테이블을 생성할 수 있는 권한
	create any table	소유자에 상관없이 다른 user 이름으로 테이블을 생성할 수 있는 권한
	alter any table	소유자에 상관없이 모든 테이블의 구조를 수정할 수 있는 권한
	drop any table	소유자에 상관없이 모든 사용자의 테이블을 삭제할 수 있는 권한
	update any table	소유자에 상관없이 모든 사용자의 테이블을 업데이트할 수 있는 권한
	delete any table	소유자에 상관없이 모든 사용자의 테이블의 데이터를 삭제 할 수 있는 권한
	insert any table	소유자에 상관없이 모든 사용자의 테이블의 데이터를 삽입 할 수 있는 권한
session	create session	서버에 접속할 수 있는 권한
	alter session	접속 상태에서 환경값을 변경할 수 있는 권한
	restricted session	restricted 모드로 open 된 db에 접속할 수 있는 권한
tablespace	create tablespace	tablespace를 만들 수 있는 권한
	alter tablespace	tablespace를 수정할 수 있는 권한
	drop tablespace	tablespace를 삭제할 수 있는 권한
	unlimited tablespace	tablespace 사용 용량을 무제한으로 허용하는 권한, 즉 quota 옵션적용을 받지않음

SYSOPER /SYSDBA privilege
privilege	할 수 있는 일
SYSOPER	startup / shutdown
	alter database mount / open
	alter database backup control file to ....
	recover database
	alter database archivelog
	restricted session
SYSDBA	SYSOPER privilege with admin option
	create database
	alter tablespace ... begin backup / end backup
	recover database until

SYSTEM 관련 권한 할당하기 / 해제하기

- scott 사용자에게 create table , create session 권한을 할당

SYS> grant create table, create session to scott ;

- scott 사용자에게 create table 권한을 해제

SYS> revoke create table from scott ;

사용자가 가지고 있는 권한 조회

- scott 사용자가 가지고 있는 모든 권한 조회

SYS> select * from dba_sys_privs

2 where grantee = "SCOTT" ;

Object 관련 Privilege

주로 DML 과 연관이 많음. 즉 object 를 select , insert , update , delete 등을 할 수 있는 권한

object 권한 할당하기 / 해제하기 ( 실습 전에 webuser 계정으로 webtest 테이블을 생성하고 해야함 )

- scott 사용자에게 webuser 가 만든 webtest 테이블을 select 할 수 있도록 허용

SYS> grant select on webuser.webtest to scott ;

- scott 사용자에게 webuser 가 만든 webtest 테이블을 update 할 수 있도록 허용, 그리고 scott 사용자가 이 권한을 다른 사람에게 줄 수 있는 권한도 부여

SYS> grant update on webuser.webtest to scott with grant option ;

- scott 사용자가 가진 webuser의 webtest 테이블을 select 하는 권한을 해제

SYS> revoke select on webuser.webtest from scott ;

Role 관리하기

role 속에 여러가지 권한을 넣어두고 사용자에게 role 을 부여하면 여러가지 권한을 갖게됨

(1) role 생성하기

SYS> create role trole ;

(2) role 에 create session , create table 권한 할당

SYS> grant create session , create table to role ;

(3) 사용자에게 trole 할당

SYS> grant trole to scott ;

(4) 어떤 사용자가 어떤 role 을 사용하는지 확인

SYS> select * from dba_role_privs where grantee='SCOTT' ; <-- SCOTT 계정에 어떤 role 이 있는지 확인

(5) 어떤 role 에 어떤 권한이 있는지 확인

SYS> select * from dba_sys_privs where grantee = 'CONNECT' ; <--CONNECT 에 어떤 권한이 있는지 확인

저작자표시 변경금지

'오라클 > 관리 실무' 카테고리의 다른 글

DBMS_JOB & DBMS_SCHEDULER (0)	2014.10.27
Oracle 저장구조 (0)	2014.10.23
Tablespace 와 Data File 관리하기 (2)	2014.10.21
Redo Log 관리하기 (0)	2014.10.20
Control File 관리하기 (0)	2014.10.20
ORACLE 시작하기 & 종료하기 (0)	2014.10.20
Oracle Background Process (0)	2014.10.17

Oracle 저장구조

김포춘 2014. 10. 23. 18:27

2014. 10. 23. 18:27

< 오라클 저장구조 >

1. Oracle Block

Oracle Block 은 OS Block 을 한 개 이상 합쳐서 생성하며 DB_BLOCK_SIZE 로 지정

Oracle Block 의 크기가 8KB이고 OS Block 이 2KB 이면 OS Block 4개가 합쳐져야 1개의 Oracle Block

OS 블록과 Oracle 블록을 1:1 비율로 생성하면 Block Split 현상이 생기지 않음

DB_BLOCK_SIZE 파라미터는 Create database 할때 한 번 지정이 되면 db를 재생성하기 전에 변경이 안되며

이 파라미터에서 지정되는 크기 값을 standard block size 라고 한다

Block 의 크기는 2KB , 4KB , 8KB , 16KB , 32KB 가 제공 ( 간혹 64KB 지원하는 OS도 있음 )

5개의 Block 중에서 DB_BLOCK_SIZE 값에 설정된 크기를 Standard Block Size 라고 하고

나머지를 Non-standard Block Size 라고 한다.

이렇게 다양한 블록 사이즈는 Tablespace 를 생성할 때 다르게 지정해서 사용 할 수 있다.

단 Standard block size 외의 테이블 스페이스를 생성할 경우 DB Buffer Cache 에도 해당 Block 사이즈 만큼의 공간을

미리 할당해 두어야 에러가 발생하지 않는다.

SQL> create tablespace test_4k

2 datafile '/app/oracle/oradata/testdb/test01.dbf' size 5m

3 blocksize 4k ;

create tablespace test_4k

ERROR at line 1 :

ORA-29339 : tablespace block size 4096 does not match configured block sizes <- 에러발생

SQL> alter system set db_4k_chache_size=10M ; <- DB Cache 에 미리 공간 할당

System altered

SQL> create te tablespace test_4k

2 datafile '/app/oracle/oradata/testdb/test01.dbf' size 5m

3 blocksize 4k ;

Tablespace created <- 에러없이 생성

2. PCTFREE & PCTUSED

PCTFREE : Block 에 입력되어 있는 데이터들이 Update 될 경우를 대비해서 비워두는 공간

이 값은 % 로 지정하며 8KB 의 블록을 PCTFREE=20 으로 지정할 경우 8KB의 20% 만큼 Update만을 위해 비워둠

PCTUSED : PCTFREE 를 뺀 공간이 Dirty block이 되었을때 특정량이 지워질 때 까지 Free block이 되지 않는다.

여기서 얼마만큼의 양이 지워 져야 Free block이 되는가를 결정하는 파라미터가 PCTUSED

- 위 그림과 같이 PCTFREE=20 을 주면 8K 의 20% 는 업데이트를 위해 비워두는 공간

- 남은 80% 중에서 블록이 가득 차면 Dirty block 이 되는데 PCTUSED를 20% 지정해 주면 블록에서 사용하고 있는 양이 20%가 되기 전까지는 Dirty block 으로 남아 있다가 20%가 되면 Free block로 바뀌게 된다.

위 방식은 Tablespace 가 ASSM 방식 인지 MSSM 방식인지에 따라 역할이 달라짐

- ASSM ( Automatic Segment Space Management ) : PCTUSED 파라미터 사용 안됨

- MSSM ( Manual Segment Space Management ) : Freelist 를 사용하기 때문에 FLM 이라고도 하며 테이블 생성시 두가지 파라미터 모두 사용

3. Row Migration & Row Chaining

Row Migration : Row 의 update 가 발생했을 경우 해당 block 에 빈 공간이 부족해서 다른 block 으로 이사를 가는 경우

- row migration 을 줄이려면 PCTFREE 값을 많이 주거나 테이블을 재생성 하는 작업을 해서 경우를 줄여야함

Row Chaining : 데이터가 너무 길어서 하나의 블록에 모두 기록할 수 없는 경우 다른 블록과 연결해서 데이터를 저장하는 경우

- block 의 크기보다 큰 데이터가 들어와서 생기는 경우가 많으므로 block의 크기를 크게 생성하는것이 좋다.

주의점

- row migration 을 줄이기 위해 PCTFREE 값을 많이주게되면 update 가 없을 경우 공간낭비가 심해지므로 주의

- row chaining 을 줄이기 위해 block 을 크게 주면 wait 현상이 많이 발생해서 성능이 저하될수 있음

4. Extent & Segment

연속적으로 있는 Block 을 여러개 묶어 놓은 단위를 Extent 라고 한다

이러한 Extent 를 여러개 모여서 Segment 가 구성됨

사용자가 테이블 생성시 데이터가 없더라도 기본값을 가지고 데이터파일에 연속적인 block 을 묶어서 extent를 생성

그리고 데이터가 계속 추가되어 최초 값을 다 사용하게 되면 extent를 추가로 생성하게 됨

실습.

SQL> create tablespace test_extent

2 datafile '/app/oracle/oradata/testdb/ex_test01.dbf' size 5M ;

SQL> create table ex_table01

2 ( no number , name varchar2(10)) tablespace test_extent ;

SQL> col tablespace_name for a15

SQL> select tablespace_name , extent_id , blocks , bytes

2 from user_extents

3 where segment_name='EX_TABLE01' ;

TABLESPACE_NAME EXTENT_ID BLOCKS BYTES

---------------- --------- ------- ------

TEST_EXTENT 0 8 65536

ex_table01 테이블을 생성하면서 자동적으로 64KB Extent 1개가 할당 되었고 이 Extent는 8kb block 8개 모아서 생성

이렇게 초기 할당된 extent 를 모두 다 쓰게 되면 다시 재 할당을 받게 됨

저작자표시 변경금지

'오라클 > 관리 실무' 카테고리의 다른 글

DBMS_JOB & DBMS_SCHEDULER (0)	2014.10.27
사용자 관리 (0)	2014.10.27
Tablespace 와 Data File 관리하기 (2)	2014.10.21
Redo Log 관리하기 (0)	2014.10.20
Control File 관리하기 (0)	2014.10.20
ORACLE 시작하기 & 종료하기 (0)	2014.10.20
Oracle Background Process (0)	2014.10.17

Tablespace 와 Data File 관리하기

김포춘 2014. 10. 21. 18:27

2014. 10. 21. 18:27

Tablespace = D.B Cache 내에서 데이터를 작업하는 공간

일반적으로 Tablespace가 클수록 수행 속도가 빠름

Tablespace 의 종류 및 특징

(1) SYSTEM tablespace

- 데이터 딕셔너리 정보들이 저장되어 있어서 이 Tablespace 가 손상될 경우 Oracle 서버가 시작이 안됨

데이터 딕셔너리 : 오라클 서버의 모든 정보를 저장하고 있는 아주 중요한 테이블이나 뷰들

- Static Dictionary : 내용이 실시간으로 변경 안됨

USER_XXX , ALL_XXX , DBA_XXX = DB 상태가 OPEN 일때만 조회가능

- Dynamic Performance View : 실시간으로 변경되는 내용을 볼 수 있음, Control File / 메모리로 가서 정보를

가져옴 , V $ XXX = DB상태 모든 상태에서 조회가능

데이터 딕셔너리에 들어있는 주요 정보

- 데이터베이스의 논리적인 구조와 물리적인 구조 정보들

- 객체의 정의와 공간 사용 정보들

- 제약조건에 관련된 정보들

- 사용자에 관련된 정보들

- Role, Privilege 등에 관련된 정보들

- 감사 및 보안등에 관련된 정보들

(2) SYSAUX tablespace

10g 버전부터 등장한 tablespace 로 oracle 서버의 성능 튜닝을 위한 데이터들이 저장되어 있음

(3) 일반 Tablespace

가장 일반적으로 많이 사용되는 tablespace로 관리자가 필요에 의해 만드는 tablespace

DBA 에 의해 얼마든지 생성하고 삭제할 수 있음

실습 1. 일반 Tablespace 생성 및 조회하기

SQL> create tablespace haksa

2 datafile '/app/oracle/oradata/testdb/haksa01.dbf' size 1m ;

Tablespace created.

SQL> select tablespace_name, status, contents, extent_management,

2 segment_space_management

3 from dba_tablespaces ;

TABLESPACE_NAME STATUS CONTENTS EXTENT_MANAGEMENT SEGMENT_SPAC

------------------- -------- -------- -------------------- --------------

SYSTEM ONLINE PERMANENT LOCAL MANUAL

SYSAUX ONLINE PERMANENT LOCAL AUTO

UNDOTBS1 ONLINE UNDO LOCAL MANUAL

TEMP ONLINE TEMPORARY LOCAL MANUAL

USERS ONLINE PERMANENT LOCAL AUTO

EXAMPLE ONLINE PERMANENT LOCAL AUTO

HAKSA ONLINE PERMANENT LOCAL AUTO

7 rows selected.

SQL> select tablespace_name, bytes/1024/1024 MB , file_name

2 from dba_data_files ;

TABLESPACE_NAME MB FILE_NAME

-------------------- ---- ----------------------------------------

EXAMPLE 346 /app/oracle/oradata/testdb/example01.dbf

USERS 8 /app/oracle/oradata/testdb/users01.dbf

UNDOTBS1 90 /app/oracle/oradata/testdb/undotbs01.dbf

SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf

SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf

HAKSA 1 /app/oracle/oradata/testdb/haksa01.dbf

6 rows selected.

실습 2. 각 Data file 의 실제 사용량 확인하는 방법

SQL> set line 200;

SQL> col file# for 999 ;

SQL> col ts_name for a10 ;

SQL> col total_blocks for 9999999 ;

SQL> col used_blocks for 9999999 ;

SQL> col pct_used for a10 ;

SQL> select distinct d.file_id file#,

2 d.tablespace_name ts_name,

3 d.bytes /1024/1024 MB,

4 d.bytes /8192 total_blocks,

5 sum(e.blocks) used_blocks,

6 to_char(nvl(round(sum(e.blocks)/(d.bytes/8192), 4),0) *100, '09.99') || '%' pct_used

7 from dba_extents e, dba_data_files d

8 where d.file_id = e.file_id(+)

9 group by d.file_id , d.tablespace_name , d.bytes

10 order by 1,2 ;

FILE# TS_NAME MB TOTAL_BLOCKS USED_BLOCKS PCT_USED

----- ---------- ---- ------------ ----------- ----------

1 SYSTEM 710 90880 89992 99.02%

2 SYSAUX 570 72960 68784 94.28%

3 UNDOTBS1 90 11520 2720 23.61%

4 USERS 8 960 736 76.67%

5 EXAMPLE 346 44240 39568 89.44%

6 HAKSA 1 128 00.00%

6 rows selected.

실습 3. Tablespace 용량 관리 하기

SQL> col tablespace_name for a10

SQL> col file_name for a50

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files ;

TABLESPACE MB FILE_NAME

---------- ---- --------------------------------------------------

EXAMPLE 346 /app/oracle/oradata/testdb/example01.dbf

USERS 8 /app/oracle/oradata/testdb/users01.dbf

UNDOTBS1 90 /app/oracle/oradata/testdb/undotbs01.dbf

SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf

SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf

HAKSA 1 /app/oracle/oradata/testdb/haksa01.dbf

6 rows selected.

## haksa Tablespace 에 iphak table을 만들어서 일부로 가득 차게해서 장애생기게 만듬

SQL> create table scott.iphak (studno number ) tablespace haksa ;

Table created.

SQL> begin

2 for i in 1..50000 loop

3 insert into scott.iphak values (i) ;

4 end loop ;

5 commit ;

6 end ;

7 /

PL/SQL procedure successfully completed.

SQL> /

begin

ERROR at line 1:

ORA-01653: unable to extend table SCOTT.IPHAK by 8 in tablespace HAKSA <-- 용량부족으로 에러발생

ORA-06512: at line 3

조치방법 : 1. Data file 을 하나 더 추가해 주는 방법

2. Data file 을 크게 늘려주는 방법 (수동증가와 자동증가가 있다)

조치방법 1. 수동으로 Tablespace 에 Data file 을 추가하는 방법

SQL> alter tablespace haksa

2 add datafile '/app/oracle/oradata/testdb/haksa02.dbf' size 20M ; <-- data file 추가

Tablespace altered.

SQL> select tablespace_name, bytes/1024/1024 MB , file_name from dba_data_files ;

TABLESPACE MB FILE_NAME

---------- ---- --------------------------------------------------

EXAMPLE 346 /app/oracle/oradata/testdb/example01.dbf

USERS 8 /app/oracle/oradata/testdb/users01.dbf

UNDOTBS1 90 /app/oracle/oradata/testdb/undotbs01.dbf

SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf

SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf

HAKSA 1 /app/oracle/oradata/testdb/haksa01.dbf

HAKSA 20 /app/oracle/oradata/testdb/haksa02.dbf

7 rows selected.

조치방법 2. Data file 크기 수동 증가 시키기

SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf'resize 20M ; <--data file 크기 로증가

Database altered.

조치방법 3. Data file 크기 자동 증가 시키기

SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' autoextend on ;

Database altered.

data file 은 자동 증가하게 되며 ORACLE 이 32 비트용일 경우 최대 파일 1개의 크기는 16GB까지 가능하며

ORACLE 이 64비트용일 경우 최대 크기가 32GB 까지 가능

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files ;

TABLESPACE MB FILE_NAME

---------- ---- --------------------------------------------------

EXAMPLE 346 /app/oracle/oradata/testdb/example01.dbf

USERS 8 /app/oracle/oradata/testdb/users01.dbf

UNDOTBS1 90 /app/oracle/oradata/testdb/undotbs01.dbf

SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf

SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf

HAKSA 20 /app/oracle/oradata/testdb/haksa01.dbf <-- 용량이 증가됨

HAKSA 20 /app/oracle/oradata/testdb/haksa02.dbf

7 rows selected.

## 각 data file 들의 autoextend 유무 확인하기

SQL> set line 200

SQL> col tablespace_name for a10

SQL> col file_name for a50

SQL> select tablespace_name, bytes/1024/1024 MB, file_name, autoextensible "AUTO", online_status

2 from dba_data_files ;

TABLESPACE MB FILE_NAME AUTO ONLINE_STATUS

---------- ---- ------------------------------------ ---- ----------------

EXAMPLE 346 /app/oracle/oradata/testdb/example01.dbf YES ONLINE

USERS 8 /app/oracle/oradata/testdb/users01.dbf YES ONLINE

UNDOTBS1 90 /app/oracle/oradata/testdb/undotbs01.dbf YES ONLINE

SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf YES ONLINE

SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf YES SYSTEM

HAKSA 20 /app/oracle/oradata/testdb/haksa01.dbf YES ONLINE

HAKSA 20 /app/oracle/oradata/testdb/haksa02.dbf NO ONLINE

7 rows selected.

SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf'autoextend on ;

Database altered.

SQL> select tablespace_name,bytes/1024/1024 MB, file_name, autoextensible "AUTO", online_status

2 from dba_data_files ;

TABLESPACE MB FILE_NAME AUTO ONLINE_STATUS

---------- ---- ------------------------------------ ------ --------------

EXAMPLE 346 /app/oracle/oradata/testdb/example01.dbf YES ONLINE

USERS 8 /app/oracle/oradata/testdb/users01.dbf YES ONLINE

UNDOTBS1 90 /app/oracle/oradata/testdb/undotbs01.dbf YES ONLINE

SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf YES ONLINE

SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf YES SYSTEM

HAKSA 20 /app/oracle/oradata/testdb/haksa01.dbf YES ONLINE

HAKSA 20 /app/oracle/oradata/testdb/haksa02.dbf NO ONLINE

7 rows selected.

SQL> begin

2 for i in 1..500000 loop

3 insert into scott.iphak values (i) ;

4 end loop;

5 commit ;

6 end ;

7 /

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.

SQL> select tablespace_name,bytes/1024/1024 MB, file_name,autoextensible "AUTO",online_status

2 from dba_data_files;

TABLESPACE MB FILE_NAME AUTO ONLINE_STATUS

---------- ---- --------------------------------------- ------- --------------

EXAMPLE 346 /app/oracle/oradata/testdb/example01.dbf YES ONLINE

USERS 8 /app/oracle/oradata/testdb/users01.dbf YES ONLINE

UNDOTBS1 265 /app/oracle/oradata/testdb/undotbs01.dbf YES ONLINE

SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf YES ONLINE

SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf YES SYSTEM

HAKSA 29 /app/oracle/oradata/testdb/haksa01.dbf YES ONLINE

HAKSA 20 /app/oracle/oradata/testdb/haksa02.dbf NO ONLINE

7 rows selected.

실습 4. Tablespace Offline

Tablespace를 offline 한다는 것은 더이상 tablespace에 접근을 못한다는 의미로 해당 tablespace만 shutdown 시키는것

특정 tablespace 의 데이터파일의 위치를 이동하거나 장애가 나서 복구할때 유용하게 사용

Offline 하는 3가지 방법

1. Normal Mode - 아무런 문제가 없을때 정상적으로 수행하는 방법

SQL> alter tablespace haksa offline ;

Database altered.

2. Temporary Mode - 현재 offline 시키고자 하는 tablespace 의 data file 이 하나라도 이상이 생기게 되었을때 사용

명령어 - offline temporary

3. Immediate Mode - archive log mode 일 경우에만 사용. data file에 장애가 나서 데이터를 내려쓰지 못하는 상황에 tablespace를 offline 해야 할 경우에 사용.

archive log mode 에서 사용

SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' offline ;

no archive log mode 에서 사용 -> 노 아카이브 모드에서 오프라인할 경우 recovery 하라는 메세지가 나옴

SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' offline drop ;

data file 이 online 인지 offline 인지 확인하는 방법

SQL> select file#, name, status from v$datafile;

FILE# NAME STATUS

----- -------------------------------------- --------

1 /app/oracle/oradata/testdb/system01.dbf SYSTEM

2 /app/oracle/oradata/testdb/sysaux01.dbf ONLINE

3 /app/oracle/oradata/testdb/undotbs01.dbf ONLINE

4 /app/oracle/oradata/testdb/users01.dbf ONLINE

5 /app/oracle/oradata/testdb/example01.dbf ONLINE

6 /app/oracle/oradata/testdb/haksa01.dbf ONLINE

7 /app/oracle/oradata/testdb/haksa02.dbf ONLINE

7 rows selected.

no archive log mode 에서 offline 하고 조회

SQL> alter database datafile '/app/oracle/oradata/testdb/haksa02.dbf' offline drop ;

Database altered.

SQL> select file# , name , status from v$datafile ;

FILE# NAME STATUS

----- ------------------------------------ ---------

1 /app/oracle/oradata/testdb/system01.dbf SYSTEM

2 /app/oracle/oradata/testdb/sysaux01.dbf ONLINE

3 /app/oracle/oradata/testdb/undotbs01.dbf ONLINE

4 /app/oracle/oradata/testdb/users01.dbf ONLINE

5 /app/oracle/oradata/testdb/example01.dbf ONLINE

6 /app/oracle/oradata/testdb/haksa01.dbf ONLINE

7 /app/oracle/oradata/testdb/haksa02.dbf RECOVER

7 rows selected.

< 강제로 datafile 을 offline 시키면 위와 같이 복구가 필요한 상태로 변함 >

SQL> alter tablespace example offline ;

Tablespace altered.

SQL> select file# , name , status from v$datafile ;

FILE# NAME STATUS

----- ----------------------------------- ----------

1 /app/oracle/oradata/testdb/system01.dbf SYSTEM

2 /app/oracle/oradata/testdb/sysaux01.dbf ONLINE

3 /app/oracle/oradata/testdb/undotbs01.dbf ONLINE

4 /app/oracle/oradata/testdb/users01.dbf ONLINE

5 /app/oracle/oradata/testdb/example01.dbf OFFLINE

6 /app/oracle/oradata/testdb/haksa01.dbf ONLINE

7 /app/oracle/oradata/testdb/haksa02.dbf RECOVER

7 rows selected.

Tablespace 를 offline 하게되면 그 파일들에는 새로운 정보가 저장되지 않는다. 그래서 offline 하고 online을 한다면 반드시 체크포인트를 발생시켜 data file 간의 동기화 작업을 해줘야 한다.

SQL> 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# ;

from v#datafile a, v$tablespace b

FILE# TS# NAME STATUS CHECKPOINT_CHANGE#

----- ---- ------ ------- ------------------

1 0 SYSTEM SYSTEM 1228512

2 1 SYSAUX ONLINE 1228512

3 2 UNDOTBS1 ONLINE 1228512

4 4 USERS ONLINE 1228512

5 6 EXAMPLE OFFLINE 1229265 <-- (checkpoint scn 이 다른파일과 다른것을 알수 있다

6 7 HAKSA ONLINE 1229084 여기서 online 을 해도 scn 은 여전히 다르다 )

7 7 HAKSA RECOVER 1229084

7 rows selected.

SQL> alter tablespace example online ;

Tablespace altered.

SQL> 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# ;

FILE# TS# NAME STATUS CHECKPOINT_CHANGE#

----- ---------- ------------------- ---------- ------------------

1 0 SYSTEM SYSTEM 1228512

2 1 SYSAUX ONLINE 1228512

3 2 UNDOTBS1 ONLINE 1228512

4 4 USERS ONLINE 1228512

5 6 EXAMPLE ONLINE 1229323 <-- 여전히 다름

6 7 HAKSA ONLINE 1229084

7 7 HAKSA RECOVER 1229084

7 rows selected.

이 상태에서 data file 을 백업 받는다면 백업파일 자체가 문제가 생기게 되고, 향후 복구에 문제가 될 수 있음

이럴 경우 alter system checkpoint ; 명령어를 사용해서 수동으로 체크포인트를 발생시킨후 모두 동기화 시킨 다음 백업을 받아야 함

SQL> alter system checkpoint ; <-- 강제로 checkpoint 발생시킴

System altered.

SQL> alter tablespace haksa offline ;

alter tablespace haksa offline

ERROR at line 1:

ORA-01191: file 7 is already offline - cannot do a normal offline <-- 이미 하나가 offline 상태여서 에러남

ORA-01110: data file 7: '/app/oracle/oradata/testdb/haksa02.dbf'

SQL> alter tablespace haksa offline temporary ; <-- temporary offline 을 사용해서 offline 시켜줌

Tablespace altered.

SQL> recover tablespace haksa ; <-- recovery 필요한 haksa 파일을 복구

Media recovery complete.

SQL> alter tablespace haksa online ; <-- haksa 파일 online 시켜줌

Tablespace altered.

SQL> 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# ;

from v$data_file a, v$tablespace b

FILE# TS# NAME STATUS CHECKPOINT_CHANGE#

----- --- ------- -------- ---------------------

1 0 SYSTEM SYSTEM 1229584

2 1 SYSAUX ONLINE 1229584

3 2 UNDOTBS1 ONLINE 1229584

4 4 USERS ONLINE 1229584

5 6 EXAMPLE ONLINE 1229584

6 7 HAKSA ONLINE 1229630 <-- 복구했지만 scn이 다름

7 7 HAKSA ONLINE 1229630 <-- 복구했지만 scn이 다름

7 rows selected.

SQL> 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#

FILE# TS# NAME STATUS CHECKPOINT_CHANGE#

----- ---------- -------------------------------------------------- -------

1 0 SYSTEM SYSTEM 1229674

2 1 SYSAUX ONLINE 1229674

3 2 UNDOTBS1 ONLINE 1229674

4 4 USERS ONLINE 1229674

5 6 EXAMPLE ONLINE 1229674

6 7 HAKSA ONLINE 1229674 <-- SCN 같아짐

7 7 HAKSA ONLINE 1229674 <-- SCN 같아짐

7 rows selected.

실습 5. Data file 이동시키는 작업

data file 을 이동시킬 때 순서가 가장 중요함.

data file 이 사용중일 때 절대 이동시키거나 복사를 하면 안된다. offline 과 shutdown 을 활용해서 사용안함으로 만들자

1. Offline 되는 Tablespace 의 Data file 이동하기

<순서 요약>

1. 해당 Tablespace offline <- 반드시 해야함!!!

2. Data file 을 대상 위치로 복사

3. 컨트롤 파일 내의 해당 Data file 위치 변경

4. 해당 Tablespace online

haksa Tablespace 의 Data file haksa01.dbf 를 이동 /app/oracle/disk1/haksa01.dbf 로 이동시켜보겠음

먼저 이동시킬 디렉토리를 생성

SQL>!mkdir /app/oracle/disk1

SQL> alter tablespace haksa offline ; <-- 변경전에 반드시 offline

SQL> !cp /app/oracle/oradata/testdb/haksa01.dbf /app/oracle/disk1/

SQL>select name from v$datafile ; <-- 컨트롤 파일에 기록된 data file 의 위치확인

NAME

---------------------------------------------

/app/oracle/oradata/testdb/system01.dbf

/app/oracle/oradata/testdb/sysaux01.dbf

/app/oracle/oradata/testdb/undotbs01.dbf

/app/oracle/oradata/testdb/users01.dbf

/app/oracle/oradata/testdb/example01.dbf

/app/oracle/oradata/testdb/haksa01.dbf <-- 요놈

/app/oracle/oradata/testdb/haksa02.dbf

/app/oracle/oradata/testdb/undo01.dbf

SQL>alter tablespace haksa rename

2 datafile '/app/oracle/oradata/testdb/haksa01.dbf' <-- 원래 있던 경로와 파일명

3 to '/app/oracle/disk1/haksa01.dbf' ; <-- 바뀐 경로와 파일명

SQL>select name from v$datafile ;

NAME

---------------------------------------------

/data2/disk3/system01.dbf

/data2/disk3/sysaux01.dbf

/data2/disk4/undotbs01.dbf

/data2/disk5/users01.dbf

/data2/disk4/example01.dbf

/app/oracle/disk1/haksa01.dbf <-- 바뀐것을 확인할 수 있다

/app/oracle/oradata/testdb/haksa02.dbf

/app/oracle/oradata/testdb/undo01.dbf

SQL>alter tablespace online <-- 다시 online 해주면 완료

2. Offline 안되는 Tablespace 의 Data file 이동하기

Offline 안되는 Tablespace 3가지

- system tablespace

- undo tablespace

- default tablespace

위 3가지를 이동할 때는 offline 이 안되기 때문에 db를 종료하고 작업을 해야함

<순서요약>

1. DB를 종료

2. MOUNT 상태로 시작

3. Data file 을 복사

4. 컨트롤 파일의 내용을 변경

5. DB OPEN

system01.dbf 파일을 /app/oracle/disk3/system01.dbf 로 이동시켜보겠음

SQL> shutdown immediate ;

SQL> startup mount ;

SQL> !mkdir /app/oracle/disk3

SQL> !cp /app/oracle/oradata/testdb/system01.dbf /app/oracle/disk3/

SQL> select name from v$datafile ;

NAME

---------------------------------------------

/app/oracle/oradata/testdb/system01.dbf <-- 요놈

/app/oracle/oradata/testdb/sysaux01.dbf

/app/oracle/oradata/testdb/undotbs01.dbf

/app/oracle/oradata/testdb/users01.dbf

/app/oracle/oradata/testdb/example01.dbf

/app/oracle/disk3/haksa01.dbf

/app/oracle/oradata/testdb/haksa02.dbf

/app/oracle/oradata/testdb/undo01.dbf

SQL> alter database rename

2 file '/app/oracle/oradata/testdb/system01.dbf' <--변경전 경로와 이름

3 to '/app/oracle/disk3/system01.dbf' ; <--변경후 경로와 이름

SQL> select name from v$datafile ;

NAME

---------------------------------------------

/app/oracle/disk3/system01.dbf <-- 변경 되었다

/app/oracle/oradata/testdb/sysaux01.dbf

/app/oracle/oradata/testdb/undotbs01.dbf

/app/oracle/oradata/testdb/users01.dbf

/app/oracle/oradata/testdb/example01.dbf

/app/oracle/disk3/haksa01.dbf

/app/oracle/oradata/testdb/haksa02.dbf

/app/oracle/oradata/testdb/undo01.dbf

SQL> alter database open ;

DB MOUNT상태면 모든 Data file 및 Redo log 파일도 이동시킬 수 있다.

3. Redo log file 이동하기

Redo log file 은 offline 안되기 때문에 반드시 사용을 안하게 만들기 위해 D.B를 MOUNT 상태로 두고 작업

1. 현재상태 확인

SQL> col member for a50

SQL> 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 ;

GROUP# MEMBER MB ARC STATUS

------ --------------------------------------------- ---- --- --------

1 /app/oracle/oradata/testdb/redo01.log 50 NO INACTIVE

2 /app/oracle/oradata/testdb/redo02.log 50 NO CURRENT

3 /app/oracle/oradata/testdb/redo03.log 50 NO INACTIVE

이 redo log 파일들을

/app/oracle/disk4/redo01_a.log , redo02_a.log , redo03_a.log

/app/oracle/disk5/redo01_b.log , redo02_b.log , redo03_b.log 로 이동

SQL> select status from v$instance <-- 시작전에 반드시 Instance 상태부터 확인

STATUS

-------

OPEN <-- redo log 파일을 옮기려면 반드시 mount 상태여야 한다

SQL> shutdown immediate ;

SQL> startup mount ;

SQL> !mkdir /app/oracle/disk4

SQL> !mkdir /app/oracle/disk5

SQL>!cp /app/oracle/oradata/testdb/redo01.log /app/oracle/disk4/redo01_a.log <-- 이름 바꾸며 복사

SQL>!cp /app/oracle/oradata/testdb/redo02.log /app/oracle/disk4/redo03_a.log

SQL>!cp /app/oracle/oradata/testdb/redo03.log /app/oracle/disk4/redo03_a.log

SQL> alter database rename

2 file '/app/oracle/oradata/testdb/redo01.log' <--변경 전 이름과 위치

3 to '/app/oracle/disk4/redo01_a.log' ; <-- 변경 후 이름과 위치

SQL> alter database rename

2 file '/app/oracle/oradata/testdb/redo02.log' <--변경 전 이름과 위치

3 to '/app/oracle/disk4/redo02_a.log' ; <-- 변경 후 이름과 위치

SQL> alter database rename

2 file '/app/oracle/oradata/testdb/redo03.log' <--변경 전 이름과 위치

3 to '/app/oracle/disk4/redo03_a.log' ; <-- 변경 후 이름과 위치

SQL> select member from v$logfile ;

MEMBER

---------------------------------------

/app/oracle/disk4/redo01_a.log <-- 바뀌었음

/app/oracle/disk4/redo02_a.log <-- 바뀌었음

/app/oracle/disk4/redo03_a.log <-- 바뀌었음

SQL> 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 ;

SQL> select member from v$logfile ;

MEMBER

---------------------------------------

/app/oracle/disk4/redo01_a.log

/app/oracle/disk4/redo02_a.log

/app/oracle/disk4/redo03_a.log

/app/oracle/disk5/redo01_b.log <-- 추가되었음

/app/oracle/disk5/redo02_b.log <-- 추가되었음

/app/oracle/disk5/redo03_b.log <-- 추가되었음

SQL> alter database open ;

실습 6 . Tablespace 삭제하기

SQL> drop tablespace haksa ; <-- table이 하나라도 있으면 그냥 안지워 진다

SQL> drop tablespace haksa including contents and datafiles ; <-- 옵션을 줘서 삭제해야함

drop 은 가급적 사용하지 않음

(2) Undo Tablespace

Undo data 란 DML 을 수행할 경우 발생하는 원본 데이터. 즉 홍길동을 일지매로 업데이트 할경우 홍길동이 Undo data

Undo data 만을 저장하는 segment 를 undo segment 라고 하며, undo segment를 저장하고 있는 tablespace 를 undo tablespace 라고 한다.

1. Undo Tablespace 의 특징

- Oracle Server process 는 tablespace 에 undo segment 를 생성하고 각 사용자 별로 undo segment 를 할당해서 undo data를 관리. 단, 사용자는 관여할 수 없음

- Undo tablespace 는 instance 당 여러개가 동시에 존재 할 수 있지만 사용되는 것은 한번에 1개 뿐

- undo tablespace 관리방법에는 자동 Mode 인 Automatic Undo Management (AUM) 과 수동 모드가 있다.

자동 Mode로 관리하려면 초기화 파라미터 파일( pfile, spfile )에 undo_management=auto 로 설정

수동 Mode로 관리하려면 초기화 파라미터 파일( pfile, spfile )에 undo_management=manual 로 설정

2. 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 로 만들어 주는데 사용

< 데이터 업데이트 순서 >

1. 데이터를 DB Buffer Cache 로 복사

2. Redo log Buffer 에 변경내용 기록

3. Undo segment 기록

4. DB Buffer Cache 의 원본 변경

< CR 작업 >

- 사용자 A 가 empno=10 번인 일지매를 홍길동으로 변경하는 Update 문을 수행해서 DB Buffer Cache 에 1번과 같이 이름이 변경 되었고, Lock 이 설정되어 사용자 A가 commit 이나 rollback 을 수행하기 전까지 아무도 1번 블록의 변경된 데이터를 볼 수 없는 상태이다.

- 이 상태에서 사용자 B가 empno=10 번의 조건으로 모든 컬럼을 가져오는 쿼리를 수행 했을 경우 B사용자의 Server Process 는 3번 Undo 에 있는 데이터를 2번과 같이 DB Buffer Cache로 복사해 와서 데이터를 조회

이와 같은 과정을 CR (Consistent Read - 읽기 일관성 ) 작업이라고 한다.

3. Undo segment 할당되는 원리

Undo Tablespace 안의 Undo Data file 의 크기는 증가만 되고, 줄어들지 않는다.

위 그림은 Undo Tablespace 안에 Undo Segment 4개가 할당되어 각각 A , B , C , D 에 의해 사용되고 있는 상태

이 상황에서 E 사용자가 새로 접속해 DML 을 수행할 경우 Undo Segment를 확보하게 되는데 이때 확보하는 Undo Segment

가 기존에 있던 segment 중에서 트랜잭션이 완료된 것을 먼저 확인후 완료된 트랜잭션에 덮어 쓰는 형식

아래 그림은 A 가 쓰던 Undo segment가 commit이 된 상태라 E가 덮어쓴 것

다음 새로 F 사용자가 접속해서 DML을 수행하려 할때 위의 모든 사용자 중에서 아무도 트랜잭션이 완료되지 않았으면

새로운 Undo segment 를 생성해서 기록

이렇게 undo segment 가 data file 의 저장공간이 허용하는 범위까지 늘어나다가 data file에 더 공간이 없게되면, 하나의

segment 에 2개 세션 이상의 undo data를 함께 기록.

그러다 그 공간마저 없으면 해당 트랜잭션은 에러가 발생

※ Undo table space의 늘어난 data file의 용량은 덮어쓸 뿐이지 삭제되지 않아서 크기는 줄어들지 않음

나중에 비정상적으로 data file 이 커지면 관리자가 다른 undo tablespace 를 신규로 만들어 변경시키고, 기존 undo tablespace 를 삭제해 주어야 한다.

실습 1. 현재 상태 파악

SQL> show parameter undo ;

실습 2. 신규 undo tablespace 생성

SQL> create undo tablespace undo01

2 datafile '/app/oracle/oradata/testdb/undo01.dbf size 10 M

3 autoextend on ;

SQL> select tablespace_name, bytes/1024/1024 mb , file_name from dba_data_files ; <--생성된 undo tablespace 경로확인

실습 3. Undo tablespace 변경 ( undotbs1 -> undo01 )

SQL> show parameter undo ;

NAME TYPE VALUE

------------------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL> alter system set undo_tablespace=undo01 ; <-- 재부팅 없이 즉시 변경 가능

-> pfile 을 사용할 경우 이 작업 후 반드시 파라미터 파일의 내용도 변경해야 DB 재시작후 장애가 없음

SQL> show parameter undo ;

NAME TYPE VALUE

------------------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDO01

(5) temporary tablespace

임시 자료를 저장하는 tablespace 로 DB가 재시작되면 이 곳에 있던 내용은 모두 사라지며, 일반적으로 정렬작업을 할 때 PGA 공간이 부족하면 이곳을 이용해 정렬작업 등을 하게됨, 정렬작업 외에 Export/Import 등 여러가지 작업을 할 경우도 사용

Temporary tablespace 는 하나의 Instance에 여러개 만들 수 있으므로 성능향상을 위해 사용자별로 하나씩, 크게 할당해주는게 좋다.

< Temporary tablespace 동작 원리 >

1. 사용자가 100 건의 데이터를 출력하고자 한다.

2. Disk 에서 DB캐쉬로 데이터100 건을 복사

3. 정렬을 위해 PGA로 데이터를 옮길 때 데이터가 옮기려는 데이터가 많을 경우 Sort run 단위로 잘라서 이동

데이터 양이 적어서 PGA에서 한번에 정렬할 수 있으면 Temporary tablespace 로 안가고 바로 정렬해서 출력

4. PGA 에서 데이터를 정렬해서 Temporary Tablespace 로 이동

5. Temporary tablespace 에서 100건의 데이터를 모아서 병합(merge) 시켜 사용자에게 출력

실습 1. temporary tablespace 조회

SQL> select file_id, tablespace_name, bytes 1024/1024 MB, file_name

2 from dba_temp_files ;

FILE_ID TABLESPAE MB FILE_NAME

------ ---------- --- ----------

2 TEMP 10 /app/oracle/oradata/testdb/temo01.dbf

2. 신규 temporary tablespace 생성

SQL> create temporary tablespace temp2

2 tempfile '/app/oracle/oradata/testdb/temp02.dbf' size 10 M

3 autoextend on ;

3. Default temporary tablespace 설정

SQL> select * from databae_properties

2 where property_name like 'DEFAULT_TEMP%' ;

PROPERTY_NAME PROPERTY_V DESCRIPTION

------------------------- ----------- -------------------------------

DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace

SQL> alter database default temporary tablespace temp2 ;

SQL> select * from databae_properties

2 where property_name like 'DEFAULT_TEMP%' ;

PROPERTY_NAME PROPERTY_V DESCRIPTION

------------------------- ----------- -------------------------------

DEFAULT_TEMP_TABLESPACE TEMP2 Name of default temporary tablespace

4. temporary tablespace 크기 변경하기

SQL> alter database tempfile '/app/oracle/oradata/testdb/temp02.dbf' resize 100M ;

5. temporary tablespace 삭제하기 < default temporary tablespace 는 삭제 안됨 >

SQL> create temporary tablespace temp3

2 tempfile '/app/oracle/oradata/testdb/temp03.dbf' size 10M ;

SQL> alter database default temporary tablespace temp3 ; <--temp3 생성하여 default로 변경

SQL> drop tablespace temp2 ;

Temporary tablespace group ( 11g 부터 생김 )

기존에는 schema 에 할당된 temporary tablespace가 1개뿐이라서 여러명이 접속해 작업을 하면 늦어지게 됬지만

11g 부터는 temporary tablespace 를 여러개 만들어 그룹으로 묶어주고 특정 schema에 temporary tablespace group을 지정해 주는 것. 그렇게 되면 하나의 스키마로 여러명의 사용자가 동시 로그인해서 작업을 하더라도 각각 temporary tablespace group 안에 있는 여러 개의 temporary tablespace를 각각 별도로 사용해 성능이 향상됨

저작자표시 변경금지

'오라클 > 관리 실무' 카테고리의 다른 글

DBMS_JOB & DBMS_SCHEDULER (0)	2014.10.27
사용자 관리 (0)	2014.10.27
Oracle 저장구조 (0)	2014.10.23
Redo Log 관리하기 (0)	2014.10.20
Control File 관리하기 (0)	2014.10.20
ORACLE 시작하기 & 종료하기 (0)	2014.10.20
Oracle Background Process (0)	2014.10.17

Redo Log 관리하기

김포춘 2014. 10. 20. 19:43

2014. 10. 20. 19:43

Redo Log : 변경되는 내용이 있을경우 모두 기록해 두었다가 장애를 대비하는 기능

Redo Log Buffer : 오라클에서 데이터 변경시 장애가 발생할 것을 대비해 변경되기 전과 후의 내용을 기록하는 메모리

Redo Log File : 오라클에서 데이터 변경시 장애가 발생할 것을 대비해 변경되기 전과 후의 내용을 기록하는 파일

데이터 변경( DDL , DML , TCL ) 이 발생하면 두가지 매커니즘에 의해 Redo Log 에 기록 됨

- Write Log Ahead : 데이터를 변경하기 전에 Redo Log 에 먼저 기록한 후 데이터를 변경

DBWR 이 작동하기 전에 LGWR 이 먼저 작동

- Log force at Commit : 사용자로 부터 commit 요청이 들어오면 관련된 모든 Redo Record 들은 Redo Log File 에 저장한후 Commit 을 완료

< Redo log 기록 원리 >

1. 사용자가 쿼리를 수행 하면 서버 프로세스는 원하는 Block 이 D.B.Buffer Cache 에 있는지 확인한 후 없을 경우 해당 블록을 Data file 에서 찾아서 D.B.Buffer Cache 로 복사해온다. 그 후 해당 블록을 다른 사용자가 바꿀 수 없도록 Lock 을 설정 ( page fix 라고 함 ) 한 후, PGA 에서 Redo Change Vector 를 생성. ( Change Vector = 변경된 데이터를 나중에 복구할 목적으로 Redo log 에 기록할 변경된 데이터에 대한 모든 정보 ) change vector는 row 단위로 redo log buffer 에 복사됨

2. PGA에서 Redo Log Buffer 에 복사하기 위해 latch 를 획득해야 함. ( latch = 한정된 자원을 여러 사용자가 사용하는 것을 막기위해 순서를 정리해 주는 역할을 하는 장치 ) . Redo log buffer 에 기록하기전에 먼저 Redo Copy latch 를 획득해야 한다. redo copy latch 는 change vector 가 Redo log buffer에 기록될 때 까지 갖고 있어야 해서 여러개가 존재

3. Redo Copy Latch 를 확보한 서버 프로세스는 Redo Log Buffer 에 내용을 기록하기 위해 Redo Allocation latch 를 확보해야 한다. 8i 버전 까지는 Redo Allocation latch 가 1개 밖에 없어서 데이터의 변경이 많이되는 서버일경우 경합이 일어나 속도가 저하 됬었다. 9i 부터는 Redo Log Buffer 를 여러 공간으로 나눠 공간마다 Redo Allocation latch 를 할당해 주는 Shared Redo Strand라는 기능이 도입되 여러 프로세스가 동시에 작업이 가능하게 해주었다.

4. Redo log buffer 에 기록된 내용들은 특정 상황이 되면 LGWR 에 요청해서 Redo Log file 로 데이터를 기록한후 Redo log buffer 에서 삭제( Flush )

- LGWR 이 Redo log file 로 기록하는 경우

1. 3초마다

2. Redo log buffer 의 전체크기의 1/3 이 찼거나 1M 이 넘을 경우

3. 사용자가 commit 또는 rollback 을 수행할 때

4. DBWR 이 LGWR 에게 쓰기를 요청할 때

1. LGWR ( Log Writer ) 에 의한 Redo Log File 에 파일 쓰기

< 데이터를 입력받기 전에 Data File 에서 빈 블록을 D.B.Buffer Cache 에 이동시키고 다른 사용자가 사용하지 못하게 LOCK을 걸어준다.>

1. Redo Log Buffer 에 데이터가 들어오면 LGWR 프로세스를 이용해 commit데이터에 SCN을 기록 해서 Redo Log File로 이동시킨다. 이때 이동하고 나면 Redo Log Buffer 에 있던 데이터는 없어진다.

2. Redo Log Buffer 에 데이터가 들어오면 D.B.Buffer Cache 에도 똑같이 데이터가 들어간다, 이때 데이터는 Data File 에서 이동시켰던 빈 블록으로 들어감

3. LGWR 은 Redo Log File 에 저장하고 부여한 각각의 데이터의 SCN 최종 번호를 Control File의 commit SCN 부분에 기록한다.

2. Log Switch 발생에 따른 변화

1,2 Commit 된 데이터들이 Redo Log File 에 기록하다가 공간이 부족하면 다음 파일로 이동해 기록하는 것을 Log Switch가 발생했다고 한다.

3. Log Switch 가 발생하면 CKPT 프로세스가 Log Switch 를 감지해서 checkpoint 신호를 발생시킨다

Redo Log File 의 상태

- Current : 현재 LGWR 에 의해서 Redo Log File 에 기록되고 있는 상태 ( = Pinned Buffer )

- Active : Redo Log File의 공간이 가득차서 Log Switch 에 의해 다른파일로 Current 넘어간 상태, 그러나 저장된 기록은 Data File 에 저장되지 않은 파일의 상태 ( = Dirty Buffer )

- Inactive : Redo Log File 에 저장된 데이터가 DataFile 에도 저장된 상태. 이 상태에서만 Redo Log File 을 지울수 있다.( = Free Buffer )

3. CheckPoint 에 따른 변화

1. CKPT 프로세스가 Log Switch 를 감지하고 DBWR에 checkpoint 신호를 전달 ( 가득찬 그룹의 최종 SCN을 보냄 )

2. DBWR 은 D.B.Buffer cache 에 기록된 데이터를 DataFile 에 저장 ( Log Switch 가 일어나서 넘어온 SCN 숫자와 그에 맞는 데이터만 저장 )

3. CKPT 프로세스는 Control File 의 checkpoint SCN에도 직접 SCN을 기록

Redo Log File 의 그룹과 멤버

	최소	최대
그룹	2	3
멤버	1	2

저작자표시 변경금지

'오라클 > 관리 실무' 카테고리의 다른 글

사용자 관리 (0)	2014.10.27
Oracle 저장구조 (0)	2014.10.23
Tablespace 와 Data File 관리하기 (2)	2014.10.21
Control File 관리하기 (0)	2014.10.20
ORACLE 시작하기 & 종료하기 (0)	2014.10.20
Oracle Background Process (0)	2014.10.17
SQL 문장의 실행 원리 (0)	2014.10.17

Control File 관리하기

김포춘 2014. 10. 20. 15:21

2014. 10. 20. 15:21

Control 파일은 Database가 운영될 때 실시간으로 각종 정보가 저장되고 또 조회가 된다.

이 파일에 장애가 발생할 경우 Instance가 실패되어 중단되므로 잘 관리해야함

컨트롤 파일 관리하기

컨트롤 파일이 삭제 될 경우 아주 심각한 문제가 발생하기 때문에 이 파일이 삭제되지 않도록 주의해야 하며, 혹시나 삭제되더라도 복구할 수 있도록 여러곳에 복사본을 만들어 분산시켜 관리하는것을 권장 ( 이를 Multiplexing - 다중화 라고 한다 )

DBA가 startup을 실행하면 parameter file을 읽은 후 control file 의 위치를 확인 후 해당 control file들을 메모리로 불러와 내용을 확인. 다중화 할 경우 parameter file 에 control file 위치를 기록한 후 그 위치에 control file을 복사해 두 면 된다.

실습 1. spfile 일 경우 다중화 하는 방법

SQL> show parameter spfile ; 을 이용해 spfile로 실행하는지 확인 (value 값이 있으면 spfile )

step 1. 현재 control file 조회

(11g)

SQL> select name from v$controlfile ;

NAME

-----------------------------------------

/app/oracle/oradata/testdb/control01.ctl

/app/oracle/fast_recovery_area/testdb/control02.ctl

step 2. spfile 의 내용을 변경한 후 Instance 종료

SQL> alter system set control_file='/home/oracle/disk1/ctrl01.ctl' ,

'/home/oracle/disk2/ctrl02.ctl' ,

'/home/oracle/disk3/ctrl03.ctl' scope = spfile ;

System altered.

SQL> shutdown immediate ;

step 3. 대상 디렉토리를 생성하고 파일을 복사

SQL>!

[oracle@ ~]$ cd /home/oracle

[oracle@ oracle]$ mkdir disk1 disk2 disk3

[oracle@ oracle]$ cp /app/oracle/oradata/testdb/control01.ctl /home/oracle/disk1/control01.ctl

[oracle@ oracle]$ cp /app/oracle/oradata/testdb/control01.ctl /home/oracle/disk2/control02.ctl

[oracle@ oracle]$ cp /app/oracle/oradata/testdb/control01.ctl /home/oracle/disk3/control03.ctl

[oracle@ oracle]$ exit

step 4. startup 시켜서 확인

SQL> startup

SQL> select name from v$controlfile ;

NAME

----------------------------

/home/oracle/disk1/control01.ctl

/home/oracle/disk2/control02.ctl

/home/oracle/disk3/control03.ctl

순서 : spfile 의 내용변경 -> Instance 종료 -> control file 복사 -> Instance Open

실습 2. pfile 일 경우 다중화 하는 방법

현재 spfile 이므로 spfile 삭제하고 pfile 생성

SQL> create pfile from spfile ;

SQL> !rm -rf $ORACLE_HOME/dbs/spfiletestdb.ora

SQL> shutdown immediate ;

SQL> startup

SQL> show parameter pfile ; <- pfile 로 열었기 때문에 value 값이 비어있어야 한다

step 1. 현재 사용중인 control file 조회

SQL> select name from v$controlfile ;

NAME

-----------------------------

/home/oracle/disk1/control01.ctl

/home/oracle/disk2/control02.ctl

/home/oracle/disk3/control03.ctl

위에서 조회된 control file의 위치를

/home/oracle/disk4/control01.ctl

/home/oracle/disk5/control02.ctl

/home/oracle/disk6/control03.ctl 로 다중화

step 2. instance 종료

SQL> shutdown immediate ;

step 3. pfile 에서 control file의 경로를 수정한 후 저장

SQL> !vi $ORACLE_HOME/dbs/inittestdb.ora

*.control_files='/home/oracle/disk4/control01.ctl' ,

'/home/oracle/disk4/control01.ctl' ,

'/home/oracle/disk4/control01.ctl'

찾아서 변경

step 4. 해당 디렉토리를 생성 후 control file 복사

SQL> !

[oracle @localhost ~]$ cd /home/oracle

[oracle @localhost oracle]$ mkdir disk4 disk5 disk6

[oracle @localhost oracle]$ cp /home/oracle/disk1/control01.ctl /home/oracle/disk4/control01.ctl

[oracle @localhost oracle]$ cp /home/oracle/disk1/control01.ctl /home/oracle/disk5/control02.ctl

[oracle @localhost oracle]$ cp /home/oracle/disk1/control01.ctl /home/oracle/disk6/control03.ctl

[oracle @localhost oracle]$ exit

step 5. startup 해서 확인

SQL> startup

SQL> select name from v$controlfile ;

NAME

--------------------------------------

/home/oracle/disk4/control01.ctl

/home/oracle/disk5/control02.ctl

/home/oracle/disk6/control03.ctl

pfile 의 경우 step3 과 step4 의 과정을 바꾸어도 상관없다. 그러나 나머지 순서는 바뀌면 장애가 발생하니 순서에 주의

control file 에는 변경되는 정보가 실시간으로 저장되기 때문에 가장 최근에 사용한 것만 진짜 control file 이고 나머지는 사용할 수 없는 control file이 되므로 이동이나 복사할때 최근 사용한 것으로 해야 한다.

저작자표시 변경금지

'오라클 > 관리 실무' 카테고리의 다른 글

Oracle 저장구조 (0)	2014.10.23
Tablespace 와 Data File 관리하기 (2)	2014.10.21
Redo Log 관리하기 (0)	2014.10.20
ORACLE 시작하기 & 종료하기 (0)	2014.10.20
Oracle Background Process (0)	2014.10.17
SQL 문장의 실행 원리 (0)	2014.10.17
Oracle Architecture (2)	2014.10.17

ORACLE 시작하기 & 종료하기

김포춘 2014. 10. 20. 14:44

2014. 10. 20. 14:44

<오라클 서버의 시작 순서와 파일들>

※ Alter Log 파일은 10g 의 경우 $ORACLE_BASE/admin/SID/bdump/alert_SID.log

11g 의 경우 $ORACLE_BASE/diag/rdbms/SID/SID/trace/alert_SID.log로 존재

1. Parameter File ( 초기화 파라미터 파일 )

(1) 파라미터란 ?

- 묵시적 파라미터 : 관리자가 지정하지 않을 경우 자동으로 기본 값을 가지는 파라미터

- 명시적 파라미터 : 관리자가 지정해 주어야만 값을 가지는 파라미터

항목/파일	Pfile	Spfile
파일이 존재하는 기본경로	$ORACLE_HOME/dbs
파일 이름	initSID.ora	spfileSID.ora
내용 변경	관리자 ( 사람 )	서버 프로세스
파일 형태	Text ( OS 편집기로 편집가능 )	Binary ( OS 편집기로 편집 불가 )
	정적 파라미터	동적 파라미터

8i 버전 까지 pfile 이 기본 파라미터 파일

9i 버전 부터 spfile 이 기본 파라미터 파일 ( 그래도 여전히 pfile 사용 가능 )

(2) 파라미터 파일의 내용 확인하기

- spfile 은 파일 형태가 바이너리 파일이라서 사람이 내용을 수정할 수 없고, 프로그램이나 프로세스가 내용을 변경

※ 절대 바이너리파일은 사용자가 수정하면 안됨

- pfile 과 spfile 이 동시에 존재할 경우 spfile 내용만 사용함

(3) 파라미터 파일의 내용 변경하기

- pfile 을 수정하고 적용하려면 재시작 해야 함

- 9i 부터 Dynamic SGA 기능이 도입되어 pfile 을 사용하더라도 alter system set 명령을 사용하면 재부팅 없이 즉시적용가능 ( 그러나 D.B를 재부팅하면 os편집기로 수정하지 않았기 때문에 원래의 값으로 돌아가게 됨 )

- spfile 은 편집기로 내용을 수정할 수 없기 때문에 alter system set 명령을 사용하여 수정

사용 예 ) Database Buffer Cache 값을 30M로 변경하는 예

SYS> ALTER SYSTEM SET db_cache_size=30m SCOPE = Memory ;

위의 예에서 SCOPE 부분에 올 수 있는 옵션에는 Memory, Spfile, Both 세가지가 있다.

Memory = Spfile의 내용은 변경하지 말고 현재 작동 중인 인스턴스에만 적용하라는 의미. 재부팅하면 다시 Spfile에 적혀있는 값으로 되돌아감. 테스트 등의 목적으로 일시적으로 사용할때

Spfile = 현재 운영중인 인스턴스에는 적용하지 말고 Spfile 의 내용만 변경하라는 뜻.

재부팅 후부터 적용하겠다는 의미

Both = 현재운영중인 인스턴스에도 즉시 적용하고, spfile 에도 적용하여 재부팅 후에도 유지되게 하라는 뜻

만약 scope 옵션을 사용하지 않을 경우 both 가 기본 모드

오라클 설치후 사용하기전에 반드시 파라미터 파일을 변경해줘야 함

2. 다양한 방법으로 Instance Open 하기

앞서 살펴본 순서와 같이 오라클 시작 순서는 NOMOUNT -> MOUNT -> OPEN 3단계가 있다.

* 사용중인 파일은 절대로 이동 및 복사를 하면 안됨 ( pfile 은 예외 )

- 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 하기 ( 허가 받은 사용자만 접속할 수 있도록 하는 mode )

SYS> STARTUP RESTRICT ;

Restricted Session이란 권한이 있어야만 접속 가능

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

SYS> ALTER SYSTEM ENABLE RESTRICTED SESSION ;

SYS> ALTER SYSTEM DISABLE RESTRICTED SESSION ;

3. Oracle Instance 종료하기

(1) Shutdown 의 4가지 옵션

1. NORMAL ( 기본 옵션 )

- 명령어를 실행하였을 때 접속중인 사용자들이 모두 접속을 종료할 때까지 기다렸다가 종료하는 옵션,

사용자가 접속을 종료하지 않으면 Instance는 종료되지 않음

2. TRANSACTIONAL

- 사용자 스스로 접속종료를 기다려 주지않고 강제로 접속을 중단시킨 후 Instance를 종료

- 접속을 중단시키는 시점은 사용자가 수행중인 Transaction이 끝나는 시점

- Transaction 이 끝나는 시점 : 사용자가 트랜잭션을 종료시키는 명령어 (DML, DCL, TCL) 을 수행할 때

3. IMMEDIATE

- 사용자의 행동에 상관없이 즉시 접속을 강제 종료

- 접속이 종료되는 시점까지 사용자가 수행한 작업중 Commit 이 완료된 데이터를 D.B 버퍼캐쉬에서 찾아 데이터 파일로 저장해 주고, Commit 이 되지않은 작업들은 모두 Rollback 시킨후 Instance 종료

4. ABORT

- immediate 옵션과 같이 즉시 접속을 강제 종료, 다른점은 수행한 작업을 저장하지도,Rollback 시키지도않고 종료

- 비정상 종료 즉 Instance Crash라고 부르며 이렇게 꺼진 Instance는 다시 Startup 될때 SMON이 Instance Recovery를 수행해서 복구해야 함

실습 1. Parameter file 생성 및 관리

모든 Parameter file 이 삭제되어 없을 경우 Oracle 설치시 기본값을 가지고 만들어지는 최초 parameter file 을 활용해 pfile생성

[oracle@localhost ~]$ sqlplus / as sysdba

SQL> shutdown immediate

SQL> !

[oracle@localhost ~]$ cd $ORACLE_HOME/dbs

[oracle@localhost dbs]$ ls

hc_DBA0.dat init.ora orapwtestdb spfiletestdb.ora hc_testdb.dat 1KTESTDB snapcf_testdb.f

위와 같이 9i 이후부터 오라클 설치시 기본적으로 spfile 이 존재

[oracle@localhost dbs]$ rm -rf spfiletestdb.ora <- spfile 을 삭제하는 장애를 만듬

[oracle@localhost dbs]$ exit

SQL> startup <- 초기화 파라미터 파일을 삭제했기 때문에 에러가 생김

ORA-01078 : failure in processing system parameters

LRM-00109 : could not open parameter file '/app/oracle/product/11g/dbs/inittestdb.ora'

위와 같이 초기화 파라미터 파일이 삭제되서 생기는 에러는 아래 방법과 같이 해결해주면 된다

1. 현재 parameter file 확인 - parameter 파일이 없는것을 확인할 수 있다.

[oracle@localhost dbs]$ ls

hc_testdb.dat init.ora initdw.ora 1KTESTDB orapwtestdb

2. 이미 만들어져 있는 원본 pfile 찾기

[oracle@localhost ~]$ cd $ORACLE_bASE/admin/testdb/pfile

[oracle@localhost pfile]$ ls

init.ora.8262014183816 <- 원본 pfile, 뒤에 숫자는 server 마다 다름

3. 원본 pfile 복사

[oracle@localhost ~]$ cd $ORACLE_HOME/dbs

[oracle@localhost dbs]$ cp $ORACLE_BASE/admin/testdb/pfile/init.ora.8262014183816 inittestdb.ora

[oracle@localhost dbs]$ ls

hc_testdb.dat init.ora initdw.ora 1KTESTDB orapwtestdb inittestdb.ora <-pfile

4. test

[oracle@localhost dbs]$ exit

SQL> startup pfile 을 복사해 왔기 때문에 제대로 startup 되는것을 볼 수 있다

※현재 pfile을 사용했는지 spfile 을 사용했는지 알고 싶을때

SQL> show parameter spfile; 했을때 VALUE 부분에 값이 있으면 spfile 이고 없으면 pfile 이다.

실습 2. pfile, spfile 만들기

pfile 이 있고 spfile 이 없을 때

SQL> create spfile from pfile ;

spfile 이 있고 pfile 이 없을 때

SQL> create pfile from spfile ;

저작자표시 변경금지

'오라클 > 관리 실무' 카테고리의 다른 글

Oracle 저장구조 (0)	2014.10.23
Tablespace 와 Data File 관리하기 (2)	2014.10.21
Redo Log 관리하기 (0)	2014.10.20
Control File 관리하기 (0)	2014.10.20
Oracle Background Process (0)	2014.10.17
SQL 문장의 실행 원리 (0)	2014.10.17
Oracle Architecture (2)	2014.10.17

Oracle Background Process

김포춘 2014. 10. 17. 19:17

2014. 10. 17. 19:17

User Process	사용자가 작성한 sql 문장을 server process로 전달해 주고 결과를 가져오는 프로세스
Server Process	user process 가 전해준 sql 문장을 실제 수행하는 프로세스
Background Process	oracle server 가 시작되면 자동으로 시작되어 운영과 유지를 담당하는 프로세스

1. 필수 Background process

(1) DBWR( Database Writer )

- Database Buffer Cache 에서 변경된 블록을 데이터 파일로 저장하는 역할

DBWR 이 DB Buffer Cache 의 Dirty Buffer 의 내용을 파일에 내려쓰는 경우

1. Checkpoint 신호가 발생했을 때

2. Dirty Buffer 가 임계 값을 지났을 때 -- 1/3 데이터를 사용했을 때

3. Time out 이 발생했을 때 -- 3초동안 작업이중지됬을때

(2) LGWR( Log Writer )

- 데이터가 변경되면 Server Process 가 변경 내역( Change Vector )을 Redo Log Buffer 에 기록

그리고 LGWR은 Redo Log Buffer 에 있는 내용을 디스크의 Redo Log File 로 저장

1. Commit 이 발생했을 때

2. Redo Log Buffer 에 데이터가 1/3 이 찼을 때

3. 변경량이 1M 가 되었을 때

4. 3초 마다

5. DBWR이 내려쓰기 전에

Redo Log Buffer 에 있는 내용을 Redo Log File 에 내려쓰는 이유 : 서버가 꺼지면 데이터가 다 날아갈 수 있기 때문에

(3) PMON( Process Monitor ) - 서버프로세스들의 대장

- PMON 은 모든 서버 프로세스들을 감시하고 비정상적으로 종료된 프로세스가 있다면 관련 복구작업등을 하는 역할

(4) SMON ( System Monitor )

주요 업무

- 인스턴스가 비정상 종료 되었을 경우( Instance Crash ) 인스턴스를 시작할 때 Clean Up 하는 역할( Instance Recovery )

- 어떤 사정이 ( File 에러나 Tablespace 가 offline 상태 ) 있어서 Instance Recovery 과정에서 누락된 Transaction 을 Recovery 하는 역할도 담당

- 비정상 종료된 Transaction 이 사용 중이던 Temporary Segment 를 Clean Up 하는 역할

- Dictionary Managed Tablespace 에서 Free extents들을 모아주는 역할

비정상 종료후 Instance Recovery 하는 과정

1. Parameter file 을 읽어서 nomount 단계에서 instance 를 생성

2. Mount 단계에서 Control file 의 내용을 확인해서 Instance Crash 상황임을 확인

3. Redo log file 에서 비정상 종료 되기전수행하던 작업을 다시 수행 ( Roll Forward 라고함 )

이때 중요한 것은 종료되기전에 작업을 수행했을 때 commit을 했어도 commit후에 수행했던 작업들도 다시 수행됨

예) 1. a 입력

2. b 입력

3. commit

4. c 입력

5. db 비정상 종료됨

이경우 4번도 수행됨

4. database 를 open

5. commit 안된 4번작업을 취소 ( roll backward라고함)

(5) CKPT ( Checkpoint Process )

CKPT Process 는 DBWR 에게 Checkpoint 신호를 전달해 주며 Control File 과 Data file Header 에 해당 Checkpoint 정보를 기록하는 역할

checkpoint 정보에는 checkpoint 위치와 SCN, 해당 내용을 담고있는 Redo log 내용의 위치값을 담고 있음

저작자표시 변경금지

'오라클 > 관리 실무' 카테고리의 다른 글

Oracle 저장구조 (0)	2014.10.23
Tablespace 와 Data File 관리하기 (2)	2014.10.21
Redo Log 관리하기 (0)	2014.10.20
Control File 관리하기 (0)	2014.10.20
ORACLE 시작하기 & 종료하기 (0)	2014.10.20
SQL 문장의 실행 원리 (0)	2014.10.17
Oracle Architecture (2)	2014.10.17

SQL 문장의 실행 원리

김포춘 2014. 10. 17. 16:55

2014. 10. 17. 16:55

(1) SQL 문장의 실행 원리

- User Process : 쿼리를 치는 프로그램으로 sqlplus, toad, orange 등이 있다

- Tnsname.ora : 주소록과 같은 개념으로 저장된 정보를 통해 Database를 찾아감 ( IP, PORT, SID 등 저장되있음 )

Oracle Client 를 설치해야 생김. netca 명령어를 실행

- Server Process : 실제 작업을 수행하는 역할 , User Process 로부터 쿼리 내용 전달 받아서 D.B에서 찾음

- Connection : 유저 프로세스와 서버 프로세스가 만나서 쿼리 전달받는 것

- Session : 서버 프로세스가 유저 프로세스로부터 쿼리를 전달 받아 D.B에서 작업을 수행하는 것

과정 설명

- 유저 프로세스에서 쿼리를 작성 -> 1. Tnsname.ora 에 저장된 정보를 확인해 리스너로 이동 -> 2. 리스너에서 D.B 에 있는 서버 프로세스를 호출 -> 3,4 서버 프로세스와 유저프로세스가 만나서 쿼리내용 전달 -> 5. 서버프로세스가 D.B로 가서 작업을 수행해서 결과값 출력

예 ) 유저프로세스 : 면회자 , 리스너 ; 위병 병사 , D.B : 군대 , 서버프로세스 : 당직사관

-> 면회자가 면회할 대상의 정보(쿼리)를 갖고 네비게이션에 주소(Tnsname.ora)를 입력해 군대로 출발 (1번단계) -> 위병소에서 병사(리스너)에게 면회왔다고 면회요청 (2번단계) -> 군대에서 당직에게 면회자 있다고 연락해 호출(3번단계)

-> 당직에게 면회당사자 정보를 전달해줌(connection) (4번단계) -> 당직은 군대에서 면회당사자 정보로 병사찾음 (session)(5번단계) -> 병사 찾아서 면회하러 나감 (출력)

위와 같은 과정을 2번째 부터는 1,2,3 번의 과정을 건너뛰고 Tnsname.ora 에서 바로 Server Process 단계로 가는 A 과정

-> 5번 과정으로 작업 수행 ( DB로 처음 연결하는게 첫번째라고 행함 )

(2) Select 문장의 실행 원리

1. Parse

- Server Process 가 User Process 로부터 SQL 문장을 받아서 SQL Parser 를통해 각 SQL문에 쓰인 키워드나 컬럼명등을 분석해 Parse Tree 라는 것을 생성

- Parse Tree를 만드는 과정에서 문법검사등을 하고 이상이 없으면 의미검사를 한다.

-> 문법검사 단계에서 스펠링이 틀리면 Parse Tree 생성단계에서 오류가 생기게 되고

의미검사 단계에서 잘못되면 Semantic Check 단계에서 에러가 발생

- Dictionary Cache (Row Cache) : 해당 문법이 맞는지 틀린지 여부나 해당 테이블이 있는지 없는지 여부를 알기위해

데이터 딕셔너리를 사용하게 되고 자주 사용하는 데이터 딕셔너리를 캐싱해 두어

성능을 높이는 역할을 하는곳

- 실행계획

- 검사단계를 마치고 실행계획단계로 넘어가는데 Shared Pool 의 Library Cache를 검사해 기존에 사용했던 sql 문장이나 pl/sql 문장이 있으면 soft parse를 실행해 다음단계로 넘어감

- 기존에 사용한적이 없어서 soft parse를 실행하지 못하면 Shared Pool 의 Dictionary Cache를 참조하여 실행계획 생성

- soft parse 를 항상 먼저 실행하고 실패했을 경우에 hard parse를 실행

RBO ( Rule Based Optimizer ) : 서버 프로세스가 실행계획을 세워 달라고 요청이 들어오면 이름대로 미리 정해져 있는 규칙을 사용해 실행 계획을 세움 ( 융통성 없는 방식 )

CBO ( Cost Based Optimizer ) : 규칙을 기반으로 실행계획을 세우되 상황에 맞게 계획세움 ( 대부분이 CBO 사용 )

2. Bind

- 실행 계획을 1개만 생성 한 후 바인드 변수 값을 바꾸어 여러 번 실행하는 것

3. Execute

- Database Buffer Cache 에서 서버프로세스가 원하는 블록이 있는지 검사

- 원하는 블록이 없을시 데이터파일에서 서버프로세스가 원하는 블록을 Databse Buffer Cache 로 복사

4. Fetch

- 복사한 데이터 블록 에서 사용자가 요청한 데이터만 골라내는 과정

>만약 사용자가 정렬(sort) 등의 추가 작업을 요구했을 경우 Fetch 과정에서 sort를 완료한후 PGA 공간으로 데이터 보내서 정렬 발생 ( PGA 공간은 서버 프로세스 별로 각각 독립적으로 할당되어 사용 )

저작자표시 변경금지

'오라클 > 관리 실무' 카테고리의 다른 글

Oracle 저장구조 (0)	2014.10.23
Tablespace 와 Data File 관리하기 (2)	2014.10.21
Redo Log 관리하기 (0)	2014.10.20
Control File 관리하기 (0)	2014.10.20
ORACLE 시작하기 & 종료하기 (0)	2014.10.20
Oracle Background Process (0)	2014.10.17
Oracle Architecture (2)	2014.10.17

Oracle Architecture

김포춘 2014. 10. 17. 15:21

2014. 10. 17. 15:21

(1) Oracle Server 전체 구조

Oracle 프로그램을 설치후 실행시키면 메모리와 디스크에 Oracle 만의 특별한 구조를 생성

이렇게 생성되는 구조를 Oracle Server 라고 함

메모리 부분에 생성되는 구조 (Instance) ( Instance 구조는 오라클 버전에 따라 다르다 )

디스크 부분에 생성되는 구조 (Data base)

- 데이터가 저장되는 데이터 파일 (Data File)

- DB전체의 관리정보가 들어있는 컨트롤파일 (Control files)

- 장애 복구시 사용되는 리두 로그파일 (Redo log files)

<Oracle Server 구조>

위 그림을 좀 더 자세하게 살펴 보자

< Oracle Instance 와 Database >

(2) Oracle Instance 의 할당 및 관리

Instance - SGA : 실제 작업들이 수행되는 공간

- Background process : Oracle Server 가 잘 운영 되도록 데이터베이스와 인스턴스를 돌아다니면서

데이터를I/O 해주는 역할(DBWn, LGWR, PMON, SMON, CKPT, ETC..)

Instance 생성 과정

[ Oracle 데이터베이스가 종료되어있는 상태에서 관리자가 DB에 접속해서 ORACLE을 시작(Start up) 한다고 가정]

- Startup 요청을 받은 최초의 Oracle Server Process가 초기화 파라미터 (pfile 이나 spfile) 에 적혀있는 설정을 참고해 OS Kernel 에게 공유 메모리를 사용할 수 있도록 할당 요청

- 할당 요청을 받은 OS Kernel은 저장되어 있는 OS Kernel 파라미터를 조회해서 그 파일들에 설정되어 있는 공유 메모리를 할당 ( kernel - 리눅스 : /etc/sysctl.conf , 솔라리스 : /etc/system 파일에 기록 )

요약 : 파라미터파일의 설정값을 커널에 요청 -> 커널에 설정되어 있는 값을 전송 ( ※ 요청값이 1G 인데 커널 설정이 500MB 만 허락하도록 되어있으면 1G 허락을 해주지않고 500MB 만 허락해줌 )

------------------------------------------------------------------------------------------

OS kernel 은 RAM의 일부를 Oracle에 할당해 준 후에도 해당 메모리 공간을 다른 프로그램이 사용할 수 없도록 관리함

하나의 메모리 블록을 여러 프로그램이 동시에 중복 사용하는 사태를 막기 위해 세마포어를 사용

세마포어 : 깃발이라는 뜻으로, 자원의 현재 사용여부를 표시

세마 포어는 보통 한개씩 쓰지않고 세트로 묶어서 여러 개씩 사용함

- 프로세스는 메모리 블록을 사용하기 전에 세마포어의 상태를 먼저 확인함. ( set 되어있을시 unset 될때까지 대기 or unset 되어있는다른 메모리블록을 찾음)

- 메모리의 중복 사용을 막기위해 세마포어를 set 상태로 해두고 다른 프로세스의 접근을 막는다.

- 메모리 블록이 unset 이 되면 프로세스는 해당 메모리 블록에서 작업을 수행할 수 있고 메모리 블록을 사용하게 되면 세마포어를 다시 set 상태로 세팅

(3) SGA의 주요 구성 요소

1. Database Buffer Cache ★중요★

데이터의 조회와 변경 등의 실제 작업이 일어나는 공간

- Pinned Buffer : 다른 사용자가 현재 사용중인 buffer 블록, 데이터 변경중 (commit, rollback 안된상태)

- Dirty Buffer : 다른 사용자가 내용을 변경후 저장하지 않은 buffer 블록, 데이터 변경완료 -> 저장 안함

(commit , rollback 된 상태)

- Free Buffer : 사용하고 있지 않은 buffer 블록 or Dirty Buffer 였다가 저장완료된 buffer 블록

데이터 변경완료 -> 저장 완료

예) 사용자 - 손님 , DataBase Buffer Cache - 매장, Database - 창고, Server Process - 직원

버퍼캐쉬에 데이터가 있을 때

사용자가 쿼리 날림 -> 서버 프로세스가 D.B 버퍼캐쉬에서 데이터 찾음 -> 데이터 출력

버퍼캐쉬에 데이터가 없을 때

사용자가 쿼리 날림 -> 서버 프로세스가 D.B 버퍼캐쉬에서 데이터 찾음 -> 데이터 없어서 서버프로세스가 D.B로 가서 데이터 찾음 -> 찾은 데이터 D.B 버퍼캐쉬로 복사 -> 출력

버퍼 블록들의 상태를 관리 - LIST 를 만들어 사용

LRU 알고리즘 : SGA가 100MB 인데 변경 하려는 자료가 150MB 일경우 SGA의 일부분을 덮어 써야 하는데 이럴 경우 가장 사용이 안된것을 덮어쓰는 알고리즘

LIST : 버퍼 블록의 사용여부가 적혀 있음 ( 한번에 한명만 볼 수 있음)

- LRU List

- 메인 리스트 : 사용된 buffer 리스트

★- 서브 리스트 : 미 사용된 buffer 들이나 , DBWR 에 기록된 buffer 리스트 ( Free list )

- LRUW List

★- 메인 리스트 : 변경된 buffer 들의 리스트 ( Dirty list )

- 서브 리스트 : 현재 DBWR 에 기록중인 buffer 리스트

LATCH : 번호표 의 의미 , 프로세스가 한꺼번에 사용하려고 할 때 순서를 관리해주기위해 사용

2. Redo Log Buffer

- 데이터 변경사항을 기록 (장부, 일지)

Redo : 작업을 했는데 문제가 생기면 다시 작업

Undo : 작업을 했는데 문제가 생기면 취소 ( rollback )

3. SGA

SGA 각 구성요소의 크기에 따라 오라클 성능이 달라짐

대체로 성능을 높이기 위해 Database Buffer Cache 의 크기를 높여주면 빨라짐

Database Buffer Cache 의 크기를 100MB로 변경할 경우

SYS> alter system set DB_CACHE_SIZE=100M ;

메모리 관리 방법

8i	9i
static	dynamic
메모리 변경후 재부팅 해야 적용	메모리 변경후 즉시 적용

그래뉼 - 오라클에서 만든 메모리를 할당하는 새로운 단위

4. PGA

프로세스들이 개별적으로 사용하는 메모리 공간

오라클 메모리 - SGA = 공유해서 사용 ex) 운동장

- PGA = 개별 사용 ex) 사물함

저작자표시 변경금지

'오라클 > 관리 실무' 카테고리의 다른 글

Oracle 저장구조 (0)	2014.10.23
Tablespace 와 Data File 관리하기 (2)	2014.10.21
Redo Log 관리하기 (0)	2014.10.20
Control File 관리하기 (0)	2014.10.20
ORACLE 시작하기 & 종료하기 (0)	2014.10.20
Oracle Background Process (0)	2014.10.17
SQL 문장의 실행 원리 (0)	2014.10.17

PREV 이전 1 NEXT 다음

오라클 스터디