gsafer.app.html,11G LOB SecureFiles：Faster, Safer, and Smaller

Faster, Safer, and Smaller LOBs

By Jonathan Gennick

Oracle SecureFiles provides increased performance, better security, and reduced disk usage.

Many organizations today face the challenge of

an explosion in the amount of unstructured, often file-based data that

must be managed in conjunction with the more-structured data typically

associated with relational databases. Banking applications store check

images for cleared checks, healthcare applications store digital images

from X-rays and CAT scans, geospatial systems store satellite imagery,

enterprise resource planning systems store invoice images, and the list

goes on.

Oracle Database 11g makes significant

strides in addressing the problem of unstructured data, by introducing

a completely new storage infrastructure for data that today is often

left in file systems. This new infrastructure is called Oracle

SecureFiles, and it is a complete, drop-in replacement of Oracle's

previous large-object (LOB) infrastructure (now called BasicFiles).

Every facet of the Oracle SecureFiles LOB architecture, from disk

format to network protocol to redo and undo algorithms, has been

rethought and reimplemented to

Improve performance

Improve security

Reduce disk usage

Suppose you're migrating digital assets from

databases and file systems and developing a more-complete digital asset

management system such as one a magazine publisher might use. Your

system must manage article drafts and the workflow involved in writing,

editing, revising, and publishing that content. This article describes

how to set up and configure Oracle SecureFiles and how to migrate

sample content for this digital asset management system.

Configuring Your Instance

After installing Oracle Database 11g and before using Oracle SecureFiles, check the setting of the new Oracle Database 11g db_securefile initialization parameter. The default setting is

db_securefile = permitted

This setting makes BasicFiles, not SecureFiles,

the default storage approach for any new LOB columns you create while

enabling you to explicitly specify SecureFiles when needed. (BasicFiles

is also the LOB architecture in previous database releases.) Other

settings give you various options for forcing the use of one or the

other storage type. For example, you can setdb_securefile = forceto ensure that all LOBs are created as SecureFiles even when the

creating user explicitly specifies otherwise. You should be aware of

which db_securefile setting is in effect for your database.

Creating Tablespaces

Any tablespaces you plan to use with Oracle

SecureFiles need to be configured for automatic segment space

management (ASSM). The key here is to specify SEGMENT SPACE MANAGEMENT

AUTO when creating your LOB tablespaces. The following creates the

ARTICLE_LOBS tablespace for this article:

CREATE TABLESPACE

article_lobs

DATAFILE 'H:\APP\JONATHAN\ORADATA\ORCL\ARTICLE_LOBS.DBF'

SIZE 5M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;

If you are following along with this article's

example, be sure to give yourself adequate quota on the ARTICLE_LOBS

tablespace. You'll also need to adjust the datafile path to something

appropriate for your system.

Setting Up for Encryption

Oracle SecureFiles provides optional transparent

data encryption of LOBs. Transparent data encryption protects LOB data

from unauthorized users who might somehow gain access to a datafile.

This encryption is completely transparent to end users and applications.

If you plan to use encryption with Oracle

SecureFiles, you'll need to create a transparent data encryption wallet

to hold the encryption key. First, create a directory to contain the

wallet. You can place that directory underneath $ORACLE_HOME. For

example

mkdir H:\app\Jonathan\product\11.1.0\db_1\wallet

Next, add an ENCRYPTION_WALLET_LOCATION setting

to your sqlnet.ora file. You'll find sqlnet.ora in

$ORACLE_HOME/network/admin. Following is the setting corresponding to

the directory just created:

ENCRYPTION_WALLET_LOCATION=

(SOURCE=(METHOD=FILE)(METHOD_DATA=

(DIRECTORY= H:\app\Jonathan\product\

11.1.0\db_1\wallet)))

Be certain to include the

ENCRYPTION_WALLET_LOCATION setting in sqlnet.ora as one long line. And

again, if you are following along with this article's example, you'll

need to adjust the wallet directory to something appropriate for your

own system.

Finally, set an encryption key:

ALTER SYSTEM SET ENCRYPTION KEY

AUTHENTICATED BY "My secret key";

With this setting, your Oracle Database instance

will generate an encryption key and place it in the wallet. The wallet

is then protected by the password "My secret key."

Creating SecureFile LOBs

Creating an Oracle SecureFiles LOB can be as simple as specifyingSTORE AS SECUREFILEin the LOB storage clause of a CREATE TABLE or ALTER TABLE statement.

(Be sure to assign your SecureFiles LOBs to appropriate, ASSM-enabled

tablespaces.) Listing 1 creates the article_draft_redef table, with a

single, SecureFiles LOB column named article_content. Data for that LOB

is assigned to the ARTICLE_LOBS tablespace (created earlier in this

article). Each row in the table contains a magazine article at a

specific stage in the writing/editing/revising cycle.

Code Listing 1:Creating an Oracle SecureFiles LOB

CREATE TABLE article_draft_redef (

article_id NUMBER,

article_stage VARCHAR(10),

article_content BLOB,

CONSTRAINT article_stage_check_b

CHECK (article_stage IN (

'1st Draft', 'Edit Pass', '2nd Draft',

'Copyedit', 'Review', 'Final')),

CONSTRAINT article_draft_pk_b

PRIMARY KEY (article_id)

)

LOB (article_content) STORE AS SECUREFILE

article_content (

TABLESPACE article_lobs

RETENTION MIN 3600

KEEP_DUPLICATES NOCOMPRESS DECRYPT

CACHE READS);

Listing 1 shows some of the options available when creating a new LOB.

RETENTION MIN 3600ensures that at least one hour (60 minutes x 60 seconds/minute = 3,600)

of undo data is retained for the LOB column. This ability to specify a

minimum retention time is helpful for running your database in

flashback mode. The default is RETENTION AUTO, which retains only

enough undo to satisfy consistent read requests while a transaction is

in progress. You can also specify a maximum retention in bytes or no

retention at all. See theOracle SQL Referencefor details on the syntax for retention.

KEEP_DUPLICATES, NOCOMPRESS, andDECRYPTexplicitly disable deduplication, compression, and encryption. I'll

come back to these options later in the article. In real life, you

would likely enable one or more of them from the beginning.

CACHE READScauses LOB data to be placed into the buffer cache during read

operations but not during write operations. You gain improved read

performance here at the price of potentially crowding other data out of

the buffer cache. NOCACHE specifies the default behavior, which is

never to bring a LOB's data into the buffer cache.

I've created the article_draft_redef table in

Listing 1 to migrate a table that uses the older BasicFiles to the

newer SecureFiles storage method. To follow along with the migration,

download the o57securefiles.zip

file, unzip that file, and follow the instructions in readme.txt to

create the original table named article_draft that I will be migrating.

The logical column structure of article_draft is exactly the same as

that of article_draft_redef.

The article_draft table contains existing LOB

data stored with BasicFiles. I'll assume that that data is in use by

running applications and that I want to migrate that data from

BasicFiles to SecureFiles. The article_draft_redef table, with its

SecureFiles LOB column, represents the destination of the article_draft

table data. (Bonus! There is even a real article for you to read in the

example data.)

Planning a Migration

Because Oracle SecureFiles represents a

completely new way of writing LOB data to the database, the only way to

migrate LOB data from BasicFiles to SecureFiles is essentially to

rewrite the data by recreating or redefining the tables containing

BasicFiles columns. If you can afford to take your data offline, you

can simply select data from your old table and insert it into the new

one. Begin with a statement such as

INSERT INTO article_draft_redef

SELECT article_id,

article_stage,

article_content

FROM article_draft;

Follow this INSERT with a DROP and a RENAME to make the new table take the place of the old one:

DROP TABLE article_draft;

RENAME article_draft_redef

TO article_draft;

There's really no need to take your data

offline, though. Instead, consider using the online table redefinition

feature introduced in Oracle9i Database Release 1. Listing 2

shows a PL/SQL block that migrates the article_draft table to the

structure indicated by the article_draft_redef table. Online table

definition is very easy to do, and it makes the migration transparent

to users and applications, because the table being migrated remains

available the entire time.

Code Listing 2:Migrating by online redefinition

DECLARE

error_counter PLS_INTEGER;

BEGIN

--Begin the redefinition process

DBMS_REDEFINITION.START_REDEF_TABLE (

'gennick', 'article_draft', 'article_draft_redef',

'article_id, article_stage, article_content');

--Finish the redefinition process

DBMS_REDEFINITION.FINISH_REDEF_TABLE (

'gennick', 'article_draft', 'article_draft_redef');

END;

If your LOB table happens to be partitioned,

another approach to consider is partition exchange. You can specify LOB

storage on a per-

partition basis, giving you the option of migrating one partition at a

time from BasicFiles to SecureFiles. Use the URL under "Next Steps"

to go to Arup Nanda's article on partitioning features, "Partition

Decisions," where you will find an example showing how partition

exchange is done.

Be sure you have enough tablespace and disk

space set aside to support whatever migration method you choose. For

the online redefinition approach, you'll need enough space for two

complete copies of your data to coexist. Partition exchange requires

only enough disk space to hold two copies of whichever partition you

are exchanging. Bear in mind that one downside of partition exchange is

that the partition being swapped needs to be briefly taken offline. In

the end, you'll have to weigh the different approaches and their

trade-offs and choose the approach that works best in your own

situation.

Confirming Migration

You can confirm the SecureFiles status of a

given LOB column by checking that column's segment subtype. For

example, execute the following to confirm that article_content is now a

SecureFiles LOB:

SELECT segment_subtype

FROM user_segments

WHERE segment_name='ARTICLE_CONTENT';

The result you want to see is SECUREFILE, which

indicates SecureFiles storage. If you see ASSM, you're still set for

BasicFiles storage.

Enabling Deduplication

Having migrated to Oracle SecureFiles, you can choose to enablededuplication, an Oracle SecureFiles feature whereby the database server stores onlyonecopy of a given LOB in a given column within the same partition. Two or

more users can independently store the same data in a LOB

column—storing the same article draft twice, for example—and the

database keeps track of that duplication, storing onlyonecopy of the data. Deduplication is transparent. Users each perceive

that they have their own copy of deduplicated data, even though that is

not really the case.

Code Listing 3:Checking space used by a LOB

DECLARE

seg_blocks NUMBER;

seg_bytes NUMBER;

used_blocks NUMBER;

used_bytes NUMBER;

expired_blocks NUMBER;

expired_bytes NUMBER;

unexpired_blocks NUMBER;

unexpired_bytes NUMBER;

BEGIN

DBMS_SPACE.SPACE_USAGE (

'GENNICK', 'ARTICLE_CONTENT', 'LOB'

, seg_blocks, seg_bytes, used_blocks, used_bytes

, expired_blocks, expired_bytes, unexpired_blocks, unexpired_bytes);

DBMS_OUTPUT.PUT_LINE ('Bytes used = ' || to_char(used_bytes));

END;

In a real-life migration, it would likely make sense to deduplicateduring,

not after, the migration process. For example purposes, though, it's

nice to be able to see that removal of duplicates is actually

occurring. Run the code in Listing 3 to see how many bytes are in use

by the LOB segment. (Be sure to change the first parameter of the

procedure call to a valid schema name on your system.) On my system,

the result is

Bytes used = 450560

To deduplicate the data in the article_content column in the article_draft table, issue the following ALTER TABLE statement:

ALTER TABLE article_draft

MODIFY LOB(article_content)

(DEDUPLICATE LOB);

Existing LOBs in the article_content column will

be scanned, and any current duplicates will be detected and eliminated.

Any new LOB values written to the column will be checked against

existing values to prevent future duplication.

Now, run the code in Listing 3 again and you can

see the effects of the deduplication. The number of bytes used by the

segment should be reduced. On my system, I see the following, lower

usage:

Bytes used = 376832

The number of bytes used in the LOB segment has

dropped because document #6 is a duplicate of an article stored in a

different row. Because of deduplication, that article is not stored

twice. Instead, a pointer is stored to the LOB in that other row. When

you read the contents of the LOB for document #6, that pointer is

dereferenced for you in a completely transparent manner.

Duplicate detection is based on checksums

computed with the SHA1 cryptographic hash algorithm. When you write a

new LOB into a LOB segment, a checksum is computed over the firstnbytes of that new LOB. If that checksum doesn't match that for any

existing LOB in the segment, the new LOB is written to the database.

Checksums are saved for each LOB, for future deduplication.

When the checksum for a newly incoming LOB

matches an existing checksum, there is the strong possibility that the

two LOBs are identical. The database instance will begin doing a

byte-by-byte comparison of the incoming LOB data with the possible

duplicate that is already stored. That possible duplicate is referred

to as theprimaryLOB. The comparison process reads the primary LOB, but no new data is

written, so long as the LOB data continues to match. If the LOBs

compare as equal, only a pointer to the primary LOB is written. If the

comparison fails, data from the primary LOB up to that point is used to

construct the first part of the new LOB, and remaining incoming data

for the new LOB is written to disk.

Enabling Compression

Compressionencodes the data in a LOB to reduce the number of bytes required to

store that LOB. Compression is transparent and reduces the amount of

storage needed for your LOB data, giving you more benefit from each

dollar your organization spends on disk storage systems. Compression

requires additional CPU cycles, so be sure that compression is

worthwhile before enabling it on a LOB column. For example, image data,

such as PNG files and JPEG files, is often compressed as part of the

encoding scheme used. Further compression of image files may be a waste

of CPU cycles. But text-based data such as XML and word-processing

documents tends to compress a great deal, making the trade-off of CPU

time for more disk space very worthwhile.

To enable compression on the sample SecureFiles LOB column, issue the following ALTER TABLE statement:

ALTER TABLE article_draft

MODIFY LOB(article_content)

(COMPRESS HIGH);

This statement activates a high level of

compression on the column data. You can specify MEDIUM to trade away

some of that compression for reduced CPU cycles. There is currently no

LOW compression option, but that keyword is reserved for future

implementation.

If you rerun the code in Listing 3, you should

see that even less space is now used by the LOB segment. I get the

following result:

Bytes used = 90112

Remember that compression has multiple benefits.

Disk space requirements are reduced. Less data needs to be transferred

back and forth, leading to better performance. Redo generation is

reduced. Less buffer cache data gets used, leaving more room for other

objects to be in the cache.

Enabling Encryption

Finally, we come to transparent data encryption.

LOB data that is encrypted is protected even when datafiles or backup

files fall into the wrong hands. Encrypted LOB data is protected even

while being sent across the network.

To encrypt LOBs in Oracle SecureFiles, you can

choose from four encryption algorithms: 3DES168, AES128, AES192, and

AES256. For example, to choose AES256 encryption, which is a 256-bit

algorithm, issue the following ALTER TABLE statement:

ALTER TABLE article_draft

MODIFY LOB(article_content)

(ENCRYPT USING 'AES256');

Remember that transparent data encryption

protects your data from malevolent users who might happen to come into

possession of a database datafile or backup piece. You still must

manage access to database objects within your database, though, so that

only authorized database users are allowed access to query the LOB data

you are protecting.

Reap the Benefits!

Oracle SecureFiles offers three compelling

features you can take advantage of immediately without making any

changes to your LOB-using applications:

Deduplication

Compression

Encryption

Preliminary testing also shows significant

increases in performance through use of Oracle SecureFiles. Oracle's

in-house testing shows a 200 to 900 percent increase in write

performance, depending on LOB size and whether existing segment space

is being reused. Early-adopter clients have reported 300 to 700 percent

increases in performance of key application functions.

Because Oracle SecureFiles is completely

backward-compatible with BasicFiles, even to the point of using the

same datatype names, you can begin to take advantage of everything

Oracle SecureFiles has to offer, almost from the moment you upgrade to

Oracle Database 11g. You do need to rewrite your LOB data in the

database, but you can do that while keeping the data online, and the

change is completely transparent to both users and applications.

Oracle SecureFiles is a gift. Drop it in. Take

immediate advantage of improved performance, save money on disk

storage, and secure your data from those who would do you harm.

Jonathan Gennick() is an experienced Oracle professional and member of the Oak Table Network. He wrote the best-sellingSQL Pocket Guideand theOracle SQL*Plus Pocket Reference, both from O'Reilly Media.