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.

-Mike

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

$ execute DBMS_STATS.GATHER_DICTIONARY_STATS;

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:

_optim_dict_stats_at_db_cr_upg=FALSE

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

-Mike

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
workshop.

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:

SQL> select TABLESPACE_NAME, EXTENT_MANAGEMENT from DBA_TABLESPACES;


The procedure to migrate to locally managed tablespace is:

SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('tbs');

And the whole migration procedure would look like this:

STARTUP RESTRICT EXCLUSIVE;
EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TBS');
-- do this for all tablespaces except SYSTEM, TEMP and SYSAUX
DROP TABLESPACE TEMP;
-- necessary if there's no "real" temp tablespace definded yet - see Note:160426.1
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'f' SIZE 10M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
ALTER TABLESPACE SYSAUX OFFLINE;
ALTER TABLESPACE USERS READ ONLY;
-- for all tablespaces except SYSTEM, RBS, TEMP and SYSAUX
EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');
ALTER TABLESPACE SYSAUX ONLINE;
ALTER TABLESPACE USERS READ WRITE;
-- for all tablespaces except SYSTEM, RBS, TEMP and SYSAUX
SHUTDOWN IMMEDIATE

STARTUP


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