Thursday Apr 28, 2016

Incremental Statistics Collection in Oracle - A True Story

Recently I came across a really interesting customer case in the UK dealing with Incremental Statistics Collection issues in regards to an upgrade to Oracle Database

This is the follow-up blog post to: 

A while back I blogged already about Incremental Statistics collection in Oracle Database 

And you'll find more information in our documentation and in posts by our optimizer folks:  

The Basics

The idea of Incremental Statistics Collection is simply to save time and resources when gathering statistics for partitioned tables in order to update the global stats. In Oracle Database 12c we added the very important features of: 

  • Incremental stats working now with partition exchange as well
  • "Changed" partitions won't be eligible for new stats generation until a certain stale percentage (default: 10%) has been reached - this has to be enabled and can be tweaked

Furthermore we always recommend to:  

  • Not enable incremental stats collection globally but only for specific tables. Otherwise the footprint for the synopsis on disk can grow fairly large. Biggest footprint I've seen so far was almost 1TB in size in the SYSAUX tablespace
  • Enable it mostly for range-partitioned tables where only a few partitions undergo DML changes 

The Case

Actually the synopsis table in this particular case did contain "only" 300GB of data. But as the starting point was already Oracle Database just a change from Range-Hash to List-Hash Partitioning would happen. As this happens via metadata swapping the impact should be small.

But the issue coming up after the upgrade didn't have to do with this change in partitioning layout.

Issue No.1

During the maintenance window the incremental stats job did not finish and actually this statement caused plenty of trouble:

delete from sys.wri$_optstat_synopsis$ where bo# = :tobjn and group# in (select * from table(:groups)

Not completing this statement within the 4 hours of the default maintenance window led to a rollback of the delete - and its rollback alone took 14 hours. It turned out that the delete has to happen (and complete) before the regathering of stats could start. 

I did recommend:
patch 21498770: AUTOMATIC INCREMENTAL STATISTICS JOB TAKING MORE TIME ON  (see also MOS Note:2041541.1 - Gather_Database_Stats_Job_Proc Taking More Time in Than

and the customer requested:
Patch 22893653: MERGE REQUEST ON TOP OF DATABASE PSU FOR BUGS 19450139 20807398
on top of their January 2016 PSU - the merge included the patch I mentioned.

Besides that another issue got discovered.

Issue No.2

The daily purge of statistics didn't really work on large synopsis as the default degree of parallelism introduced with Oracle 12c gets derived from the number of blocks on the synopsis table - bigger table means a higher parallel degree for the purge. It ended up with a PARALLEL hint of 60 - and that was counterproductive. Once a purge got started manually in serial mode or with a low DOP it completed in less than 1 minute.

With a similar trace as:

set serveroutput on;
EXEC dbms_output.enable(999999999);
EXEC dbms_stats.set_global_prefs('trace',1+4);
EXEC dbms_stats.gather_table_stats(ownname=>'&TABLE_OWNER',tabname=>'&TABLE_NAME');
EXEC dbms_stats.set_global_prefs('trace',0);

the issues could be identified as: 


The customer requested another merge patch 22926433 which contains the following fixes:

20807398: ORA-00600 [KGL-HASH-COLLISION] WITH FIX TO BUG 20465582

Finally the customer agreed with Support's recommendation to truncate the two synopsis tables, WRI$_OPTSTAT_SYNOPSIS_HEAD$ andWRI$_OPTSTAT_SYNOPSIS$, and regathered incremental statistics the following weekend. Of course they validated this action plan on their performance testing environment first - with the merge patch applied - and it had the desired effect and solved the issue.

Incremental statistic gathering works now as expected, the job fits into the maintenance window.

Lessons Learned

Actually Oracle Support released a very helpful and important note just a few weeks ago (too late for this customer): 

It contains not only links to the patches for the issues the customer hit here - but also a long list for Oracle as well. 

Another MOS Note is worth to mention here: 

But these were not all issues the customer faced - so I may write up another blog post in addition within the next days.


PS. All credits go to David Butler and Rob Dawley - thanks for your hard work, sorry for all the inconvenience - and especially thanks for writing it all together and forwarding it to me!!!

Wednesday Apr 27, 2016

Incremental Statistics Collection in Oracle - Upgrade Pitfalls

A while back I blogged already about Incremental Statistics collection in Oracle Database 

And you'll find more information in our documentation and in posts by our optimizer folks:  

And  you may read on this follow-up blog post about a related real world customer example ...   .

Database Upgrade

Important to know is the fact that during a database upgrade the underlying tables containing the synopsis for incremental stats collection may be reorganized. And depending on the amount of data this can take a bit.

The largest synopsis tables I have seen so far were almost 1TB of size at a financial customer in Europe. But I have seen ranges around 300GB quite often in the past months.

What happens during the database upgrade? 

Incremental Statistics Collection got introduced with Oracle Database and improved from release to release. But during a database upgrade a reorganization of the synopsis table can happen.

  • Upgrade from Oracle or to Oracle
    • Restructuring of WRI$_OPSTAT_SYNOPSIS$ to use range-hash partitioning 
    • Most data movement will happen here
    • As for the interim period two synopsis tables exist this will consume 2x the space of the synopsis table during the movement

  • Upgrade from Oracle to Oracle 12.1.0.x:
    • Restructuring of WRI$_OPSTAT_SYNOPSIS$ from range-hash partitioning to list-hash partitioning
    • There is little data movement in this case as the move happens with the help of metadata swapping

Which symptoms may you see?

Actually very simple and obvious symptoms:
Phase 1 of the parallel upgrade to Oracle Database 12c takes unusually long. It should usually complete within the range of less than a few minutes. But in those cases it can take literally hours.

If that happens check your catupgrd0.log and watch out for the long running statements. It does not mean necessarily that it happens because of a huge synopsis table. For instance one of my German reference customers, DVAG had leftovers in the SYSAUX because of bugs in earlier releases they had worked with. 

But if you spot such results (quoting a colleague here):

"The table WRI$_OPTSTAT_SYNOPSIS$ has 20420 partitions, 344618 subpartitions and 921207 MB size. [..] This transformation step lasts for 6,5 hours, so the whole upgrade process duration has an important impact from this step." 

then you should be alerted. 

How can you check this upfront? 

We haven't included a check into the preupgrd.sql yet. But the following three queries will tell you if you may see issues when you get a larger number as result: 

  • How many tables have incremental stats on?
    SQL> select count(distinct bo#) from sys.wri$_optstat_synopsis_head$;
  • How many partitions does your WRI$_OPSTATS_SYNOPSIS$ have?
    SQL> select partition_count from dba_part_tables where table_name='WRI$_OPTSTAT_SYNOPSIS$';
  • How large is your synopsis table?
    SQL> select sum(bytes/(1024*1024)) "MB" from dba_segments where segment_name='WRI$_OPTSTAT_SYNOPSIS$';
  • Tables where inc stats are ON?
    SQL> select "OWNER" , "TABLE_NAME" ,p.valchar
    from  sys.OPTSTAT_USER_PREFS$ p
    inner join sys.obj$ o on p.obj#=o.obj#
    inner join sys.user$ u on o.owner#=u.user#
    where p.PNAME = 'INCREMENTAL';

  • Synopsis for tables which don't exist anymore?
    SQL> select distinct from sys.wri$_optstat_synopsis_head$ h where not exists (select 1 from$ t where t.obj# =;

Especially a large number of tables being monitored and a size of tens and hundreds of GBs will indicate that you may have to calculate for a longer upgrade duration.

How do you cure this?

Support sometimes gives the recommendation to look for MOS Note: 1055547.1 - SYSAUX Grows Because Optimizer Stats History is Not Purged and asks for a manual purge of stats, for instance:

for i in reverse 1..31
end loop;

But this won't clean up the synopsis tables but only stats history for object statistics. And it may create some noise in your UNDO. So in any case you may better set your stats retention policy to something such as 10 days instead of the default of 31 days instead generally.

First of all you have to make sure that this patch got applied to your target already before upgrade - it will add parallel index capabilities which will speed up the rebuild a lot: 

Be aware:
Truncating WRI$_OPTSTATS_SYNOPSIS$ and WRI$_OPTSTAT_SYNOPSIS_HEAD$ is strictly not recommended. If you plan to do it the hard way please check back with Oracle Support for their approval first.

Further Information?

Please read on here about a real world customer example ... 



Tuesday Apr 19, 2016

RMAN Catalog Upgrade fails - ORA-02296 - error creating modify_ts_pdbinc_key_not_null

This issue got raised to my via a customer I know for quite a while - all credits go to Andy Kielhorn for digging down into that issue and solving it. 

Failed RMAN Catalog Upgrade from to

The RMAN catalog upgrade:

SQL> @?/rdbms/admin/dbmsrmansys.sql

$ rman CATALOG rman/xxx@rman01



failed with the following sequence of error messages: 

error creating modify_ts_pdbinc_key_not_null
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-02296: cannot enable (RMAN.) - null values found

error creating modify_tsatt_pdbinc_key_not_null
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-02296: cannot enable (RMAN.) - null values found

error creating modify_df_pdbinc_key_not_null
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-02296: cannot enable (RMAN.) - null values found

error creating modify_tf_pdb_key_not_null
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-02296: cannot enable (RMAN.) - null values found

error creating modify_bs_pdb_key_not_null
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-02296: cannot enable (RMAN.) - null values found

Andy found also these bugs in MOS - but no helpful information included:

  • Bug 20861957
  • Bug 19677999

The  Solution

There seems to be a potential inconsistency in the RMAN catalog when the PDB/CDB mechanisms get introduced. This does not necessarily happen - but it can happen.

The workaround is described in:


==> Connect to catalog schema and clear the table having null details 

delete bdf where not exists (select 1 from dbinc where dbinc.dbinc_key = bdf.dbinc_key);
delete bcf where not exists (select 1 from dbinc where dbinc.dbinc_key = bcf.dbinc_key);

==> After clearing the offending rows , upgrade catalog worked

But please use this workaround only under Oracle Support's supervision. I did document it here to ease your verification.

Andy fixed it with:

update <rmancat_owner>.dbinc set PARENT_DBINC_KEY=NULL where (DBINC_KEY) IN (SELECT DBINC_KEY  from  <rmancat_owner>..ts where pdbinc_key is null); 

but please open an SR and point Oracle Support to the bug and the potential workarounds in case you hit the issue.


Tuesday Mar 29, 2016

Disable Transparent Hugepages on SLES11, RHEL6, RHEL7, OL6, OL7 and UEK2 Kernels

This blog post is not related to database upgrades and migrations. But still I think it is very useful for many customers operating on modern Linux systems.


Support just published an ALERT strongly recommending to disable Transparent Hugepages on Linux systems. And the below information does not apply to RAC systems only but also to single instance environments.

Which Linux Distrubutions/Kernels are affected? 

  • SLES11
  • RHEL6 and RHEL7
  • OL6 and OL7
  • UEK2 Kernels

What are the Issues? 

I'm quoting MOS Note: 1557478.1 (ALERT: Disable Transparent HugePages on SLES11, RHEL6, RHEL7, OL6, OL7 and UEK2 Kernels):

Because Transparent HugePages are known to cause unexpected node reboots and performance problems with RAC, Oracle strongly advises to disable the use of Transparent HugePages. In addition, Transparent Hugepages may cause problems even in a single-instance database environment with unexpected performance problems or delays. As such, Oracle recommends disabling Transparent HugePages on all Database servers running Oracle.

This ONLY applies to the new feature Transparent HugePages, Oracle highly recommends the use of standard HugePages that were recommended for previous releases of Linux.  See MOS Note:361323.1 for additional information on HugePages. 

As far as I see you'll have to reboot the server in order to disable Transparent Hugepages - the default is usually ALWAYS.

More Information?


Tuesday Mar 22, 2016

GC Freelist Session Waits causing slowness and hangs

Best Practice Hint

One of the best things in my job:
I learn from you folks out there. Everyday. 

Credits here go to Maciej Tokar who did explain the below topic to me via LinkedIn - thanks a lot, Maciej! 

Locks are not being closed fast enough, resulting in gc freelist waits

You can find a reference for Global Cache Freelist in the Oracle Documentation. This issue here can or will lead to database being slow, up to complete hangs. Based on my research it looks as the issue is not related to RAC only but a general thing. In your session waits you'll spot this:

Event                               Event Class        % Event   Sessions
----------------------------------- --------------- ---------- ----------
gc freelist                         Cluster              41.37       8.61

This has been logged as a bugs 21352465 (public) and 18228629 (not public). It causes locks are not being closed fast enough, resulting in gc freelist waits. In conjunction the default for _gc_element_percent seemed to be too low at 120 (or 110 in

Actually the issue can affect not only Oracle Database but also Oracle Database and

See MOS Note:2055409.1 (Database Hangs with High "gc freelist" wait ) for further details.


  • Apply the patch for bug 18228629 on top of a PSU or BP where available
    • See the drop-down list to the right labeled "Release" to access the correct patch for your release
    • Unlike the above MOS Note states in Oracle Database it is only available on top of the January 2016 PSU and BP and two other Exadata BPs - and on Linux only!
  • Use the workaround and set _gc_element_percent = 200
    • This will require an instance restart as the parameter can't be changed dynamically:
      alter system set "_gc_element_percent"=200 scope=spfile;


We've had a lot of discussions about underscore parameter in the past weeks. And I'm not a big fan of underscores especially when it comes to upgrades as experiences has shown that having underscores set one day may make it hard to remove them the other day - and underscores can significantly impact the upgrade duration in a negative way.

But on the other hand, if an issue is seriously affecting many customers, and there's no patch available for your platform and environment right now, what else can one do?



Thursday Jan 14, 2016

VBox 5.0.10/12 issues with PERL and Seg Faults - UPDATE

A bit more than two months ago I did hear from several people having issues with our Hands-On Lab environment. And it became clear that only those who use Oracle Virtual Box 5 see such errors. 

Then I read Danny Bryant's blog post (thanks to Deiby Gomez for pointing me to it) about similar issues and a potential solution yesterday:

And interestingly one of my colleagues, our PL/SQL product manager Bryn Llewellyn started an email thread and a test internally yesterday as well. The issue seem to occur only on newer versions of Apple's MacBooks.

Potential Root Cause

The PERL issues seem to happen only on specific new Intel CPUs with a so called 4th level cache.

The current assumption is that Intel CPUs with Iris Pro graphics are affected. Iris Pro means eDRAM (embedded DRAM) which is reported as 4th level cache in CPUID. We have confirmed that Crystal Well and Broadwell CPUs with Iris Pro are affected. It is likely that the Xeon E3-1200 v4 family is also affected.

It seems to be that there's a bug in the perl binary. It links against ancient code from the Intel compiler suite doing optimizations according to the CPU features. Very recent Intel CPUs have 4 cache descriptors.

People who encountered this used Virtual Box VBox 5.0.x - and it passes this information to the guest. This leads to a problem within the perl code. You won't see it on VBox 4.3 as this version does not pass the information to the guest. 

But actually it seems that this issue is independent of Virtual Box or any other virtualization software. It simply happens in this case as many people use VBox on Macs - and some Macs are equipped with this new CPU model. But people run Oracle in VBox environments and therefore see the issue as soon as they upgraded to VBox 5.0.x.

Potential Solutions

If you are using Oracle in VBox there are actually two solutions:

  • Revert to VBox 4.3 as this won't get you in trouble
    This problem was not triggered on VBox 4.3.x because this version did not  pass the full CPUID cache line information to the guest.
  • Run this sequence of commands in VBox 5.0 to tweak the CPUID bits passed to the guest:
    VBoxManage setextradata VM_NAME "VBoxInternal/CPUM/HostCPUID/Cache/Leaf" "0x4"
    VBoxManage setextradata VM_NAME "VBoxInternal/CPUM/HostCPUID/Cache/SubLeaf" "0x4"
    VBoxManage setextradata VM_NAME "VBoxInternal/CPUM/HostCPUID/Cache/eax"  "0"
    VBoxManage setextradata VM_NAME "VBoxInternal/CPUM/HostCPUID/Cache/ebx" "0" 
    VBoxManage setextradata VM_NAME "VBoxInternal/CPUM/HostCPUID/Cache/ecx" "0" 
    VBoxManage setextradata VM_NAME "VBoxInternal/CPUM/HostCPUID/Cache/edx"  "0"
    VBoxManage setextradata VM_NAME "VBoxInternal/CPUM/HostCPUID/Cache/SubLeafMask" "0xffffffff" 

    • Of course you'll need to replace VM_NAME by the name of your VM

If the error happens on a bare metal machine meaning it happens not inside a virtual image but on a native environment then the only chance you'll have (to my knowledge right now) is to exchange the PERL before doing really something such as running or in your Grid Infrastructure installation or before using the DBCA or the tool to create or upgrade a database.

In this case please refer to the blog post of Laurent Leturgez:

Issues with Oracle PERL causing segmentation faults:


Further Information

This issues is currently tracked internally as bug 22539814: ERRORS INSTALLING GRID INFRASTRUCTURE ON INTEL CPUS WITH 4 CACHE LEVEL.

So far we have not seen reports by people encountering this in a native environment but only by people using VBox 5.0.x or Parallels or VMware on a very modern version of Apple hardware.


Monday Nov 23, 2015

Network ACLs and Database Upgrade to Oracle 12c

What has been changed in Oracle Database 12c with Network ACLs?

Starting from 12c, network access control in the Oracle database is implemented using Real Application Security access control lists (ACLs). Existing 11g network ACLs in XDB will be migrated. Existing procedures and functions of the DBMS_NETWORK_ACL_ADMIN PL/SQL package and catalog views have been deprecated and replaced with new equivalents

In 12c, a network privilege can be granted by appending an access control entry (ACE) to a host ACL using DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE.  If you append an ACE to a host that has no existing host ACL, a new host ACL will be created implicitly. If the host ACL already exists, the ACE will be appended to the existing ACL.

(both paragraphs taken from MOS Note: 2078710.1)

What happens during/after upgrade?

  • Existing network ACLs will be migrated from XDB in Oracle 11g to Real Application Security in Oracle 12c.
    All privileges of the existing ACLs will be preserved
  • Existing ACLs will be renamed
  • Mapping between the old / new names is reflected in DBA_ACL_NAME_MAP.


Issues before/during Database Upgrade?

First of all the current preupgrd.sql does not warn you correctly if such ACLs exist. This fix gets added to the preupgrd.sql. But you'll need to download the most recent version from MOS Note 884522.1. The one from January 2015 does not have it yet. But this is addressed and will be implemented soon.

Here's an issue which happened to one of my very experienced colleagues from Oracle Consulting in an upgrade project:

"Customer had network ACLs defined and Privileges (resolve,connect) granted for several hosts to several DB users in

With the first DB, we observed the ACL renaming as you described it, but, much worse: 4 out of 9 privileges granted were completely gone away after the upgrade performed by DBUA (to We then were able to evaluate the missing privileges and re-grant them again. Warned by that, for the next databases to be upgraded, we copied all the content of the DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES to helper tables in order to be able to restore lost privileges (which was a good idea, as in one of the databases, only 87 out of 240 formerly existing privileges survived the upgrade)." 


Check for existing Network ACLs before the upgrade or get the most recent preupgrd.sql once it contains the check.

Preserve the existing network ACLs and privileges (DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES) in a intermediate staging table to have the possibility to restore them afterwards in case the automatic migration fails or does not happen.

If you encounter a situation where your Network ACLs don't get migrated correctly, disappear and/or don't exist in the mapping table DBA_ACL_NAME_MAP afterwards please open an SR and let Oracle Support check. There are known issues with mappings and migrations not done correctly (find some bugs below) so needs to be verified if you have hit a known issue or encountered a new one.

More Information?



Monday Nov 16, 2015

UPDATE: _rowsets_enabled in Oracle Database 12c

Last week I did post this entry with a strong recommendation to disable _rowsets_enabled in Oracle Database

Today I can give you an update, more insight information and better workarounds.

Credits go to our DWH and Optimizer people (thanks to Hermann, Angela, Nigel and Mohammed).

When is the problem happening?

When a hash join operation receives rowsets from its right input but then produces one row at a time as output. This explains why one of the bugs had as potential workaround hash_join_enabled=false (and please don't use this as a w/a!!!).

Different Workarounds 

  • Set
    event = "10055 trace name context forever, level 2097152"
    in your spfile. This will disable rowsets only for the specific situation where the problem happens.

    An important comment from Angela if you intend to set this event via an ALTER SESSION command:
    "Note that if the event is set with an alter session, it will not take effect unless the query or queries are recompiled, such as by changing the query text (by adding spaces or comments) or by flushing the shared pool."

  • Set
    in your spfile. But this will switch off the entire feature, not only the particular situation where the problem happens.

  • Apply the fix for
    (as of Nov 16, 2015 in regression testing and not available yet) as soon as it is available.

Update (Dec 2, 2015)

Thanks to Christian Ballweg from Optiz Consulting in Germany letting me know that the patch is available for download:

Bugs Resolved by This Patch


Thanks again to the Optimizer folks for their quick reaction!!!


Thursday Nov 12, 2015

Oracle VirtualBox 5.0.x - Segmentation Fault in PERL

Please see also:



Yesterday and the day before I've exchanged several emails with Ana who downloaded our Hands-On-Lab from here:

after OOW15, encountering a SEGMENTATION FAULT when trying to start the database upgrade with

$ $ORACLE_HOME/perl/bin/perl catupgrd.sql
Segmentation fault 

Very strange thing ... 

The database is in upgrade mode (checked this in the alert.log) and there are no strange things mentioned anywhere. Plus hundreds of people have run and completed our lab so far.

Tue Nov 10 20:39:47 2015
MMON started with pid=21, OS id=9828
Starting background process MMNL
Tue Nov 10 20:39:47 2015
MMNL started with pid=22, OS id=9832
Stopping Emon pool
Tue Nov 10 20:39:47 2015
ALTER SYSTEM enable restricted session;
Tue Nov 10 20:39:47 2015
Autotune of undo retention is turned off.
Tue Nov 10 20:39:47 2015
Tue Nov 10 20:39:47 2015
Tue Nov 10 20:39:47 2015
Tue Nov 10 20:39:47 2015
Resource Manager disabled during database migration: plan '' not set
Tue Nov 10 20:39:47 2015
ALTER SYSTEM SET resource_manager_plan= SCOPE=MEMORY;
Tue Nov 10 20:39:47 2015
Resource Manager disabled during database migration
replication_dependency_tracking turned off (no async multimaster replication found)
AQ Processes can not start in restrict mode
Starting background process CJQ0
Tue Nov 10 20:39:47 2015
CJQ0 started with pid=27, OS id=9836

We checked several other things - and then I came across this tweet by Martin Klier yesterday:

and started to search a bit

I have no 100% proof for the actual reason but several people seem to have issues with SEGMENTATION FAULTs in Oracle's PERL ($ORACLE_HOME/perl/bin/perl) when using Oracle VirtualBox 5.0.x - and according to VitualBox Forum that seems to happen with the most recent VBox 5.0.10 as well.

The "funny" thing is that all works perfectly well in VBox 4.3.x ...  

It reminds me a lot on the reoccuring VBox bug with my German keyboard not allowing me to type in the | (pipe) character which requires to press "ALT GR" +  "<" keys together.  



Tuesday Nov 10, 2015

Switch off "_rowsets_enabled" in Oracle Database 12c

Please find a recent update here:



Twitter is a good thing. I get alerted on things I haven't seen before. And sometimes some things are more than interesting.

This one is actually proven by Jonathan Lewis - and you can read all the details in Jonathan's blog post here:

There seems to be a realistic chance to get wrong query results displayed (regardless of using SQL*Plus or a JDBC or any other client - see the comment by Stefan Koehler below Jonathan's posting) because of the row sets feature in Oracle 12.1 allowing faster initial processing of query results.

You'll find this in the query information (marked in bold red):

Column Projection Information (identified by operation id):
   1 - "K"."KONTO_ID"[NUMBER,22]
   4 - (#keys=1) "KP"."KONTO_FK"[NUMBER,22],
   5 - (rowset=256) "KP"."KONTOPOSITION_ID"[NUMBER,22],
   6 - (rowset=256) "KWP"."KONTOPOSITION_FK"[NUMBER,22],
   8 - "K"."KONTO_ID"[NUMBER,22] 

Roy did some analysis yesterday as well (and now our Optimizer Support Experts look into this case to check whether any other wrong query result bugs are related to this topic). Because there are some open WRONG RESULTS bugs in this area, some of which were filed within the past week, we'd recommend to switch this feature off at the moment.  For instance these bugs are related to this misbehavior:

Recommendation - Updated (Nov 15, 2015)

Please read the update blog post here:


Monday Nov 02, 2015

Ouch, this hurts: bug 21923026 - patch Oracle 12c Home before upgrading if you have OLTP Compression in 11g


Actually I'd consider this as a real serious issue which may affect many customers with larger deployments using the Advanced Compression Option's OLTP Compression.

I came across it as Don Seiler full of anger twittered some bad words about Oracle Database We've exchanged a good number of emails - and I could see the issues Don and his colleagues got while assisting a customer to go live on Oracle Database 12c.

The most interesting one happened on the physical standby after the primary got upgraded. The MRP failed with an ORA-600.


After upgrading from Oracle to Oracle the MRP (Managed Recovery Process) on the physical standby database is failing with:

ORA-600 [kdBlkCheckError], [5]

Restoring the data file does not solve the issue.

This happens if the source table has OLTP Compression enabled, and this database has been upgraded to Oracle Database

This corruption on compressed tables will happen during media recovery while applying redo generated for those tables when the source database was running on the Oracle 11g software version.

This also applies to Oracle 12c upgraded physical standby databases when recovering redo that was generated on 11g.

The latter is actually the issue Don must have had encountered here.

Which versions are affected?

This happens only in Oracle Database and Oracle Database The issue is fixed in the MAIN code line in Oracle 12.2. You can detect tables with OLTP Compression with these queries:

select owner, table_name, compression, compress_for  
from   dba_tables

select table_owner, table_name, compression, compress_for  
from   dba_tab_partitions

select table_owner, table_name, compression, compress_for  
from   dba_tab_subpartitions


Uncompress the table would work - but is not a pleasant idea.

Install the fix for Bug 21923026 in the new Oracle 12c home BEFORE upgrading the database

Issue got filed as bug 21682261 but has been replaced by the patch for bug 21923026. It should be applied to Oracle before upgrade. If you hit the issue on a physical standby then apply the patch to the standby's home first, then refresh the affected data file by RMAN with a copy of this file from production - and restart MRP again.

Patch 21923026 is available on top of a plain Oracle installation but also on top of several PSUs and BPs. Please check the RELEASE selection of the patch download for bug 21923026

Further Information

  • Bug 21682261 
    (non published bug - therefore no link added)


Friday Oct 16, 2015

Ouch, this hurts: bug 20880215 - patch Oracle 12c


Just realized that my preivous blog post heading was misleading: it said "patch AFTER upgrade" which is misleading. You should apply this patch BEFORE you upgrade but of course to your new/future Oracle 12c home. If you hit the issue then apply the patch after upgrading to remedy it. Sorry for the confusion.

No updates for over a week? Sorry for that but too many workshops, swamped with OOW prep work - and a wonderful issue with the browser cache and the blog software putting all my new blog post work directly into /dev/null.

Thanks to Marcel Paul for highlighting this issue to me. I really benefit from such emails as I can learn a lot from those - and furthermore distribute information to many other tech folks as well.

Actually Marcel updated me as a follow up to me recent blog post:

describing an issue which will require to apply a patch before upgrade if you are not on Oracle or higher - and the below issue is related to this one but requires a patch AFTER upgrade, regardless what your source version was.


Marcel let me know about an issue they saw with their upgrade from Oracle to Oracle after the upgrade causing a core dump and potential table metadata corruption when you'll ADD a column to a table with a DEFAULT and ENABLE NOVALIDATE. This issue gets treated as bug 20880215 (ORA-7445 [QCSISCOLINFRO()+358] FOR ADD COLUMN WITH DEFAULT AND ENABLE NOVALIDATE)

Test Case

Very simple test case done by Marcel (thanks again!) - I could verify it within 30 seconds. 

create table dummy(code varchar2(5), text varchar2(30));
insert into dummy(code, text) values ('CD1','Hello World');
select * from dummy;
alter table dummy ADD condition varchar2(3) DEFAULT 'YES' not null enable novalidate;
select * from dummy;


First of all the client (in my case SQL*Plus) will disconnect with the meaningless ORA-3113. So lets have a look into the alert.log for more useful information:

Wed Oct 14 15:06:59 2015
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4] [PC:0xCDB0046, qcsIsColInFro()+358] [flags: 0x0, count: 1]
Errors in file /oradata/diag/rdbms/cdb2/CDB2/trace/CDB2_ora_2424.trc  (incident=3433) (PDBNAME=CDB$ROOT):
ORA-07445: exception encountered: core dump [qcsIsColInFro()+358] [SIGSEGV] [ADDR:0x4] [PC:0xCDB0046] [Address not mapped to object] []
Incident details in: /oradata/diag/rdbms/cdb2/CDB2/incident/incdir_3433/CDB2_ora_2424_i3433.trc

Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

Wed Oct 14 15:07:08 2015
Dumping diagnostic data in directory=[cdmp_20151014150708], requested by (instance=1, osid=2424), summary=[incident=3433].

So we've got a core dump. And whatever we query now from the table involving the column "condition" we'll get the same error.


Apply the fix for bug 20880215 (ORA-7445 [QCSISCOLINFRO+358] FOR ADD COLUMN WITH DEFAULT AND ENABLE NOVALIDATE). You may have to request the fix for your platform if it hasn't been done yet. 

Further Information

A request for inclusion into the next Bundle Patches had been filed as well.


Tuesday Sep 29, 2015

No OS Authentication? datapatch will fail in every upgrade

Thanks to Daniel from SimCorp for bringing this to my attention:Patch
Actually, command line upgrades are affected as well,
if you do not use OS authentication. Apparently, datapatch
is not able to execute in non-OS authentication mode.
See MOS note 1635007.1. 


You are doing a command line upgrade to Oracle Database 12c with - and you don't use OS authentication allowing connections with "/ as sysdba" then won't be able to execute the SPU/PSU/BP related SQL commands as it will fail to connect to your database with an ORA-1017 (invalid username/password) error.


Bug 18361221 is fixed in Oracle 12.2 and got backported to and but not actually included in any bundles at the moment. Without this fix, datapatch will only connect with '/ as sysdba'. 

Either apply generic patch 18361221 to your destination Oracle Home or switch on OS authentication by setting:

temporarily in your sqlnet.ora for the duration of the upgrade only. See the documentation for further information about SQLNET.AUTHENTICATION_SERVICES. Or, of course, run -verbose after upgrading your database in any case ...

Further Information and Links:

Related Blog Posts


Monday Sep 14, 2015

SQL Plan Management - Known Issues in Oracle

Our Support colleagues released the patch recommendation note for SQL Plan Management (SPM) for Oracle Database


SPM Note

In the unlikely event you'd like to upgrade to Oracle Database or (very very unlikely hopefully) please see these matching notes:


Some additional things to mention:

  • SPM is an Oracle Enterprise Edition feature at no extra cost
  • SPM is THE feature to ensure plan stability tackling changes such as (of course) upgrades and migrations
  • SPM has been improved a lot internally in Oracle Database
    • We now store entire plans instead of a large accumulation of hints in the SQL Management Base (SMB) in SYSAUX tablespace
    • The "Evolve" task does happen automatically (SYS_AUTO_SPM_EVOLVE_TASK) as part of the Automatic SQL Tuning Task 
  • You should always adjust the retention when starting to play with SQL Plan Management as the default retention of 53 weeks may lead to a too large LOB segment in SYSAUX tablespace (and LOB segments never shrink)
    • SQL> exec DBMS_SPM.CONFIGURE('plan_retention_weeks',5);
  • See the Oracle Database 12c documentation about SPM:



Friday Aug 21, 2015

DBMS_STATS. GATHER_DICTIONARY_STATS fails with ORA-20001 ORA-6502 ORA-6512 - Concurrent Stats

I really have to say "Thank you very much" to the people out there alerting me about issues I haven't seen before. Just in the past week I've got to learn about three issues which are related to the upgrade - and I haven't seen before. Please don't expect me always to follow up with the progress of your SRs or the related bugs. But your experiences are such an important source for me - so thanks again!

The most recent issue (thanks to Bernd Tuba from MM Warburg) ...


execute dbms_stats.gather_dictionary_stats

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_STATS", line 24268
ORA-20001: Unexpected configuration: sofar: 41 objList.count: 41 sofar_part: 0 partObjList.count: 0 sofar_ind: 0 indObjList.count: 21 concurrent: TRUE runPartTable: TRUE jstats.Running: -19
ORA-06512: at "SYS.DBMS_STATS", line 27383
ORA-06512: at "SYS.DBMS_STATS", line 27402
ORA-06512: at line 1

I see the word "CONCURRENT" - and it immediately rings a bell. Wasn't there an issue with the change of setting for default stats gathering in Oracle Database being now CONCURRENT=TRUE?

Yes ... the reason for this blog's existence is not only to give you some interesting useful information to read about but also to dump of details from my limited brain capacity :-) I have written something about a similar issue in October 2014:


This is interesting. Whereas in my other blog post the concurrent stats gathering conflicted with the resource manager settings and is fixed by a patch (Patch 19664340: ORA-20000: UNABLE TO GATHER STATISTICS CONCURRENTLY: RESOURCE MANAGER IS NOT ENABLED) this one is a different topic.

(unpublished bug - therefore no link included here)


Very simple to solve - switch CONCURRENT stats gathering to FALSE

SQL> exec dbms_stats.set_global_prefs('CONCURRENT', 'FALSE');

In the Exadata Upgrade Note (MOS Note: 1681467.1) you'll find the same recommendation. 

More Information? 


Tuesday Aug 18, 2015

Ouch, this hurts: bug 17325413 - patch BEFORE upgrade!

PatchI really don't want to turn this blog into something making our database look bad. But in this case it is really necessary as it is VERY UNUSUAL that we recommend to patch the database BEFORE upgrade

Just for clarification:

The following topic will affect all databases between and - those (and only those) need to be patched BEFORE upgrade. The topic is fixed in but as it gets introduced with the BEFORE upgrade database version you'll have to apply the fix before upgrade. The inclusion of the fix in means only that the misbehavior won't happen there again. But as it is a meta data dictionary corruption you'll have to apply the fix before as otherwise it will break during or after the upgrade.

First of all, thanks to Ehtiram Hasanov (cleverbridge AG) and Oliver Pyka ( for highlighting this to me. And sorry for hitting this issue ...


After upgrading to Oracle Database you'll get one of the below errors when trying to read data: 

  • ORA-07445: exception encountered: core dump [qcsIsColInFro()+358] [SIGSEGV] [ADDR:0x4] [PC:0xCDB4A26] [Address not mapped to object] []
  • ORA-12899 / ORA-607
  • ORA-600 [kdmv_check_row_2:IMCU row has wrong contents]
  • ORA-600 [kddummy_blkchk]
  • ORA-600 [kdBlkCheckError]
  • ORA-600 [klaprs_12]
  • ORA-600 [13013]
  • ORA-600 [17182] 


Basically this happens when you try to drop a column with a DEFAULT value and a NOT NULL definition - it ends up with dropped column data being written to disk leading to block corruptions. This causes problems for generating undo which cannot be applied; a ROLLBACK fails.

If you need more information please look up this MOS Note about
Bug 17325413 - Drop column with DEFAULT value and NOT NULL definition ends up with dropped column data hitting disk leading to corruption

Versions being affected:

  • These versions require to be patched BEFORE upgrade:
    • Oracle Database and above (may happen with earlier PSUs as well)
      Solution: Apply the fix 17325413  on top - see below
    • Oracle Database and 
      Solution: Apply the most recent PSU
  • These versions can get you the issue if you haven't patched BEFORE upgrade:
    • Oracle Database
    • Oracle Database

Workaround and/or Fix:

The MOS Note about Bug 17325413 - Drop column with DEFAULT value and NOT NULL definition ends up with dropped column data hitting disk leading to corruption explains the workaround WHEN you hit this issues. 

As a precaution you will have to make sure that you applied one of those fixes BEFORE upgrading to Oracle Database as the fix for Bug 17325413 is included in all those mentioned below (list is taken from above MOS Note as well).

The best way to avoid this is really to apply the patch (or the PSU/BP including the patch) before upgrading.

The issue has been mentioned in "Oracle - Known Issues and Alerts" (MOS Note:1562139.1)  under "Issues Introduced":

Issues introduced

But that does jump into your eye as a thing you need to fix before upgrade.
We'll see if we can get the issue added to the 12c MOS Notes as "Upgrade Issues".


Thursday Jul 30, 2015

Things to consider BEFORE upgrading to Oracle to AVOID poor performance and wrong results

Finally it got published on MyOracle Support (MOS) portal - one of the most helpful notes when we prep for an upgrade or a migration - and potentially the one with the longest title in MOS:

MOS Note: 2034610.1
Things to consider BEFORE upgrading to Oracle Database
to AVOID poor performance and wrong results 

Avoid Poor Performance and Wrong Results when upgrading to

Just in case you are NOT upgrading/migrating to Oracle Database there are three other helpful notes for earlier releases available as well:

Document 1320966.1
Things to Consider Before Upgrading to
to Avoid Poor Performance or Wrong Results

Document 1392633.1
Things to Consider Before Upgrading to
to Avoid Poor Performance or Wrong Results
Document 1645862.1
Things to Consider Before Upgrading to
to Avoid Poor Performance or Wrong Results


Wednesday Jul 01, 2015

Log Writer Slave Issues in - mainly on IBM AIX

Currently we see a lot of issues with the Multiple Logwriter feature in Oracle Database, especially on the IBM AIX platform.

What are Multiple LGWRs?

You will see multiple LGnn (Log Writer Worker) processes on multiprocessor systems, The LGWR (Log Writer) creates worker processes (slaves) to improve the performance of writing to the redo log. LGnn (Log Writer Worker) processes are not used when there is a SYNC standby destination. Possible processes include LG00-LG99.

Here is a list of new background processes in Oracle Database 12.1:
MOS Note 1625912.1 - New Background Processes In 12c 

For deep dive information about how to trace the LGWR (and DBWR) and the differences 11.2/12.1 and wait events and much more, please see this Deep Dive PDF from an UKOUG talk from Frits Hoogland.

Known Symptoms:


Turn off multiple logwriters on IBM AIX at the moment - in some cases this may solve issues on other platforms as well but please don't take this as a generic recommendation for all platforms. We are seeing the issues mainly on IBM AIX.

Set in your spfile: 


See also:



Tuesday Jun 30, 2015

Some Data Pump issues:
+ STATUS parameter giving bad performance

One of my dear Oracle ACS colleagues (Danke Thomas!) highlighted this issue to me as one of his lead customers hit this pitfall a week ago. . 

DBMS_DATAPUMP Import Over NETWORK_LINK fails with ORA-39126 / ORA-31600

Symptoms are: 

ORA-31600: invalid input value IN ('VIEWS_AS_TABLES/TABLE_DATA') for parameter VALUE in function SET_FILTER

This can be cured with the patch for bug19501000 -  but this patch can conflict with:Bug 18793246  EXPDP slow showing base object lookup during datapump export causes full table scan per object and therefore may require a merge patch - patch 21253883 is the one to go with in this case.


Another issue Roy just came across:

Data Pump is giving bad performance in Oracle when the STATUS parameter option is used on command line

Symptoms are: 

It looks like the routines we are using to get status are significantly slower in 12c than in 11g. On a STATUS call of expdp/impdp runs in 0.2-0.3 seconds, but in it takes 0.8-1.6 seconds. As a result the client falls behind on; it is taking about 0.5-0.8 seconds to put out each line in the logfile because it is getting the status each time. With over 9000 tables in a test that half a second really adds up. The result in this test case was that the data pump job completed in 35 minutes, but it took another 30-35 minutes to finish putting out messages on the client (the log file was already complete) and return control to the command line. This happens only when you use STATUS on the command line.

Recommendation is:  

Don't use the STATUS parameter on the expdp/impdp command line in Oracle until the issue is fixed. This will be tracked under Bug 21123545.


Tuesday May 26, 2015

Oracle - Security Behavior Change with non-SYSDBA Triggers

Oracle Database SecuritySometimes things get revealed at unexpected occasions. This one happened during a recent customer upgrade to Oracle Database 12c with a 3rd party geospatioanl application installed (ESRI).

At the very end of the upgrade the customer saw many ORA-1031 (insufficient privileges) errors and it seemed to be that nothing was working correctly anymore. 

This happened during the run of catupend.sql. The following code path in  catupend.sql causes the error.

cursor ddl_triggers is                                       
   select o.object_id from dba_triggers t, dba_objects o     
    where t.owner = o.owner and t.trigger_name = o.object_name
      and o.object_type = 'TRIGGER'                          
      and (t.triggering_event like '%ALTER%' or              
    t.triggering_event like '%DDL%');     

ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_UTILITY", line 1279
ORA-06512: at line 20

Apparently there's no access to an application trigger anymore - which got deployed as a system trigger (for more information about ESRI's system trigger please click this link). Even though this is strange it doesn't seem like a big issue. But in fact it is as this procedure failed and caused other stuff not getting validated correctly. So subsequent actions (for instance the run of utlu121s.sql, the post upgrade script) failed with ORA-1031 as well pointing to DBMS_UTILITY.

The customer [Danke Andy!!!] itself found the workaround by pattern matching similar issues in MOS and trying some grants - the 3rd one did the trick:


So it was obvious that something in the security architecture in Oracle Database had been changed - and somebody forgot to document it. Later on I've learned that this change got introduced with the July 2013 PSU/CPU as well. I don't blame the customer for not applying PSUs since almost two years - I knew that upfront and we are implementing a 2-PSUs-per-year strategy now with the upgrade to Oracle Database 12c. 

The system trigger ESRI had created couldn't be validated anymore under the context of the SDE (ESRI's application) user. Therefore it failed but caused other actions to fail as well.  

This behavior change is related to "SYSDBA privilege should not be available in non-SYS owned DR procedure / trigger execution" which is first fixed into, and then backported as part of CPU July-2013.
When SYS executes a non-SYS owned DR procedure or a Trigger, the SYS privileges would not available during the procedure/trigger execution. The procedure/trigger owner privileges prevail.


Friday Feb 13, 2015

Is it always the Optimizer? Should you disable Group By Elimination in 12c?

I wouldn't say it's always the optimizer - but sometimes one or two tiny little things are broken making it necessary to turn off new functionality for a while.

Please don't misinterpret this posting!
As far as I see (and I really do work with customers!) I'd state the Oracle Database Optimizer is more stable, faster and predictable compared to 11.2.0.x. Our Optimizer Architects and Developers have done a great job. But with all the complexity involved sometimes it takes a few fixes or incarnations until a great new feature really matures. The Group-By-Elimination feature in Oracle Database 12c seems to be such a candidate. 

What does the feature do? A simple example demonstrates the feature.

First the elimination is OFF (FALSE): 

SQL> explain plan for
  2  select /*+ opt_param('_optimizer_aggr_groupby_elim', 'false')*/
  3   dummy, sum(cnt)
  4    from (select dummy,
  5                 count(*) cnt
  6            from dualcopy
  7           group by dummy)
  8   group by dummy
  9  ; 

|  Ld | Operation            | Name     |
|   0 | SELECT STATEMENT     |          |
|   1 |  HASH GROUP BY       |          |
|   2 |   VIEW               |          |
|   3 |    HASH GROUP BY     |          |

And now it's ON (TRUE):

SQL> explain plan for
  2  select /*+ opt_param('_optimizer_aggr_groupby_elim', 'true')*/
  3   dummy, sum(cnt)
  4    from (select dummy,
  5                 count(*) cnt
  6            from dualcopy
  7           group by dummy)
  8   group by dummy
  9  ;
| Ld  | Operation          | Name     |
|   0 | SELECT STATEMENT   |          |
|   1 |  HASH GROUP BY     |          |

By comparing the two execution plans you'll see the difference immediately.

But there seem to be a few issues with that new feature such as several wrong query result bugs. The issues will be be tracked under the non-public bug20508819. Support may release a note as well.

At the moment we'd recommend to set: 



Wednesday Jul 11, 2012

Upgrade to - OCM: ORA-12012 and ORA-29280

OCM is the Oracle Configuration Manager, a tool to proactively monitor your Oracle environment to provide this information to Oracle Software Support. As OCM is installed by default in many databases but is some sort of independent from the database's version you won't expect any issues during or after a database upgrade ;-)

But after the upgrade from Oracle to Oracle on Exadata X2-2 one of my customers found the following error in the alert.log every 24 hours:

Errors in file /opt/oracle/diag/rdbms/db/trace/db_j001_26027.trc:
ORA-12012: error on auto execute of job "ORACLE_OCM"."MGMT_CONFIG_JOB_2_1"
ORA-29280: invalid directory path
ORA-06512: at "ORACLE_OCM.MGMT_DB_LL_METRICS", line 2436
ORA-06512: at line 1

Why is that happening and how to solve that issue now?

OCM is trying to write to a local directory which does not exist. Besides that the OCM version delivered with Oracle Database Patch Set is older than the newest available OCM Collector 10.3.7 - the one which has that issue fixed.

So you'll either drop OCM completely if you won't use it:

SQL> drop user ORACLE_OCM cascade;

or you'll disable the collector jobs:

SQL> exec dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB');

SQL> exec dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');

or you'll have to reconfigure OCM - and please see MOS Note:1453959.1 for a detailed description how to do that - it's basically executing the script ORACLE_HOME/ccr/admin/scripts/installCCRSQL - but there maybe other things to consider especially in a RAC environment.

- Mike

Just for the records: Bug 12927935: ORA-12012, ORACLE_OCM.MGMT_DB_LL_METRICS, ORA-29280

Tuesday Jul 03, 2012

Data Pump: Consistent Export?

Ouch ... I have to admit as I did say in several workshops in the past weeks that a data pump export with expdp is per se consistent.

Well ... I thought it is ... but it's not. Thanks to a customer who is doing a large unicode migration at the moment. We were discussing parameters in the expdp's par file. And I did ask my colleagues after doing some research on MOS. And here are the results of my "research":

  • MOS Note 377218.1 has a nice example showing a data pump export of a partitioned table with DELETEs on that table as inconsistent
  • Background:
    Back in the old 9i days when Data Pump was designed flashback technology wasn't as popular and well known as today - and UNDO usage was the major concern as a consistent per default export would have heavily relied on UNDO. That's why - similar to good ol' exp - the export won't operate per default in consistency mode
  • To get a consistent data pump export with expdp you'll have to set:
    in your parameter file. Then it will be consistent according to the timestamp when the process has been started. You could use FLASHBACK_SCN instead and determine the SCN beforehand if you'd like to be exact.

So sorry if I had proclaimed a feature which unfortunately is not there by default :-(

- Mike


Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle

Based in Germany. Interlink between customers/partners and the Upgrade Development. Running workshops between Arctic and Antartica. Assisting customers in their reference projects onsite and remotely. Connect via:

- -


« May 2016
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Viewlets and Videos
Workshop Map
x Oracle related Tech Blogs
This week on my Rega & Pono
Upgrade Reference Papers