Is the January Oracle CPU rolling upgradeable?

I’ve been running RAC for many years, but I have been consistently frustrated at having to lose availability to perform patches/upgrades. Obviously, I have heard a lot of buzz about rolling upgrades but so far, in the versions I have worked on up to and including 10.2.0.3 I have never seen an upgrade that I was going to apply whereby it was possible to perform an upgrade on my RAC cluster without incurring downtime. Perhaps, “rolling upgradeable” is more marketing smoke and mirrors than something actually based on reality?The January Quarterly critical patch update has recently come out, and at first I thought it was going to be possible to apply this doing a rolling upgrade and not incur any downtime, you will probably be surprised finding a dba owning up to applying a CPU, but if it was possible to apply this CPU without downtime then I thought I should give it a shot. So reading the README for the CPUJAN2008 patch, I page down the section regarding patch installation instructions for a RAC environment, and it clearly states that you can patch 1 node at a time. Great, is this a rolling upgradeable CPU?I then look at the post-installation instructions which state the following:

Select one node to execute the post installation steps. Follow the same set of instructions as mentioned in the Section 3.3.3, “Post Installation Instructions for a Non-RAC Environment”.

Users can continue to access the database during the post-installation steps.

So I go to the post-install instructions for the Non-Rac Environment, and sure I can run the catcpu.sql online, and looking at this script, Oracle have obviously thought about availability in a RAC environment:


.
Rem Check open UPGRADE status; set session attributes
Rem Following call to check_server_instance is commented as it checks if
Rem database
Rem is started up in UPGRADE mode else quits.
Rem CPU patches needs to be RAC compliant means database should not be started
Rem in UPGRADE mode.
-- EXECUTE dbms_registry.check_server_instance;
.

But there is something new with this CPU, you must run a sql script called view_recompile_jan2008cpu.sql and here is a problem. You have to run this sql script after the database has been started using startup upgrade. This is bad news for system availability as only sysdba privilege users can access the system. Oracle are aware of this as a problem as they have the following note:

Depending on these considerations and your downtime schedule, you can choose to schedule the recompilation of views independent of the rest of the CPU installation. If you do this, your system will continue to work; however, the CPU installation will not be complete until the view recompilation is completed.

There are two thoughts for me here, why does the documentation state users can access the system during the post-installation phase, when to fully patch the security holes you must have downtime on your RAC cluster. The second point is, that it is no wonder that hardly anyone is applying these CPU’s if they are requiring system unavailability. If you have made a large investment in RAC to improve your availability then to have this compromised by a security patch is not a nice choice.

You should not have to make the choice between availability and security but it seems like for now, you cannot have both.

Advertisements

8 thoughts on “Is the January Oracle CPU rolling upgradeable?

  1. Hi Jason,

    I agree with you that this patch and the way its documentation is written are ‘a bit annoying’ for a production DBA. Anyway there is also the possibility of applying only the ‘rolling part’ of the patch and leaving the views recompilation for a later time (say when upgrading to 10.2.0.4)
    My understanding (I may be wrong here) is the the views recompilation is actually related to the ‘bug on views’ that was supposed to be fixed in Jul07 and then in Oct07..will this be the final fix for it? 🙂

    Cheers,
    Luca

  2. Hi Luca!

    I now have a bug out regarding the documentation: 6764071, I think Oracle realise that the docs for this are completely misleading.

    Very interesting what you say on the views recompilation, the script has the following comment:

    — explicitly recompile all views that potentially have the vulnerability

    You are probably correct, we would probably get most of the benefits of the patch without the recompile (and downtime)

    question to you, do CERN apply CPU’s?

    cheers!

    jason.

  3. Yes we do apply CPUs. From Pete Finningan’s blog I understand that “.. only a very small percentage of people install a CPU within the quarter (perhaps 1 – 5%)”, that would be us (and a few more people I hope!)

    L.

  4. I think this is a doc bug. View recompilations can be done online.

    We use EM Provisioning Pack to automate CPU application. I simply added a SQL directive to the Deployment Procedure and it ran fine in rolling mode.

  5. Hi Patrick,

    Thanks for reading. I do have a documentation bug out on this: 6764071, but my analyst for the SR seems to be stating that you cannot run the recompile with the database open.

    Actually, if you are talking about running view_recompile_jan2008cpu.sql with the database open then I am really, really confused as this is in the script:

    Rem =======================================================================
    Rem To check if DB is started in upgrade mode
    Rem =======================================================================

    — Start the database in upgrade mode. This is preferred over restricted mode
    — because any user with “restrict” privilege can still connect when the database
    — is in restricted mode.

    DECLARE
    openupgrade VARCHAR2(30);
    BEGIN
    select status into openupgrade from v$instance;
    if openupgrade != ‘OPEN MIGRATE’
    then RAISE_APPLICATION_ERROR(-20000,
    ‘Database is not open for upgrade. ‘ ||
    ‘Shutdown and restart using UPGRADE.’);
    end if ;
    END;
    /

    It actually checks you have the database started with upgrade.

    jason.

  6. NO you can’t run the view_recompile_jan2008cpu.sql without suting down the database and opening it in upgrade mode.

    timst2>sqlplus ‘/as sysdba’;

    SQL*Plus: Release 10.2.0.4.0 – Production on Tue Apr 13 16:09:14 2010

    Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
    With the Partitioning, Real Application Clusters, OLAP, Data Mining
    and Real Application Testing options

    SQL> @recompile_precheck_jan2008cpu.sql

    Running precheck.sql…

    Number of views to be recompiled :2281
    ———————————————————————–

    Number of objects to be recompiled :4858
    Please follow the README.txt instructions for running viewrecomp.sql

    PL/SQL procedure successfully completed.

    SQL> !ls -ltr
    total 22
    -rw-r–r– 1 oracle oinstall 5143 Dec 24 06:33 view_recompile_jan2008cpu.sql
    -rw-r–r– 1 oracle oinstall 2095 Dec 24 06:33 recompile_precheck_jan2008cpu.sql
    -rw-r–r– 1 oracle oinstall 60 Apr 13 13:31 vcomplogfile.log
    -rw-r–r– 1 oracle oinstall 177 Apr 13 16:09 vcomp_TIMST_13Apr2010_16_09_01.log

    SQL> @view_recompile_jan2008cpu.sql

    PL/SQL procedure successfully completed.

    DECLARE
    *
    ERROR at line 1:
    ORA-20000: Database is not open for upgrade. Shutdown and restart using
    UPGRADE.
    ORA-06512: at line 6

    Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
    With the Partitioning, Real Application Clusters, OLAP, Data Mining
    and Real Application Testing options
    timst2>

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s