By Mike Dietrich-Oracle on May 06, 2016
Thanks again to Laura for this interview at Collaborate 2016
Why you need to Upgrade to Oracle Database 12c - NOW!
It's time for a new and improved version of our team's preupgrd.sql (comes with the preupgrade package utluppkg.sql).
Please always download and use the most recent version from:
as this version is 3 years newer than the one you'll get with a fresh install of Oracle Database 184.108.40.206
Great news - and very important. Once you apply the most recent April 2016 PSU or BP (recommended - please see here: April 2016 PSU/BP are here!) then you'll get the most recent version of the preupgrd.sql with utluppkg.sql as well automatically.
No need to download it separately anymore once you are on this PSU or BP.
Nice little best practice for statistics and Data Pump when doing either Transportable Tablespaces or Full Transportable Export-Import (credits to Roy and Dean Gagne).
First of all we always recommend to exclude statistics when doing a Data Pump export as the import of such stats takes way longer than transporting them via a stats table. If you are unfamiliar with transporting stats between databases please see the Oracle Performance Tuning Guide with a nice tutorial:
The basic steps to transport statistics from one database to another fast and efficient consist of:
For the regular Data Pump exports we always recommend to set:
to avoid performance penalties during impdp.
But this does not affect Transportable Tablespaces and Full Transportable Export/Import.
For reasons I don't know the metadata heterogeneous object for "transportable" is different than all of the others Therefore in order to exclude statistics for Transportable Tablespaces and Full Transportable Export/Import you must set:
Keep this in mind during your next migration
As of today a new version of our upgrade tool preupgrd.sql (including the package utluppkg.sql) for upgrades to Oracle Database 220.127.116.11 is available as download from MOS:
Download it and exchange the existing preupgrd.sql and utluppkg.sql in your current Oracle 18.104.22.168 ?/rdbms/admin directory.
There's nothing more annoying than unwanted and useless email newsletters in your inbox.
But sometimes I wish to have an alert about important MOS Note changes, recent additions to my favorite notes, updates to the bugs I monitor etc. I would like to know about helpful new MOS articles - when they get published
And this is what you need to do (and I'll have to apologize for the small pictures but our fancy, modern blog design template does not allow to include larger pictures ...).
Once you've logged in you'll click on MORE and then navigate to SETTINGS.
Here it is important that you choose "Send with Selected Options" first and then "HTML" as otherwise the email does not seem to get sent ("Text" option does not seem to work).
Of course you may add more products - I chose only the "Database" in my example.
*** AND DON'T FORGET TO HIT THE "APPLY" BUTTON FURTHER DOWN ON THE SCREEN ***
Then it's important to check your current "Favorites". Click on the yellow/orange STAR in the upper menu close to the search field and select "Documents". If "Documents" does not get displayed there's no need to clean up as you haven't marked anything as favorite yet. But in my below example you see that I have a lot of very old docs in my list.
So I will clean them up first.
Click on "Manage Favorites" via the yellow/orange STAR and mark all unwanted documents by using either the usual SHIFT or STRG keys. Hit "Remove".
In my case the list is empty now.
The important work is now to mark certain documents or bugs as FAVORITES. In the below example I'd choose the most important MOS Note 161818.1 via the plain ID search (right side of the screenshot), and once the note gets displayed I'll push the withe STAR on the upper left corner above the document - the STAR will turn yellow/orange.
Once I've did that I will find this note now in my list of favorite documents:
Repeat this with the notes you'd like to get informed about changes. For instance I monitor always the "Known Issues and Alerts" for Oracle Database 22.214.171.124 and 126.96.36.199:
Once you've did that you'll get an email per day with the recent changes - and it will contain information about the updates in your fav notes.
It's hard to keep track about recent changes and updates to important MOS Notes. And it's even harder to learn about newly published articles in your areas of interest. The HOT Topics email feature in MOS is pretty cool and extremely helpful. I like it a lot.
But you'll have to enable it first in your settings as MOS does not want to spam you.
Credits to Roy who dug out all the tiny pieces such as the HTML switch
Whenever you attempt to upgrade an Oracle database to Oracle Database 188.8.131.52 please get the most recent version of preupgrd.sql, the preupgrade check script, (including the utluppkg.sql package) from MOS Note:884522.1 first.
The new version has many improvements over the previous one - and it is more than 2 years "younger" then the one you'll get with a standard installation of Oracle Database 184.108.40.206. It will give you also good advice when you are planning a migration.
Almost every day I get at least one email by somebody asking things such as ...
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:
And a nice overview table clearly showing the Patching End Dates as well:
MOS Note:742060.1 is clearly THE REFERENCE in terms of questions such as:
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:.
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 ...
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:
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.
The files contained a ton of such messages:----- Cursor Obsoletion Dump sql_id=5p8a9d4017bq3 -----
After doing a bit of research I came across this document and an explanation:
Well, we introduced an Enhancement - via an unpublished bug (and I'd guess it is undocumented then) in Oracle Database 220.127.116.11 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" = N;
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 18.104.22.168 is 1 meaning this will happen after every single invalidation.
Just be aware that the underlying cursor sharing problem needs to be investigated - always.
If you have cursor sharing issues you may set this parameter higher therefore not every invalidation causes a dump, then investigate and solve the issue, and finally switch the parameter to 0 once the issue is taken care of.
Please be aware that switching the parameter to 0 will lead to a lack of diagnostics information in case of cursor invalidations.
Friday, Oct 16, 2015, Oracle announced that the Extended Support for Oracle Database 22.214.171.124 will be waived until May 31, 2017. After this period of Waived Extendend Support, Extended Support for Oracle Database 126.96.36.199 will be offered until end of December 2020.
This information can be found here:
Something to mention:
*** Updated on Nov 6 to reflect 188.8.131.52 changes***
There were a lot of questions regarding Oracle Database Standard Edition 184.108.40.206 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 220.127.116.11 SE2:
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:
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.
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;
set timing on
execution_name=>'Compare workload Elapsed',
'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.
Please find all our articles about Oracle Database Standard Edition 18.104.22.168 SE2:
Some questions regarding support for Oracle Database Standard Edition 22.214.171.124 (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
Full patching support for 126.96.36.199 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 188.8.131.52 SE2, so through until end August 2016.
After that period Oracle Database 184.108.40.206 will enter Sustaining Support. There won't be any Extended Support for Oracle Database 220.127.116.11 in any edition.
Yes, quarterly Patch Set Updates and Critical Patch Updates for 18.104.22.168 will continue to be delivered until end of Premier Support for Oracle Database 22.214.171.124.
Beginning with the release of Oracle Database 126.96.36.199, Oracle Database Standard Edition (SE) and Oracle Database Standard Edition One (SE1) are no longer being released. 188.8.131.52 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:
Notes and Exceptions*
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".
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:
Please find all our articles about Oracle Database Standard Edition 184.108.40.206 SE2:
No, you can't as Oracle Multitenant is a licensable option for Enterprise Edition (EE) databases only.
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:
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:
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.
The Support Note for Heterogeneous Data Guard Configurations explains which combinations are allowed beginning with specific releases:
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.
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 220.127.116.11:
The key action is to recreate the controlfile:
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.
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:
You plan to upgrade your database(s) to Oracle Database 18.104.22.168,?
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.
SET SERVEROUTPUT ON;
SET SERVEROUTPUT OFF;
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.
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.
In both cases the preupgrade package @?/rdbms/admin/utluppkg.sql from either your 22.214.171.124 $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:
SQL> SELECT COUNT(*) FROM SYS.OBJ$ o, SYS.COL$ c, SYS.COLTYPE$ t WHERE o.OBJ# = t.OBJ# AND c.OBJ# = t.OBJ# AND c.COL# = t.COL# AND t.INTCOL# = c.INTCOL# AND BITAND(t.FLAGS, 256) = 256;
PS: Credits to my colleagues Cindy and Agrim for providing tech background and the MOS Note
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):
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.
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.
and double check with DBA_REGISTRY_SQLPATCH view:
select PATCH_ID, PATCH_UID, VERSION, STATUS, DESCRIPTION
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.
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 126.96.36.199 (ODA/oakcli)
Please find a more current blog post about certification of Oracle Database 12c on MS Win 10 here:
If 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:
Addition [Oct, 22, 2015]:
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 188.8.131.52 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 184.108.40.206 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
For all the skeptical people (Germans especially) let me add that in Oracle Database 220.127.116.11 the Grid Infrastructure Management Repository (GIMR) is not mandatory - but its existence will be mandatory for a future upgrade to Grid Infrastructure 12.2.
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 18.104.22.168 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)
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 events.oracle.com!