Wednesday Feb 03, 2016

DBUA and Read-Only Tablespaces - Things to Know

Some people prefer the manual upgrade on the command line, others prefer the graphical tool Database Upgrade Assistant (DBUA).

DBUA and Read-Only Tablespaces 

The DBUA offers you an option of setting your non-Oracle tablespaces read-only during the upgrade.

DBUA Read Only 1

What the option doesn't tell you is the purpose - and the use case when to "click" it on.

Partial Offline Backup 

The option of having data tablespaces which don't belong to the Oracle supplied components is simply to do an offline backup and - in case of a failing upgrade - restore quickly. You'll find this in our big slide deck under "Fallback Strategies - Partial Offline Backup". We have used this method in several scenarios:

  • Large telco systems where the time to restore/recover the entire database would have taken hours or days
  • DWHs where the database is large and intentionally operated in NOARCHIVELOG mode
  • Standard Edition databases where Guaranteed Restore Points in combination with FLASHBACK DATABASE are not available

FLASHBACK DATABASE is my all-time favorite as it is simple, fast and easy to use. You'll set a Guaranteed Restore Point - and in case of failure during the upgrade you'll flashback. Just don't forget to drop the restore point later when you don't need it anymore. Otherwise your FRA will run out of space the sooner or later. The only real caveat in this case is the fact that you can't change COMPATIBLE

When setting data tablespaces read-only the idea is to offline backup the "heart" of the database consisting of all files belonging to SYSTEM, SYSAUX and UNDO tablespaces plus the redologs plus the controlfiles. The tricky part: you'll have to backup also all other repository tablespaces. Those can exist for instance when the database has seen several upgrades already and started its life maybe in the Oracle 8i or 9i days. So you may see also XDB, DRSYS and ODM. You'll have to leave them in read-write as well during the upgrade and backup the files offline beforehand.

The Customer Case

The real tricky part is something Marvin hit and commented on the upgrade blog:

I am upgrading from to During the DBUA setup screens, I checked "Set User Tablespaces to Read Only During the Upgrade". It just seemed like the right thing to do. All of my tablespaces were ONLINE. All tablespace files were autoextendable. During the upgrade I got this error.

Context component upgrade error
ORA-01647 tablespace xxxxx is read-only.
Cannot allocate space in it.

There was plenty of space. I re-ran without the box checked and it ran ok. Just curious if anyone else has seen this.

The read-only option in DBUA has an issue - it does not detect all repository tablespaces right now.

DBUA Upgrade - Read Only Tablespaces

Marvin and I exchanged some mails and from the DBUA logs I could see what happened:

[AWT-EventQueue-0] [ 2016-01-28 11:07:03.768 CST ] [ProgressPane$]  Progress Item passedCONTEXT

[AWT-EventQueue-0] [ 2016-01-28 11:07:03.768 CST ] [ProgressPane$]  Progress Item passedCONTEXT

[AWT-EventQueue-0] [ 2016-01-28 11:07:03.768 CST ] [ProgressPane$]  Progress Item passedCONTEXT

[AWT-EventQueue-0] [ 2016-01-28 11:07:03.781 CST ] [ProgressPane$]  progress to next step CONTEXT

[Thread-364] [ 2016-01-28 11:07:43.758 CST ] [BasicStep.handleNonIgnorableError:479]  oracle.sysman.assistants.util.InteractiveMessageHandler@5bd44e0b:messageHandler

[Thread-364] [ 2016-01-28 11:07:43.759 CST ] [BasicStep.handleNonIgnorableError:480]  CONTEXT component upgrade error:

ORA-01647: tablespace 'WCI_OCS' is read-only, cannot allocate space in it


[Thread-364] [ 2016-01-28 11:15:42.179 CST ] [SummarizableStep$StepSummary.addDetail:783]  Adding detail: CONTEXT component upgrade error:

ORA-01647: tablespace 'WCI_OCS' is read-only, cannot allocate space in it

The repository of TEXT (or CONTEXT) is not in SYSAUX as it would be the default but in another tablespace. And this tablespace obviously was set to read-only as DBUA did not discover this tablespace as a repository but a regular user data tablespace. Bang!!!

Simple workaround:
Run the upgrade without the Read-Only Option. And this worked out fine. 

You can create the TEXT component by yourself and decide in which tablespace it should be created:

SQL> connect SYS/password as SYSDBA
SQL> spool text_install.txt
SQL> @?/ctx/admin/catctx.sql change_on_install SYSAUX TEMP NOLOCK

Thanks to my team mates Cindy, Hector and Byron

Yesterday I forwarded the email to our Upgrade Team and I received three replies within minutes explaining:

  • The query the DBUA uses is not as sophisticated as you would think:
    select tablespace_name from dba_tablespaces where contents != 'UNDO' and contents != 'TEMPORARY' and status = 'ONLINE' and tablespace_name != 'SYSTEM' and tablespace_name != 'SYSAUX' and tablespace_name != (select PROPERTY_VALUE from database_properties where PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE') 

  • We have proposed a improved query already

  • It should be included in a future release of the database 


The option having data tablespaces read-only during an upgrade is meant for a fast fallback in case of an failure during an upgrade. But your first option should always be a Guaranteed Restore Point instead. If you still need the read-only solution than please be careful as you may have repositories in non-standard tablespaces. DBA_USER's DEFAULT_TABLESPACE column may give you an indication - but you should also check DBA_SEGMENTS. And I personally would use this option in conjunction with a command line approach.


Thursday Jan 07, 2016

Oracle Database certification on Microsoft Windows 10

The MOS Note: 1307195.1  about

Certification Information for Oracle Database on Microsoft Windows x64 (64-bit)

got updated right before the 2015 Christmas holidays with the official certification information for Oracle Database 12c on Microsoft Windows 10.

And it says:

Windows 10 O/S Information:
RAC is not certified.
The earliest release certified on Windows 10 is


Previous related blog posts:

Wednesday Dec 02, 2015

What or who is JOX? And what happens if ...

Are you worried because of the title of this blog post? Don't worry ... it will be about the Oracle database and not about miraculous additives.

Months back I wrote about how to remove options from the Oracle database's kernel by either using chopt or linking options off:

Recently a very interesting case was logged in relation to a bug:
A customer had linked off the JOX option from the database kernel.

What is JOX? 

Well, do a quick search for "JOX" in the Oracle Documentation and you'll get "zero results" (or you may get results in the 8.1 and 9.0 documentation which does not help here). So our documentation doesn't want to tell you further details on "JOX". But once you know that this is the acronym for the JavaVM JIT Compiler then we'll get more results leading directly to this spfile parameter:

 The default in my environments is always TRUE. It enables or disables the Just-in-Time (JIT) compiler for the Oracle Java Virtual Machine (OracleJVM) environment.

And from here a link guides us into the Java Documentation explaining a bit more details about the JIT in case you are interested. 

Why am I writing this?

You can unlink the JOX option - and compile oracle without the JIT:

$ cd $ORACLE_HOME/rdbms/lib
$ /usr/bin/
make -f /u01/app/oracle/product/ jox_off ioracle

But let me add two things

  • We recently had a bug just happening because JOX was unlinked - all our tests have JOX linked in
  • As you can control the availability of JOX via the above initialization parameter I don't see a deeper reason to unlink JOX
  • And based on the feedback of Marcelo Ochoa (thanks Marcelo!!!) you'll encounter a performance dip in some components such as interMedia, XMLDB etc as code will now be interpreted, and not JIT compiled.
My conclusion: There's no real reason to take JOX out of the RDBMS kernel. Better leave it linked in.

How do you find out if JOX is linked in?

You can't query the database for JOX as it is not mentioned in V$OPTION or DBA_REGISTRY. But you can find it out easily on the OS level: 

$ cd $ORACLE_HOME/rdbms/lib
$ ar -t libknlopt.a | grep -c jox.o

If the result is "1" it means "JOX installed", and "0"  means it is not installed.

Credits to Byron Motta from our Database Upgrade team! 

Further information about JIT?


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:


Wednesday Sep 16, 2015

Script: Is your database ready for Oracle GoldenGate?

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

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

Complete Database Profile OGG readiness check for Classic Extract 

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

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

Check OGG readiness for Schema Only

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

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

Difference and Execution 

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

Log into sqlplus as sysdba and run the script:

SQL> @full-DB_CheckOracle_15092015.sql

SQL> @full-schemaCheckOracle_15092015.sql

Review the output.


Monday Jun 16, 2014

DBUA can ignore Underscores with "keepHiddenParams"

The Database Upgrade Assistant (DBUA) by default will remove all underscores and events before actually doing an upgrade. This is expected behaviour as we believe that underscores or events were meant to treat misbehaviour of the database just in a specific release only. And in our experience a nice collection of underscores will at least make your upgrades run slower.

Now with Oracle Database and Oracle Database the DBUA has a new parameter which can be used during startup:

$> dbua -keepHiddenParams

Then it will keep the hidden/underscore parameters during and after the upgrade.


PS: Many thanks to Mr. Frank Becker for highlighting this to myself - I wasn't aware of it - screenshot is courtesy from Mr Becker as well 

Thursday Apr 17, 2014

Avoid Poor Performance and Wrong Results in

One of the best notes in MOS got refreshed last night for Oracle Database It has not only information about performance bugs but also about the worst and meanest category of issues, wrong query result bugs. Those are the ones I call the "sleeping beauties" as you as a DBA usually won't recognize them. It is recognized at the application level when the result of a report is incorrect or instead of 4 rows just 3 rows get delivered in the output. Therefore you should be highly aware of those and add this note to your bookmarks.

MOS Note 1645862.1Things to Consider Before Upgrading to to Avoid Poor Performance or Wrong Results

And in case you don't want to move to Oracle but searching for information and known issues in Oracle or Oracle then please find the corresponding documents:

  • MOS Note: 1320966.1 Things to Consider Before Upgrading to to Avoid Poor Performance or Wrong Results
  • MOS Note: 1392633.1 Things to Consider Before Upgrading to to Avoid Poor Performance or Wrong Results

Friday Dec 21, 2012

Creating ASM for test purposes in the file system

First of all, I'm back after pausing for a while - sorry for not updating the blog in the past weeks ... and you won't see many updates in the following weeks as it'll be holiday season (and we Germans have sooooo many public holidays) :-)

Anyway, back to tech topics. Today I want to test Oracle Restart upgrades. Oracle Restart internally is called SIHA (Single Instance High Availability) which explains the topic a bit more. Basically it means having your database reside in ASM and let Oracle Clusterware take care on it, even though you don't have a cluster. Not a bad idea as this can be very helpful in real world environments. But I did realize that the entire process is not documented in all details. So I'd thought I should give this a try.

The first challenge I do face: I have just one disk in my machine - so I'll have to tweak ASM a bit to make it work with files on the file system.

Creating two empty strawman files in file system with dd is not a big deal:
$ dd if=/dev/zero of=/oradata/ASM/dg_DATA bs=8192 count=1000000 oflag=direct
1000000+0 records in
1000000+0 records out
8192000000 bytes (8.2 GB) copied, 336.371 seconds, 24.4 MB/s
[V112] oracle@localhost:/oradata
$ dd if=/dev/zero of=/oradata/ASM/dg_BCK bs=8192 count=500000 oflag=direct
500000+0 records in
500000+0 records out
4096000000 bytes (4.1 GB) copied, 246.021 seconds, 16.6 MB/s

But the next step is to start the cssd (Cluster Synchronization Services Demon) in my Oracle Database installation from within the $ORACLE_HOME/bin directory:
[root@localhost bin]# . localconfig add
/etc/oracle does not exist. Creating it now.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configuration for local CSS has been initialized
Adding to inittab
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)

Otherwise no chance for ASM to start up.

Now my attempts to use simply DBCA (Database Configuration Assistant) to creare the ASM instance on these two strawman files did not work as the DBCA didn't want to recognize the "disks". So back to good old command line. By the way, there's a MOS Note out there which may be helpful as well (but didn't work in my case).
How To Create ASM Diskgroups using NFS/NAS Files? (Doc ID 731775.1)

  1. Create a password file for ASM instance in $ORACLE_HOME/dbs
  2. Create a fresh init.ora for ASM within the same directory having the following parameters set:
  3. With these parameter set I could bring the instance into MOUNT state ready to create the two disk groups after setting the ORACLE_SID=+ASM in the environment:
    SYS:+ASM> create diskgroup DATA external redundancy disk '/oradata/ASM/dg_DATA';
    Diskgroup created.
    SYS:+ASM>  create diskgroup BCK  external redundancy disk '/oradata/ASM/dg_BCK';
    Diskgroup created.

Starting up ASM did work now well after shutting it down first - and a check for SELECT path from V$ASM_DISK did show me my disks.

Next step - simply - is to create a database with DBCA inside of ASM. So the first part of my test did complete.

... to be continued soon ...

Monday Nov 12, 2012

Rules Manager and Expression Filter getting removed

I doubt that many people are using the Oracle features "Rules Manager" and "Expression Filter" as usually people handle these things (such as ensuring that a zip code or a car number plate has a certain format) within the application code and not inside the database. Oracle Beehive for instance uses that just on the side. 

Anyway, just learned today that Rules Manager and Expression Filter components will get removed once our next database release most likely called Oracle Database 12c will get released. So before upgrading to Oracle Database 12c you can remove EXF and RUL components (SELECT COMP_ID FROM DBA_REGISTRY WHERE COMP_ID IN ('EXF','RUL'); ).

You'd simply do that by executing the following script before upgrade:
SQL> @?/rdbms/admin/catnoexf.sql
This will clean up Rules Manager and Expression Filter components inside the database. You could run ?/rdbms/admin/catnorul.sql before but I believe catnoexf.sql will clean up everything already.

And you'll find all this information plus guidelines for migration of existing content in MOS Note: 1233535.1 - Obsolescence Notice: Rules Manager and Expression Filter Features of Oracle Database


Friday Sep 02, 2011

Oracle Database Express Edition 11g Release 2 is now available!

Many customers have asked over the past year whether we would be producing a newer version of the Oracle Database Express Edition, because it makes such a terrific starter database for students, developers, and DBAs. Well, the answer is Yes!

As of today, Oracle Database Express Edition 11g Release 2 is available for download. As with the 10g release, this Express Edition is free of charge. To find more information about downloading the software and documentation, please visit the OTN page for Express Edition.

Remember, you can always upgrade your database to Standard or Enterprise Edition if you outgrow the resource constraints of Express Edition. So, free free to download the new release and check it out!


Wednesday Aug 10, 2011

Upgrade - and an interesting surprise

Patchset is out there for a long, looong time. But still Roy and me - and unfortunately our customers - sometimes experience some nice surprises after upgrade.

Roy did work on the weekend with a financial institution customer in the US to support them during their go-live on Oracle with several systems. It was well tested and long planned. And overall the whole process went well except for one database (see Roy's entry below on the change with JOB_QUEUE_PROCESSES in

Yesterday I've received a text message from Roy to have a closer look into a service request the customer did open due to massive performance problems after go-live. The customer has seen MUTEX S CONTENTION in the AWR and ADDM reports slowing down the whole cluster. And the support people from BDE did a good job to drill down and diagnose the issue. But the solution is an interesting surprise.

The April 2011 PSU (Patch 11724916) did contain a fix:
Bug:10187168 Enhancement to obsolete parent cursors if VERSION_COUNT exceeds a threshold

And you might believe that having the fix included in the PSU does enable the code of this fix?? At least I would believe that. But as Roy would call it "the old 'hidden bug fix' trick..." the code is there but you'll have to enable it. MOS Note 10187168.8 describes what you'll have to do.

Actually you'll have to set this hidden parameter:
and besides that this nice event in your init.ora/spfile:
events= "106001 trace name context forever, level 1024"
Then bounce the instance ... an voila ... the patch is now enabled.

Scary, isn't it ...
As I've spent 6 years in Oracle's RDBMS Support as well I was first checking the patch readme to see whether this is mentioned somewhere - but the only remark pointing to this bug is:

Anyway, what is this parameter for?

It's an enhancement request to allow parent cursor to be obsoleted if the version count exceeds a defined threshold. This threshold gets set with the new underscore _cursor_obsolete_threshold.
But due to the fact that other patches included in this PSU don't know about this newly introduced underscore event 106001 has to be set instead. The value equals the threshold.

To make things more complicated and enable this fix now (see my comments above) the underscore _cursor_features_enabled has to be set as well. And its value is dependend on the patch level - for Oracle it is 1026 whereas for Oracle it would be 18.

And let me mention that these parameters will have to be set and adjusted just under Oracle's Support guidance!

Wednesday Mar 09, 2011

New hidden parameters in Oracle 11.2

[Read More]

Monday Jan 17, 2011

Is Oracle certified to run on VMWare?

[Read More]

Monday Nov 15, 2010

Recap on Deinstall Routines

[Read More]

Friday Oct 15, 2010

How to deinstall "old" SW after has been applied?

I'm still learning continuously new things. On my test box I've installed a few weeks ago in-place which is not our recommended solution - and can be quite tricky with But I was running out of space ...

Customer have asked us for a looooooong time why we don't deliver full release but patch sets which have to be applied to a base installation. Now this all has been changed with as it is a full release. And that's the new strategy: We force people to install into a separate $ORACLE_HOME. This is called (and recommended) an out-of-place patch upgrade. Which is the default for Grid Infrastructure as well beginning with already.

You can still go "the old way" for the database installation but you'll have to 'detach' your current home from the OUI inventory first in order to be able to install into the existing one. Something we fully disrecommend as it will increase your downtime if anything fails.

Nevertheless the question came up at the upgrade workshop in Montreal (and you might have asked yourself as well already):
How do I deinstall the "old" software once I'm using the new Oracle home???


My first thought: Hey, if it's a Unix box simply 'rm -rf' the directories ;-)
Second thought: Use the OUI as it always did offer a 'REMOVE' button to remove leftovers.
Third thought: Use the magic 'X' to deinstall similar to your iPhone (ok. we are not this far yet)


Lesson learned now (thanks Roy!):
There's a deinstallation tool called 'deinstall' available in your $ORACLE_HOME/deinstall directory. Please find a detailed description right here in the Oracle Database Installation Guide(s) for 11.2. It will work for the database, Clusterware and ASM. But you could download it separately from OTN as well as part of the software distribution.

Friday Apr 09, 2010

Instant Client 11.2 available for download

[Read More]

Tuesday Apr 06, 2010

Oracle Database 11g Release 2 for Windows available!

[Read More]

Thursday Mar 25, 2010

Fix invalid objects and components - BEFORE you upgrade!

[Read More]

Tuesday Mar 09, 2010

Gathering Workload Statistics

[Read More]

Monday Mar 08, 2010

Remove "old" parameters and events from your init.ora

[Read More]

Tuesday Feb 09, 2010

Dmitry's upgrade findings

[Read More]

Friday Dec 04, 2009

Optimizer patch recommendation for

[Read More]

Thursday Aug 20, 2009

Is there a FALLBACK strategy?

[Read More]

Wednesday Aug 19, 2009

Backup your database!!!

[Read More]

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:

- -


« February 2016
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