Converting your LOB tables from BasicFiles to SecureFiles format in Symantec DLP

Products

• Data Loss Prevention Enforce
• Data Loss Prevention
• Data Loss Prevention Oracle Standard Edition 2

Issue/Introduction

If your database is still using BasicFiles LOB storage, you should move to SecureFiles LOB storage format.

Refer to "Solution #1" for Oracle Standard Edition (12c and newer).

Refer to "Solution #2" for Oracle Enterprise Edition (any version) and version 11g Standard.

Cause

Oracle Standard Edition 12c and newer does not support online table redefinition, which is used by the Symantec database space reclamation utility. The utility script is no longer valid for Oracle Standard Edition 12c and newer, but is valid for Oracle Enterprise Editions with the attached script.

Resolution

Solution #1

This solution applies to all supported databases and requires that you shut down the system during the conversion process.

Unlike BasicFiles LOB storage, SecureFiles LOB storage tracks deleted LOBs and makes that space available after the retention period expires. After converting to SecureFiles LOB storage, you do not need to run a script to reclaim LOB space in your database. Space reclamation is handled automatically.

If you are using an Oracle 12c Standard database that still includes BasicFiles LOB storage tables, you should convert them as soon as possible to take advantage of the improved functionality of the SecureFiles LOB storage format. You should convert your tables to SecureFiles format before running the Upgrade Readiness Tool when upgrading to the next release of Symantec Data Loss Prevention.

You can manually convert your Oracle 12c LOB tables from BasicFiles to SecureFiles using the following procedure:

1. Back up the Oracle database before making any changes.
2. Shut down all DLP services on your Enforce Server. The following links are to the Symantec Data Loss Prevention help. Your service names may be slightly different. You can also refer to the topics "Starting and stopping services on Linux" and "About starting and stopping services on Windows" in the Symantec Data Loss Prevention Administration Guide appropriate to your version.
   • For Linux, see Stopping an Enforce Server on Linux
   • For Windows, see Stopping an Enforce Server on Windows
3. On the Oracle server, stop the Oracle Listener service. This will prevent external connections to the database that may interfere with the export/import process. The remaining steps will need to be executed on the Oracle server directly.
4. Estimate if there is enough space on the database hard drive for the SecureFiles export by running the following queries:

   expdp protect/<protect password> NOLOGFILE=YES ESTIMATE_ONLY=YES TABLES='MESSAGELOB'

   expdp protect/<protect password> NOLOGFILE=YES ESTIMATE_ONLY=YES TABLES='MESSAGECOMPONENTLOB'

   expdp protect/<protect password> NOLOGFILE=YES ESTIMATE_ONLY=YES TABLES='CONDITIONVIOLATIONLOB'

   Use these estimates to provide confirmation for whether there is sufficient space on the database hard drive. If there is enough space, proceed to step 5. If space is insufficient, you will need to work with your server team to add the additional required space.

5. Export the MESSAGELOB, MESSAGECOMPONENTLOB, and CONDITIONVIOLATIONLOB database tables to the data pump directory:

   expdp protect/<protect password> dumpfile=protect_messagelob.dmp logfile=protect_messagelob.log directory=DATA_PUMP_DIR tables='MESSAGELOB'

   expdp protect/<protect password> dumpfile=protect_messagecom.dmp logfile=protect_messagecom.log directory=DATA_PUMP_DIR tables='MESSAGECOMPONENTLOB'

   expdp protect/<protect password> dumpfile=protect_cvlob.dmp logfile=protect_cvlob.log directory=DATA_PUMP_DIR tables='CONDITIONVIOLATIONLOB'

6. Verify that the tables appear in the data pump directory:
          select DIRECTORY_NAME, DIRECTORY_PATH from dba_directories where DIRECTORY_NAME = 'DATA_PUMP_DIR';

7. Import the tables from the data pump directory as follows:

   impdp protect/<protect password> dumpfile=protect_messagelob.dmp logfile=protect_import_message.log directory=DATA_PUMP_DIR table_exists_action=REPLACE transform=LOB_STORAGE:SECUREFILE

   impdp protect/<protect password> dumpfile=protect_messagecom.dmp logfile=protect_import_messagecom.log directory=DATA_PUMP_DIR table_exists_action=REPLACE transform=LOB_STORAGE:SECUREFILE

   impdp protect/<protect password> dumpfile=protect_cvlob.dmp logfile=protect_import_cv.log directory=DATA_PUMP_DIR table_exists_action=REPLACE transform=LOB_STORAGE:SECUREFILE

8. Run the following query to verify that the tables are in SecureFiles LOB storage format: 
                     select table_name, securefile from user_lobs where table_name like '%LOB%';

9. The query returns yes in the securefile column to indicate that the tables are in SecureFiles LOB storage format.

NOTE: Make sure you are logged in as "protect" user (or your equivalent) as this is expected when running the select query.  The reason for this is the user_lobs view only reports lobs that are owned by the logged-in user.

Once completed:

1. Restart the Oracle Listener service on the Oracle server.

2. Restart all DLP services on your Enforce Server. The following links are to the Symantec Data Loss Prevention 15.5 help. Your service names may be slightly different. You can also refer to the topics "Starting and stopping services on Linux" and "About starting and stopping services on Windows" in the Symantec Data Loss Prevention Administration Guide appropriate to your version.

   • For Linux, see Starting an Enforce Server on Linux

   • For Windows, see Starting an Enforce Server on Windows

Solution #2

This solution applies to installations of Oracle Enterprise (Any version) and Oracle 11g Standard (11.2.0.4) databases and allows you to continue running your system during the conversion process.

NOTE: This solution cannot be applied to Oracle 12c and above Standard Edition databases. See "Solution #1" if you are on Oracle Standard.

Symantec provides a LOB space management script (DLP_lobspace_mgmt_b.pls) that converts BasicFiles Large Object (LOB) storage to SecureFiles LOB storage in your database when you run the database space reclamation utility (DLP_Lobspace_reclaim.sql).

Unlike BasicFiles LOB storage, SecureFiles LOB storage tracks deleted LOBs and makes that space available after the retention period expires. After converting to SecureFiles LOB storage, you do not need to run a script to reclaim LOB space in your database. Space reclamation is handled automatically.

Update the LOB space management script

Updating the LOB space management script requires that you update the DLP_lobspace_mgmt_b.pls and DLP_Lobspace_reclaim.sql files.

NOTE: The process to update your database to use SecureFiles for LOB storage temporarily requires roughly the same amount of space that the LOB tablespace currently consumes. For example, if your LOB_tablespace takes up 20 GB, you need an additional 20 GB of space in LOB_tablespace to successfully run the update. After you complete the process, the data used size returns to the previous size (approximately) and decreases as space reclamation automatically occurs. To add LOB_tablespace file to increase the amount available refer to article 159990.

Incidents continue to be written to the Enforce Server during the SecureFiles format conversion process. The process does not affect Enforce Server functions and there is minimal performance impact.

NOTE: For large databases (.5 TB or more) and for environments that have continuous incidents being added every minute, it would be best practice to stop the Incident Persister service while running the LOB_lobspace_reclaim.sql.  Otherwise the UNDO_RETENTION initialization parameter will need to be increased significantly as well as the size of the UNDO Tablespace

To update the files on Symantec Data Loss Prevention systems, follow these steps:

1. Obtain the latest LOB space management script by completing the following steps:
   1. Download LOB_Space_Management_Script-September2019.zip attached to the bottom of this KB article. 
   2. Move the file to a temporary location on your Enforce Server computer.
2. Navigate to where the  DLP_lobspace_mgmt_b.pls and dlp_lobspace_reclaim.sql files are located on the Enforce Server:
   • Version 15.0 and earlier:
     • Linux: /opt/SymantecDLP/Protect/install/sql
     • Windows: C:\SymantecDLP\Protect\install\sql
   • Version 15.1:
     • Linux: /opt/Symantec/DataLossPrevention/Enforce Server/15.1/Protect/install/sql
     • Windows: C:\Program Files\Symantec\Data Loss Prevention\Enforce Server\15.1\Protect\install\sql
   • Version 15.5 and later:
     • Linux: /opt/Symantec/DataLossPrevention/Enforce Server/<DLPVersion>/Protect/install/sql
     • Windows: C:\Program Files\Symantec\DataLossPrevention\EnforceServer\<DLPVersion>\Protect\install\sql
3. Rename the DLP_lobspace_mgmt_b.pls and DLP_Lobspace_reclaim.sql files.
4. Extract the new DLP_lobspace_mgmt_b.pls and DLP_Lobspace_reclaim.sql files from the LOB_Space_Management_Script-September2019.zip file to the same directory. Refer to step 2 for directory locations.

Convert the Oracle 11g/12c/19c Enterprise database to SecureFiles LOB storage

To use the database space reclamation utility to convert your Oracle BasicFiles LOB storage to SecureFiles LOB storage, follow this procedure:

1. Back up the Oracle database before making any changes.
2. Open a command prompt and navigate to the directory that contains the database space reclamation script. Refer to step 2 in "Update the LOB space management script" for the location.
3. Connect to sqlplus as the SYS user: sqlplus sys/[sysdba password] as sysdba.
4. Run the database space reclamation utility: @@DLP_Lobspace_reclaim.sql.
5. Run the following query to verify that the tables are in SecureFiles LOB storage format: 
   select table_name, securefile from user_lobs where table_name like '%LOB%';
   The query returns yes in the securefile column to indicate that the tables are in SecureFiles LOB storage format.  Here is an example of a successful conversion:

Additional Information

If you run the DLP_Lobspace_reclaim.sql on Data Loss Prevention 14.x or above, and you are using Oracle Standard Edition (12.1.x or newer), the script will fail with this message: "ERROR at line 1: ORA-00439: feature not enabled: Online Redefinition." You can refer to lobspace_reclamation.log for error information. Refer to "Solution #1" for steps to complete the conversion.

Note the DLP_lobspace_mgmt_s.pls script can be found in the C:\Program Files\Symantec\DataLossPrevention\EnforceServer\15.X.00000\Protect\install\sql folder on your Enforce server.