Monday Nov 23, 2015

Network ACLs and Database Upgrade to Oracle 12c

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

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

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

(both paragraphs taken from MOS Note: 2078710.1)

What happens during/after upgrade?

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


Issues before/during Database Upgrade?

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

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

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

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


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

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

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

More Information?



Thursday Nov 19, 2015

Why you should remove APEX from the CDB$ROOT

Upgrade Blog posts about Oracle Application Express:

Oracle APEX (Application Express) is great piece of software. But it gets installed by default into the container database's CDB$ROOT unless you'd customized your CDB creation via scripts in Oracle See: 

on how to customize a Single/Multitenant Database with less options.

But that is not the topic I would like to write about. I came across an pitfall in the past days twice.

Think of having a local APEX (Application Express) application in a standalone database or already inside a PDB. You'd like to plug it into a CDB, regardless if this CDB is local or in the Oracle DBaaS Cloud. But your current APEX is on a lower version than the APEX inside the target CDB.

Let's assume you'd like to migrate this standalone database or PDB over into the cloud. But as soon as you plug it in - even though you'll have Oracle Database in both sites locally and in the cloud, you can't open your PDB in read/write mode. It will open RESTRICTED only.

Then you'll check PDB_PLUG_IN_VIOLATIONS and get a plug in error:

"APEX mismatch: PDB installed version CDB installed version" 

Reason is simply that an Oracle Database standard deployment will contain APEX 4.2.0 whereas the Oracle Cloud deployment in this scenario uses the more recent APEX version 5.0 already.

Having APEX installed into the CDB$ROOT was not a great idea. Actually it will cause you a lot of trouble and does not make much sense in the idea of Multitenant concept.

Oracle APEX Multitenant

I see why it has been done this way as not having APEX in the CDB$ROOT would have meant not having it in the PDB$SEED either - and all further provisioned PDBs would not have had APEX deployed by default. In this case every PDB would have required a separate APEX installation - but then you'd get the freedom of different APEX versions within the same Multitenant environment. And way more freedom during unplug/plug. But I'll elaborate this further down below.

Different APEX versions in Multitenant

First of all check your current APEX version with this query:


-------- ------------ -------

Find out in which container APEX is currently installed into:

SQL> select r.COMP_NAME, r.VERSION, c.NAME, c.CON_ID from CDB_REGISTRY r, V$CONTAINERS c where r.CON_ID=c.CON_ID and r.COMP_ID='APEX' order by CON_ID;

COMP_NAME                   VERSION   NAME      CON_ID
--------------------------- --------- --------- ------
Oracle Application Express  CDB$ROOT       1

Then choose the best solution for your environment to avoid trouble with APEX in a Multitenant environment.


Migrate a PDB with containing an APEX 4.2 deployment into another CDB with APEX 5.0 already installed in the CDB$ROOT container (the standard deployment in the Oracle DBaaS Cloud).

Two Solutions

Basically I see two potential solutions:

  1. Remove APEX from the CDB$ROOT before plug in your PDB with a different APEX version
  2. Upgrade your APEX installation in the PDB after plugging it into the CDB to match the CDB's APEX version

Solution 1 - Remove APEX from the CDB$ROOT 

I'd prefer Solution 1 as this will give you way more freedom. It will even allow you to have different APEX versions in the same Multitenant environment in different PDBs. But you'll have to do this steps right after creation of the container database - or before you worked with APEX in any of your PDBs as the following steps will remove APEX from all your PDBs!!!! If you already have APEX applications inside one of your PDBs you must export them before the APEX removal. Keep in mind that the "application export" facility in APEX 4 does not carry your image files automatically (APEX 5 is smarter).

Remove APEX from CDB$ROOT will remove it from all PDBs as well

The Oracle Documentation describes the path pretty straight forward. 

  1. Make sure to change into the local $ORACLE_HOME/apex directory first before starting SQL*Plus as otherwise the removal process won't work and error out - I'd assume that path variables are not carried on correctly. 
    cd $ORACLE_HOME/apex

  2. Connect to your CDB$ROOT:
    sqlplus / as sysdba

  3. Run the "Remove APEX from the CDB$ROOT" script:
    SQL> @apexremov_con.sql

  4. Check afterwards if APEX has been removed - also check for invalid objects. If necessary recompile. 
    In my tests two objects were INVALID after the removal but could be easily fixed
    (this part is not mentioned in the documentation):

    no rows selected

    SQL> select object_name, status from dba_objects where status='INVALID';

    OBJECT_NAME                      STATUS
    -------------------------------- -------------
    APEX_SPATIAL                     INVALID

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

    SQL> select object_name, status from dba_objects where status='INVALID';
    no rows selected

  5. Once you have done this your PDB containing the APEX application should not give you APEX errors anymore upon plugin as there's no mismatch anymore between CDB$ROOT's and the PDB's APEX.

  6. Just in the case - independently of my example - you'd like to install APEX now locally into a PDB then you'll have to use the apexins.sql script:

    cd $ORACLE_HOME/apex
    sqlplus / as sysdba
    SQL> alter session set container=PDB1;
    SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/

    Be aware - this step may run a long time (in my VBox image it took over 22 minutes to complete).

    timing for: Complete Installation
    Elapsed: 00:22:16.29

    Check the existence of the component finally:

    SQL> select comp_id, status , con_id from cdb_registry where comp_id='APEX';

    --------- ------- -------
    APEX      VALID         3

Solution 2 - Upgrade APEX in the PDB after plugin

Even though I'd prefer solution 1 there may be some cases where you'll have to deal with Solution 2 and upgrade APEX after plugin. One simple reason could be that somebody uses APEX already in another PDB and you can't export and preserve it for later import - then you better don't follow Solution 1 then as it will force you to test your backup/restore strategy concept soon after ... 

  1. Run apexins.sql (from the APEX 5.0 installation - $ORACLE_HOME/apex) only in this particular PDB after plugin. You'll have to use

  2. The documentation
    may not be correct mentioning all parameters without the --p option. Jason Straub corrected it:

    host &OH_HOME/perl/bin/perl -I &OH_HOME/rdbms/admin &OH_HOME/rdbms/admin/ -b apexins -c '<PDB_name>' apexins_nocdb.sql --pSYSAUX --pSYSAUX --pTEMP --p/i/

    whereas &OH_HOME represents the full path to the Oracle home.

More Scenarios 

If you look at some of the potential scenarios you may see when trying to move PDBs around including APEX applications it is pretty obvious why having APEX only locally inside each PDB is a wise move. It will give you way more flexibility, less trouble, actually less upgrades (and APEX upgrades can take a while) - and simply less headache.

Assume that my CDB1 and CDB2 below are at the same database version - having different database versions will require upgrade/downgrade tasks in addition. 

APEX installed commonly within CDB$ROOT

  • CDB1 has APEX 4.2 in CDB$ROOT
    CDB2 has APEX 5.0 in CDB$ROOT
    • Unplug a PDB from CDB1 and want to plug it into CDB2 but you'll get an error in PDB_PLUG_IN_VIOLATIONS:
      “APEX mismatch: PDB installed version CDB installed version”
    • That is actually the simple case as you'd only have to upgrade APEX inside the PDB:
      host &OH_HOME/perl/bin/perl -I &OH_HOME/rdbms/admin &OH_HOME/rdbms/admin/ -b apexins -c '<PDB_name>' apexins_nocdb.sql --pSYSAUX --pSYSAUX --pTEMP --p/i/

  • CDB1 has APEX 5.0 in CDB$ROOT
    CDB2 has APEX 4.2 in CDB$ROOT
    • You unplug a PDB from CDB1 and want to plug it into CDB2 - but you can't actually plug in.
    • As APEX can't be downgraded to 4.2 in your PDB you'll have to upgrade APEX in the CDB2 to 5.0 first
    • Then you can plugin your PDB into CDB2

APEX installed locally within the PDB only

  • CDB1 has APEX in CDB$ROOT
    CDB2 has NO APEX in CDB$ROOT
    • Unplug a PDB with an APEX application from CDB1 and want to plug it into CDB2- fails
    • You will need to install APEX into CDB2's CDB$ROOT before being able to plug in the PDB
      • And it has to be the same version of APEX as used in CDB1

  • CDB1 has NO APEX in CDB$ROOT
    CDB2 has APEX  in CDB$ROOT
    • Unplug a PDB from CDB1 (having APEX locally in the PDB only) and want to plug it into CDB2 - works if ...
      • the PDBs local APEX version match the CDB2's common APEX version
      • If they don't match ...
        • PDB has APEX 4.2
          CDB2 has APEX 5.0
          Works, but you will need to upgrade APEX in the PDB after plugin
        • PDB has APEX 5.0
          CDB2 has APEX 4.2
          Fails, and you'll have to upgrade CDB2s common APEX 4.2 to 5.0 first before being able to plugin.

  • CDB1 has NO APEX in CDB$ROOT
    CDB2 has NO APEX  in CDB$ROOT
    • That is actually the best case as you don't have to care.
      • The only thing you will need to be aware of is that you'll need to patch your ?/apex home in case that PDB has a higher APEX version already than the one we ship by default 

More Information

You can find more information via these links:

And Finally ...

I'd recommend to NEVER have APEX in any CDB$ROOT. I see the advantage of having it as a new PDB will always have APEX provisioned with it. That is convenient, no doubt. But honestly if you really need APEX in most or all of your PDBs then create your own PDB$MASTER and provision from this one instead of using the PDB$SEED. In all other cases it's by far easier to install APEX inside the PDB when you really need it. Even with the penalty that it requires some extra 10 or more minutes until it is ready to go.

Given also the fact that APEX upgrade may take a while as well (see this blog post:  ) there's another advantage which personally I consider way more important:

Having APEX locally in the PDB only offers you way more freedom and flexibility. All the complicated scenarios come up only when either the source has no APEX in the CDB$ROOT (APEX locally in the PDB) but the target has any version (most likely a different one) or the target has no APEX in the CDB$ROOT but the source had it. And even more fun, both CDBs have APEX in the CDB$ROOT but at different versions.

This can be totally avoided by NEVER having APEX installed in your CDB$ROOT.

Which means also for the Oracle Cloud DBaaS databases:
Remove APEX from the CDB$ROOT and install it into your PDB before really starting up doing APEX stuff inside. APEX is such cool piece of software - and it's free - and powerful - and easy!!!

And (thanks to Jason again for this hint) if you look into the APEX 5.0 Installation Guide you'll find this tip:


Oracle recommends removing Oracle Application Express from the root container database for the majority of use cases, except for hosting companies or installations where all pluggable databases (PDBs) utilize Oracle Application Express and they all need to run the exact same release and patch set of Oracle Application Express. To learn more, see "Installing Application Express into Different PDBs."


Monday Nov 16, 2015

UPDATE: _rowsets_enabled in Oracle Database 12c

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

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

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

When is the problem happening?

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

Different Workarounds 

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

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

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

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

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


Thursday Nov 12, 2015

Oracle VirtualBox 5.0.x - Segmentation Fault in PERL

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

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

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

Very strange thing ... 

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

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

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

and started to search a bit

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

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

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



Tuesday 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


Thursday Oct 22, 2015

PSU (and CPU/SPU) October 2015 got released

October 21, 2015 - Oracle released the October 2015 SPU/CPU, PSU and BPs. 


For Oracle Database and Oracle Grid Infrastructure access the PSUs from here - if you have only a single instance database you can safely take the Combo patch as you'll get downtime anyways - but for customers running RAC you need to evaluate the OJVM component as this will incur downtime which the database-only patch does not require as it can be applied rolling.

Patch Set Updates

Document Description Rolling RAC Patch Download
Note:21555660.8 Oracle JavaVM Component Database PSU (Oct 2015) (OJVM PSU) No Patch:21555660
Note:21520444.8 Combo of OJVM PSU and DB PSU (Oct 2015) Part Patch:21520444

No patch found at the moment [Mike]
Note:21359755.8 (Oct 2015) Database Patch Set Update (DB PSU) Yes Patch:21359755

Grid Infrastructure

Document Description Rolling RAC Patch Download
Note:21523260.8 Combo of OJVM PSU and GI PSU (Oct 2015) Part Patch:21523260
Note:21523234.8 (Oct 2015) Grid Infrastructure Patch Set Update (GI PSU) Yes Patch:21523234

And be aware to patch your DBaaS Cloud databases as well - this is not done automatically ;-)



Tuesday Oct 06, 2015

New in Oracle 12c: _optimizer_gather_stats_on_load

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

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

What caused this change?

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

Quoting from the first paper: 

Online statistics gathering

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

The parameter controlling this change is not mentioned:

  • _optimizer_gather_stats_on_load

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

Things to Know

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


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

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

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

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


Tuesday Sep 29, 2015

No OS Authentication? datapatch will fail in every upgrade

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


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


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

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

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

Further Information and Links:

Related Blog Posts


Monday Sep 28, 2015

Upcoming Upgrade Workshops in Prague and Bucharest

Oracle Upgrade Workshops Fall 2015

I've just returned from a work intense week from Seoul, South Korea - and there are upcoming Upgrade / Migrate /Consolidate to Oracle Database 12c workshops already on my radar - and both are almost booked out completely. So if you are either based in the Prague or the Bucharest region you may quickly sign up (and show up as well) :-)

  • Upgrade / Migrate / Consolidate to Oracle Database 12c
    Wednesday, October, 7, 2015
    Prague, Czech Republic
    @Oracle Czech
    Registration Link is here
  • Upgrade / Migrate / Consolidate to Oracle Database 12c
    Tuesday, October 13, 2015
    Bucharest, Romania
    @Oracle Romania
    Registration Link is here 

Hope to see you there :-)


Monday Sep 21, 2015

DBUA displays wrong RMAN backup for restore - Oracle

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

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

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

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

DBUA Restore Backup

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

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

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

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

The query should be: 

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

to display the most recent full backup.

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


Tuesday Sep 08, 2015

MOS Download for Oracle Database including SE2

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


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

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

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

Patches And Updates - MOS

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

Latest Patch Sets - MOS

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

MOS - Linux

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

Patch Numbers Oracle SE2

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

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

Files 3 and 4 contain Oracle Database Standard Edition SE2.

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



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


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




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


    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


    Thursday Jul 02, 2015

    SAP on Oracle Database 12c now with Oracle In-Memory

    Oracle SAPOn March 31, 2015 SAP has been certified to run on Oracle Database

    As of June 30, 2015, Oracle Database In-Memory is supported and certified for SAP environments for all SAP products based on SAP NetWeaver 7.x. on Unix/Linux, Windows and Oracle Engineered Systems platforms running Oracle Database 12c - in single instance and Oracle Oracle Real Application Clusters deployments.

    Oracle Database 12c is the database of choice for SAP customers based on In-Memory Technology which is fully supported for SAP BW and SAP OLTP applications.

    For requirements, restrictions, and implementation details see the documents below.


    PS: Fidel, thanks for the correct links - highly appreciated!!! 

    Tuesday Jun 30, 2015

    Some Data Pump issues:
    + DBMS_DATAPUMP Import via NETWORK_LINK fails
    + STATUS parameter giving bad performance

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

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

    Symptoms are: 

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

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


    Another issue Roy just came across:

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

    Symptoms are: 

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

    Recommendation is:  

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


    Wednesday Jun 17, 2015

    Webcast "Why Upgrade to Oracle 12c" available

    In April I've done a webcast mainly for Oracle partners to explain and elaborate
    Why Upgrade to Oracle Database 12c?

    This webcast is now available without registration etc. Watch it here (click on the picture below):

    Webcast Why Upgrade To Oracle Database 12c - Mike Dietrich, April 2015


    Wednesday May 27, 2015

    Removing Options from the Oracle Database kernel in 12c

    Remove Options from the Oracle Database Kernel - chopt

    Sometimes people have the desire to remove options from the database kernel (i.e. from the oracle executable).

    It's a matter of fact that by default you'll get plenty of things linked into your kernel in Oracle Database 12c.

    In case you'd like to remove things the chopt utility does still exist in Oracle Database 12c - but you may recognize a difference between Oracle 11.2 and Oracle 12.1. Anyhow, ideally you'll do these changes before you create a database directly after the installation has been completed. See the documentation for Post Installation Tasks first:

    Now let's call chopt and see what it tells us on the command prompt:

    $ chopt

    chopt <enable|disable> <option>

                      dm = Oracle Data Mining RDBMS Files
                    olap = Oracle OLAP
            partitioning = Oracle Partitioning
                     rat = Oracle Real Application Testing
    e.g. chopt enable rat 

    For a first try I'm unlinking Data Mining:

    $ chopt disable dm

    Writing to /u01/app/oracle/product/
    /usr/bin/make -f /u01/app/oracle/product/ dm_off ORACLE_HOME=/u01/app/oracle/product/
    /usr/bin/make -f /u01/app/oracle/product/ ioracle ORACLE_HOME=/u01/app/oracle/product/

    I tested all the 4 available chopt options and this is the result when you exit SQL*Plus afterwards:

    SQL> exit
    Disconnected from Oracle Database 12c Enterprise Edition Release - 64bit Production

    versus before with all options still linked into the kernel:

    SQL> exit
    Disconnected from Oracle Database 12c Enterprise Edition Release - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

    But how about all the other options being available in releases before Oracle Database 12c, such as: lbac_on|off (Label Security), dv_on|off (Database Vault)? If you'd refer to the list of options to link on/off published by Ghokan Atil years back in his blog you may find more things to try out.

    Lets give it a try with Label Security:

    $ /usr/bin/make -f /u01/app/oracle/product/ lbac_off ORACLE_HOME=/u01/app/oracle/product/
    lbac_off has been deprecated

    Ah, very smart ;-) It signals that you can't link those things off anymore. The same would happen with dv_off.

    And how about things which are from older sources, such as Spatial Data (sdo_on|off)?

    $ /usr/bin/make -f /u01/app/oracle/product/ sdo_off ORACLE_HOME=/u01/app/oracle/product/
    Warning: sdo is always turned on. sdo_off is disabled.

    In this case it would be simple: You'd take out SDO from the components inside the database - and there's no need to unlink anything.


    Monday May 18, 2015

    Create a PDB directly from a stand-alone database?

    The documentation offers a well hidden feature for migrating a database into the universe of Oracle Single-/Multitenant:

    Remote Cloning with the NON$CDB option.

    If you'll read the documentation it doesn't say much about this option, neither the requirements nor the exact syntax or an example:

    Scroll down to the FROM clause:

    ... FROM NON$CDB@dblink ... this option will be able to plugin a stand-alone database and make it a pluggable database. Sounds interesting, let's try it.

    Test 1 - Try to plugin an Oracle database

    Well, the documentation doesn't say anywhere anything about source release limitans. So I tried it simply with an Oracle database. 

    1. Created a database link from my existing CDB pointing into my database
    2. Started my SOURCEDB in read-only mode
    3. Tried to create a pluggable database from my SOURCEDB - and failed ...
      SQL> create pluggable database PDB1 from non$cdb@sourcedb;
      create pluggable database PDB1 from non$cdb@sourcedb
      ERROR at line 1:
      ORA-17627: ORA-28002: the password will expire within 7 days
      ORA-17629: Cannot connect to the remote database server

    Test 2 - Try to plugin an Oracle database in file system 

    Thanks to Tim Hall - his blog post did the magic trick for me:

    First of all, the reason why my Test 1 failed is simply that I can't have a user in an Oracle database with the privilege CREATE PLUGGABLE DATABASE - but this is a requirement as I learned later on.

    1. You'll need a user in SOURCEDB with the privilege to CREATE PLUGGABLE DATABSE:
    2. Start SOURCEDB in read-only mode after shutting it down:
    3. Create a database link pointing from the CDB back into the SOURCEDB:
      CREATE DATABASE LINK sourcedblink
      CONNECT TO sourcedb_user IDENTIFIED BY password USING 'upgr12';
    4. Now create the pluggable database from the stand-alone UPGR12 database:
      CREATE PLUGGABLE DATABASE pdb_upgr12 FROM NON$CDB@sourcedblink
    5. But when you check the status of the new PDB you'll realize it is OPEN but only in RESTRICTED mode. Therefore noncdb_to_pdb,sql needs to be run. Connect to the new PDB and start the script:


    What will you get from this command? Actually it will allow a simple way to plug in a stand-alone database into a container database but the following restrictions apply:

    • Source database must be at least Oracle
    • Source database must be on the same OS platform
    • Source database must be at the same (equal) version as the container database
    • Script noncdb_to_pdb.sql needs to be run

    You may have a look at this MOS Note:1928653.1 Example for Cloning PDB from NON-CDB via Dblink as well [Thanks Krishnakumar for pointing me to this note].

    Finally the only simplification seems to be to avoid the extra step of creating the XML manifest file with DBMS_PDB.DESCRIBE - but apart from that I can't see many other benefits - except for easing of remote cloning with the above restrictions.



    Thursday Apr 23, 2015

    CDBs with less options now supported in Oracle

    When Oracle Multitenant was launched Roy and I amongst many other people always mentioned that the requirement of having all options in a Container Database (CDB$ROOT), and therefore also for the PDB$SEED with the immediate result that all PDBs provisioned from the PDB$SEED will have all options as well, will hinder customer adoption significantly. 

    Almost all customers I have talked to in the past 3-4 years about Oracle Multitenant mentioned immediately that it will be a huge problem for them to install all options as (1) their policy is to install only things they are licensed for to (2) prevent developers, users and DBAs to use things accidentally without even knowing that this or that will require a license.

    As it is not allowed to manipulate and change the PDB$SEED the workaround - as PDBs were allowed to have less options - has been to create a stand-alone Oracle 12c database with exactly the options you'd like to have configured as your gold standard - and then plug it in under a remarkable name, for instance PDB$MASTER. Switch it to read only and make sure from now on you'll provision a new PDB always as a clone from PDB$MASTER, and not from PDB$SEED.

    All Options in a CDB

    That would have even worked in the Single Tenant case, which does not require licensing the Oracle Multitenant option and where you have only one active ("customer-created PDB";) PDB. For this purpose you would have unplugged your PDB$MASTER after making it a pluggable database and provision new PDBs with just your desired options set as plugging in PDB$MASTER under a new name (e.g. PDB26) using the COPY option of the command.

    Now this will become all obsolete as from now you it is allowed to have a CDB installation with less options. This applies to linked kernel modules (e.g. RAT) as well as to configured database components (e.g. JAVA, OWM, SPATIAL etc).

    Please see the following new/rephrased MOS Notes:

    MOS Note:2001512.1 basically explains the following steps:

    • Do all the click work in DBCA (Database Creation Assistant) to create a container database - but let DBCA only create the scripts
    • Edit the <SID>.sql script and remove the unwanted options according to the dependency table in the MOS Note
    • Edit the CreateDBCatalog.sql in case you want to remove OWM (Oracle Workspace Manager) creation as well 
    • Add the Oracle PERL $ORACLE_HOME/perl/bin in front of your $PATH variable
    • Start the <SID>.sh script on the shell prompt

    Here's an example of a CreateDBCatalog.sql and a XXXX.sql creating a CDB with no options except XDB (which is mandatory in Oracle Database 12c):

    cat CreateDBCatalog.sql

    connect "SYS"/"&&sysPassword" as SYSDBA
    set echo on
    spool /u01/app/oracle/admin/XXXX/scripts/CreateDBCatalog.log append
    alter session set "_oracle_script"=true;
    alter pluggable database pdb$seed close;
    alter pluggable database pdb$seed open;
    host perl /u01/app/oracle/product/ -n 1 -l /u01/app/oracle/admin/XXXX/scripts -b catalog /u01/app/oracle/product/;
    host perl /u01/app/oracle/product/ -n 1 -l /u01/app/oracle/admin/XXXX/scripts -b catproc /u01/app/oracle/product/;
    host perl /u01/app/oracle/product/ -n 1 -l /u01/app/oracle/admin/XXXX/scripts -b catoctk /u01/app/oracle/product/;
    -- host perl /u01/app/oracle/product/ -n 1 -l /u01/app/oracle/admin/XXXX/scripts -b owminst /u01/app/oracle/product/;
    host perl /u01/app/oracle/product/ -n 1 -l /u01/app/oracle/admin/XXXX/scripts -b pupbld -u SYSTEM/&&systemPassword /u01/app/oracle/product/;
    connect "SYSTEM"/"&&systemPassword"
    set echo on
    spool /u01/app/oracle/admin/XXXX/scripts/sqlPlusHelp.log append
    host perl /u01/app/oracle/product/ -n 1 -l /u01/app/oracle/admin/XXXX/scripts -b hlpbld -u SYSTEM/&&systemPassword -a 1  /u01/app/oracle/product/ 1helpus.sql;
    spool off
    spool off


    cat XXXX.sql

    set verify off
    ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
    ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
    host /u01/app/oracle/product/ file=/u01/app/oracle/product/ force=y format=12
    -- @/u01/app/oracle/admin/XXXX/scripts/JServer.sql
    -- @/u01/app/oracle/admin/XXXX/scripts/context.sql
    -- @/u01/app/oracle/admin/XXXX/scripts/ordinst.sql
    -- @/u01/app/oracle/admin/XXXX/scripts/interMedia.sql
    -- @/u01/app/oracle/admin/XXXX/scripts/cwmlite.sql
    -- @/u01/app/oracle/admin/XXXX/scripts/spatial.sql
    -- @/u01/app/oracle/admin/XXXX/scripts/labelSecurity.sql
    -- @/u01/app/oracle/admin/XXXX/scripts/apex.sql
    -- @/u01/app/oracle/admin/XXXX/scripts/datavault.sql
    -- @/u01/app/oracle/admin/XXXX/scripts/CreateClustDBViews.sql



    This results in a database having only these components - the minimal component set in Oracle 

    -------- --------------------------------------

    CATALOG  Oracle Database Catalog View

    CATPROC  Oracle Database Packages and

    XDB      Oracle XML Database


    -- Mike 

    Tuesday Apr 21, 2015

    REPLAY and Slides for Webcast "Why Upgrade to Oracle Database 12c" for ISVs/Partners - Apr 2015

    Thanks for attending today's webcast about
    "Upgrading to Oracle Database 12c - and why you should upgrade".

    Webcast Why Upgrade 12c

    Please access the slides via this link:

    Webcast Slides - Why Upgrade to Oracle Database 12c

    And for those who couldn't participate here's the replay of the 45 minute Webcast:

    REPLAY Webcast "Why Upgrade to Oracle 12c"

    Thanks :-)



    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