Oracle Secure File 활용

haisins

6년 ago

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)

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)

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

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