Resolving ORA-02299 Error: cannot validate (x) - duplicate keys found (KBA4524)

At a Glance

Description:	This KBA describes masking an Oracle table and how to resolve ORA-02299 Error: cannot validate (x) - duplicate keys found.
Affects versions:	This issue is a database issue and affects all Masking Versions. In version 6.0.11 the Masking Connector for Oracle was updated with Driver Plugin SDK.
ORA Error:	ORA-02299: cannot validate (x) - duplicate keys found
	This issue can appear in different steps depending on the version and if manually created. From 6.0.11 the error shows up in the Driver Plugin: DRIVER_SUPPORT_TASK_FAILURE All versions up to and including 6.0.10 Execute_Enable_Constraints_Script.0 If using manual Post SQL scripts: Execute_Postscript.0
Root Cause:	A rule which is defined in the database has been violated when a Unique Constraint was re-enabled due to duplicated value(s) in the column. This is Oracle's way to say 'Stop! I cannot proceed or I break rules that the database owner has defined'.
The Rule:	There is one rule to a Unique Constraint: A Unique Constraint ensures that all values in a column are unique - are different. Note: On Oracle, a Unique Constraint accepts multiple NULLs.
The issue:	There are two possible issues here: A Masked Value is not Unique (the masked values have duplicates). The original values might not be unique (constraint applied using option 'novalidate').
Solution:	To resolve this issue: Verify that the original values are unique. Ensure the Algorithm generates a UNIQUE masked value that is mapped 1:1. Notes: Examples of Engine built-in algorithms are Character Mapping, Segment Mapping, Mapping algorithms. For more details, see Algorithm Summary - KBA4066 (link below).
Related KBAs:	This issue relates to: KBA1817 - Resolving ORA-00001 Unique Constraint Violated KBA8758 - Resolving ORA-01452 cannot CREATE UNIQUE INDEX

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies

Major Release	All Sub Releases
6.0	6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0, 6.0.10.0, 6.0.10.1, 6.0.11.0, 6.0.12.0, 6.0.12.1
5.3	5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0, 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0
5.2	5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1
5.1	5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0
5.0	5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1, 5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

ORA-02299: cannot validate (x) - duplicate keys found

This article provides the root cause and a solution to ORA-02299 (cannot validate (x) - duplicate keys found) from a masking perspective. The error ORA-02299 is common in masking but is also easy to understand and resolve.

What is a Unique Constraint and why do we get this error?

What is a Unique Constraint?

A Unique Constraint (Oracle) is:

A Unique Constraint ensures that all values in a column are unique - are different.

Notes:

On Oracle, a Unique Constraint can accept multiple NULLs.
A Unique Constraint is in many ways the same as a Primary Key Constraint. There is this one major difference a table can have multiple Unique Constraints while it can only have one Primary Key.

What caused this error?

There is one possible cause of this error:

The Masked Values are not Unique - there are duplicated values.

Example

To visualize this, consult the following example. To show why this error happens, the example shows the Pre Masking Steps, the Masking Operation, and the Post Masking Steps. The error happens in the Post Masking Steps and is caused by duplicates in Masked Values.

The example is created using Secure Lookup (SL) with 25 values (the allocation of SL is Random and the mapping is N:1 which means multiple input values can be masked to the same masked value).

Steps and explanation

The error happens after all data has been masked and the Post Masking Steps start.

Pre Masking Steps:
- Disable (Unique) Constraint.
Masking operation:
- Unique input values are masked and then updated to the following values (15, 2, 32, 21, 2, 4, 9, 16).
  - Note that 2 appears twice (This is the issue, the Algorithm has caused duplicated value).
Post Masking Steps:
- Enable (Unique) Constraint - This operation fails.

Root causes - explanation:

The algorithm is not masking to a unique value and the masked value '2' is duplicated.
When the Masking Job finishes, the Post Masking operation fails with:
- 'ORA-02299: cannot validate (x) - duplicate keys found'

Example - Cannot Create Unique Index

# Pre Masking Steps
---------------------------------------------------------------------------
ALTER TABLE ... DISABLE CONSTRAINT

# Masking Operation
- Viewing masked data
---------------------------------------------------------------------------
+------+---------+
| Orig | Mask   |
+------+---------+
| 0 | 15 |
| 1 | 2*   | < First duplicate
| 2 | 32 |
| 3 | 21 |
| 4 | 2*   | < Second duplicated
| 5 | 4 |
| 6 | 9    |
| 7 | 16 |
| C O M M I T   |
+------+---------+

# Post Masking Steps
---------------------------------------------------------------------------
Execute_Enable_Constraints_Script.0
ALTER TABLE ... < Error:
ORA-02299: cannot validate (x) - duplicate keys found

Error message

The error message is:

ORA-02299: cannot validate (x) - duplicate keys found

Error in the logs

The error message can differ. It is usually in the step:

Execute_Enable_Constraints_Script.0

Full example from job log (and info.log):

[JOB_ID_xx_yy] 0000/00/00 00:00:00 - Execute_Enable_Constraints_Script.0 - ORA-02299: cannot validate (x) - duplicate keys found

If the ALTER TABLE ... ENABLE CONSTRAINT was executed in the Post SQL Script, then the error is in step:

Execute_Postscript.0

Solution

How to resolve ORA-02299?

Ensure the table has Unique values before the job starts.
Define an Algorithm that is Unique and maps 1:1 (for example Mapping or Segment Mapping).
Assign the Algorithm to the Column in the Inventory.

Note - Point 1:

It is possible that the column has duplicates even if there is a Unique Constraint on the column. If the constraint was created using 'novalidate', then the Constraint needs to be Disabled and Re-Enabled manually (with the 'novalidate' option).

KBA