11g

 

11g Shared I/O Pool for Securefile Lobs Oracle 11g New Features Tips by Burleson Consulting June 29, 2008

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.

% Oracle can automatically adjust the size of this cache to up to 4% of the            buffer cache.

% The default size of shared i/o pool is 0

LUTZ AS SYSDBA @ orcl SQL>  SELECT * FROM v$sgainfo

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.

% Make sure that LOGGING is enforced for the lob storage during online            backups!

% Make sure that all redo which was generated during the online backup is            archived!

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

% Although it is possible to use the ALTER TABLE statement to enable            encryption, compression or de-duplication of securefile lobs after the creation,            Oracle recommends to use ONLINE REDEFINITION for such            operations.

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

% If you try to encrypt an already encrypted column Oracle will return an error            ORA-28334

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.

% Table shrinking is not supported for securefile lobs in Oracle 11g