Monday Apr 14, 2014

Upcoming UPGRADE Workshops in Europe - Register Now

There will be a lot of travel within the next few weeks. So please keep your fingers crossed that Lufthansa pilots don't go on strike for their ridicolous goals again. And I hope to see you in one of our upcoming workshops within the next weeks somewhere in the below cities.

Click on the date/location to the RIGHT to register, see location and agenda etc. 

CU soon :-)

- Mike and Roy 

Wednesday Mar 05, 2014

PSU1 and PSU2: Datapatch Issues coverd in MOS Note

You may have read a posting disrecommending PSU1 and PSU2 for Oracle Multitenant especially in RAC/GI environments earlier this week. Actually following a lot of internal discsussions I will post some advice and clarification later this week.

Now I have an useful update:
Datapatch Issues are covered within a separate MOS Note making it easier to keep track and find workarounds for known issues.
Please see MOS Note:1609718.1 Datapatch Known Issues

-Mike

Monday Mar 03, 2014

Upgrade Workshops in India in March 2014
REGISTER NOW!

*** Due to sick leave I won't be able to come to India  ***
*** I deeply appologize - hope to see you soon again *** 

 

It will be a tough trip as I will travel on my own with long distances in between the workshops but I'm really looking forward to my second trip to India to run 3 Upgrade, Migrate and Consolidate to Oracle Database 12c workshops in March 2014 in India.

Register as soon as possible as we have limited seat capacity:

Hope to see you soon :-)

-Mike

Friday Feb 07, 2014

Airfare Pricing vs. Oracle Multitenant for DBaaS?

I'm currently evalutating flight options to and from India for the 3 workshops in March in Mumbai, Delhi and Bangalore.

As everything at Oracle is fully self-serviced I've got stuck in our booking tool for over an hour now just wondering ... wondering ... wondering ...

For instance I wonder why an Economy class ticket with Lufthansa and Swiss to Mumbai and return from Bangalore will cost over EUR 5000 (no joke!!!) even though Swiss is a 100% subsidiary of Lufthansa.

whereas I can fly a slightly different route with Delta Airlines only from Germany to the US and back to Amsterdam and then further to Mumbai for less than half of the price - even though this includes different airlines as well (KLM and Delta) and will take more than twice as long and almsost triple the distance:

Roy (kudos!) and I had this great idea if YOU as a customer would match airline pricing strategy to your internal Database-as-a-Service (DBaaS) strategy?

  • First of all you make the price dependent on the time frame one odered a fresh PDB
    • The earlier the cheaper - except for the last week before a fixed date as now you'll have to max out allocated resources
  • Second you will have to make it completely intransparent so nobody will be able to proof against your pricing strategy being insane
  • Third you should make the price also depend on the process somebody used to order a PDB
  • Furthermore you should introduce some extra components such as "serviced by a lead DBA" will make it more expensive
    • Same for "served by another companies expert" - even though you own that company as well
  • And don't forget to include some components which will give yourself perfect flexibility such as "The enegery prices climbed up this week so unfortunately we'll have to make the provisioning and operation of a PDB more expensive" - and never take back this price growth (or if you do so then just by a portion of it)
    • Or tell people they'll get a special price with the only downside that the department working with this PDB will have to get up to work now at 3:40am in the morning and the PDB won't be accessible after 9am anymore

Wouldn't this be a wonderful pricing model?

Of course you read my irony and sarcasm. You know the answer: You'll be in real trouble if you'd offer such a service and pricng internally to anybody. But I'll never understand airline pricing models ...

-Mike

Tuesday Jan 07, 2014

Upcoming ASIA Upgrade to Oracle 12c workshops

.

Happy New Year to all of you and your families!!!

.

We have still a few remaining seats for the upcoming workshops in January 2014.

Oracle Database 12c Upgrade, Migrate and Consolidate Workshops


  • Singapore - Jan 21, 2014
    • Register Here!
      @Singapore Post Centre Theatrette - Level 5 - 10 Eunos Road 8 - Singapore 408600

  • Jakarta/Indonesia - Jan 22, 2014
  • Kuala Lumpur/Malaysia - Jan 23, 2014

Oracle's Global Server Technology Upgrade Development Group offers you an opportunity to attend this exclusive full day seminar on how to Upgrade, Migrate & Consolidate to Oracle Database 12c.

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

In this seminar the Oracle Database Upgrade Development team will guide you through several different examples of upgrade, migration and consolidation techniques and strategies - shown with real world customer cases.

You will learn:
  • How to take full advantage of the new features and options in Database 12c
  • The changes and enhancements to the Oracle Database upgrade process
  • Best practices to upgrade and migrate successfully

So you may register asap and spread the word :-)
Looking forward to meet you there!

-Mike

Wednesday Oct 16, 2013

October 2013 PSUs and CPUs - News for 12c

Last night CET the most recent Patch Set Updates (PSU) and Critical Patch Updates (CPU aka SPU) got published on MOS. And there's a significant and remarkable change for Oracle Database 12c onwards. MOS Note: 1571391.1 - Patch Set Update and Critical Patch Update October 2013 Availability Document says:

2.1 Database Security Patching from 12.1.0.1 Onwards

Starting with Oracle Database version 12.1.0.1, Oracle only provides Patch Set Updates (PSU) to meet the Critical Patch Update (CPU) program requirements for security patching. Security Patch Updates (SPU) will no longer be available. Oracle has moved to this simplified model due to popular demand. The PSU is Oracle's preferred proactive patching mechanism since 2009.

For more information, see My Oracle Support Note 1581950.1, Database Security Patching from 12.1.0.1 onwards.

That's a real change. It's not harmful at all as our recommendation for years now is to apply PSUs as they contain not only the security patches but also the important fixes for critical issues. And apply them asap - the day we publish the Security Patch Advisory with some details is the day when external security experts go public as well with their findings.

MOS Note: 756671.1 - Oracle Recommended Patches -- Oracle Database will guide you to the databases patches for your platform. If you miss the PSU for Oracle Database 10.2.0.5 (the Pre-Release Announcement stated that there will be a PSU for 10.2.0.5) my personal understanding is: as Oracle 10.2.0.5 went out of Extended Support in July 2013 there won't be any October PSU released anymore.

And I'll apply the new 12.1.0.1 Oct13 PSU now to my Oracle Database 12c Multitenant environment as well :-)

Don't forget:
MOS Note: 224346.1 - OPatch: Where Can I Find the Latest Version of OPatch?
Find it via Patch Placeholder 6880880

-Mike

Thursday Jul 18, 2013

Full Transportable Export/Import White Paper

One of the really cool and helpful features in Oracle Database 12c is called Full Transportable Export/Import. It combines the basics of transportable tablespaces - of course cross-platform, cross-endianess, cross-version - with Data Pump taking care on all the stuff not stored in tables and indexes, such as views, synonyms, trigger, packages etc. And you can even reduce downtime by combining the feature with incrementally converted RMAN backups.

And the best thing: this will work with an Oracle Database 11.2.0.3/4 to Oracle Database 12c - so you don't have to be on Oracle Database 12c in order to leverage from that cool new feature.

See the brand new White Paper about Full Transportable Export/Import here:
http://www.oracle.com/technetwork/database/enterprise-edition/full-transportable-wp-12c-1973971.pdf

-Mike

Wednesday Jul 17, 2013

Oracle Multitenant (Pluggable Database) White Paper

The feature we did introduce for a while now as Pluggable Database got named officially Oracle Multitenant - and if you'd like to read more about this feature the newly release White Paper may give you a good overview:

http://www.oracle.com/technetwork/database/multitenant-wp-12c-1949736.pdf

-Mike

Tuesday Jun 25, 2013

Oracle Database 12c is available for download now!

Good things come to those who wait ... finally ... Oracle Database 12c (Oracle 12.1.0.1) is available for download from the Oracle Software Cloud (formerly know as eDelivery) and OTN (Oracle Tech Network) for Linux 64bit (Solaris will follow within the next few hours):

And yes, it will be supported on Oracle Exadata and SuperCluster as well :-)

.

.

And with the release of Oracle Database 12c we are offering you also our

NEW
Upgrade, Migrate and Consolidate to Oracle Database 12c
slide deck

with (sorry, we've did it again!) over 500 slides covering:

  • The brand new Parallel Upgrade including new Pre/Post-Upgrade-Fix-Ups
  • The new Full Transportable Export/Import Feature
  • Obviously Oracle Multitenant, which got talked about a lot as Pluggable Databases or Container Databases before
  • Plenty of new parameters, cool and very helpful features and much more ...
  • Download the slides Upgrade, Migrate and Consolidate to Oracle Database 12c

And of course, the slide deck will see some updates in the near future :-)

-Mike

.

.

Tuesday Apr 02, 2013

Long time no update ...

I deeply apologize for not updating the blog for some weeks now. I'm still working for Oracle, still in the same group - but due to some other things going on in preparation for Oracle Database 12c I had either no time or simply nothing to report at the moment. I didn't want to bore you with Oracle Database 11.2 stuff as the slides don't get changed right now. Oracle Database 11.2.0.4 patchset should be available sometime in early summer with a good bunch of fixes included.

Roy and me had delivered the first internal only Upgrade, Migrate and Consolidate to Oracle Database 12c workshops in Redwood Shores and Colorado Springs mainly to Support and Development colleagues. That was great fun as we did include 3 nice parts for hands-on as well. And we've got so much good and valuable feedback.

In the near future Roy will come over to Europe and end of April we'll deliver the workshop again in Paris, Munich and Reading. South Africa will follow in May - and maybe Orlando in the US. As soon as Oracle Database 12c will get released we'll be ready to go for customer workshops as well. So stay tuned for upcoming dates and events.

- Mike

PS: This happens when you'd like to fly back home from Denver, Colorado, in late March ...now I know what a Blizzard feels like ... and the picture isn't blurry ... it's simply PLENTY of snow ...

Denver Blizzard

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

-M.

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 10.2.0.5 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]
@?/rdbms/admin/utlirp.sql
shutdown immediate

In the destination environment:

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

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 Oct 04, 2012

OOW 2012:
Deutsche Messe on their SAP Database Upgades

This Sunday Andreas Ellerhoff from Deutsche Messe (German Trade Fair) did report about Deutsche Messe's SAP/Oracle database upgrades to Oracle Database 11.2 and their improvements in comparison to Oracle Database 10g. Presentation was well attended and attendees I've talked to later on gave very positive feedback.

Download the slides here as PDF (2.6MB)

Thursday Jun 28, 2012

Guaranteed Restore Points as Fallback Method

Thanks to the great audience yesterday in the Upgrade & Migration Workshop in Utrecht. That was really fun and I was amazed by our new facilities (and the  "wellness" lights surrounding the plenum room's walls).

And another reason why I like to do these workshops is that often I learn new things from you :-) So credits here to Rick van  Ek who has highlighted the following topic to me. Yesterday (and in some previous workshops) I did mention during the discussion about Fallback Strategies that you'll have to switch on Flashback Database beforehand to create a guaranteed restore point in case you'll encounter an issue during the database upgrade.

I knew that we've made it possible since Oracle Database 11.2 to switch Flashback Database on without taking the database into MOUNT status (you could switch it off anyway while the database is open before in all releases). But before Oracle Database 11.2 that did require MOUNT status.

SQL> create restore point rp1 guarantee flashback database ;
create restore point rp1 guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'RP1'.
ORA-38787: Creating the first guaranteed restore point requires mount mode when
flashback database is off
.

But Rick did mention that I won't need to switch Flashback Database On to create a guaranteed restore point. And he's right - in older releases I would have had to go into MOUNT state to define the restore point which meant to restart the database. But in 11.2 that's no necessary anymore. And the same will apply when you upgrade your pre-11.2 database (e.g. an Oracle Database 10.2.0.4) to Oracle Database 11.2.

As soon as you start your "old" not-yet-upgraded database in your 11.2 environment with STARTUP UPGRADE you can define a guaranteed restore point. If you tail the alert.log you'll see that the database will start the RVWR (Recovery Writer) background process - you'll just have to make sure that you'd define the values for db_recovery_file_dest_size and db_recovery_file_dest.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2228944 bytes
Variable Size             134221104 bytes
Database Buffers          272629760 bytes
Redo Buffers                8466432 bytes
Database mounted.
Database opened.
SQL> create restore point grpt guarantee flashback database;
Restore point created.
SQL> drop restore point grpt;

And don't forget to drop that restore point the sooner or later as it is guaranteed - and will fill up your Fast Recovery Area pretty quickly ;-) Just on the side: in any case archivelog mode is required if you'd like to work with restore points.

- Mike

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:

Wednesday Feb 15, 2012

Why is every patchset now a full release?

This question got posted by Naveen in the previous entry today - and I found it worth it to create a new blog entry as this question gets raised in nearly every 2nd workshop as well - so others might be interested as well.

Mike,

A question has been bothering me for a while and I thought I'll throw it out there. I didn't raise a support ticket, I knew what the response is going to be.

So here is the question. I have an 11g ORACLE HOME that was built as 11.2.0.1. Now if I want to upgrade to 11.2.0.2 or 11.2.0.3, I guess there is no easy way to upgrade the existing ORACLE HOME. Why didn't Oracle give the option to just apply a patch (opatch) and upgrade to 11.2.0.2 (or .3). For a customer to create a new ORACLE HOME is just a lot of work and breaks a lot of things. We have to copy over so many configs from the old home to the new home.

I'm sure there is a good reason, I'm just trying to understand what they are.

Thanks
Naveen

Naveen, thanks for your question. And the answer has many aspects.

Install into your existing ORACLE Home

First of all you can still install the new patch set (which is now a full release since Oracle 11.2.0.2) into your existing $ORACLE_HOME. But you'll have to detach your current home from the OUI's inventory first. Please see slide 41 of the Upgrade and Migration workshop deck. Please backup the contents of $ORACLE_HOME/dbs and $ORACLE_HOME/network/admin first as you'll have to copy them back later.

$ ./runInstaller -detachHome ORACLE_HOME=/u01/orahomes/11.2.0

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 10047 MB Passed

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /u01/orabase

'DetachHome' was successful.

Once you've done that you'll install into your existing (old) directory.  And finally you'll copy back your dbs and network/admin files.

Why customers might use this procedure?

There are basically two reason why a customer chooses this strategy:
(a) Home naming conventions not allowing an Oracle 11.2.0.2 home and another Oracle 11.2.0.3 home
(b) Space issues
Both are valid reasons and therefore you can stay with the old strategy.

Why did we change from patchsets to full release patchsets?

Simple reason: Customers did ask us for a looooong time why we are delivering 3GB large patchsets instead of full releases. So we'd follow this wish. And second it will decrease the overall downtime when you'd install into your new Oracle Home. If you apply patchset software to your existing Oracle Home every Oracle process serviced by that home must be shut down as well - obviously including the database(s). And furthermore if something fails you'll have to restore your OUI's inventory plus all home contents - or reinstall your previous Oracle software.

Besides that we recommend to patch this home first with important one-offs (see links in MOS Note 161818.1) and the latest PSU or CPU. If you'd do that to your already emptied old home it will simply increase again your overall downtime.

And this is the reason why we recommend:
Always install into a new Oracle home beginning with Oracle Database 11.2 - don't erase your old home to reuse it unless there's no significant need for that procedure.

Monday Oct 17, 2011

Database Upgrade: Most important Support Notes

It's been a while ... sorry for not updating the Blog this frequently in the past weeks ;-)Notes

But now I'm back in business and today I'm updating our major slide set with some new stuff. And while I'm updating it I thought about what would be the most important MOS (My Oracle Support) Notes on Database Upgrades. 

Actually I would say you should start here - and these Notes should guide you to the most important bits and pieces around upgrades. I won't claim that this is THE final list so you might have a look into our comprehensive slide set for further details and more helpful notes and white papers:

Note:1152016.1 Master Note For Oracle Database Upgrades and Migrations

Note:837570.1 Complete Checklist for Manual Upgrades to 11g Release 2

Note:1320966.1 Things to Consider Before Upgrade to 11.2.0.2 Database Performance

Note:785351.1 Upgrade Companion 11.2

Note:1348336.1 Known Issues and Alerts for Patch Set 11.2.0.3

Note:1179474.1 Known Issues and Alerts for Patch Set 11.2.0.2

Monday Aug 29, 2011

Why upgrade?

Why Upgrade?

Simple question, isn't it? Paul from Sogeti in the Netherlands did ask this via email last week. He told me that it's difficult to convince customers who only look at an database as the backend to their application. If one is looking only from the apps perspective on the whole topic a database upgrade might be something totally useless simply costing time, effort and money.

These facts might convince one or the other customer:

#1: Oracle support period will end soon or has ended.
As there are tons of customers still on 10.2 the have left now even the first year of free-extended support - meaning no bug fixes unless you pay 20% of your support fee on top to get extended support for critical issues for another year - I'd call this "Wake Up Call" ;-)

#2: The application provider is pushing you to uprgade.
Oracle EBS, Siebel, Peoplesoft, JD Edwards, even SAP - current supported versions will require Oracle 11.2 - so you'll have to go.

#3: CPU or PSUs - security fixes.
The news (and I mean ALL news, not even the yellow press) is full of reports and hacked and cracked systems. It is essential to have the most current security fixes. And you'll get them only during the premier support period unless you pay extra for extended support.

#4: Potential cost savings part 1.
What is the current cost driver in modern data centers? Fast disks taking tons of energy especially for cooling. 11.2 offers advanced compression and everybody can try this out with the free compression advisor beforehand. If I get acces to a customers system showing them that we could save half of the disk space this matters and pays back quite quickly if you consider energy costs and the cost for new storages.

#5: Potential cost savings part 2.
Active Data Guard. Utilize your standby for reports, offload reader traffic such as Apple does with iTunes platform. And amazon.com does as well. You'll avoid new hardware for a longer time, you'll give end users a better experience as you decrease the traffic and spread it. But this will make only sense if you have a standby.

#6: Faster access to LOB data - move to Secure Files.
Doesn't cost anything extra, is available in SE, EE etc. And will give your end-users much faster access to CLOB/BLOB data in the database. 


Monday Jul 04, 2011

Database Registry: Component Dependencies

Recently I had to dig a bit into the component dependency topic as I was working with an Oracle Database 10.1.0.5 customer planning to upgrade to Oracle Database 11.2.0.2 with a limited downtime window. To achieve the given downtime window we planned to remove some unused components from the database.

Database Component Dependencies

Example: Component Dependencies in Oracle Database 11.1

 As you know already the time it'll take to upgrade an Oracle database is mainly dependent on the number of installed components. If you'll query DBA_REGISTRY (or the underlying REGISTRY$) you'll spot all the installed (and usually configured) database components. And the more of these components are installed the longer the upgrade usually will take.

Please don't get me wrong here: This is NOT a recommendation to deinstall any components. Tomorrow or the day after somebody wishes to use some component functionality - and not nice if you have deinstalled it.

But first of all it might be a good idea to know a little bit about component dependecies - and on the other hand this might help you in one or another situations to fix INVALID components.
And please be advised: The presentation I'm refering to is not a official support note. And as it is the first release of this presentation I don't guarantee that every mentioned workaround is the best available - or will work in any environment. Please check also the referenced notes in case you'd like to "tweak" one or the other component.

You might download the presentation either via the DOWNLOAD SLIDES section in the right column of this blog (please scroll down a bit) or via this direct link here.

Database Component Dependencies Oracle 11.1
Example: Component Dependencies in Oracle Database 11.1 displayed a different way

And as always I'm happy to get your feedback :-)

Thx - Mike

P.S. Unfortunately the graphs can't be shown as animated slides in the PDF version - sorry for that, I know that the graph slides are a bit complicated ...

Wednesday Jun 29, 2011

Too many heap subpools might break the upgrade

Recently one of our new upcoming Oracle Database 11.2 reference customers did upgrade their production database - a huge EBS system - from Oracle 9.2.0.8 to Oracle Database 11.2.0.2. They've tested very well, we've optimized the upgrade process, the recompilation timings etc. 

But once the live upgrade was done it did fail in the JAVA component piece with this error:

begin if initjvmaux.startstep('CREATE_JAVA_SYSTEM') then
*
ORA-29553: classw in use: SYS.javax/mail/folder
ORA-06512: at "SYS.INITJVMAUX", line 23
ORA-06512: at line 5

Support diagnosis was pretty quick - and refered to:
Bug 10165223 - ORA-29553: class in use: sys.javax/mail/folder during database upgrade

But how could this happen? Actually I don't know as we have used the same init.ora setup on test and production. The only difference: the prod system has more CPUs and RAM. Anyway, the bug names as workarounds to either decrease the SGA to less than 1GB or decrease the number of heap subpools to 1. Finally this query did help to diagnose the number of heap subpools:

select count(distinct kghluidx) num_subpools from x$kghlu where kghlushrpool = 1;

The result was 2 - so we did run the upgrade now with this parameter set:

_kghdsidx_count=1

And finally it did work well.

One sad thing:
After the upgrade did fail Support did recommend to restore the whole database - which took an additional 3-4 hours. As the ORACLE SERVER component has been already upgraded successfully at the stage where the error did happen it would have been fine to go on with the manual upgrade and start catupgrd.sql script. It would have been detected that the ORACLE SERVER is upgraded already and just picked up the non-upgraded components.

The good news:
Finally I had one extra slide to add to our workshop presentation ;-)

Monday May 23, 2011

Time Zone Upgrade might be slow

One of the best things with my role in Oracle's Upgrade Development is:
I work with customers - and I learn a lot from customers.

This is an issue Richie and his team from Accenture in Ireland brought to my attention. I try to support them currently in an EBS database upgrade. And he did highlight a potential issue with the post upgrade time zone change to me:

Time ZonesBug 10209691 - slow performance on ALL_TSTZ_TAB_COLS
Workaround: alter session set "_with_subquery"=materialize;

All together the upgrade of an Oracle 9.2.0.8 EBS 11i database takes approx 30 minutes. And we still do recommend upgrading the Time Zone Data post upgrade as first of all we use the data type TIMESTAMP WITH TIMEZONE internally in the dictionary and - more important - the scheduler relies on correct settings. Otherwise it could happen that jobs don't get executed.

Of course Roy and me added this information to the slides already ;-)


Thursday May 19, 2011

Upgrade Fallback: Offline Backup??

When me an Roy talk about Fallback Scenarios in our Upgrade Workshop we cover first issues happening during the upgrade and second issues happening a few days (hours, weeks) after the upgrade.

One of the fallback options for issues encountered during the upgrade is to take an offline backup. Ok, not a complete offline backup of your 24TB database. Take all data tablespaces into read-only mode, then shutdown the database. Take a backup of all datafiles for SYSTEM, SYSAUX and UNDO plus the controlfiles plus the redologs. TEMP files are not a requirement as you could also ALTER TABLESPACE TEMP ADD TEMPFILE afterwards. Or you copy in TEMP datafiles as well. Now you do the upgrade and in case something fails you simply would shutdown, copy in the backuped files and startup again in the pre-upgrade environment.

Today Bartosz Mróz who was at the Upgrade Workshop last week in Warsaw sent me an email complaining that he has gotten an error:
ORA-01647: tablespace 'TOOLS' is read-only, cannot allocate space in it
when he did follow my recommendation :-(

TOOLSShame on me - and again I appologize and change the slides. The TOOLS tablespace got introduced a while back (was it Oracle8?) to move data out of SYSTEM and place repositories such as for the Developer Suite etc. into that tablespace, and have it not in SYSTEM anymore. A concept we later again introduced with SYSAUX in 10g onwards. So TOOLS has to be included in the list of files you have to offline backup as well.

Still the idea of the idea is to not recover your complete 24TB database in case something fails during the upgrade. And you could test read-only queries as well before changing the data tablespaces back to read-write.


Update 3-JUN-2011:

Again I have to appologize as the list of tablespace is not complete - neither here nor on the slides. I will most likely work well on a 10g/11g database but not for a 9i database with many components such as OLAP, UltraSearch etc. in it. Simple reason: In Oracle 8i or 9i these components had their own designated repository tablespaces. And once you set them READ ONLY during upgrade the component upgrade will fail.

Sorry again: this here should be the correct and complete list of tablespaces having to stay in READ WRITE mode during upgrade: SYSTEM, SYSAUX, TEMP, UNDO, TOOLS, DRSYS, XDB, ODM

Thanks again Bartosz!

 Mike


 
  

About

Mike Dietrich - Oracle Mike Dietrich
Senior Principal Technologist - Database Upgrade Development Group - Oracle Corporation

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

Contact me either via XING or LinkedIn

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
4
5
6
9
10
12
13
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
Slides Download Center
OOW Slides Download
Visitors since 17-OCT-2011
White Paper and Docs
Oracle Blogs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers