Migration Methods

15.2 Migration Methods

This section describes various methods you can use to migrate LONG or BasicFile LOB data to SecureFile storage.

Topics

Migrating LOBs with SecureFiles Migration Utility
This is the recommended method to migrate BasicFile LOB data to SecureFile storage. This utility encapsulates all the functionality offered by Online Redefinition and saves you the time and effort involved in manually running a series of API calls.
Migrating LOBs with Online Redefinition
Use Online redefinition to migrate LONG or BasicFile LOB data to SecureFile storage by running several API calls.
Migrating LOBs with Data Pump
Oracle Data Pump can either recreate tables as they are in your source database, or recreate LOB columns as SecureFile LOBs.

Parent topic: Migrating Columns to SecureFile LOBs

15.2.1 Migrating LOBs with SecureFiles Migration Utility

This is the recommended method to migrate BasicFile LOB data to SecureFile storage. This utility encapsulates all the functionality offered by Online Redefinition and saves you the time and effort involved in manually running a series of API calls.

Advantages

No need not take the table or partition offline.
Perform the migration at the database, schema, table or LOB segment level.
After migrating the data, you can also use the SecureFiles Migration Utility to compress the SecureFile LOBs.

Disadvantages

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

To migrate BasicFile LOB data to SecureFile storage using the SecureFiles migration utility:

Run the following command as is to create a table.

create table migration_config (ctime date, data clob , constraint c1 check(data is json));

Make a single entry in the table to specify the schema, table, and columns that you want to migrate. Enter first as the value for run_type as this is the first time you are running the script. For others, provide values based on your environment.
Example Command

The following example shows a example single entry that specifies the objects that you want to migrate.
```
insert into migration_config values
    (systimestamp,
    {"schema_name" : ["TEST2"],
    "table_name" : ["TEST1.TAB_DEFERRED_SEGCREATION1", "TEST1.TAB_NON_LOB1",
    "TEST1.BASIC1A", "TEST1.BASIC3A"],
    "column_name" : ["TEST1.TAB_PARTS1.a",
    "TEST1.BASIC123.a", "TEST1.BASIC125.a"],
    "metadata_schema_name" : "TEMP1",
    "run_type" : "first", 
    "directory_path" : "<full path to folder for log files>",
    "compress_storage_rec_threshold" : 5000, 
    "trace" : 1});
```
Where,
- schema_name: Mandatory. Specify a comma-separated list of schema names that you want to migrate and compress. If you do not specify a value, {"schema_name" : []}, the entire schema is not migrated. Instead the script checks for finer granularity that may be specified in the table_name or column_name arrays.
- table_name: Mandatory. Specify a comma-separated list of tables that you want to migrate and compress. You must enter the name in the following format, <schema_name>.<table_name>, where the schema name prefixes the table name. In the following example, TEST1 is the name of the schema and BASIC1A and BASIC3A are the names of the tables.
```
"table_name" : ["TEST1.BASIC1A", "TEST1.BASIC3A"]
```
  If you do not specify a value, "table_name" : [], then the script checks for finer granularity that may be specified in the column_name array.
- column_name: Mandatory. Specify a comma-separated list of columns that you want to migrate and compress. You must enter the name in the following format, <schema_name>.<table_name>.<column_name>, where the schema name and table name prefixes the column name.
  If you do not specify a value, "column_name" : [], then all LOB columns belonging to the specified table are migrated.
- metadata_schema_name: Mandatory. Enter a unique schema name. This is a temporary schema which the script uses to store metadata tables or reports that are generated during the migration. The schema must have a default ASSM tablespace to store intermediate data.
- run_type: Mandatory. The permitted values are first, second, and third corresponding to the three stages in which you execute the script.
- directory_path: Mandatory. Enter the complete path to the folder where you want to save the generated log files if trace is 1.
- compress_storage_rec_threshold: Optional. The script recommends compressing LOBs that are above the storage threshold that you enter. The default value is 5000 MB.
- trace: Optional. Set this to 1 to enable tracing. The log files are saved in the folder path that you specify in directory_path. If you do not enter a value or enter any other value, then the log files are not generated.
Example entry to migrate and compress all tables in specified schemas

The following example entry in the migration_config table would migrate and then compress all tables and columns in the specified schemas, TEST1 and TEST2 when you run the script.
```
insert into migration_config values (systimestamp,{
    "schema_name" : ["TEST1","TEST2"],
    "table_name" : [], 
    "segment_name" : [], 
    "metadata_schema_name" : "TEMP1", 
    "run_type" : "first", 
    "directory_path" : "<full_path>",
    "trace" : 1});
```
Example entry to migrate and compress all schemas, tables, and columns

The following example entry in the migration_config table would migrate and then compress all schemas, tables, and columns when you run the script.
```
insert into migration_config values (systimestamp,{
    "schema_name" : [],
    "table_name" : [], 
    "segment_name" : [], 
    "metadata_schema_name" : "TEMP1", 
    "run_type" : "first", 
    "directory_path" : "<full_path>",
    "trace" : 1});
```
Run the script as SYS user after creating the table and inserting a row with details of the required configuration.
```
SQL> @securefile_migration_script.sql
```
Three reports, report1, report2, and report3 are generated and stored as tables in the temporary table space. You had specified the name of the temporary table space in metadata_schema_name in a previous step.
- Report1 table provides details about the DDL information for all the tables in specified schema for all users.
- Report2 table provides details about index DDL information for all the tables in specified schema for all users.
- Report3 table lists all the BasicFile LOB segments under all users.
Look at the reports and identify if there are any BasicFile LOBs that you do not want to migrate, such as when BasicFile LOB segment is on MSSM tablespace. If you do not want to migrate a BasicFile LOB, change the value of the Allow migrate column in the report3 table to N for the specific LOB that you do not want to migrate. The default value for all LOBs is Y.
Create an interim table for the LOBs that you want to migrate. Ensure that the interim table that you create is identical to the original table in all respects except for the property that you intent to change.
Example Original Table
For example, let's consider that the existing BasicFile table that you want to migrate has the following properties.
```
CREATE TABLE basic1a
(
    a CLOB,
    b NUMBER
);
```
Example Interim Table

As shown in the following example, the interim table that you create must be identical to the original table, except for the store as securefile in the interim table and the name of the interim table must be unique.
```
CREATE TABLE basic1a_int1
(
    a CLOB,
    b NUMBER
) lob(a) store as securefile;
```
Update the row that you have inserted to change the run_type to second as shown in the following command.
```
SQL> update migration_config set data = JSON_TRANSFORM(data, SET '$.run_type' = 'second');
```
Run the script as SYS user.
```
SQL> @securefile_migration_script.sql
```
The BasicFile LOB data is migrated to SecureFile storage.

After the migration is completed successfully, the script generates report4 and report5 and stores it as tables in the temporary table space. You had specified the name of the temporary table space in metadata_schema_name in a previous step.
- report4 table provides details about the LOBs, such as storage, compression ratio, compression recommendation, compression type.
- report5 table contains recommendations about which LOBs should be compressed in the COMPRESS_RECOMMENDATION column as Y (yes) or N (no). The recommendation is based on the information available in report4.
Look at the second report and identify if you want to compress the LOBs are per the recommendation. If you do not want to compress LOBs, skip the next steps.
Look at the reports and identify if there are any SecureFile LOBs that you do not want to compress. If you do not want to compress a LOB, change the value of the compress_recommendation column in report 4 to N for the specific LOB that you do not want to compress. The default value is Y for all LOBs.
Create an interim table for the LOBs that you want to compress. Ensure that the interim table that you create is identical to the original table in all respects except for the property that you intent to change.
Example Original Table
For example, let's consider that the existing BasicFile table that you want to migrate has the following properties.
```
CREATE TABLE basic1a
(
    a CLOB,
    b NUMBER
);
```
Example Interim Table

As shown in the following example, the interim table that you create must be identical to the original table, except for the LOB(a) STORE AS SECUREFILE seg_basic1a (ENABLE STORAGE IN ROW CACHE LOGGING COMPRESS MEDIUM) in the interim table and the name of the interim table must be unique.
```
CREATE TABLE comp_basic1a_int1
(
    a CLOB,
    b NUMBER
) LOB(a) STORE AS SECUREFILE seg_basic1a (ENABLE STORAGE IN ROW CACHE LOGGING COMPRESS MEDIUM);
```
Where, you can specify LOW, MEDIUM, and HIGH as the options to provide varying degrees of compression.

Update the row that you have inserted to change the run_type to third.

SQL> update migration_config set data = JSON_TRANSFORM(data, SET '$.run_type' = 'third);

Run the script as SYS user.
```
SQL> @securefile_migration_script.sql
```
Report 5 is updated with the status of compression.

Parent topic: Migration Methods

15.2.2 Migrating LOBs with Online Redefinition

Use Online redefinition to migrate LONG or BasicFile LOB data to SecureFile storage by running several API calls.

Consider using the SecureFiles Migration Utility, which automates this task and saves you the time and effort involved in manually running a series of API calls. See Migrating LOBs with SecureFiles Migration Utility.

While online redefinition for LONG to LOB migration must be performed at the table level, BasicFile to SecureFile migration can be performed at the table or partition level.

Online Redefintion Advantages

No need not take the table or partition offline
Can be done in parallel.
To set up parallel execution of online redefinition, run:
```
ALTER SESSION FORCE PARALLEL DML;
```

Online Redefinition Disadvantages

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

Example 15-1 Online Redefinition for Migrating Tables from BasicFiles LOB storage to SecureFile LOB storage

REM Grant privileges required for online redefinition.
GRANT EXECUTE ON DBMS_REDEFINITION TO pm;
GRANT ALTER ANY TABLE TO pm;
GRANT DROP ANY TABLE TO pm;
GRANT LOCK ANY TABLE TO pm;
GRANT CREATE ANY TABLE TO pm;
GRANT SELECT ANY TABLE TO pm;
REM Privileges required to perform cloning of dependent objects.
GRANT CREATE ANY TRIGGER TO pm;
GRANT CREATE ANY INDEX TO pm;
CONNECT pm/pm

-- This forces the online redefinition to execute in parallel
ALTER SESSION FORCE parallel dml;

DROP TABLE cust;
CREATE TABLE cust(c_id NUMBER PRIMARY KEY,
    c_zip NUMBER,
    c_name VARCHAR(30) DEFAULT NULL,
    c_lob CLOB
);
INSERT INTO cust VALUES(1, 94065, 'hhh', 'ttt');
-- Creating Interim Table
-- There is no requirement to specify constraints because they are
-- copied over from the original table.
CREATE TABLE cust_int(c_id NUMBER NOT NULL,
    c_zip NUMBER,
    c_name VARCHAR(30) DEFAULT NULL,
    c_lob CLOB
) LOB(c_lob) STORE AS SECUREFILE (NOCACHE FILESYSTEM_LIKE_LOGGING);
DECLARE
    col_mapping VARCHAR2(1000);
BEGIN
-- map all the columns in the interim table to the original table
    col_mapping :=
    'c_id c_id , '||
    'c_zip c_zip , '||
    'c_name c_name, '||
    'c_lob c_lob';
DBMS_REDEFINITION.START_REDEF_TABLE('pm', 'cust', 'cust_int', col_mapping);
END;
/
DECLARE
    error_count pls_integer := 0;
BEGIN
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('pm', 'cust', 'cust_int',
      1, TRUE,TRUE,TRUE,FALSE, error_count);
    DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('pm', 'cust', 'cust_int');
-- Drop the interim table
DROP TABLE cust_int;
DESC cust;
-- The following insert statement fails. This illustrates
-- that the primary key constraint on the c_id column is
-- preserved after migration.
INSERT INTO cust VALUES(1, 94065, 'hhh', 'ttt');
SELECT * FROM cust;

Example 15-2 Online Redefinition for Migrating Tables from the LONG datatype to a SecureFile LOB

The steps for LONG to LOB migration are:

Create an empty interim table. This table holds the migrated data when the redefinition process is done. In the interim table:
- Define a CLOB or NCLOB column for each LONG column in the original table that you are migrating.
- Define a BLOB column for each LONG RAW column in the original table that you are migrating.
Start the redefinition process. To do so, call DBMS_REDEFINITION.START_REDEF_TABLE and pass the column mapping using the TO_LOB operator as follows:
```
DBMS_REDEFINITION.START_REDEF_TABLE(
    'schema_name', 
    'original_table',
    'interim_table', 
    'TO_LOB(long_col_name) lob_col_name',
    'options_flag',
    'orderby_cols'); 
```
where long_col_name is the name of the LONG or LONG RAW column that you are converting in the original table and lob_col_name is the name of the LOB column in the interim table. This LOB column holds the converted data.
Call the DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS procedure as described in the related documentation.
Call the DBMS_REDEFINITION.FINISH_REDEF_TABLE procedure as described in the related documentation.

The following example demonstrates online redefinition for LONG to LOB migration.

REM Grant privileges required for online redefinition.
GRANT execute ON DBMS_REDEFINITION TO pm;
GRANT ALTER ANY TABLE TO pm;
GRANT DROP ANY TABLE TO pm;
GRANT LOCK ANY TABLE TO pm;
GRANT CREATE ANY TABLE TO pm;
GRANT SELECT ANY TABLE TO pm;

REM Privileges required to perform cloning of dependent objects.
GRANT CREATE ANY TRIGGER TO pm;
GRANT CREATE ANY INDEX TO pm;

CONNECT pm/pm

-- This forces the online redefinition to execute in parallel
ALTER SESSION FORCE parallel dml;

DROP TABLE cust;
CREATE TABLE cust(c_id   NUMBER PRIMARY KEY,
                  c_zip  NUMBER,
                  c_name VARCHAR(30) DEFAULT NULL,
                  c_long LONG
                  );
INSERT INTO cust VALUES(1, 94065, 'hhh', 'ttt');

-- Creating Interim Table 
-- There is no requirement to specify constraints because they are 
-- copied over from the original table.
CREATE TABLE cust_int(c_id  NUMBER NOT NULL,
                  c_zip  NUMBER,
                  c_name VARCHAR(30) DEFAULT NULL,
                  c_long CLOB
                  );

DECLARE
 col_mapping VARCHAR2(1000);
BEGIN
--  map all the columns in the interim table to the original table
 col_mapping :=
               'c_id             c_id  , '||
               'c_zip            c_zip , '||
               'c_name           c_name, '||
               'to_lob(c_long)   c_long';

DBMS_REDEFINITION.START_REDEF_TABLE('pm', 'cust', 'cust_int', col_mapping);
END;
/

DECLARE
 error_count PLS_INTEGER := 0;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('pm', 'cust', 'cust_int',
                                          1, true, true, true, false,
                                          error_count);

  DBMS_OUTPUT.PUT_LINE('errors := ' || to_char(error_count));
END;
/

EXEC  DBMS_REDEFINITION.FINISH_REDEF_TABLE('pm', 'cust', 'cust_int');

-- Drop the interim table
DROP TABLE cust_int;

DESC cust;

-- The following insert statement fails. This illustrates 
-- that the primary key constraint on the c_id column is 
-- preserved after migration. 

INSERT INTO cust VALUES(1, 94065, 'hhh', 'ttt');

SELECT * FROM cust;

Parent topic: Migration Methods

15.2.3 Migrating LOBs with Data Pump

Oracle Data Pump can either recreate tables as they are in your source database, or recreate LOB columns as SecureFile LOBs.

When Oracle Data Pump recreates tables, by default it recreates them as they existed in the source database. Therefore, if a LOB column was a BasicFiles LOB in the source database, Oracle Data Pump attempts to recreate it as a BasicFile LOB in the imported database. However, you can force creation of LOBs as SecureFile LOBs in the recreated tables by using a TRANSFORM parameter for the command line, or by using a LOB_STORAGE parameter for the DBMS_DATAPUMP and DBMS_METADATA packages.

Example:

impdp system/manager directory=dpump_dir schemas=lobuser dumpfile=lobuser.dmp
      transform=lob_storage:securefile

Note:

The transform name is not valid in transportable import.