Monday Jan 11, 2016

New Webcast "Why Upgrade to Oracle 12c" available

In December 2015 I did a recording for a webcast about "Why Upgrade to Oracle Database 12c" refreshing the content from a few months back.

Webcast "Why Upgrade to Oracle Database 12c"

Oracle's latest generation of database technology, Oracle Database, has some impressive new features. It offers great potential for a fast upgrade, simple migrations and consolidation, making more efficient use of hardware and delivering major improvements in management efficiency.

Join our webcast on Upgrading to Oracle Database 12c and you will learn:

  • Why you still need to upgrade to Oracle Database
  • How to ensure that your applications are ready for Oracle Database 12c
  • How to evaluate and test all enhancements of the upgrade process plus other new features
  • Best practices to upgrade and migrate successfully 

If you would like to watch it please access the webcast via this link: 

And please excuse the suboptimal audio quality - there were some technical difficulties to work around.


Tuesday Dec 01, 2015

UKOUG Tech 2015 - 3 Talks - Upgrades & Consolidation

UKOUG 2015- I'ma  aSpeaker

UKOUG Tech 2015 Conference will begin on Monday, Dec 7, 2015. But there will be some activities on Sunday as well.  

It will be my first time at UKOUG's conference. It will happen in Birmingham/UK in the ICC.

Looking forward to meet great customers, experts and colleagues.

I'm happy to be invited to deliver 3 talks:

Hope to meet you there!


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?



Friday Oct 23, 2015

OOW 2015 - Upgrade and Migration Talks

Oracle Open World 2015
will kick off in a day and a bit.
And still some work to do ;-)

A few things I would like to mention:

  • Our four HOLs are all overbooked already with many people on the waiting lists. You can always come by and wait in line at the Nikko Hotel's Golden Gate lab room as some people don't show up. But no guarantee. What I would recommend to you in case you are interested in the lab but didn't get a seat:

    Come to our talk instead on Monday at 1:30pm as Roy and I will demonstrate LIVE and UNCENSORED parts of the lab. Then you'll download it from the blog (find it in the Slides Download Center) and try it out by yourself afterwards: Hands On Lab Upgrade, Migrate, Consolidate to 12c

    Upgrade and Migrate to Oracle Database 12c: Live and Uncensored! [CON6777]
    Mike Dietrich, Master Product Manager, Oracle
    Roy Swonger, Sr Director, Software Development, Oracle

    Monday, Oct 26, 1:30 p.m. | Moscone South—102
  • 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. Again uncensored (mostly ;-) ) 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
  • And the 4th talk I'd like to highlight is the Database Upgrade 12c - Oracle Support talk from Support Architect Roderick Manalac.

    Oracle Database 12c Upgrade: Tools and Best Practices from Oracle Support [CON8664]
    Roderick Manalac, Architect - Database Support, Oracle
    Thursday, Oct 29, 12:00 p.m. | Moscone South—305


CU soon :-)


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 Sep 14, 2015

SQL Plan Management - Known Issues in Oracle

Our Support colleagues released the patch recommendation note for SQL Plan Management (SPM) for Oracle Database


SPM Note

In the unlikely event you'd like to upgrade to Oracle Database or (very very unlikely hopefully) please see these matching notes:


Some additional things to mention:

  • SPM is an Oracle Enterprise Edition feature at no extra cost
  • SPM is THE feature to ensure plan stability tackling changes such as (of course) upgrades and migrations
  • SPM has been improved a lot internally in Oracle Database
    • We now store entire plans instead of a large accumulation of hints in the SQL Management Base (SMB) in SYSAUX tablespace
    • The "Evolve" task does happen automatically (SYS_AUTO_SPM_EVOLVE_TASK) as part of the Automatic SQL Tuning Task 
  • You should always adjust the retention when starting to play with SQL Plan Management as the default retention of 53 weeks may lead to a too large LOB segment in SYSAUX tablespace (and LOB segments never shrink)
    • SQL> exec DBMS_SPM.CONFIGURE('plan_retention_weeks',5);
  • See the Oracle Database 12c documentation about SPM:



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 Jul 30, 2015

    SQL Monitoring - Limitation at 300 lines per statement

    One of the best parts of my job at Oracle:
    I still learn something new every day.

    Yesterday I've learned from my colleague from Oracle Switzerland, Thomas Teske, that SQL Real Time Monitoring has an embedded default limitation on the number of lines in the statement. If the limit (default: 300 lines) is exceeded the statement won't be monitored. We both work with a leading Swiss company and we wanted to monitor a complex plan. 

    Now you may think: Who the heck has statements longer than 300 lines?
    Well ... sometimes that is beyond your influence as in this particular case this is of course done by the application.


    SQL> alter system set "_sqlmon_max_planlines"=800 scope=both;

    or set in your spfile:


    This limitation is described in:

    MOS Note:1613163.1
    How to Monitor SQL Statements with Large Plans Using Real-Time SQL Monitoring?

    If you'd like to read a bit more about SQL Real Time Monitoring please follow one of these links - and be aware that it's part of the Tuning Pack license and VERY helpful in many everyday situations. You'll have to have STATISTICS_LEVEL either TYPICAL (the default) or ALL and CONTROL_MANAGEMENT_PACK_ACCESS='DIAGNOSTIC+TUNING' (the default as well).



    Things to consider BEFORE upgrading to Oracle to AVOID poor performance and wrong results

    Finally it got published on MyOracle Support (MOS) portal - one of the most helpful notes when we prep for an upgrade or a migration - and potentially the one with the longest title in MOS:

    MOS Note: 2034610.1
    Things to consider BEFORE upgrading to Oracle Database
    to AVOID poor performance and wrong results 

    Avoid Poor Performance and Wrong Results when upgrading to

    Just in case you are NOT upgrading/migrating to Oracle Database there are three other helpful notes for earlier releases available as well:

    Document 1320966.1
    Things to Consider Before Upgrading to
    to Avoid Poor Performance or Wrong Results

    Document 1392633.1
    Things to Consider Before Upgrading to
    to Avoid Poor Performance or Wrong Results
    Document 1645862.1
    Things to Consider Before Upgrading to
    to Avoid Poor Performance or Wrong Results


    Wednesday Jul 29, 2015

    ORAchk - How to log SRs and ERs for ORAchk

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

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

    Especially as the documentation of ORAchk states:

    Appendix B - How to Obtain Support

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

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

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

    We hope this helps :-)


    Monday May 11, 2015

    New version of the BIG 12c SLIDE DECK available

    I'm not on vacation right now. I'm just very busy traveling between customer onsite visits, customer meetings and workshops, internal and external workshops, testing sessions ... and so on ... 

    A lot of stuff has been stacked up in my inbox regarding changes in Oracle 12c, changed behavior - and thanks for your inputs. I will blog on it as soon as I have understood the issue and the solution - so plenty of stuff should come in the next weeks ;-)

    Just end of last week Roy and I uploaded a new version of our BIG slide deck - now having even more slides (but still not over 550) *haha* ;-)

    Find them here - and as always - the change log at the end of the deck will explain what we've recently adjusted/added/altered.

    Upgrade, Migrate and Consolidate to Oracle Database 12c


    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.



    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:
    • BEGIN
    • To use queued write mode run this procedure:
    • BEGIN

    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?

    Known Issues


    Tuesday Jan 20, 2015

    Some Tokyo Impressions - January 2015

    Just some impressions from our week in Tokyo, Japan in January 2015.

    Tokyo 2015

    Tokyo 2015

    Tokyo 2015

    Tokyo 2015

    Tokyo 2015

    Tokyo 2015

    Tokyo 2015

    Tokyo 2015

    Tokyo 2015

    Tokyo 2015

    Tokyo 2015

    Tokyo 2015

    Tokyo 2015

    Tokyo 2015


    Friday Dec 19, 2014

    Upgrade Workshops 2014 - Summary

    2014 is almost over. Time for a quick review :-)

    Roy and I would like to thank you for attending at our workshops throughout the globe. We've delivered more workshops than ever before. Most of them in Europe and Asia, but we did visit also South and Middle America, Australia and - well - the US of course. 

    See here the full workshop map:

    We delivered in 2014:

    • 62 workshops and conferences
    • for almost 4500 attendees
      • 13x in Asia/Australia
      •  5x in South/Mid America
      • 15x in the US and
      • 29x in EMEA
    • Many dedicated customer workshops
    • Several webcasts

    Thanks again to all of you - it was a pleasure to meet with you all.

    Have wonderful holidays and a successful start into 2015. Hope to see you all soon again and don't forget: Upgrade Now!!!

    -Mike & Roy 

    Thursday Nov 13, 2014

    Incremental Statistics Collection improved in Oracle 12c

    Traveling right now through Asia. It was Beijing for 32 hours, Toyko for 24 hours - and now we are running an internal 2-day workshop with colleagues from Korea, New Zealand, India and some other countries in Seoul. And yesterday I had the pleasure to listen to Tom Kyte to his optimizer talk at the OTN Conference in Tokyo. And I learned a lot - as always when having the chance to listen to Tom, Graham Wood and the other great experts.

    Oracle Database 11.1 offered a great new feature: Incremental Statistics Collection which helped a lot to make stats collection on partitioned tables way more efficient. But it had a few flaws and it took a while to work as expected. And it had one side effect when you used it heavily: It stored tons of data in WRI$_OPSTAT_SYNOPSIS. We saw it on some databases at almost 300GB. 

    Now the thing with such a huge WRI$_OPSTAT_SYNOPSIS can be: It gets a new partitioning layout during upgrades twice:

    • Upgrade from Oracle 11.1.0.x/ to Oracle
      • Change to Range.Hash Partitioning for WRI$_OPSTAT_SYNOPSIS
      • This can cause a lot of data movement.
    • Upgrade from Oracle to Oracle 12.1.0.x:
      • Change to List-Hash Partitioning
      • This will cause not as much data movement as in the previous change

    Tom explained yesterday that in Oracle Database 12c Incremental Statistics Collection has gotten a few excellent extensions making it more efficient: 

    • Smaller footprint on disk for synopses compared to previous releases
    • Incremental stats with partition exchange operations
    • Ability to define a stale percentage for existing partitions

    The latter one is very interesting as it meant: Before Oracle Database 12c whenever you did change a single row within an existing partition during a recalculation of the Global Stats this particular partition need to be examined again - even though just one record has been changed - instead of still using the stored synopsis.

    In Oracle Database 12c you can now define a stale percentage. First you'll have to enable it, second you can set a stale percentage by yourself - otherwise the default of 10% would apply - but only if it has been enabled. Otherwise the pre-12c default will be kept (and this is the behavior in Oracle Database 12c out of the box):

    • Switch incremental statistics on for a specific partitioned table:
    • Switch on the new 12c stale percentage feature globally:
    • Change (only if desired) the stale percentage of default of 10%:

    Monday May 27, 2013

    The MAGIC Questions

    Almost every week Roy, Carol and I receive one or more emails in the following style:

    "Hey, we (or my customer) plan(s) an upgrade to Oracle 11g. We (or the customer) wants zero downtime. Currently we (or they) are on AIX with Oracle 10g (and someold  9i) databases. Can we get an advice please?"

    or another one here ...

    "Upgrade from 8i to 11g. The customer's database is 28 TB (quite big!). Downtime is 5-6 hours. It's on AIX. And it's an it's an Oracle EBS database"

    Well, in both cases we lack a lot of useful information - or sometimes things are almost impossible or simply wishful thinking. So we have a collection of (we call them) The Magic Questions. Once those are answered upfront it is way easier to give a helpful advice.

    • Will you exchange the hardware?
    • Will you change to a new OS version?
    • Will you change to an entire new OS architecture?
    • Will you change the database characterset?
    • Do you plan to consolidate (schema/database/...)?
    • Number of databases you plan to upgrade or migrate?
    • Size of database(s)?
    • Exact source and target Oracle versions?
    • Maximum allowed downtime per database?
    • Fallback requirements?
    • Test environment available? Testing tools?
    • Does a performance baseline exist?
    • Changes required to enable new features?
    • RAC/Grid Infrastructure already in use or planned?

    Once we get the answer and (even more helpful) a sheet describing the entire landscape in more detail we will be able to give some advice.


    Friday Oct 19, 2012

    Migration of a database from 32bit to 64bit

    Database migrations from an 32bit environment to an 64bit environment keeping the same platform architecture (e.g. moving an Oracle database from MS Windows XP 32bit to MS Windows Server 2003 64bit) does not happen that often anymore. But still we see them getting done. And there are a few things to note when doing such a move.

    First of all the important question is:
    Will you upgrade your database as part of this move - Yes or No?

    If you say "Yes" then you are almost done with that topic as we will take care of that bitnes move during the upgrade. The only thing you have to take care is OLAP in case you are using OLAP Option with Analytic Workspaces (AW) by yourself. Those store data in Binary LOBs - and in order to move AWs from 32bit to 64bit you have to export your AWs prior to the move - and import them later on. People who don't use OLAP don't have to take care on this. In that case you'll have to drop AWs after the export - please see MOS Note:386990.1 for further details.

    But if you say "No" (meaning: no upgrade actions involved - you keep your database version) then you have to make sure to invalidate all packages and stored code in the database before you shutdown your database in the 32bit environment and prior to moving it over. And the same rule as above for OLAP applies once you use the OLAP Option.

    In the source environment:

    startup upgrade;    -- [or startup migrate; -- for Oracle 9i]
    shutdown immediate

    In the destination environment:

    startup upgrade
    @?/olap/admin/xumuts.plb --Only if OLAP Option is installed

    The script utlirp.sql will invalidate all packages and stored code, utlrp.sql will recompile - and xumuts.plb will rebuild the OLAP Analytic Workspaces in case you have the OLAP Option installed.

    Thursday Sep 06, 2012

    Upgrade and Migration Factory by Oracle Consulting

    Oracle Consulting did launch a while ago the Oracle Consulting Migration Factory. The purpose of this offer is to support customers in large upgrade and migration projects with hundreds or thousands of databases. If you'll need this support in your projects please check out the Oracle Consulting Migration Factory page.

    Please find more info in the Migration Factory White Paper.

    Wednesday May 02, 2012

    Behaviour Changes Oracle 8i to 11.2 + New Features Oracle 11.2 + refreshed Upgrade and Migration Slides

    We have uploaded refreshed and new slide sets:

    Tuesday Apr 03, 2012

    New Slides - and a discussion about Dictionary Statistics

    First of all we have just upoaded a new version of the Upgrade and Migration Workshop slides with some added information. So please feel free to download them from here.The slides have one new interesting information which lead to a discussion I've had in the past days with a very large customer regarding their upgrades - and internally on the mailing list targeting an EBS database upgrade from Oracle 10.2 to Oracle 11.2.

    Why are we creating dictionary statistics during upgrade?

    I'd believe this forced dictionary statistics creation got introduced with the desupport of the Rule Based Optimizer in Oracle 10g. The goal: as RBO is not supported anymore we have to make sure that the data dictionary has fresh and non-stale statistics. Actually that would have led in Oracle 9i to strange behaviour in some databases - so in Oracle 9i this was strongly disrecommended.

    The upgrade scripts got hardcoded to create these stats. But during tests we had the following findings:

    It's important to create dictionary statistics the night before the upgrade. Not two weeks before, not 60 minutes before your downtime begins. But very close to the upgrade. From Oracle 10g onwards you'd just say:


    This is important to make sure you have fresh dictionary statistics during upgrade for performance reasons. Tests have shown that running an upgrade without valid dictionary statistics might slow down the whole upgrade by factors of 2x-3x.

    And it would be also a great idea post upgrade to create again fresh dictionary statistics when you've did suppress the stats creation during the upgrade process. Suppress? Yes, you could set this underscore parameter in the init.ora:


    to suppress the forced dictionary statistics collection during an upgrade. We believe strongly that (a) people using the default statistics creation process which will create dictionary statistics by default and (b) create fresh stats before upgrade on the dictionary. Therefore we find it save once you have followed our advice to use the underscore during upgrade. And we've taken out that forced statistics collection during upgrade in the next release of the database.

    Please note: If you are using the DBUA for the upgrade it will remove underscore parameters for the upgrade run to improve performance - which is generally a good idea. So you'll have to start the DBUA with that call:

    $ dbua -initParam "_optim_dict_stats_at_cb_cr_upg"=FALSE


    Thursday Dec 22, 2011

    Some impressions ...

    Thanks again to our colleagues of Oracle Japan for the (as always) excellent organization of our journey to Japan in December 2011. And thanks to all the customers and colleagues we've met and which did visit the workshops :-) We'll hope to meet you next time again!

    And here are some impressions:
    (you might click on the pictures to get a larger view)

    Osaka by Night

    Silent Cooking in Tokyo

    Mackerel :-)

    Yes ... Otoro and Maguro ... delicious!!!

    Okonomiyaki - also VERY delicious!!!

    Fuji-san in the morning

    Ginko Trees in Tokyo

    Kyoto - Kinkaku-Ji (Temple of the Golden Pavilion)

    Beautiful gardens at Kinkaku-Ji in Kyoto
    (the picture doesn't reflect that it was freezing cold)

    German (yes!!!) Christmas Market in Osaka :-)
    (and the two big cans contain Sauerkraut - what else ...)


    Merry Christmas!!!

    Thursday Nov 10, 2011

    Upgrade & Migration Workshop in Singapore

    Singapore 2

    Thanks to everybody being there today in Singapore for our new
    Upgrade and Migration to Oracle Database 11.2
    workshop.It's actually great to be back :-)

    In order to get the new slides for this workshop you might download them from here.

    Thanks for your time and have successful upgrades and migrations!

    Singapore Casino

    Tuesday Nov 08, 2011

    Upgrade & Migration Workshop in KL

    Thanks to everybody being there today for our new
    Upgrade and Migration to Oracle Database 11.2

    In order to get the new slides for this workshop you might download them from here.

    Thanks for your time and have successful upgrades and migrations!

    Information: I had to reupload the slides on Nov 9th, 4:30am CET (11:30am local KL time). In case you have downloaded them beforehand you may please download them again as the original PDF missed over 300 slides ;-) Sorry for the inconvenience!!!

    Monday May 16, 2011

    Move to Locally Managed Tablespaces

    As I've got asked during the workshop in Warsaw how to migrate Dictionary Managed to Locally Managed tablespaces here's some additional information and an example.

    To find out if a tablespace is dictionary or locally managed you'd use this query:


    The procedure to migrate to locally managed tablespace is:


    And the whole migration procedure would look like this:

    -- do this for all tablespaces except SYSTEM, TEMP and SYSAUX
    -- necessary if there's no "real" temp tablespace definded yet - see Note:160426.1
    -- for all tablespaces except SYSTEM, RBS, TEMP and SYSAUX
    -- for all tablespaces except SYSTEM, RBS, TEMP and SYSAUX



    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