Wednesday 28 May 2014

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


ORA-12012: error on auto execute of job 21
ORA-00001: unique constraint (PERFSTAT.STATS$MUTEX_SLEEP_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 5264
ORA-06512: at "PERFSTAT.STATSPACK", line 104
ORA-06512: at line 1


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.

View Metalink Note 382993.1 to get the low-down on this one, but the workaround is an easy implementation-

###### workaround or solution#########
Disable the constraint which is avoiding the job to create a snapshot

1)ALTER TABLE perfstat.stats$mutex_sleep disable CONSTRAINT STATS$MUTEX_SLEEP_PK;

Then create an index on this:-

2)CREATE INDEX perfstat.STATS$MUTEX_SLEEP_PK ON STATS$MUTEX_SLEEP(SNAP_ID,DBID,INSTANCE_NUMBER,MUTEX_TYPE,LOCATION);
after executing these two steps take an on demand 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) Exec statspack.snap;
 

Monday 26 May 2014

DROP DATABASE in Oracle


Since 10g, It is possible to drop a database in Oracle 10g with the new SQL statement drop database.
The command gets rid of datafiles online redo log files, controlfiles and spfile.