Oracle Secure File 활용


 

 

1      테스트환경

n   서버 : IBM,8202-E4B (8192M)

n   운영체제 : Aix 6.1 TL 06

n   오라클 : Oracle 11.2.0.3.0 Enterprise 64bit

 

2      Oracle Secure File 개요

         Oracle Database 11g에서는 LOB 데이터 유형을 Oracle Secure files 로 완전히 새롭게 설계하여 성능과 관리 효율성 대폭향상.

n         디스크 형식가변 조각 크기

n         네트워크 프로토콜/출력향상

n         버전 지정 및 공유 메커니즘

n         리두 및 언두 알고리즘유저 구성 불필요

n         공간 및 메모리 관련 향상된 기능

n         기존LOB의 데이터의 중복성 제거

         LOG 파일을 Secure Files 로 취급할지 여부를 지정하려면 DB_SECURE FILE 초기화 파라미터 사용합니다.

clip_image002[4] 

 

2.1           Secure Files DB parameter 옵션

n         Always : 모든 LOB SecureFile LOB로 생성하고 ASSM테이블스페이스에 있지 않은 LOB는 모든 SecureFile LOB가 되도록한다

n         Force : 생성되어 전달되는 모든 LOB SecureFile LOB가 되도록한다

n         Permitted : SecureFiles를 생성할 수 있도록 한다 (default )

n         Naver : SecureFiles로 지정되는 모든 LOB BasicFiles 로 생성된다

n         Ignore : SecureFiles를 허용하지 않으며 SecureFiles 옵션을 사용하여 Basic Files를 강제로  변환하는 경우 발생하는 모든 오류를 무시

 

 

2.2           SecureFIiles 저장 영역옵션

n         Maxsize : 최대 LOB 세크먼트 크기를 지정

n         Retention : 사용할 Retention 정책지정

         max : maxsize 에 도달할때가지 이전 버전을 회수하는데 사용

         min : 지정된 시간동안 이전버전을 보관

         auto : 기본적으로 공간과 시간 간의 균형을 유지 자동설정 (default )

         none : 이전 버전을 가능한 만큼 재사용한다

n         CHUNK, PCTVERSION : 더이상 지정할 필요가 없다 기존의  스크립트와의 호환성을 해 남겨둠 구문이 분석되지만 해석되지는 않는다

 

3             Using SecureFiles

n         Securefile로 이용하여 중복제거, 중복허용,암호화,압축등 다양하게 사용할수있다

3.1           기존의 LOB테이블을 Secure file로 이관

  • BasicFile을 이용해서 LOB칼럼을 포함한 테이블을 생성하고, 그 테이블을 SecureFile로 마이그레이션

 

è 테이블 스페이스 생성  user생성 스크립트

CREATE TABLESPACE
secf_tbs1

DATAFILE ‘/oracle/secf_tbs1.dbf’
SIZE 150M REUSE

EXTENT MANAGEMENT
LOCAL

UNIFORM SIZE 64M

SEGMENT SPACE MANAGEMENT
AUTO

 

CREATE TABLESPACE
secf_tbs2

DATAFILE ‘/oracle/secf_tbs2.dbf’
SIZE 150M REUSE

EXTENT MANAGEMENT
LOCAL

UNIFORM SIZE 64M

SEGMENT SPACE MANAGEMENT
AUTO

 

CREATE USER sf_demo IDENTIFIED BY
oracle

DEFAULT TABLESPACE
park

TEMPORARY TABLESPACE
temp

QUOTA UNLIMITED ON
park

GRANT connect, resource TO
sf_demo

GRANT EXECUTE ANY PROCEDURE, CREATE
ANY DIRECTORY TO sf_demo

 

clip_image004[4]

 

è SF_DEMO 스키마에 테이블을 생성한다. RESUME라는 BLOB칼럼을 가지는 RESUMES 테이블을 생성한다.
이 LOB칼럼은 BasicFile 포맷으로 저장된다
.

è Resumes 테이블 생성 스크립트 (sf_medo)

CREATE TABLE resumes

 (id
NUMBER, first_name VARCHAR2(15),

 last_name VARCHAR2(40), resume BLOB)

 LOB(resume) STORE AS BASICFILE

 (TABLESPACE secf_tbs1)

 

clip_image006[4]

è BLOB 칼럼에 MS Word 파일을 저장하기 위해서 다음과 같이 Word 파일이 들어있는 디렉토리 패스를 DIRECTORY 객체로 생성한다.

è DIRECTORY 생성 스크립트

CREATE OR REPLACE DIRECTORY cwd AS
‘/oracle/park/securefiles/files’;

clip_image008[4]

 

è MS Word 파일을 파일시스템에서 읽어 들이는 loadLOBFromBFILE_proc 프로시저를 생성한다.

프로시저는 DBMS_LOB 패키지를 이용한다.

è loadLOBFromBFILE_proc 생성 스크립트

CREATE OR REPLACE PROCEDURE
loadLOBFromBFILE_proc (dest_loc IN OUT BLOB, file_name IN
VARCHAR2)

IS

   src_loc        BFILE := BFILENAME(‘CWD’, file_name);

   amount         INTEGER := 4000;

BEGIN

 

   DBMS_LOB.OPEN(src_loc,
DBMS_LOB.LOB_READONLY);

   amount
:= DBMS_LOB.GETLENGTH(src_loc);

   DBMS_LOB.LOADFROMFILE(dest_loc,
src_loc, amount);

   DBMS_LOB.CLOSE(src_loc);

 

END;

/

clip_image010[4]

è 위에서 생성한 loadLOBFromBFILE_proc 을 이용해서 RESUMES 테이블의 LOB칼럼에 insert하는 write_lob 프로시저를 생성한다.

è write_lob 프로시져 생성 스크립트

CREATE OR REPLACE PROCEDURE
write_lob (p_file IN VARCHAR2)

IS

 i  NUMBER;

 fn
VARCHAR2(15);

 ln
VARCHAR2(40);

 b  BLOB;

 

BEGIN

DBMS_OUTPUT.ENABLE;

DBMS_OUTPUT.PUT_LINE(‘Begin
inserting rows…’);

  FOR i IN 1 .. 30 LOOP

    fn:=SUBSTR(p_file,1,INSTR(p_file,’.’)-1);

    ln:=SUBSTR(p_file,INSTR(p_file,’.’)+1,LENGTH(p_file)-INSTR(p_file,’.’)-4);

    INSERT
INTO resumes VALUES (i, fn, ln, EMPTY_BLOB())

      RETURNING resume INTO b;

    loadLOBFromBFILE_proc(b,p_file);

    DBMS_OUTPUT.PUT_LINE(‘Row
‘|| i ||’ inserted.’);

  END LOOP;

  COMMIT;

END;

/

 

clip_image012[4]

 

è RESUMES 테이블에서 LOB 칼럼을 읽어오는 READ_LOB 프로시저를 생성한다.

è READ_LOB 프로시저 생성 스크립트

CREATE OR REPLACE PROCEDURE
read_lob

IS

     lob_loc         BLOB;

     CURSOR
resumes_cur IS

       SELECT id, first_name, last_name,
resume

       FROM resumes;

     resumes_rec     resumes%ROWTYPE;

  BEGIN

  OPEN resumes_cur;

  LOOP

    FETCH
resumes_cur INTO resumes_rec;

     lob_loc
:= resumes_rec.resume;

     DBMS_OUTPUT.PUT_LINE(‘The
length is: ‘|| DBMS_LOB.GETLENGTH(lob_loc));

     DBMS_OUTPUT.PUT_LINE(‘The
ID is: ‘|| resumes_rec.id);

    
just print out the first 200 bytes of the LOB

    
because DBMS_OUTPUT.PUT_LINE cannot display more than 255
bytes

     DBMS_OUTPUT.PUT_LINE(‘The
blob is read: ‘||

             UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(lob_loc,200,1)));

    EXIT
WHEN resumes_cur%NOTFOUND;

   END
LOOP;

 CLOSE resumes_cur;

END;

/

 

clip_image014[4]

 

è 다음과 같이 RESUMES 테이블에 데이터를 insert 한다. (sf_demo유저)

è  insert 스크립트

exec write_lob(‘karl.brimmer.doc’);

exec write_lob(‘monica.petera.doc’);

exec write_lob(‘david.sloan.doc’);

 

clip_image016[4]

 

è 세크먼트 조회 스크립트

SELECT tablespace_name,
segment_name, segment_type, segment_subtype

FROM dba_segments

WHERE tablespace_name in
(‘SECF_TBS1’ , ‘SECF_TBS2’)

AND segment_type =
‘LOBSEGMENT’

clip_image018[4]

è LOB 세그먼트 조회

select column_name, segment_name,
compression, encrypt, securefile

from dba_lobs

where owner = ‘SF_DEMO’

clip_image020[4]

현재 COMRE,ENCR,REC 컬럼은 압축,암호화,SECUREFILE이 적용되지않는상태 

è다음은 위에서 생성한 RESUMES 테이블을 SecureFile 포맷을 이용하는 테이블로 Online Redefinition
이용해서 마이그레이션하는 과정이다.
resumes_interim 테이블을 생성하고 LOB칼럼을 SECF_TBS2 테이블스페이스에 SecureFile 포맷으로 저장되도록 설정한다.
Compression
및 De-duplication도 설정해 본다
.

 

è resumes_interim 테이블 스크립트

CREATE TABLE
sf_demo.resumes_interim

  (id NUMBER, first_name VARCHAR2(15),

  last_name VARCHAR2 (40), resume BLOB)

  LOB (resume) STORE AS SECUREFILE

  (TABLESPACE SECF_TBS2

  COMPRESS HIGH

  DEDUPLICATE)

  /

clip_image022[4]

 

è DBMS_REDEFINITION 패키지를 이용해서 Online Redefinition 작업을 수행한다. 이 작업 중에도 RESUMES 테이블은 Online 상태이다.

è DBMS_REDEFINITION 패키지를 이용하여 SECUREFILE 로 이관
스크립트

Set serveroutput on

DECLARE

 error_count PLS_INTEGER := 0;

BEGIN

 

DBMS_REDEFINITION.START_REDEF_TABLE

(‘sf_demo’, ‘resumes’, ‘resumes_interim’,

‘id id, first_name first_name, last_name last_name,
resume resume’,

OPTIONS_FLAG =>
DBMS_REDEFINITION.CONS_USE_ROWID);

 

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS

(‘sf_demo’, ‘resumes’, ‘resumes_interim’,

1, true,true,true,false, error_count);

 

DBMS_OUTPUT.PUT_LINE(‘Errors := ‘ ||
TO_CHAR(error_count));

 

DBMS_REDEFINITION.FINISH_REDEF_TABLE

(‘sf_demo’, ‘resumes’,
‘resumes_interim’);

 

END;

/

clip_image024[4]

 

 

 

è 변경된 Lob  조회 (compre,ssion, deduplication, securefile 적용)

clip_image026[4]

clip_image028[4]

è 이관이 완료된 resumes_interim 테이블은 삭제한다.

DROP TABLE sf_demo.resumes_interim

clip_image030[4]

 

 


Comments

comments

haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다