Migration to SecureFiles using Online Table Redefinition in Oracle 11gR2

Migration to SecureFiles using Online Table Redefinition in Oracle 11gR2

Oracle introduced SecureFiles in Oracle 11g to enhance existing LOB types. The new SecureFiles concept and architecture is entirely new engineered LOBs in comparison to the old LOBs existing prior to Oracle 11g that are now referred to as Basic Files. Information about SecureFiles in Oracle 11gR2 can be found here. Information about how to use secure files can be found here. Note that for compatibility reasons Basic Files functionality is supported.

1. Environment is set by the value of DB_SECUREFILE parameter controlling if LOBS are created as Basicfiles or SecureFiles.
   

   The db_securefile parameter is set as show below.

   SQL> show parameter db_securefile

   NAME TYPE VALUE

   ———————————— ———– ——————————

   db_securefile string PERMITTED

   SQL>

   Values than db_securefile parameter can take are:

   PERMITTED – Allows the DBA to create SecureFiles

   NEVER – disallow creation in the future ( after the parameter is set to this value)

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

   IGNORE – ignores SecureFiles LOB options and SECUREFILE keyword. LOBS are created as BasicFiles.

2. Create a table tab1 in demo schema as BasicFiles LOB.
   

   SQL> connect demo/demo;

   Connected.

   SQL> create table tab1 (id number primary key, c clob)

   partition by range(id)

   (partition p1 values less than (100) tablespace lob_ts lob(c) store as BASICFILE lobp1,

   partition p2 values less than (200) tablespace lob_tslob(c) store as BASICFILE lobp2,

   partition p3 values less than (300) tablespace lob_tslob(c) store as BASICFILE lobp3); 2 3 4 5

   Table created.

   SQL>

1. Verify created CLOB partitions are Basicfiles.
   

   SQL> select table_name, securefile from dba_lob_partitions;

   TABLE_NAME SEC

   —————————— —

   TAB1 NO

   TAB1 NO

   TAB1 NO

1. Populate the basic table and gather stats.
   

   SQL> insert into tab1 values(1,’Value in part 1′);

   1 row created.

   SQL> insert into tab1 values(110,’Value in Partition 2′);

   1 row created.

   SQL>

   SQL> insert into tab1 values(220,’Value in Partition 3′);

   1 row created.

   SQL> commit;

   Commit complete.

   SQL> exec dbms_stats.gather_table_stats(‘DEMO’,’TAB1′);

   PL/SQL procedure successfully completed.

   SQL>

   SQL> select table_name, partition_name, num_rows from user_tab_statistics;

   TABLE_NAME PARTITION_NAME NUM_ROWS

   —————————— —————————— ———-

   TAB1 3

   TAB1 P1 1

   TAB1 P2 1

   TAB1 P3 1

   SQL>

2. Create an intermediate table.
   

   SQL> create table tab1_tmp (id number, c clob)

   partition by range(id)

   (partition p1 values less than (100) tablespace lob_ts lob(c) store as SECUREFILE lobp1 (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING),

   partition p2 values less than (200) tablespace lob_ts lob(c) store as SECUREFILE lobp2 (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING),

   partition p3 values less than (300) tablespace lob_ts lob(c) store as SECUREFILE lobp3 (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING)); 2 3 4 5

   Table created.

SQL>

1. Verify created CLOB partitions corresponding to the intermediate are Securefiles.
   

SQL> select table_name, securefile from dba_lob_partitions;

TABLE_NAME SEC

—————————— —

TAB1 NO

TAB1 NO

TAB1 NO

TAB1_TMP YES

TAB1_TMP YES

TAB1_TMP YES

7 rows selected.

SQL>

1. Perform the Online redefinition.
   

   SQL> set serveroutput on;

   SQL> declare

   2 error_Count number;

   3

   4 begin

   5 dbms_redefinition.start_redef_table(‘demo’,’tab1′,’tab1_tmp’,’id id, c c’);

   6 dbms_redefinition.copy_table_dependents(‘demo’,’tab1′,’tab1_tmp’,1,

   7 true,true,true,false,error_count);

   8 DBMS_OUTPUT.PUT_LINE(‘errors := ‘ || TO_CHAR(error_count));

   9 dbms_redefinition.finish_redef_table(‘demo’,’tab1′,’tab1_tmp’);

   10 end;

   11 /

   errors := 0

   PL/SQL procedure successfully completed.

   SQL>

1. Check the outcome.
   

   SQL> select table_name, securefile from dba_lob_partitions;

   TABLE_NAME SEC

   —————————— —

   TAB1_TMP NO

   TAB1_TMP NO

   TAB1_TMP NO

   TAB1 YES

   TABLE_NAME SEC

   —————————— —

   TAB1 YES

   TAB1 YES

   13 rows selected.

   SQL>

   SQL> select * from tab1;

   ID C

   ———- ———————————————————————

   ———–

   1 Value in part 1

   110 Value in Partition 2

   220 Value in Partition 3

   SQL>

   Oracle redefines tab1 as Securefiles.

Online redefinition is the only method that Oracle recommends for migration of BasicFiles LOBs to SecureFiles LOBs.

Online Redefinition Advantages

• No requirement to take the table or partition offline
  
• Can be done in parallel
  

Online Redefinition Disadvantages

• Additional storage equal to the entire table or partition and all LOB segments must be available
  
• Global indexes must be rebuilt