11g Shared I/O Pool for Securefile Lobs Oracle 11g New Features Tips by Donald BurlesonJune 29, 2015

Oracle 11g New Features Tips

The Cache Option defaults to NOCACHE. Possible values are as follows:

• CACHE: Oracle places LOB data into the buffer cache which provides fast access. CACHE is only supported in combination with the logging option.

• NOCACHE: LOB values are not placed into the buffer cache.

• CACHE READS: LOB values are placed into the buffer cache but only for read operations. Write operations are not cached.

• NOCACHE (default)

Oracle 11g uses a cache called SHARED I/O POOL for securefile lobs operations if nocache is used for the lobs. When lobs are created with the NOCACHE option, which is the default.

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      1300380 No
Redo Buffers                        6119424 No
Buffer Cache Size                 213909504 Yes
Shared Pool Size                  201326592 Yes
Large Pool Size                     4194304 Yes
Java Pool Size                      4194304 Yes
Streams Pool Size                         0 Yes
Shared IO Pool Size                       0 Yes
Granule Size                        4194304 No
Maximum SGA Size                  431046656 No
Startup overhead in Shared Pool    46137344 No
Free SGA Memory Available                 0 

12 rows selected.

The shared i/o pool uses shared memory and thus can be used by large concurrent operations of securefile lobs. If the memory for the shared i/o pool is too small for a large securefile lob operation the server process will temporarily utilize PGA memory until enough shared memory is available again.

Securefile lobs can be created with different LOGGING options.

The default is LOGGING which creates redo records for the creation of the lob segment as well as for the subsequent changes.

Another alternative is FILESYSTEM_LIKE_LOGGING. This only created redo for the metadata of the securefile lob. This reduces the mean time to recover in case of an instance crash. At the same time full data recoverability is ensured. The drawback here is that data can change during an online backup which would lead to inconsistency in. This would be fatal in case of media recovery being necessary.

Here is the syntax to enforce logging and to disable it again:

LUTZ AS SYSDBA @ orcl SQL> ALTER DATABASE FORCE LOGGING;
?
LUTZ AS SYSDBA @ orcl SQL> ALTER DATABASE NO FORCE LOGGING;

It is also possible to use ALTER TABLE to modify the properties of securefile lob columns:

ALTER TABLE encrypt_03 MODIFY LOB (resume_doc) (KEEP_DUPLICATES);
ALTER TABLE encrypt_03 MODIFY LOB (resume_doc) (DEDUPLICATE LOB);
ALTER TABLE encrypt_03 MODIFY PARTITION p1 LOB (resume_doc) (DEDUPLICATE LOB);

ALTER TABLE encrypt_03 MODIFY LOB (resume_doc) (NOCOMPRESS);
ALTER TABLE encrypt_03 MODIFY LOB (resume_doc) (COMPRESS HIGH);
ALTER TABLE encrypt_03 MODIFY PARTITION p1 LOB (resume_doc) (COMPRESS HIGH);

ALTER TABLE encrypt_03 MODIFY (resume_doc CLOB ENCRYPT USING '3DES168');
ALTER TABLE encrypt_03 MODIFY PARTITION p1 LOB (resume_doc) ( ENCRYPT );
ALTER TABLE encrypt_03 MODIFY (resume_doc CLOB ENCRYPT IDENTIFIED BY lutz1234);

The ENCRYPT and DECRYPT keywords can only be used to encrypt not yet encrypted respectively decrypt already encrypted lob columns.

LUTZ  @ orcl SQL> alter table encrypt_01 modify (resume_pdf CLOB encrypt using 'AES192');

alter table encrypt_01 modify (resume_pdf CLOB encrypt using 'AES192')
                               *
ERROR at line 1:
ORA-28334: column is already encrypted 

[oracle@rhas4 ~]$ oerr ora 28334
28334, 0000, "column is already encrypted"
// *Cause: An attempt was made to encrypt an encrypted column.
//
// *Action:
//

Lobs can only be encrypted on a per column basis.

Transparently Encrypted Exports of securefile lobs are only possible with ORACLE datapump (expdp).

The same is true for transportable tablespaces with securefile lobs.