Blog - comments

Hello David,I think Oracle is behaving differently with 12.1.0.2.x. At least in my case I was able t...
David D'Acquisto

Bravo, il fallait y penser Mery Christmas

Eric

Great !! Thank you very much.

SEK

Great !! Thank you very much.

SEK
Franck, thank you for the thorough explanation on diagnosing these types issues. It showed me exac...
TimL
Blog Michael Schwalm Oracle Database 12c: SecureFiles enhancements

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

Oracle Database 12c: SecureFiles enhancements

Oracle Database 12c features with some enhancements for LOB objects and particularly for the SecureFiles. This is not a revolution, but we can see that the range of possibilities with LOB objects increases with the different releases of Oracle...

 

SecureFiles is the default for LOB storage

With Oracle 11g, when creating a table containing LOB data, LOB are stored as BASIC FILE per default and the default value for the init parameter DB_SECUREFILE is set to PERMITTED (the creation of SecureFiles is allowed but not automatic). To create SecureFiles on 11g, two solutions are available:

  • It is possible to set the DB_SECUREFILE parameter to ALWAYS, in order to force the system to create LOB files as SecureFiles. In that case, all BasicFile lob storage options are ignored. Because SecureFiles are only supported with ASSM tablespaces, an error is raised if the user try to create a LOB on a non ASSM tablespace. BasicFile becomes purely unavailable.
  • Use the STORE AS SECUREFILE clause when creating the table, as follows:

 

SQL> CREATE TABLE t1 (a CLOB)
LOB(a) STORE AS SECUREFILE;

 

With Oracle 12c, LOBs are now stored as SecureFiles per default, and the init parameter DB_SECUREFILE has changed:

  • The new PREFERRED value is now the default, if the COMPATIBLE init parameter is set to 12.0.0.0 or higher. PREFERRED makes LOBs to be stored as SecureFiles per default, unless the BASICFILE clause is explicitely used when creating the table, or the tablespace is not ASSM.
  • The ALWAYS value still forces the storage as SecureFiles, however, LOBs are stored as BasicFile if the tablespace is not ASSM, instead of raising an error. BasicFile is not banned.

 

I checked this new feature on an Oracle 12c database:

 

SQL> show parameter db_securefile;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_securefile                        string      PREFERRED

 

The parameter has the PREFERRED value. Now, what happens if I create a table without specifying the SECUREFILE clause?

 

SQL> create table t1 (a CLOB);
SQL> select table_name, securefile from user_lobs;
 
TABLE_NAME                     SECUREFILE
------------------------------ ---------------
T1                             YES

 

The LOB has been created as SecureFile.

 

Note that even if Securefiles is now the default, it does not prevent the customer from buying the license for both Oracle Advanced Compression and Oracle Advance Security options in order to benefit of deduplication, encryption, and compression for SecureFiles.

 

Oracle Datapump supports SecureFiles as Default

Oracle 12c stores LOBs as SecureFiles per default. In the case where a table or a database using BasicFiles was exported using EXPDP, if you want to import the dump file into a 12c database, Data Pump will try to recreate LOBs exactly as they were stored in the old database. It was not possible to recreate LOBs directly as SecureFiles.

 

I think you have guessed, Oracle 12c offers a new clause for the impdp tool, in order to convert LOBs stored as BasicFiles to SecureFiles on the fly: TRANSFORM=LOB_STORAGE:SECUREFILE.

 

To demonstrate this new feature, I firstly created a table containing LOBs stored as Basicfile on an 11g Database.

 

SQL> connect msc/***
SQL> create table t1 (a BLOB);

 

Then I created a directory to put the dump file.

 

SQL> create directory DPUMP_DIR as '/home/oracle';

 

The path /home/oracle also contains several files. I inserted myfile.txt in the table t1 with the following PL/SQL code:

 

DECLARE
  src_lob  BFILE := BFILENAME('DPUMP_DIR', 'myfile.txt');
  dest_lob BLOB;
BEGIN
  INSERT INTO t1 VALUES(EMPTY_BLOB())
     RETURNING a INTO dest_lob;  DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,
                         SRC_LOB  => src_lob,
                         AMOUNT   => DBMS_LOB.GETLENGTH(src_lob) );
  DBMS_LOB.CLOSE(src_lob);  COMMIT;
END;
/

 

We can see that the LOB has not been stored as SecureFile:

 

SQL> select table_name, securefile from user_lobs;
TABLE_NAME                     SEC
------------------------------ ---
T1                             NO

 

Now, I perform the export of the table with the msc user:

 

$ expdp msc/*** dumpfile=EXPORT_T1.dmp logfile=EXPORT_T1.log directory=DPUMP_DIR tables=T1;

 

I will try to import this table to a 12c Database by two ways.

 

1) Without the TRANSFORM clause

 

$ impdp msc/*** dumpfile=EXPORT_T1.dmp logfile=IMPORT_T1.log directory=DPUMP_DIR table_exists_action=REPLACE

 

Let's see the SECUREFILE column in the USER_LOBS view:

 

SQL> select table_name, securefile from user_lobs;
TABLE_NAME                     SECUREFILE
------------------------------ ---------------
T1                             YES

 

As expected, Data Pump has recreated the table as it was stored in the 11g Database.

 

2) With the TRANSFORM clause

 

$ impdp msc/*** dumpfile=EXPORT_T1.dmp logfile=IMPORT_T1.log directory=DPUMP_DIR table_exists_action=REPLACE transform=LOB_STORAGE:SECUREFILE

 

This time, we can see that the LOB has been imported as SecureFile:

 

SQL> select table_name, securefile from user_lobs;
TABLE_NAME                     SECUREFILE
------------------------------ ---------------
T1                             YES

 

Parallel DML supported for LOB stored as SecureFiles on a non-partitioned table

With Oracle 11g, parallel DML is already supported with LOB columns, no matter if it is stored as BasicFile or SecureFile. But it is only supported for partitioned tables.

Oracle 12c offers SecureFiles LOB support enhancements, since it is now possible to perform statements in parallel with LOB columns stored as SecureFiles on a non partitioned table. For LOB stored as BasicFile, the statement still runs in Serial mode, even if the parallel clause is specified.

 

To demonstrate this new feature, I have created a table T1 on two 11g and 12c databases.

 

SQL> create table t1 (a BLOB)lob (a) store as SECUREFILE;

 

SQL> select table_name, securefile from user_lobs;
TABLE_NAME                     SEC
------------------------------ ---
T1                             YES

 

DECLARE
  src_lob BFILE := BFILENAME('DPUMP_DIR', 'myfile.txt');
  dest_lob BLOB;
BEGIN
  INSERT INTO t1 VALUES(EMPTY_BLOB()) RETURNING a INTO dest_lob;
  DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob, SRC_LOB => src_lob, AMOUNT => DBMS_LOB.GETLENGTH(src_lob) );
  DBMS_LOB.CLOSE(src_lob);
  COMMIT;
END;
/

 

11g

Now let's see what happens on the 11g database if I try to perform an INSERT AS SELECT statement in parallel. I used the dbms_xplan package to see the execution plan of the statement.

 

SQL> set lines 200
SQL> alter session force parallel dml;
SQL> explain plan for insert into t1 select * from t1;
Explained.

 

SQL> select * from table(dbms_xplan.display);

 

plan_11g

 

Here we can see that the SELECT statement has been performed in parallel (it corresponds to the TABLE ACCESS FULL operation with ID 5), however the insertion of data was performed in serial, since the LOAD TABLE CONVENTIONAL operation (id 1) is above the PX COORDINATOR.

 

12c

I perform exactly same operations on the 12c database.

 

SQL> set lines 200
SQL> alter session force parallel dml;
SQL> explain plan for 2 insert into t1 select * from t1;
Explained.

 

SQL> select * from table(dbms_xplan.display);

 

plan_12c

 

This time, we can see that the LOAD AS SELECT operation has been performed in parallel mode, since it is located below the PX COORDINATOR operation on this execution plan.

 

A word about Oracle documentation: in the Oracle Database SecureFiles and Large Objects Developer's Guide, we can read the following:

 

Oracle supports parallel execution of most of the following DML operations when performed on partitioned tables with SecureFiles LOBs or BasicFiles LOBs, and non-partitioned tables with SecureFiles LOBs only

  • INSERT
  • INSERT AS SELECT
  • CREATE TABLE AS SELECT
  • DELETE
  • UPDATE
  • MERGE (conditional UPDATE and INSERT)

[...]

 

"Most of the following DML" does not necessary mean that all of these DML are supported. I performed some tests and I could only run INSERT AS SELECT, DELETE, UPDATE and CREATE TABLE AS SELECT (which is a DDL in fact) statements in parallel with SECUREFILE on a range partitioned table. On a non-partitioned table, only INSERT AS SELECT worked in parallel...

Rate this blog entry:
1

Michael Schwalm is Consultant at dbi Services and has more than two years of experience in Oracle database administration. He has a broad knowledge in the realization of virtualization infrastructures such as vMware vSphere. He took his first steps in database administration as an integrator of a web applications on Unix, Oracle, and Websphere environments. Michael Schwalm is Oracle Certified Professional 11g and RAC Implementation Specialist 11g. Prior to joining dbi services, Michael Schwalm was application administrator at SOGETI Est (F) on behalf of PSA Peugeot Citroen and responsible for the realization and managing of Unix environments and Oracle databases in the context of migration projects. Michael Schwalm holds a BTS diploma in Information System Management from Belfort (F) and a TSAR diploma in advanced network administration from Strasbourg (F). His branch-related experience covers Automotive, Software industry, Financial Services / Banking, etc.

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Saturday, 20 December 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter