Tuesday Oct 06, 2015

New in Oracle 12c: _optimizer_gather_stats_on_load

Received an email from Roy last night with some performance issues a customer in the US encountered recently during their upgrade testing

One issue the customer encountered has to do with tons of parallel slaves creating a massive noise on the system when they are doing a CTAS (Create Table As Select) - and the same thing happens with an IAS (Insert Append Select).

What caused this change?

In this case the behavior change is well documented, even though not linked to the responsible underscore parameter. 

Quoting from the first paper: 

Online statistics gathering

In Oracle Database 12c, online statistics gathering “piggybacks” statistics gather as part of a direct-path data loading operation such as, create table as select (CTAS) and insert as select (IAS) operations. Gathering statistics as part of the data loading operation, means no additional full data scan is required to have statistics available immediately after the data is loaded.

The parameter controlling this change is not mentioned:

  • _optimizer_gather_stats_on_load

The default is TRUE since Oracle - the parameter or functionality did not exist before Oracle Database 12c.

Things to Know

The online stats gathering for IAS can happen only if the object you are loading data into is empty. You'll recognize the feature when you see a line saying OPTIMIZER STATISTICS GATHERING in the execution plan. Additionally you'll find STATS_ON_LOAD in the NOTES column of below query:


Consider now that first of all immediate stats gathering may not be desired as it will mean additional load to the system. Even though the feature has been designed to generate as little load as possible it's still doing something in the background. Hence there may be situations where you'd like to switch it off, e.g. with a hint:

insert /*+append NO_GATHER_OPTIMIZER_STATISTICS*/ into MYTAB select ...

And how about the number of parallel slaves creating the statistics? 

There's no easy answer to this based on the information I have at the moment - but I'd highly recommend this article about parallel execution in Oracle:


Friday Oct 02, 2015

OOW 2015 Sessions and Labs - Oracle Open World

OOW 2015OMG ... only a few weeks to go ... Oracle Open World 2015 in San Francisco is coming closer and closer ...

And this year will be really tough as we have a reduced number of people there - but more work to do as in previous years. 3 talks (2 for Upgrade, 1 for Data Pump), 4 labs (all in Nikko Hotel 15min walking distance from Moscone Center) - plus a good number of customer meetings already lined up. Plus the chance to meet so many great people ... and not to forget the Data Warehouse Global Leaders event at the Oracle HQ. 

I have that strange feeling that I will be VERY tired when I'll board the plane on Friday night heading back to Germany ... ;-)

Focus On Upgrades/Migrations

As the fantastic application we are using for the OOW content catalog doesn't allow me to link directly to a session Roy has built a Focus On document to guide you to some important talks around Upgrades and Migrations at OOW2015.for your convenience: 


Session ID


Start Time


CON6777 Upgrade and Migrate to Oracle Database 12c: Live and Uncensored!

Many customers now have database environments numbering in the hundreds or even thousands. This session addresses the challenge of maintaining technical currency of such an environment while also containing upgrade and migration costs at a reasonable level. Learn from Oracle Database upgrade experts about product features, options, tools, techniques, and services that can help you maintain control of your database environment. You will also see examples of how real customers are successfully meeting this challenge today.
October 26
at 13:30h
Moscone South—102
CON8375 How to Upgrade Hundreds or Thousands of Databases in a Reasonable Amount of Time

Many customers now have database environments numbering in the hundreds or even thousands. This session addresses the challenge of maintaining technical currency of such an environment while also containing upgrade and migration costs at a reasonable level. Learn from Oracle Database upgrade experts about product features, options, tools, techniques, and services that can help you maintain control of your database environment. You will also see examples of how real customers are successfully meeting this challenge today.
October 28
at 12:15h
Moscone South—102
CON8376 Deep Dive: More Oracle Data Pump Performance Tips and Tricks

The Oracle Data Pump development team is back with even more performance tips and tricks for DBAs! In this session, learn about Oracle Data Pump features, parameters, and patches—some added since the first patch set of Oracle Database 12c—that will improve performance and decrease overhead for Oracle Data Pump projects. Whether you are an Oracle Data Pump novice or already an expert, you are sure to learn something new in this session that will help you maximize the throughput of your export and import operations.
October 29
at 9:30h

Moscone South—305

HOL10348 Upgrade, Migrate, and Consolidate to Oracle Database 12c [HOL10438]

The Oracle Data Pump development team is back with even more performance tips and tricks for DBAs! In this session, learn about Oracle Data Pump features, parameters, and patches—some added since the first patch set of Oracle Database 12c—that will improve performance and decrease overhead for Oracle Data Pump projects. Whether you are an Oracle Data Pump novice or already an expert, you are sure to learn something new in this session that will help you maximize the throughput of your export and import operations.
Oct 26 at 11:00h
Oct 27 at 11:45h
Oct 28 at 13:15h
Oct 29 at 12:30h

Hotel Nikko - Golden Gate

Hope to see you at OOW 2015!


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 catctl.pl - and you don't use OS authentication allowing connections with "/ as sysdba" then datapatch.pl 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 datapatch.pl -verbose after upgrading your database in any case ...

Further Information and Links:

Related Blog Posts datapatch.pl:


Monday Sep 21, 2015

DBUA displays wrong RMAN backup for restore - Oracle

If you are using the Database Upgrade Assistant (DBUA) to upgrade your database to Oracle Database be aware when you choose to potentially restore your database from a existing backup in case of an error during the upgrade.

First of all I would always stop DBUA and try the command line upgrade after fixing the issues instead of restoring the entire database. But this is a different story.

Anyhow, the most recent available backup to be displayed is most likely your newest one as the underlying query uses a MAX function - but leading to an incorrect (or unintended) result.

Ignore the fact that the screenshot is in German - the interesting part is the displayed time stamp for the most recent available backup:

DBUA Restore Backup

The customer who alerted me was wondering as his list of backups showed also backups from August and early September.

The query being used in DBUA to gather the most recent date; 

SELECT MAX (TO_CHAR (completion_time, 'DD-MON-YYYY HH24:MI:SS')) AS end_time FROM (SELECT completion_time FROM v$backup_set)

may give you this result: 31-JUL-2015 23:59:52 - even if you have newer backups taken in August and September. The TO_CHAR conversion will lead to the incorrect handling of the date as the MAX function won't deliver the most recent date but the alphabetical highest value of the conversion result. 

The query should be: 

SELECT MAX (completion_time) AS end_time
FROM (SELECT completion_time FROM v$backup_set)

to display the most recent full backup.

It will be fixed in the next release.
Credits go to Bernd Tuba from MM Warburg - thanks!!


Thursday Sep 17, 2015

SE2 - Some questions, some answers ...

Oracle Standard Edition SE2 were a lot of questions regarding Oracle Database Standard Edition SE2 in the past days.And you may find some things mentioned especially in the COMMENT section of the blog post from Sep 1, 2015.

And please find the official Oracle SE2 "brief" document here giving you a good overview on SE2:

I'll try to summarize some topics I'm able to answer by myself. And please don't expect me to assist with license or license migration questions - that is far beyond my scope or knowledge or responsibility, and you may please contact your Oracle sales person regarding these topics instead.

Please find all our articles about Oracle Database Standard Edition SE2:

Software and Support 

  • Q:
    How long will Premier Support offered for Oracle Database Standard Edition SE and SE1?
  • A:
    Premier Support for Oracle Database Standard Edition SE and SE1 will continue until Sept 1, 2016. At this date SE/SE1 will enter Sustaining Support.
  • Q:
    Will there be Extended Support offered for Oracle Database Standard Edition SE and SE1?
  • A:
    No. Extended Support for 12.1 SE and SE1 is not offered. The Lifetime Support Policy document has been updated to reflect this.
  • Q:
    Are there plans to release Oracle SE/SE1?
  • A:
    Oracle Standard Edition will be offered as SE2 only.
  • Q:
    Is hard partitioning allowed with SE2?
  • A:
    Yes, it is as long as there are no more that 2 physical sockets in the server and you are using a supported hard partitioning software to bind a VM to a specific CPU. SE2 is not allowed to run on servers with more than 2 sockets.
    For an explanation and a list of supported hard partitioning technologies please see here:
  • Q:
    Is Oracle Multitenant available with SE2?
  • A:
    Oracle Multitenant is an Oracle Enterprise Edition only license. But you can have Oracle Single Tenant with many benefits meaning you'll have one pluggable database within one container database. Features such as remote cloning will be fully supported. See: https://blogs.oracle.com/UPGRADE/entry/can_you_have_oracle_multitenant
  • Q:
    Can we move PDB from SE2 Single Tenant to a EE Single/Multitenant database? 
  • A:
    Yes, of course - this is fully supported.

Database Installation and Patching

  • Q:
    Can I install Oracle Standard Edition SE2 into the existing home (in-place)?
  • A:
    Yes, this is possible even though I would never recommend to do this for the simple reason that wiping out your current home will increase the potential downtime and cause you plenty of extra work in case of fallback. If you still want to install for whatever reason into the existing home there's no change in handling to any other Oracle release since Oracle
  • Q:
    Which PSU should I apply on top of the releases Oracle Database Standard Edition SE2 installation?
  • A:
    Take the same PSU as you would take for an EE database - there's no difference and there won't be specific PSUs (or SPU/CPUs or BPs) released for SE2. Check out MOS Note:1683799.1 ( Patch Set - Availability and Known Issues) for the most recent recommended PSU
  • Q:
    Is RAC (Real Application Clusters) still included in SE2?
  • A:
    Yes it is - limited to 2 nodes, and each node must be a single-socket server. See above: there's the possibility to either remove a CPU physically - or hard partitioning with a supported virtualization solution such as OVM.
  • Q:
    I would like to use SE2 with RAC. Which Grid Infrastructure will I need to download?
  • A:
    When deploying a SE2 RAC Cluster please download the regular Oracle Grid Infrastructure (GI) packages from the official download locations (OTN, MOS, eDelivery). There's no separate download for an SE2 GI.
  • Q:
    Can I install/use SE2 on a 4-socket server?
  • A:
    No - SE2 is limited to be deployed on 2-socket servers only. 
  • Q: 
    Can I deploy more than one SE2 database on a server?
  • A:
    Yes, of course you can.

Database Upgrade

  • Q:
    I want to upgrade from Oracle SE/SE1 to Oracle SE2. Is there any specific procedure or tool to use?
  • A:
    Upgrade works exactly as expected. There's no difference for SE2 compared to the upgrade procedure from Oracle EE to Oracle EE. 
  • Q:
    I would like to upgrade from Oracle SE/SE1 to Oracle EE. Is there any specific procedure or tool to use?
  • A:
    Upgrade is always the same procedure, regardless of SE, SE1, SE2 or EE. But if you'll convert from SE/1/2 to EE please follow the advises in MOS Note:2046103.1 (How to Convert Oracle Database 12c from Standard to Enterprise Edition ?)

Other topics

  • Q:
    Does the thread limitation in SE2 (16 in single instance mode, 8 per node in RAC mode) include background processes?
  • A:
    No, background processes such as LGWR or PMON are not included in this limitation.
  • Q:
    Is the thread limitation valid across all instances on the same server?
  • A:
    No, the limit is enforced per database instance and not per server.
  • Q:
    Does the thread limitation happen based on number of OS threads or CPU threads?
  • A:
    Actually the use of the term "threads" may be a bit misleading. Please have a look into the documentation about Instance Caging. That will explain how the thread limitation will be enforced. Views to monitor the resource usage are: V$RSRC_CONSUMER_GROUPV$RSRCMGRMETRIC and V$RSRCMGRMETRIC_HISTORY.
  • Q:
    Is there a core limitation for the hardware SE2 is installed on?
  • A:
    No, the box you'll use Oracle SE2 on can have as many cores as the CPUs offer. But there's a socket limitation of a maximum of 2 physical sockets. But the most modern CPUs can present 36 cores to SE2 on a 2-socket machine. 
  • Q:
    Can I disable Hyper Threading to present more "real" threads to the database?
  • A:
    Yes, this is possible but the effect may not be huge.


Wednesday Sep 16, 2015

Script: Is your database ready for Oracle GoldenGate?

Oracle GoldenGate can be a good addition to a lot of upgrade and migration projects to decrease the downtime to a bare minimum, or even zero in some cases.

But before you consider Oracle GoldenGate as THE solution to decrease your downtime you may evaluate if your database is ready for OGG. For this purpose you can download scripts from MyOracle Support (MOS) to check exactly this.

Complete Database Profile OGG readiness check for Classic Extract 

MOS Note:1298562.1:
GoldenGate database Complete Database Profile check script for Oracle DB (All Schemas) Classic Extract 

This script is intended to query all of the non default database users to identify current configuration and identify any unsupported data types or types that may need special considerations for Oracle GoldenGate in an Oracle environment.

Check OGG readiness for Schema Only

MOS Note: 1296168.1
Oracle GoldenGate database Schema Profile check script for Oracle DB

This script is intended to query the database by schema to identify current configuration and identify any unsupported data types or types that may need special considerations for Oracle GoldenGate in an Oracle environment. 

Difference and Execution 

The main difference between the two scripts is the amount of data being processed. The Schema Script is more targeted and therefore should return fewer items that need additional checking.

Log into sqlplus as sysdba and run the script:

SQL> @full-DB_CheckOracle_15092015.sql

SQL> @full-schemaCheckOracle_15092015.sql

Review the output.


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:



Thursday Sep 10, 2015

Don't get puzzled by "sqlpatch" messages during Upgrade

During my last Hands-On-Labs in Uruguay and Argentina I've had several people wondering about these messages below when running the command line upgrade with catctl.pl:

catctl.pl - sqlpatch

This (and another) message breaks the nicely structured format of the catctl.pl output. And as it ends with an "err" extension it looks to many people as if the upgrade had gotten an error,

But please don't feel disturbed. It's just messages from sqlpatch invocation - and the "err" extension is just pointing to an error file in case something has gone wrong.

In a future release such messages will be written to the logfiles only but not to the screen output anymore.


Wednesday Sep 09, 2015

Different Metrics for SPA (SQL Performance Analyzer)

I'm more the command line type of person. Once I've understand what's going on behind the curtains I certainly switch to the GUI-click-click tools. But in the case of Real Application Testing - even though the support via the OEM GUI is excellent - sometimes I prefer to run my procedures from the command line and check my reports in the browser.

Recently Thomas, a colleague from Oracle ACS Support, and I were asking ourselves about the different comparison metrics for the SQL Performance Analyzer reporting We did scan the documentation but we found only examples but no complete list. Then we did ask a colleague but thanks to OEM we got an incomplete list as well.

Finally Thomas dug it out - it's stored in the dictionary in the table V$SQLPA_METRIC

SQL> SELECT metric_name FROM v$sqlpa_metric;


9 rows selected.

What do you do with these metrics now?

You can use them in such a way:

set timing on

   execution_name=>'Compare workload Elapsed',
   execution_type=>'compare performance',
                     'execution_name2','TEST 11107 workload'),
   execution_desc=>'Compare 11107 Workload on 12102 Elapsed');

You can vary the elapsed_time in my example with the various comparison metrics mentioned in v$sqlpa_metric


Tuesday Sep 08, 2015

MOS Download for Oracle Database including SE2

Please find all our articles about Oracle Database Standard Edition SE2:


You have 3 options to download Oracle Database Enterprise Edition and Oracle Database Standard Edition SE2.

Regarding OTN and eDelivery nothing has been changed since Sep 1, 2015 when Oracle Database Standard Edition SE2 got published. But the download from MOS now has changed.

When you search in the under Patches and Updates tab ...

Patches And Updates - MOS

... and display the Latest Patch Sets ...

Latest Patch Sets - MOS

... check for instance for platform Linux x86-64bit ...

MOS - Linux

... then you'll wonder about the two patch numbers ...

Patch Numbers Oracle SE2

The only difference of 2.5 GB in size results from two more zip files included in the new patch number 2141921 ...

... you'll have to check the README button to find out what's hidden behind each file ...

Files 3 and 4 contain Oracle Database Standard Edition SE2.

So please don't get puzzled by the different patch numbers - the only real difference is the inclusion of SE2 into the download package but the included patches etc are exactly the same as in the previous patch number. 



Monday Sep 07, 2015

Oracle Database SE2 - Support and Patches for Oracle Database SE/SE1

Oracle SE2

Please find all our articles about Oracle Database Standard Edition SE2:


Some questions regarding support for Oracle Database Standard Edition (SE/SE1) came up in the past days.

Most of those are answered already in MOS Note 2027072.1 (Oracle Database 12c Standard Edition 2 ( )

How long will Oracle Database in any edition remain under Premier Support? 

Full patching support for for all versions of 12gR1 (Enterprise Edition, Standard Edition and Standard One Edition) will be proviced for an additional 12 months from the release of SE2, so through until end August 2016. 

After that period Oracle Database will enter Sustaining Support. There won't be any Extended Support for Oracle Database in any edition.

Will there be PSUs and SPUs/CPUs available?

Yes, quarterly Patch Set Updates and Critical Patch Updates for will continue to be delivered until end of Premier Support for Oracle Database  

Will there be an Oracle Database Standard Edition SE/SE1? 

Beginning with the release of Oracle Database,  Oracle Database Standard Edition (SE) and Oracle Database Standard Edition One (SE1) are no longer being released. was the final edition that we will produce for SE and SE1.  

MOS Note:742060.1 Release Schedule of Current Database Releases does reflect this extension already:

Release Patching Ends

Notes and Exceptions* 31-Aug-2016



Friday Sep 04, 2015

Oracle non-CDB architecture may be desupported after Oracle Database 12.2

You may wonder about the headline of this blog post.
Haven't I blogged about this a long time ago?

Yes, I did. On January 22, 2015 - over 7 months ago:

But whenever in the past weeks this message has been refreshed and got a bit more precise (which I'd guess everybody appreciates). Now we are clearly saying that such a change won't happen with Oracle 12.2. Before we've said only "in a later release".

See the Oracle Database 12c Upgrade Guide

Deprecation Note non-CDB architecture

In case you'd like to explore the world of (at least) Oracle Single Tenant (no extra license required, available even with SE2) which I'd highly recommend you'll find some useful advice in this slide deck:

  • How Oracle Single Tenant will change a DBA's life 


    Thursday Sep 03, 2015

    Can you have Oracle Multitenant in Oracle SE2?

    Please find all our articles about Oracle Database Standard Edition SE2:


    Can you have Oracle Multitenant in Oracle Standard Edition SE2?

    No, you can't as Oracle Multitenant is a licensable option for Enterprise Edition (EE) databases only.

    But wait a second ...

    You can do Oracle Single Tenant with SE2 of course meaning you can have one active pluggable database within one container database at a time. That is possible and does not require any additional licenses.

    See the DBCA (Database Configuration Assistant) screen when you'll try to create a container database:

    DBCA - Single Tenant

    More information? 

    You'll find more information about the differences in handling and such between Oracle non-CDB, Oracle Single Tenant and Oracle Multitenant databases in this presentation:


    Wednesday Sep 02, 2015

    No patches anymore for Oracle Database

    Just in case you've missed the date please be aware:

    No bug fixes, no patches, no CPU/SPUs, no PSUs, no BPs will be produced for Oracle Database since Augugst 28, 2015

    I know from the many emails I'm receiving that people are a bit disturbed since the Oracle Lifetime Support Policy brochure (Lifetime Support Policy and Brochure for Technology Products ) doesn't talk explicitly about patch sets but offers support for Oracle 11.2 until January 2018.

    Oracle 11.2 Lifetime Support Policy

    But this - as always - applies to the terminal patch set only - i.e. Oracle Database

    For a detailed description please see MOS Note:742060.1 and my previous blog posts from:

    I have visited customers with hundreds of Oracle databases in the past months. If you are now sitting there with one or many or many many Oracle instances don't even think of moving to Oracle The amount of work you'll spend in evaluating Oracle is exactly the same as testing Oracle Move to Oracle Database - NOW!

    Waived Extended Support for Oracle Database will end in less than 5 months.

    And keep in mind: there's no such thing as a 2nd release anymore. Every patch set release is a full release - no matter if we talk about or Both have new features, new behavior etc etc.


    Tuesday Sep 01, 2015

    Oracle Database Standard Edition (SE2) available for download

    Oracle SE2Finally ...

    Oracle Database Standard Edition (SE2) is available for download.

    Some information is already available in the Oracle Database Licensing Information. And you'll find more here: https://www.oracle.com/database/standard-edition-two/index.html

    Quoting from the pricing document:

    "Oracle Database Standard Edition 2 may only be licensed on servers that have a maximum capacity of 2 sockets. When used with Oracle Real Application Clusters, Oracle Database Standard Edition 2 may only be licensed on a maximum of 2 one-socket servers. In addition, notwithstanding any provision in Your Oracle license agreement to the contrary, each Oracle Database Standard Edition 2 database may use a maximum of 16 CPU threads at any time. When used with Oracle Real Application Clusters, each Oracle Database Standard Edition 2 database may use a maximum of 8 CPU threads per instance at any time. The minimums when licensing by Named User Plus (NUP) metric are 10 NUP licenses per server."


    Download Oracle Database Standard Edition (SE2) on OTN

    ( - Standard Edition (SE2)

    Download Microsoft Windows x64 (64-bit) File 1, File 2  (2.6 GB)
    Download Linux x86-64 File 1, File 2  (2.5 GB)
    Download Oracle Solaris (SPARC systems, 64-bit) File 1, File 2  (2.7 GB)
    Download Oracle Solaris (x86 systems, 64-bit) File 1, File 2  (2.5 GB)
    Download HP-UX Itanium File 1, File 2  (3.3 GB)
    Download AIX (PPC64) File 1, File 2  (2.9 GB)
    Download zLinux64 File 1, File 2  (2.5 GB)

    And on the Oracle Software Cloud (edelivery.oracle.com) which is the recommended download location:

    Edelivery.oracle.com SE2

    This location has been a corrected download link pointing to OTN as well:



    For a good overview about first experiences with Oracle Database Standard Edition (SE2) please see Franck Pachot's excellent blog post here:

    Please find all our articles about Oracle Database Standard Edition SE2:



    Upgrade downtime credited to APEX

    What do you think when you see this post-upgrade result?

    Oracle Database 12.1 Post-Upgrade Status Tool           08-07-2015 15:08:26

    Component                               Current         Version  Elapsed Time
    Name                                    Status          Number   HH:MM:SS

    Oracle Server                          UPGRADED  00:19:26
    JServer JAVA Virtual Machine              VALID  00:10:52
    Oracle Workspace Manager                  VALID  00:01:52
    OLAP Analytic Workspace                   VALID  00:00:34
    OLAP Catalog                         OPTION OFF  00:00:00
    Oracle OLAP API                           VALID  00:00:42
    Oracle XDK                                VALID  00:01:07
    Oracle Text                               VALID  00:01:36
    Oracle XML Database                       VALID  00:03:55
    Oracle Database Java Packages             VALID  00:00:22
    Oracle Multimedia                         VALID  00:03:57
    Spatial                                UPGRADED  00:08:56
    Oracle Application Express                VALID  00:46:19
    Final Actions                                                    00:03:48

    Total Upgrade Time: 01:44:16

    I've got a bit worried as the time to upgrade APEX took 44% of the complete database upgrade downtime. APEX (Oracle Application Express) is a fantastic piece of software which is still completely underrated - potentially because it is for free for everybody who has an Oracle Database license. And things not costing anything are just worth nothing, ey? 

    Simply be aware when you have APEX in your databases installed - and especially if you ACTIVELY use APEX - it may be a very good idea to upgrade APEX upfront without causing downtime for your entire database.

    See this blog post here about how to upgrade APEX upfront:



    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? 


    Thursday Aug 20, 2015

    Upgrade nach Oracle Database 12c - alles ganz einfach!

    Sorry - German only - if you seek for a very similar presentation in English please find it here:

    Danke noch mal an meinen Kollegen Frank Schneede, der das erste Webinar rund um 12c eingeführt hat. 

    Das Replay meines Seminar-Teils ist hier in Deutsch verfügbar:

    Viel Spass :-)


    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 databases, and only - 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 (http://www.pyka.de/) 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".


    Monday Aug 17, 2015

    DBCA 12c and "datapatch.pl" - things to know

    A few weeks ago I did blog about the DBUA (Database Upgrade Assistant) not executing 'datapatch' (i.e. not applying the SQL changes involved with a SPU/PSU/BP) automatically:

    Again, please note that this behavior DOES NOT APPLY to command line upgrades done with catctl.pl - as you can see from this somewhat disturbing messages during the upgrade in phase 65 and phase 69 (which are not errors but just informational messages for datapatch's execution):

    Datapatch phases command line upgrade catctl.pl

    But afterwards I have learned that things are worse.
    The same behavior is true when you create a database.

    Not a typo.
    You create a fresh database with DBCA (Database Configuration Assistant), you are a honest customer, you have followed our advice and applied the most recent PSU (or SPU or BP) into your Oracle Home. You don't even deploy one of the preconfigured databases but use the CREATE CUSTOM DATABASE option of DBCA. And the database will run from the patched home - but the SQL changes haven't been applied to it


    DBCA does not call 'datapatch' for database changes.

    I consider this even worse than the DBUA behavior as the person who upgrades a database in most cases is aware of the future home. But the person who either deploys a new database or asks for one to be deployed is often not identical with the person who did patch the homes.

    And there's no warning displayed yet nor (afaik) is there a MOS note available talking about it.

    How do you fix the issue?

    After creating a new database make sure to run:

    cd $ORACLE_HOME/OPatch
    ./datapatch -verbose

    and double check with DBA_REGISTRY_SQLPATCH view:

    order by BUNDLE_SERIES;

    Actually to be 100% you may find some patch information in DBA_REGISTRY_SQLPATCH showing that the JAVAVM patch has been applied in case you've installed the Combo version of the PSU. But you'll miss the database changes.

    Related Blog Posts datapatch.pl:


    Addition - Sept 18, 2015:

    Please be aware that the same thing happens on ODA (Oracle Database Appliance) with the oakcli. This will be fixed in the version (ODA/oakcli)

    Friday Aug 14, 2015

    Oracle Databases and Microsoft Windows 10

    MS Windows 10If you have upgraded already or plan to upgrade to Microsoft Windows 10 in the near (or later) future, you may find this Statement Of Direction by Oracle regarding Microsoft Windows 10 certification quite useful:


    In summary:

    • Oracle plans to certify Oracle Database by October 2015
    • Oracle plans to certify the next major version of the database as well on MS Win 10 - 64bit

    Tuesday Aug 04, 2015

    Other people's thoughts: "Should you upgrade to 12c?"

    You still don't believe me yet when I say: You can't seriously wait for the so called 2nd release of Oracle Database 12c as it will be an entirely new release again? And you haven't upgraded yet to Oracle Database for various reasons? 

    Then sometimes it's good to listen to other people's opinions - and I'm happy to share this 6 minute video by Tim Hall (very well known for his great page oracle-base). Listen to Tim and his Thoughts about Upgrading to Oracle Database 12c. And don't get disturbed that he's driving on the wrong side of the road ;-)

    And I promise I didn't bribe Tim (cocktails don't count) ;-)

    Thanks Tim!!! 


    Thursday Jul 30, 2015

    SQL Monitoring - Limitation at 300 lines per statement

    One of the best parts of my job at Oracle:
    I still learn something new every day.

    Yesterday I've learned from my colleague from Oracle Switzerland, Thomas Teske, that SQL Real Time Monitoring has an embedded default limitation on the number of lines in the statement. If the limit (default: 300 lines) is exceeded the statement won't be monitored. We both work with a leading Swiss company and we wanted to monitor a complex plan. 

    Now you may think: Who the heck has statements longer than 300 lines?
    Well ... sometimes that is beyond your influence as in this particular case this is of course done by the application.


    SQL> alter system set "_sqlmon_max_planlines"=800 scope=both;

    or set in your spfile:


    This limitation is described in:

    MOS Note:1613163.1
    How to Monitor SQL Statements with Large Plans Using Real-Time SQL Monitoring?

    If you'd like to read a bit more about SQL Real Time Monitoring please follow one of these links - and be aware that it's part of the Tuning Pack license and VERY helpful in many everyday situations. You'll have to have STATISTICS_LEVEL either TYPICAL (the default) or ALL and CONTROL_MANAGEMENT_PACK_ACCESS='DIAGNOSTIC+TUNING' (the default as well).



    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


    Tuesday Jul 28, 2015

    Optimizer Issue in Oracle "Reduce Group By"

    Wrong Query Results BugDBAs biggest fears I'd guess are Optimizer Wrong Query Results bugs as usually the optimizer does not write a message into the alert.log saying "Sorry, I was in a bad mood today ..."

    The Oracle Database Optimizer is a complex piece - and in Oracle 12c it delivers great performance results. Plus (my personal experience when you know what to do) it is more predictable which I like a lot when changing databases from one to another release. But due to its complexity sometimes we see issues - and sometimes it is necessary to switch off tiny little pieces until a fix is available.

    Roy just came across this one - and we believe it's worth to tell you about it. Again, our intention is only to prevent issues when upgrading or migrating to Oracle Database


    An outer join query with a bind variable and a group by clause can produce wrong results in some cases.


     If all of the following match, you may be hitting this bug:
     - two or more subquery views are outer-joined on column C1
     - column C1 is specified on select list of top-most query block
     - column C1 is filtered on a bind value


     create table test1(c1 number(5),c2 varchar2(16));
     insert into test1 values(1,'3');

     set NULL NULL
     variable num1 number
     execute :num1 :=1;

     -- Following query retuns wrong result(NULL), this should return 1.

     select V.c1 from
      (SELECT c1 FROM test1 GROUP BY c1) V,
      (SELECT c1 FROM test1 WHERE c2 = '1' GROUP BY c1) V2
     where  V.c1 = :num1
        and V.c1 = V2.c1(+);


    alter session set "_optimizer_reduce_groupby_key" = false;

    Please don't use the workaround:
    alter session set optimizer_features_enable='';
    as this will switch off other good optimizer features working very well.

    More information:

    See MOS Note:20634449.8 describing:
    Bug 20634449 - Wrong results from OUTER JOIN with a bind variable and a GROUP BY clause in

    As far as I can see there are no interim (one-off/single) patches available right now. 



    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:

    - -


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