Oracle 12.2 Adaptive Features are enabled in Oracle 12.1.0.2 Windows BPs

Oracle 12.2 adaptive features are enabled in Oracle 12.1.0.2 Windows BPs already, This is quite a surprise to me. But thanks to Michael Pfautz. He alerted me today.as he was wondering why his database didn’t start up anymore after he applied the August 31 Bundle Patch on Windows. It complained about the parameter optimizer_adaptive_features in the SPFILE. Michael had to remove this parameter before he could startup his database again. Oracle 12.2 Adaptive Features are enabled in Oracle 12.1.0.2 Windows BPs We recommend to add two very important parameters to Oracle Database 12.1.0.2 installations. Enabling Oracle 12.2 Adaptive Features in…

How to patch the Oracle Instant Client?

That’s an interesting question a customer raised the other day: How to patch the Oracle Instant Client? I silently assumed that there’s a new release of the Oracle Instant Client every quarter or at least when we deliver fixes which apply to the client as well. Hm … What is the Oracle Instant Client? First of all, the Oracle Instant Client is a pretty cool thing. It gets distributed via OTN as a simple download. You deploy it to your client machine, you set LD_LIBRARY_PATH (or PATH on Windows) and add a tnsnames file if necessary, you may add SQL*Plus…

Oracle 12.2.0.1 RESTART: Make sure you’ve patched your Oracle 12.1.0.2 homes

This blog post title sounds a bit weird, doesn’t it? Oracle 12.2.0.1 RESTART: Make sure you’ve patched your Oracle 12.1.0.2 homes. Why the heck should you patch your 12.1.0.2 database homes upfront? Oracle 12.2.0.1 RESTART: Make sure you’ve patched your Oracle 12.1.0.2 homes Well, there’s an interesting issue happening. When you work with Oracle Grid Infrastructure, no matter if you operate a cluster or “just” use Oracle Restart, you’ll have to upgrade GI to Oracle 12.2 first before you can manage 12.2 databases with it. That’s not new. It’s a well understood rule. Now guess you operate Oracle RESTART (SIHA…

More Information about RU and RUR patches for Oracle 12.2

The first RU (Release Update) for Oracle Database 12.2.0.1 has been released. And Oracle Support provides some very helpful documents to explain the implication of the new RU (Release Update) and RUR (Release Update Revision) patches. More Information about RU and RUR patches for Oracle 12.2 First of all, these are the most important MOS notes to have a look at: MOS Note:742060.1 – Release Schedule for Current Database Releases (updated Aug 3, 2017 ) MOS Note: 2285040.1 – Release Update and Release Update Revisions for Database Proactive Patch Program MOS Note: 2275525.1 – FAQ for RU and RUR (not…

The first RU for Oracle Database 12.2.0.1 is available

The first RU (Release Update) for Oracle 12.2.0.1 is available Just in case the port you are looking for is not available right now please see the ETA dates in the link below. Details of the July 2017 Critical Patch Advisory Find the General Information about the July 2017 Critical Patch Advisory here: http://www.oracle.com/technetwork/security-advisory/cpujul2017-3236622.html Find the Risk Matrix for the July 2017 Critical Patch Advisory here: http://www.oracle.com/technetwork/security-advisory/cpujul2017verbose-3236625.html#DB The details about the Database are included in this document: MOS Note: 2261562.1 – Patch Set Update and Critical Patch Update July 2017 Availability Document Please find the “Missing patches” in section 2.2…

Issue with 2k and 4k db_block_size – ORA-1450 when upgrading to Oracle 12.2.0.1

Recently we got alerted by a customer and a colleague from Italy about an issue with the upgrade from Oracle 12.1.0.x to Oracle 12.2.0.1 if – and only if – your database got created with 2k or 4k db block size. Problem If your database got created with 2k or 4k db block size, and you attempt an upgrade from any upgrade-supported Oracle version to Oracle Database 12.2.0.1 the upgrade – regardless of DBUA or catctl.pl – will fail with an ORA-1450: maximum key length (1478) exceeded. Analysis In the catupgrd0.log – regardless of using the DBUA or the command…

Oracle 12.2.0.1 Bundle Patch 12.2.0.1.170516 DBBP on Linux x86-64 is available [not anymore]

All credits go to Ricardo Maeda as I knew that we’ll release a Bundle Patch for Oracle 12.2.0.1 sometime this week – but I couldn’t find it linked from the usual MOS notes. And please don’t ask my why that is. Anyhow, with patch 2579308 you’ll get access to the first BP for Oracle Database 12.2.0.1. There will be a first bigger Proactive Bundle Patch in July at the usual schedule – but this one is at least a start. Plus in addition get the OPatch version 12.2.0.1.7 via patch 6880880. The BP contains: Obrigado, Ricardo! Update June 9, 2017…

Use the correct download to patch APEX from 5.1.0 to 5.1.1

When you plan to upgrade Oracle Application Express (APEX) from version 5.1.0 to 5.1.1 please be aware to download a patch from MOS instead the 5.1.1 version from OTN. The text on the OTN page is a bit misleading – and a customer I work with downloaded the wrong version which led to a failed upgrade – even though the scripts seem to be made for this patch upgrade. Even though the page says “This is a cumulative patch set for Application Express 5.1.0” the page offers you a full install and not the patch for an existing APEX 5.1.0…

Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2

Oracle Database 12.2 introduces the new split-up adaptive parameters, OPTIMIZER_ADAPTIVE_PLANS and OPTIMITER_ADAPTIVE_STATISTICS. And the key trigger to cure potential and known performance issues in Oracle Database 12.1.0.2 is enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2 For more information please see: https://mikedietrichde.com/2016/11/22/optimizer_adaptive_features-obsolete-in-oracle-12-2/ https://blogs.oracle.com/optimizer/entry/optimizer_adaptive_features_in_the Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2 But Oracle Database 12.2 on-premises is not out yet – so what should you do when upgrading to Oracle Database 12.1 – or struggling with some of the “adaptive” features in Oracle 12.1? MOS Note: 2187449.1 Oracle Highly Recommended Adaptive Feature Configuration Parameter Settings for 12.1.0.2.0 It recommends to…

OPTIMIZER_ADAPTIVE_FEATURES obsolete in Oracle 12.2

The Oracle Database 12.1 parameter OPTIMIZER_ADAPTIVE_FEATURES has been made OBSOLETE (i.e. must be removed from the SPFILE when upgrading) in Oracle Database 12.2. It gets replaced with two parameters of whom one is enabled, the other one is disabled by default: OPTIMIZER_ADAPTIVE_PLANS=TRUE by default OPTIMIZER_ADAPTIVE_STATISTICS=FALSE by default Nigel Bayliss, our “Optimizer” Product Manager has blogged about it already with way more detailed insights. https://blogs.oracle.com/optimizer/entry/optimizer_adaptive_features_in_the But as Oracle Database 12.2 on-premises is not available how should you deal with this feature in Oracle Database 12.1? Enabling ADAPTIVE Features of Oracle 12.2 in 12.1     –Mike .

RMAN Recovery Catalog with July PSU: How to resolve RASCHEMAVER and BASCHEMAVER schema mismatch

Related blog posts: RMAN Catalog Upgrade fails – ORA-02296 – error creating modify_ts_pdbinc_key_not_null (April 19, 2016) RMAN Catalog requires Enterprise Edition (EE) since Oracle Database 12.1.0.2 (April 22, 2016) RMAN Catalog Upgrade to Oracle 12.1.0.2 (August 1, 2014) Thanks to Ah Huat Tan from Amway IT Services for keeping me updated! Actually as I see that more people who got hit by this issue so I’d consider it to be worth to write about it. Problem and Analysis You’d apply the July 2016 PSU or BP. According to the readme you are required to upgrade your RMAN catalog afterwards. The…

OJVM patch: Standby-First patching, yes or no?

I have blogged in the past more than once about the “wonderful” topic of OJVM patching. Anyhow,  I’d like to summarize a few typical questions sent to me in the past months regarding OJVM patching. Do I need to apply the OJVM patch every time I apply a PSU or BP? Unfortunately this is not a one-time-and-then-you-are-set operation. You’ll have to do it every time. How do I find out if OJVM is used in my database? I tried to nail this down in this blog post here: Java in the database – OJVM non-rolling patches – OJVM Removal But…

PDB unplug/plug/patch with PSUs or BPs

This question was posted on the blog a few days ago referring to my previous blog posts describing the two techniques to upgrade in an Oracle Multitenant environment: Everything at Once One at a Time — via unplug/plug Question:  We are planning to upgrade from 12.1.0.2.1 to 12.1.0.2.5. The plan is to create new 12.1.0.2.5 oracle home and gradually moving PDB from 12.1.0.2.1 to 12.1.0.2.5. Should I follow the same steps? Is there any document for what we want to do? And luckily there is a MOS Note out there describing the steps: MOS Note:2102849.1 Unplug/Plug on different PSU environments on 12.1.0.2…

Speed up Upgrade Phase 65 with a new catuposb.sql

Credits to Chris Smids from Proximus in Belgium 🙂 Thanks, Chris!!! Upgrade to Oracle 12.1.0.2 is slow in phase: #65 ? You are wondering why phase: #65 of the database upgrade to Oracle Database 12.1.0.2 takes quite a while. You dig down into the catupgrd0.log and recognized this statement taking a while: dbms_output.put_line(‘catuposb, update 4 – rows updated ‘ || rows_updated); END; — end of update for system internally generated objs / The cause for this issue is buried in the script catuposb.sql hitting stale histograms which did not get refreshed even if you gathered dictionary stats before the upgrade as…

RMAN Catalog Upgrade fails – ORA-02296 – error creating modify_ts_pdbinc_key_not_null

This issue got raised to my via a customer I know for quite a while – all credits go to Andy Kielhorn for digging down into that issue and solving it. Failed RMAN Catalog Upgrade from 11.2.0.4 to 12.1.0.2 The RMAN catalog upgrade: SQL> @?/rdbms/admin/dbmsrmansys.sql $ rman CATALOG rman/xxx@rman01 RMAN> UPGRADE CATALOG; RMAN> UPGRADE CATALOG; failed with the following sequence of error messages: error creating modify_ts_pdbinc_key_not_null RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-06004: ORACLE error from recovery catalog database: ORA-02296: cannot enable (RMAN.) – null values found error creating modify_tsatt_pdbinc_key_not_null RMAN-00571: =========================================================== RMAN-00569: =============== ERROR…

Patching does not work – Journey to the Cloud VI

What happened so far on my Journey to the Cloud? Part I – Push a Button (Dec 3, 2015) Part II – Switch On/Off and Remove (Dec 4, 2015) Part III – Patch, patch, patch (Dec 22, 2015) Part IV – Clean Up APEX (Jan 19, 2016) Part V – TDE is wonderful (Jan 28, 2016) You are here ==> Part VI – Patching does not work (Apr 18, 2016) Part VII – APEX is in CDB$ROOT again (Dec 21, 2016) Patching in the Cloud I would like to patch my Oracle DBaaS Cloud today. It was so simple a few weeks…

GC Freelist Session Waits causing slowness and hangs

One of the best things in my job: I learn from you folks out there. Everyday. Credits here go to Maciej Tokar who did explain the below topic to me via LinkedIn – thanks a lot, Maciej! Locks are not being closed fast enough, resulting in gc freelist waits You can find a reference for Global Cache Freelist in the Oracle Documentation. This issue here can or will lead to database being slow, up to complete hangs. Based on my research it looks as the issue is not related to RAC only but a general thing. In your session waits…

Parameter Recommendations for Oracle Database 12c – Part II

See also: Parameter Recommendations for Oracle Database 12c – Part I 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 Support. Always use proper testing…

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

What happened so far on my Journey to the Cloud? Part I – Push a Button (Dec 3, 2015) Part II – Switch On/Off and Remove (Dec 4, 2015) You are here! ==> Part III – Patch, patch, patch (Dec 22, 2015) Part IV – Clean Up APEX (Jan 19, 2016) Part V – TDE is wonderful (Jan 28, 2016) Part VI – Patching does not work (Apr 18, 2016) Part VII – APEX is in CDB$ROOT again (Dec 21, 2016) I haven’t stopped my journey if you wonder about the inactivity for two weeks. I just had to learn a bit…

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

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

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

I 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 misbehavior won’t…

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…

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

I have already explained in broad details a while ago how to: Upgrade a Multitenant Environment – Everything at Once https://mikedietrichde.com/2014/08/06/upgrade-pdbs-everything-at-once-full-cdb-upgrade/ Upgrade in a Single-/Multitenant Environment – One/Many at a time or also known as unplug/plug/upgrade strategy https://mikedietrichde.com/2014/08/05/upgrade-pdbs-one-at-a-time-unplugplug/ 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 PSU…

Let’s do the Time Warp again!

Once you start reading about Daylight Saving Time changes in MyOracleSupport you’ll find still a lot of notes explaining this and that and back and forth. But sometimes there seems to be a bit too much information – and lacking clear instructions. Once a customer called that the “Time Zone Spaghetti” after reading MOS notes about DST for several hours ending up with the note where he has begun to read before still not clear what to do now 😉 I’m using usually the scripts from MOS Note:977512.1 as you’ll just have to exchange the DST version you are upgrading…

New Time Zone Patch DST V18 is available

Sorry for not updating the blog more often at the moment – but more updates will come soon as I play around with Oracle Restart and single instance databases in ASM with Oracle 11.2. Just on the side there’s a new time zone patch to DST V18 available since May 2012. You can download it via PATCH download from MOS with the patch number: 13417321 What do you think? Will Lufthansa operate a faster jet the other night? Will the jet stream be more powerful? Or a better type of fuel? Or is it just the travel portal which hasn’t…