ORA-00001: unique constraint (PERFSTAT.STATS$MUTEX_SLEEP_PK) violated

Error:

This document discusses a recurring statspack error that we occasionally face in our databases. Although it is a known Oracle bug, there are workarounds and solutions. Here is a sample of the error from the alert log file:
ORA-12012: error on auto-execute of job 569336
ORA-00001: unique constraint (PERFSTAT.STATS$MUTEX_SLEEP_PK) violated
ORA-06512: at “PERFSTAT.STATSPACK”, line 5661
ORA-06512: at “PERFSTAT.STATSPACK”, line 105
ORA-06512: at line 1
Job_name= SP_SNAPSHOT
Owner=PERFSTAT

In this case unique constraint is violated because the job try to take a snapshot of same number which is already been taken or sometime it receives two
same id number to process the snapshot which again violates the unique constraint.
NOTE: This is an oracle known bug.

Workaround or Solution

1. Disable the constraint which is avoiding the job to create a snapshot

ALTER TABLEperfstat.stats$mutex_sleep disable CONSTRAINTSTATS$MUTEX_SLEEP_PK;

2. Then create an index on this.

CREATE INDEXperfstat.STATS$MUTEX_SLEEP_PK ON STATS$MUTEX_SLEEP(SNAP_ID,DBID,INSTANCE_NUMBER,MUTEX_TYPE,LOCATION);

After executing these two steps take a statspack snapshot just to make sure if the problem has been sorted out.
If the the on demand snapshot completes successfully then it means the problem is sorted out
NOTE: constantly monitor alert log file while doing all these steps.

3. For taken the snapshot on demand execute the following statement:

Exec statspack.snap;

4. For more verify take two snapshot on demand after 10 min gap and fetch out the spreport.sql

@?\rdbms\admin\spreport.sql;