Secure file Migration and Accessing securefile metadata information-1170351.1

最新推荐文章于 2021-04-09 03:43:07 发布

cuilian2013

最新推荐文章于 2021-04-09 03:43:07 发布

阅读量264

点赞数

文章标签：数据库

Secure file Migration and Accessing securefile metadata information [Video] (文档 ID 1170351.1)

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.1 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Purpose

The goal of this article is to highlight how to migrate to Secure files and use of different PL/SQL packages to access Securefile metadata information.

Scope and Application

. Secure file Migration Presentation-Video
. SecureFiles Migration
. SecureFiles Migration example
. SecureFiles Metadata information

Secure file Migration and Accessing securefile metadata information [Video]

SECURE FILE MIGRATION PRESENTATION-VIDEO:

downloadattachmentprocessor?attachid=778.1:VIDEOLOGO&clickstream=no

Video - SecureFile Migration and Accessing Metadata Information (05:30)

downloadattachmentprocessor?attachid=778.1:IC.&clickstream=no

MIGRATING TO SECURE FILES:

We can easily migrate from a Basicfile to Secure file LOBS.The two recommended methods for migration to securefiles are Partition exchange and online redefinition.

1] Partition Exchange

. Needs additional space equal to the largest of the partition in
the table.
. Can maintain indexes during the exchange.
. Can spread the workload out over several smaller maintenance
windows.
. Required that the table or Partition needs to be offline to
perform. the exchange.

2] Online Redefinition

. No need to take the table or partition offline.
. Can be done in parallel.
. Requires additional storage equal to the entire table and all
LOB segments to be available.
. Requires that any global indexes to be rebuilt.

Online redefinition is recommended method.However,Using portioning and taking these actions on a partition-by-partition basis may be help lower the disk space required.

NOTE:Once conversion has been completed, that LOB cannot be downgraded to a BasicFile LOB.

SECUREFILES MIGRATION EXAMPLE:

In this article, we will be using a simple partitioned table with one LOB column for our examples. The example is kept simple for clarity of explaining the example to migrate from BasicFiles to SecureFiles.

This method works whether or not your table is partitioned. To migrate an entire table via online redefinition, you simply create a new table with the properties you want, in our case the LOB columns are SecureFiles, and migrate the data to that table. On completion, you can drop this newly created table, as it will be holding the original, pre-migration, data.During the migration more than 2x the space is required for the original table and LOBs. This is because the data will be stored in two locations, the original table and the migration table. Upon completion of the migration, the original data can be dropped and the space can be reused. If compression and deduplication are used on the destination SecureFile columns, the amount of space required will be reduced appropriately. Please see the Administrator’s Guide for more information/restrictions of online redefinition.

Let’s look at a simple example of Online Redefinition of a simple table with a Single LOB column.

Our source table definition:

create table TEST(
c_id number primary key,
c_lob clob
)
lob (c_lob) store as
(tablespace LOBTBS)
partition by range(c_id) (
partition tab1_p1 values less than (100),
partition tab1_p2 values less than (200),
partition tab1_p3 values less than (300),
partition tab1_p4 values less than (400));

Our destination table definition:

create table TEST2(
c_id number primary key,
c_lob clob
)
lob(c_lob) store as SecureFile
(tablespace LOBTBS2)
partition by range(c_id) (
partition tab1_sf_p1 values less than (100),
partition tab1_sf_p2 values less than (200),
partition tab1_sf_p3 values less than (300),
partition tab1_sf_p4 values less than (400));

Note: LOBTBS2 is an ASSM tablespace.

Notice two significant changes to the original table definition. They are in bold above. The tablespace is in bold to note that SecureFiles must be created in an ASSM tablespace. If you are still using MSSM tablespaces for your (BasicFile) LOB data, you will need to create or allocate ASSM tablespaces to implement the use of SecureFiles.Performing the actual redefinition is simple. For our example, we’ll assume the table above, and user “Scott”.

1. Create the destination table (TEST2 from above).

2. Start the redefinition:
SQL> dbms_redefinition.start_redef_table('SCOTT', 'TEST', 'TEST2','C_ID C_ID, C_LOB C_LOB');

3. Copy the table dependents:
SQL> dbms_redefinition.copy_table_dependents('SCOTT', 'TEST', 'TEST2', 1, true,
true, true, false, error_count);

4. Finish the redefinition (swaps the table names):
SQL> dbms_redefinition.finish_redef_table('SCOTT','TEST', 'TEST2');

5. Drop the destination table(Optional) :
SQL> DROP TABLE SCOTT.TEST2;

When this process completes, TEST’s LOB column will be a SecureFile LOB column, TEST2 will have TEST’s original definition. TEST2 can now be dropped to remove the space used by the original table.

ACCESSING SECUREFILES METADATA:

Oracle 11g provides several methods to keep track of BasicFile and SecureFile LOB metadata.

1] DBMS_LOB packages:

LOB inherit the LOB column settings for deduplication,encryption and compression which can also be configured on a per LON level using the LOB locater API.However,the LONG API cannot be used to configure these LOB settings.You must use the following DBMS_LOB packages for these features.

DBMS_LOB.GETOPTIONS This function obtains settings corresponding to the option_type field for a particular LOB.An integer corresponding to a predefined constant based on the option type is returned.The return values are a combination of COMPRESS_ON, ENCRYPT_ON and DEDUPLICATE_ON depending on which option types are passed in.

DBMS_LOB.SETOPTIONS This procedure enables/disables features on a per-LOB basis, overriding the default LOB column settings.DBMS_LOB.SETOPTIONS cannot be used to enable or disable encryption on individual LOBs.It incurs a round trip to the server to make the changes persistent.

You cannot turn compression or deduplication on or off for a SecureFile column that does not have those features on. The GETOPTIONS Functions and SetOptions Procedures work on individual SecureFiles. You can turn off a feature on a particular SecureFile and turn on a feature that has already been turned off by SetOptions,but you cannot turn on an option that has not been given to the SecureFile when the table was created.

2] DBMS_SPACE.SPACE_USAGE:

The existing SPACE_USAGE procedure is overloaded to return information about LOB space usage.It returns the amount of disk space in blocks used by all the LOBS in the LOB segment.This procedure can be used only in tablespace created with ASSM and does not treat LOB chunks belongings to BAsicFile as used space.

3] SECURE FILES MONITORING:

Oracle 11g modified several data dictionary views to provide additional information about SecureFiles and a list of these modified views is provided below:

DATA DICTONARY          VIEW USAGE

DBA_SEGMENTS            Shows all segments in the database, and includes a
                        new column, SEGMENT_SUBTYPE, that describes the LOB
                        segment type.
DBA_LOBS                Lists all LOBs in the database, and now includes
                        metadata about compression,encryption and
                        deduplication.
DBA_LOB_PARTITIONS      Describes all LOB partitions in the database, and now
                        includes metadata about compression,encryption and
                        deduplication.
DBA_PART_LOBS           Shows table-level metadata for all partitioned LOBs
                        in the database.