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:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterOracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |