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

출처: https://goalker.tistory.com/99 [오라클 스터디]

move를 통한 reorg 작업을 간단히 소개하겠습니다.

** 운영중인 상황에서는 MOVE를 사용해서는 안됨(REORG 작업으로 처리해야됨.

    ==> DBMS_REDEFINITION 패키지를 이용한 처리 참조:  https://argolee.tistory.com/19

1.REORG란?

복잡하고 불필요하게 공간을 차지하고 있는 오브젝트들을 재편성하여

액세스 되는 블록을  감소시키는 등 (ROW Migration, ROW chaining 현상 완화)

용량 확보 및 SQL 쿼리의 성능 향상을 꾀하는 작업

REORG 작업의 필요성

1) 요구되는 전체 I/O 의 최소화

2) 낭비된 디스크 공간의 복구

=>HWM 감소시켜 datafile resize 작업 가능

3) 과다하게 확장된 스페이스의 교정 작업

*HWM란?

=>SEGMENT에서 사용한 '적' 이 있는 BLOCK 과 아예 없는 BLOCK간의 경계선

테이블에 데이터가 insert 될 때 할당된 블록들 중 Free Block에 row가 들어감

마지막 까지 사용됐던 block이 HWM로 남는데

만약 데이터가 삭제(delete) 되어도 HWM는 변경되지 않음.

문제점

1) 풀스캔 시, 쿼리 수행 시간이 길어짐

=>사용자가 테이블을 풀 스캔 할 때 위와 같이 데이터가 있는 3번까지의 블록을 스캔하는 게 아니라

HWM아래쪽 영역인 1~5번까지의 블록을 스캔하기 때문

2) append hint를 사용한 insert 등 특정 insert 시 공간 낭비

=> 특정 조건의 INSERT 시 HWM아래의 영역에 빈 공간이 있어도 HWM 위쪽으로 데이터가 추가되어

아래쪽 영역이 낭비됨

* 사진 출처: http://tocsg.tistory.com/33 [투씨에스지 기술 블로그]

3) RESIZE 불가

=>10GB 중 6GB만 사용하는 테이블스페이스이므로

TOTALSPACE를 7GB로 RESIZE가 가능해야함

ALTER TABLESPACE TS_REORG RESIZE 7G;

==>실사용량은 6GB지만 HWM가 7GB 보다 더 높게 설정되어있다는 의미

2. REORG 작업 절차

1)REORG 대상 TABLESPACE에 있는SEGMENT 조회

해당 테이블스페이스에 6종류의 SEGMENT가 있음을 확인함

SEGMENT_TYPE을 확인하는 이유는 TYPE마다 MOVE 해주는 방법이 다르기 때문

추가로

 long column을 가진 TABLE 조회

=>LONG column 을 가진 Table은 일반적인 Table 과는 달리 move 가 안되므로 export/import 해줘야함

2)  MOVE 쿼리 추출

1)  TABLE

SELECT 'ALTER TABLE ' || OWNER || '.' || SEGMENT_NAME || ' MOVE TABLESPACE ' || TABLESPACE_NAME ||';'

FROM DBA_SEGMENTS  

WHERE TABLESPACE_NAME ='TS_NPT_DEV_D'

AND SEGMENT_TYPE='TABLE';

2)   TABLE PARTITION

SELECT 'ALTER TABLE ' || OWNER || '.' || SEGMENT_NAME ||' MOVE ' || REPLACE(SEGMENT_TYPE,'TABLE','')||' ' || PARTITION_NAME || ' TABLESPACE ' || TABLESPACE_NAME ||';'

FROM DBA_SEGMENTS  

WHERE TABLESPACE_NAME = 'TS_NPT_DEV_D'

AND SEGMENT_TYPE IN ('TABLE PARTITION','TABLE SUBPARTITION');

3)   INDEX

SELECT 'ALTER INDEX ' || OWNER || '.' || SEGMENT_NAME || ' REBUILD TABLESPACE ' || TABLESPACE_NAME ||';'

FROM DBA_SEGMENTS   

WHERE TABLESPACE_NAME='TS_NPT_DEV_D'

AND SEGMENT_TYPE = 'INDEX';

4)  INDEX PARTITION

SELECT 'ALTER INDEX ' || OWNER || '.' || SEGMENT_NAME || ' REBUILD ' || REPLACE(SEGMENT_TYPE,'INDEX','')|| ' '||PARTITION_NAME || ' TABLESPACE ' || TABLESPACE_NAME ||';'

FROM DBA_SEGMENTS    

WHERE TABLESPACE_NAME='TS_NPT_DEV_D'

AND SEGMENT_TYPE IN ('INDEX PARTITION','INDEX SUBPARTITION');

*참고사항

1) TABLE과 INDEX의 경우, MOVE 와 REBUILD 의 차이가 있음

2) PARTITION의 경우 MOVE 뒤에 해당 테이블의 어떤 PARTITION을 MOVE 할 지 지정해야 하기 때문에

PARTITION_NAME 정보가 추가로 붙음

5) LONG column TABLE

OS> EXPDP TABLES=NPT_DW_DEV.PLAN_TABLE DIRECTORY=DATA_PUMP_DIR DUMPFILE=0110_DW.dmp LOGFILE=0110_DW.log

OS> IMPDP  DIRECTORY=DATA_PUMP_DIR DUMPFILE=0110_DW.dmp logfile=0110_imp_dw.log tablespaces=TS_NPT_DEV_D           remap_schema=NPT_DW_DEV:NPT_DW_DEV

*LONG은 EXPORT/IMPORT 방법으로만 MOVE 가능

6) LOB SEGMENT

SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME ||' MOVE LOB (' ||COLUMN_NAME||')' || ' STORE AS (TABLESPACE TS_REORG);'

FROM   DBA_LOBS

WHERE  SEGMENT_NAME IN (SELECT SEGMENT_NAME

        FROM   DBA_SEGMENTS

        WHERE  TABLESPACE_NAME='TS_NPT_DEV_D'

        AND    SEGMENT_TYPE LIKE 'LOB%');

*참고사항

1)  LOB의 경우 속한 테이블과는 별도의 SEGMENT로 생성이 되기 때문에  TABLE 밖에 생성이 된다.

    그래서 MOVE 할 경우, LOB 테이블의 해당 LOB COLUMN 자체를 옮겨주어야 함

2)  세그먼트를 별도로 가지는 LOB과 같은 Data type은 

비구조적,비정형적인 특성의 사이즈가 큰 데이터셋을 보관하기 위한 용도로 사용됨.

테이블의 lob column엔 실제 데이터가 저장된 segment를 가리키는 포인트 주소값만 갖고 있음

*LOB SEGMENT 확인

1) LOB TABLE 생성 후 METADATA 확인

2) LOB_TEST 테이블 SEGMENT와 TEXT (LOB SEGMENT) 별도로 생성된 것 확인

3) MOVE 후 TABLESPACE RESIZE

MOVE를 통한 REORG 작업 완료 후엔 위와 같이 HWM가 내려오고

블락 내부의 빈공간들이 재편성됨

=> TABLESPACE RESIZE 후 작업 종료

1.    Initial Data Load on OGG

OGG 구성에 있어 초기적재하는 방법에서 CTAS (Create Table as Select) 를 사용할 수 있다. 초기적재 이후 동기화 부분에 있어 FLASHBACK 을 사용하면 PK 에 대한 중복에러 없이 깔끔하게 동기화 할 수 있다.

REPLICAT 을 시작할 때 AFTERCSN 옵션이 있다. 이 의미는 DB 의 SCN 과 동일한 의미로 동기화 시점을 지정할 수 있다. DB 에서는 SCN 이라는게 있고 이를 이용해 FLASHBACK QUERY 를 사용할 수 있다.

FLASHBACK QUERY 를 사용해 SCN (특정 DB 시각) 의 데이터 스냅샷을 얻을 수 있고 이를 이용해 초기적재를 실시한다.

FLASHBACK QUERY 는 UNDO TABLESPACE 를 이용하며 v$database.flashback_on 이 활성화 되어 있을 필요가 없다.

이 기술의 한계라고 하면 AS OF SCN 으로 지정할 SCN (혹은 TIMESTAMP) 데이터가 존재하지 않는 경우, 혹은 의미가 없어지는 경우이다.

전자의 경우는 일어날 일이 없을거라 생각되나, 후자의 경우는 초기 적재할 데이터의 양에 따라 발생할 가능성이 있다. 이를테면 초기적재를 완료하고 SCN 을 지정을 해 REPLICAT 을 시작하려 했으나 해당 SCN 의 trail 파일이 이미 age out 된 경우가 그런 경우이다.

2.    Usage of FLASHBACK QUERY

일단 CTAS 이야기로 들어가기 전에 FLASHBACK QUERY 의 TARGET 에 대해 확인해 볼 필요가 있다.

‘원격지의 DB 에 대해 FLASHBACK QUERY 를 던질 수 있냐?’ 란 부분이다.

왜 이런 에러 메시지가 나는지 모르겠지만 (소스쪽에서 DB LINK ALIAS 를 떼서 수행하면 에러나지 않는다.) 수행이 안 되는 걸 확인하였다.

소스쪽에서는 문제없이 수행된다.

결론은 CTAS 의 SEELCT 부분에 사용 될 테이블은 Local 위치의 Source 테이블이어야 한다.

3.    Create Remote Table using CTAS

결론부터 이야기 하면 원격지에 DB Link 를 사용하여 create table 하는 액션은 금지되어 있다.

결국에는 문서의 제목을 바꿔야 하는 수준이다.

초기적재를 CTAS 를 이용하는게 아니라 INSERT-SELECT 를 이용해야 한다.

때문에 초기적재 시나리오는 테이블을 먼저 생성 후 진행되어야 하며, 원격지에 DDL 을 날리는 행위가 금지되어 있기에, Target 에서 DDL 을 수행해야 한다. 더불어 CTAS 로 데이터까지 한번에 끌어오면 좋겠지만 Target 입장에서 Source 에 Flashback 을 수행하는 행위가 불가능하다.

4.    Initial Load and Sync Data (Outline)

사전 전제로 다음을 가정한다.

Source 측의 extract 는 이미 동작 중이고 DML 에 대해 Capture 활동 중이다.

Target 측에는 manager 만 구성되어 있고, Replicat 은 구성되어 있지 않다.

초기 적재는 다음과 같이 진행되겠다.

a.    Target 에 Source 와 동일한 구조의 테이블을 생성, 방법은 다음과 같은 방법이 있을 것이다.

-      expdp, impdp 를 이용한 방법

-      Target 에서 CTAS 를 이용해 빈 테이블을 생성

다만 위와 같은 방법을 사용하면 소스와 타겟 사이에 차이가 발생한다.

대표적으로 constraints 와 Index 차이이다.

이를 고려해 초기적재를 실시해야 할 것이다.

b.    aftercsn 을 사용한 Replicat 시작

실제로는 Data pump 를 사용하지 않고, CTAS 로 빈 테이블을 만들어 사용하도록 하겠다.

5.    Initial Load and Sync Data (Scenario)

l  Target

데이터를 초기 적재 할 테이블을 생성했다.

l  Source

초기 적재할 테이블에 flashback-query 를 사용한 insert-select 로 3분전의 시점 (scn 을 이용한 시점에 시각) 으로 데이터를 로드하였다.

l  Target

scn 을 GoldenGate 의 aftercsn 을 이용하여 데이터 싱크를 시작하였다.

6.    Future Tasks

위에서는 말 그대로 데이터만 동일하게 만들었을 뿐 사용하기 위한 환경은 만들어지지 않았다.

앞서 언급했듯이 인덱스와 제약조건이 전무한 상황이기 때문에 (CTAS 로 테이블을 생성했기 때문에) 이에 대한 작업을 진행해야 한다.

초기적재 작업에서는 인덱스와 제약조건이 없어도 되고 없는 편이 낫다. 그 이유는 초기적재를 위한 작업 속도 때문으로 데이터 적재가 끝난 후 인덱스 및 제약조건 추가를 하는편이 이득이다.

위를 고려해 작업을 고급화(단순화) 하기 위한 작업으론 아래와 같은 내용이 있을 것이다.

-      Table 생성 스크립트

-      초기적재 (insert-select) 스크립트

-      인덱스 및 제약조건 생성 스크립트

출처: https://h391106.tistory.com/290?category=487092 [Lee]

1. 성능분석에 이용되는 뷰

오라클에서 수행 누적 통계치를 저장하고 확인하고자 할때 사용하는 시스템 별로 사용하는 뷰들은 다음과 같다. 

v$sysstat : 오라클에서 인스턴스 구동후 현재까지의 "누적 통계치"를 확인할 때 사용할 수 있는 뷰

v$sesstat : 수행하는 세션별로 통계치를 확인 하는 뷰

v$mystat :  현재 접속해 있는 자기 세션에 대한 수행통계

v$system_event : 이벤트에 대한 대기시간 정보를 포함하고 있는 뷰. 이벤트 발생시의 정보을 볼 수 있다.

다음 명령으로 전체 측정항목을 볼수 있다.

SQL> select * from v$statname;

STATISTIC# NAME                                                    CLASS    STAT_ID
---------- -------------------------------------------------- ---------- ----------
         0 logons cumulative                                           1 2666645286
         1 logons current                                              1 3080465522
         2 opened cursors cumulative                                   1   85052502
         3 opened cursors current                                      1 2301954928
         4 user commits                                                1  582481098
         5 user rollbacks                                              1 3671147913
         6 user calls                                                  1 2882015696
       ....
       378 OS Signals received                                        16  210375991
       379 OS Voluntary context switches                              16 2422402766
       380 OS Involuntary context switches                            16 3316937952

381 rows selected.

2. 뷰에서 성능분석에 자주 이용되는 항목들

----------------------------------------------------------------  자주 사용되는 항목들 -------------------------------------------------------------------

1. Buffer NoWait % 
버퍼블록을 읽으려 할 때, buffer busy waits대기 없이 곧바로 읽기에 성공한 비율을 나타냄.

select  round(100*(1-bfwt/gets),2) "Buffer Nowait %"
from    (
        select  sum(a.count) bfwt
        from    v$waitstat a
        ),
        (
        select  b.value gets
        from    v$sysstat b
        where   b.name = 'session logical reads'
        );
 

2. Redo NoWait %
Redo로그를 기록할 공간을 요청하지 않고 곧바로 Redo 엔트리를 기록한 비율을 나타냄.

select  round(100*(1-rlsr/rent),2) "Redo Nowait %"
from    (
        select  a.value rlsr
        from    v$sysstat a
        where   a.name = 'redo log space requests'
        ),
        (
        select  value rent
        from    v$sysstat b
        where   b.name = 'redo entries'
        );

3. Buffer Hit % 
디스크 읽기를 수반하지 않고 버퍼캐시에서 블록찾기에 성공한 비율을 나타냄.

select  round(100*(1-(phyr-phyrd-nvl(phyrdl,0))/gets),2) "Buffer Hit %"
from    (
        select  value phyr
        from    v$sysstat a
        where   a.name = 'physical reads'
        ),
        (
        select  value phyrd
        from    v$sysstat b
        where   b.name = 'physical reads direct'
        ),
        (
        select  value phyrdl
        from    v$sysstat c
        where   c.name = 'physical reads direct (lob)'
        ),
        (
        select  value gets
        from    v$sysstat d
        where   d.name = 'session logical reads'
        );

4. Latch Hit %
래치 경합없이 첫번째 시도에서 곧바로 래치를 획득한 비율을 나타냄.

select  round(100*(1-sum(a.misses)/sum(a.gets)),2) "Latch Hit %"
from    v$latch a;

5. Library Hit %
파싱부하와 관련 있는 항목.
라이브러리 캐시에 이미 적재된 SQL커서를 생행하거나 오브젝트정보를 읽으려할 때 커서 또는 오브젝트정보가 Heap영역에서 찾아지는 비율을 나타냄.

-- Library Hit (Get) %
select  round(100*sum(a.gethits)/sum(a.gets),2) "Library Cache Get Hit %"
from    v$librarycache a;
 
-- Library Hit (Pin) %
select  round(100*sum(a.pinhits)/sum(a.pins),2) "Library Cache Pin Hit %"
from    v$librarycache a;

6. Soft Parse %
파싱부하와 관련 있는 항목.
실행계획이 라이브러리 캐시에서 찾아져 하드파싱을 일으키지 않고 SQL을 수행한 비율을 나타냄.

-- Soft Parse %
select  round(100*(1-hprs/prse),2) "Soft Parase %"
from    (
        select  a.value hprs
        from    v$sysstat a
        where   a.name = 'parse count (hard)'
        ),
        (
        select  b.value prse
        from    v$sysstat b
        where   b.name = 'parse count (total)'
        );

7. Execute to Parse %
파싱부하와 관련 있는 항목.
Parse Call없이 곧바로 SQL을 수행한 비율. 즉, 커서를 애플리케이션에서 캐싱한 채 반복 수행한 비율을 나타냄.

-- Execute to Paraes %
select  round((1-prse/exe)*100,2) "Execute to Parse %"
from    (
        select  a.value prse
        from    v$sysstat a
        where   a.name = 'parse count (total)'
        ),
        (
        select  b.value exe
        from    v$sysstat b
        where   b.name = 'execute count'
        );

8. Parse CPU to Parse Elapsd %
파싱부하와 관련 있는 항목.
파싱 총 소요 시간 중 CPU time이 차지한 비율. 파싱에 소요된 시간 중 실제 일을 수행한 시간비율을 나타냄.

-- Parase CPU to Parse Elapsed %
select  decode(prsela,0,to_number(null),round(prscpu/prsela*100,2)) "Parse CPU to Parse Elapsed %"
from    (
        select  a.value prsela
        from    v$sysstat a
        where   a.name = 'parse time elapsed'
        ),
        (
        select  b.value prscpu
        from    v$sysstat b
        where   b.name = 'parse time cpu'
        );
 
 

9. % Non-Parse CPU
파싱부하와 관련 있는 항목.
SQL을 수행하면서 사용한 전체 CPU time중 파싱 이외의 작업이 차지한 비율을 나타냄.

-- Non-Parse CPU %
select  decode(tcpu,0,to_number(null),round(100*(1-(prscpu/tcpu)),2)) "% Non-Parase CPU"
from    (
        select  a.value tcpu
        from    v$sysstat a
        where   a.name = 'CPU used by this session'
        ),
        (
        select  b.value prscpu
        from    v$sysstat b
        where   b.name = 'parse time cpu'
        );

10. In-memory Sort %
전체 소트 수행횟수에서 In-Memory방식으로 소트한 비율을 나타냄.

-- In-memory Sort %
select  decode((srtm+srtd),0,to_number(null),round(100*srtm/(srtd+srtm),2)) "In-memory Sort %"
from    (
        select  a.value srtm
        from    v$sysstat a
        where   a.name = 'sorts (memory)'
        ),
        (
        select  b.value srtd
        from    v$sysstat b
        where   b.name = 'sorts (disk)'
        );
        

11. Memory Usage %
Shared Pool내에서 현재 사용중인 메모리 비중을 나타냄.

-- Memory Usage %
select  100*(1-sum(decode(a.name,'free memory',a.bytes))/sum(a.bytes))
from    v$sgastat a
where   a.pool = 'shared pool';

12. % SQL with executions>1
전체 SQL 개수에서 두번이상 수행된 SQL이 차지하는 비중을 나타냄.

13. % Memory for SQL w/exec>1
전체 SQL이 차지하는 메모리 중 두번이상 수행된 SQL이 차지하는 메모리 비중을 나타냄.

14. SESSION LOGICAL READS
SESSION 의 논리적 읽기 ( 메모리->CPU ) 를 알 수 있다. 이 값의 일정시간 당 델타값이 증가한 다는 것은 일량이 증가하고 있다고 해석하면 된다. 여기서 이상징후를 확인하면 해당 SID 에 대해 TRACE 라던가 수행 쿼리를 조회하는 행동으로 연계할 수 있다.

15. PHYSICAL READS
SESSION 의 물리적 읽기 ( 디스크->메모리 ) 를 알 수 있다. 이 값이 증가하면 증가할 수록 디스크의 I/O 가 많이 발생한다. 이와 관련해 I/O 관련 WAIT EVENT 들이 관측될 수 있다. SESSION LOGICAL READS 와 마찬가지로 많이 유발하는 SID 를 찾아 추가적인 조치를 취할 수 있다.

16. PARSE COUNT (HARD)
이것은 순전히 LITERAL SQL 수행을 잡아내기 위한 방편이다. 이 지표는 상황에 따라 SYSTEM  | SESSION LEVEL 로 조회해야 한다. 이를테면 순간적으로 세션을 맺고 LITERAL SQL 을 수행하고 빠지는 세션이 많은 경우엔 SYSTEM LEVEL 로 봐야 시스템에 HARD PARSING 이 많이 발생하고 있다는 것을 알 수 있을 것이다.

17. EXECUTE COUNT
이 지표는 SQL 을 수행한 수를 의미한다. (RECURSIVE CALL 제외) 시스템의 활동성을 의미한다. 보통 급증보다 급감하는 경우에 이슈가 있는 경우가 있다. (시스템이 IDLE 인 경우를 제외)

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

 위 점검항목중 SESSION LOGICAL READS, PHYSICAL READS, PARSE COUNT (HARD), EXECUTE COUNT, dbfile SEQ READ,dbfile scattred read 항목을 v$sysstat와 v$session_wait 으로 부터 조회하는 쿼리.

SQL> col sread for 99999999999999999999;

SQL> col pread for 99999999999999999999;

SQL> select
sum(decode(name, 'session logical reads',value,0)) sread,
sum(decode(name, 'physical reads',value, 0)) pread,
sum(decode(name, 'parse count (hard)',value, 0)) pcount,
sum(decode(name, 'execute count',value, 0)) exec,
sum(decode(name, 'seq',value, 0)) "db file seq",
sum(decode(name, 'scatt', value, 0)) "db file scatt"
    from
    (

    select name,value from v$sysstat
    where name in ('session logical reads','physical reads','execute count','parse count (hard)','db file sequential reads')
        union all
    select 'seq',nvl(sum(seconds_in_wait),0) from v$session_wait
    where event ='db file sequential read'
        union all
    select 'scat',nvl(sum(seconds_in_wait),0) from v$session_wait
    where event ='db file scattered read'

    )
;

3. 변화량(델타값) 스크립팅

하지만 위의 뷰들을 검색한 결과로는 토탈 수치만 나오기 때문에 그것만으로는 변화량을 이해할수 없다.

의미를 부여하기 위해서는 변화량(델타값)이 필요하다.

때문에 뷰를 검색한 결과를 별도의 뷰에 기록한 후 현재값과의 연산으로 변화량을 구하던지,

쉘스크립트 등을 이용하여 연산해야 한다.

요는 어디든 예전값을 기록해야 한다는것.

인덱스가 어느 정도의 영역을 사용하는지 알기 위해서는 index_stats 테이블을 만들고

analyze 를 수행하면 index 블럭의 사용 상황을 조회 하면 됩니다.

이러한 블럭의 사용 상황을 조회 하는 것뿐만 아니라 어떤 인덱스가 rebuild 대상인지, 

BITMAP INDEX 의 대상인지를 분석하는 방법을 설명 하고자 합니다.

DBA는 사용중인 인덱스들이 균형을 이루는지, 아니면 rebuild 를 해주어야 할만큼

균형이 깨졌는지 주기적으로 확인할 필요가 있습니다. 

균형이 깨져버린 인덱스는 인덱스의 특정 부분에만 I/O 를 집중시키게 함으로써 성능에 병목현상을 초래하게

됩니다.

아래의 예제는 균형이 깨진 인덱스 (unbalanced indexes) 인지 확인하는 예제 입니다.

1.분석하고자 하는 인덱스에 대한 통계치를 조사. 

  양이 많은 인덱스의 경우

  (수백만건 이상의 row를 지닌 테이블에 대한 인덱스의 경우) COMPUTE STATISTICS

  대신에  ESTIMATE 옵션을 사용합니다.

Example:

SQL>analyze index A1_PK compute statistics;

Index analyzed.

2. 인덱스가 얼마나 균형을 이루었는지 조사

아래의 스크립트를 수행합니다. (index_check.sql)

아래 예는 SCOTT 유저의 모든 인덱스에 대한 조사를 수행합니다.

SQL>select index_name, blevel,

decode(blevel, 0, 'OK BLEVEL',1,'OK BLEVEL',

2, 'OK BLEVEL', 3, 'OK BLEVEL', 4, 'OK BLEVEL','BLEVEL HIGH' ) 'OK?'

from dba_indexes

where owner = 'SCOTT';

INDEX_NAME                         BLEVEL OK?

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

S_CUSTOMER_ID_PK                        0 OK BLEVEL

S_DEPT_ID_PK                            0 OK BLEVEL

S_DEPT_NAME_REGION_ID_UK                0 OK BLEVEL

S_EMP_ID_PK                             0 OK BLEVEL

S_EMP_USERID_UK                         0 OK BLEVEL

S_IMAGE_ID_PK                           0 OK BLEVEL

S_INVENTORY_PRODID_WARID_PK               BLEVEL HIGH

S_ITEM_ORDID_ITEMID_PK                    BLEVEL HIGH

S_ITEM_ORDID_PRODID_UK                    BLEVEL HIGH

S_LONGTEXT_ID_PK                          BLEVEL HIGH

S_ORD_ID_PK                               BLEVEL HIGH

INDEX_NAME                         BLEVEL OK?

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

S_PRODUCT_ID_PK                           BLEVEL HIGH

S_PRODUCT_NAME_UK                         BLEVEL HIGH

S_REGION_ID_PK                            BLEVEL HIGH

S_REGION_NAME_UK                          BLEVEL HIGH

S_TITLE_TITLE_PK                          BLEVEL HIGH

S_WAREHOUSE_ID_PK                         BLEVEL HIGH

17 rows selected.

3. BLEVEL (Branch level)

 B-Tree 인덱스 형식의 일부이며 이는 오라클이 인덱스

서치를 할때 몇 단계를 거쳐서 블럭의 위치를 찾아내는가와 관계가 있습니다.

최악의 경우에는 각각의 BLEVEL 에 대해서 매번 디스크 읽기가 필요할 수 있습니다.

만일 BLEVEL 이 4 이상 나오게 되면 해당 인덱스를 rebuild 를 할 필요가 있습니다.

( select * from dba_indexes where owner = 'SCOTT' and blevel >= 4 ; )

주: 위의 index_check.sql 명령은 Analyze 되지 않은 인덱스에 대해서는

'BLEVEL HGH' 로 나타내게 됩니다.

4. ANALYZE 명령의 VALIDATE STRUCTURE 옵션을 사용

INDEX_STATS 테이블에 추가적인 인덱스 정보를 생성합니다. 

이 테이블은 OWNER 에 대한 정보를 가지고 있지 않으므로 현재의 세션에서 수행된 anlayze 정보를 나타내고 있습니다.

SQL>analyze index SCOTT.S_EMP_ID_PK    validate structure;

Index analyzed.

5. 아래와 같이 인덱스에 대한 정보를 조회 합니다.

SQL>  select DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS) PCT_DELETED,

     (LF_ROWS-DISTINCT_KEYS)*100/ decode(LF_ROWS,0,1,LF_ROWS) DISTINCTIVENESS

     from index_stats

     where NAME='&index_name';

Enter value for index_name: S_EMP_ID_PK

old   4:         where NAME='&index_name'

new   4:         where NAME='S_EMP_ID_PK'

PCT_DELETED DISTINCTIVENESS

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

          0               0

6. 조회된 결과에 대한 분석

PCT_DELETED 컬럼은 ?p % leaf 노드가 지워져서 사용되지 않은 상태로 있는지 나타내

줍니다. 지워져서 사용되지 않은 빈도가 높을수록 인덱스는 불 균형 (unbalanced)

상태가 심한 것입니다. 만일 PCT_DELETED 가  20% 이상으로 나타나면 인덱스는

REBUILD 의 대상이라고 보면 됩니다. 만일 좀 더 자주 INDEX REBUILD 를 할 수 있다면

REBUILD 할 PCT_DELETED 기준은 10% 까지로 낮출 수도 있습니다.

높은 PCT_DELETED  값을 그대로 방치 할 경우엔 과도한 redo 할당으로 성능 저하가

생길 수도 있습니다.  

DISTINCTIVENESS 컬럼은 인덱스가 만들어진 컬럼의 값이 얼마나 자주 반복되는 지를

보여줍니다.

예를 들면

만일 1만건의 row와 9000건의 서로 다른 값을 가진 테이블이 있을 때

DISTINCTIVENESS 값은 다음과 같이 계산됩니다.

(10000-9000)*100/10000=10

위와 같은 결과라면 컬럼의 값들이 잘 분산 되어 있다는 결론을 내릴 수 있습니다.  

인덱스 분석 수집 정보 확인

ANALYZE INDEX 명령을 수행 한 후 INDEX_STATS를 조회 하면 됩니다.

 
  
SQL> SELECT blocks,  btree_space,  used_space,  pct_used "사용율(%)",
            lf_rows,  del_lf_rows "삭제행"
     FROM  INDEX_STATS;
  
BLOCKS  BTREE_SPACE  USED_SPACE  사용율(%)   LF_ROWS   삭제행
------  -----------  ----------  --------   --------  --------
     5        23984       12489        53        892        51 
 
 
-- 인덱스가 삭제된 행이 많으면 인덱스를 재구축 해야 합니다.
-- 예를 들어 LF_ROW에 대한 DEL_LF_ROWS의 비가 30%를 초과하면 
-- 인덱스를 재구축 해야 합니다.
  
 
-- 인덱스의 재구축..
SQL> ALTER INDEX board_pk  REBUILD;
 인덱스가 변경되었습니다.
 
 
-- 분석 자료의 수집
SQL> ANALYZE INDEX board_pk VALIDATE STRUCTURE;
인덱스가 분석되었습니다.
 
 
-- 다시 index_stats를 조회 하면 삭제행이 0으로 
-- 나오는것을 확인 할 수 있습니다.
SQL> SELECT blocks, btree_space, used_space, pct_used "사용율(%)",
            lf_rows, del_lf_rows "삭제행"
     FROM INDEX_STATS;
 
 BLOCKS  BTREE_SPACE  USED_SPACE  사용율(%)  LF_ROWS    삭제행
-------  -----------  ----------  ---------  -------   --------
      5        24032       11775        49       841        0  
    

Oracle Database 10g와 11g 사이에 밀접한 데이터 복제를 쉽게 달성하도록 Oracle GoldenGate를 설치, 설정 및 구성하는 방법을 배워보자.

Oracle GoldenGate

Oracle GoldenGate는 다양한 엔터프라이즈 시스템 사이에 트랜잭션 데이터를 고속으로 복제하고 통합하기 위해 사용되는 배포가 용이한 최신 제품이다. 또한 서로 다른 Oracle Database 버전이나 서로 다른 하드웨어 플랫폼을 포함한 동종 시스템과 이기종 시스템 사이 및 Oracle Database와 오라클 이외의 데이터베이스(Microsoft SQL Server, 오픈 시스템과 z/OS용 IBM DB2, Sybase 등) 사이에 데이터를 이동하는 유연성을 제공한다.

이 기사는 Oracle Database 10g와 11g 사이에 밀접한 데이터 복제를 쉽게 달성하도록 본 제품을 설치, 설정 및 구성할 수 있는 방법을 선보이며 본 제품의 뛰어난 성능에 대해서도 소개한다.

2009년에 오라클이 인수한 Oracle GoldenGate는 광범위한 산업에 걸쳐 전 세계에 4,000여 건 이상의 솔루션 구현 실적을 나타냈다. 소프트웨어로 실시간 로그 기반 변경 데이터 캡처(CDC)를 수행하고 이기종 데이터베이스 사이에 방대한 분량의 트랜잭션 데이터를 복제할 수 있으며 복제 시간(Latency)가 극히 짧고 설치가 매우 간단한 것이 특징이다.

일반적으로 GoldenGate 캡처(Capture, Extract), 데이터 펌프(Data Pump) 및 전송(Delivery, Replicat) 프로세스를 설치하게 되고, 다양한 운영 체제와 데이터베이스에서 사용할 수 있다. 대상 데이터베이스의 요구 사항에 따라 모든 데이터 또는 일부 데이터를 복제할 수 있다. Oracle Database용 Oracle GoldenGate의 경우 DML(데이터 조작 언어) 및 DDL(데이터 정의 언어) 연산을 복제할 수 있다. (현재 DDL 연산은 유사한 오라클 버전 사이에서만 복제가 가능하며 서로 다른 오라클 버전 사이의 복제는 불가능하다.)

Oracle GoldenGate는 공식적으로 Oracle 9i 릴리스 2에서 Oracle Database 11g 릴리스 2까지 지원하며 이 버전들에 대해서는 모두 DML/DDL 지원이 제공된다. 본 제품은 Oracle Database의 Enterprise Edition과 Standard Edition에서 모두 작동한다.

앞서 언급했듯이 지원되는 Oracle Database의 유사 버전 또는 서로 다른 버전 사이 및 Oracle Database와 오라클 이외의 데이터베이스 사이에 데이터 이동이 가능하다. 대부분의 경우 데이터의 필터링, 매핑 및 변환이 가능하다. 참고로 DDL 지원을 사용 중인 경우에는 데이터 필터링, 매핑 및 변환이 지원되지 않는다(다시 말해서 GoldenGate를 유사한 Oracle Database 버전 사이의 DDL 복제에 사용 중인 경우).

Oracle GoldenGate는 데이터베이스와 관련성이 높은 제품이므로 데이터베이스 관리자들은 사용 방법을 아주 쉽게 습득 가능하다. 구현 시간도 매우 적다.(일부 업체의 경우에는 1개월 이내에 100% 가동에 이르렀다.)

제품 구성

Oracle GoldenGate 제품군에는 다음 항목이 포함되어 있다.

？ Oracle GoldenGate
？ Oracle GoldenGate용 관리 팩(일명 Oracle GoldenGate Director)
？ Oracle GoldenGate Veridata
？ Oracle GoldenGate 애플리케이션 어댑터
？ 메인프레임용 Oracle GoldenGate

고객들에게 높은 부가가치를 제공하기 위해 Oracle GoldenGate 코어 라이선스에는 Oracle Active Data Guard에 대한 전체 사용 라이선스와 Oracle Database의 XStream에 대한 전체 사용 라이선스가 포함되어 있다. XStream은 Oracle Streams에 대한 API이다. Oracle Active Data Guard를 사용하면 트랜잭션 로그(Redo Log)가 운영 데이터베이스에 쓰이고 있는 중간에도 대기(Standby) 데이터베이스를 조회용으로 바로 사용할 수 있다. 이것은 운영 데이터베이스에서 발생하는 리포팅과 조회 작업을 대기(Standby) 데이터베이스에서 별도로 실행시킬 수 있는 탁월한 기능이다.

전통적으로 Oracle Streams는 Oracle Database 사이에만 데이터를 복제하는데 사용되어 왔다. Oracle GoldenGate는 오라클 이외의 데이터베이스 사이의 데이터 복제를 제공하며 설정하기가 쉽다.

이미 발행된 <Oracle - GoldenGate 제품 전략 보고서>에서 우리가 천명했던 말을 상기하고자 한다. “Oracle GoldenGate의 전략적 성격을 감안할 때 Oracle Streams는 계속 지원하되 적극적으로 향상시키지는 않을 것이다. 그 대신에 Oracle Streams의 가장 좋은 요소를 Oracle GoldenGate에 포함하는 방법을 평가할 계획이다.”

이 데모의 목적

이 데모를 위해서 Microsoft Windows 서버 한 대에 데이터베이스 3개 HRPRD1, HRPRD2 및 HRPRD3를 설치해야 한다. 데이터베이스 버전은 각각 Oracle Database 11g Oracle Database10g 및 Oracle Database 10g이다.

이들 세 데이터베이스의 SYS 및 SYSTEM 암호는 시험 목적을 위해 hrpassword1 로 설정되었으며, 이 암호는 나중에 변경할 수 있다.

이 데모의 목적은 두 Oracle 10g 데이터베이스 HRPRD2 및 HRPRD3의 HR.EMPLOYEES 테이블 사이에 단방향 복제를 설정하는 것이다. 첫 번째 데이터베이스의 테이블에서 발생한 모든 DML 변경사항을 두 번째 데이터베이스로 옮겨야 한다. 이것은 나중에 Oracle 11g 데이터베이스 HRPRD1에도 동시에 복제를 하는 것으로 기능이 추가된다. 따라서 HRPRD2의 테이블에서 업데이트, 삽입 또는 삭제되는 모든 내용은 HRPRD1뿐만 아니라 HRPRD3에서도 업데이트된다.

Oracle Database 10g에서 Oracle Database 10g로 복제한 다음 Oracle Database 11g로 복제하는 이 전략은 업그레이드 시에 자주 사용되며, 이 경우 업데이트된 데이터베이스 버전으로 전환하기 전에 데이터를 복제해야 한다. 바로 이러한 경우에 Oracle GoldenGate가 손쉬운 해결 방법을 제공한다.

참고로 Oracle Database의 경우 Microsoft ODBC 데이터 소스 관리자를 사용하는 데이터베이스를 위해 데이터 소스 이름(DSN)을 만들 필요가 없다. Oracle GoldenGate 복제는 리스너를 사용하여 Oracle Database에 직접 연결한다.

다운로드할 내용

먼저 Windows 시스템의 경우에는 Microsoft 다운로드 센터에서 Microsoft Visual C ++ 2005 SP1 재배포 가능 패키지를 다운로드하여 설치해야 한다. 이 패키지는 컴퓨터에 아직 Visual C++가 설치되지 않은 경우 애플리케이션을 실행하는 데 필요한 Visual C++ 라이브러리의 런타임 구성요소를 설치하기 위해 사용된다.

다음에는 Oracle Technology Network에서 Oracle GoldenGate 소프트웨어를 다운로드해야 한다. Windows 플랫폼의 경우, OTN에서는 이4비트 버전, 즉 Oracle 10g 64비트 용 Oracle GoldenGate v10.4.0.x만을 제공한다. 32비트 Windows 버전을 다운로드하려면 Oracle E-Delivery사이트를 사용해야 한다. (참고로 E-Delivery 다운로드는 OTN 개발자 라이선스가 아닌 30일 시험판 라이선스를 제공한다.) 이 사이트에서 로그인한 다음 Microsoft Windows(32비트) 플랫폼의 경우 검색창에 “Oracle Fusion Middleware Product Pack”(Oracle GoldenGate는 Oracle Database 제품이 아닌 Oracle Fusion Middleware 제품으로 간주되기 때문)을 입력하고 Go를 클릭한다.

미디어 팩의 목록이 나타난다. 이 목록에서 먼저 Oracle GoldenGate on Oracle Media Pack for Microsoft Windows (32-bit)를 선택한다.

이 미디어 팩에는 아래와 같은 구성요소들이 있다.

<표 1>

Oracle GoldenGate for Oracle 11g뿐만 아니라 Oracle GoldenGate for Oracle 10g도 필요하므로 zip 파일 3개를 모두 다운로드한다. 이후 Oracle Fusion Middleware용 미디어 팩 목록으로 돌아가서 Management Pack for Oracle GoldenGate (v2.0.0.2) Media Pack for Microsoft Windows를 선택한다.

ManagementPack의 미디어 팩에는 아래와 같은 구성요소들이 있다.

<표 2>

Management Pack을 설치하려는 경우 이 구성요소들도 모두 다운로드한다. Management Pack은 Windows 서버에 설치되는 독립형 제품이다. 제품의 원래 이름은 Oracle GoldenGate Director이며 이 제품은 Oracle GoldenGate 환경을 쉽게 모니터링 및 관리할 수 있게 해주는 Multi-tier 클라이언트-서버 애플리 케이션으로 구성되어 있다.

Oracle GoldenGate Director의 GUI 인터페이스를 사용하면 중앙에서 Oracle GoldenGate를 설계 및 구성할 수 있으며 서버 간 데이터를 복제하도록 설정된 여러 Oracle GoldenGate 프로세스들을 관리 및 모니터링 하는 것도 가능하다.

미디어 팩의 주 목록에는 Microsoft Windows용 Oracle GoldenGate Veridata 미디어 팩 등과 같은 다른 미디어 팩도 포함되어 있다. Oracle GoldenGate Veridata는 서로 다른 플랫폼에 Agent를 설치하여 실시간 데이터 비교를 통해 두 데이터베이스의 동기화(일치) 여부를 다운타임 없이 판단할 수 있다. 이 비교 작업은 24*7 언제나 수행할 수 있으며 대량 데이터 복제와 동시에 실행할 수도 있다.

또한 서로 다른 플랫폼 간의 데이터 비교도 가능하다.

전술한 주 미디어 팩 외에 다른 Oracle GoldenGate 팩도 목록에 표시된다. 이러한 팩은 Sybase, SQL Server, IBM DB2, Teradata 및 JMS와 플랫 파일 등 오라클 이외의 데이터베이스를 위한 것으로서 이 기종을 지원하는 Oracle GoldenGate의 특징을 잘 보여준다.

설치 단계

다운로드한 Oracle GoldenGate 문서 zip 파일(V18423-01.zip)에는 Linux, UNIX 및 Windows용 Oracle GoldenGate for Oracle Database의 설치 단계를 설명하는 Oracle GoldenGate Oracle Installation and Setup Guide Version 10.4(Oracle GoldenGate 설치 및 설정 가이드) 버전 10.4(gg_ora_inst_v104.pdf)가 들어있다. 참고로 Sybase, SQL Server, DB2, MySQL, Teradata용 Oracle GoldenGate의 설치 문서도 있다.

먼저 Oracle GoldenGate for Oracle 10g 압축 파일 V18162-01.zip을 새로운 Oracle GoldenGate 소프트웨어 설치 디렉터리(디렉터리 이름에 공백 없이)에 푼다. 예를 들어 Oracle GoldenGate 디렉터리로 C:\OGG10G를 사용할 수 있다. 이 디렉터리의 소프트웨어 코드는 첫 번째와 두 번째 Oracle 10g 데이터베이스 사이의 복제를 설정하기 위해 사용된다.

세 번째 데이터베이스는 Oracle Database 11g 버전이므로 Oracle GoldenGate for Oracle 11g의 압축 파일 V18164-01.zip을 풀어야 한다. 이 Oracle GoldenGate 버전의 설치 위치는 C:\OGG11G 디렉터리를 사용해야 한다. 이 디렉 터리의 소프트웨어 코드는 세 번째 Oracle Database 11g 데이터베이스에 대한복제를 제어하는 데 사용된다.

만약 원본 또는 대상 데이터베이스로 Oracle9i가 있다면 다른 버전의 Oracle GoldenGate 코드가 필요하다. Oracle GoldenGate의 Oracle9i 버전은 이 오라클 웹 사이트에서 다운로드할 수 없다. 이 버전이 필요하면 My Oracle Support 에서 서비스 요청(SR)을 올려야 한다.

명령 프롬프트에서 아래 단계를 따른다.

mkdir C:\OGG10G
cd C:\OGG10G
unzip c:\V18162-01.zip
mkdir C:\OGG11G
cd C:\OGG11G
unzip C:\V18164-01.zip
# start the 10gdatabasesifnotalreadystarted
net start OracleServiceHRPRD2
net start OracleServiceHRPRD3
# start the 11gdatabaseifnotalreadystarted
net start OracleServiceHRPRD1

환경 변수 설정

이제 명령창 두 개를 열고(시작 -> 실행 ->Cmd) ORACLE_HOME,ORACLE_SID,LD_LIBRARY_PATH등의 해당 환경 변수를 Oracle Database 10g 및 Oracle Database 11g에 맞는 값으로 설정할 수 있다.

Oracle 10g 데이터베이스 사이의 복제를 설정하려면 환경 변수를 아래와 같이 설정한다.

set ORACLE_HOME=C:\Oracle\product\10.2.0\db_1
set ORACLE_SID=HRPRD2
set PATH=%ORACLE_HOME%\bin;%PATH%
set LD_LIBRARY_PATH=C:\OGG10G; %ORACLE_HOME%\LIB;
%ORACLE_HOME%\jdbc\lib
set CLASSPATH=;C:\Program
Files\Java\jre6\lib\ext\QTJava.zip;%ORACLE_HOME%\jdbc\lib

ORACLE_SID는 HRPRD2 또는 HRPRD3(둘 다 Oracle 10g 데이터베이스 임)으로 설정할 수 있다. PATH, LD_LIBRARY_PATH 및 CLASSPATH에 추가된 사항에 주목한다. 다른 대안은 이러한 변경사항을 시스템 변수 컴퓨터 속성 고급 환경변수 시스템 변수를 사용하여 이 작업을 할 수 있다. 이 시스템 변수를 추가하고 경로를 위에 지적한 대로 변경한다.

이 경우 서버 한 대에 두 가지 데이터베이스 버전을 설치하는 방법의 결점은 Oracle GoldenGate for Oracle 10g 또는 Oracle GoldenGate for Oracle 11g용 Manager 서비스가 시작될 때마다 시스템 변수를 변경해야 한다는 것이다. 즉, Oracle Database 11g에 속한 시스템 변수를 추가한 다음 Oracle GoldenGate for Oracle 11g Manager서비스를 시작해야 한다. 그 후 시스템 변수를 Oracle Database 10g에 필요한 것으로 변경한 다음 Oracle GoldenGate for Oracle 10g Manager 서비스를 시작해야 한다.

이 시스템은 테스트 시스템이므로 이 방법으로 수행해도 괜찮을 수 있다. 그러나 실무 환경에서는 스크립트 방식 등과 같은 더 자동화된 방법을 선택하여 변수를 설정하고 이 스크립트 자체에서 Manager 서비스를 시작해야 한다.

GGSCI 명령 인터프리터

명령창 또는 내 컴퓨터 수준에서 환경 변수를 설정한 후 Oracle GoldenGate 폴더로 이동하고 명령 Oracle GoldenGate Command Interpreter for Oracle(GGSCI) 프로그램을 명령줄(< 그림 1 > 참조) 또는 Windows 탐색기에서 실행한다.

< 그림 1 > Oracle GoldenGate 명령 인터프리터

이제 GGSCI에서 CREATE SUBDIRS 명령을 내려 Oracle GoldenGate 작업 하위 디렉터리를 만든다. <그림 2>에는 생성된 주요 디렉터리들이 열거돼 있다.

< 그림 2 > 작업 하위 디렉터리 만들기

Oracle GoldenGate Manager를 로컬 프로그램으로 실행

Oracle GoldenGate Manager 서비스는 명령창에서 로컬 프로그램으로 실행하거나 Windows가 시작될 때 자동으로 시작되는 Windows 서비스로 설치할 수 있는데, 후자를 권장한다.

로컬 명령으로 실행하려면 디렉터리에서 매개변수(Parameter) 파일을 사용하여 Manager 실행 파일을 호출하면 된다. 구문은 다음과 같다.

C:\OGG10G>mgr
Usage: MGR PARAMFILE <param file> [REPORTFILE <report file>]
[PID <process id>] [PORT <port number>]
[USESUBDIRS] [NOUSESUBDIRS]
[PAUSEATEND] [NOPAUSEATEND]
[CD <directory>]

필수 매개변수는 PARAMFILE이다. 이를 위해 다음과 같이 GGSCI에 매개변수 파일을 만들어야 한다.

GGSCI (HaviPori) 1> edit params mgr

위 명령을 실행하면 C:\OGG10G\dirprm 디렉터리에 새 mgr.prm 파일이 생성된다. 관리자 프로세스를 실행하는 데 사용되는 포트 번호를 입력하고 파일을 저장한다.

PORT 7809

기본 포트는 7809이다. 포트 번호를 선택하면 다른 프로그램과 충돌이 발생해서는 안되며 방화벽에 제한도 없어야 한다. PORT는 Manager의 유일한 필수 매개 변수이므로 이 값은 반드시 지정해야 한다. 이 포트는 GGSCI가 Manager에게 프로세스를 시작하라는 요청을 보낼 때 사용하며 Extract 프로세스도 여러 동작을 위해 이 포트를 사용한다. 매개변수 파일을 만든 후 Windows 명령 프롬프트에서 다음과 같이 Manager를 시작할 수 있다.

C:\OGG10G>mgr paramfile c:\OGG10G\dirprm\mgr.prm

위 명령을 실행하면 Manager가 시작되고 < 그림 3 >과 같은 메시지가 표시된다.

< 그림 3 > Windows 명령 프롬프트에서 관리자 시작하기

Manager의 실행을 유지하려면 이 창을 계속 열어 두어야 한다. 로그아웃하면 창이 닫히고 Manager가 정지한다.

Manager를 Windows 서비스로 설치

앞에서 설명한 것처럼 Manager를 시작하는 또 다른 방법은 Manager를 Windows 서비스로 등록하는 것이다. Windows 클러스터의 경우에는 Windows 서비스만이 자동으로 Passive 서버로 Failover 될 수 있기 때문에 반드시 이 방법을 사용해야 한다. 클러스터가 아닌 시스템에서는 Manager를 반드시 서비스로 실행할 필요는 없지만 적극 권장한다. Windows에서 Manager 프로세스가 서비스로 생성될 때 기본 이름은 GGSMGR이다. 이 이름은 변경 가능하다. 동일한 서버에 Oracle GoldenGate Manager 서비스가 두 개 이상(예: Oracle GoldenGate for Oracle 10g에 대한 서비스와 Oracle GoldenGate for Oracle 11g에 대한 서비스) 필요한 경우에는 사용자 정의 이름을 지정하는 것이 필요하다.

또한 Oracle GoldenGate Veridata를 실행하려는 경우에도 별도의 Manager 서비스가 필요하다. Manager 서비스에 다른 이름을 지정하는 방법은 다음과 같다.

GGSCI 명령 프롬프트에서 다음 명령을 내린다.

EDIT PARAMS ./GLOBALS

이때 나타나는 메모장 편집기에서 새 파일 만들기를 선택하고(이 과정을 처음 수행하는 경우) 다음 줄을 입력한 다음 GLOBALS 파일을 저장한다.

MGRSERVNAME OracleGGSMGR1

OracleDBConsole<instancename>, OracleJobScheduler<instancename> 등의 서비스와 같이 보통 Oracle이라는 접두어가 붙는 Windows용 Oracle 서비스의 일반 규칙에 따라 이름 OracleGGSMGR1을 사용한다. Manager 서비스는 C:\OGG10G 디렉터리에서 Install 명령을 통해 쉽게 생성할 수 있다.

<그림 4>와 같이 Addservice 명령을 사용한다.

< 그림 4 > 관리자 서비스 만들기

새 서비스가 Windows 서비스 목록에 자동 서비스로 나타난다(< 그림 5 > 참조).

< 그림 5 > 관리자 서비스 속성

Manager 서비스는 로컬 시스템 계정으로 실행하거나 Addservice 명령에서 지정한 사용자 이름과 암호를 사용하여 특정 계정으로 실행할 수 있다. 이제 다음 명령으로 Manager 서비스를 시작할 수 있다.

GGSCI (HaviPori) 1> start manager
Starting Manager as service ('OracleGGSMGR1')...
Service started.

Windows 이벤트 메시지 설치

이벤트 메시지를 Windows 이벤트 관리자가 표시할 수 있도록 install 명령을 사용하여 Windows 레지스트리에 설치할 수도 있다. 다음 명령을 사용하면 된다.

C:\OGG10G>install addevents
Oracle GoldenGate messages installed successfully.
Install program terminated normally.

일반적 메시지가 아닌 구체적인 것이 생성되도록 하려면 category.dll과 ggsmsg.dll 파일을 C:\OGG10G 디렉터리에서 C:\WINDOWS\system32 디렉터리로 복사하면 된다.

복제 설정 - HRPRD2상의 Extract 프로세스

Oracle GoldenGate Manager프로세스가 작동하여 실행된다. 다음 단계는 데이터베이스 사이에 복제를 설정하는 것이다. 이를 위해 최소한 한 개의 Extract 및 Replicat 그룹을 만들고 구성해야 한다. 이들 그룹에서 캡처해서 복제할 데이터를 지정한다. Extract 프로세스는 원본 데이터베이스에서 발생한 데이터 변경사항을 캡처하고 이것을 대상 서버에 있는 trail로 생성한다. Replicat 프로세스는 대상서버에서 작동하고 있으며 trail에서 데이터 변경사항을 가져와 대상 데이터베이스에 적용함으로써 실제 복제를 실행한다. 또한 데이터베이스의 Supplemental Logging 기능을 켜서 Oracle GoldenGate 복제를 실행할 수 있도록 해야 한다.

이 작업은 데이터베이스에 SYSDBA로 로그온한 상태에서 다음 명령을 수행해야 한다.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> alter system switch logfile;
System altered

이제 Extract 그룹을 추가할 준비가 되었다.

GGSCI (HaviPori) 1> ADD EXTRACT emp_ext, TRANLOG, BEGIN NOW
EXTRACT added.

TRANLOG를 지정했는데, 이것은 트랜잭션(Redo) 로그가 데이터 원본임을 의미한다. BEGIN 다음에는 처리를 시작할 로그의 타임 스탬프나, NOW 옵션을 주어 즉시 시작한다는 것을 나타낼 수 있다. 또는 TRANLOG를 EXTSEQNO(시작할 Oracle Redo 로그의 시퀀스 번호)나 EXTRBA(해당 로그의 상대 바이트 주소) 옵션과 함께 사용할 수 있다.

참고로 Extract 그룹의 이름에는 8문자만이 허용되는데, 다음을 지정하면 전체 도움말을 볼 수 있다.

GGSCI (HaviPori) 1> help add extract

다음 단계는 RMTTRAIL 또는 EXTTRAIL을 추가하는 것이다. 전자는 원격 시스템에 사용되며 후자는 동일한 로컬 시스템에서의 복제를 설정하는 경우에 권장된다. 다음 명령에 의해 생성되는 EXTTRAIL을 사용한다.

GGSCI (HaviPori) 1> ADD EXTTRAIL C:\OGG10G\dirdat\et, EXTRACT emp_ext
EXTTRAIL added.

EXTTRAIL은 서버의 물리적 디렉터리, 주로 Oracle GoldenGate 서브 디렉터리 중 하나인 dirdat에 지정된다. 실제 파일이 생성될 때 지정한 두 문자에 숫자 접미어가 추가되어 파일 이름을 만들기 때문에, 지정된 파일 이름은 두 문자로만 지정할 수 있다. 예를 들어 C:\OGG10G\dirdat\et000001 파일은 EXTRACT 프로세스가 시작되어 데이터 변경이 일어날 때 압축이 풀린 파일로 생성된다.

RMTTRAIL의 구문도 마찬가지이며, 단지 이 명령을 사용할 때에는 단어 EXTTRAIL을 RMTTRAIL로 바꾸기만 하면 된다. 주요 차이점은 Extract의 매개변수 파일에 있는데, 이 매개변수 파일은 다음 단계에서 생성된다. RMTTRAIL의 경우 매개변수 파일에는 원격 호스트와 원격 관리자 포트 번호를 설명하는 추가 매개변수가 포함되어 있다.

이제 다음과 같이 Extract 그룹 emp_ext에 대한 매개변수 파일을 만든다.

GGSCI (HaviPori) 1> EDIT PARAM emp_ext

이 파일에 다음 줄을 입력한다.

EXTRACT emp_ext
USERID system@localhost:1521/HRPRD2, PASSWORD hrpassword1
EXTTRAIL C:\OGG10G\dirdat\et
TABLE hr.employees;

위 명령은 첫 번째 Oracle 10g 데이터베이스(HRPRD2)에서 Extract 그룹이 HR.EMPLOYEES 테이블의 변경 사항을 추출하도록 지정한 것이다. RMTTRAIL을 사용할 경우에는 위 명령을 다음과 같이 변경하면 된다.

RMTHOST <remote host>, MGRPORT <remote manager port number>
RMTTRAIL C:\<remote GoldenGate Directory>\dirdat\rt

이제 다음 명령을 사용하여 Extract 프로세스를 시작할 수 있다.

GGSCI (HaviPori) 1> START EXTRACT EMP_EXT
Sending START request to MANAGER ('OracleGGSMGR1') ...
EXTRACT EMP_EXT starting

프로세스에 관한 상태와 정보는 다음과 같이 확인할 수 있다.

GGSCI (HaviPori) 2> STATUS EXTRACT EMP_EXT
EXTRACT EMP_EXT: RUNNING
GGSCI (HaviPori) 3> INFO EXTRACT EMP_EXT
EXTRACT EMP_EXT Last Started 2009-12-08 13:04 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:00 ago)
Log Read Checkpoint Oracle Redo Logs
2009-12-08 13:05:58 Seqno 11, RBA 578560

또한 프로세스에 관한 자세한 정보를 표시할 수도 있다.

GGSCI (HaviPori) 4> INFO EXTRACT EMP_EXT detail
EXTRACT EMP_EXT Last Started 2009-12-08 13:04 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:00 ago)
Log Read Checkpoint Oracle Redo Logs
2009-12-08 13:07:19 Seqno 11, RBA 596992
Target Extract Trails:
Remote Trail Name Seqno RBA Max MB
C:\OGG10G\dirprm\et 0 923 10
Extract Source Begin End
D:\ORADATA\HRPRD2\REDO01.LOG 2009-12-08 12:59 2009-12-08 13:07
Not Available * Initialized * 2009-12-08 12:59
Current directory C:\OGG10G
Report file C:\OGG10G\dirrpt\EMP_EXT.rpt
Parameter file C:\OGG10G\dirprm\EMP_EXT.prm
Checkpoint file C:\OGG10G\dirchk\EMP_EXT.cpe
Process file C:\OGG10G\dirpcs\EMP_

Extract 문제 해결

오류가 있으면 Extract 프로세스가 STOPPED(정지됨)로 나타날 수 있다. 이 경우 오류를 조사해야 한다. 먼저 위의 자세한 정보에서 언급한 보고서 파일 C:\OGG10G\dirrpt\EMP_EXT.rpt를 확인한다. 그런 다음 주 C:\OGG10G 디렉터리에 있는 로그 파일 ggserr.log 및 sqlnet.log에서 관련 메시지가 있는지 확인한다.

ggserr.log 파일에는 다음과 같은 이벤트 정보가 들어 있다.

2009-12-02 14:53:26 GGS INFO 301 Oracle GoldenGate
Manager for Oracle, mgr.prm: Command received from GGSCI on host
10.10.10.10 (START EXTRACT EMP_EXT )
2009-12-02 14:53:26 GGS INFO 302 Oracle GoldenGate Manager for
Oracle, mgr.prm:
EXTRACT EMP_EXT starting.

이 이벤트는 다음과 같은 방법으로 확인할 수도 있다.

GGSCI (HaviPori) 1> VIEW GGSEVT
...
....
2009-12-02 15:09:34 GGS INFO 302 Oracle GoldenGate Manager for
Oracle,
mgr.prm: EXTRACT EMP_EXT starting.
2009-12-02 15:13:26 GGS INFO 399 Oracle GoldenGate Command
Interpreter
for Oracle: GGSCI command (HaviPoriH): EDIT PARAM emp_ext.

sqlnet.log 파일에 다음과 같은 오류가 나타날 수 있다.

TNS-12557: TNS:protocol adapter not loadable

이 오류는 데이터베이스 연결 문제를 표시해 주므로 리스너를 점검해야 한다. 시작할 때 Path(경로)에 오라클 홈 bin 하위 디렉터리가 포함되어 있는지 확인한다. 다른 TNS 오류가 있을 수 있으며, 이 경우 다른 해결 방법이 표시될 수 있다.

EMP_EXT.rpt에 다음과 같은 오류가 표시될 수 있다.

2009-12-08 13:01:27 GGS ERROR 182 OCI Error beginning
session (status = 28009-ORA-28009: connection as SYS should be as
SYSDBA or SYSOPER).
2009-12-08 13:01:27 GGS ERROR 190 PROCESS ABENDING.

이것은 Extract 프로세스가 SYSDBA 없이 sys로 로그온하려 하고 있음을 나타낸다. Extract에 대한 매개변수 파일의 Login을 'system'으로 변경하면 된다. 여기서 Extract 매개변수 파일의 Login 줄 끝에 SYSDBA를 지정하는 방법은 통하지 않는다는 데 유의해야 한다(단, 다음에 보게 될 GGSCI의 DBLOGIN 명령에서는이 방법이 가능함).

ggserr.log 파일에 유사한 오류가 나타날 수 있다.

2009-12-03 00:43:16 GGS INFO 399 Oracle GoldenGate
Command Interpreter for Oracle: GGSCI command (HaviPoriH): start
manager.
2009-12-03 00:43:25 GGS ERROR 182 Oracle GoldenGate
Manager for Oracle, mgr.prm: OCI Error during OCIServerAttach
(status = 12154-ORA-12154: TNS:could not resolve the connect identifier
specified).
2009-12-03 00:43:25 GGS ERROR 190 Oracle GoldenGate
Manager for Oracle, mgr.prm: PROCESS ABENDING.

위의 오류는 Manager 매개변수 파일 mgr.prm에 제공한 로그인 자격 증명 문제를 나타낸다. 따라서 Manager는 시작되지 않은 것이다. 있을 수 있는 또 다른 오류는 Extract 보고서 파일에서 확인할 수 있다. 예를 들어 EMP_EXT.rpt에는 다음 오류가 있을 수 있다.

2009-12-07 16:40:08 GGS ERROR 190 No minimum supplemental
logging is enabled. This may cause extract process to handle key
update incorrectly if key column is not in first row piece.
2009-12-07 16:40:08 GGS ERROR 190 PROCESS ABENDING.

이 오류에 대한 해결 방법은 데이터베이스의 Supplemental Logging을 활성화 하는 것이다. 여기서는 이미 이 작업을 수행했으므로 오류가 나타나지 않는다.

HRPRD3에서 Replicat 프로세스 설정

이제 대상 데이터베이스 HRPRD3(이 경우 동일한 서버에 있음)로 이동한다. 역시 Oracle 10g 데이터베이스이므로 하위 디렉터리 C:\OGG10G의 동일한 Oracle GoldenGate for Oracle 10g 및 동일한 Manager 서비스를 사용할 수 있다. 데이터베이스가 다른 서버에 있는 경우 데이터베이스 버전에 맞는 Oracle GoldenGate 소프트웨어를 설치한 다음 해당 서버에서 새 Manager 서비스를 구성해야 한다. 첫 단계는 데이터베이스 명령을 내릴 수 있도록 GGSCI 에서 DBLOGIN을 사용하여 데이터베이스에 연결하는 것이다.

DBLOGIN이 다음과 같이 제대로 작동하는지 확인한다.

GGSCI (HaviPori) 1>
DBLOGIN USERID system@localhost:1521/HRPRD3 PASSWORD
hrpassword1
Successfully logged into database.

DBLOGIN에는 다른 두 개의 매개변수 SOURCEDB와 TARGETDB가 있다. 이 매
개변수들은 Oracle 이외의 데이터베이스만를 위한 것이다.

이제 복제 중인 Employees 테이블에 대한 Checkpoint 테이블을 추가해야 한다.

GGSCI (HaviPori) 2> ADD CHECKPOINTTABLE HR.EMPLOYEES_CHKPT
Successfully created checkpoint table HR.EMPLOYEES_CHKPT.

이 테이블에 저장된 Checkpoint는 Replicat 프로세스의 현재 읽기, 쓰기 위치를 가리킨다. 이 과정은 프로세스를 다시 시작해야 하는 경우 또는 서버에 오류가 있거나 네트워크에 다른 방법으로 데이터 손실을 야기할 수 있는 일시적 장애가 있는 경우 데이터 손실을 방지하기 위해 사용된다. 다른 장점은 Checkpoint를 사용하여 동일한 trail을 복수의 Extract 또는 Replicat 프로세스에서 읽을 수 있다는 것이다.

Extract 및 Replicat 프로세스가 배치 모드로 실행될 때는 일반적으로 재시작을하기 때문에 Checkpoint가 필요하지 않으며, 따라서 Checkpoint는 선택사항이다. 그러나 Extract 및 Replicat 프로세스를 지속적으로 실행해야 하는 경우에는 Checkpoint가 필요하다. heckpoint는 보통 dirchk 하위 디렉터리에 파일로 유지되고 있지만, Replicat의 경우에는 데이터베이스의 Checkpoint 테이블에 저장할 수 있다. 만약 GLOBALS 매개변수 파일에 Checkpoint 테이블을 지정하면 ADD CHECKPOINT 명령에 별도로 지정할 필요는 없다. 다음과 같은 모습이 될 수 있다.

GGSCI (HaviPori) 3> ADD CHECKPOINTTABLE
No checkpoint table specified, using GLOBALS specification (hr.
employees_chkpt).
Successfully created checkpoint table HR.EMPLOYEES_CHKPT.

이제 다음과 같이 첫 번째 데이터베이스에서 Extract 그룹 설정에 사용했던 것과 같은 EXTTRAIL을 지정하여 Replicat 그룹을 추가할 수 있다. 그러면 Replicat 그룹이 Extract 그룹에서 만든 trail에 정보를 기록하거나 해당 trail을 사용하게 된다.

GGSCI (HaviPori) 4> ADD REPLICAT emp_rep, EXTTRAIL C:\OGG10G\
dirdat\et, CHECKPOINTTABLE hr.employees_chkpt,
REPLICAT added.

다음과 같이 매개변수 파일을 이 Replicat 그룹에 맞게 편집한다.

GGSCI (HaviPori) 5> EDIT PARAM emp_rep

새 파일에 다음 내용을 입력한다.

REPLICAT emp_rep
USERID system@localhost:1521/HRPRD3, PASSWORD hrpassword1
ASSUMETARGETDEFS
MAP hr.employees, TARGET hr.employees;

이 테이블은 똑같은 DDL 구조를 가지고 있기 때문에 ASSUMETARGETDEFS 매개변수를 사용한다.

이제 Replicat 그룹을 시작할 수 있다.

GGSCI (HaviPori) 6> start REPLICAT emp_rep
Sending START request to MANAGER ('GGSMGR') ...
REPLICAT EMP_REP starting

상태가 표시되려면 잠시 기다려야 하는데, 즉시 확인하려고 하면 상태가 'stopped(정지됨)'으로 나타날 수 있다. 상태가 'running(실행 중)'으로 표시되면 자세한 정보를 확인하고 info all 명령을 내려서 실행 중인 모든 프로세스를 표시할 수 있다.

GGSCI (HaviPori) 7> status REPLICAT emp_rep
REPLICAT EMP_REP: STOPPED
GGSCI (HaviPori) 8> status REPLICAT emp_rep
REPLICAT EMP_REP: RUNNING
GGSCI (HaviPori) 11> info REPLICAT emp_rep detail
REPLICAT EMP_REP Last Started 2009-12-08 13:35 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:01 ago)
Log Read Checkpoint File C:\OGG10G\dirdat\et000001
2009-12-08 13:33:24.000000 RBA 985
Extract Source Begin End
C:\OGG10G\dirdat\et000001 2009-12-08 13:33 2009-12-08 13:33
C:\OGG10G\dirdat\et000000 * Initialized * 2009-12-08 13:33
Current directory C:\OGG10G
Report file C:\OGG10G\dirrpt\EMP_REP.rpt
Parameter file C:\OGG10G\dirprm\EMP_REP.prm
Checkpoint file C:\OGG10G\dirchk\EMP_REP.cpr
Checkpoint table HR.EMPLOYEES_CHKPT
Process file C:\OGG10G\dirpcs\EMP_REP.pcr
Error log C:\OGG10G\ggserr.log
GGSCI (HaviPori) 12> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EMP_EXT 00:00:00 00:00:03
REPLICAT RUNNING EMP_REP 00:00:00 00:00:06

이제 두 개의 Oracle Database 10g 데이터베이스 HRPRD2와 HRPRD3 사이에서 데이터 복제를 테스트할 수 있다. 그러나 테스트는 다음 단계에서 Oracle 11g 데이터베이스 HRPRD1에 대한 복제까지 설정하고 나서 해보기로 한다.

HRPRD1에서 Replicat 프로세스 설정

Oracle 11g 데이터베이스 HRPRD1도 같은 서버에 있다. 이 데이터베이스는 11g 버전이기 때문에 이 글의 시작 부분에서 압축을 풀어 C:\OGG11G 디렉터리에 저장했던 Oracle GoldenGate for Oracle 11g를 사용해야 한다.

Oracle 11g 데이터베이스 홈에 대해 환경 변수를 다음과 같이 설정한다.

set ORACLE_HOME=C:\app\havipori\product\11.1.0\db_1
set ORACLE_SID=HRPRD1
set PATH=%ORACLE_HOME%\bin;%PATH%
set LD_LIBRARY_PATH=C:\OGG11G; %ORACLE_HOME%\LIB;
%ORACLE_HOME%\jdbc\lib
set CLASSPATH=;C:\Program Files\Java\jre6\lib\ext\QTJava.
zip;%ORACLE_HOME%\jdbc\lib

Oracle GoldenGate for Oracle 10g 설치와 유사한 단계에 따라 Oracle GGSMGR2라는 Manager 서비스를 만들고 해당 서비스를 시작한다. 작업 하위 디렉터리와 mgr.prm 파일을 만든다. 이 단계는 아래와 같다.

C:\OGG11G>ggsci
GGSCI (HaviPori) 1> EDIT PARAMS ./GLOBALS

이 파일에 MGRSERVNAME OracleGGSMGR2를 입력하고 파일을 저장한다.

C:\OGG11G>install addservice
Service 'OracleGGSMGR2' created.
Install program terminated normally.
GGSCI (HaviPori) 1>create subdirs
Creating subdirectories under current directory C:\OGG11G
Parameter files C:\OGG11G\dirprm: created
Report files C:\OGG11G\dirrpt: created
Checkpoint files C:\OGG11G\dirchk: created
Process status files C:\OGG11G\dirpcs: created
SQL script files C:\OGG11G\dirsql: created
Database definitions files C:\OGG11G\dirdef: created
Extract data files C:\OGG11G\dirdat: created
Temporary files C:\OGG11G\dirtmp: created
Veridata files C:\OGG11G\dirver: created
Veridata Lock files C:\OGG11G\dirver\lock: created
Veridata Out-Of-Sync files C:\OGG11G\dirver\oos: created
Veridata Out-Of-Sync XML files C:\OGG11G\dirver\oosxml: created
Veridata Parameter files C:\OGG11G\dirver\params: created
Veridata Report files C:\OGG11G\dirver\report: created
Veridata Status files C:\OGG11G\dirver\status: created
Veridata Trace files C:\OGG11G\dirver\trace: created
Stdout files C:\OGG11G\dirout: created
GGSCI (HaviPori) 2> EDIT PARAMS mgr

이 파일에 다음 줄을 입력하고 파일을 저장한다. 참고로 다른 포트 번호를 사용했고 Manager는 이제 HRPRD1에 로그온한다는 것을 알 수 있다.

PORT 7810
USERID system@localhost:1521/HRPRD1 PASSWORD hrpassword1

이제 Manager를 시작할 수 있다. 다음 단계는 DBLOGIN을 사용하여 Oracle 11g 데이터베이스에 로그인한 다음 이 데이터베이스에 Checkpoint 테이블을 만드는것이다.

GGSCI (HaviPori) 3> start manager
Starting Manager as service ('OracleGGSMGR2')...
Service started.
GGSCI (HaviPori) 4> DBLOGIN USERID system@localhost:1521/HRPRD1
PASSWORD hrpassword1
Successfully logged into database.
GGSCI (HaviPori) 5> ADD CHECKPOINTTABLE HR.EMPLOYEES_CHKPT
Successfully created checkpoint table HR.EMPLOYEES_CHKPT.

이제 이 데이터베이스 HRPRD1에 두 번째 Replicat 그룹을 만들 준비가 되었다. 참고로 앞서 HRPRD3 데이터베이스에서 사용한 것과 똑같은 trail을 사용한다. 이렇게 해서 HRPRD2에서 오는 동일한 trail을 두 Replicat 그룹의 두 데이터베이스 HRPRD3과 HRPRD1에서 사용하고 있는데, 이 상황이 매우 인상적이다.

ADD REPLICAT 명령에 BEGIN NOW를 사용하지 않으면 Replicat 프로세스는 추적 기록의 시작 부분에서 바로 시작하는데, 그렇게 하면 된다.

GGSCI (HaviPori) 6> ADD REPLICAT emp_rep, EXTTRAIL C:\OGG10G\
dirdat\et, CHECKPOINTTABLE hr.employees_chkpt,
REPLICAT added.
GGSCI (HaviPori) 7> EDIT PARAM emp_rep

이 파일에 다음 줄을 입력한다.

REPLICAT emp_rep
USERID system@localhost:1521/HRPRD1, PASSWORD hrpassword1
ASSUMETARGETDEFS
HANDLECOLLISIONS
DISCARDFILE c:\OGG11G\dirrpt\emp_ext.dsc, purge
MAP hr.employees, TARGET hr.employees;

여기에는 HANDLECOLLISIONS 및 DISCARDFILE 등과 같은 매개변수가 지정되어 있는데, 이것들은 테이블 내의 중복 같은 오류를 처리하기 위한 것이다. 이 매개변수들을 여기서는 옵션으로 지정했지만 첫 번째 Replicat 그룹 매개변수 파일에 저장할 수도 있다. HANDLECOLLISIONS는 원본 데이터베이스가 활성화되어 있는 동안 초기 데이터 로드에 대한 자동 오류 처리를 수행한다. 초기 로드가 완료된 후에는 반드시 이 매개변수를 제거해야 한다. DISCARDFILE 매개변수는 오류가 발생한 레코드에 대한 기록이 저장될 파일을 지정한다.

이제 Replicat 그룹을 시작하고 상태를 확인한다.

GGSCI (HaviPori) 8> start REPLICAT emp_rep
Sending START request to MANAGER ('GGSMGR2') ...
REPLICAT EMP_REP starting
GGSCI (HaviPori) 9> status REPLICAT emp_rep
REPLICAT EMP_REP: STOPPED

상태는 정지된 것(Stopped)으로 나타나지만 C:\OGG11G 디렉터리의 로그 파일에는 오류가 나타나 있지 않다.

이때 오류를 표시하는 유일한 방법은 명령 프롬프트에서 Replicat를 실행하는 것 뿐이다. 이 방법은 디버깅 목적을 위한 것이다. 이 때 오류는 보고서 파일이 아닌 화면상에 나타난다. 이러한 오류는 우리가 사용하는 테스트 서버의 환경적인 문제일 가능성이 크며 Oracle GoldenGate for Oracle 11g에서는 이 방법을 통해 문제를 찾아낼 수 있다. 사용할 명령은 다 paramfile c:\OGG11G\dirprm\emp_rep.prm
***********************************************************************
Oracle GoldenGate Delivery for Oracle
Version 10.4.0.19 Build 002
Windows (optimized), Oracle 11 on Sep 18 2009 16:44:02
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
Starting at 2009-12-09 11:46:28
***********************************************************************
Operating System Version:
Microsoft Windows XP Professional, on x86
Version 5.1 (Build 2600: Service Pack 2)
Process id: 1504
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
REPLICAT emp_rep
USERID system@localhost:1521/HRPRD1, PASSWORD ***********
ASSUMETARGETDEFS
HANdlecollisions
Discardfile c:\OGG11G\dirrpt\emp_ext.dsc, purge
MAP hr.employees, TARGET hr.employees;
CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 512M
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 1G
CACHESIZEMAX (strict force to disk): 881M
Database Version:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
Database Language and Character Set:
NLS_LANG environment variable not set, using default value
AMERICAN_AMERICA.WE8M
SWIN1252.
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "WE8MSWIN1252"
Warning: NLS_LANG is not set. Please refer to user manual for more
information.
*****************************************************************
******
** Run Time Messages **
*****************************************************************
******
Opened trail file C:\OGG10G\dirdat\et000001 at 2009-12-09 11:46:35
MAP resolved (entry HR.EMPLOYEES):
MAP HR.EMPLOYEES, TARGET hr.employees;
Using following columns in default map by name:
EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER,
HIRE_DATE,
JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID
Using the following key columns for target table HR.EMPLOYEES:
EMPLOYEE_ID.

이 때 Replicat 프로세스는 화면 출력 상태로 대기 중이다. 새로운 명령창을 열고 GGSCI를 시작한 다음 상태를 확인하면 Replicat 프로세스가 RUNNING(실행 중) 으로 표시된다. Oracle Database 11g에 대한 복제는 이제 작동하기 시작한다.

HANDLECOLLISIONS 및 DISCARDFILE 등과 같은 매개변수는 명령줄에서 Replicat 프로세스를 실행할 때 발생할 수 있는 중복 레코드 오류 때문에 넣은 것이다. 이 오류는 아래의 출력에 나타나 있는데, 이들은 중복 레코드 때문에 문제가 있음을 나타낸다. 좀 더 살펴보면 사용자가 대상 Oracle 11g 데이터베이스에 직접 레코드를 삽입했고 이 레코드는 Oracle GoldenGate가 Oracle 10g 데이터베이스에 복제한 데이터와 같다는 것을 알 수 있다. 이로 인해 복제를 중단시키는 레코드 중복 문제가 발생했다.

…
***********************************************************************
** Run Time Messages **
***********************************************************************
Opened trail file C:\OGG10G\dirdat\et000000 at 2009-12-09 11:17:51
Switching to next trail file C:\OGG10G\dirdat\et000001 at 2009-12-09
11:17:51 du
e to EOF, with current RBA 923
Opened trail file C:\OGG10G\dirdat\et000001 at 2009-12-09 11:17:51
Processed extract process graceful restart record at seq 1, rba 923.
MAP resolved (entry HR.EMPLOYEES):
MAP HR.EMPLOYEES, TARGET hr.employees;
Using following columns in default map by name:
EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER,
HIRE_DATE,
JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID
Using the following key columns for target table HR.EMPLOYEES:
EMPLOYEE_ID.
2009-12-09 11:17:54 GGS WARNING 218 Aborted grouped transaction
on 'HR.EMP
LOYEES', Database error 1 (ORA-00001: unique constraint (HR.
EMP_EMAIL_UK) violat
ed).
2009-12-09 11:17:54 GGS WARNING 218 Repositioning to rba 985 in
seqno 1.
2009-12-09 11:17:54 GGS WARNING 218 SQL error 1 mapping
HR.EMPLOYEES to HR
.EMPLOYEES OCI Error ORA-00001: unique constraint (HR.
EMP_EMAIL_UK) violated (status = 1), SQL <INSERT INTO
"HR"."EMPLOYEES" ("EMPLOYEE_ID","FIRST_NAME","LAST_N
AME","EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID","SALARY","COM
MISSION_PCT","MANA
GER_ID","DEPARTMENT_ID") VALUES (:a0,:a1,:a2,:a3,:a4,:a5>.
2009-12-09 11:17:54 GGS WARNING 218 Repositioning to rba 985 in
seqno 1.
….

이 문제를 해결하려면 매개변수 HANDLECOLLISIONS와 DISCARDFILE을 넣고 Replicat 프로세스를 다시 시작해야 한다. DISCARDFILE은 오류가 발생한 레코드에 대한 추가 정보를 제공한다. 현재 명령 프롬프트에서 실행한 Replicat 프로세스가 동작 중인데, trail에 대한 모든 처리를 완료할 수 있게 둔다.

그런 다음 Ctrl-C를 눌러 명령창에서 Replicat 프로세스를 정지한다. 이때 다음 내용이 표시된다.

Enter X to exit or C to continue: X
***********************************************************************
* ** Run Time Statistics ** ************************************************************************
Last record for the last committed transaction is the following:
___________________________________________________________________
Trail name : C:\OGG10G\dirdat\et000001
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 196 (x00c4) IO Time : 2009-12-08 13:43:36.000000
IOType : 5 (x05) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 11 AuditPos : 1188880
Continued : N (x00) RecCount : 1 (x01)
2009-12-08 13:43:36.000000 Insert Len 196 RBA 1295
Name: HR.EMPLOYEES
___________________________________________________________________
Reading C:\OGG10G\dirdat\et000001, current RBA 1604, 2 records
Report at 2009-12-09 11:47:56 (activity since 2009-12-09 11:46:35)
From Table HR.EMPLOYEES to HR.EMPLOYEES:
# inserts: 2
# updates: 0
# deletes: 0
# discards: 0
# insert collisions: 2
Last log location read:
FILE: C:\OGG10G\dirdat\et000001
SEQNO: 1
RBA: 1604
TIMESTAMP: Not Available
EOF: YES
READERR: 400

이제 HANDLECOLLISIONS 매개변수가 동작하지 않도록 주석 접두어 '--'를 앞에 넣는다. 그런 다음 명령줄에서 Replicat 프로세스를 다시 시작한다. 중복 레코드 문제가 해결되었기 때문에 이제 프로세스가 제대로 작동하고 앞으로 원본 데이터베이스에 변경사항이 생기면 정상 복제된다.

복제 시험

HRPRD1, HRPRD2 및 HRPRD3의 3개 데이터베이스 모두 HR.EMPLOYEES 테이블의 레코드는 < 그림 6 >의 명령창에서 확인할 수 있다. 세 데이터베이스 모두 정확히 107개의 레코드가 있다. 내려진 SELECT 명령은 세 번째 창에 나타나 있다. 이제 원본 데이터베이스 HRPRD2의 HR.EMPLOYEES 테이블에 행을 삽입 (INSERT) 후 커밋한 다음 삽입한 행이 대상 데이터베이스 HRPRD3 및 HRPRD1 에 복제되는지 확인한다. INSERT는 < 그림 7 >에 나타나 있다.

3개 데이터베이스에서 다시 SELECT를 실행하면 모든 데이터베이스에 109개의 레코드가 있다는 것을 알 수 있다(<그림 8> 참조). 즉, Oracle GoldenGate for Oracle 10g 및 Oracle 11g를 사용한 복제에 성공했다.

Oracle Database 10g에서 10g 데이터베이스로 복제가 제대로 이루어지는 것을 확인했다. Oracle Database 10g에서 11g 데이터베이스로의 복제도 Replicat 프로세스를 명령 프롬프트에서 "replicat paramfile c:\OGG11G\dirprm\emp_rep.prm" 옵션으로 시작할 경우 가능했다. Replicat 프로세스를 GGSCI에서 시작하면 Stopped 상태로 변한다. 그 이유는 아마 테스트 서버의 환경 오류 때문일 것이다. 참고로 명령 프롬프트에서 시작하면 오류 없이 제대로 작동한다.

< 그림 6 > employee 레코드가 있는 세 데이터베이스

< 그림 7 > HRPRD2 employee 테이블에 삽입하기

< 그림 8 > 다른 데이터베이스에 즉시 복제

기능, 자원 요구사항 및 영향

앞에서 Oracle GoldenGate의 Extract 및 Replicat 프로세스의 작동을 보았다. GGSCI 명령 인터페이스에서는 인스턴스(즉, Manager 프로세스 한 개)당 최대 300개라는 엄청난 숫자의 Extract 및 Replicat 프로세스가 지원된다. 그러나 프로세스 수가 증가한 만큼 메모리 사용량도 증가하는데, Extract 및 Replicat 프로세스 각각에 약 25~55MB의 메모리가 필요하다. 메모리 사용량은 실제로는 더 증가할 수도 있으며 동시 트랜잭션 수와 트랜잭션 크기에 따라 달라진다. 이것은 항상 쉽게 결정하기 어려운 문제이다. 또한 생성되는 redo 로그 수에 따라 Oracle GoldenGate가 약 3~5% 정도의 CPU 부하가 원본 시스템에 발생한다.

여기서는 단방향 복제만 설정했지만, 양방향 멀티마스터(멀티사이트 업데이트) 복제를 설정할 수도 있다. Oracle GoldenGate는 동일 레코드에 대한 업데이트 시점을 파악하여 이러한 환경에서 발생할 수 있는 동시 변경으로 인한 충돌 문제를 해결한다. 충돌 해결을 위한 규칙(타임 스탬프 또는 항상 먼저 작동하는 쪽 등)을 설정하거나 충돌 해결 방법을 user exit으로 작성할 수 있다. Oracle GoldenGate 는 대상 데이터베이스의 테이블에 고유한 키가 없더라도 데이터를 복제할 수 있다. 고유 키가 필요한 삭제 또는 업데이트 작업의 경우에는, 몇 개의 컬럼들 또는 전체 레코드를 고유 키로 사용함으로써 데이터베이스 종류에 상관 없이 이 기능을 지원할 수 있다.

현재 Oracle GoldenGate는 2바이트 데이터를 복제할 수 없지만 향후 릴리스에서는 가능하게 될 것이다. 현재로서는 원본 데이터를 그대로 전달하는 방법으로 2바이트 문자를 처리한다.

Oracle Golden Gate Director

이 제품은 이제 Oracle Management Pack for GoldenGate라고 부르며, GUI 인터페이스에서 Oracle GoldenGate 시스템을 모니터링 및 관리하기 위한 Multi-tier 클라이언트-서버 애플리케이션이다. Oracle GoldenGate Director의 원격 클라이언트를 인스턴스가 실행 중인 호스트에 설치하면 다수의 Oracle GoldenGate 인스턴스(Manager 프로세스)를 관리할 수 있다.

클라이언트는 모두 Oracle GoldenGate Director 서버의 중앙 시스템에 연결되며 애플리케이션 서버로 Oracle WebLogic Server 11g(10.3.1)를 사용한다. 이전에는 JBoss Application Server 3.2.7을 제공했었다. 지금은 Oracle WebLogic Server를 대신 제공하는데, Oracle GoldenGate Director 서버를 설치하기 전에 이 서버를 시스템에 미리 설치해야 한다. Oracle GoldenGate Director 서버에는 웹 서버가 있으며 기존의 Oracle Database에 최소 200MB의 데이터베이스저장소가 필요하다. 또는 MySql이나 Microsoft SQL Server를 저장소로 사용할 수 있다. 보안을 위해 저장소에 사용되는 데이터베이스 암호는 최소 8문자여야 하며 최소 숫자 1개와 영숫자 1문자를 포함해야 한다.

GGSCI에서 프로세스를 정의하면 Oracle GoldenGate Director를 사용하여 해당 프로세스들을 구성하고 모니터링 및 제어할 수 있다. OS 명령줄에서 실행되는 다른 독립형 프로세스(예: DDLGEN 프로세스)는 Oracle GoldenGate Director로 모니터링할 수 없다. Extract 프로세스, Replicat 프로세스, Manager 프로세스, trail로컬 또는 원격), 파일(로컬 또는 원격) 및 Extract 및 Replicat 작업은 모두 Windows, UNIX 및 NonStop 플랫폼 기반의 Oracle GoldenGate Director로 구성하고 모니터링 및 제어할 수 있다.

결론 및 추가 정보

Manager 매개변수 파일에는 동적 포트 할당, Manager가 시작될 때 복제 프로세스(Extract 및 Replicat)를 자동 시작하게 해주는 기능, trail에 대한 중앙 관리등도 지정할 수 있다.

이러한 고급 관리자 매개변수는 The Oracle GoldenGate Administration Guide Version 10.4에 자세히 설명되어 있다. 이 가이드에서는 Extract 및 Replicat 프로세스를 여러가지 다른 방법으로 구성하는 방법 즉, Oracle GoldenGate를 Live Reporting, 실시간 데이터 분산, 실시간 데이터 웨어하우징, Live-Standby 데이터베이스 유지관리 및 Active-Active HA 등의 여러 목적에 사용하는 방법, 그리고 DDL 동기화 구성 방법에 대해 설명한다. 또한 Oracle GoldenGate는 데이터베이스를 다운타임 거의 없이 업그레이드해야 하는 데이터베이스 업그레이드 상황에서 매우 유용할 수 있다. 즉, 이전 데이터베이스 버전에서 새 데이터베이스 버전으로 Oracle GoldenGate 복제를 설정한 다음, 준비가 모두 끝나면 모든 클라이언트를 새 데이터베이스를 가리키도록 전환하기만 하면 된다. 예를 들어 기존의 모든 데이터베이스 내용을 세계에서 가장 빠른 데이터베이스 환경인 Oracle Exadata V2 데이터베이스 시스템으로 이동하는 경우 이 방법은 탁월한 선택이 될 수 있다.

또 하나의 유용한 Oracle GoldenGate 참조서로 Oracle GoldenGate Troubleshooting and Tuning Guide Version 10.4가 있다. 이 참조서와 Administration Guide 둘 다 이 글의 시작 부분에서 다운로드한 문서 zip 파일에 포함되어 있다.

지금까지 설명한 Oracle GoldenGate 복제 환경 구성을 기꺼이 지원해 준(진정한 오라클 정신으로) Oracle Fusion Middleware 및 Oracle GoldenGate 제품 관리자 여러분께 감사하며, Oracle GoldenGate Director를 Oracle Enterprise Manager Grid Control과 통합한다는 계획이 신속히 이루어지기를 기대한다.

Oracle Enterprise Manager Grid Control은 오라클의 강력한 엔터프라이즈 관리 제품이다. 현재 환경의 모든 Oracle RAC 또는 Non-Oracle RAC 데이터베이스, Oracle Automatic Storage Management(ASM) 인스턴스 및 Oracle Clusterware의 패칭을 자동화하는 방법을 배우려면 필자의 기사 “Patch a Thousand Databases, Using Oracle Enterprise Manager Grid Control” (Oracle Enterprise Manager Grid Control을 사용하여 수많은 데이터베이스를 패치하는 방법)을 읽을 것을 권한다. Oracle Enterprise Manager Grid Control 을 사용하여 Oracle Recovery Manager(Oracle RMAN) 백업을 현재 데이터 베이스에 맞도록 쉽게 설정하려면 'Oracle RMAN Backups: Pushing the Easy Button'(Oracle RMAN 백업: Easy 버튼 누르기)을 읽을 것을 권한다.

그리고 Oracle Enterprise Manager Grid Control이 Oracle Data Guard 설정 및 모니터링에 사용할 경우 많은 시간과 자원을 어떻게 절약할 수 있는지 이해 하려면 필자의 또 다른 최근 기사 'Easy Disaster Proof Production with Grid Control'(Grid Control을 이용한 손쉬운 재해 예방 실무)을 읽을 것을 권한다.

오라클의 세계를 즐기시기를!

필자소개

오라클 ACE 감독(데이터베이스)인 포러스 호미 하베왈라(Porus Homi Havewala)는 싱가포르의 오라클 플래티넘 파트너인 S & I Systems Pte Ltd.의 수석 컨설턴트이다. 1994년부터 오라클 기술을 폭 넓게 경험했으며 실무 DBA, 선임 컨설턴트, e-비즈니스 기술 DBA 겸 시스템 관리자, 개발 DBA 및 데이터베이스 설계자/모델러(Oracle Designer를 이용한)로 일해 왔다 최초의 실무 Oracle Enterprise Manager Grid Control 현장에 선임 데이터베이스 설계자 및 기술 팀 리더로 참여했다. 그는 열렬한 Oracle Enterprise Manager Grid Control 전도사이며 Grid 관리에 관한 여러 OTN 기사를 썼다.

• Extract 문제 해결

오류가 있으면 Extract 프로세스가 STOPPED(정지됨)로 나타날 수 있다. 

이 경우 오류를 조사하기.

GGSCI (qasp-db) 6> info extract ebia detail

EXTRACT    EBIA      Last Started 2018-11-27 10:17   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:07 ago)

Log Read Checkpoint  Oracle Redo Logs

                     2018-12-14 17:35:45  Seqno 329593, RBA 261834448

                     SCN 3021.280829753 (12975377030969)

...

Current directory    /new_ogg/ggs

Report file          /new_ogg/ggs/dirrpt/EBIA.rpt

Parameter file       /new_ogg/ggs/dirprm/ebia.prm

Checkpoint file      /new_ogg/ggs/dirchk/EBIA.cpe

Process file         /new_ogg/ggs/dirpcs/EBIA.pce

Stdout file          /new_ogg/ggs/dirout/EBIA.out

Error log            /new_ogg/ggs/ggserr.log

..

자세 정보 보고서 파일 C:\OGG10G\dirrpt\EMP_EXT.rpt를 확인한다. 

그런 다음 C:\OGG10G 디렉터리에 있는 로그 파일 ggserr.log 및 sqlnet.log에서 관련 메시지가 있는지 확인한다.

# CASE ------------------------------------------------------------------------------------

▶sqlnet.log 파일에 다음과 같은 오류가 나타날 수 있다.

TNS-12557: TNS:protocol adapter not loadable

이 오류는 데이터베이스 연결 문제를 표시해 주므로 리스너를 점검해야 한다. 시작할 때 Path(경로)에 오라클 홈 bin 하위 디렉터리가 포함되어 있는지 확인한다. 다른 TNS 오류가 있을 수 있으며, 이 경우 다른 해결 방법이 표시될 수 있다.


▶EMP_EXT.rpt에 다음과 같은 오류가 표시될 수 있다.

2009-12-08 13:01:27 GGS ERROR 182 OCI Error beginning
session (status = 28009-ORA-28009: connection as SYS should be as
SYSDBA or SYSOPER).
2009-12-08 13:01:27 GGS ERROR 190 PROCESS ABENDING.

이것은 Extract 프로세스가 SYSDBA 없이 sys로 로그온하려 하고 있음을 나타낸다. Extract에 대한 매개변수 파일의 Login을 'system'으로 변경하면 된다. 여기서 Extract 매개변수 파일의 Login 줄 끝에 SYSDBA를 지정하는 방법은 통하지 않는다는 데 유의해야 한다(단, 다음에 보게 될 GGSCI의 DBLOGIN 명령에서는이 방법이 가능함).

▶ggserr.log 파일에 유사한 오류가 나타날 수 있다.

2009-12-03 00:43:16 GGS INFO 399 Oracle GoldenGate
Command Interpreter for Oracle: GGSCI command (HaviPoriH): start
manager.
2009-12-03 00:43:25 GGS ERROR 182 Oracle GoldenGate
Manager for Oracle, mgr.prm: OCI Error during OCIServerAttach
(status = 12154-ORA-12154: TNS:could not resolve the connect identifier
specified).
2009-12-03 00:43:25 GGS ERROR 190 Oracle GoldenGate
Manager for Oracle, mgr.prm: PROCESS ABENDING.

위의 오류는 Manager 매개변수 파일 mgr.prm에 제공한 로그인 자격 증명 문제를 나타낸다. 

따라서 Manager는 시작되지 않은 것이다. 

있을 수 있는 또 다른 오류는 Extract 보고서 파일에서 확인할 수 있다. 

예를 들어 EMP_EXT.rpt에는 다음 오류가 있을 수 있다.

2009-12-07 16:40:08 GGS ERROR 190 No minimum supplemental
logging is enabled. This may cause extract process to handle key
update incorrectly if key column is not in first row piece.
2009-12-07 16:40:08 GGS ERROR 190 PROCESS ABENDING.

이 오류에 대한 해결 방법은 데이터베이스의 Supplemental Logging을 활성화 하는 것이다. 

여기서는 이미 이 작업을 수행했으므로 오류가 나타나지 않는다.

HRPRD3에서 Replicat 프로세스 설정

이제 대상 데이터베이스 HRPRD3(이 경우 동일한 서버에 있음)로 이동한다. 

역시 Oracle 10g 데이터베이스이므로 하위 디렉터리 C:\OGG10G의 동일한 Oracle GoldenGate for Oracle 10g 및 동일한 Manager 서비스를 사용할 수 있다. 

데이터베이스가 다른 서버에 있는 경우 데이터베이스 버전에 맞는 Oracle GoldenGate 소프트웨어를 설치한 다음 해당 서버에서 새 Manager 서비스를 구성해야 한다. 

첫 단계는 데이터베이스 명령을 내릴 수 있도록 GGSCI 에서 DBLOGIN을 사용하여 데이터베이스에 연결하는 것이다.

DBLOGIN이 다음과 같이 제대로 작동하는지 확인한다.

GGSCI (HaviPori) 1>
DBLOGIN USERID system@localhost:1521/HRPRD3 PASSWORD
hrpassword1
Successfully logged into database.

DBLOGIN에는 다른 두 개의 매개변수 SOURCEDB와 TARGETDB가 있다. 이 매
개변수들은 Oracle 이외의 데이터베이스만를 위한 것이다.

이제 복제 중인 Employees 테이블에 대한 Checkpoint 테이블을 추가해야 한다.

GGSCI (HaviPori) 2> ADD CHECKPOINTTABLE HR.EMPLOYEES_CHKPT
Successfully created checkpoint table HR.EMPLOYEES_CHKPT.

이 테이블에 저장된 Checkpoint는 Replicat 프로세스의 현재 읽기, 쓰기 위치를 가리킨다. 이 과정은 프로세스를 다시 시작해야 하는 경우 또는 서버에 오류가 있거나 네트워크에 다른 방법으로 데이터 손실을 야기할 수 있는 일시적 장애가 있는 경우 데이터 손실을 방지하기 위해 사용된다. 다른 장점은 Checkpoint를 사용하여 동일한 trail을 복수의 Extract 또는 Replicat 프로세스에서 읽을 수 있다는 것이다.

Extract 및 Replicat 프로세스가 배치 모드로 실행될 때는 일반적으로 재시작을하기 때문에 Checkpoint가 필요하지 않으며, 따라서 Checkpoint는 선택사항이다. 그러나 Extract 및 Replicat 프로세스를 지속적으로 실행해야 하는 경우에는 Checkpoint가 필요하다. heckpoint는 보통 dirchk 하위 디렉터리에 파일로 유지되고 있지만, Replicat의 경우에는 데이터베이스의 Checkpoint 테이블에 저장할 수 있다. 

만약 GLOBALS 매개변수 파일에 Checkpoint 테이블을 지정하면 ADD CHECKPOINT 명령에 별도로 지정할 필요는 없다. 

다음과 같은 모습이 될 수 있다.

GGSCI (HaviPori) 3> ADD CHECKPOINTTABLE
No checkpoint table specified, using GLOBALS specification (hr.
employees_chkpt).
Successfully created checkpoint table HR.EMPLOYEES_CHKPT.

이제 다음과 같이 첫 번째 데이터베이스에서 Extract 그룹 설정에 사용했던 것과 같은 EXTTRAIL을 지정하여 Replicat 그룹을 추가할 수 있다. 그러면 Replicat 그룹이 Extract 그룹에서 만든 trail에 정보를 기록하거나 해당 trail을 사용하게 된다.

GGSCI (HaviPori) 4> ADD REPLICAT emp_rep, EXTTRAIL C:\OGG10G\
dirdat\et, CHECKPOINTTABLE hr.employees_chkpt,
REPLICAT added.

다음과 같이 매개변수 파일을 이 Replicat 그룹에 맞게 편집한다.

GGSCI (HaviPori) 5> EDIT PARAM emp_rep

새 파일에 다음 내용을 입력한다.

REPLICAT emp_rep
USERID system@localhost:1521/HRPRD3, PASSWORD hrpassword1
ASSUMETARGETDEFS
MAP hr.employees, TARGET hr.employees;

이 테이블은 똑같은 DDL 구조를 가지고 있기 때문에 ASSUMETARGETDEFS 매개변수를 사용한다.

이제 Replicat 그룹을 시작할 수 있다.

GGSCI (HaviPori) 6> start REPLICAT emp_rep
Sending START request to MANAGER ('GGSMGR') ...
REPLICAT EMP_REP starting

상태가 표시되려면 잠시 기다려야 하는데, 즉시 확인하려고 하면 상태가 'stopped(정지됨)'으로 나타날 수 있다. 상태가 'running(실행 중)'으로 표시되면 자세한 정보를 확인하고 info all 명령을 내려서 실행 중인 모든 프로세스를 표시할 수 있다.

GGSCI (HaviPori) 7> status REPLICAT emp_rep
REPLICAT EMP_REP: STOPPED
GGSCI (HaviPori) 8> status REPLICAT emp_rep
REPLICAT EMP_REP: RUNNING

1. COUNT 함수

입력되는 데이터의 총 건수를 반환

SELECT COUNT (*) , COUNT(hpage)

FROM professor ;

COUNT(*) = NULL값 포함 결과

COUNT(hpage) = NULL값 제외한 결과 

2. SUM 함수

입력된 데이터들의 합계값을 구하는 함수

3. AVG 함수

입력된 데이터들의 평균값을 구하는 함수

※ 전체 평균을 구할 때 NULL 값이 있을 수도 있기에 NVL함수를 응용해서 구해줌

예) 전체 인원 16명, 보너스 받는인원 10명, 그대로 평균을 구해주면 보너스 총합에서 보너스 받는 인원만 나눠주기에

정확한 결과가 나오지 않는다. 그래서 NVL을 이용해서 보너스 받지않는 인원은 0 으로 해서 평균을 구해준다.

4. MAX / MIN 함수

MAX 함수 = 주어진 데이터 중에서 가장 큰 값을 돌려줌

MIN 함수 = 주어진 데이터 중에서 가장 작은 값을 돌려줌

SELECT MAX(SAL), MIN(SAL)                            SELECT MAX(hiredater), MIN(hiredate)

FROM emp;                                                     FROM emp;

MAX(SAL)   MIN(SAL)                                        MAX(HIREDATE)    MIN(HIREDATE)

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

       5000          800                                            23-MAY-87         17-DEC-80

날짜의 경우 최근 날짜가 크고 이전 날짜가 작다.

5. STDDEV 함수 / VARIANCE 함수

STDDEV 함수 = 표준편차 구하는 함수

VARIANCE 함수 = 분산 구하는 함수

특정 조건으로 세부적인 그룹화 하기 (GROUP BY 절 사용)

GROUP BY 절 사용시 주의사항

1. SELECT 절에 사용된 그룹함수 이외의 컬럼이나 표현식은 반드시 GROUP BY 절에 사용되어야 한다.

2. GROUP BY 절에 사용된 컬럼은 SELECT 절에 사용되지 않아도 된다.

3. GROUP BY 절에는 반드시 컬럼명이 사용되어야 하며 컬럼 Alias 는 사용하면 안된다.

조건을 주고 검색하기 (HAVING 절 사용)

그룹 함수를 비교 조건으로 사용하고 싶은 경우에 WHERE 절 대신 HAVING 절을 사용

자동으로 소계 / 합계를 구해주는 함수

1. ROLLUP 함수

ROLLUP 함수는 주어진 데이터들의 소계를 구해줌

       deptno ,position 컬럼으로 ROLLUP 한 경우

       deptno 컬럼으로 ROLLUP 한 경우                          position 컬럼으로 ROLLUP 한 경우

2. CUBE 함수

ROLLUP 함수와 같이 각 소계도 출력하고 전체 총계까지 출력

     deptno,position 컬럼으로 ROLLUP 한 경우

실전 그룹핑 관련 함수

1. GROUPING SETS 

그룹핑 조건이 여러 개 일 경우 유용하게 사용됨

  기존 방법은 따로 구한뒤 UNION 으로 묶었지만 GROUPING SETS 함수를 사용한 경우 간결하게 쿼리를 작성가능

2. LISTAGG 함수

출력 결과를 가로로 나열

LISTAGG(나열하고싶은 컬럼명,'데이터 구분할 문자') WITHIN GROUP(ORDER BY 가로로나열하고싶은 규칙)

LISTAGG(name,'**') WITHIN GROUP(ORDER BY hiredate) 

3.LAG 함수

이전 행 값을 가져 올 때 사용하는 함수

-문법:

LAG(출력할 컬럼명 , OFFSET , 기본 출력값)

 OVER(Query_partition구문 , ORDER BY 정렬할 컬럼)

4. LEAD함수

LAG 함수와 반대로 이후의 값을 가져오는 함수. LAG 함수와 방법은 동일하나 OFFSET 값이 마지막에 보인다.

5. RANK 함수 - 순위 출력 함수

집계용 - 단일조건 순위 출력

분석용 - 전체조건에서 순위 출력

집계용 문법 

-RANK(조건값) WITHIN GROUP(ORDER BY 조건값 컬럼명 [ASC|DESC])

예 ) 이름이 '송도권' 인 교수의 순위를 조회

분석용 문법 : RANK() 뒤가 WITHIN GROUP 에서 OVER 로 바뀜

-RANK() OVER(ORDER BY 조건컬럼명 [ASC|DESC]

6. SUM( ) OVER 를 활용한 누계 구하기

7. 판매 비율 구하기

RATIO_TO_REPORT 라는 함수를 사용하여 비율을 구할 수 있다.

예 ) panmae 테이블에서 100번 제품의 판매 내역과 각 판매점 별로 판매 비중을 구해보자

8. LAG 함수를 활용한 차이 구하기

출처: http://goalker.tistory.com/72 [오라클 스터디]

•  Flush Buffer Cache

     Oracle 10 g 에서는 다음 명령을 이용해서 Buffer Cache를 Flush한다.

- alter system flush buffer_cache;

     또는 특정 Tablespace를 Offline후 Online하면 해당 Tablespace에 속하는 Buffer들이 Flush된다.

• 10053 Event (CBO Enable optimizer trace) 

CBO의 Optimization 과정을 Trace 파일에 출력한다. Syntax는 다음과 같다. 

- alter session set events '10053 trace name context forever, level 1';

• 10104 Event (dump hash join statistics to trace file)

     Hash Join 수행 과정을 Trace 파일에 출력 상세한 정보를 조회한다. 

     Hash Join을 Troubleshooting할 경우에 주로 사용된다. 

Syntax는 다음과 같다. 

- alter session set events '10104 trace name context forever, level 1';

• 10195 Event (Disable generation of predicates from CHECK constraints)

     CHECK Constraint에 의한 불필요한 fiterting 적용이 발생하는 것을 방지하는 역할을 한다.

Syntax는 다음과 같다. 

- alter session set events '10195 trace name context off'; 
- alter session set events '10195 trace name context forever, level 1';

10195 Event의 사용 예는 다음과 같다.

-- Object 생성
create table t_const1(id number, name varchar2(10), reg_date date);
create index t_const1_idx on t_const1(name);
-- CHECK Constraint 추가 
alter table t_const1 add constraint c1 check(name = trim(name));
-- Plan 확인
explain plan for select count(*) from t_const1
where name = '0987654321';
PLAN_TABLE_OUTPUT
Plan hash value: 225932663
----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     1 |     7 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |              |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| T_CONST1_IDX |     1 |     7 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("NAME"='0987654321')
       filter(TRIM("NAME")='0987654321')  <-- 불필요한 Filtering 추가

alter session set events '10195 trace name context forever, level 1';
PLAN_TABLE_OUTPUT
Plan hash value: 225932663
----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     1 |     7 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |              |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| T_CONST1_IDX |     1 |     7 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("NAME"='0987654321')  <-- 불필요한 Filtring 제거됨

Check Constraint에 의한 Filtering 추가는 일반적으로 성능에 유리한 경우가 많다. 

Filtering에 의해 데이터가 걸러지면 그만큼 일량이 줄어들 가능성이 있기 때문이다. 

특정 쿼리 조건에서는 이러한 Filtering이 오히려 부작용이 있을 수 있으면 그런 특별한 경우에는 10195 Event를 이용해서 Check Constraint에 의한 Filtering 조건 추가를 방지할 수 있다.

• 10200 Event(consistent read buffer status)

Oracle의 Logical Reads를 Trace하는 기능을 제공한다. 

Logical Reads의 작동 메커니즘이나 버그에 의한 Logical Reads 이상 현상을 추적할 때 많이 사용된다. 

Syntax는 다음과 같다. 

- alter session set events '10200 trace name context forever, level 1'; 
- alter session set events '10200 trace name context off';

Logical Reads의 Trace 결과는 다음과 같다.

SQL> alter session set events '10200 trace name context forever, level 1';
SQL> select count(*) from t; 
SQL> alter session set events '10200 trace name context off';

Consistent read started for block 0 : 00400a72
  env: (scn: 0x07df.1aadbf59  xid: 0x0004.009.0000054f  uba: 0x008002de.04ac.05  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x07df.1aad99e4 0sch: scn: 0x0000.00000000)
CR exa ret 2 on:  03C44148  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 0 : 400a72
Consistent read started for block 0 : 00400a75
  env: (scn: 0x07df.1aadbf59  xid: 0x0004.009.0000054f  uba: 0x008002de.04ac.05  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x07df.1aad99e4 0sch: scn: 0x0000.00000000)
CR exa ret 2 on:  03C44148  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 0 : 400a75

• 10626 Event(specify timeout for online index rebuild to wait for DML) 

Online Rebuild 작업이 선행 Transaction에 의해 Blocking된 경우 대기 시간(Timeout)의 최대치를 설정한다.

Change the behaviour of an online index rebuild such that it will timeout, rather than wait indefinitely for existing DML to complete. The event level is the number of seconds the online index rebuild should wait. Values outside the legal range of 2 to 300 seconds will be adjusted automatically.

• 10629 Event(force online index build to backoff and retry DML lock upgrade) 

Online Rebuild시 DML Lock(TM Lock) Blocking을 줄이기 위해 TM Lock을 SS->S로 Upgrade하는 방식을 제어한다. Oracle이 제공하는 설명은 다음과 같다. 

Change the behaviour of an online index rebuild such that it will backoff and retry a failed DML lock upgrade. The event level is the number of retries the online index rebuild should wait. Level 1 means backoff and retry indefinitely. Any other value less than 32 will be adjusted automatically to be 32. 

즉, Online Rebuild를 수행하는 Session이 TM Lock을 S(4)로 획득하지 못하면, 대기 상태에 빠지는 방법으로 TM Lock에 의한 Blocking을 회피한다. 사용법은 다음과 같다. 

- alter session set events '10629 trace name context forever, level 1'; 
- alter session set events '10629 trace name context forever, level 32'; 

Online Rebuild 작업이 중간에 종료되는 경우 Index의 상태가 정리(Clean)되지 않아서 이후에 Index에 대한 DDL이 수행되지 않을 수 있다. 이 경우 ORA-8014 Error가 발생한다.

Oracle 10'g'부터는 dbms_repair.online_index_clean 함수를 이용해서 Online Rebuild에 실패한 Index의 상태를 정리할 수 있다.

• 10704 Event(Print out information about what enqueues are being obtained) 

Enqueue 획득과 해제에 대한 Debugging 정보를 출력한다. 

특정 Operation이 어떤 Enqueue를 필요로 하며, 얼마나 오래 점유하는지 알 수 있다. 

Enqueue Blocking 현상을 Troubleshooting할 때 사용할 수 있다.

Syntax는 다음과 같다. 

- alter session set events '10704 trace name context forever, level 10'; 
- alter session set events '10704 trace name context off'; 

Event 레벨은 다음과 같다.

1. 1-4: print out basic info for ksqlrl, ksqcmi
2. 5-9: also print out stuff in callbacks: ksqlac, ksqlop
3. 10+: also print out time for each line
4. 
   

간단한 DML에 대한 Trace 결과는 다음과 같다. 

CU, FB, HW, TM, TX Lock을 점유하고 해제하는 일련의 작업을 Debugging할 수 있다.

alter session set events '10704 trace name context forever, level 10';
insert into t1 values(1);
alter session set events '10704 trace name context off';

제공 : DB포탈사이트 DBguide.net

플랜이란 무엇인가?

CBO 방식에서 옵티마이저는 주어진 환경(통계정보, SQL문) 하에서 최적의 실행계획(PLAN)을 우리에게 제공한다. 옵티마이저는 우리에게 어떤 경로로 테이블을 접근하는지, 어떤 방식으로 조인하는지, 어떤 인덱스 자원을 사용하는지 등에 대한 최적화한 계획(플랜)을 알려준다.

만약 옵티마이저가 제공하는 플랜이 완벽하다면 오라클은 우리에게 플랜을 제공할 필요가 없으며, 쿼리문에 힌트절을 허용할 이유도 없을 것이다. 물론 개발자들인 우리도 플랜에 대해 알아야 할 이유가 전혀 없을 것이다. 하지만 플랜은 완벽하지 않다. 오히려 간혹 잘못된 정보를 주곤 한다.

체스 챔피언과의 시합에서는 수많은 기보에 대한 충분한 분석이 이루어져서 우승하였지만, 수많은 개발자들이 작성한 무수히 많은 쿼리문에 대해 케이스별로 올바르게 분석하고 대응한다는 것은 불가능에 가깝다. 또한 퀴즈 대결에서는 방대한 데이터 축적과 빅데이터 처리에 최적화한 강력한 성능의 슈퍼 컴퓨터로 우승하였지만, 오라클 서버는 슈퍼컴퓨터가 아니라 그냥 약간 좋은 컴퓨터일 뿐이다. 그리고 쿼리를 분석하는 데 충분한 시간도 주어지지 않는다. 

이와 같이 한정된 분석자료, 한정된 서버자원, 한정된 처리시간 하에서 오라클 옵티마이저가 우리에게 완벽한 플랜을 제공하리라는 어떠한 희망도 근거도 없다. 단지 대체로 괜찮은 플랜을 제공할 뿐이다. 수시로 올바르지 않은 플랜을 제공하기도 한다. 이와 같은 이유 때문에 우리는 플랜을 볼 줄 알아야 하고, 이해할 줄 알아야 하고, 비교할 줄 알아야 하며, 변경할 줄 알아야 한다.

이번 연재 목표는 플랜을 쉽게 보고 쉽게 이해하는 것이다. 또한 플랜을 비교하는 것이다. 비교하기 위해서는 오라클 옵티마이저가 제공하는 플랜 외에 개발자 자신이 생각하는 플랜이 있어야 한다. 플랜을 비교할 때는 개발자가 생각하는 플랜이 항상 올바른 것이고 옵티마이저가 제공하는 플랜은 틀렸다고 전제하고 시작해야 한다. 대부분은 일치하겠지만 만약 서로 다르다면 개발자 플랜을 우선해야 한다. 옵티마이저의 플랜은 의심스러운 눈길로 바라봐야 한다. 

연재의 말미에는 개발자가 생각하는 플랜을 필자는 어떤 방법으로 쉽게 표현하는지에 대한 내용도 있으며, 쿼리 제작 시 개발자의 생각(플랜)을 어떤 방법으로 쿼리에 포함 시키는지에 대한 내용도 있다.

오라클 플랜 보는 법

플랜에 대한 내용은 기본적인 내용이라 일반적인 DB 도서에서는 자세한 내용을 설명하지 않는다. 하지만 필자가 경험하기로는 상당수 개발자들이 의외로 모르고 있는 경우를 많이 보았다. 심지어 쿼리 작성 후 데이터 결과만 확인할 뿐 플랜을 아예 보지 않는 개발자도 많았다. 쿼리 제작 후에는 반드시 플랜을 확인하는 습관을 가져야 한다. 아래 플랜을 보면서 하나씩 배워 보자. 



위 플랜을 해석하면 다음과 같다. 

ACCESS FULL : 고객 테이블 FULL SCAN(전체 접근) 
Cost=633K : 633,000 비용발생(논리적 비용 = IO + MEM + CPU + NET + …) 
Card=42M : 42,000,000건(접근하는 레코드 수) 
Bytes=15G : 15,000,000,000(42,000,000 * 1 ROW의 총 길이) 

ACCESS FULL은 고객 테이블을 FULL SCAN하겠다는 의미다. 어떠한 인덱스도 통하지 않고 테이블을 직접 접근해 전체 데이터를 읽는다는 의미다. 플랜에서 이 용어가 보인다는 것은 다음의 3가지 경우에 해당한다.

첫째, 해당 쿼리에 대한 적절한 인덱스가 존재하지 않는 경우로써 필요한 인덱스를 생성함으로써 해결 가능하다. 
둘째, 인덱스는 존재하지만 부정확한 통계정보로 인하여 인덱스를 타지 않는 경우로써, 최신의 통계 정보를 구성하거나 힌트절을 사용해 해결할 수 있다. 
셋째, 테이블 FULL SCAN하는 것이 인덱스를 통한 랜덤 엑세스보다 유리한 경우로써, 데이터 조회 범위가 커서 인덱스를 사용하는 것이 별로 효용성이 없을 때다. 위의 쿼리는 조회조건이 없으므로 바로 이 경우에 해당한다. 

Cost는 비용을 의미하는데 해당 쿼리가 동작되었을 때 소요하는 비용을 말한다. 비용이 크면 클수록 오라클이 많은 일을 하고 있다고 생각하면 된다. 즉 무거운 쿼리인 것이다. 여기서 비용은 물리적 비용이 아니라, 논리적 비용을 의미한다. 논리적 비용이란 직접적이고 구체적인 수치에 의해 명확하게 알 수 있는 비용이 아니다. 오라클 옵티마이저가 산출한 비용에 대해 우리는 왜 그러한 비용값이 계산되었는지 이해할 필요까지는 없다. 단지 플랜 비교 시 비교 기준 값으로는 삼을 수 있다. 

Card(Cardinality)는 쿼리 조건에 맞는 레코드 건수를 의미한다. 참고로 우리는 K는 10의 3승을 의미하고, M은 10의 6승을 의미하고, G는 10의 9승을 의미함을 이미 알고 있다. 위의 플랜에서 Card 값은 42M이므로 고객 테이블의 데이터 건수가 4,200만임을 알 수 있다. 

Bytes는 쿼리 실행,시 발생하는 네트워크 트레픽을 의미한다. 즉 I/O 발생량이다. 1Row를 구성하는 컬럼의 길이 총 합을 구한 후 Card 값을 곱하면 된다. 결국 위의 플랜에서는 15,000,000,000 Byte라는 어마어마한 네트워크 트레픽이 발생함을 알 수 있다.

위의 플랜을 종합하면, 해당 쿼리는 고객 테이블을 FULL SCAN으로 접근해 4,200만 건의 데이터를 읽어오며, 이때 15,000,000,000Byte의 네트워크 트래픽을 유발한다. 그리고 이때 비용은 633K가 발생함을 오라클 옵티마이저가 알려 주고 있다.

그럼, 계속해서 다음 플랜을 살펴보자. 아래 플랜에서 우리는 플랜을 해석하는 순서를 알아볼 것이다. 또한 ORDER BY 절이 있는 쿼리의 비용(Cost) 부분에 주목해서 살펴볼 것이다. 



플랜을 해석하는 순서는 다음과 같다.

첫째, 레벨(깊이)이 다른 경우에는 안쪽 레벨부터 해석한다.
둘째, 레벨(깊이)이 같은 경우에는 위에서 아래로 해석한다.

따라서 위 플랜은 1번(가져와서), 2번(SORT해서), 3번(보여준다)순으로 해석하면 된다.

플랜을 자세히 보면, 고객 테이블을 FULL SCAN하는 1번의 비용(Cost)은 633K인데 반해, SORT하는 2번의 비용(Cost)은 6M임을 알 수 있다. 결국 4,200만 건의 데이터를 가져오는 비용보다, 가져온 데이터를 SORT하는 비용이 10배 가량 높음을 알 수 있다. 대부부분의 개발자들은 튜닝 시 조건절 컬럼의 인덱스 유무에 관심을 갖지만, SORT를 없애는 것도 튜닝에서 중요한 부분임을 알 수 있다.

계속해서 다음 2개의 플랜을 동시에 살펴보자.




위 2개의 플랜에서 쿼리의 차이점은 ROWNUM < = 1 혹은 2 부분이다. 테이블을 FULL SCAN하기 위해 접근하지만 COUNT(STOPKEY) 부분에서 레코드 건수가 1 혹은 2가 되었을 때 SCAN을 중지하고 빠져 나옴을 알 수 있다. 2개의 플랜에서 ROWNUM 값이 1 혹은 2에 따라서 Card 값과 Bytes 값이 배수가 됨을 알 수 있다. 그런데 비용(Cost) 값은 왜 같을까? 그것은 고객 테이블의 첫 번째 레코드와 두 번째 레코드가 동일 블록에 저장돼 있기 때문일 것이다. 참고로 오라클은 최소 운반 단위인 블록 단위로 데이터를 운반한다.

이번엔 좀더 복잡한 플랜을 인덱스 생성도와 같이 비교해 살펴보자. 



플랜의 해석 순서는, 깊이가 다른 경우에는 안쪽에서 바깥쪽으로, 깊이가 같은 경우에는 위에서 밑으로 해석한다고 이미 설명하였다. 따라서 위 플랜은 1 → 2 → 3 → 4 → 5 → 6순으로 해석한다. 그리고 플랜의 내용에서 스캔은 UNIQUE SCAN임을 알 수 있고, 두 테이블의 조인 방식은 NESTED LOOP JOIN임을 알 수 있다. 즉, 순차적 루프에 의한 접근 방식이다. 조인 방식에 대한 더 자세한 내용은 지난 연재인 ‘오라클 조인의 방식’에서 확인할 수 있다. 

플랜의 해석 순서를 그림으로 변환하면 인덱스 생성도와 동일하다는 것을 우리는 알 수 있다. 
(주문번호 인덱스 → 주문 테이블 → 고객번호 인덱스 → 고객 테이블) 

이번에는 고객 테이블에 인덱스가 없는 경우를 가정해 보았다. 인덱스 생성도와 같이 살펴보자.

고객 테이블의 고객번호 컬럼에 인덱스가 없어서 고객 테이블에서 FULL SCAN이 발생하고 있다. 실제 리턴 결과 건수는 1건이지만, 인덱스가 존재하지 않음에 따라 고객 테이블의 전체 데이터를 FULL SCAN하고 있는 것이다. 여기에서 우리는 Card=1이라는 것에 주목할 필요가 있다. 비록 인덱스는 없지만 고객 테이블에 통계정보가 구성돼 있음을 유추할 수 있고, 고객번호는 UNIQUE함을 추정할 수 있다. 따라서 고객번호 컬럼을 인덱스로 생성해야 함을 알 수 있다. 플랜에서는 인덱스를 생성해야 할 컬럼을 직관적으로 바로 알기는 어려우나, 인덱스 생성도를 함께 이용하면 어떤 위치에 어떤 인덱스를 생성해야 하는지 한눈에 알 수 있다. 

덧붙여, 테이블을 FULL SCAN한다는 의미는 다음의 3가지 경우에 해당함을 다시 한번 강조한다.

첫째, 해당 쿼리에 대한 적절한 인덱스가 존재하지 않는 경우로서 필요한 인덱스를 생성해 해결 가능하다. 둘째, 인덱스는 존재하나 부정확한 통계정보로 인하여 인덱스를 타지 않는 경우로서 최신의 통계 정보를 구성하거나 힌트절을 사용해서 해결 가능하다. 셋째, 테이블을 FULL SCAN하는 것이 인덱스를 통한 랜덤 엑세스보다 유리한 경우로써 데이터 조회 범위가 커서 인덱스를 사용하는 것이 별 효용성이 없을 때다.

이번에는 조인절 양방향 모두에 인덱스가 없는 경우를 가정해 보았다. 



위 쿼리의 문제점은 이런 경우 두 테이블 간 조인 방식은 예전에는 Sort Merge Join 방식으로 풀리는 경우가 많았다. 처리 순서는 다음과 같다.

첫째, 고객 테이블에서 고객명이 ‘홍길동’인 고객을 구한 후 고객번호 순으로 정렬한다(SORT). 
둘째, 주문 테이블에서 주문일자가 ‘20150112’인 주문을 구한 후 고객번호 순으로 정렬한다(SORT). 
셋째, 정렬된 고객 정보와 주문 정보를 고객번호 컬럼으로 결합한다(MERGE).

하지만 Sort Merge Join 방식은 성능상 문제가 많은 조인 방식이다. 해결 방안은 조인절 컬럼에 인덱스를 생성하여 Nested Loop Join 방식으로 플랜이 풀리게 해야 한다. 만약에 고객 테이블의 고객번호를 인덱스로 생성한다면 주문 테이블에서 고객 테이블로 순차적으로 접근할 것이고, 주문 테이블의 고객번호를 인덱스로 생성한다면 고객 테이블에서 주문 테이블로 순차적으로 접근할 것이다. 

만약, 업무 로직상 조인절에 인덱스를 생성하기 곤란한 상황이라면 힌트절을 추가해 Hash Join 방식으로 접근하는 것도 좋은 방법이다. 대부분 Hash Join 방식이 Sort Merge Join 방식보다 성능이 더 좋다. 그래서 요즘 Sort Merge Join 방식은 거의 볼 수 없고 Hash Join 방식을 많이 사용한다.

아래의 경우가 힌트절을 추가해Hash Join 방식을 설명한 것이다. 



Hash Join 방식은 해시 함수를 이용한 접근 방식인데, 대량의 데이터 처리에 효율적인 조인 방식이다. Nested Loop Join 방식에서 처리 범위가 부담스럽거나, Sort Merge Join 방식에서 정렬(Sort)이 부담스러울 때 사용한다. Hash Join 방식의 처리 순서는 다음과 같다. 

첫째, 고객 테이블에서 고객명이 ‘홍길동’인 고객을 구한 후, 조인절 컬럼인 고객번호를 해시 함수로 분류해해시 테이블을 생성한다(해시 함수를 이용해 해시 테이블 생성).
둘째, 주문 테이블에서 주문일자가 ‘20150112’인 주문을 구한 후, 조인절 컬럼인 고객번호를 해시 함수로 변환해해시 테이블로 순차적으로 접근한다(해시 함수를 통해 해시 테이블 탐색).

메모리에 해시 테이블을 생성하고 해시 함수를 이용하여 연산 조인을 하기 때문에 CPU 사용이 증가할 수 있으므로 조회 빈도가 높은 온라인 프로그램에는 적합하지 않는 조인 방식이다.

끝으로, UNION ALL과 UNION 관련하여 다음 2개 플랜을 동시에 살펴보자. 



쿼리에서 UNION ALL 구문을 사용하면 중복되는 데이터를 있는 그대로 모두 보여준다. 하지만 UNION 구문을 사용하면 중복되는 데이터를 제거하고 UNIQUE하게 보여준다는 것을 우리는 이미 알고 있다. 위의 2개 플랜의 차이점은 SORT (UNIQUE) 부분이다. 이것의 의미는 데이터를 정렬한 후에 중복된 데이터를 제거하고 UNIQUE하게 보여 준다는 의미다.

그런데 SORT는 왜 하는가? 왜냐하면 SORT는 중복된 데이터를 제거하는 가장 단순하고 쉬운 방법이기 때문이다. SORT 이후에 동일한 값이 연속 되어 있으면 제거하면 되기 때문이다. 

예전에는 화투 1~2세트는 갖고 있는 집이 많았다. 화투는 기본적으로 1월(솔)부터 12월(비)까지 총 48장으로 구성되는데, 만약 화투 개수가 48장 이상이라면 중복된 화투장을 찾아 빼야 할 것이다. 이때 화투를 모두 펼쳐놓고 같은 모양을 찾아서 빼기란 쉬운 일이 아니다. 아마도 대부분의 사람들은 1월부터 12월까지 화투를 정렬(SORT)한 후, 중복되는 화투장을 찾아내는 방법을 쓸 것이다. UNION 구문 사용 시에 오라클 옵티마이저도 우리와 동일한 방법으로 중복된 데이터를 제거한다. 바로 SORT(정렬)을 이용하고 있는 것이다.

오라클 옵티마이저의 실행계획과 개발자의 실행계획

오라클 옵티마이저의 실행계획(Plan)은 통계정보(과거+현재)를 기반으로 한다. 그에 반해 개발자의 실행계획(Plan)은 과거 + 현재 + 미래 정보를 기반으로 한다. 당연히 개발자의 실행 계획이 더 옳을 것이다. 개발자들은 향후 데이터 증감 추이가 어떻게 될 것인지, 생성 예정인 인덱스는 무엇인지, 또 프로그램을 누가 어떤 용도로 얼마나 빈번하게 사용하는지 등, 오라클 옵티마이저보다 더 많은 정보를 얻을 수 있기 때문에 더 좋은 플랜을 계획할 수 있다.

오라클 옵티마이저 실행계획과 개발자의 실행계획이 일치하면 좋겠지만 항상 일치하진 않는다. 지금까지의 경험상으로 90% 이상은 일치했고, 10% 미만은 일치하지 않았다. 그렇다면 왜 일치하지 않는 걸까? 다음과 같은 여러 가지 이유로 인하여 일치하지 않았다. 

첫째, 통계정보 구성이 실제 데이터를 반영하지 못하거나 없는 경우
둘째, 적절한 인덱스가 존재하지 않거나 부적절한 경우
셋째, 쿼리가 최적화 되어 있지 않는 경우나 잘못 사용된 경우
넷째, 오라클 옵티마이저의 알고리즘이 완벽하지 않다는 현실적인 문제

실행계획이 일치하지 않는 경우에는 통계정보를 재구성하거나 필요한 인덱스 생성 및 쿼리 최적화로 어느 정도 해결할 수 있다. 하지만 옵티마이저의 알고리즘 기술수준 문제에 있어서는 해결 방법이 없다. 이때에는 힌트절을 추가하여 인위적으로 플랜을 변경해야 한다. 오라클이 힌트절 기능을 제공한다는 의미는 곧 오라클 옵티마이저의 알고리즘이 완벽하지 않다는 의미다. 향후 오라클 버전에서 성능 개선이 점차 이루어진다면 힌트절 기능이 축소되거나 동작에 제한이 걸릴 수도 있다. 이는 개발자 중심의 실행계획에서 오라클 중심의 실행계획으로 주도권이 넘어간다는 의미다. 만약 그런 상황이 실제로 온다면 개발자들에게 좋은 상황인지 나쁜 상황인지 고민스럽다. 

오라클 옵티마이저의 실행계획은 어떤 내용인지 이미 알고 있다. 그렇다면 개발자의 실행계획은 무엇인가? 개발자의 실행계획은 개발자의 마음속에 있는 실행 계획이다. 쿼리를 제작할 때 대부분의 개발자들은 이미 마음속에 실행계획(접근순서, 접근방법, 자원선택)을 갖고 있다. 하지만 그러한 실행계획은 어느 순간이 지나면 잊혀지는 찰나적인 생각일 뿐이다. 이러한 고민을 해결하고자 필자는 쿼리를 제작할 때 실행계획의 내용을 쿼리에 포함시키는 방법을 사용한다. 바로 공정 쿼리다. 



우리는 인덱스 생성도에서 테이블 접근 순서 및 인덱스 생성 위치를 알 수 있다. 또한 공정쿼리 작성법에 의해 작성된 쿼리에서도 테이블 접근 순서 및 인덱스 생성 위치를 쉽게 알 수 있다. 작성된 쿼리에 개발자가 생각하는 실행 계획이 내포되어 있어서, 오라클 옵티마이저가 제시하는 플랜과의 비교 작업이 한결 쉬워졌고 언제든지 가능해졌다. 

공정쿼리 방식으로 제작한 쿼리는 다른 개발자가 처음 보더라도 쉽게 플랜을 유추해 낼 수 있고 인덱스 생성 위치를 바로 알 수 있어서 좋다. 더 자세한 내용은 지난 연재인 ‘공정쿼리 작성법’을 참고하기 바란다. 결론적으로 인덱스 생성도와 공정쿼리와 플랜은 형태는 틀리지만 본질적으로 동일한 의미를 지니고 있으므로 삼위일체라 생각할 수 있다.

바인드 변수와 하드 파싱

대부분의 개발자들은 튜닝시 플랜을 상수값으로 테스트하지만, 실제로 바인드 변수로 운영되는 경우에는 플랜이 다를 수 있다. 프로그램에 바인드 변수로 되어 있다면 반드시 바인드 변수로 플랜을 확인해야 한다. 아래 두 개의 쿼리를 살펴보자.

SELECT * FROM 고객 WHERE 고객명 = ‘홍길동’ → 상수값 
SELECT * FROM 고객 WHERE 고객명 = :NAME → 바인드 변수

위의 두 개의 쿼리는 동일한 결과를 조회하지만 플랜은 다른 수 있다. 이와 같은 쿼리들은 대부분의 경우 플랜이 동일하지만 간혹 다른 경우도 있으므로, 쿼리가 어떤 방식으로 운영되는지에 따라 플랜을 구분-확인해야 한다. 

그럼, 바인드 변수를 사용하는 이유는 무엇일까? 바인드 변수 사용은 하드 파싱을 줄이기 위한 수단이다. 오라클 옵티마이저는 상수값이 다르면 서로 다른 쿼리로 인식해 파싱을 새로 한다. 특히 실행 횟수가 많고 컬럼의 distinct 값이 크다면, 하드 파싱이 자주 발생해 시스템 전반적으로 많은 메모리를 사용할 것이고 CPU 부하도 높을 것이다. 반면에 바인드 변수를 사용한다면 오라클 옵티마이저는 동일한 쿼리로 인식하기 때문에 파싱을 매번 하지는 않는다. OLTP 프로그램은 데이터베이스 성능을 고려해 바인드 변수 사용을 권고한다. 

이번 연재에서는 오라클 옵티마이저가 제공하는 플랜과 개발자가 생각하는 플랜을 어떠한 방법으로 쿼리문에 구현해야 하는지에 대해 배웠다. 다음 연재에서는 개발자들의 영원한 숙제인 ‘NULL’에 대해 자세히 알아본다.

용기를 갖자
오라클 DB뿐 아니라 대부분의 DB 구성 알고리즘은 어느 날 ‘하늘에서 뚝 떨어져 새로 만들어진 것’이 아니라 
실생활에서 이용되는 혹은 이미 상식 수준에서 인지되는 그런 보편적인 원리를 바탕으로 만들어졌으므로 
쉽게 접근하고 이해할 수 있다. 서두에서 말했듯이 ‘레몬시장이론’을 상기하며 DB를 지레짐작으로 
어려워하지 말고 용기를 내고 하나씩 터득해 나가기를 바란다.

이 글은 DB 전문가 수준의 이해를 요구하지는 않는다. 단지 DB에 대해서 더 친숙하고 더 쉽게 이해하고 
접근하길 바랄 뿐이다. 이 글을 읽으면서 궁금하거나 의문 나는 점이 있으면, 댓글을 달아주실 것을 적극 바란다.
아무리 어렵고 힘든 일이더라고 ‘관계’와 ‘소통’으로 풀어나갈 수 있음을 다시 한 번 믿으며...

[지난 문제의 정답과 풀이]

원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제

지난 연재에 출제한 ‘원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제’에 대한 정답과 해설은 아래와 같다. 문제를 풀면서 DB 원리를 하나씩 배우고 이해할 수 있다.

[이번 호 문제]

원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제

각 연재의 말미에 간단하면서도 재미있고 생각해 보는 문제를 출제하려 한다. 모든 문제는 DB의 원리를 이해할 수 있는 문제로 출제할 예정이다. 문제를 풀면서 DB 원리를 하나씩 배우고 이해할 수 있다. 정답과 그에 대한 설명은 다음 연재에서 한다.

출처 : 한국데이터베이스진흥원

제공 : DB포탈사이트 DBguide.net