Wednesday Apr 01, 2015

Upgrade Workshop incl Hands-On in Vienna/Austria on April 9./10. - still some seats available :-)

Just in case you'll would like to participate in an Upgrade and Migration Workshop including Hands-On in the area in and around Vienna, Austria :-)

I'll deliver an Upgrade workshop in the Oracle Office (IZD-Tower, 3. Stock - Wagramer Straße 19 - 1223 Wien) next week on April 9 and 10. It will include Hands-On so you'll have to bring your laptop with Oracle Virtual Box installed and roughly 40 GB of free space to copy an image into your environment.

Just in case you'd like to participate ... here's the registration link which has more details about the agenda and the requirements:


SAP is now certified on Oracle Database

ORACLE and SAPSAP certified Oracle Database as of March 31, 2015!

Yes, it's true. Our colleagues in the Oracle/SAP/CompetenceCenter in Walldorf worked really hard to complete the certification for SAP with Oracle Database But finally it happened. Actually it was announced long time before as planned. And it happened right in time.

Please find the official documents here:

SAP Service Marketplace ==> Products ==> Installation & Upgrade Guides ==> Database Upgrades (login required) ==> Oracle 

And here's the link to the official announcement:

Great job by a excellent Oracle team in Walldorf - and please no excuses anymore from anybody saying that you can't certify your application on Oracle Database If SAP can do it, everybody can do it :-)

If you are looking for more information and useful documents please see this discussion here - scroll down a bit (thanks to Upgrade Expert Andreas Becker from the Oracle-SAP-CC in Walldorf):



I'm expressing my sincere condolences as one of the 5 founders of SAP, Klaus Tschira, died on March 31, 2015 way too early at the age of 74 years. May he rest in piece!

Thursday Mar 19, 2015

Migration of an EM Repository cross-platform?

Can you migrate your EM Cloud Control Repository to another OS platform? Cross-platform and cross-Endianness?

This question sounds so incredibly simple that you won't even start thinking I guess. Same for ourselves. Use Data Pump. Or Transportable Tablespaces. Or Full Transportable Export/Import if your source is at least or newer.

But sometimes in life things seem to be simple, but as soon as you unmask them you'll find a full bunch of issues. It's a fact that the repository of EM Cloud Control is quite a bit complicated. And uses plenty of databases technologies. 

Actually all credits go to Roy here as he has worked with the EM group for the past 6 months on this topic.

You can migrate a EM Cloud Control Repository cross-platform but not cross-Endianness (e.g. HP-UX to OL, big to little Endianness). The latter is scheduled to be supported in EM 13.2.



As EM Cloud Control Repository migrations is possible right now only within the same Endianness group you should decide carefully where you store your EM Cloud Control Repository.



Friday Mar 13, 2015

EM Cloud Control (or newer) OMS Repository certified with Oracle Database plus patches

The certification for Oracle Database as Repository Database underneath of Oracle Enterprise Manager Cloud Control (OMS) had been revoked for a while. One reason may have been the potential removal of the SYSMAN user during a DBUA upgrade ;-) But this is just a rumor. I don't know the exact reasons.

After checking with the certification matrix earlier today I realized:

Cloud Control Repository Database Certification Oracle

Oracle Database is certified as a repository database for Cloud Control resp. OMS.

But ... with a few important restrictions (see MOS Note:1987905.1 - 12c Database has been Certified as a 12cR4 Repository with Certain Patchset Restrictions):

  • Database PSU October 2014 ( or newer
  • Database Patch 20243268 on top of the PSU
    • Bug20243268:


PS: Thanks for the anonymous comment ... I wasn't aware of the MOS Note and updated the patch requirements

Tuesday Mar 10, 2015

Another Upgrade Workshop in Reston on March 31!

For those who were unable to attend the previous Database Upgrade workshop in Reston, VA (just outside DC), we have added another event in three weeks on Tuesday, March 31. This time we will have the nice auditorium with a larger capacity, so we should be able to accommodate everybody. If you would like to sign up, please see the evite at 

You will notice that this time the registration is being handled via telephone, to avoid the problems we had last time with the email system.  If you have any problems registering, please let me know.

So, apologies to anybody who was shut out last month, and I hope we get a nice big audience in three weeks! 

Monday Mar 09, 2015

Applying a PSU or BP to a Single-/Multitenant Environment

I have already explained in broad details a while ago how to:

But one may miss the steps for applying a PSU (Patch Set Update) or BP (Bundled Patch) to a Single-/Multitenant Environment. At first everything will work just the same if you choose the Everything-at-Once strategy as datapatch will adjust all the required things regardless of being executed in a stand-alone or a singe/Multitenant environment.

But what happens if you apply a PSU or a BP to one of your Multitenant environments and want to move PDBs one after another (or a few at the same time) to the new environment?
Or revert a PSU by plugging out from a CDB with the PSU inside - and plug it back into a CDB with a lower version or no PSU at all? 

First step - Check Plug In Compatibility 

Before you can even start your unplug/plug operation you should always perform the plugin check. This is divided in two simple steps:

  1. Create the XML description file for your PDB in CDB_SOURCE
    exec DBMS_PDB.DESCRIBE ('/home/oracle/PDB1_unplug.xml', 'PDB1');
  2. Run the plug in check in CDB_DEST
      if DBMS_PDB.CHECK_PLUG_COMPATIBILITY('/home/oracle/PDB1_unplug.xml','PDB1') then  
      DBMS_OUTPUT.PUT_LINE('No violations found - you can relax');
      DBMS_OUTPUT.PUT_LINE('Violations found - check PDB_PLUG_IN_VIOLATIONS');
    end if;

No Plugin Violations?

Then please follow the procedure described in:
without the upgrade part as you don't need to upgrade anything in this case of course. 

Higher patch in CDB_DEST than in CDB_SOURCE?

Then run this query:


It will tell you to execute datapatch:

------  ----------------------------------------------------------------------------
ERROR   PSU bundle patch 1 (PSU Patch 4711): Installed in the CDB but not in the PDB

Call datapatch to install in the PDB or the CDB

Lower patch in CDB_DEST than in CDB_SOURCE?

Now this becomes a bit more tricky. See the output of PDB_PLUG_IN_VIOLATIONS:

----- ----------------------------------------------------------------------------
ERROR PSU bundle patch 1 (PSU Patch 4711): Installed in the PDB but not in the CDB

Call datapatch to install in the PDB or the CDB

Huh? Install???
What does this mean? Should I install now the current CDB/PDB's PSU into my target environment before being able to step down? 

Actually I think this message is misleading. And when you look into the MyOracle Support Note describing this under scenario 3 (MOS Note:1935365.1 - Multitenant Unplug/Plug Best Practices) you'll see that the author silently assumed as well that is is more likely that you'll remove the patch from the PDB. 

But how do you remove changes which came in with datapatch from within a PDB only?

You will need to run datapatch -rollback on the affected PDBs only:

$> datapatch -rollback <patch id> –force [–bundle_series] -pdbs <pdb1,pdb2,...,pdbn>

For further information see:


Tuesday Mar 03, 2015

There is still time to register for next week's Upgrade Workshop in Omaha!

Upgrade Workshop Banner

While the event in San Francisco is a sell-out (metaphorically speaking -- Upgrade Workshops are of course free of charge), we do still have room at next week's seminar in Omaha on Wednesday, March 11:

Oracle Database 12c Upgrade Seminar

This full-day event at the Embassy Suites in La Vista will include discussions of many ways to upgrade and migrate to Oracle Database 12c. I will also talk about Oracle Multitenant and other new features. We also include a guide for how to ensure you will have good performance after the upgrade.

I have visited Omaha as a tourist before, but this is my first upgrade workshop in the area. I hope to see you there! 

ORAchk with new checks + Restart support


Never used or heard about ORAchk? Then it's time to give it a try! 

ORAchk has now be released. ORAchk has so many great features, especially more than 50 new health checks, OL7 Support - and is now aware of Oracle Restart environments.

New features include:

  • Linux on System Z (RHEL 6, RHEL 7, SuSE 12)
  • Oracle Enterprise Linux 7
  • Single Instance ASM Checks
  • Upgrade Validation checks for
  • Enhanced Golden Gate Checks 
  • Enterprise Manager Agent Checks
  • Enhanced Reporting to highlight checks that ORAchk cannot gain full visibility for
    (checks that require manual validation)
  • Improved health Score Calculation (you can now hit 100%)
  • Over 50 new health checks
  • Bug Fixes

Download the most recent version via MOS Note:1268927.1.

Learn more about the Upgrade Readiness Assessment with ORAckh in MOS Note: 1457357.1


PS: Updated the post on Mar 7, 2015, as ORAchk became production!

Monday Mar 02, 2015

New Behaviour in Oracle Database 12c and SELECT ANY DICTIONARY with reduced privilege set

You've just upgraded to Oracle Database 12c - but your favorite admin tool receives an ORA-1031: Insufficient Privileges after connection?

Then the reason may be the reduced set of privileges for the SELECT ANY DICTIONARY privilege. This privilege does not allow access to tables USER$, ENC$ and DEFAULT_PWD$, LINK$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, and XS$VERIFIERS. Actually such changes are not new. For instance in Oracle 10.1 we removed the access to  LINK$ in SELECT ANY DICTIONARY (well, this may have happened because the dblink's password was stored in clear text in LINK$ - a misbehavior which is fixed since Oracle 10.2).

Please be very careful with granting this privilege. Furthermore, you need to be aware that it can't be granted either through a role, nor is it included in the GRANT ALL PRIVILEGES.

Oracle 11.2:

Oracle 12.1:

Documentation can be found here: 

  1. SELECT ANY DICTIONARY Privilege No Longer Accesses Some SYS Data Dictionary Tables
    For better security, the SELECT ANY DICTIONARY system privilege no longer permits you to query the SYS schema system tables DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, and XS$VERIFIERS. Only user SYShas access to these tables, but user SYS can grant object privileges (such as GRANT SELECT ON USER$ TO sec_admin) to other users.
  2. Increased Security When Using SELECT ANY DICTIONARY

Please be aware that you can't query anywhere inside the database which privileges are included in the SELECT ANY DICTIONARY privilege as this is embedded in our code.


PS: Credits go to Andy Kielhorn for highlighting this to me and thanks to Gopal for providing me with the doc links

Friday Feb 27, 2015

Why you seriously can't wait for the second release!

Premier Support for Oracle 11.2 has ended 4 weeks ago at 31-January-2015. 
Read here if you didn't know or don't believe it.

I think most Oracle DBAs are aware of it. And I have stressed the topic about the need to upgrade to Oracle Database a lot in the past months via the blog, in workshops and in discussions and customer meetings.

But there are still plenty of people out there you would like to wait for Oracle Database 12.2, the so called "second" release. From looking backwards I can understand this thinking. Neither Oracle 9.0 nor Oracle 10.1 were the best and most stable releases ever. If you waited a while then you could expect at least the first or sometimes even the 2nd patch set for the SECOND release being available. And then most people started going live. And some did wait for the terminal patch set.

But this has changed. You can't seriously wait anymore for the second release. Why? There are several reasons and it's fairly easy to explain.

  • Reason 1 - Every release is a full release
  • Reason 2 - Every release has a significant number of changes
  • Reason 3 - Every release has a significant number of new features
  • Reason 4 - Oracle is the TERMINAL patch set 
  • Reason 5 - The time span between releases has grown to large
  • Reason 6 - Important application providers will certify Oracle


Especially the Reason 5 is very important. You can't seriously wait for Oracle Database 12.2 as you will potentially see a period with no bug fixing support for Oracle 11.2. 

So let's be honest:
You don't wait for the second release. You'll wait at least for the first patch set.
Patch Sets in the past got released roughly 12 months after the initial drop has been put out (please don't get this wrong: I'm not saying that will be released 12 months after - I just try to project from the past!).

We announced already the planned availability of Oracle 12.2 for H1CY16 (first half of calender year 2016).
See the Release Schedule MOS Note:742060.1for further details.

And keep in mind that this is a plan and no fixed schedule. So let's project the usual patch set cycle from the past. Then we may be in the 2nd half of 2017. If you start your tests (I hope you'll test :-) ) by then you may be ready to go live in 2018 - and Extended Support for Oracle will end 31-January-2018.

Ouch ...

Look at the release cycles:

Oracle Release Cycle

It has grown from 18 months in the past to 45 months for Oracle Database For Oracle Database 12.2 we may be at 30-36 months based on the currently announced plan.

Any further questions?

Be smart and transform from "We'll go live on the 2nd release only" into "We'll go live with the current release's first or terminal patch set!". This will be Oracle Database There's a reason why large application providers such as SAP will announce support for Oracle Database soon.


Thursday Feb 26, 2015

Oracle In-Memory Advisor with Oracle Multitenant? Issues?

Do you want to use the Oracle In-Memory Advisor in an Oracle Multitenant environment?

This is possible but with the current version it will require a workaround. The next available version of the IM Advisor is designated to support it without any workarounds.

At the moment the IM Advisor does not install into the CDB$ROOT container. But it can be installed into any PDB.

  • Create the user IMADVISOR in the PDB as local user first
  • No objects must be placed into the IMADVISOR schema but the user must have a default and a temporary tablespace
  • Install the IM Advisor locally in this PDB by using the install script imstimadv.sql
    • The script will detect the presences of the IMADVISOR schema. If the IMADVISOR schema is empty, it will proceed. It will then ask you to press ENTER instead of prompting you to set the password and default tablespace. Unless you setup the IMADVISOR user with an automated authentication method, it will still prompt you for the IMADVISOR's password when it connects to the IMADVISOR user

An issue which I learned about in the past days:

ORA-24817 get raised when using the IM Advisor.'s imadvisor_fetch_recommendations script unless you do not have a massive shared pool configured. The script does a SET LONGCHUNKSIZE 2000000000; and this needs to be reduced.
There has been a bug filed for it and I think it's supposed to be fixed in the next release of the IM Advisor.


Tuesday Feb 24, 2015

Oracle In-Memory Advisor now available

Oracle In-Memory is such a great feature - but often the challenge you'll face is the tricky question:
Which of your tables and/or partitions should you mark for In-Memory column store availability? 

And the answer is now easier to find as the new In-.Memory Advisor is available via download from 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

In case you have obtained a pre-release version of the Advisor, please discard it and replace it with the production version 


Monday Feb 23, 2015

Upgrade the Operating System in a RAC Environment

Last week at the upgrade workshop in Budapest a customer had a interesting and - I believe - not uncommon question.

How can I upgrade my Linux Operating System in my RAC environment without taking the entire cluster down?
In this specific case the customer wanted to upgrade from RHEL 5.10 to RHEL6 or RHEL7.

Let's assume it's the typical 2-node-RAC where one wants to upgrade in a rolling fashion. And the data is stored within ASM.

  1. Drain Node 1 (i.e. take the workload off) - this will be the node getting upgraded first
  2. Remove Node 1 from the  cluster (deleteNode procedure)
  3. Upgrade the OS (which is most likely a reimaging of the node). If the OS upgrade does not wipe out the entire server you can follow MOS Note:1559762.1 as it shows an OS upgrade from OL 6.2 to 6.4 which leave the Oracle installations intact) 
  4. Add Node 1 back to the cluster (addNode procedure)
  5. Extend the Database Home to Node 1 using either cloning or addNode
  6. Make the database available on the newly added Node 1 and drain Node 2
  7. Repeat steps 2-6 for Node 2 
  8. Ideally now you'll perform a rolling upgrade of Oracle GI to Oracle Database 12c. Please apply the most recent PSU as well.
  9. Furthermore you may now look into upgrading your databases to Oracle Database as well.
See the following documentation: 

Friday Feb 20, 2015

Upgrade Workshops coming in March in North America

The days are getting longer, the snow is still getting deeper, and the upgrade workshops keep coming in North America! Remember that you can always go to and plug in a keyword of "upgrade" to find upcoming live events. Roy will be the speaker at some of these events, while others will be delivered by some of our top presales technical folks like Dan Wittry:

March 5: Oracle 12c Database Upgrade Seminar-Detroit (Troy, MI)

March 11: Oracle Database 12c Upgrade Seminar - Omaha (La Vista, NE)

March 12: Oracle Database 12c Upgrade Seminar - Redwood Shores, CA Sold out!

March 17: Oracle 12c Database Upgrade Seminar-Calgary

March 19: Oracle Database 12c Upgrade Seminar - Seattle (Bellevue, WA)

March 19: Oracle Database 12c Upgrade Seminar Tampa

As you can see, we do sometimes reach capacity and have to shut down registrations. If you are interested in any of these events, sign up now and don't be left out in the cold (literally in the north, or metaphorically in other regions).

Downgrade Oracle Restart 12c - Grid Infrastructure only?

Oracle RestartCan you downgrade your Oracle Restart installation from Oracle 12c back to Oracle 11g?

Actually there's no real direct downgrade supported for Oracle Restart. But of course there's a way to do it.

Basically it is: 

  • Deconfigure Oracle Restart in 12c
  • Configure Oracle Restart in 11g 

But wait a minute. It is very important to know if you have upgraded your database already. If that is the case then first you MUST downgrade your database(s) as you can't manage a higher version Oracle Database with a lower version Clusterware.

So first of all, please downgrade your Oracle database(s) first: 

At the next stage you'll need to "downgrade" the Oracle Clusterware resp Grid Infrastructure for Oracle Restart: 

Before you attempt this you'll need to deconfigure the Restart resources - and please be aware that here's a small difference in commands between Oracle and Oracle

This is from the documentation for Oracle

  • Deconfigure Oracle Restart:
    • Log in as root
    • cd /u01/app/
    • -deconfig -force
  • Once this is complete you can now deinstall the Grid Infrastructure with the deinstall tool
  • Then you will need either to reinstall the previous - for instance Oracle - Grid Infrastructure or - if it's still present on the machine - reconfigure it by running from the previous Clusterware's home
  • And finally reconfigure the database(s) again with Clusterware
    • $ srvctl downgrade database -d db-unique-name -o oraclehome -t to_version

In case you'd plan to do this exercise back from Oracle instead then you'll have different steps to follow to deconfigure Oracle Restart

  • Deconfigure Oracle Restart
    • # cd /u01/app/
    • # /u01/app/ /u01/app/ -deconfig


PS: See bug18160024 or the GI install guide, section A11.4 for the Standalone GI downgrade instructions from 

Wednesday Feb 18, 2015

Grid Infrastructure PSU Jan 2015 - Am I too intolerant?

Germans are not only known for being precise and timely - but sometimes also for being too direct. Well, Roy could tell you stories ... and I always honor his politeness :-)

Enough about stereotypes. I work with a customer at the moment on their 12c upgrades. And I did recommend the most recent PSU (Patch Set Updates) for their Grid Infrastructure environments running Oracle Restart. Same of course for the database homes but this blog post will just name some findings I've had the other night when trying to apply the January 2015 GI PSU to my Oracle Restart environment.

First of all, start here:

  • Drill down from MOS Note:161818.1 (Click on the Release Link for 12.1.0!) into
  • Oracle 12.1 Support Status and Alerts MOS Note:1565065.1 and further into 
  • Patch Set - Availability and Known Issues MOS Note:1683799.1 to get
    • Recommendations for patches on top of Oracle Database - and of course for Grid Infrastructure (GI), Engineered Systems and Database In-Memory as well.

For GI you'll get the following recommendations:

Grid Infrastructure

Document Description Rolling RAC Patch Download
Note:20132450.8 Combo of OJVM PSU and GI PSU (Jan 2015) Part Patch:20132450
Note:19954978.8 (Jan 2015) Grid Infrastructure Patch Set Update (GI PSU) Yes Patch:19954978

Well, first question:
Do you need the Combo Patch or the non-Combo? 

My personal recommendation: Take the non-Combo Patch as the combo patch includes not only the GI PSU, but also the Database PSU, the OJVM Patch and some other things you will not need for patching your Grid Infrastructure only. Of course I can see the benefit of downloading just one big piece and applying everything all together to my environment. But first of all parts of the patch (speaking of OJVM) are not rolling applicable - and second I'd like to control and script things separately. But please feel free to see and do this in a different way.

So I did download Patch:19954978 to my environment. Unzipped it. 

You all know it already - you will need a new OPatch!

Of course my OPatch version is too old. You will need at least OPatch - the most recent one is OPatch - and you simply download it via MOS patch 6880880 and install it after removing the old directory into your $ORACLE_HOME. 

First learning experience? OPatch doesn't do anything without a response file.

The reason for this (at least for me as I don't patch daily) new requirement seems to be the new opatchauto call which scripts the entire apply process in a silent way. Well, at least the patch readme tells me what to do. Please read MOS Note:966023.1 to learn about how to create this response file.

If you do not have the OCM response file (ocm.rsp), see the following My Oracle Support Document 966023.1 How To Create An OCM Response File For Opatch Silent Installation.

I did create it with OCM OFF as I don't see a benefit in my environment for using OCM. And just on the side: I was a bit worried that this note does not contain the new opatchauto syntax but instead lists an example (which is always good and nice and helpful) from the old days:

$ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /u02/unconfig.rsp


Second learning experience? Analyze analyzes ... not everything!

In the readme under 2.2 you'll find also the requirement to run an anylze for conflict detection and resolution first. Maybe I'm too naive as I'm so happy with all the stuff ORAchk (credits for this gem of a tool go to Sandesh Rao's team - I will write something about it later) can deliver - and expected too much. In my case the anylyze signaled: All good, sky is bright and nice and clear - ready to fly!

#GRID_HOME/OPatch/opatchauto apply <UNZIPPED_PATCH_LOCATION>/19954978 -analyze -ocmrf <ocm response file>

You'll read below why my assumption was incorrect.

Third learning experience? An error is an error is an error!

Can you expect a patch to get applied correctly in the first run?

opatchauto apply /oradata/grid/12.1.0/19954978 -oh /oradata/grid/12.1.0 -ocmrf /oradata/grid/12.1.0/OPatch/ocm/bin/emocmrsp 

I did. Well ... mine failed. Must be Murphy's Law See the log below - I have marked the failing part in red.

2015-02-16_19-45-09 :
Failed to run this command :
/oradata/grid/12.1.0/OPatch/opatch napply -phBaseFile /tmp/OraGI12Home1_oracle_patchList -local  -invPtrLoc /oradata/grid/12.1.0/oraInst.loc -oh /oradata/grid/12.1.0 -silent -ocmrf /oradata/grid/12.1.0/OPatch/ocm/bin/emocmrsp
Command "/oradata/grid/12.1.0/OPatch/opatch napply -phBaseFile /tmp/OraGI12Home1_oracle_patchList -local  -invPtrLoc /oradata/grid/12.1.0/oraInst.loc -oh /oradata/grid/12.1.0 -silent -ocmrf /oradata/grid/12.1.0/OPatch/ocm/bin/emocmrsp" execution failed:
UtilSession failed:
Prerequisite check "CheckSystemSpace" failed.

Log file Location for the failed command: /oradata/grid/12.1.0/cfgtoollogs/opatch/opatch2015-02-16_19-45-03PM_1.log

2015-02-16_19-45-09 :

--------------After fixing the cause of failure you have two options shown below:
Run 'opatchauto resume'
Manually run the commands listed below

/oradata/grid/12.1.0/OPatch/opatch napply -phBaseFile /tmp/OraGI12Home1_oracle_patchList -local  -invPtrLoc /oradata/grid/12.1.0/oraInst.loc -oh /oradata/grid/12.1.0 -silent -ocmrf /oradata/grid/12.1.0/OPatch/ocm/bin/emocmrsp (Run as oracle) - (TRIED BUT FAILED)

Now the question is:
How much space does it really require to apply this PSU? 

At this point I was wondering as the analyze passed successfully - and I couldn't find anything in the readme about specific space requirements. My disk had roughly 8GB of free space - and as my GI Restart installation's footprint was around 6GB I don't had any bad thoughts. 

Forth learning experience? Always see the logfile first ...

Just rerunning the mentioned command gave me the correct information (just wondering why OPatch couldn't tell me this during the failed run?). So I did execute as oracle user:

/oradata/grid/12.1.0/OPatch/opatch napply -phBaseFile /tmp/OraGI12Home1_oracle_patchList -local  -invPtrLoc /oradata/grid/12.1.0/oraInst.loc -oh /oradata/grid/12.1.0 -silent -ocmrf /oradata/grid/12.1.0/OPatch/ocm/bin/emocmrsp

Receiving finally this result:

Verifying environment and performing prerequisite checks...
Prerequisite check "CheckSystemSpace" failed.
The details are:
Required amount of space(12773.858MB) is not available.
UtilSession failed:
Prerequisite check "CheckSystemSpace" failed.
Log file location: /oradata/grid/12.1.0/cfgtoollogs/opatch/opatch2015-02-16_19-49-04PM_1.log

Ouch - 12.7GB of free space??? Really? Seriously?? Now I was scared. 

The patch for Linux x86-64 has a tip size of 873 MB - but unzipped it takes 3.3 GB. So why does it require 12.7 GB of free space? Actually I don't know the answer yet but I will follow up here as soon I know the details. Colleagues in Development ensure that especially on AIX you will need even more free space, such as in the area of 22GB!!!

One of the reasons for such a huge space requirement may be this:

Starting with 10.2, Opatch does not backup only the affected modules, it also takes a backup of the complete affected libraries to $ORACLE_HOME/.patch_storage/<patchid>/backup/<lib_directory_name>/<library_name>.


Fifth learning experience? There are workarounds ...

Blogs are sometimes VERY helpful. I found two helpful entries from external bloggers (but didn't bookmark them so I can't credit them here - sorry).

/oradata/grid/12.1.0/OPatch/opatch napply -phBaseFile /tmp/OraGI12Home1_oracle_patchList -local  -invPtrLoc /oradata/grid/12.1.0/oraInst.loc -oh /oradata/grid/12.1.0 -silent -ocmrf /oradata/grid/12.1.0/OPatch/ocm/bin/emocmrsp OPatch.SKIP_VERIFY_SPACE=true

Of course you can't give this variable at the end to opatchauto as it wouldn't understand.

Final learning lesson? Clean up is a great idea!

This is not as simple as I did expect it. Of course, it's not. But please see this MOS Note:550522.1 - How To Avoid Disk Full Issues Because OPatch Backups Take Big Amount Of Disk Space

Famous Last Words?

Patches are great. In fact YOU NEED TO PATCH. Take the PSUs. There's no way out as it will help you to avoid plenty of known issues. But I hope that my above learning experience may help you to sail around one or the other pitfall :-)


Monday Feb 16, 2015

Oracle Fail Safe 4.1.1 released - supports Multitenant

Oracle Fail SafeOracle Fail Safe 4.1.1 is now released. It will be included in future Oracle Database 12c media packs.  Customers can download the kit and documentation from the Oracle Technology Network (OTN) now by starting at the main Fail Safe page.  

The main changes in this release are:

  • Basic multi-tenant (container database, or CDB) support
  • This release of Oracle Fail Safe adds support for the container database feature that was introduced in Oracle Database 12c. Fail Safe will recognize that a database is a root container and will start and stop individual pluggable databases owned by the container database. When a database is failed over or moved to another node in the cluster, Oracle Fail Safe will start each pluggable database using the state that was saved by the last SQL ALTER PLUGGABLE DATABASE ALL SAVE STATE command. Oracle database 12c patch set 1 ( is required to have the ability to save the state of the pluggable databases. 

  • Improved net (TNS) configuration
  • Cluster Shared Volume (CSV) support

 All documentation for this release, including the release notes, can be found here.


Friday Feb 13, 2015

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

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

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

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

First the elimination is OFF (FALSE): 

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

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

And now it's ON (TRUE):

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

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

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

At the moment we'd recommend to set: 



Hands-On-Lab "Upgrade, Migrate & Consolidate to Oracle Database 12c" available for DOWNLOAD now!

Wow ... that was a hard piece of work. Roy put a lot of effort into getting our Hands-On-Lab on OTN for download. We promised to have it available after OOW - or at least a description how to create it by yourself. And finally it's there. Find it here:

A few important things to mention before you start the download: 

  • It's a Virtual Box image
  • You will need to install Oracle Virtual Box first - and please install also the VBox Extensions
  • Your PC must have a 64-bit host operating system
  • You need to enable Virtualization options in your computer's BIOS
  • You PC should have at least 4GB of RAM - having 8GB is very helpful
  • A fast disk (SSD) will speed up things
  • The instructions are available for download but are included in the download as well
  • The lab will guide you through the following tasks:
    1. Upgrade an database to Oracle
    2. Plug in this database into a CDB
    3. Migrate an database with Full Transportable Export into another PDB
    4. Unplug an PDB and plug/upgrade it into an CDB

You'll find a picture as screen background inside the VBox image always giving you guidance about "what to accomplish" and "how to switch environments".

Enjoy :-)




Monday Feb 09, 2015

Virtual Technology Summit with Hands-On Labs!

VTS Virtual Technology Summit

If you are at all interested in upgrade to Oracle Database 12c, you really should sign up for the Virtual Technology Summit for February. This is a "pseudo-live" event with recorded webcasts and live experts online to answer questions. Even better, it includes a hands-on lab that will take you through the process of:

  1. Upgrading from to
  2. Plugging that upgraded database in as a PDB
  3. Migrating a second database to a second PDB using the new Full Transportable Export/Import feature

The Virtual Technology Summit will run three times: 

and it has content for developers and sysadmins as well as DBAs.

So, sign up now and get a head start by downloading the hands-on lab VM image here:

We think you will find the VM environment really useful to keep around for those times when you want to try something out, without having to install binaries or create databases. 


Friday Feb 06, 2015

New MOS Note:1962125.1 - Overview of Database Patch Delivery Methods

Usually I don't announce MOS Notes but this one may be very helpful to sort out between all the different patches available these days for the database only. From Patch Sets to PSUs to SPUs to Interim Patches to Bundle Patches and so on.

Plus it includes also recommendations for testing and if you should apply them on a regular basis.

A very important MOS Note for most DBAs:

MOS Note:1962125.1
Overview of Database Patch Delivery Methods

MOS Note - Patch Delivery Methods


Thursday Feb 05, 2015

Oracle Multitenant: New SQL Container Clause

Tiny little enhancement in Oracle Database
The new CONTAINER clause to access data from different containers within one SQL statement. This may be very helpful, especially in case of schema consolidation. Similar things could have be done in Oracle already by using database links - but resulting in way more complicated SQLs.

This is the new clause: 

SELECT ename FROM CONTAINERS(scott.emp) WHERE CON_ID IN (45, 49); 

See the documentation for more info about it.


Tuesday Feb 03, 2015

How to migrate to Unified Auditing?


What is Unified Auditing and is it on by default?

Unified Auditing is the new auditing facility since Oracle Database 12c. But the "old" auditing is still working. And there are a few things to mention if you'd like to make the right choice. I have written some things about it a while ago but as I discovered yesterday my previous blog post (  doesn't satisfy all my needs.

The initial motivation to move towards the new Unified Audit trail is audit performance. The audit records will be written into the read-only table AUDSYS in SYSAUX tablespace. But there are other benefits such as no dependency on init.ora parameters, one location - one format, and close interaction with Oracle Audit Vault and Database Firewall. And of course tiny things such as the immediate write, which avoids losing any audit records during an instance crash.

Audit records are coming from those sources:

  • Audit records (including SYS audit records) from unified audit policies and AUDIT settings
  • Fine-grained audit records from the DBMS_FGA PL/SQL package
  • Oracle Database Real Application Security audit records
  • Oracle Recovery Manager audit records
  • Oracle Database Vault audit records
  • Oracle Label Security audit records
  • Oracle Data Mining records
  • Oracle Data Pump
  • Oracle SQL*Loader Direct Load 

In addition to user SYS all users having the roles AUDIT_ADMIN and AUDIT_VIEWER can query the AUDSYS table.

After upgrade to Oracle Database 12c Unified Auditing is not enabled by default in order to prevent customers having "old" auditing on already from enabling both auditing facilities at the same time. This is something you need to be aware of: Unified Auditing can be on together with the "old" auditing at the same time

Check if Unified Auditing is linked into the oracle kernel;


----------------  ---------- 
Unified Auditing  FALSE

To link it into the kernel or enable it use the following commands/actions - and the documentation states that you'll have to shut down the listener and restart it again afterwards:

  • UNIX
    • cd $ORACLE_HOME/rdbms/lib
      make -f uniaud_on ioracle
  • Windows
    • Rename the file %ORACLE_HOME%/bin/orauniaud12.dll.option to %ORACLE_HOME%/bin/orauniaud12.dll

The tricky part is now that - even though Unified Auditing is not enabled by default - Unified Auditing is enabled in a Mixed Mode, i.e. there are two auditing policies enabled - but the option is not linked into the kernel.

To disable these policies you'll execute:

  • SQL> noaudit policy ORA_SECURECONFIG;
  • SQL> noaudit policy ORA_LOGON_FAILURES;

Don't get me wrong: This is not a recommendation to disable Unified Auditing. I just would like to explain what's on and the possibilities to turn things into the desired direction. The documentation says about Mixed Mode:

Mixed mode is intended to introduce unified auditing, so that you can have a feel of how it works and what its nuances and benefits are. Mixed mode enables you to migrate your existing applications and scripts to use unified auditing. Once you have decided to use pure unified auditing, you can relink the oracle binary with the unified audit option turned on and thereby enable it as the one and only audit facility the Oracle database runs. 

How do you enable a Unified Auditing Policy?

The documentation offers a straight forward tutorial (which is a bit EM driven):

How to change between IMMEDIATE and QUEUED WRITE mode?

For a performance evaluation please see Szymon's blog post at the CERN blogs. To switch between the different modes please see the Oracle Documentation:

  • To use immediate write mode use this procedure:
  • To use queued write mode run this procedure:

The size of the queue by default is 1MB. If you'd like to change it (maximum: 30MB) the initialization parameter UNIFIED_AUDIT_SGA_QUEUE_SIZE has to be changed.

What happens now to the traditional AUDIT_TRAIL parameter and what effect does it have?

AUDIT_TAIL will still trigger and direct the "old" auditing facilitiy (SYS.AUD$ for the database audit trail, SYS.FGA_LOG$ for fine-grained auditing, DVSYS.AUDIT_TRAIL$ for Oracle Database Vault, Oracle Label Security, and so on). So be aware to have both auditing facilities on at the same time as this won't make much sense. Our recommendation since Oracle Database 11g is generally to set AUDIT_TRAIL in every 11g/12c database explicitly to the value you want. Otherwise it could always happen (and happens many times) that your database accidentally writes audit records into AUD$. Reason why this happens so often: the default setting for AUDIT_TRAIL since Oracle Database 11g is "DB" unless you change this via the non-standard parameter listening in the DBCA (Database Configuration Assistant).

Therefore always set AUDIT_TRAIL explicitly to the value you want to prevent the database from accidental auditing.

Summary - Steps to migrate to Unified Auditing?

  1. Turn off traditional auditing with AUDIT_TRAIL=NONE
  2. Link Unified Auditing into the kernel or enable it on Windows
  3. Define your auditing policies 
  4. Monitor it with the views UNIFIED_AUDIT_TRAIL and in multitenant environments with CDB_UNIFIED_AUDIT_TRAIL
A final question remains unanswered:
What happens to your existing audit records in AUD$ and the other auditing tables?

Actually I can't answer this question but to me there seems to be no way to migrate existing audit records into the new Unified Auditing facility. But I don't think that this will cause any issues as you can keep and safely store the contents of the traditional auditing. They don't get overwritten or deleted during an upgrade.

Further information required?


Thursday Jan 29, 2015

Upgrade to 12c and Plugin - one fast way to move into the world of Oracle Multitenant

What is the best way to move into Oracle Multitenant (which includes Single Tenant with just one PDB) as well?

This question came up on the blog, it's part of the Multitenant chapter in our big slide deck but let me elaborate this a bit more in detail. Of course there are several ways to move a database into a Single/Multitenant environment such as Data Pump but one fact is for sure: You can't convert a stand-alone database to become a container database (CDB$ROOT). A container database has to be built up from scratch as first step. Please use the DBCA to create it as this is the easiest path.

Upgrade and Plugin

But how can your stand-alone database be moved now?

  1. Upgrade your stand-alone database to Oracle Database 12c, ideally to Oracle with the newest PSU included already. Upgrade it to exactly the same version including the same PSU as your container database (CDB) got created from.
  2. Then start your stand-alone database in read-only mode - of course at this step you'll encounter downtime:
    startup open read only;
  3.  Generate the XML description file – this file will contain the information describing the database structure. To create it the database has to be in read only mode:
    exec DBMS_PDB.DESCRIBE('/tmp/pdb1.xml');
  4. Shutdown the database
    shutdown immediate
  5. Change into your new CDB and do a compatibility check:
    compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '/tmp/pdb1.xml', pdb_name => 'PDB1') WHEN TRUE THEN 'YES' ELSE 'NO'

  6. Plugin your stand-alone database (in my case with a new name PDB1):
    create pluggable database PDB1 using '/tmp/pdb1.xml' nocopy tempfile reuse;
    Be aware that using the NOCOPY option will require that you have a valid backup of your database. If you'll use the COPY option instead you will need additional disk space. When using NOCOPY files will remain at their location. When using COPY you'll combine it with the FILE_NAME_CONVERT option, converting source to destination paths.
    It's always a recommendation to keep the TEMP tablespaces. In case you'd like to remove then you'll have to drop the tablespace and the tempfile before. Otherwise there will be a reference in the XML manifest file making it impossible to plug in your new PDB.

  7. Connect to this new PDB1 and perform sanity operations:
    alter session set container=PDB1;

    This script will do required sanity operations to connect the PDB with the CDB. It will do things such as change object id's, mark common objects etc. Its runtime can vary between 5 minutes and several hours depending on the complexity of the former stand-alone's database data dictionary. It will do recompilations of many invalid objects as well. Please test it carefully. 
    If this step gets skipped the PDB can be open in read-only mode only.
  8. ----

  9. Now the database is plugged in – but not open yet. It will need to be started.
    show pdbs

  10. To connect to the consolidated PDB1 from the command prompt the following command syntax needs to be used:
    sqlplus "sys/oracle@pdb1 as sysdba"
    As an alternative you could use the EZconnect syntax:
    sqlplus "sys/oracle@//localhost:1521/pdb1 as sysdba"


Thursday Jan 22, 2015

Non-CDB architecture of Oracle databases is DEPRECATED since Oracle Database

Beginning with Oracle Database a non-CDB architecture is deprecated.

non-CDB deprecated in Oracle


What does this mean?

Deprecation first of all does not mean "desupported". It means in this case that you of course can have still stand-alone Oracle databases as you know the architecture from previous releases. But you can also have a single-tenant deployment (a CDB with one PDB - no Multitenant license required) or multitenant databases (a CDB with up to 252 PDBs - Multitenant Option license required).

But in a future Oracle release single-tenant and multitenant databases only may be allowed. Right now there's no information available when this might happen. --

Which features are not supported at the moment?

  • Database Change Notification
  • Continuous Query Notification (CQN)
  • Client Side Cache
  • Heat Map
  • Automatic Data Optimization
  • Oracle Streams
  • Oracle Fail Safe
  • Flashback Pluggable Database (Flashback Database works but will flashback CDB$ROOT including all PDBs)
  • Data Recovery Advisor (DRA)
  • Flashback Transaction Backout 



From the Upgrade Guide:
By deprecate, we mean that the feature is no longer being enhanced but is still supported for the full life of the 12.1 release. By desupported, we mean that Oracle will no longer fix bugs related to that feature and may remove the code altogether. Where indicated, a deprecated feature may be desupported in a future major release


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

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite or remotely with reference customers. Acting as interlink between customers/partners and the Upgrade Development.

Follow me on TWITTER

Contact me via LinkedIn or XING


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