devan05

CA DLP Tuesday Tip: Using Oracle Import and Export utilities.

Discussion created by devan05 Employee on Jun 12, 2012
Latest reply on Jun 12, 2012 by Chris_Hackett
CA DLP Tuesday Tip: Using Oracle Import and Export utilities. with CA (DLP) DataMinder posted by Andrew Devine Snr. support Engineer 12 June 2012.

Oracle 11g Release 2 (11.2) and earlier ships with import and export database utilities. The Export utility (exp) is used to write data from an Oracle database into an operating system file in binary format. This file is stored outside the database, and it can be read into another Oracle database using the Import utility.

Using these utilities in connection with CA (DLP) DataMinder has special considerations. Sepcifically, using the Import utility (imp) to move DLP data into a receptacle database where the objects were created by the native scripts, requires the following special actions;

Action #1: The native scripts create the database sequences WGN3SEQ and Wgn3CpID. These must be DROPped before the import is run, otherwise they will have the default Out Of The Box (OOTB) values instead of the source database values.

Action #2: The native scripts populate the table WGNID with OOTB values. These must be deleted before the import is run, otherwise the import will experience duplicate key errors and the IDs will have the OOTB values instead of the source database values.

Action #3: Ensure that all of the CA (DLP) DataMinder database users have all of their GRANTed roles enabled by default. The Oracle Export utility (exp) only sets all roles to be activated by default if the operation is performed in full database mode.

This action is to be performed after the import has completed and before any attempt is made to start the CA (DLP) DataMinder infrastructure. For each and every CA (DLP) DataMinder Oracle database user, the DBA must execute the following statement in SQL*Plus or similar:

ALTER USER <username> DEFAULT ROLE ALL;


For more information on Oracle Database utilities, please refer to the Oracle documentation.

Outcomes