Best Practice

Save Patching Downtime with Oracle Multitenant?

Posted on December 2, 2019 by Mike.Dietrich Best Practice Patch Recommendation Single-/Multitenant 2

At OOW 2019 we announced that you can have up to 3 user-created pluggable databases (PDBs) per container database (CDB) without having a Multitenant license. And in this blog post I’d like to demonstrate how you can save patching downtime with Oracle Multitenant.

The Concept

The idea this entire process settles on is very simple. When you create a fresh container database in the target (already patched!) environment, you don’t have to patch this one. You only have to relocate the PDB from the source into the target CDB. In addition, only datapatch needs to be executed. As the …

When and how should you change COMPATIBLE?

Posted on April 17, 2019 by Mike.Dietrich Best Practice 12

When and how should you change COMPATIBLE?

COMPATIBLE is an almost mystic parameter. It has a default setting for each release. But if you try to find more information what it really does, you won’t be very happy. And in reply to my previous blog post about whether you need to change COMPATIBLE when you apply an RU, I received the following question: When and how should you change COMPATIBLE?

What does COMPATIBLE do?

To find an answer to this question, I consulted the documentation at first. And I found this:

COMPATIBLE
Setting COMPATIBLE ensures that new features do not write data formats or structures

Should you change COMPATIBLE when you apply an RU?

Posted on April 10, 2019 by Mike.Dietrich Best Practice 11

I have a million ideas for blog posts. But I like it even more when people ask me to explain something on the blog which isn’t there yet but may be interesting for others as well. And Robert Ortel mailed me the other day and asked if he should change COMPATIBLE when he applies an RU for Oracle 18c. That’s a good question. And I doubt that the documentation has a good recommendation.

Should you change COMPATIBLE when you apply an RU?

No.

Should you change COMPATIBLE when you apply an RU?

That’s a short blog post this time, isn’t it? 🙂 But honestly, you shouldn’t touch COMPATIBLE for …

Creating CDBs and non-CDBs with less options

Posted on August 8, 2018 by Mike.Dietrich Best Practice DBCA Single-/Multitenant 2

I wanted to write up this blog post a long time ago. When Helen Vanderheide did ask me a few weeks ago if she can omit catoctk.sql during the creation of the database, I realized that I should update my previous blog posts about Creating CDBs and non-CDBs with less options. And of course, the following is usable with non-CDBs as well.

You’ll find an example and especially the component dependency matrix below as well as many links to related blog posts at the end. If you don’t want to read all this, just scroll down to the Summary…

New preupgrade.jar and changes in MOS Note:884522.1

Posted on May 7, 2018 by Mike.Dietrich Best Practice 12

We read and listen to your comments. And that’s why my team mate Byron has added a new preupgrade.jar and changes to MOS Note:884522.1. One reader commented that MOS Note:884522.1 (How to Download and Run Oracle’s Database Pre-Upgrade Utility) does not explain how to “run” the preupgrade tool even though it mentions this in the note’s title.

New preupgrade.jar and changes in MOS Note:884522.1

With MOS Note:884522.1 you can download always the most recent versions of the Oracle 18c and 12.2 preupgrade.jar tools, of the Oracle 12.1.0.2 preupgrd.sql and utluppkg.sql. And you’ll even find the last utlu112i.sql for upgrades …

Oracle Database 18c: A new preupgrade.jar is available

Posted on March 19, 2018 by Mike.Dietrich Best Practice 2

In case you plan to upgrade a database to Oracle Database 18c: A new preupgrade.jar is available for download from MyOracle Support (MOS).

Oracle Database 18c: A new preupgrade.jar is available

Please download it – as always – from MOS Note:884522.1.

The same MOS note will get you also the most recent versions of the preupgrade.jar for Oracle Database 12.2.0.1 upgrades. Of course you can download also all previous incarnations such as preupgrd.sql for 12.1.0.2 upgrades and even utlu112i.sql for 11g upgrades.

What’s new in preupgrade.jar for Oracle 18c upgrades?

There are a few important and good Improvements and …

Multiple Hops – Which should be the intermediate release?

Posted on February 13, 2018 by Mike.Dietrich Best Practice 0

This is a topic which doesn’t come up very often. But if it does come up from time to time, and the documentation may give you advice – but as I realized – it does not always give the best advice. When you have to do multiple hops – which should be your intermediate release?

I posted something about this topic a while back:

Multiple hop upgrades? Execute the matching preupgrade scripts for each hop

What are Multiple Hops?

We speak of multiple hops in relation to database upgrades when your source release does not allow you to upgrade directly …

preupgrade.jar January 2018 (Build 11) is available now

Posted on January 19, 2018 by Mike.Dietrich Best Practice 12

A new version of the preupgrade.jar January 2018 (Build 11) is available now.

Please download it from MOS Note:884522.1.:

MOS Note: 884522.1 – How to Download and Run Oracle’s Database Pre-Upgrade Utility

Please always download the most recent version as this one is much newer with a lot of improvements compared to the version you get with the default installation. Major changes since the first drop are:

Added load only capabilities.
Improved the interaction with OS
Added more obsolete/deprecated parameters
Improved IPC calls on Windows
Added functionality for aud$unified table partitions.
Added preupgrade and postupgrade automatic fixups
Check for

Book Recommendation: Oracle Database Upgrade and Migration Methods

Posted on December 19, 2017 by Mike.Dietrich Best Practice 4

Thanks guys, I received my copy 🙂

APRESS: Oracle Database Upgrade and Migration Methods

And today I’d like to write a book recommendation: Oracle Database Upgrade and Migration Methods.

The book got authored by these 3 true database upgrade and migration experts:

And it’s cool book mostly for techies. It has plenty of examples in all details. A lot of good tips and tricks (for instance: Disable the Broker when you upgrade your database with standby in place). All of it from …

utlrp.sql – How to decrease resource consumption with utlprp.sql

Posted on October 11, 2017 by Mike.Dietrich Best Practice 4

utlrp.sql - How to decrease resource consumption with utlprp.sql Just being back from Oracle Open World 2017 I’m working on the open topics, the homework, the follow-up activities. And I took several notes during OOW when customers asked me about things I’ve seen before.

One topic I wanted to write about a long time ago already: utlrp.sql – How to decrease resource consumption with utlprp.sql.

Where it started

Actually it’s all Uwe’s fault (Uwe Kirchhoff is one of my best and oldest friends at Oracle and one of the best ACS guys I can think of). Uwe told me this story about a customer case he …

Other people’s thoughts: “Should you upgrade to 12c?”

Posted on August 4, 2015 by Mike.Dietrich Best Practice Oracle Database 12c Upgrade / Migration 1

You still don’t believe me yet when I say: You can’t seriously wait for the so called 2nd release of Oracle Database 12c as it will be an entirely new release again? And you haven’t upgraded yet to Oracle Database 12.1.0.2 for various reasons?

Then sometimes it’s good to listen to other people’s opinions – and I’m happy to share this 6 minute video by Tim Hall (very well known for his great page oracle-base). Listen to Tim and his Thoughts about Upgrading to Oracle Database 12c. And don’t get disturbed that he’s driving on the wrong …

The MAGIC Questions

Posted on May 27, 2013 by Mike.Dietrich Best Practice 2

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 …

Potential check for corruptions

Posted on January 17, 2013 by Mike.Dietrich Best Practice 0

Having a corruption somewhere in the database is one of the worst case scenarios I could ever imagine – especially if it “sleeps” somewhere in the data dictionary. Recently I did talk to a customer who encountered a failing upgrade due to a data dictionary corruption gotten introduced in an earlier release.

What can you do to check your database(s) prior to an upgrade or generally from time to time? Actually I know now two powerful possibilities:

hcheck.sql
See MOS Note:136697.1
This script will check for known problems in Oracle8i, Oracle9i, Oracle10g
and Oracle 11g.
You will need

Will gathering fixed object stats reduce recompilation time post upgrade?

Posted on May 30, 2011 by Mike.Dietrich Best Practice Oracle Database 11g 2

Interesting question, isn’t it?
Will the time to recompile invalid objects post upgrade decreased once fixed object stats have been gathered?

First of all fixed object stats on X$-tables won’t be gathered by default [This will change with Oracle 12c where it is part of the Auto Stats Gathering job].
X$ structures are undocumented. V$ views are build on top of them and
should only be used even though it might be sometimes useful to access
X$ tables such as X$BH (buffer headers – contains information describing the current contents of a piece of the buffer cache…

Upgrade Fallback: Offline Backup??

Posted on May 19, 2011 by Mike.Dietrich Best Practice Fallback 0

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 …

Move to Locally Managed Tablespaces

Posted on May 16, 2011 by Mike.Dietrich Best Practice 0

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

Cross-Platform Migration using Heterogeneous Data Guard

Posted on February 17, 2011 by Roy.Swonger Best Practice 2

Most people think of Data Guard as a disaster recovery solution, and it certainly excels in that role. However, did you know that you can also use Data Guard for platform migration under some conditions?

While you would normally have your primary and standby Data Guard systems running on the same OS and hardware platform, there are some heterogeneous combinations of primary and stanby system that are supported by Data Guard Physical Standby.

One example of heterogeneous Data Guard support is the ability to go between Linux and Windows on many processor architectures. Another is the support for environments that …

What an upgrade scenario …

Posted on July 14, 2010 by Mike.Dietrich Best Practice 1

Do you understand this one here 😉 ? No??

Don’t worry 🙂 I’ve taken the picture last week when we’ve run a very special internal training called the “Upgrade Tech Challenge” here in Munich. This was actually the upgrade plan to 11.2 of one of the groups involving a Real Application Testing run with SQL Performance Analyzer (SPA) and taking care about the fallback as well. And as all groups completed their challenge by Friday I’d suppose at least the group itself understood what they wanted to do here 😉…

Fix invalid objects and components – BEFORE you upgrade!

Posted on March 25, 2010 by Mike.Dietrich Best Practice 0

We are currently running a Tech Challange Workshop with 25 Oracle consultants and support folks from all over EMEA. We call it Tech Challange because we seperate these experts having between 5 and 20 years of Oracle experience into 5 groups – and each group has to complete their special challange such as moving a database from 10.2 to Exadata V2 or upgrading from single instance 10.2 to Real Application Clusters 11.2 with the new Grid Infrastructure.

Actually we start this training with a bit presentation pieces about upgrades, Real Application Testing and Golden Gate. And one topic I …

Gathering Workload Statistics

Posted on March 9, 2010 by Mike.Dietrich Best Practice 0

We refer in our upgrade talk to gather system (aka workload) statistics to give the optimizer some good knowledge about how powerful your IO-system might be. The optimizer since Oracle 9.2 takes CPU and IO costing into consideration. If you never have taken care on these stats you’ll find default values in AUX_STATS$. These values have been defined a few years back. So it might be a good idea right a few days after upgrading to the new release to create them while a real workload is running. Taking these stats does not generate any overhead or performance degredation …

Remove “old” parameters and events from your init.ora

Posted on March 8, 2010 by Mike.Dietrich Best Practice 0

It’s cleanup time!!

When upgrading to a new database release, especially Oracle Database 11g Release 1 or Release 2, you should always remove old init.ora parameters from the init.ora/spfile when you upgrade. For instance we’ve had a lot of useful (and necessary) tuning parameters in Oracle 9i environments such as _always_anti_join=off, star_transformation=false or several events.

Make sure you remove all of them when you upgrade as keeping them will definitely slow down the database performance in the new release. You don’t believe this? Then see this result of a customer case having a packaged OLTP workload for the …

What does an upgrade has in common with mountain hiking?

Posted on September 28, 2009 by Mike.Dietrich Best Practice 0

You might ask yourself what an Oracle database upgrade and mountain hiking have in common?

On first sight: really nothing. But if you come a bit closer and look more into the details there are some similarities. Maybe more than you’d thought at the beginning.

Being well prepared is of vital importance for your upgrade success!

First of all, likewise a hike an upgrade has to be prepared very well. You probably won’t do an 5 hour hike without reading a tour description beforehand, grab a good map, check your equipment the day before. And with an Oracle upgrade it …

How long will your upgrade take?

Posted on September 8, 2009 by Mike.Dietrich Best Practice 6

It seems to be a simple question but to predict the upgrade duration is really hard to do.

Due to the fact that an Oracle database upgrade doesn’t touch or change data the duration is totally independent of the size of the database. It won’t make any difference if the database is 2 GB or 28 TB of size.

The main factors determine the upgrade duration in descending order are:

Number of installed database components and options – the more components/option gotten installed, the more upgrade scripts will have to be run, the longer it’ll take

Valid and non-stale

Is there a FALLBACK strategy?

Posted on August 19, 2009 by Mike.Dietrich Best Practice 4

Having a valid and complete backup before you start your upgrades is key.
But how do you plan your fallback strategy?

The first question you’ll have to ask is:
Are you allowed to loose any data updates in case of going back – Yes or No?

If you say “Yes we can!” because you would be able to redo any changes you’ve done to the upgraded system already once you’ll go back, then restoring your backup from before the upgrade could be your easy fallback strategy.

You don’t have the possibility to do a complete online …

Backup your database!!!

Posted on August 18, 2009 by Mike.Dietrich Best Practice 2

Yes, I appologize for writing this sentence … “Backup your database!!!” … it sounds a bit frumpy, such as something from the past. But still nowadays with those high-class super storage solutions a valid backup can become of vital importance. Especially before you start the upgrade process.

RMAN (Oracle Recovery Manager) does a great job in backing up your database completely online. Have I said online? Yes!! Even though some Metalink notes still recommend to do an offline backup you shouldn’t stop your 58 TB data warehouse database for 2 days to do a complete offline backup. Use …

Best Practice

The Concept

What does COMPATIBLE do?

Should you change COMPATIBLE when you apply an RU?

New preupgrade.jar and changes in MOS Note:884522.1

Oracle Database 18c: A new preupgrade.jar is available

What’s new in preupgrade.jar for Oracle 18c upgrades?

What are Multiple Hops?

Where it started

Archives

Categories

Upcoming Events

Upgrade @YouTube

Blog Stats

Subscribe via Email

Follow me on Twitter

Recent Posts