DBA의 정석

DB Admin Knowhow

Oracle DB Admin

Oracle Secure File 활용

Byhaisins

1월 15, 2019 , ,

 

 

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 초기화 파라미터 사용합니다.

 

 

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

 

 

è 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)

 

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

è DIRECTORY 생성 스크립트

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

 

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

/

è 위에서 생성한 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;

/

 

 

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

/

 

 

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

è  insert 스크립트

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

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

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

 

 

è 세크먼트 조회 스크립트

SELECT tablespace_name,
segment_name, segment_type, segment_subtype

FROM dba_segments

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

AND segment_type =
‘LOBSEGMENT’

è LOB 세그먼트 조회

select column_name, segment_name,
compression, encrypt, securefile

from dba_lobs

where owner = ‘SF_DEMO’

– 현재 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)

  /

 

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

/

 

 

 

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

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

DROP TABLE sf_demo.resumes_interim

 

 

By haisins

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

Related Post

Oracle DB Admin

Oracle Block change tracking 기능

5월 8, 2023 haisins
Oracle DB Admin

Oracle Merge 문

4월 18, 2023 haisins
Oracle DB Admin

IT 스페셜리스트. Oracle Certified Master가 되고 싶다면?

4월 18, 2023 haisins
One thought on “Oracle Secure File 활용”

  1. Hey very cool blog!! Man .. Excellent .. Superb .. I will bookmark your site and take the feeds also…I am glad to search out numerous helpful info here within the put up, we want work out more techniques in this regard, thanks for sharing. . . . . .

    답변하기

답글 남기기

이메일 주소를 발행하지 않을 것입니다. 필수 항목은 *(으)로 표시합니다

You missed

Oracle News

[NEWS] 오라클, ‘오라클 데이터베이스 23c 개발자용 무료 버전’ 공개

5월 8, 2023
Oracle DB Admin

Oracle Block change tracking 기능

5월 8, 2023
Oracle Up,Mig

DB Migration Using TTS

4월 20, 2023
DB 설치 & 구축

Windows 용 Oracle Database 19c 설치 필요 조건

4월 19, 2023