KEWROCISTMTEXEC - encountered error: (ORA-00001: unique constraint (SYS.WRH$_IC_DEVICE_STATS_PK) violated

AWR Snapshots Not Generated, ORA-1: unique constraint (SYS.WRH$_IC_DEVICE_STATS_PK) violated (Doc ID 960003.1)
Oracle Server - Enterprise Edition - Version: to
This problem can occur on any platform.


AWR snapshots are not generated.
MMON trace file shows the following error:-

Oracle Database 11g Enterprise Edition Release - Development
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /scratch/oracle/db_home
System name: Linux
Node name: stait09
Release: 2.6.9-
Version: #1 SMP Thu Oct 26 14:26:54 PDT 2006
Machine: i686
Instance name: RacStres_1
Redo thread mounted by this instance: 1
Oracle process number: 42
Unix process pid: 11334, image: oracle@stait09 (m000)
*** 2008-03-31 02:00:16.124
*** SESSION ID:(837.1158) 2008-03-31 02:00:16.124
*** CLIENT ID:() 2008-03-31 02:00:16.124
*** SERVICE NAME:(SYS$BACKGROUND) 2008-03-31 02:00:16.124
*** MODULE NAME:(MMON_SLAVE) 2008-03-31 02:00:16.124
*** ACTION NAME:(Remote-Flush Slave Action) 2008-03-31 02:00:16.124
*** KEWROCISTMTEXEC - encountered error: (ORA-1: unique constraint
*** SQLSTR: total-len=632, dump-len=240,
STR={insert into WRH$_IC_DEVICE_STATS (snap_id, dbid,
instance_number, if_name, ip_addr, net_mask, flags, mtu, bytes_received,
packets_received, receive_errors, receive_dropped, receive_buf_or,
receive_frame_err, bytes_s
ent, packets_s}
*** KEWRAFM1: Error=13509 encountered by kewrfteh
This issue occur when AWR gets snapshot, So AWR reports cannot be generated.


Similar Bug 7247999 encountered for the problem.
The bug is said to have fixed in 11.2 and the one-off patch could be requested for 11.1.


The patch could be requested and applied for the appropriate version.
Otherwise, just recreate the constraint as follows:
Rem Change the primary key for WRH$_IC_DEVICE_STATS
-- drop key to add new columns
alter table WRH$_IC_DEVICE_STATS
drop constraint WRH$_IC_DEVICE_STATS_PK;

-- create key using additional column
alter table WRH$_IC_DEVICE_STATS
add constraint WRH$_IC_DEVICE_STATS_PK
PRIMARY KEY (dbid, snap_id, instance_number, if_name, ip_addr)
using index tablespace SYSAUX;

Niciun comentariu:

Trimiteți un comentariu