Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Friday, September 25, 2015

Implement securefile LOB

Implement securefile LOB


SHAIKDB>show parameter securefile

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_securefile                string     PERMITTED

Allowable Values: { ALWAYS | PERMITTED | NEVER | IGNORE }

Description: This parameter enables the database administrator to either allow SECUREFILE LOBs to be created (PERMITTED), disallow SECUREFILE LOBs from being created going forward (NEVER), attempt to create SECUREFILE LOBs but fall back to BASICFILE LOBs (ALWAYS), or disallow SECUREFILE LOBs and ignore any errors that would otherwise be caused by forcing BASICFILE LOBs with SECUREFILE options (IGNORE).

If NEVER is specified, any LOBs that are specified as SECUREFILE LOBs are created as BASICFILE LOBs. All SECUREFILE specific storage options and features (for example, compress, encrypt, deduplicate) will throw an exception. The BASICFILE LOB defaults are used for storage options not specified.

ALWAYS attempts to create all LOBs as SECUREFILE LOBs but creates any LOBs not in ASSM tablespaces as BASICFILE LOBs, unless SECUREFILE is explicitly specified. Any BASICFILE LOB storage options specified will be ignored, and the SECUREFILE defaults are used for all storage options not specified.

If IGNORE is specified, the SECUREFILE keyword and all SECUREFILE options are ignored.
Dynamic: The parameter is dynamic and the scope is ALTER SYSTEM.



SecureFiles Monitoring

DATA DICTONARY          VIEW USAGE

DBA_SEGMENTS            Shows all segments in the database, and includes a new column, SEGMENT_SUBTYPE, that describes the LOB segment type.
DBA_LOBS                Lists all LOBs in the database, and now includes metadata about compression,encryption and deduplication.
DBA_LOB_PARTITIONS      Describes all LOB partitions in the database, and now includes metadata about     compression,encryption and deduplication.
DBA_PART_LOBS           Shows table-level metadata for all partitioned LOBs in the database.


A SecureFile can only be created in an Automatic Segment Space Management (ASSM) tablespace. The following parameter descriptions apply to the LOB storage paradigm using parameter SECUREFILE.

SECUREFILE

To use the SecureFile storage paradigm and functionality, explicitly specify the storage parameter SECUREFILE. A SecureFile can only be created in an Automatic Segment Space Management (ASSM) tablespace.
For BASICFILE LOBs, specifying any of the SECUREFILE options results in an error.

CHUNK

For SECUREFILE LOBs CHUNK is an advisory size and is provided for backward compatibility purposes.

RETENTION

Under 11g compatibility for SecureFiles, this parameter name specifies the retention policy to be used. The value of MAX specifies keeping old versions of LOB data blocks until the space used by segment has reached the size specified in the MAXSIZE parameter. If MAXSIZE is not specified, MAX behaves like AUTO.
A value of MIN means to use a retention time of the specified seconds. A value of NONE means that there is no retention period and space can be reused in any way deemed necessary. A value of AUTO tells the system to manage the space as efficiently as possible weighing both time and space needs.

FILESYSTEM_LIKE_LOGGING means that SecureFiles only log the metadata. This option is invalid for BasicFiles. This setting is similar to the metadata journaling of file systems, which reduces mean time to recovery from failures. The LOGGING setting for SecureFile LOBs is similar to the data journaling of file systems. Both the LOGGING and FILESYSTEM_LIKE_LOGGING settings provide a complete transactional file system by way of SecureFiles.




DEDUPLICATE

The option DEDUPLICATE enables you to specify that LOB data which is identical in two or more rows in a LOB column should all share the same data blocks. The database combines SecureFiles with identical content into a single copy, reducing storage and simplifying storage management.

KEEP_DUPLICATES

The opposite of DEDUPLICATE is KEEP_DUPLICATES.


DBMS_LOB.SETOPTIONS can be used to enable or disable deduplication on individual LOBs.

CACHE and NOLOGGING are not supported together. CACHE and FILESYSTEM_LIKE_LOGGING are not supported together.

<large_object_datatypes> ::=
{ BLOB | CLOB | NCLOB| BFILE }



Demo:
Create a table with SECUREFILE and LOB-level deduplication:

SHAIKDB>create tablespace lobs datafile '/u01/app/oracle/shaikdb/lob01.dbf' size 1g autoextend on next 8m maxsize 10g;   

Tablespace created.

SHAIKDB>create user lob identified by lob default tablespace lobs;

User created.

SHAIKDB>grant create session,dba to lob;

Grant succeeded.


SHAIKDB>create table dedup (id number,desc1 blob)
   lob(desc1) store as securefile (deduplicate nocache) tablespace lobs;

Table created.


SHAIKDB>select table_name,tablespace_name,index_name,deduplication,securefile from user_lobs;

TABLE_NAME   TABLESPACE       INDEX_NAME            DEDUPLICATION   SEC
----------         ----------                ------------------------------                   --------------- ---
DEDUP       LOBS       SYS_IL0000074709C00002$$         LOB         YES



Create a table with a SECUREFILE LOB column and LOB deduplication enabled on only one partition. Only LOBs that belong to partition p1 are deduplicated.

SHAIKDB>create table dedup_part (id number,desc1 clob)
 2  lob(desc1) store as securefile
 3  partition by list(id) (
 4  partition p1 values ('1','3','5') lob(desc1) store as securefile (deduplicate),
 5  partition p2 values ('2','4','6'));

Table created.

Create a table with a SECUREFILE LOB column and disable deduplication. LOBs will be created with deduplication disabled.

SHAIKDB>create table nodedup (id number,desc1 nclob)
 2  lob(desc1) store as securefile tablespace lobs;

Table created.



Create a table with a SecureFile column and SecureFile deduplication enabled on the column except for one partition. All LOBs except those that belong to p2 will be deduplicated:

SHAIKDB>create table dedup_sub_part (id number,region varchar2(10),desc1 clob)
 2  lob(desc1) store as securefile (deduplicate cache)
 3  partition by range (region)  subpartition by hash(id) subpartitions 4 (
 4    partition p1 values less than (10) lob(desc1) store as part1
 5  ( subpartition p1_sub lob(desc1) store as subp1,
 6  subpartition p2_sub lob(desc1) store as subp2));

Table created.


SECUREFILE TABLE with Compression

  • SecureFile compression does not entail table or index compression and vice-versa.
  • A server-wide default SecureFile compression algorithm is used.
  • LOW,MEDIUM, and HIGH options provide varying degrees of compression. The higher the compression, the higher the latency incurred. The HIGH setting incurs more work, but will compress the data better. The default is MEDIUM. LOW option uses LZO block-based lossless compression algorithm. Decompression is simple and very fast.


Create a table with a SECUREFILE LOB column and LOB compression enabled on only one partition. Only LOBs that belong to partition p1 are compressed.

SHAIKDB>CREATE TABLE COMP (ID NUMBER,DESC1 CLOB)
 2  LOB(DESC1) STORE AS SECUREFILE (COMPRESS HIGH DEDUPLICATE CACHE);

Table created.


Parameters for ALTER TABLE With SECUREFILE LOBs

You can modify LOB storage with an ALTER TABLE statement or with online redefinition by using the DBMS_REDEFINITION package.

Oracle recommends that you enable compression, deduplication, or encryption at table creation time. Enabling these features using ALTER TABLE will cause the DDL to read/modify/write the entire LOB column holding a DDL lock the entire time during this potentially long operation.

The SHRINK option is not supported for SecureFiles.

If you have not enabled LOB encryption, compression, or deduplication at create time, Oracle recommends that you use online redefinition to enable them after creation, because this process is more disk-space efficient for changes to these three parameters.



ALTER TABLE [ schema.]table
 [ alter_table_properties
 | column_clauses
 | constraint_clauses
 | alter_table_partitioning
 | alter_external_table_clauses
 | move_table_clause
 ]
   [ enable_disable_clause
   | { ENABLE | DISABLE }
     { TABLE LOCK | ALL TRIGGERS }
   [ enable_disable_clause
   | { ENABLE | DISABLE }
     { TABLE LOCK | ALL TRIGGERS }
   ]...
 ] ;


<column_clauses> ::=
 { { add_column_clause
   | modify_column_clause
   | drop_column_clause
   }
   [ add_column_clause
   | modify_column_clause
   | drop_column_clause
   ]...
 | rename_column_clause
 | modify_collection_retrieval
   [ modify_collection_retrieval ]...
 | modify_LOB_storage_clause
   [ modify_LOB_storage_clause ] ...
 | alter_varray_col_properties
   [ alter_varray_col_properties ]
 }

<modify_LOB_storage_clause> ::=
MODIFY LOB (LOB_item) ( modify_LOB_parameters )

<modify_LOB_parameters> ::=
{ storage_clause
| PCTVERSION integer
| FREEPOOLS integer
| REBUILD FREEPOOLS
| LOB_retention_clause
| LOB_deduplicate_clause
| LOB_compression_clause
| { ENCRYPT encryption_spec | DECRYPT }
| { CACHE
 | { NOCACHE | CACHE READS } [ logging_clause ]
 }
| allocate_extent_clause
| shrink_clause
| deallocate_unused_clause
} ...

Disable deduplication on a SECUREFILE LOB.

SHAIKDB>select table_name,column_name,tablespace_name,index_name,deduplication,securefile,segment_created from user_lobs;

TABLE_NAME COLUMN_NAME       TABLESPACE INDEX_NAME             DEDUPLICATION   SEC SEG
---------- -------------------- ---------- ------------------------------ --------------- --- ---
DEDUP       DESC1       LOBS       SYS_IL0000074709C00002$$      LOB         YES NO
NODEDUP    DESC1       LOBS       SYS_IL0000074721C00002$$      NO         YES NO
COMP       DESC1       LOBS       SYS_IL0000074736C00002$$      LOB         YES NO
DEDUP_PART DESC1       LOBS       SYS_IL0000074712C00002$$      NO         YES N/A
DEDUP_SUB_ DESC1       LOBS       SYS_IL0000074724C00003$$      LOB         YES N/A
PART

SHAIKDB>alter table DEDUP modify lob(desc1) (keep_duplicates cache);

Table altered.

SHAIKDB>select table_name,column_name,tablespace_name,index_name,deduplication,securefile,segment_created from user_lobs;

TABLE_NAME COLUMN_NAME       TABLESPACE INDEX_NAME             DEDUPLICATION   SEC SEG
---------- -------------------- ---------- ------------------------------ --------------- --- ---
DEDUP       DESC1       LOBS       SYS_IL0000074709C00002$$      NO         YES NO
NODEDUP    DESC1       LOBS       SYS_IL0000074721C00002$$      NO         YES NO
COMP       DESC1       LOBS       SYS_IL0000074736C00002$$      LOB         YES NO
DEDUP_PART DESC1       LOBS       SYS_IL0000074712C00002$$      NO         YES N/A
DEDUP_SUB_ DESC1       LOBS       SYS_IL0000074724C00003$$      LOB         YES N/A



SHAIKDB>alter table dedup_sub_part modify lob(desc1) (keep_duplicates);

Table altered.

SHAIKDB>select table_name,column_name,tablespace_name,index_name,deduplication,securefile,segment_created from user_lobs;

TABLE_NAME COLUMN_NAME       TABLESPACE INDEX_NAME             DEDUPLICATION   SEC SEG
---------- -------------------- ---------- ------------------------------ --------------- --- ---
DEDUP       DESC1       LOBS       SYS_IL0000074709C00002$$      NO         YES NO
NODEDUP    DESC1       LOBS       SYS_IL0000074721C00002$$      NO         YES NO
COMP       DESC1       LOBS       SYS_IL0000074736C00002$$      LOB         YES NO
DEDUP_PART DESC1       LOBS       SYS_IL0000074712C00002$$      NO         YES N/A
DEDUP_SUB_ DESC1       LOBS       SYS_IL0000074724C00003$$      NO         YES N/A
PART




Disable LOB-level deduplication on a SECUREFILE LOB column within a single partition.


SHAIKDB>select table_name,partition_name,column_name,tablespace_name,deduplication,securefile,segment_created from user_lob_partitions;

TABLE_NAME PARTITION_NAME         COLUMN_NAME           TABLESPACE DEDUPLICATION   SEC SEG
---------- ------------------------------ -------------------- ---------- --------------- --- ---
DEDUP_PART P1                 DESC1            LOBS      LOB         YES YES
DEDUP_PART P2                 DESC1            LOBS      NO         YES YES
DEDUP_SUB_ P1                 DESC1              NO         YES N/A
PART


SHAIKDB>alter table dedup_part modify partition p1 lob(desc1) ( keep_duplicates cache);

Table altered.

SHAIKDB>select table_name,partition_name,column_name,tablespace_name,deduplication,securefile,segment_created from user_lob_partitions;

TABLE_NAME PARTITION_NAME         COLUMN_NAME           TABLESPACE DEDUPLICATION   SEC SEG
---------- ------------------------------ -------------------- ---------- --------------- --- ---
DEDUP_PART P1                 DESC1            LOBS      NO         YES YES
DEDUP_PART P2                 DESC1            LOBS      NO         YES YES
DEDUP_SUB_ P1                 DESC1              NO         YES N/A
PART



Documentation:

Master Note: Overview of Oracle Large Objects (BasicFiles LOBs and SecureFiles LOBs) (Doc ID 1490228.1)
Oracle® Database SecureFiles and Large Objects Developer's Guide
11g Release 2 (11.2)  Part Number E10645-01 → 4 Using Oracle SecureFiles

No comments: