Doc ID: Note:199416.1
Subject: ALERT: EXP Can Produce Dump File with Corrupted Data
Type: ALERT
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 13-JUN-2002
Last Revision Date: 29-JUL-2002


EXP Can Produce Dump File with Corrupted Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Versions Affected
~~~~~~~~~~~~~~~~~
Export Utility (EXP)
8.1.7.3
8.1.7.4
9.0.1.2
9.0.1.3

Versions below are NOT affected:
8.1.7.0
8.1.7.1
8.1.7.2
9.0.1.0
9.0.1.1
9.2.0.1

Platforms Affected
~~~~~~~~~~~~~~~~~~
GENERIC

Description
~~~~~~~~~~~
After the export (EXP) process, the produced dump file may contain
corrupted data. [BUG:2410612]

Example:
1. There exists table A with repetitive data where column values are the
same for successive rows
2. Create table B as a working copy of table A:
create table B as select * from A;
2. Export table B in conventional path
3. Rename table B to C
4. Import table B
5. Compare table B and C, e.g:
select * from B minus select * from C;
The above query should return no rows, but in the case of the export file
being affected by this bug then the query would show rows where the data
has been corrupted.

Note that import using the SHOW=Y option may report no problems with the
export file even though the data itself has been corrupted.

Likelihood of Occurrence
~~~~~~~~~~~~~~~~~~~~~~~~
The problem is related to the actual data exported and is most likely to
impact the export of repetitive data where column values are the same for
successive rows.

The tests show that this is not bound to a specific configuration or
option. For new issues please check the referenced bug.

Possible Symptoms
~~~~~~~~~~~~~~~~~
The problem causes incorrect data in the database when a dump is imported.
You can see incorrect data in the tables for repetitive column values
in successive rows.

The problem occurs silently. Do not expect an error like ORA-1578. In
order to find the corrupted rows, all of the imported rows must be compared
with the original ones.

Workaround
~~~~~~~~~~
Oracle recommends that the fix for this issue be applied where it is
available.

Where the fix cannot be used, it is advisable to use the following
workarounds when using export:

Workaround 1:
Use the DIRECT=Y option of export.

Workaround 2:
Before export set the environment variable ORA_OCI_NO_OPTIMIZED_FETCH to 1.
E.g.:
UNIX csh:
setenv ORA_OCI_NO_OPTIMIZED_FETCH 1
UNIX sh,ksh,bash:
ORA_OCI_NO_OPTIMIZED_FETCH=1; export ORA_OCI_NO_OPTIMIZED_FETCH
Windows:
set ORA_OCI_NO_OPTIMIZED_FETCH=1
OpenVMS:
define/job ORA_OCI_NO_OPTIMIZED_FETCH 1


Workaround 3:
Use an unaffected version (listed above) of the export utility over SQL*Net
with conventional path. e.g. export utility in an 8.1.7.0 installation
connecting to a 8.1.7.3 database, will not reproduce the problem.

Note: Oracle recommends that customers do not rely solely on EXP as a backup
mechanism - customers are advised to always implement a proper physical
backup strategy for databases.

Patches
~~~~~~~
Oracle recommends that the fix for this issue be applied where it is
available.

Patches for this bug can be found on MetaLink by following these steps:
1. Login to MetaLink - http://metalink.oracle.com
2. Choose Patches from the Menu.
3. Input 2410612 into the Patch Number field.
4. Click the Submit button.

References
~~~~~~~~~~
[BUG:2410612] CONVENTIONAL EXPORT HAS WRONG DATA ON IMPORT


Modification History
22-Jul-2002 Added OpenVMS workaround
30-Jul-2002 Patches are available.