ODA -- "Patch your ODA with ODA Patch Bundle" - Stay away from PSUs, CPUs and/or other patches

I want to inform you on an important subject.. It is about patching Oracle Database Appliance.
You may already know this, but in some cases you may want to take the risk and apply one-off patches, PSUs or CPUs on your Oracle Database Appliance environments..

Firstly, I want you to know that, this is not a good idea!

Only in special circumstances, you may consider applying one-offs on ODA, but you still need to get the approval of Oracle Support by creating a SR.

But generally no PSU, no CPU!

Only the ODA Patch bundle...  The one-button patch specifically designed to upgrade Oracle Database Appliance firmware, OS, Grid Infrastructure, and Database PSUs.

Do you want to upgrade your GRID PSU or Database PSU on ODA? Then find a ODA Patch bundle that delivers those PSUs and go on with that..

For instance;

If you have Oracle Database Appliance 12.1.2.10.0 release, then we can say that your Oracle Database version is 12.1.0.2 or 11.1.0.4 .. Moreover, we can also say that your PSU level is 12.1.0.2.170117 (PSU) or 11.2.0.4.161018.

Well, if you want to upgrade your PSU version, then you should upgrade your ODA release to a newer ODA release, such as 12.1.2.12.0.

12.1.2.12 delivers Oracle Database Bundle Patch (BP) 12.1.0.2.170814 and Oracle Database Patch Set Update (PSU) 11.2.0.4.170814.

By doing such an upgrade, you also get an upgrade in the other layers and components such as OS, kernel, BIOS and etc.. (These are nice to have and but also required... Keep that in mind..)

Check the documentation for ODA software releases and all the related details... (Just select your ODA release from drop down menu and check the documentation of it) -> 

https://docs.oracle.com/en/engineered-systems/oracle-database-appliance/index.html

Also, now we have Oracle Support patch tab to find our patches for ODA..

Earlier it was not available.. 

Well, I have dealed with this machine almost since its birth, but then I took a break. So maybe it wasn't available at least for some years ago..

So let me tell you a quick real story about the effect of applying PSUs on ODA directly.

Yes, I have seen this.. 

I have seen a real bad effect of applying GRID and Database PSU into ODA X6-2 HA..

After patching GRID and RDBMS with traditional PSUs, at first everyting seemed okay.

However; the nightmare started when the database was opened.. Interestingly, one of the node rebooted itself, when the database was opened. After the reboot, the issue continued.. It was like an infinite power cycle..!

 

Very interesting, right? No clues in the OS side, no clues in CRS, ASM or ACFS logs.. No errors in agent logs, nothing in the database.. The node was rebooting itself directly when the database was started. 

The environment was a standard one.. A Bare Metal ODA X6-2 HA.. It was ACFS-based. So, if ACFS is present in the environment, it is even more dramatic.. That is, facing with this kind of an issue is becoming even more potential.

I still think that, the reboot problem was related with ACFS.. I mean, everytime when the database was opened and did some stuff on ACFS, the OS was crashing and we saw a reboot.. ( without any clues, believe me...) 

The only thing that I could see was in the "last" command output.. It was a crash.. Probably it was caused by a failing ACFS kernel module.. ( a fault in a kernel module may bring the system down, may result a direct crash just like the one I faced) 

That fail was probably an unexpected one, because even the dmesg command output was clean.. Normally, just a simple printk(KERN_ALERT ... would do the work for informing us.. So this must be an unexpected one.

Ofcourse, we could reproduce and trace it at OS level, but we didn't have that amount of time.. 

So, as suggested by the title of this blog post, Go on with ODA Patch Bundle. Stay away from PSUs...

Read the following MOS note for more->

• ODA Support Guidelines for Using Existing Interim Patches or Requesting New Interim/Merge Patch Requests (Doc ID 2081794.1)
• Oracle Database Appliance FAQ (Doc ID 1463638.1)
• ODA Patching FAQ : 18.3 and Lower (Doc ID 1546944.1)

RDBMS -- About Oracle Market-Driven Support for 11.2.0.4

Extended Support for Oracle Database 11.2.0.4 is planned to end. That is, at the end of 2020, there will be no extended support for 11.2.0.4. With this in mind, 11.2.0.4 customers were worried.. However; Oracle annouced the Market Driven Support for 11.2.0.4, and 11.2.0.4 customers seemed relieved...

I also informed my followers about this earlier, in my last 2 webinars.

http://ermanarslan.blogspot.com/2020/04/ebs-oracle-ebs-19c-upgrade-webinar.html
http://ermanarslan.blogspot.com/2020/04/rdbms-19c-upgrade-webinar-presentation.html

In this post, I want to give some more info about this Market Driven Support.. Let's move on and try to find some answers to confusing questions.

11.2.0.4 customers have risks -> As of 1 January 2021 (extended support ends), they will have NO access to: new bug fixes, new security updates, or other critical-issue patches.

Mission critical systems will face operational and security risks.Sustaining support doesn't generally address any newly discovered defects or vulnerabilities. This means, no new updates, patches, code fixes or security updates..

This is huge, right? We still have lots of customers using 11.2.0.4 databases and they are unfortuneatly not ready to upgrade at the moment.

Fortuneatly, Oracle gave a helping hand to the customers.

This helping hand is the offering of Market-Driven Support..

Market-Driven Support is for 11.2.0.4 customers only.

By having Market Driven Support in hand, customers should also have the latest PSU/BP applied to the 11.2.0.4 databases.( this is recommended)

This type of support is available for 2 periods, 1 Jan 2021 to 31 Dec 2021 and 1 Jan 2022 to 31 Dec 2022. So, it's available from the end of Extended Support (Jan 2021) until Dec 2022.

Some valuable Oracle ACS (Advanced Customer Services) services are also included in this offer.

Let's see the key service components included in Market-Driven Support ->

• Severity 1 fixes or workarounds for newly discovered severity 1 problems( for PROD env)

• Critical security updates  to address potential vulnerabilities and reduce downtime risk. Oracle will manage the scheduling and decide the contents of these security updates.. It is important to note that, these security updates will not include updates for embedded Java/JDK functionality. Cryptography-related updates or patches are not included either. Standard SPUs are not included. So this means, limited security patches and updates..

• One Database Upgrade Planning Workshop to assist customer to develop their upgrade plans. Oracle ACS will provide this workshop.. This workshop is aimed to help customer upgrade their databases to a fully supported release.

• A Technical Account Manager (TAM) as single point of contact.

So far so good.. 

Well, there is an important issue I would like to underline.. That is, Market Driven Support is not an extension to Extended Support! It is not a sustaining support either.. It is somewhere in between :)

Check this website for the Extended Support offering -> 

https://www.oracle.com/support/lifetime-support/

There you will see a long line of benefits and you will also see Security alerts and Updates without any exceptions... In addition to that, you can create SR wity any level of severity.

Market-Driven Support is a completely seperate support type. So, the customers purchasing this support, should still plan their upgrades or cloud transitioning projects asap to avoid future risks.

I 'am not a Oracle Salesman, but the pricing seems fixed.

It just depends on how many production databases you have.

The prices vary according to the number of databases you have . (1) Up to 50 database (2) 51-500 databases, (3) 501+ databases.

Well, that's it :)

I wish you a healthy and beautiful week as I finish my writing...

RDBMS -- SQL Performance Tuning - Correcting SQL Plans by setting hidden parameters at session level & Fixing Plans via Sql Profiles

We recently dealt with a SQL performance problem. Actually, the problem itself wasn't so interesting. The solution, however; was stylish :)

In one of our customer sites,  we encountered a performance degradation in an important SQL.
The issue arised after we upgraded the database to 18C. (unfortunaetly they did not notice this problem in performance-test phase).

The query was trying to fetch rows from all_objects and all_synonyms-type views and the execution plan was not quite good.

We had already seen these kinds of performance problems and remember the workaround..
That is, the workaround "ALTER SYSTEM SET "_fix_control" = '8560951:on" saved our day previously.

Reference: Accessing ALL_OBJECTS View Performs Slowly, Relative to Response Time on Another Database (Doc ID 2061736.1)

However; this time we couldn't directly set that fix_control parameter. Because, we were already  on production, so setting an underscore parameter without testing it, was so risky..

Interesting part start here : )

Look what we have done; 

• We have set the parameter at session level ->  ALTER SESSION SET "_fix_control" = '8560951:on'
• We then run the exact same query again (in order to make Oracle optimizer to build the desired/correct plan) -- Attention -> We executed the exact same query, as we didn't want the sqlid to be changed/or a new sqlid to be generated during our execution.. 
• Then we checked the Sqlids and the associated sql plans for the problematic sql text (Checked the average estimated seconds and plan hash values.. The new execution plan was the quickest)
• Well, we fixed the plan for that sqlid :) After fixing the plan, our sql started run faster!
• Lastly, we got the sql optimizer trace and saw that the hidden parameter was really active for the sql.. 

SQL DIAG TRACE:

begin

dbms_sqldiag.dump_trace(p_sql_id=>'43xdnx0r5dhmx',

p_child_number=> 0,p_component=> 'Compiler',

p_file_id=>'Compiler_Trace_43xdnx0r5dhmx');

end;

A PIECE OF CONTENT OF THE TRACE FILE:

Content of other_xml column

===========================

  db_version     : 18.0.0.0

  parse_schema   : ERMANRPT

  plan_hash_full : 1560297160

  plan_hash      : 533470668

  plan_hash_2    : 1023844655

  sql_profile    : coe_43xdnx0r5dhmx_2690840819

  Outline Data:

  /*+

    BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('18.1.0')

      DB_VERSION('18.1.0')

      OPT_PARAM('query_rewrite_enabled' 'false')

      OPT_PARAM('_optim_peek_user_binds' 'false')

      OPT_PARAM('_fix_control' '8560951:1') --> YES IT IS HERE! :)

      ALL_ROWS