Converting your LOB tables from BasicFiles to SecureFiles format in Symantec Data Loss Prevention 14.6 and 15.x:

This solution applies to Oracle 11g Standard (11.2.0.4), Oracle 11g Enterprise (11.2.0.4), and Oracle 12c Enterprise (12.1.x and 12.2.x) databases and allows you to continue running your system during the conversion process.

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.

Incidents continue to be written to the Enforce Server during the SecureFile 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 significatly 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 or Oracle 12c Enterprise database to SecureFiles LOB storage

To use the database space reclamation utility to convert your Oracle 11g 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.

Solution #2

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 must 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 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.
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 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 the estimates that the queries provide to confirm whether there is sufficient space on the database hard drive. If there is enough space, proceed to step 5.

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%';
   The query returns yes in the securefile column to indicate that the tables are in SecureFiles LOB storage format.
9. Restart the Oracle Listener service on the Oracle server.
10. 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.