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

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

This document is based on reoccurring Error of statspack which we encounter once in a while in our databases.
Even though the described below is an oracle known bug but there are some workaround and solutions to this.
Below given is the sample of error which reflects in 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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.