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

By | Oracle DB | No Comments

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.

NOTE: This solution cannot be applied to Oracle 12c Standard (12.1.x and 12.2.x) databases. See “Solution #2” if you are on Oracle 12c 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 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.

How to default to the local database when logging in from the command line

By | Oracle DB | No Comments

Set the ORACLE_SID via My Computer > Properties > Advanced > Environment Variables. Add a new variable (or edit existing) by clicking New and supplying a variable name of ORACLE_SID and a value of whatever the database (service) name is as it appears in the tnsnames.ora file. Click OK when you are done.

When you log in from the command line again, the new variable will be picked up and you should get something similar to the following:

C:\Documents and Settings\Administrator>sqlplus protect

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Feb 10 13:49:24 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

Connected to:Oracle Database 10g Release 10.2.0.4.0 - Production

SQL>

To connect to another database, provide the net service name as it appears in the tnsnames.ora file. Use the following example:

C:\Documents and Settings\Administrator>sqlplus protect@vontudbs

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Feb 10 13:49:24 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

Connected to:Oracle Database 10g Release 10.2.0.4.0 - Production

SQL>

How to Removing Oracle Database Client Software for symantec DLP

By | Oracle DB | No Comments

5.1 Stopping Oracle Services on Windows

You must first stop the Oracle Windows services before removing Oracle components or removing any registry entries.

See Also:

Your Microsoft online help for more information about stopping services

To stop Windows services:

  1. Open the Windows Services utility: From the Start menu, choose Programs, then Administrative Tools, then Services.
  2. If any Oracle services (names begin with Oracle or Ora) exist and have the status Started, then select each of the services, and click Stop.
  3. Stop the Microsoft Distributed Transaction Coordinator service.
  4. Exit Services.
  5. Restart the computer.

5.2 Removing Oracle Database Client with Oracle Universal Installer

You first use Oracle Universal Installer to remove Oracle Database Client from the inventory on the computer. Afterwards, you must manually remove the remaining components. This section covers the following topics:

  • Guidelines for Removing Oracle Database Client with Oracle Universal Installer
  • Procedure for Removing Oracle Database Client with Oracle Universal Installer

5.2.1 Guidelines for Removing Oracle Database Client with Oracle Universal Installer

Follow these guidelines:

  • Do not manually remove Oracle Database Client components without first deinstalling with Oracle Universal Installer. An exception is if, during an installation, you exit Oracle Universal Installer using any of the following methods:
    • Clicking Cancel
    • Turning off the computer
    • If the installation does not complete (that is, all required configuration tools do not run at the end)

    In these cases, Oracle Universal Installer does not register the installation in its inventory. However, it may have copied files to your Oracle home. Remove these files manually and restart the installation.

  • If you need to remove an Oracle home manually, first remove the Oracle components with Oracle Universal Installer. An example of removing the Oracle home manually would be by deleting the directory structure with Windows Explorer or the command prompt.

    You should not remove the Oracle home manually first because their components remain registered in the Oracle Universal Installer inventory. If you subsequently try to install Oracle in the same home, some or all of the components selected may not be installed, since Oracle Universal Installer will determine the components are already installed.

5.2.2 Procedure for Removing Oracle Database Client with Oracle Universal Installer

Oracle Universal Installer creates Windows services for Oracle components during installation. However, Oracle Universal Installer does not delete all the services created by Oracle Net Configuration Assistant.

To remove components on a Windows computer with Oracle Universal Installer:

  1. Ensure that you first follow the instructions in “Stopping Oracle Services on Windows”.
  2. Start Oracle Universal Installer. The start procedure depends on which version of Oracle Database Client you installed.
    1. If you installed the Administrator, Runtime, or Custom versions of Oracle Database Client, then Oracle Universal Installer was also installed. From the Start menu, choose Programs, then Oracle – HOME_NAME, then Oracle Installation Products, then Universal Installer. The Welcome window for Oracle Universal Installer appears.
    2. If you installed the Instant Client version of Oracle Database Client, Oracle Universal Installer was not installed. Instead, run it from your installation media or the installation directory you created for downloaded or copied installation files.

      To do so, insert Oracle Database installation media and navigate to the client directory. Alternatively, navigate to the directory where you downloaded or copied the installation files. Then double-click setup.exe to start Oracle Universal Installer. In the Welcome window, choose Install/Deinstall Products.

  3. Click the Deinstall Products button.

    The Inventory window appears.

  4. Select the Oracle home you wish to remove. Expand the tree of installed components only if you want to remove selected components of an Oracle home.

    For example, if you installed Oracle Database Client with the Runtime option and later installed additional components with the Custom option, then expand the Oracle home component to display all the components installed in the Oracle home.

  5. Check the boxes of components to remove.
  6. Click Remove.

    The Confirmation window appears.

  7. Click Yes to remove the selected components.

    Note:

    A message may appear indicating that removing some components may cause other components to not function properly.

    After the components are removed from your computer, the Inventory window appears without the removed components.

  8. Click Close to close the Inventory window.
  9. Click Cancel to exit Oracle Universal Installer.
  10. Click Yes to confirm that you want to exit.
  11. After Oracle Universal Installer exits, go to the next section to remove the remaining Oracle Database Client components.
  12. Then restart the computer.

5.3 Manually Removing the Remaining Oracle Database Client Components

Oracle Universal Installer does not remove all Oracle components. After using Oracle Universal Installer to remove Oracle components, you need to manually remove remaining registry keys, environment variables, Start menu options, and directories.

This section contains these topics:

  • Updating the PATH Environment Variable Path
  • Removing Oracle Database Client from the Start Menu
  • Removing Oracle Database Client Directories

Note:

In rare situations, you might want to correct serious system problems by completely removing Oracle components manually from the computer without first deinstalling with Oracle Universal Installer. Do this only as a last resort, and only if you want to remove all Oracle components from your system.

5.3.1 Updating the PATH Environment Variable Path

Check the PATH environment variable and remove any Oracle entries.

  1. Display System in the Control Panel.
  2. Select the Advanced tab and then click Environment Variables.
  3. Select the system variable PATH and edit it to remove any Oracle entries.

    For example, remove Oracle entries that contain ORACLE_BASE\ORACLE_HOME in the PATH variable. You may see a PATH variable that contains entries similar to the following:

    ORACLE_BASE\ORACLE_HOME\bin;ORACLE_BASE\ORACLE_HOME\jre\1.4.2\bin\client;
    ORACLE_BASE\ORACLE_HOME\jre\1.4.2\bin
    
  4. Save any changes and exit System.

5.3.2 Removing Oracle Database Client from the Start Menu

Check the Start menu for any Oracle Database Client entries and remove them.

Follow these steps:

  1. Select Start, then Programs, then Oracle – HOME_NAME.
  2. Right-click Oracle – HOME_NAME, and from the menu, select Delete.

You can also remove Oracle Database Client menu entries by using the following method:

  1. Right click the Start button to display the pop-up menu.
  2. Select the Explore All Users option.
  3. Under Documents and Settings, expand the \Start Menu\Programs folder.
  4. Right-click and delete the Oracle – HOME_NAME folder.

5.3.3 Removing Oracle Database Client Directories

After removing all Oracle Database Client registry keys and restarting the computer, delete any existing Oracle Database Client directories and files.

  1. Using My Computer or Windows Explorer, delete the SYSTEM_DRIVE:\program files\oracle directory.
  2. Using My Computer or Windows Explorer, delete all ORACLE_BASE directories on your hard drive.

How to Extend Oracle tablespace (LOB_TABLESPACE, USERS, etc.) when almost full

By | Oracle DB | No Comments

Caution: Pay close attention to which tablespace names the logs or alerts identify for extension. LOB_TABLESPACE is usually affected, but it can be USERS or other tablespaces. All tablespaces are extended the same way; substitute the appropriate tablespace name.

Tablespace summary

In DLP 14.x and later, you can view the “Tablespace Summary” in the DLP Enforce console.

Note: This summary does not fully describe the tablespace, such as the location of the database files.

Locate the Oracle database files

Before you can extend the tablespace, you need to know where the current Oracle database files are located.

Use the following command:

select file_name from sys.dba_data_files;

The resulting output is similar to the following, which indicates each database file’s directory and file name (.DBF extension):

E:\ORACLE\ORADATA\PROTECT\SYSTEM01.DBF
E:\ORACLE\ORADATA\PROTECT\SYSAUX01.DBF
E:\ORACLE\ORADATA\PROTECT\UNDOTBS.DBF
E:\ORACLE\ORADATA\PROTECT\DRSYS01.DBF
E:\ORACLE\ORADATA\PROTECT\LOB01.DBF
E:\ORACLE\ORADATA\PROTECT\LOB02.DBF
E:\ORACLE\ORADATA\PROTECT\LOB03.DBF
E:\ORACLE\ORADATA\PROTECT\USERS01.DBF
E:\ORACLE\ORADATA\PROTECT\USERS02.DBF
E:\ORACLE\ORADATA\PROTECT\USERS03.DBF

Note: You can also use Oracle Enterprise Manager to locate the database files.

Extend the tablespace

To add tablespace through SQL, log inconnecting to the database using sys as sysdbaand enter the following:

ALTER TABLESPACE <tablespace name>
ADD 
DATAFILE '<directory and datafile name>' SIZE 138240K
REUSE AUTOEXTEND 
ON NEXT 10240K MAXSIZE 32767M; 

Based on the sample output under “Locate the Oracle database files”, if it is “LOB_TABLESPACE” which is full, the newly created database file is named “LOB04.DBF”.

Here is a specific example that adds a new database file to the “LOB_TABLESPACE”:

ALTER TABLESPACE LOB_TABLESPACE
ADD 
DATAFILE 'D:\ORACLE\ORADATA\PROTECT\LOB04.DBF' SIZE 138240K 
REUSE AUTOEXTEND 
ON NEXT 10240K MAXSIZE 32767M;

Additional information

  • On some systems using Oracle 11g, you may need to omit the quotes surrounding the tablespace name (e.g. ALTER TABLESPACE LOB_TABLESPACE ADD DATAFILE…).
  • You can gain tablespace by deleting incidents, as Oracle overwrites the deleted data when new incidents come in. However, deleting incidents will not work if your tablespace is already full. For more information

How to change the “protect” user password in the Oracle database for Symantec DLP

By | Oracle DB | No Comments
  • DLP connects to the Oracle database using a user named “protect”.
  • The Oracle protect password is stored in an encrypted file named DatabasePassword.properties located on the Enforce server
  • The DBPasswordChanger utility is used to change the Oracle database password in that file.
  • In DLP versions 15.0 and earlier, the DBPasswordChanger is located in \SymantecDLP\Protect\bin
  • In DLP versions 15.1 and later it is located at \Program Files\Symantec\DataLossPrevention\EnforceServer\15.5\Protect\bin (Windows), or /opt/SymantecDLP/Protect/bin (Linux).

 

NOTE:

To avoid an account lock-out, run the DBPasswordChanger utility as soon as possible after the Oracle Data Loss Prevention account password is changed. If a lock-out does occur, see the article: “ORA-28000: the account is locked” for resolution.

Example:

  • DLP Administrator password is rhubarb
  • New Oracle protect user password is potato

 

Process Overview:

  1. Shutdown all DLP services. (see Windows, see Linux)
  2. Change the database password within Oracle.
  3. Verify the new password.
  4. Change the password on the Enforce server.
  5. Start the DLP services.
  6. Log in to the Enforce UI.

 

Detailed steps for 2-4 above:

Changing the database password for the protect account on Oracle:

IMPORTANT: Be sure to follow the guidelines for acceptable passwords in the article: Password guidelines for the Oracle ‘protect’ user

– Start a sqlplus session:
sqlplus /nolog

 

– Login as sysdba:
SQL> connect sys as sysdba
(Enter the password when prompted.)

 

– Change the protect password to potato:
SQL> alter user protect identified by potato;
– Verify the password change:
SQL> conn protect/potato

– Exit sqlplus:
SQL> exit 

Changeing the password for the protect account used by the Enforce server:

NOTE: The examples assume a Windows installation; for Linux, substitute the appropriate paths (e.g. /opt/Vontu/Protect/bin)

– Start a command shell and change to the bin directory:
cd \SymantecDLP\Protect\bin

– Change the Oracle password in the configuration file:

For version 15.0 and earlier:

The syntax for DBPasswordChanger is:

DBPasswordChanger <PasswordFilePath> <New Oracle Password>

So:

DBPasswordChanger c:\SymantecDLP\protect\config\DatabasePassword.properties potato

 

For version 15.1 and later:

The syntax for DBPasswordChanger is:

DBPasswordChanger <PasswordFilePath> <New Oracle Password>

So:

DBPasswordChanger “C:\Program Files\Symantec\DataLossPrevention\EnforceServer\15.5\Protect\config\DatabasePassword.properties” potato

Error: “ORA-28000: the account is locked” in Symantec DLP Enforce

By | Oracle DB | No Comments

There are two ways to unlock the Oracle database account:

  • From the Oracle Enterprise Manager
  • From the command line using SQL*Plus

Unlock using Oracle Enterprise Manager

  1. From the Oracle Enterprise Manager, select Network > Databases > Security > Users.
  2. Edit the protect user, then select the unlocked radio button.

Unlock from the command line using SQL*Plus

  1. Load SQL*Plus.
  2. Check what is locked and what is not locked with the following command:
    select username,account_status from dba_users;

    Note: Remember to add the semicolon or the command will not execute.

  3. To unlock the [username] (without brackets) account, enter the following command:
    alter user [username] account unlock;
  4. Rerun step 2 to verify success.