Friday Nov 06, 2015

Oracle Database Release Status - MOS Note:742060.1

Almost every day I get at least one email by somebody asking things such as ...

  • What is the current status of Oracle 
  • Is Oracle Standard Edition One under Extended Support's treatment?
  • Do I get bug fixes for Oracle
  • And is it really true that I don't get bug fixes for Oracle anymore???

All valid questions - and all of them get answered within this reference note called:

Why am I writing this?

Simply because the Lifetime Support Policy Brochure only has now a link to a central MOS note added - but no explanatory text such as "Full Extended Support coverage will only apply to the terminal release". And the link is added in the right bottom corner underneath the table on (real) page 6 (the document's pagination doesn't count pages 1 and 2 as 1 and 2 for whatever reason). 

MOS Note:742060.1 has now an updated chart displaying clearly the Support Periods:

Oracle Release Chart

And a nice overview table clearly showing the Patching End Dates as well:

Oracle Patching End Dates for the Database

MOS Note:742060.1 is clearly THE REFERENCE in terms of questions such as:

  • How long will I get bug fixes for release X?
  • When is Premier Support ending for release Y?
  • Do I get Waived Extended Support for release Z?

Tuesday Oct 27, 2015

OOW 2015 - Upgrade and Migrate to Oracle 12c Talk - Live and Uncensored - Get the Slides

Thank You, Thank You, Thank You!!!

Roy and I were extremely happy yesterday as the room was full. Totally sold out. I've read on Twitter that some people wanted to go in and weren't allowed to join as the room capacity was reached.

If you want to get the few slides for our first talk:

And thanks again - you were an awesome audience! And we are looking forward to see you in one of our two other talks:.

  • Our 2nd talk will happen on Wednesday at 12:15pm (skip the unhealthy lunch).
    We'll contrast some nasty things with some very good and detailed customer examples included in it. Real world examples, no artificial lab exercises. Againuncensored (mostly <img src=" title=";-)" style="border: none;" /> ) and just from first hand experience.

    How to Upgrade Hundreds or Thousands of Databases in a Reasonable Amount of Time [CON8375]
    Mike Dietrich, Master Product Manager, Oracle
    Roy Swonger, Sr Director, Software Development, Oracle

    Wednesday, Oct 28, 12:15 p.m. | Moscone South—102
  • The 3rd talk of our group is the Data Pump  Performance Tips and Tricks talk delivered by Data Pump experts from Development.

    Deep Dive: More Oracle Data Pump Performance Tips and Tricks [CON8376]
    Dean Gagne, Consulting Member of Technical Staff, Oracle
    Jim Stenoish, Senior Director, Software Development, Oracle

    Thursday, Oct 29, 9:30 a.m. | Moscone South—305


Monday Oct 26, 2015

OOW 2015 - if you've missed our Upgrade Hands On Lab

Ready to kick off with the first of our four Hands-On-Labs at OOW15 at the Nikko Hotel. Currently it's calm before the storm  ...

OOW 2015

But just in case you didn't grab a seat at one of the labs then please come to our talk today (Monday!) at 1:30pm in Moscone South 102 as we'll show you the core parts of the lab - and you'll be able to download it later on from the blog and run it by yourself.

Download the lab via this link:


Tuesday Oct 20, 2015

Where do these large trace files come from in Oracle 12c?

Just had an observation about very large trace files on one of my customers I'm working with at the moment. When I write "very" I mean "VERY" as some grew over 10GB within a few hours.

Growth - Trace Files - (c) Mike Dietrich

The files contained a ton of such messages:

----- Cursor Obsoletion Dump sql_id=5p8a9d4017bq3 -----
Parent cursor obsoleted 1 time(s). maxchild=1024 basephd=00007FFB8AD45CB0 phd=00007FFB8AD45CB0

After doing a bit of research I came across this document and an explanation:

MOS Note:1955319.1;
Huge Trace Files Created Containing "----- Cursor Obsoletion Dump sql_id=%s -----"

Well, we introduced an Enhancement - via an unpublished bug (and I'd guess it is undocumented then) in Oracle Database to improve cursor sharing diagnostics by dumping information about an obsolete parent cursor and it's child cursors after the parent cursor has been obsoleted N times.

You can control this behavior by altering the value for:

alter system set "_kks_obsolete_dump_threshold" = 0;

Possible value range is 0..8 whereas 0 means: switch the obsolete cursor dump off completely, and other values (N) defined the number of invalidations after which the cursor will be dumped.

The default in Oracle Database is 1 meaning this will happen after every single invalidation

Just be aware that the underlying cursor sharing problem needs to be investigated always.


Saturday Oct 17, 2015

Extended Support Fee for Oracle waived until May 31, 2017 - Extended Support until Dec 2020

Friday, Oct 16, 2015, Oracle announced that the Extended Support for Oracle Database will be waived until May 31, 2017. After this period of Waived Extendend Support, Extended Support for Oracle Database will be offered until end of December 2020.

This information can be found here:

Something to mention:

  • There's no bug fixing support for Oracle Database or below available anymore
  • If you'll compare the effort to upgrade to Oracle Database to Oracle you'll have to do the exact same amount of work. tests etc - no difference
  • UPDATE [Oct 20, 2015]
    Now MOS Note:742060.1 has updated as well but only the text, not the graphic yet. Usually this will take another few extra days as the owner of the Note is not in charge of the graph's creation. Please be patient ;-)


Thursday Sep 17, 2015

SE2 - Some questions, some answers ...

Oracle Standard Edition SE2*** Updated on Nov 6  to reflect changes***

There 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 Aug 31, 2016. After this date (as of Sep 1, 2016) Oracle 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:
    Is the extension of the Waived Extended Support and the Paid Extended Support for Oracle valid for SE and SE1 as well?
  • A:
    Yes, it is - there's actually no distinction (as far as I know) between support periods for SE, SE1 and EE.
  • 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:
  • 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. And physically removing 2 CPUs from a 4-socket-machine does not satisfy the license agreement.
  • 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.


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 - sqlpatch

This (and another) message breaks the nicely structured format of the 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


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:


    Monday Aug 24, 2015

    Migration IBM AIX ==> SPARC Solaris with Data Guard

    Can we migrate our database with Oracle Data Guard?

    We are getting this question asked quite often during our workshops or via email. And if you are staying within the same operating system family (such as Red Hat 5.8 to OL 7) all is fine, and this is one of the best and most simple approaches to jump between servers. Even when you add a subsequent database upgrade all is very straight forward.

    But what if you mix operating systems?

    The Support Note for Heterogeneous Data Guard Configurations explains which combinations are allowed beginning with specific releases:

    MOS Note: 413484.1
    Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration

    The most popular combinations are Windows to Linux or Intel Solaris to Linux beginning with Oracle 11.1. One combination often requested was IBM AIX to Oracle SPARC Solaris. And we did support this for a few weeks - a white paper got published - and taken away shortly after, removing the support as it turned out that the control file had 5 or 6 pieces which were highly OS dependent. There were rumors circling around you still could do it with a bit of manual extra work.

    Migration Oracle on AIX to SPARC SOLARIS

    Now (ok, since end of March 2015), surprise-surprise, there's an official MOS Note available explaining how to migrate away from IBM AIX to SPARC Solaris by using a Physical Standby Database beginning with Oracle

    MOS Note: 1982638.1
    One Time Migration Steps from IBM AIX Power to Solaris SPARC using Data Guard

    It is based on the MOS Note:469493.1 (Step By Step Guide to create a Physical Standby Database using RMAN Backup/Restore).

    The key action is to recreate the controlfile:

    To recreate a new primary control file, use the following procedure:
    a) On the open production, connect as SYSDBA, and issue "alter database backup controlfile to trace;"
    b) In the trace file for this session you will see two sets of DDLs to create a new backup control file. Use the set of DDLs that create the control file with the NORESETLOGS option (Set # 1)
    c) Shutdown the production database

    SQL> shutdown;

    d) Run the commands in the trace file starting with 'startup NOMOUNT', 'CREATE CONTROLFILE REUSE .. NORESETLOGS ..', 'RECOVER DATABASE', etc..

    And how about Big to Little Endian platforms?

    As the redo stream is highly OS dependent I don't think that we'll see combinations such as HP-UX to OL in the near future - just my feeling. But with the offering of Full Transportable Export/Import and the help of RMAN Incremental Backups (see this presentation in our Slides Download Center about it: ) we have very strong alternative in place.


    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 :-)


    Wednesday Aug 19, 2015

    Invalid Table Data before Upgrade to

    Oracle Database Upgrade 12c

    You plan to upgrade your database(s) to Oracle Database,?

    You did run the preupgrd.sql including the preupgrade package in your current database already?

    But in the preupgrade.log you'll see the following ERROR:

    ERROR: --> Invalid Oracle supplied table data found in your database.

         Invalid data can be seen prior to the database upgrade
         or during PDB plug in.  This table data must be made
         valid BEFORE upgrade or plug in.

       - To fix the data, load the Preupgrade package and execute
         the fixup routine.
         For plug in, execute the fix up routine in the PDB.

        exec dbms_preup.run_fixup_and_report('INVALID_SYS_TABLEDATA')

    Where does it come from?

    First of all it's important to know that you can't upgrade your database without resolving this error condition before. If you'd ignore it you'll see the magic universal ORA-1722: invalid number error indicating that one of the mandatory checks in the upgrade scripts had failed

    For more information on type evolution check the Oracle Database 12c Object-Relational Developer's Guide

    What does this warning mean? 

    The error results from a failed check for table data in columns of evolved types. Those must be upgraded before the database can be upgraded, otherwise they will be considered as "invalid" data. The same thing can happen when you try to plugin a stand-alone (non-CDB) database making it a pluggable database. The sanity script noncdb_to_pdb.sql will also check for this condition.  

    And how do you solve the ERROR

    • For oracle-supplied data, the conversion would be done by:
      exec dbms_preup.run_fixup_and_report('INVALID_SYS_TABLEDATA');

    • For user data, the conversion would be done by:
      exec dbms_preup.run_fixup_and_report('INVALID_USR_TABLEDATA');

    In both cases the preupgrade package @?/rdbms/admin/utluppkg.sql from either your $ORACLE_HOME or from the download via MOS Note: 884522.1 must be loaded into your database - it has been loaded already if you'd execute the preupgrd.sql

    You shouldn't run the fixups on a live production system during normal operation but only right before upgrade as the fixups run ALTER TABLE DDL commands. Column metadata and data related to the object columns/tables that contains data of older version types will be updated. 

    The below query will check how many of the tables/columns (oracle-supplied and user data) are affected


    Further Information?

    MOS Note: 2009405.1
    ORA-01722: invalid number SELECT TO_NUMBER(‘INVALID_TABLEDATA’) while upgrading to 12c


    PS: Credits to my colleagues Cindy and Agrim for providing tech background and the MOS Note 

    Monday Aug 17, 2015

    DBCA 12c and "" - 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 - 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

    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


    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

    Addition [Oct, 22, 2015]:




    Wednesday Aug 05, 2015

    Grid Infrastructure Management Repository (GIMR) - Datapatch?

    I have blogged about the Grid Infrastructure Management Repository (GIMR) a while back:

    And Markus Michalewicz, our Director of Product Management, Oracle Real Application Clusters (RAC), has published a very interesting and helpful insight article about GIMR on July 30, 2015. Read it here:

    Since Oracle Database the GIMR database will be created by default - and it is a single tenant database having a CDB$ROOT and one active PDB. 

    Recently the question came up if - in the likely event of applying a PSU or BP to the GI Home - you'll have to run datapatch manually to adopt the SQL changes for the PSU/BP into the GIMR database as well?

    Simple answer: No.

    SQL changes will be automatically applied to the GIMR database by default. This got introduced in Oracle with the PSU1 already and is tracked by ER BUG 14830129 - MGMT DATABASE PATCH ACTIONS NEED TO RUN DURING GI POST PATCH PHASE

    You can verify this by looking at your logs (Thanks Santosh!) - you should see something similar as:

    <grid_home>/cfgtoollogs/crsconfig/crspatch_xxxx file

    2015-07-15 15:36:51: Mgmtdb is running on node: racnode1; local node: racnode1
    2015-07-15 15:36:51: Mgmtdb is running on the local node
    2015-07-15 15:36:51: Starting to patch Mgmt DB ...
    2015-07-15 15:36:51: Invoking "/opt/oracle/app/12.1.0/grid/sqlpatch/sqlpatch -db -MGMTDB"
    2015-07-15 15:36:51: Running as user oracle: /opt/oracle/app/12.1.0/grid/sqlpatch/sqlpatch -db -MGMTDB
    2015-07-15 15:36:51:   Invoking "/opt/oracle/app/12.1.0/grid/sqlpatch/sqlpatch -db -MGMTDB" as user "oracle"
    2015-07-15 15:36:51: Executing /bin/su oracle -c "/opt/oracle/app/12.1.0/grid/sqlpatch/sqlpatch -db -MGMTDB"
    2015-07-15 15:36:51: Executing cmd: /bin/su oracle -c "/opt/oracle/app/12.1.0/grid/sqlpatch/sqlpatch -db -MGMTDB"
    2015-07-15 15:37:50: Command output:
    >  SQL Patching tool version on Tue Jul 15 15:36:51 2015
    >  Copyright (c) 2015, Oracle.  All rights reserved.

    >  Connecting to database...OK
    >  Note:  Datapatch will only apply or rollback SQL fixes for PDBs
    >         that are in an open state, no patches will be applied to closed PDBs.
    >         Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
    >         (Doc ID 1585822.1)
    >  Determining current state...done
    >  Adding patches to installation queue and performing prereq checks...done
    >  Installation queue:
    >    For the following PDBs: CDB$ROOT PDB$SEED CRS
    >      Nothing to roll back
    >      The following patches will be applied:
    >        20831110 (Database Patch Set Update : (20831110))

    >  Installing patches...
    >  Patch installation complete.  Total patches installed: 3

    >  Validating logfiles...done
    >  SQL Patching tool complete on Tue Jul 21 15:37:50 2015


    For all the skeptical people (Germans especially) let me add that in Oracle Database the Grid Infrastructure Management Repository (GIMR) is not mandatory - but its existence will be mandatory for a future upgrade to Grid Infrastructure 12.2.


    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!!! 


    Saturday Aug 01, 2015

    Upgrade workshop this week in Denver

    Denver workshop

    I am looking forward to my trip to the Denver area this week, including an upgrade workshop at the Oracle office in the tech center area. We have a good registration list so far but there is still room! If you would like to attend, just register at

    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).



    Wednesday Jul 29, 2015

    ORAchk - How to log SRs and ERs for ORAchk

    ORAchkI have recently recommended to use ORAchk in its new version - and I can just emphasize on this.

    During a conf call with a lead customer from the UK Roy and I learned about the uncertainness on how to log a Service Request (SR) or enhancement Request (ER) against ORAchk in case something is not working correctly or missing.

    Especially as the documentation of ORAchk states:

    Appendix B - How to Obtain Support

    If problems are encountered either at runtime or if there are questions about the content of the findings of the tool, please post your issues/questions/concerns to the ORAchk thread within the ORAchk Thread of the Scalability RAC My Oracle Support Community.

    Roy explored this topic in the past days and that is the outcome

    • Of course you can file SRs via MOS (and ERs as well).
      If a Support Engineer told you that the product is only supported via the Community Forum this is not correct. Just insist :-)
    • There's an official product ID (10655), component (ORACHK) and sub component (FRAMEWORK) for filing ORAchk bugs and enhancements internally in our bug database. But of course a customer will need to file an SR first via MOS.

    We hope this helps :-)


    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. 


    Friday Jul 24, 2015

    Why does a PDB require an upgrade?

    Why do I need to upgrade (or downgrade) a Pluggable Database (PDB) once I unplug it from version A and replug into version B?

    This question is one of the most often asked questions in our workshops when Roy and I present Upgrades and Oracle Multitenant.

    If you take a look into the documentation you'll find:

    But unfortunately this is not 100% correct for every object.

    When you do a simple query for basic dictionary structures such as OBJ$ you'll easily find out that a PDB has its own Data Dictionary.  Some things are really linked into the PDB only - check the SHARING column of DBA_OBJECTS for instance. If it says "METADATA LINK" it will give you an indication that it exchanges information with its parent in the CDB$ROOT. But that doesn't mean necessarily that it's just an empty meta object. Some tables do exist in the PDB as well and allocate space. Check it by yourself and you'll find out that OBJ$ inside a PDB has in fact the same attribute - but still owns segments in the PDB and in the CDB$ROOT - as other objects do allocate space as well. And of course the object's definition exists in the PDB as well. 

    • CDB$ROOT

      OBJ$ in CDB$ROOT

    • PDB 

      OBJ$ in PDB

    So the answer is simple:
    A Pluggable Database (PDB) must be upgraded (or downgraded) when it get moved between versions simply because it has its own data dictionary. And this doesn't get upgraded automatically.



    Monday Jul 20, 2015

    DBUA 12c and "" - things to know

    For clarification:
    The following blog post applies to upgrades to Oracle 12.1 done by DBUA only whenever a SPU/PSU/BP is installed into the 12.1 home prior to the upgrade (which I'd highly recommend as patching before upgrade saves you headache after upgrade!).

    Two customers independently reported last week that they have doubts on DBUA's ability to apply the required SQL changes associated with CPU/SPU or PSU

    First of all, let me tell you that this is not an issue when you do a command line upgrade to Oracle Database 12c with - you'll only need to take care when using the DBUA

    One claimed that this feature alongside with had been announced a while back:

    Oracle Premier Support - Oracle Database Support News
    Issue November, 2014 Volume 46
    (Doc ID 1954478.1)

    Which Patching Tools uses Datapatch ?

    • Opatchauto   
      • OPatchAuto calls datapatch automatically to complete post patch actions upon installation of the binary patch and restart of the database.
    • Enterprise Manager Cloud Control   
      • Starting version 12.1 EMCC now calls datapatch to complete post patch actions upon any 12c or later database restart
    • Upgrade   
      • and DBUA now call Datapatch during the upgrade process
    • OPatch  
      • Datapatch integration with OPatch is not possible as OPatch is executed when the database is down and datapatch requires the database to be open to complete its activity.

    The other customer provided all the logfiles - and I print the important logs only with the interesting part marked in RED:

    Contents of catupgrd_datapatch_upgrade.log
    SQL Patching tool version on Tue Jul 14 13:10:39 2015
    Copyright (c) 2014, Oracle.  All rights reserved.
    Connecting to database...OK
    Bootstrapping registry and package to current versions...done
    Determining current state...done
    Current state of SQL patches:
    Patch 19282028 (Database PSU, Oracle JavaVM Component ():
      Installed in the binary registry only
    Bundle series PSU:
      ID 1 in the binary registry and not installed in the SQL registry
    Adding patches to installation queue and performing prereq checks...
    Installation queue:
      Nothing to roll back
    Nothing to apply
    SQL Patching tool complete on Tue Jul 14 13:10:57 2015

    Contents of sqlpatch_catcon__catcon_22773.lst
    catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts
    catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any
            catconInit: start logging catcon output at 2015-07-14 13:10:39


    Ok, so it seems to be true that DBUA did not apply the post upgrade SQL changes associated with the most recent PSU.

    DBUA 12c

    Now digging a bit deeper we could solve the puzzle.

    The DBUA uses the " -x" option executing catuppst.sql (the post upgrade script) in a separate step whereas on the command line will execute catuppst.sql by default (tracked with bug19990037). The DBUA uses instead to execute catuppst.sql. In previous releases this was not an issue as catbundle.sql got automatically executed as part of catuppst.sql. But as is a PERL script, and a PERL script cannot be run from within a SQL script, catuppst.sql can no longer call the post-patching activities. The DBUA in misses this action as a separate task.

    Summary and Solution

    DBUA misses the post-upgrade datapatch execution in Oracle The solution is to apply the SQL changes manually after DBUA has completed the database upgrade to Oracle Database 12c:

    cd $ORACLE_HOME/OPatch
    ./datapatch -verbose

    And again, this is only necessary when you used the DBUA for a database upgrade. This step is not required for the command line upgrade. This will be fixed in an upcoming release of the DBUA.

    If you are in doubt whether the DBUA or the command line upgrade had been used, unfortunately you won't find any indication inside the database. But look into $ORACLE_BASE/cfgtoollogs/dbua/logs - if the "dbua" directory exists, the DBUA had been used. If not than the command line upgrade had been processed.

    Related Blog Posts


    Monday Jul 13, 2015

    Updated version of the In-Memory Advisor is available

    Which of your tables and/or partitions should you mark for In-Memory column store availability? 

    You'll get the answer with the help of the Oracle In-.Memory Advisor which just got updated and is available via MyOracle Support:

    The Advisor produces a report identifying the objects that should be placed In-Memory for maximum benefit, along with a SQL*PLUS script which implements those recommendations. It can be run on Oracle Database and above. And of course the recommendations can be implemented on Oracle Database (and newer).

    Important to know:
    The In-Memory Advisor is licensed as part of the Database Tuning Pack.

    Further information can be found at: OTN

    The New Features include:

    • Oracle Multitenant support
    • Enhanced analysis
    • and bug fixes


    See the RELEASE NOTES for further details on version 



    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:

    - -


    « November 2015
    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