This article describes new feature of 11g SecureFile LOB.
In new version 11g Oracle decided to create new type large objects (LOB) called Secure Files. It offers many new benefits versus old version of LOB called in 11g release BasicFile:
- performance
- intelligent compression
- transparent decryption
- easier management and development
Issues in previous version
- LOB instantiation was expected to be several megabytes
- designed to be “write once read many times”
- small number of updates
- OLTP workload was not anticipated
- undo space managed by two parameters PCTVERSION and RETENTION
- CHUNK size is static parameter and anticipates LOB sizes are typically uniform
- High concurrency writes in Oracle RAC was not anticipated
- Low concurrency for DML
New architecture features
- chunks bigger than oracle data blocks up to 64MB
- oracle tries to keep data in adjacent locations on disk to minimizes internal fragmentation
- by using variable chunk sizes, SecureFiles avoids versioning of large unnecessary data
- higher read and write performance
- better optimized network layer
- better optimized usage of UNDO and REDO data
- better optimized memory usage
- can be created only in ASSM tablespaces
Storage Options
For SecureFiles following storage options can be defined:
- MAXSIZE – defines maximum LOB size
- RETENTION – defines retention policy
- AUTO – selects MIN, MAX or NONE automatically – DEFAULT
- MIN – keep old versions at least MIN seconds
- MAX – keep old versions until MAXSIZE is reached
- NONE – reuse old versions as much as possible
Following storage options are not used for SecureFiles:
- FREELIST, FREELIST GROUPS, FREEPOOLS, CHUNK, PCTVERSION
DB_SECUREFILE
NOTE SecureFiles can be created only in ASSM tablespaces in other case exception is raised
New initialization parameter has appeared in 11G DB_SECUREFILE that allows determine usage of SecureFiles:
- ALWAYS – try to create always SecureFiles LOB in ASSM tablespaces or BasicFiles in NON ASSM tablespaces
- FORCE – forces to create always SecureFiles LOB
- PERMITTED – allows to create SecureFiles – default option
- NEVER – disallows to create SecureFiles, All specific SecureFile storage options and features (for example, compress, encrypt, deduplicate) will throw an exception
- IGNORE – disallows to create SecureFiles and ignores any errors that would caused by forcing BasicFiles with SecureFiles options
Examples
Two tablespaces are created ASSM and no ASSM for test purposes:
- users_assm – auto segment space management tablespace. SecureFiles LOB can be created here.
- users_noassm – manual segment space management tablespace. SecureFiles LOB can’t be created here
BasicFiles LOB can be created in each of the tablespaces
CREATE TABLESPACE users_assm DATAFILE 'D:\APP\ORACLE\ORADATA\ORA11G\USERS_ASSM01.DBF' SIZE 100m SEGMENT SPACE MANAGEMENT AUTO; CREATE TABLESPACE users_noassm DATAFILE 'D:\APP\ORACLE\ORADATA\ORA11G\USERS_NOASSM01.DBF' SIZE 100m SEGMENT SPACE MANAGEMENT MANUAL;
DB_SECUREFILE=’ALWAYS’
ALWAYS – try to create always as default SecureFiles LOB in ASSM tablespaces or BasicFiles in NON ASSM tablespaces
Let’s change parameter in session to check ALWAYS option
ALTER SESSION SET db_securefile = 'ALWAYS';
run test
CREATE TABLE tbl_def_assm(id1 CLOB) LOB(id1) STORE AS (TABLESPACE users_assm); CREATE TABLE tbl_def_noassm(id1 CLOB) LOB(id1) STORE AS (TABLESPACE users_noassm); CREATE TABLE tbl_eb_assm(id1 CLOB) LOB(id1) STORE AS BASICFILE (TABLESPACE users_assm); CREATE TABLE tbl_eb_noassm(id1 CLOB) LOB(id1) STORE AS BASICFILE (TABLESPACE users_noassm); CREATE TABLE tbl_es_assm(id1 CLOB) LOB(id1) STORE AS SECUREFILE (TABLESPACE users_assm); CREATE TABLE tbl_es_noassm(id1 CLOB) LOB(id1) STORE AS SECUREFILE (TABLESPACE users_noassm); SQL Error: ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace "USERS_NOASSM"
check results
SELECT table_name, column_name, tablespace_name, securefile FROM user_lobs WHERE table_name like 'TBL%ASSM' ORDER BY table_name; TABLE_NAME COLUMN_NAME TABLESPACE_NAME SECUREFILE --------------- ------------ ---------------- ------------ TBL_DEF_ASSM ID1 USERS_ASSM YES TBL_DEF_NOASSM ID1 USERS_NOASSM NO TBL_EB_ASSM ID1 USERS_ASSM YES TBL_EB_NOASSM ID1 USERS_NOASSM NO TBL_ES_ASSM ID1 USERS_ASSM YES
clean
DROP TABLE tbl_def_assm; DROP TABLE tbl_def_noassm; DROP TABLE tbl_eb_assm; DROP TABLE tbl_eb_noassm; DROP TABLE tbl_es_assm;
DB_SECUREFILE=’FORCE’
FORCE – forces to create always SecureFiles LOB
Let’s change parameter in session to check FORCE option
ALTER SESSION SET db_securefile = 'FORCE';
run test
CREATE TABLE tbl_def_assm(id1 CLOB) LOB(id1) STORE AS (TABLESPACE users_assm); CREATE TABLE tbl_def_noassm(id1 CLOB) LOB(id1) STORE AS (TABLESPACE users_noassm); SQL Error: ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace "USERS_NOASSM" CREATE TABLE tbl_eb_assm(id1 CLOB) LOB(id1) STORE AS BASICFILE (TABLESPACE users_assm); CREATE TABLE tbl_eb_noassm(id1 CLOB) LOB(id1) STORE AS BASICFILE (TABLESPACE users_noassm); SQL Error: ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace "USERS_NOASSM" CREATE TABLE tbl_es_assm(id1 CLOB) LOB(id1) STORE AS SECUREFILE (TABLESPACE users_assm); CREATE TABLE tbl_es_noassm(id1 CLOB) LOB(id1) STORE AS SECUREFILE (TABLESPACE users_noassm); SQL Error: ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace "USERS_NOASSM"
check results
SELECT table_name, column_name, tablespace_name, securefile FROM user_lobs WHERE table_name like 'TBL%ASSM' ORDER BY table_name; SELECT table_name, column_name, tablespace_name, securefile FROM user_lobs WHERE table_name like 'TBL%ASSM' ORDER BY table_name; TABLE_NAME COLUMN_NAME TABLESPACE_NAME SECUREFILE --------------- ------------ ---------------- ------------ TBL_DEF_ASSM ID1 USERS_ASSM YES TBL_EB_ASSM ID1 USERS_ASSM YES TBL_ES_ASSM ID1 USERS_ASSM YES
clean
DROP TABLE tbl_def_assm; DROP TABLE tbl_eb_assm; DROP TABLE tbl_es_assm;
DB_SECUREFILE=’PERMITTED’
PERMITTED – allows to create SecureFiles – default option
Let’s change parameter in session to check PERMITTED option
ALTER SESSION SET db_securefile = 'PERMITTED';
run test
CREATE TABLE tbl_def_assm(id1 CLOB) LOB(id1) STORE AS (TABLESPACE users_assm); CREATE TABLE tbl_def_noassm(id1 CLOB) LOB(id1) STORE AS (TABLESPACE users_noassm); CREATE TABLE tbl_eb_assm(id1 CLOB) LOB(id1) STORE AS BASICFILE (TABLESPACE users_assm); CREATE TABLE tbl_eb_noassm(id1 CLOB) LOB(id1) STORE AS BASICFILE (TABLESPACE users_noassm); CREATE TABLE tbl_es_assm(id1 CLOB) LOB(id1) STORE AS SECUREFILE (TABLESPACE users_assm); CREATE TABLE tbl_es_noassm(id1 CLOB) LOB(id1) STORE AS SECUREFILE (TABLESPACE users_noassm); SQL Error: ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace "USERS_NOASSM"
check results
SELECT table_name, column_name, tablespace_name, securefile FROM user_lobs WHERE table_name like 'TBL%ASSM' ORDER BY table_name; TABLE_NAME COLUMN_NAME TABLESPACE_NAME SECUREFILE --------------- ------------ ---------------- ------------ TBL_DEF_ASSM ID1 USERS_ASSM NO TBL_DEF_NOASSM ID1 USERS_NOASSM NO TBL_EB_ASSM ID1 USERS_ASSM NO TBL_EB_NOASSM ID1 USERS_NOASSM NO TBL_ES_ASSM ID1 USERS_ASSM YES
clean
DROP TABLE tbl_def_assm; DROP TABLE tbl_def_noassm; DROP TABLE tbl_eb_assm; DROP TABLE tbl_eb_noassm; DROP TABLE tbl_es_assm;
DB_SECUREFILE=’NEVER’
NEVER – disallows to create SecureFiles, All specific SecureFile storage options and features (for example, compress, encrypt, deduplicate) will throw an exception
Let’s change parameter in session to check NEVER option
ALTER SESSION SET db_securefile = 'NEVER';
run test
CREATE TABLE tbl_def_assm(id1 CLOB) LOB(id1) STORE AS (TABLESPACE users_assm); CREATE TABLE tbl_def_noassm(id1 CLOB) LOB(id1) STORE AS (TABLESPACE users_noassm); CREATE TABLE tbl_eb_assm(id1 CLOB) LOB(id1) STORE AS BASICFILE (TABLESPACE users_assm); CREATE TABLE tbl_eb_noassm(id1 CLOB) LOB(id1) STORE AS BASICFILE (TABLESPACE users_noassm); CREATE TABLE tbl_es_assm(id1 CLOB) LOB(id1) STORE AS SECUREFILE (COMPRESS TABLESPACE users_assm); SQL Error: ORA-43856: Unsupported LOB type for SECUREFILE LOB operation CREATE TABLE tbl_es_noassm(id1 CLOB) LOB(id1) STORE AS SECUREFILE (COMPRESS TABLESPACE users_noassm); SQL Error: ORA-43856: Unsupported LOB type for SECUREFILE LOB operation CREATE TABLE tbl_es_assm(id1 CLOB) LOB(id1) STORE AS SECUREFILE (TABLESPACE users_assm); CREATE TABLE tbl_es_noassm(id1 CLOB) LOB(id1) STORE AS SECUREFILE (TABLESPACE users_noassm);
check results
SELECT table_name, column_name, tablespace_name, securefile FROM user_lobs WHERE table_name like 'TBL%ASSM' ORDER BY table_name; TABLE_NAME COLUMN_NAME TABLESPACE_NAME SECUREFILE --------------- ------------ ---------------- ------------ TBL_DEF_ASSM ID1 USERS_ASSM NO TBL_DEF_NOASSM ID1 USERS_NOASSM NO TBL_EB_ASSM ID1 USERS_ASSM NO TBL_EB_NOASSM ID1 USERS_NOASSM NO TBL_ES_ASSM ID1 USERS_ASSM NO TBL_ES_ASSM ID1 USERS_ASSM NO
clean
DROP TABLE tbl_def_assm; DROP TABLE tbl_def_noassm; DROP TABLE tbl_eb_assm; DROP TABLE tbl_eb_noassm; DROP TABLE tbl_es_assm; DROP TABLE tbl_es_noassm;
DB_SECUREFILE=’IGNORE’
IGNORE – disallows to create SecureFiles and ignores any errors that would caused by forcing BasicFiles with SecureFiles options
Let’s change parameter in session to check IGNORE option
ALTER SESSION SET db_securefile = 'IGNORE';
run test
CREATE TABLE tbl_def_assm(id1 CLOB) LOB(id1) STORE AS (TABLESPACE users_assm); CREATE TABLE tbl_def_noassm(id1 CLOB) LOB(id1) STORE AS (TABLESPACE users_noassm); CREATE TABLE tbl_eb_assm(id1 CLOB) LOB(id1) STORE AS BASICFILE (TABLESPACE users_assm); CREATE TABLE tbl_eb_noassm(id1 CLOB) LOB(id1) STORE AS BASICFILE (TABLESPACE users_noassm); CREATE TABLE tbl_es_assm(id1 CLOB) LOB(id1) STORE AS SECUREFILE (COMPRESS TABLESPACE users_assm); CREATE TABLE tbl_es_noassm(id1 CLOB) LOB(id1) STORE AS SECUREFILE (COMPRESS TABLESPACE users_noassm);
check results
SELECT table_name, column_name, tablespace_name, securefile FROM user_lobs WHERE table_name like 'TBL%ASSM' ORDER BY table_name; TABLE_NAME COLUMN_NAME TABLESPACE_NAME SECUREFILE --------------- ------------ ---------------- ------------ TBL_DEF_ASSM ID1 USERS_ASSM NO TBL_DEF_NOASSM ID1 USERS_NOASSM NO TBL_EB_ASSM ID1 USERS_ASSM NO TBL_EB_NOASSM ID1 USERS_NOASSM NO TBL_ES_ASSM ID1 USERS_ASSM NO TBL_ES_ASSM ID1 USERS_ASSM NO
clean
DROP TABLE tbl_def_assm; DROP TABLE tbl_def_noassm; DROP TABLE tbl_eb_assm; DROP TABLE tbl_eb_noassm; DROP TABLE tbl_es_assm; DROP TABLE tbl_es_noassm;
Deduplication
New storage parameter DEDUPLIACATE can be specified for SecureFiles. It enables detection of repeated data in LOB columns for a table/partition and save it only once. Parameter KEEP_DEDUPLICATE prevents deduplication.
ALTER SESSION SET db_securefile = 'ALWAYS'; CREATE TABLE tbl_dd_assm ( id1 CLOB, id2 CLOB, id3 CLOB ) LOB(id1) STORE AS SECUREFILE id1_l (DEDUPLICATE TABLESPACE users_assm) LOB(id2) STORE AS SECUREFILE id2_l (KEEP_DUPLICATES TABLESPACE users_assm) LOB(id3) STORE AS SECUREFILE id3_l (TABLESPACE users_assm);
check
SELECT table_name, column_name, segment_name, securefile, deduplication FROM user_lobs WHERE table_name = 'TBL_DD_ASSM' ORDER BY table_name; TABLE_NAME COLUMN_NAME SEGMENT_NAME SECUREFILE DEDUPLICATION ------------ ------------ ------------- ----------- -------------- TBL_DD_ASSM ID1 ID1_DEDU YES LOB TBL_DD_ASSM ID2 ID2_KEDE YES NO TBL_DD_ASSM ID3 ID3_DEF YES NO
load data
BEGIN FOR i IN 1..1000 LOOP INSERT INTO tbl_dd_assm ( id1, id2, id3 ) VALUES ( rpad('*',10000, '*'), rpad('*',10000, '*'), rpad('*',10000, '*') ); END LOOP; COMMIT; END; /
As expected segment ID1_DEDU with DEDUPLICATE is very small
SELECT segment_name, segment_subtype, bytes FROM user_segments WHERE segment_name IN ( 'ID1_DEDU', 'ID2_KEDE', 'ID3_DEF' ); SEGMENT_NAME SEGMENT_SUBTYPE BYTES ------------- ---------------- -------- ID1_DEDU SECUREFILE 1245184 ID2_KEDE SECUREFILE 10682368 ID3_DEF SECUREFILE 10682368
clean
DROP TABLE tbl_dd_assm;
Compression
Three types of compression are available for SecureFiles:
- LOW – recommended for fast writes, reads
- MEDIUM – default for low latency systems
- HIGH – CPU intensive, high latency systems
Compression requires Advanced Compression Option
Example
CREATE TABLE tbl_nocomp ( id1 NUMBER, id2 CLOB ) LOB(id2) STORE AS SECUREFILE tbl_nocomp_id2 (NOCOMPRESS TABLESPACE users_assm); CREATE TABLE tbl_comp_low ( id1 NUMBER, id2 CLOB ) LOB(id2) STORE AS SECUREFILE tbl_comp_low_id2 (COMPRESS LOW TABLESPACE users_assm); CREATE TABLE tbl_comp_med ( id1 NUMBER, id2 CLOB ) LOB(id2) STORE AS SECUREFILE tbl_comp_med_id2 (COMPRESS MEDIUM TABLESPACE users_assm); CREATE TABLE tbl_comp_high ( id1 NUMBER, id2 CLOB ) LOB(id2) STORE AS SECUREFILE tbl_comp_high_id2 (COMPRESS HIGH TABLESPACE users_assm);
load test data
INSERT /*+ APPEND */ INTO tbl_nocomp SELECT LEVEL, rpad('X', 10000, 'X') FROM dual CONNECT BY LEVEL < 1001; COMMIT; INSERT /*+ APPEND */ INTO tbl_comp_low SELECT LEVEL, rpad('X', 10000, 'X') FROM dual CONNECT BY LEVEL < 1001; COMMIT; INSERT /*+ APPEND */ INTO tbl_comp_med SELECT LEVEL, rpad('X', 10000, 'X') FROM dual CONNECT BY LEVEL < 1001; COMMIT; INSERT /*+ APPEND */ INTO tbl_comp_high SELECT LEVEL, rpad('X', 10000, 'X') FROM dual CONNECT BY LEVEL < 1001; COMMIT;
collects stats
exec dbms_stats.gather_table_stats(USER, 'TBL_NOCOMP'); exec dbms_stats.gather_table_stats(USER, 'TBL_COMP_HIGH'); exec dbms_stats.gather_table_stats(USER, 'TBL_COMP_MED'); exec dbms_stats.gather_table_stats(USER, 'TBL_COMP_LOW');
check results. I didn’t load complex data so there is no difference in space consumption for LOW, MEDIUM, HIGH
SELECT table_name, column_name, securefile, compression FROM user_lobs WHERE table_name IN ( 'TBL_NOCOMP', 'TBL_COMP_HIGH', 'TBL_COMP_MED', 'TBL_COMP_LOW' ) ORDER BY table_name; TABLE_NAME COLUMN_NAME SECUREFILE COMPRESSION -------------- ------------ ----------- ------------ TBL_COMP_HIGH ID2 YES HIGH TBL_COMP_LOW ID2 YES LOW TBL_COMP_MED ID2 YES MEDIUM TBL_NOCOMP ID2 YES NO SELECT segment_name, segment_type, segment_subtype, bytes FROM user_segments WHERE segment_name IN ( 'TBL_NOCOMP_ID2', 'TBL_COMP_HIGH_ID2', 'TBL_COMP_MED_ID2', 'TBL_COMP_LOW_ID2' ); SEGMENT_NAME SEGMENT_TYPE SEGMENT_SUBTYPE BYTES ------------------ ------------- ---------------- ------ TBL_COMP_HIGH_ID2 LOBSEGMENT SECUREFILE 131072 TBL_COMP_LOW_ID2 LOBSEGMENT SECUREFILE 131072 TBL_COMP_MED_ID2 LOBSEGMENT SECUREFILE 131072 TBL_NOCOMP_ID2 LOBSEGMENT SECUREFILE 10682368
clean
DROP TABLE tbl_comp_high; DROP TABLE tbl_comp_med; DROP TABLE tbl_comp_low; DROP TABLE tbl_nocomp;
Encryption
SecureFiles encryption uses Transparent Data Enryption (TDE) feature. Following encryption algorithms can be used:
- 3DES168 – triple data encryption standard with 168-bit key size
- AES128 – advanced encryption standard with 128-bit key size
- AES192 – advanced encryption standard with 192-bit key size – DEFAULT
- AES256 – advanced encryption standard with 256-bit key size
Following keywords are used
- ENCRYPT
- DECRYPT
Wallet must be setup to use this feature
TDE wallet management
create directory $ORACLE_BASE\admin\wallet
mkdir $ORACLE_HOME\admin\wallet
logon as user sys and set encryption master key. It will create wallet file “ewallet.p12” in above directory
sqlplus / as sysdba SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "oracle";
Encryption securefile
Now it’s time to test encryption
CREATE TABLE test_tbl_enc_def ( id1 CLOB ) LOB(id1) STORE AS SECUREFILE (ENCRYPT); CREATE TABLE test_tbl_enc ( id1 CLOB ) LOB(id1) STORE AS SECUREFILE (ENCRYPT USING 'AES128' IDENTIFIED BY 'dbaora.com');
Rekey table
ALTER TABLE test_tbl_enc REKEY USING 'AES192';
Check in views
SELECT table_name, column_name, encrypt, securefile FROM user_lobs WHERE table_name IN ('TEST_TBL_ENC_DEF', 'TEST_TBL_ENC'); TABLE_NAME COLUMN_NAME ENCRYPT SECUREFILE ----------------- ------------ -------- ----------- TEST_TBL_ENC ID1 YES YES TEST_TBL_ENC_DEF ID1 YES YES
Decription
ALTER TABLE test_tbl_enc MODIFY LOB(id1) (DECRYPT);
Caching and logging
Caching is just buffering LOB segments in memory. It speeds up operations executed on LOBs.
Following settings are available for caching:
- CACHE – LOB segments are buffered in buffer cache
- CACHE READS – LOB segments are buffered in buffer cache for read operations only
- NOCACHE – DEFAULT option, LOB segments are not buffered
Logging defines how modification in LOB data are copied to redo stream.
Following settings that can be used for SecureFiles:
- NOLOGGING – changes are not copied to redo logs
- LOGGING – changes are copied to redo logs
- FILESYSTEM_LIKE_LOGGING – only metadata is logged, but still recovery is possible for affected LOB
CREATE TABLE test_tbl_cl ( id1 CLOB ) LOB(id1) STORE AS SECUREFILE ( NOCACHE LOGGING );
ALTER TABLE test_tbl_cl MODIFY LOB(ID1) ( CACHE FILESYSTEM_LIKE_LOGGING );
Migrate BasicFile LOB to the SecureFile LOB
It can be done only manually
- CREATE TABLE … AS SELECT …
- copy to new table INSERT INTO … SELECT …
- Online table redefintion.
- Export/Import
- Operations on columns ALTER TABLE … MOVE LOB … STORE AS SECUREFILE
- Add new column SECUREFILE and copy data from BASICFILE
Extra database parameters
To avoid OS file system buffering especially for CLOB, instance parameter FILESYSTEMIO_OPTIONS should be set
ALTER SESSION SET filesystemio_options=setall;
PLSQL addons
Dummy data
CREATE TABLE test_tbl_plsql ( id1 CLOB ) LOB(id1) STORE AS SECUREFILE ( ENCRYPT COMPRESS CACHE LOGGING ); INSERT INTO test_tbl_plsql VALUES('Test data'); COMMIT;
DBMS_LOB has got extra functions to get new properties dedicated for securefiles
SET SERVEROUTPUT ON DECLARE l_clob CLOB; BEGIN SELECT id1 INTO l_clob FROM test_tbl_plsql WHERE rownum = 1; DBMS_OUTPUT.put_line('Compress : ' || DBMS_LOB.getoptions(l_clob, DBMS_LOB.opt_compress)); DBMS_OUTPUT.put_line('Dedup : ' || DBMS_LOB.getoptions(l_clob, DBMS_LOB.opt_deduplicate)); DBMS_OUTPUT.put_line('Encrypt : ' || DBMS_LOB.getoptions(l_clob, DBMS_LOB.opt_encrypt)); END; / Compress : 1 Dedup : 0 Encrypt : 2
Have a fun 🙂
Tomasz