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, 19 and newer).

Refer to "Solution #2" for Oracle Enterprise Edition (any version) 

 

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

1. For environments using Oracle Enterprise, reach out to your Oracle Database Administrators for steps on how to convert to SecureFile.