반응형

               < 오라클 저장구조 >



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
반응형

1. Data file , Redo log file, Control file 을 아래처럼 이동시키세요.

/app/oracle/disk1/control01.ctl , redo01_a.log , redo02_a.log , redo03_a.log

/app/oracle/disk2/control02.ctl , redo01_b.log , redo02_b.log , redo03_b.log

/app/oracle/disk3/control03.ctl , undo01.dbf

/app/oracle/disk4/system01.dbf , sysaux01.dbf

/app/oracle/disk5/users01.dbf , example01.dbf 


2. 20g 하드 하나 추가해서 /data2 로 마운트 oracle 계정 소유로 변경한 후 spfile을 사용해 구성

/data2/disk1/control01.ctl, redo01_a.log,redo02_a.log,redo03_a.log

       disk2/control02.ctl, redo01_b.log,redo02_b.log,redo03_b.log

       disk3/control03.ctl, system01.dbf, sysaux01.dbf

       disk4/undotbs01.dbf , example01.dbf

       disk5/users01.dbf



3. pfile 사용

/home/oracle/disk1/system01.dbf, sysaux01.dbf

    disk2/undotbs01.dbf , users01.dbf , example01.dbf

    disk3/ctrl01.ctl, redo01_a.rdo, redo02_a.rdo, redo03_a.rdo

    disk4/ctrl02.ctl, redo01_b.rdo, redo02_b.rdo, redo03_b.rdo

    disk5/ctrl03.ctl, redo01_c.rdo, redo02_c.rdo, redo03_c.rdo

반응형
반응형

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 만을 저장하는 segmentundo 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 DatabaseClean 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 : 변경되는 내용이 있을경우 모두 기록해 두었다가 장애를 대비하는 기능

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 vectorRedo 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 파일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
반응형

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

※ 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
반응형


 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
반응형

(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 PoolDictionary 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
반응형

(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
반응형

PL/SQL 익명 블록과 서브 프로그램의 차이

PL/SQL 블록에 이름을 지정하고 생성해서 저장해 두었다가 필요할 경우에 호출ㅇ해서 사용할 수 있도록 하는데 이를 서브프로그램 또는 프로그램 단위라 부른다.


대표 적인 유형으로 프로시저(PROCEDURE), 함수(FUNCTION), 패키지(PACKAGE), 트리거(TRIGGER) 가 있다



1. PROCEDURE (프로시저)

특정 작업을 처리하는 이름있는 PL/SQL BLOCK


프로시저 생성 - create procedure

프로시저 삭제 - drop procedure

프로시저 수정 - alter procedure


PROCEDURE 생성 문법 :

CREATE [OR REPLACE] PROCEDURE PROCEDURE_name

[( parameter1 [mode1] datatype1,

    parameter2 [mode2] datatype2,

    ...)]

IS |AS

- OR REPLACE : 생성하고자 하는 PROCEDURE 가 기존에 동일 이름으로 존재할 경우, 기존의 내용을 현재 내용으로 수정하는 옵션, 이 옵션은 해당 이름의 PROCEDURE를 삭제한 후 다시 생성


- PROCEDURE_name : 생성하고자 하는 PROCEDURE 명으로 스키마 내에서는 유일하게 명명 되어야 한다


- parameter : PROCEDURE 를 실행할 때 호출 환경과 PROCEDURE 간 값을 주고 받을 때 사용되는 파라미터(매개변수)로 모드(IN,OUT,IN OUT) 에 따라 역할이 다르게 수행

PROCEDURE 생성시 선언부에 선언된 파라미터를 형식 파라미터

PROCEDURE 실행시 형식 파라미터에 실제 값이나 변수를 할당/대응 하는 파라미터를 실행 파라미터


- mode : 매개변수의 역할을 결정짓는 것

- IN 모드 : 사용자로부터 값을 입력받아 PROCEDURE 로 전달해 주는 역할을 하는 모드 (기본값으로 생략가능)

- OUT 모드 : PROCEDURE 에서 호출환경(SQL PLUS, 다른 프로시저 등)으로 값을 전달하는 역할

이 모드로 설정된 매개변수는 프로시저 내에서는 읽을 수 없으며, 값을 저장하기만 하는 지역변수처럼 사용 

            호출 환경에서는 이 매개변수로부터 값을 전달받기 위해 환경변수가 선언되어 있어야 함

      - IN OUT 모드 : 설정된 매개변수는 호출환경과 프로시저 간에 값을 주고 받는 지역변수로 사용되며 읽기,쓰기가능



생성된 PROCEDURE 의 내용을 확인할 때는 USER_SOURCE 딕셔너리를 활용


실습 1. 부서번호가 20번 인 사람들의 job 을 'CLERK' 으로 변경하는 PROCEDURE

SQL> CREATE OR REPLACE PROCEDURE update_20

2    IS

3    BEGIN

4        UPDATE emp

5        SET job = 'CLERK'

6        WHERE deptno=20;

7    END ;

8    /

PROCEDURE created


SQL>EXEC update_20;  --프로시저를 실행하려면 execute(줄여서 exec) 명령어로 프로시저 이름을 적고 실행


실습 2. 사번을 입력 받아 급여를 인상하는 PROCEDURE

SCOTT>SELECT empno, ename, sal FROM emp WHERE empno=7902 ;


EMPNO    ENAME    SAL

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

   7902      FORD      3000  <- 변경전 SAL 값이 3000


SCOTT>CREATE OR REPLACE PROCEDURE up_sal

2    (vempno  IN  emp.empno%TYPE)   -- 입력값을 저장할 변수 vempno 를 선언, IN 모드 기본값이라 생략가능

3    IS

4    BEGIN

5        UPDATE emp SET sal=5000

6        WHERE empno=vempno ;

7    END ;

8    /

PROCEDURE created


SCOTT>exec up_sal(7902) ; --PROCEDURE 를 실행할 때 수정할 사원번호를 함께 입력


EMPNO    ENAME    SAL

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

   7902      FROD      5000 <- 변경후 급여가 500으로 바뀜


※ DB로 가서 데이터를 PL/SQL로 가져올 때 사용하는 변수는 3번째 줄과 4번째줄 사이 IS 와 BEGIN 사이에 입력하고,

2번 줄의 변수는 사용자로부터 데이터를 입력 받아서 PL/SQL 로 가져오는 역할을 하는 변수


실습 3. 사번을 입력 받아 그 사원의 이름과 급여를 출력하는 PROCEDURE

SCOTT>CREATE OR REPLACE PROCEDURE ename_sal

2    (vempno emp.empno%TYPE)

3    IS

4        vename emp.ename%TYPE ;

5        vsal       emp.sal%TYPE ;

6    BEGIN

7        SELECT ename, sal

8        INTO vename, vsal

9        FROM emp

10      WHERE empno=vempno ;

11      DBMS_OUTPUT.PUT_LINE('사원명은 '||vename||' 입니다') ;

12      DBMS_OUTPUT.PUT_LINE('급여는 '||vsal||' 입니다') ;

13    END ;

14    /


SCOTT>exec ename_sal(7902) ;

-> 사원명은 FOR 입니다

급여는 5000 입니다


실습 4. OUT 모드 파라미터 사용 예

SCOTT>CREATE OR REPLACE PROCEDURE info_prf

2    (v_profno    IN    professor.profno%TYPE,

3     v_name     OUT professor.name%TYPE,     --이름값을 저장할 변수

4     v_pay        OUT professor.pay%TYPE )       --급여를 저장할 변수 

5    IS

6    BEGIN   

7        SELECT    name, pay INTO v_name, v_pay

8        FROM       professor

9        WHERE     profno = v_profno ;

10    END info_prof ;

11    /


위에서 작성한 PROCEDURE 를 호출해서 값을 가져오는 익명 블록 PL/SQL 문을 작성

SCOTT>DECLARE

2    v_name professor.name%TYPE ;

3    v_pay    professor.pay%TYPE ;

4    BEGIN

5    info_prof(1001,v_name,v_pay) ;

6    DBMS_OUTPUT.PUT_LINE(v_name||' 교수의 급여는 '||v_pay||'입니다') ;

7    END ;

8    /

-> 조인형 교수의 급여는 550 입니다


위 예에서 5번 줄을 보면 프로시저를 호출 할 때 값을 주는 것을 알 수 있습니다.


또는 아래와 같이 DBMS_OUTPUT.PUT_LINE 문장으로 사용도 가능

SCOTT>DECLARE

2    v_name professor.name%TYPE ;

3    v_pay    professor.pay%TYPE ;

4    BEGIN

5    info_prof(1001,v_name,v_pay) ;

6    DBMS_OUTPUT.PUT_LINE('이    름:'||v_name) ;

7    DBMS_OUTPUT.PUT_LINE('급    여:'||v_pay) ;

8    END ;

9    /

-> 이    름 : 조인형

     급    여 : 550


또 다른 방법으로는 별도의 변수를 선언해서 값을 수행해서 받은 후 출력할 수 도 있습니다.

SCOTT>VARIABLE name VARCHAR2(10)

SCOTT>VARIABLE pay    NUMBER

SCOTT>EXEC info_prof(1001, :name, :pay) ;


SCOTT>PRINT name pay


NAME

-----------

조인형

PAY

-----------

550



2. FUNCTION (함수)

함수 생성 : CREATE FUNCTION

함수 삭제 : DROP FUNCTION

함수 수정 : ALTER FUNCTION

에러 발생시 : SHOW ERRORS 확인


함수 예제 1. 부서번호를 입력 받아 최고 급여액을 출력하는 함수

SQL>CREATE OR REPLACE FUNCTION f_max_sal

2        (v_deptno    emp.deptno%TYPE)

3    RETURN NUMBER

4    IS

5        max_sal    emp.sal%TYPE ;

6    BEGIN

7        SELECT max(sal) INTO max_sal

8        FROM emp

9        WHERE deptno = v_deptno ;

10      RETURN max_sal ; -- 이부분의 데이터 형이 위 3번 줄의 형과 같아야 함

11    END ;

12    /


SQL>SELECT f_max_sal(10) FROM dual ;


F_MAX_SAL(10)

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

5000


SQL>SELECT f_max_sal(20) FROM dual ;


F_MAX_SAL(20)

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

 3000


함수 예제 2. 부서번호를 입력 받은 후 해당 부서의 인원수를 구해주는 함수

SQL>CREATE OR REPLACE FUNCTION count_mem

2        (v_deptno    NUMBER)

3    RETURN    NUMBER

4    IS

5        total_count    NUMBER

6    BEGIN

7        SELECT    count(*) INTO total_count

8        FROM    emp

9        WHERE    deptno = v_deptno ;

10      RETURN total_count ;   -- 이 부분의 데이터 형이 위 3번 줄 데이터 형과 같아야 함

11    END;

12    /        


SQL> SELECT DISTINCT deptno, COUNT_MEM(deptno)

2    FROM    emp ;


DEPTNO    COUNT_MEM(DEPTNO)

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

30                            6

20                            5

10                            3


함수 예제 3. 부서번호를 입력 받아 부서별로 평균 급여를 구해주는 함수

SQL>CREATE OR REPLACE FUNCTION avg_sal

2        (v_deptno    emp.deptno%TYPE)

3    RETURN    NUMBER

4    IS

5        avg_sal    NUMBER ;

6    BEGIN

7        SELECT    ROUND(AVG(sal),2) INTO avg_sal

8        FROM       emp

9        WHERE     deptno = v_deptno ;

10      RETURN    avg_sal ;

11    END ;

12    /


SQL> SELECT DISTINCT deptno, AVG_SAL(deptno)

2    FROM    emp ;


DEPTNO    AVG_SAL(DEPTNO)

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

30                    1566.67

10                    2916.67

20                         2175


함수 예제 4. 사원번호를 입력 받아 해당 사원의 부서명을 알려주는 함수

SQL>CREATE OR REPLACE FUNCTION f_dname

2    (v_empno    IN    emp.empno%TYPE)

3    RETURN    varchar2

4    IS

5        v_dname    dept.dname%TYPE ;

6    BEGIN

7        SELECT    dname INTO v_dname

8        FROM     dept

9        WHERE   deptno = ( SELECT    deptno

   FROM       emp

   WHERE     empno = v_empno) ;

10    RETURN v_dname ;

11    END ;

12    /


SCOTT> SELECT ename, deptno, F_DNAME(empno) "DNAME"

2    FROM    emp ;


ENAME    DEPTNO    DNAME   

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

홍길동            10       ACCOUNTING

일지매            30       SALES

SMITH            20       RESEARCH

ALLEN            30       SALES

.......


함수 예제 5. 생성된 함수 조회하기

SCOTT>SELECT text

2    FROM    user_source

3    WHERE    type='FUNCTION'

4    AND name = 'F_MAX_SAL' ;


TEXT

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

function f_max_sal

(s_deptno emp.deptno%TYPE)

return NUMBER

IS

max_sal emp.sal%TYPE ;

begin

select max(sal) into max_sal

from    emp

where    deptno=s_deptno ;

return max_sal ;

end ;



3. ORACLE PACKAGE (패키지) 

연관성이 높은 함수나 PROCEDURE 를 하나의 그룹으로 묶어서 관리하고 사용하는 개념


PACKAGE 는 PACKAGE 선언부(Spec)와 PACKAGE 몸체부(body) 로 구성

- PACKAGE 선언부 : 해당 패키지에 사용될 함수나 프로시저, 변수 등에 대한 정의를 선언

- PACKAGE 몸체부 : 선언부에서 선언된 함수나 ㅡㅍ로시저 등이 실제 CODE로 구현되는 부분


만약 생성된 PACKAGE의 선언부가 변경되었다면 무조건 PACKAGE 몸체부는 재 생성해야 하며, 패키지를 참조(호출)하는 서브 프로그램들도 재 번역(recompile)해야한다. 반대로 패키지 몸체부만 변경되는 경우라면 몸체부만 재생성하면 된다.


1. 패키지 실행 하기

생성된 패키지 오브젝트에 대한 실행 권한을 가진 사용자만이 패키지를 호출하여 실행 할 수 있다

2. 패키지 삭제

패키지 선언부와 몸체부 모두 삭제 : DROP PACKAGE PACKAGE_name ;

패키지 몸체부만 삭제 : DROP PACKAGE BODY PACKAGE_name ;


사용 예 1. Emp table 에서 총 급여 합계와 평균 급여를 구하는 emp_sal_mgr PACKAGE 입니다.

패키지 선언부

SQL>CREATE OR REPLACE PACKAGE emp_sal_mgr

2    AS

3        PROCEDURE    emp_sal_sum ;

4        PROCEDURE    emp_sal_avg ;

5    END emp_sal_mgr ;

6    /


패키지 몸체부

SQL>CREATE OR REPLACE PACKAGE BODY emp_sal_mgr 

2    AS

3        PROCEDURE    emp_sal_sum

4    IS

5        CURSOR    emp_total_sum    IS

6            SELECT COUNT(*), SUM(NVL(sal,0))

7            FROM    emp ;

8        total_num    NUMBER ;

9        total_sum    NUMBER ;

10    BEGIN

11        OPEN emp_total_sum ;

12        FETCH emp_total_sum INTO total_num, total_sum ;

13        DBMS_OUTPUT.PUT_LINE('총인원수:'||total_num||', 급여합계: '||total_sum) ;

14        CLOSE emp_total_sum ;

15    END emp_sal_sum ;   --emp_sum 합계 구하는 PROCEDURE 끝


16    PROCEDURE emp_sal_avg  --emp_avg 평균 구하는 PROCEDURE 시작

17    IS

18        CURSOR    emp_total_avg    IS

19            SELECT COUNT(*),AVG(NVL(sal,0))

20            FROM    emp ;

21        total_num NUMBER ;

22        total_avg NUMBER ;

23    BEGIN

24        OPEN emp_total_avg ;

25        FETCH emp_total_avg INTO total_num, total_avg ;

26        DBMS_OUTPUT.PUT_LINE('총인원수:'||total_avg||', 급여합계: '||total_avg) ;

27        CLOSE emp_total_avg ;

28    END emp_sal_avg ;       --평균 구하는 PROCEDURE 끝

29    END emp_sal_mgr ;       --PACKAGE 끝

30    /


생성된 패키지 실행

SQL>SET SERVEROUTPUT ON

SQL>EXEC emp_sal_mgr.emp_sal_sum ;          --패키지이름.프로시저 이름으로 실행


-> 총인원수 : 14, 급여합계 : 29025


SQL>EXEC emp_sal_mgr.emp_sal_avg ;

-> 총 인원수 : 14, 급여평균 : 2073.214287................

위 패키지 실행결과가 맞는지 sql을 직접 수행해서 검증

SQL>SELECT COUNT(*), SUM(NVL(sal,0)) FROM emp;


COUNT(*)    SUM(NVL(SAL,0))

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

   14                        29025


사용 예2. member 테이블에 등록되어 있는 회원들의 성별과 아이디 검색과 비밀번호를 검색해주는 패키지

사용자 정보가 들어있는 member 테이블을 사용하여 회원관리를 하는 PL/SQL 패키지를 생성

이 패키지는 아래와 같은 프로시저로 구성

- 사용자 이름을 입력 받은 후 남자인지 여자인지를 구분하는 pro_gender 프로시저

- 사용자 이름과 주민번호를 입력 받아 회원의 아이디를 찾아주는 pro_find_id 프로시저

- 사용자 아이디와 연상단어를 입력 받아 회원의 비밀번호를 알려주는 pro_fin_pw 프로시저

1. 패키지 선언부를 생성

SCOTT>CREATE OR REPLACE PACKAGE pkg_member

2    IS

3    PROCEDURE    pro_gender

4        (v_name IN     member.name%TYPE) ;

5    PROCEDURE    pro_find_id

6        (v_name IN     member.name%TYPE , 

7         v_jumin2 IN     member.jumin%TYPE) ;

8    PROCEDURE    pro_find_pw

9        (v_id IN    member.id%TYPE,

10       v_an IN    member.an_key_dap%TYPE ) ;

11    END pkg_member ;

12    /


2. 패키지 몸체부를 생성

SCOTT>CREATE OR REPLACE PACKAGE BODY pkg_member

2    AS

3    PROCEDURE    pro_gender(v_name IN member.name%TYPE) --성별 조회하는 프로시저,

4    IS

5        v_name2    member.name%TYPE ;

6        v_gender    CHAR(4) ;

7        v_count    NUMBER := 0 ;

8        exception_noname    EXCEPTION ;

9    BEGIN

10       SELECT    count(*) INTO v_count

11       FROM    member

12       WHERE    name = v_name ;

13       IF v_count = 0 THEN

14          RAISE exception_noname ;

15       ELSE

16           SELECT name, CASE WHEN SUBSTR(jumin,7,1) IN (1,3) THEN '남자' ELSE '여자' END

17           INTO v_name2, v_gender

18           FROM member

19           WHERE name = v_name ;

20           DBMS_OUTPUT.PUT_LINE(v_name2||' 님의 성별은 '||v_gender||' 입니다') ;

21        END IF ;

22    EXCEPTION

23        WHEN    exception_noname

24        THEN    RAISE_APPLICATION_ERROR(-20001,'그런 이름은 없습니다!') ;

25    END pro_gender ;      --성별 조회 PROCEDURE 종료


26    PROCEDURE    pro_find_id      --이름,주민번호 입력받아 아이디를 검색하는 프로시저

27        (v_name    IN    member.name%TYPE ,

28         v_jumion2  IN    member.jumin%TYPE )

29    IS

30        v_count    NUMBER := 0 ;

31        v_count2    NUMBER := 0 ;

32        v_name2    member.name%TYPE ;

33        v_id2          member.id%TYPE ;

34        exception_noname    EXCEPTION ;

35        excetpion_nojumin    EXCEPTION ;

36    BEGIN

37        SELECT count(*) INTO v_count

38        FROM    member

39        WHERE    name = v_name ;

40         IF v_count = 0 THEN

41            RAISE exception_noname ;      --이름이 없을 경우 에러를 발생시킴

42        ELSE

43            SELECT    count(*) INTO v_count2

44            FROM    member    

45            WHERE    jumin = v_jumin2 ;

46            IF v_count2 = 0 THEN

47                RAISE exception_nojumin ;       -- 주민번호가 없을 경우 에러를 발생시킴

48            ELSE

49                SELECT name, id INTO v_name2, v_id2

50                FROM member

51                WHERE    name = v_name ;

52            END IF ;

53          END IF ;

54        DBMS_OUTPUT.PUT_LINE(v_name2||' 님의 아이디는 '||v_id2||'입니다') ;

55    EXCEPTION

56        WHEN    exception_noname

57            THEN    RAISE_APPLICATION_ERROR(-20001,'입력하신 이름은 없습니다!') ;

58        WHEN    exception_nojumin

59            THEN    RAISE_APPLICATION_ERROR(-20002,'입력하신 주민번호는 없습니다!') ;

60    END pro_find_id ;         --아이디를 찾는 프로시저를 종료


61    PROCEDURE    pro_find_pw         --아이디와 연관단어를 입력하여 비번을 찾아내는 프로시저

62    (v_id    IN    member.id%TYPE ,

63     v_an    IN    member.an_key_dap%TYPE )

64    IS

65        v_count NUMBER := 0 ;

66        v_id2 member.id%TYPE ;

67        v_an_dap member.an_key_dap%TYPE ;

68        v_pw member.passwd%TYPE ;

69        exception_noid    EXCEPTION ;

70        exception_noan    EXCEPTION ;

71    BEGIN

72        SELECT    count(*) INTO v_count

73        FROM    member

74        WHERE    id = v_id ;

75    IF    v_count = 0 THEN

76        RAISE    exception_noid ; -- 아이디가 없으면 에러를 발생시킴

77    ELSE

78        SELECT    an_key_dap INTO v_an_dap

79        FROM    member

80        WHERE    id = v_id ;

81            IF v_an_dap = v_an    THEN

82                SELECT id, passwd INTO v_id2, v_pw

83                FROM    member

84                WHERE    id = v_id ;

85            ELSE    RAISE exception_noan ;

86         END IF ;

87        END IF ;

88        DBMS_OUTPUT.PUT_LINE('입력하신 '||v_id||' 의 비밀번호는 '||v_pw||' 입니다!') ;

89    EXCEPTION

90        WHEN    exception_noid

91            THEN    RAISE_APPLICATION_ERROR (-20003,'입력하신 아이디는 없습니다!') ;

92        WHEN    exception_noan

93            THEN    RAISE_APPLICATION_ERROR (-20003,'입력하신 연상단어가 틀립니다!') ;

94        END pro_find_pw ;        -- 프로시저를 종료

95    END pkg_member ;           -- 패키지 종료

96    /


- pro_gender PROCEDURE 테스트

SCOTT>EXEC pkg_member.pro_gender('홍길동') ;

-> 홍길동 님의 성별은 남자 입니다

SCOTT>EXEC pkg_member.pro_gender('유관순') ;

-> 유관순 님의 성별은 여자 입니다


- pro_find_id PROCEDURE 테스트

이름과 주민번호를 입력하면 아이디를 검색해주는 기능

SCOTT>EXEC pkg_member.pro_find_id('홍길동',7510231234567') ;

-> 홍길동님의 아이디는 simson 입니다


- pro_find_pw PROCEDURE 테스트

SCOTT>EXEC pkg_member.pro_find_pw('simson','홍길동') ;

-> 입력하신 simson 의 비밀번호는 a1234 입니다


4. TRIGGER (트리거)

테이블, 뷰, 스키마 또는 데이터베이스에 관련된 PL/SQL블록(또는 프로시저)으로 관련된 특정 사건(EVENT)이 발생될 때마다 묵시적(자동) 으로 해당 PL/SQL TRIGGER 블록이 실행됨


간단한 예로 테이블에 상품이 입고되면(EVENT 발생) 재고 테이블에 자동으로 재고가 증가하게 만드는것 


1) 주요 트리거 유형


(1) 단순 DML TRIGGER


- BEFORE TRIGGER

테이블에서 DML 이벤트를 TRIGGER 하기 전에 TRIGGER 본문을 실행 

즉 어떤 작업을 하기전에 TRIGGER를 먼저 수행 한 후 작업을 하는 경우

- AFTER TRIGGER

테이블에서 DML 이벤트를 TRIGGER 한 후에 TRIGGER 본문을 실행

- INSTEAD OF TRIGGER

TRIGGER 문 대신 TRIGGER 본문을 실행, 다른 방법으로는 수정이 불가능한 뷰에 사용


DML TRIGGER

- 문장 TRIGGER : 대상이 모든 행으로 영향을 받는 행이 없더라도 TRIGGER가 한 번은 실행 됨

예를들어 특정 테이블에 데이터를 입력할 수 있는 시간을 지정하는 경우 어떤 데이터가 들어오던지 간에 TRIGGER 를 적용


- 행 TRIGGER : 특정 행이 TRIGGER 이벤트의 영향을 받을 경우 해당 TRIGGER가 실행되고 영향을 받는 행이  없을 경우 TRIGGER 가 실행되지 않는다.

얘를들어 제품번호가 100번인 제품만 입력안되게 막고 싶을 경우 다른 행은 TRIGGER 와 상관없지만 100번 제품은 TRIGGER 가 적용되어 입력이 안됨


행 TRIGGER가 실행될 때 PL/SQL 런타임 엔진은 두개의 데이터 구조를 생성하고 채움

- OLD : TRIGGER 가 처리한 레코드의 원래 값(기존값) 을 저장

- NEW : 새로 입력되거나 변경되는 값을 의미


(2) 혼합 TRIGGER (11g 부터 추가)

여러가지 TRIGGER 를 하나로 만든 것으로 PL/SQL 의 패키지와 비슷한 개념


혼합 TRIGGER 가 주로 사용되는 시점

- 실행되는 문장 앞에

- 실행되는 문장이 영향을 주는 각 행 앞에

- 실행되는 문장이 영향을 주는 각 행 뒤에

- 실해오디는 문장 뒤에


혼합 TRIGGER 생성시 주의사항

- 혼합 TRIGGER 는 DML TRIGGER여야 하며 테이블이나 뷰에 정의해야 한다

- 혼합 TRIGGER의 본문은 PL/SQL 에서 작성한 혼합 TRIGGER 블록이어야 한다

- 혼합 TRIGGER 본문에는 초기화 블록이 포함될 수 없으므로 예외 섹션이 있을수 없다

- 한 섹션에서 발생하는 예외는 해당 섹션에서 처리되어야 한다

- :OLD:NEW 는 선언, BEFORE STATEMENT 또는 AFTER STATEMENT 섹션에 나타날수 없다

- BEFORE EACH ROW 섹션만 :NEW 값을 변경할 수 있다

- FOLLOWS 절을 사용하지 않으면 혼합 TRIGGER 의 실행 순서가 일정하지 않는다



2) TRIGGERR 구조

트리거는 트리거가 실행되는 시점(Timing), 트리거를 실행시키는 사건(Event), 트리거와 관련된 테이블/뷰/스키마/데이터베이스 그리고 트리거 몸체부(body)로 구성



3) TRIGGER 관리

(1) 활성화/비활성화 하기

SQL>ALTER TRIGGER TRIGGER_name DISABLE | ENABLE ;


(2) 특정 테이블에 속한 TRIGGER의 활성화/비활성화

SQL>ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS ;


(3) TRIGGER 수정 후 다시 컴파일

SQL>ALTER TRIGGER tirgger_name COMPILE ;


(4) TRIGGER 삭제

SQL>DROP TRIGGER trigger_name ;


(5) TRIGGER 관련 권한


※ TRIGGER 를 생성, 변경 및 삭제할 수 있는 권한

- GRANT CREATE TRIGGER TO SCOTT ;

- GRANT ALTER ANY TRIGGER TO SCOTT ;

- GRANT DROP ANY TRIGGER TO SCOTT ;

※ 데이터베이스에서 TRIGGER를 생성할 수 있는 권한

- GRANT ADMIN IS TER DATABASE TRIGGER TO SCOTT ;

※ EXCUTE 권한 (TRIGGER 가 실행하는 스키마에 포함되지 않은 객체를 참조하는 경우)


4) TRIGGER 예제

(실습을 하기전 scott 사용자에게 create trigger 권한을 할당)

SYS>GRANT create trigger TO scott ;


예 1) 테이블에 데이터를 입력할 수 있는 시간 지정하기

(테이블 전체가 대상이므로 문장 레벨 TRIGGER 사용)

테스트를 위해 연습용 테이블 t_order 테이블 생성


SCOTT>CREATE TABLE t_order_tab

2    ( no    NUMBER ,

3     ord_code    VARCHAR2(10),

4     ord_date     DATE ) ;


데이터를 입력할 때 입력시간이 18:40 분에서 18:50 분일 경우만 입력을 허용하고 그 외는 에러발생 트리거 생성

SCOTT>CREATE OR REPLACE TRIGGER t_order

2    BEFORE    INSERT    ON t_order_tab

3    BEGIN

4        IF(TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '18:40' AND '18:50') THEN

5            RAISE_APPLICATION_ERROR(-20100,'허용시간 아닙니다') ;

6        END IF ;

7    END ;

8    /


18:40 ~ 18:50 에 입력 할 경우

SCOTT>INSERT INTO t_order_tab

2    VALUES(1,'C100',SYSDATE) ;


-> 1 row created     -- 정상적으로 입력됨


18:40 ~ 18:50 이외의 시간에 입력 할 경우

SCOTT>INSERT INTO t_order_tab

2    VALUES(2,'C200',SYSDATE) ;


-> ERROR at line 1:

ORA-20100: 허용시간 아닙니다   -- 의도하던 에러 발생


예 2) 테이블에 입력될 데이터 값을 지정하고 그 값 이외에는 에러를 발생시키는 TRIGGER 를 생성

SCOTT>CREATE OR REPLACE TRIGGER t_order2

2    BEFORE INSERT ON t_order_tab

3    FOR EACH ROW        --행 레벨 TRIGGER

4    BEGIN

5        IF(:NEW.ord_code) NOT IN ('C100') THEN            --:NEW  새로들어오는 데이터

6            RAISE_APLICATION_ERROR(-20200,'제품코드가 틀립니다!') ;

7        END IF ;

8    END ;

9    /


신규 내용을 입력하는데 앞에서 생성한 문장레벨 TRIGGER로 인해 입력이 안됨

따라서 앞의 실습에서 만든 TRIGGER 삭제

SCOTT>DROP TRIGGER t_order ;


올바른 제품코드 입력

SCOTT>INSERT INTO t_order_tab

2    VALUES(2,'C100',SYSDATE) ;

-> 1 row created      - 정상입력


틀린 제품코드(C200) 입력

SCOTT>INSERT INTO t_order_tab

2    VALUES(3,'C200',SYSDATE) ;

-> ERROR at line 1:

ORA-20200 : 제품코드가 틀립니다!  - 의도하던 오류 발생


예 3) TRIGGER 의 작동 조건을 WHEN 절로 더 구체적으로 지정

ORD_CODE 가 'C500'  인 제품에 대해서만 19:30 부터 19:35 까지 입력을 허용하는 TRIGGER

다른 제품코드느 시간 관계없이 정상적으로 입력 가능

SCOTT>CREATE OR REPLACE TRIGGER t_order3

2    BEFORE INSERT ON t_order_tab

3    FOR EACH ROW

4    WHEN (NEW.ord_code ='C500')    <-- WHEN 절에는 NEW 앞에 콜론 기호(:) 삭제

5    BEGIN

6    IF(TO_CHAR(SYSDATE,'HH24:MI')NOT BETWEEN '19:30 AND '19:35')THEN

7        RAISE_APPLICATION_ERROR(-20300,'C500 제품의 입력허용시간이 아닙니다!') ;

8    END IF

9    END ;

10    /


예 4) 특정 테이블에 입력할 수 있는 계정을 지정하기

(테이블 자체가 대상이므로 문장 TRIGGER 를 사용)


실습을 위해 새로운 테이블을 생성

SCOTT>CREATE TABLE t_test1(no NUMBER);


SCOTT>CREATE TRIGGER t_usercheck

2    BEFORE INSERT OR UPDATE OR DELETE

3    ON t_test1

4    BEGIN

5        IF USER NOT IN ('SCOTT','HR') THEN

6           RAISE_APPLICATION_ERROR(-20001,'허락된 계정이 아닙니다!!') ;

7        END IF ;

8    END ;

9    /


테스트를 위해 새로운 사용자 계정 USER1을 생성하고 필요한 권한을 할당

SCOTT> conn / as sysdba


SYS>CREATE USER user1 IDENTIFIED BY user1

2    DEFAULT TABLESPACE users

3    TEMPORARY TABLESPACE temp ;


SYS>GRANT connect, resource TO user1 ;


SYS>GRANT INSERT ON scott.t_test1 TO user1 ;


SCOTT>CONN user1/user1


USER1>INSERT INTO scott.t_test1 VALUES(1) ;

-> ERROR at line 1:

ORA-20001 : 허락된 계정이 아닙니다!!


예 5. 기존 테이블(t_test1) 에 데이터가 업데이트 될 때 기존 내용을 백업테이블(t_test2)으로 옮겨놓는 TRIGGER 를 생성. 삭제되는 특정 행이 TRIGGER의 대상이므로 행 레벨 TRIGGER를 사용

SCOTT>CREATE TABLE t_test1

2    (no NUMBER, name VARCHAR2(10)) ;


SCOTT>CREATE TABLE t_test2

2    AS SELECT * FROM t_test1 ;


SCOTT>INSERT INTO t_test1 VALUES (1,'AAA') ;


SCOTT>INSERT INTO t_test1 VALUES (2,'BBB') ;


SCOTT>COMMIT ;


SCOTT>CREATE OR REPLACE TRIGGER t_move

2    BEFORE UPDATE ON t_test1

3    FOR EACH ROW

4    BEGIN

5        INSERT INTO t_test2 VALUES (:OLD,no , :OLD.name) ;

6    END ;

7    /


위 예에서 5번 라인에 :OLD.no , :OLD,name 은 update 되면서 이전 데이터를 의미


SCOTT>SELECT * FROM t_test1 ;

NO    NAME   

---   -----

  1     AAA

  2     BBB


SCOTT>SELECT * FROM t_test2 ;

no rows selected


SCOTT>UPDATE t_test1

2    SET no=2

3    WHERE name='AAA' ;

1 row updated.


SCOTT>SELECT * FROM t_test2 ;

NO    NAME

---  ------

  1     AAA






반응형

'오라클 > PL/SQL' 카테고리의 다른 글

PL/SQL 예외처리  (0) 2014.10.16
PL/SQL CURSOR  (0) 2014.10.14
PL/SQL 제어문  (0) 2014.10.13
PL/SQL 변수  (0) 2014.10.13
PL/SQL 시작하기  (0) 2014.10.13

+ Recent posts