Hourly AWR Snapshots Generates 'ORA-00001: unique constraint (SYS.WRH$_OPTIMIZER_ENV_PK)' After Applying Jan 2017 Bundle Patch (12.1.0.2.170117DBBP) (Doc ID 2285382.1)

Last updated on JULY 01, 2021

Applies to:

Symptoms

• A new trace file is created every hour with ORA-0001 error:
  
  Trace file /u01/app/ora12102/diag/rdbms/ora12102/ora12102/trace/ora12102_mmon_7039.trc
  Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
  ORACLE_HOME = /u01/app/ora12102/product/12.1.0/dbhome_1
  System name: Linux
  Node name: myTestServer
  Release: 3.10.0-327.28.3.el7.x86_64
  Version: #1 SMP Thu Aug 18 09:36:48 PDT 2016
  Machine: x86_64
  Instance name: ora12102
  Redo thread mounted by this instance: 1
  Oracle process number: 21
  Unix process pid: 7039, image: oracle@aparkman-lnx (MMON)

  *** 2017-05-16 14:00:02.702
  *** SESSION ID:(1104.37204) 2017-05-16 14:00:02.702
  *** CLIENT ID:() 2017-05-16 14:00:02.702
  *** SERVICE NAME:(SYS$BACKGROUND) 2017-05-16 14:00:02.702
  *** MODULE NAME:(MMON_SLAVE) 2017-05-16 14:00:02.702
  *** CLIENT DRIVER:() 2017-05-16 14:00:02.702
  *** ACTION NAME:(Remote-Flush Slave Action) 2017-05-16 14:00:02.702

  *** KEWROCISTMTEXEC - encountered error: (ORA-00001: unique constraint (SYS.WRH$_OPTIMIZER_ENV_PK) violated
  )
  *** SQLSTR: total-len=294, dump-len=294,
  STR={INSERT INTO wrh$_optimizer_env (dbid, con_dbid, snap_id, optimizer_env_hash_value, optimizer_env) SELECT :dbid, :srcdbid con_dbid, :lah_snap_
  id, ope.optenvhv_kewroee, ope.optenv_kewroee FROM x$kewrattrnew atn, x$kewrtoptenv ope WHERE atn.num1_kewrattr = ope.optenvhv_kewroee }
  *** KEWRAFM1: Error=13509 encountered by kewrfteh

    
  
  
• Oracle 12.1.0.2 Database that is patched to the Jan 2017 DB Bundle Patch level (12.1.0.2.170117DBBP):
  
  $ ./opatch lspatches
  25371030;
  25101514;OCW Interim patch for 25101514
  _RED_24732088;DATABASE BUNDLE PATCH: 12.1.0.2.170117 (24732088)_EC_
  23177536;Database PSU 12.1.0.2.160719, Oracle JavaVM Component (JUL2016)
  21923026;

  OPatch succeeded.

    
  
  
• The trace file can be reproduced when a manual Automated Workload Repository (AWR) snapshot is created:
  
  SQL> BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); END; /
    
  

Changes

The errors have started since applying the Jan 2017 DB Bundle Patch (12.1.0.2.170117DBBP):

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.