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.
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.

Parent topic: Migration Methods

15.2.1.1 About SecureFiles Migration Utility

You can use the SecureFiles Migration Utility to perform the following tasks:

Migrate BasicFile LOB data to SecureFile storage, without compressing the data.
Migrate BasicFile LOB to compressed SecureFile LOBs.
Migrate uncompressed SecureFile LOBs to compressed SecureFile LOBs.

Flowchart to run the SecureFiles Migration Utility twice

To compress and migrate the LOBs, run the SecureFiles Migration Utility twice as shown in the following flowchart. In the first run, the utility analyzes the specified LOBs and generates reports with migration and compression recommendations. After you review the reports, confirm or update the reports to specify which LOBs you want to migrate or compress. Create an interim table, and then run the utility again to migrate and compress the LOBs.

flowchart for the securefiles migration utility

Advantages

No need to 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.

Parent topic: Migrating LOBs with SecureFiles Migration Utility

15.2.1.2 Migrate LOBs with SecureFiles Migration Utility

To migrate BasicFile LOB data to SecureFile storage using the SecureFiles Migration Utility:

Create a tablespace with Automatic Segment Space Management (ASSM) or use an existing one. Note down the name of the tablespace.
Sample Code

The following command creates a ASSM tablespace with the name, LOBTEMP1.
```
CREATE TABLESPACE LOBTEMP1 DATAFILE 'lobtemp1.dbf' SIZE 1G REUSE AUTOEXTEND ON SEGMENT SPACE MANAGEMENT AUTO;
```
Create a user, who is associated with this tablespace. You can skip this step if you want to use an existing ASSM tablespace with an associated user.
When you run the SecureFiles Migration Utility, it creates reports against this user.

Sample Code

The following command creates a user, with the name temp1. It also associates the user with LOBTEMP1, the ASSM tablespace you have created in the previous step.
```
CREATE USER temp1 identified by temp1 DEFAULT TABLESPACE LOBTEMP1;
```
Ensure that all the schemas on which you run the SecureFiles Migration Utility have the right privileges to access this tablespace. Failing to do so, might result in errors, such as insufficient quota for user on tablespace.
Run the following command, exactly as written to create a table, as a SYS user.
```
create table migration_config (ctime date, data clob , constraint c1 check(data is json));
```
Later, when you run the script, it reads data from the migration_config table that you have created.
Insert a single row 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 how you can insert a single row into the migration_config table.
```
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",
    "metadata_tablespace_name" : "LOBTEMP1",
    "run_type" : "first", 
    "compress_storage_rec_threshold" : 5000, 
    "directory_path" : "<full path to folder for log files>",
    "trace" : 1}');
```
Where,
- schema_name: It is mandatory to provide the key name, but the value is optional. 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: It is mandatory to provide the key name, but the value is optional. 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: It is mandatory to provide the key name, but the value is optional. 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. The script uses this schema to store metadata tables or reports that are generated during the migration. The schema must have a default ASSM tablespace to store intermediate data.
- metadata_tablespace_name: Mandatory. Specify the name of the ASSM tablespace to which the schema specified in metadata_schema_name belongs. This is the name of the ASSM tablespace that you had copied in the first step.
- run_type: Mandatory. The permitted values are first and second corresponding to the two stages in which you execute the script.
- 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.
- directory_path: Optional. Enter the complete path to the folder where you want to save the generated log files if trace is set to 1. If you do not enable tracing, then you don't need to mention the directory_path key.
- trace: Optional. Set this to 1 to enable tracing. A log file with the name securefile_migration_utility.log is created and saved in the folder path that you specify in directory_path. If you do not enter a value, then the log file is not generated.
Example insert to migrate and compress all LOB columns for all tables in the specified schemas

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

The following example entry in the migration_config table would migrate and then compress all LOB columns for all schemas, tables, and columns when you run the script.
```
insert into migration_config values (
    systimestamp,
    '{"schema_name" : [],
    "table_name" : [], 
    "column_name" : [],
    "metadata_schema_name" : "TEMP1",
    "metadata_tablespace_name" : "LOBTEMP1",
    "run_type" : "first", 
    "directory_path" : "<full_path>",
    "trace" : 1}');
```
Run the script as SYS user after creating the migration_config table and inserting a row with details of the required configuration.
```
SQL> @securefile_migration_script.sql
```
The following reports are generated and stored as tables in the temporary tablespace. You had specified the name of the temporary tablespace in metadata_schema_name in a previous step.
- sf_migration_table_ddl_report table provides details about the DDL information for all the tables in the specified schema for all users.
- sf_migration_index_ddl_report table provides details about index DDL information for all the tables in the specified schema for all users.
- sf_migration_basicfile_report table lists all the BasicFile LOB segments under all users and provides details about the BasicFile LOBs, such as storage, compression ratio, compression recommendation, and compression type. It contains recommendations about which BasicFile LOBs should be compressed in the COMPRESS_RECOMMENDATION column as Y (yes) or N (no).
- sf_migration_lob_statistics_report table contains recommendations about which SecureFile LOBs should be compressed in the COMPRESS_RECOMMENDATION column as Y (yes) or N (no). It also provides details about the SecureFile LOBs, such as storage, compression ratio, compression recommendation, and compression type.
- sf_migration_basicfile_migration_report table is generated. It is populated with data only after the migration is complete, which happens when you set run_type as second and then run the script again.
- sf_migration_lob_migration_report table is generated. It is populated with data only after the migration is complete, which happens when you set run_type as second and then run the script again.
See sf_migration_basicfile_report to identify any BasicFile LOBs that you do not want to migrate or compress as per the recommendation. See sf_migration_lob_statistics_report to identify any SecureFile LOBs that you do not want to compress as per the recommendation. Update the respective reports if you do not want to compress or migrate a LOB.
- If you do not want to compress a LOB, update the value of the compress_recommendation column in sf_migration_lob_statistics_report to N for the specific LOB that you do not want to compress. The default value is Y for all LOBs.
- If there are any BasicFile LOBs that you do not want to migrate, update the value of the Allow migrate column to N in sf_migration_basicfile_report for the specific LOB that you do not want to migrate. The default value for all LOBs is Y.
Oracle recommends that you migrate BasicFile LOBs to SecureFile LOBs.
Create an interim table under the same user, who owns the original table. You must create the interim table with the name mentioned in the interim_table column of the sf_migration_basicfile_migration_report or sf_migration_lob_migration_report report. Otherwise, the script cannot locate the interim table and it fails with the ora-00942 table not found error message.
Let's consider that there is a base table with BasicFile LOBs that you want to migrate using the SecureFiles Migration Utility.
```
CREATE TABLE basic1a
(
    a CLOB,
    b NUMBER
);
```
The following example shows how you can create an interim table to migrate BasicFile LOBs in the basic1a table to SecureFile compressed LOB, with medium compression.
```
CREATE TABLE basic1a_int1
(
    a CLOB,
    b NUMBER
) lob(a) store as securefile (compress medium);
```
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
```
Based on the recommendations in sf_migration_lob_statistics_report and sf_migration_basicfile_report, the script migrates BasicFile LOB segments to compressed or uncompressed SecureFile LOBs and it compresses SecureFile LOB segments.

After the migration is completed successfully, the script updates the following reports. These tables provides status about the success or failure of compressing the LOBs.
- sf_migration_basicfile_migration_report table provides details about the success or failure of migration of BasicFile LOBs. The generated_ddl column provides the details of DDL for the new table.
- sf_migration_lob_migration_report table provides details about the success or failure of compression of SecureFile LOBs.

Parent topic: Migrating LOBs with SecureFiles Migration Utility

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.