Patch Recommendation

Parameter Recommendations for Oracle Database 12c – Part II

Best Practice Hint
See also:

Time for a new round on Parameter Recommendations for Oracle Database 12.1.0.2. The focus of this blog post settles on very well known parameters with interesting behavior. This can be a behavior change or simply something we’d like to point out. And even if you still work on Oracle Database 11g some of the below recommendations may apply to your environment as well.

Preface

Again, please be advised – the following parameter list is mostly based on personal experience only. Some of them are officially recommended by Oracle

Continue reading...

Parameter Recommendations for Oracle Database 12c – Part I

Best Practice Hint

See also:


A few weeks ago we’ve published some parameter recommendations including several underscores but based on an internal discussion (still ongoing) we decided to remove this entry and split up the tasks. The optimizer team will take over parts of it and I’ll post an update as soon as something is published.

Preface

Please be advised – the following parameter list is mostly based on personal experience only. Some of them are officially recommended by Oracle Support. Always use proper testing mechanisms.

We strongly recommend SQL Performance Analyzer to …

Continue reading...

How to find out if a PSU has been applied? DBMS_QOPATCH

pflaster.jpgSince we change the PSU and BP patch numbering from Oracle Database 12.1.0.2.PSU6 to 12,1,0,2,160119 it is almost impossible to distinguish from the patch name only if you have applied a PSU or a BP.

But:
In Oracle Database 12c there’s a package available which is very useful to query plenty of information about patches from within the database: DBMS_QOPATCH.

Here are a few helpful examples which I created by checking in our DBaaS Cloud database.

Which patches have been applied (or rolled back)?

SQL> set serverout on

SQL> exec dbms_qopatch.get_sqlpatch_status;

Patch Id : 20415564
        Action : APPLY
        Action 
Continue reading...

Oracle January 2016 CPU PSU BP available now – BE AWARE OF CHANGES IN PATCH NUMBERING

Last night the PSUs and BPs for January 2016 have been made available for download on support.oracle.com.

Oracle Critical Patch Update Advisory – January 2016

http://www.oracle.com/technetwork/topics/security/cpujan2016-2367955.html 

It contains 248 security fixes across all products and platforms. And of course important non-security fixes – and that’s why we recommend to apply the PSUs (or the BPs in case you are on Exadata or an Oracle In-Memory user) as soon as possible.

Change in Patch Numbering

Please be aware that as of November 2015 there’s been a change in patch numbering introduced which most of you may not be aware of. …

Continue reading...

Patch, patch, patch – Journey to the Cloud – III

What happened so far on my Journey to the Cloud?

DBaaS Oracle Cloud

I haven’t stopped my journey if you wonder about the inactivity for two weeks. I just had to learn …

Continue reading...

Ouch, this hurts: bug 21923026 – patch Oracle 12c Home before upgrading if you have OLTP Compression in 11g

Patch

Actually I’d consider this as a real serious issue which may affect many customers with larger deployments using the Advanced Compression Option’s OLTP Compression.

I came across it as Don Seiler full of anger twittered some bad words about Oracle Database 12.1.0.2.. We’ve exchanged a good number of emails – and I could see the issues Don and his colleagues got while assisting a customer to go live on Oracle Database 12c.

The most interesting one happened on the physical standby after the primary got upgraded. The MRP failed with an ORA-600.

Issue

After upgrading from Oracle 11.2.0.3 to …

Continue reading...

PSU (and CPU/SPU) October 2015 got released

October 21, 2015 – Oracle released the October 2015 SPU/CPU, PSU and BPs.

See:

For Oracle Database 12.1.0.2 and Oracle Grid Infrastructure access the PSUs from here – if you have only a single instance database you can safely take the Combo patch as you’ll get downtime anyways – but for customers running RAC you need to evaluate the OJVM component as this will incur …

Continue reading...

Ouch, this hurts: bug 20880215 – patch Oracle 12c

Patch

Just realized that my preivous blog post heading was misleading: it said “patch AFTER upgrade” which is misleading. You should apply this patch BEFORE you upgrade but of course to your new/future Oracle 12c home. If you hit the issue then apply the patch after upgrading to remedy it. Sorry for the confusion.


No updates for over a week? Sorry for that but too many workshops, swamped with OOW prep work – and a wonderful issue with the browser cache and the blog software putting all my new blog post work directly into /dev/null.

Thanks to Marcel Paul for

Continue reading...

No OS Authentication? datapatch will fail in every upgrade

Thanks to Daniel from SimCorp for bringing this to my attention:Patch
Actually, command line upgrades are affected as well, if you do not use OS authentication.
Apparently, datapatch is not able to execute in non-OS authentication mode.
See MOS note 1635007.1.

Symptom:

You are doing a command line upgrade to Oracle Database 12c with catctl.pl – and you don’t use OS authentication allowing connections with “/ as sysdba” then datapatch.pl won’t be able to execute the SPU/PSU/BP related SQL commands as it will fail to connect to your database with an ORA-1017 (invalid username/password) error.

Solution:

Bug 18361221 …

Continue reading...

SQL Plan Management – Known Issues in Oracle 12.1.0.2

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

SPM Note

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

Some additional things to mention:

  • SPM is an Oracle Enterprise Edition feature at no
Continue reading...

Don’t get puzzled by “sqlpatch” messages during Upgrade

During my last Hands-On-Labs in Uruguay and Argentina I’ve had several people wondering about these messages below when running the command line upgrade with catctl.pl:

catctl.pl - sqlpatch

This (and another) message breaks the nicely structured format of the catctl.pl output. And as it ends with an “err” extension it looks to many people as if the upgrade had gotten an error,

But please don’t feel disturbed. It’s just messages from sqlpatch invocation – and the “err” extension is just pointing to an error file in case something has gone wrong.

In a future release such messages …

Continue reading...

No patches anymore for Oracle Database 11.2.0.3

Just in case you’ve missed the date please be aware:

No bug fixes, no patches, no CPU/SPUs, no PSUs, no BPs will be produced for Oracle Database 11.2.0.3 since Augugst 28, 2015

I know from the many emails I’m receiving that people are a bit disturbed since the Oracle Lifetime Support Policy brochure (Lifetime Support Policy and Brochure for Technology Products ) doesn’t talk explicitly about patch sets but offers support for Oracle 11.2 until January 2018.

Oracle 11.2 Lifetime Support Policy

But this – as always – applies to the terminal patch set only – i.e. Oracle Database 11.2.0.4.

For a detailed …

Continue reading...

Ouch, this hurts: bug 17325413 – patch BEFORE upgrade!

PatchI really don’t want to turn this blog into something making our database look bad. But in this case it is really necessary as it is VERY UNUSUAL that we recommend to patch the database BEFORE upgrade.


Just for clarification:

The following topic will affect all databases between 11.1.0.6 and 11.2.0.4.1 – those (and only those) need to be patched BEFORE upgrade. The topic is fixed in 12.1.0.2 but as it gets introduced with the BEFORE upgrade database version you’ll have to apply the fix before upgrade. The inclusion of the fix in 12.1.0.2 means only that the

Continue reading...

DBCA 12c and “datapatch.pl” – things to know

A few weeks ago I did blog about the DBUA (Database Upgrade Assistant) not executing ‘datapatch’ (i.e. not applying the SQL changes involved with a SPU/PSU/BP) automatically:

For DBUA, please note that this behavior DOES NOT APPLY to command line upgrades done with catctl.pl – as you can see from this somewhat disturbing messages during the upgrade in phase 65 and phase 69 (which are not errors but just informational messages for datapatch’s execution):

Datapatch phases command line upgrade catctl.pl

But afterwards I have learned that things are worse.
The same behavior is true when you create

Continue reading...

Grid Infrastructure Management Repository (GIMR) – Datapatch?

I have blogged about the Grid Infrastructure Management Repository (GIMR) a while back:

And Markus Michalewicz, our Director of Product Management, Oracle Real Application Clusters (RAC), has published a very interesting and helpful insight article about GIMR on July 30, 2015. Read it here:
https://www.linkedin.com/pulse/how-handle-oracle-gimr-markus-michalewicz


Since Oracle Database 12.1.0.2 the GIMR database will be created by default – and it is a single tenant database having a CDB$ROOT and one active PDB.

Recently the question came up if – in the likely event of applying a PSU …

Continue reading...

DBUA 12c and “datapatch.pl” – things to know

For clarification:
The following blog post applies to upgrades to Oracle 12.1 done by DBUA only whenever a SPU/PSU/BP is installed into the 12.1 home prior to the upgrade (which I’d highly recommend as patching before upgrade saves you headache after upgrade!).

Update [Dec 10, 2015]:
The required patch is included in  12.1.0.2BP13 but not 12.1.0.2.PSU5 (Oct 2015) – it will be included in the 12.1.0.2.PSU6 (Jan 2016) and all following PSUs.

Update:
For Oracle Database 12.2 and newer please see:
https://mikedietrichde.com/2017/05/25/dbca-execute-datapatch-oracle-database-12-2/


Two customers independently reported last week that they have doubts on DBUA’s ability to apply the required SQL

Continue reading...

Patching Best Practices – 38min Video

Patch Ever asked yourself about Database Patching Best Practices?

I know that not always everything works that simple and easy. See the blog post by Pieter Van Puymbroeck from Exitas in Belgium about some findings when applying the April 2015 PSU to an Exadata or by myself about my experiences when applying the GI PSU from Jan 2015.

But this 38 min video may give you a good overview about the best and recommended techniques at first hand by Eleanor Meritt and David Price, both from the Sustaining Engineering organization that issues bug fixes, patches and patchset updates

Continue reading...

Oracle PSU and BP April 2015 is available

As of April 14, 2015:

The April 2015 PSU/BP is available!

Here’s the most important information:

Continue reading...

Parallel Index Creation with Data Pump Import

Here is a new capability that might be interesting to anybody who is performing a migration using Data Pump. Previously, Data Pump would create indexes one at a time, specifying the PARALLEL keyword for the CREATE INDEX statement to invoke parallel query for index creation. We used to recommend a workaround to create indexes in parallel, which involved a three-step process of importing without indexes, then creating a SQLFILE of the CREATE INDEX statements, and breaking that file into multiple windows.

Through extensive performance testing we found that it is faster to create multiple indexes in parallel (using a parallel …

Continue reading...

Applying a PSU or BP to a Single-/Multitenant Environment

I have already explained in broad details a while ago how to:

But one may miss the steps for applying a PSU (Patch Set Update) or BP (Bundled Patch) to a Single-/Multitenant Environment.

At first everything will work just the same if you choose the Everything-at-Once strategy as datapatch will adjust all the required things regardless of being executed in a stand-alone or a singe/Multitenant environment.

But what happens if you apply a …

Continue reading...

Grid Infrastructure PSU Jan 2015 – Am I too intolerant?

Germans are not only known for being precise and timely – but sometimes also for being too direct. Well, Roy could tell you stories … and I always honor his politeness 🙂

Enough about stereotypes. I work with a customer at the moment on their 12c upgrades. And I did recommend the most recent PSU (Patch Set Updates) for their Grid Infrastructure environments running Oracle Restart. Same of course for the database homes but this blog post will just name some findings I’ve had the other night when trying to apply the January 2015 GI PSU to my …

Continue reading...

New MOS Note:1962125.1 – Overview of Database Patch Delivery Methods

Usually I don’t announce MOS Notes but this one may be very helpful to sort out between all the different patches available these days for the database only. From Patch Sets to PSUs to SPUs to Interim Patches to Bundle Patches and so on.

Plus it includes also recommendations for testing and if you should apply them on a regular basis.

A very important MOS Note for most DBAs:

MOS Note:1962125.1
Overview of Database Patch Delivery Methods

MOS Note - Patch Delivery Methods

–Mike…

Continue reading...

Sleeping Beauties – Upgrade to 11.2.0.4 can be slow

A customer from the US did contact me past week via LinkedIn and raised a question:

“Is it expected that my patch set upgrade from Oracle 11.2.0.3 to Oracle 11.2.0.4 takes over 3 hours?”

Of course, no – this is not expected.

This is the upgrade stats gathered post upgrade with utlu112s.sql:

SQL> @?/rdbms/admin/utlu112s.sql ; .
Oracle Database 11.2 Post-Upgrade Status Tool
10-31-2014 10:05:29
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
Oracle Server
. VALID 11.2.0.4.0 02:46:19

JServer JAVA Virtual Machine
. VALID 11.2.0.4.0 00:08:34
[..]
Final Actions
. 00:00:00
Total Upgrade Time: 03:06:47

No, this …

Continue reading...

PSU October 2014

October 14, 2014 Oracle released a new Patch Set Update. And as usual we strongly recommend that you’ll apply it as soon as possible to your databases.

There’s one fix for JAVA in it which requires to either take the database down or do some other actions. Please find a detailed description here:

Continue reading...

Avoid Poor Performance and Wrong Results in 11.2.0.4

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

Continue reading...