Oracle DBA – A lifelong learning experience

Identifying applied PSU patches

Posted by John Hallas on January 27, 2010

Metalink/My Oracle support note 988624.1 states that PSU 2 for 11GR1 was released on January 12th 2010. When applied, this takes the database version to 11.1.0.7.2. I say this but it is not apparent what version is installed.

The same note states that one of the advantages of the PSU releases is that “the version number is incremented so it is easier to tell what is installed”. Well I beg to differ and I will give you some examples to support my argument.

Firstly there is now obvious way to tell from the database what (database) version is applied. That includes looking at sqlplus, dba_registry, v$version, or the output from traces files. There might be options with taking traces or block dumps but that is hardly “easier to tell what is installed” is it.

In the example below PSU2 – Jan 2010 has been applied and the obvious way of telling is that the opatch lsinventory has a distinctive list of patches in a list with eight columns of patches and we can also see the date the patch was created (7 Jan 2010). I still recommend keeping a site record of PSU patches and patch numbers for a quick mapping.
Installed Top-level Products (2):

Oracle Database 11g 11.1.0.6.0
Oracle Database 11g Patch Set 1 11.1.0.7.0
There are 2 products installed in this Oracle Home.

Interim patches (1) :
Patch 9209238 : applied on Fri Jan 15 14:49:19 GMT 2010
Unique Patch ID: 12089910
Created on 7 Jan 2010, 04:12:05 hrs PST8PDT
Bugs fixed:
7627743, 7652888, 9135679, 7299153, 8242410, 6059178, 6955744, 7497788
7447559, 8702276, 7690421, 7719143, 8251486, 8367827, 8328853, 7630874
7515145, 8416414, 8250643, 8284633, 8348481, 8230457, 8563948, 7044551
7318049, 8940197, 7013124, 7432514, 7393258, 7553884, 8563944, 8860821
7606362, 8898852, 8483871, 7307972, 7462589, 7426959, 7330434, 7708340
7257038, 7352414, 6452375, 7341598, 8213302, 7516867, 7296258, 7340448
8290478, 8391256, 7462112, 7013817, 7331867, 7527650, 6977167, 8855565
7524944, 7494333, 8402551, 8914979, 7496908, 7719148, 7424804, 7452373
7597354, 6851669, 8543737, 7318276, 8284438, 7420394, 7350127, 7122161
9118622, 8361398, 8348464, 8224083, 8658581, 7206858, 8563945, 8402637
8257122, 8199266, 7345904, 6407486, 8352309, 9114072, 6812439, 7219752
7432601, 8542307, 8413059, 8281906, 7460818, 6843972, 7378322, 7189645
8563947, 7653579, 8876094, 8702535, 7253531, 8339352, 7225720, 7203349
8974548, 8402562, 7438445, 8855577, 6980597, 6618461, 8855570, 8565708
8369094, 8306934, 7434194, 8217795, 8833297, 7486595, 6599920, 7628866
7183523, 7412296, 7135702, 6679303, 7650993, 7830065, 7462709, 6768362
8563941, 7515779, 6870937, 8563946, 8717461, 8244217, 6840740, 8450529
6981690, 7432556, 8650661, 7523787, 9066130, 7613481, 8341623, 7643188
7348847, 8834636, 7113299, 6903819, 6900214, 7639121, 8287680, 8499043
7356443, 7334226, 7446163, 7196532, 8409848, 8342506, 8236851, 7593835
7309458, 8674263, 7694979, 8499600, 7119382, 7411865, 7409110, 6501490
6598432, 7706138, 8402555, 6941717, 7586451, 7715244, 8487273, 7499353
8408887, 7511040, 7311601, 7497640, 7373196, 7500792, 6882739, 7366290
8324760, 9209238, 8199107, 7834195, 7475055, 8563942, 7451927, 8531282
7705669, 7676737, 8301559, 8211920, 8462173, 8855553, 7454752, 7516536
8352304, 7426336, 7416901, 8534338, 7572069, 7436280, 8539923, 7311909
8790767, 7506785, 7276960, 8855559, 7013835, 6972189, 8402548, 8277580
8306933, 7477246, 7263842, 7480809, 8855575, 8433270, 7556778, 8836375
8364676, 7330611, 8549480, 8563943, 8214576, 7036453, 7628387, 8419383
7719668, 6970731, 9118620, 7377810, 8221425, 7680907, 8243648, 7630416
6851110, 7639602, 7441663, 9188010, 7357609, 8318050, 8539335, 7716219
8362693, 8588540, 8613137, 7720494, 6991626, 6784747, 8851675, 7436152
7602341, 7175513, 8339404, 7829321, 7258928, 7393804, 8856696, 6980601, 8226397

Let me compare this with another example

Installed Top-level Products (2):

Oracle Database 11g 11.1.0.6.0
Oracle Database 11g Patch Set 1 11.1.0.7.0
There are 2 products installed in this Oracle Home.

Interim patches (1) :

Patch 9156613 : applied on Wed Jan 27 10:26:44 GMT 2010
Unique Patch ID: 12072859
Created on 18 Jan 2010, 23:43:20 hrs PST8PDT
Bugs fixed:
8563943, 7036284

That tells us that patch 9156613 was created by Oracle on 18 Jan 2010 and applied on 27 Jan 2010. I would assume that no PSU has been applied because there is no long list of patches in that eight column format.

However I would be wrong. This patch is a PSU that is merged with 3 patches that were conflicting with the PSU and we asked Oracle to create a merged patchset. That patchset will be specific and probably not downloadable by the normal methods or generally available.

The problem is that there is no easy way to tell if it includes the PSU at all and if it does which PSU it includes. We will have to maintain that documentation ourselves and I am guessing there is a good chance it will confuse Oracle at some point when we raise a support call in the future.

So this is a heads up on keeping good records of which PSU releases have been applied, where and which merged patchsets have been provided by Oracle for you.

I am also creating a second blog entry on a PSU 2 – 11.1.07.2 difference. That will tell you how I knew that patch 9156613 includes the Oct 09 PSU and not the Jan 10 PSU despite it being created on 18 Jan 2010

7 Responses to “Identifying applied PSU patches”

  1. […] DBA – A lifelong learning experience Just another WordPress.com weblog « Identifying applied PSU patches PSU dependancy checking with ASM now enforced in 11G January 28, 2010 ASM has to be equal […]

  2. odenysenko said

    Hi.

    To identify CPU/PSU/PS installation just quertin
    registry$history

    Oleksandr

    • John Hallas said

      11G does not show PSU patches in registry$history. A MoS note suggests that functionality will not be available until 11.2.0.1 PSU 1.

      It was a good comment though as I had never used that view before

      John

      • Jan Polnicky said

        RE: ’11G does not show PSU patches in registry$history’
        It does (at least from 11.1.0.7.2), only if you don’t forget running the post-steps like new catbundle.sql:
        @catbundle.sql psu apply

        SQL> INSERT INTO registry$history
        2 (action_time, action,
        3 namespace, version, id,
        4 bundle_series, comments)
        5 VALUES
        6 (SYSTIMESTAMP, ‘APPLY’,
        7 SYS_CONTEXT(‘REGISTRY$CTX’,’NAMESPACE’),
        8 ‘11.1.0.7’,
        9 2,
        10 ‘PSU’,
        11 ‘PSU 11.1.0.7.2’);

  3. John Jeffries said

    PSUs also contain CPUs (Critical Patch Updates). Running $ORACLE_HOME/cpu/view_recompile/view_recompile_jan2008cpu.sql as a post installation step also inserts a row in registry$history

    I.e.

    SELECT * FROM registry$history;
    ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS
    ————— ——- ———- ——— ——- ——————
    26-JAN-10 12.33 APPLY SERVER 11.1.0.7 1 PSU 11.1.0.7.1
    26-JAN-10 12.43 CPU 6452863 view recompilation

  4. Ali Damghani said

    Jan,

    If you run catbundle against existing databases, it will add a row in registry$history. However, it is not true for databases you create later in the patched home.

    I really look forward to Oracle fixing this issue.

  5. Dimitrios Spanos said

    Just for completeness , Note 861152.1 at Oracle support says :

    1. Make sure your ‘opatch’ version is at or above

    * 10.2.0.4.5 for version 10.2 ORACLE_HOMEs
    * 11.1.0.6.5 for version 11.1 ORACLE_HOMEs
    * 11.2.0.1.0 for version 11.1 ORACLE_HOMEs

    2. Verify if PSU is installed in the ORACLE_HOME

    $ opatch lsinventory -bugs_fixed | egrep ‘PSU|PATCH SET UPDATE’

    3. Verify the PSU Post Install steps were run in the DB

    select substr(action_time,1,30) action_time,
    substr(id,1,10) id, substr(action,1,10) action,
    substr(version,1,8) version,
    substr(BUNDLE_SERIES,1,6) bundle,
    substr(comments,1,20) comments
    from registry$history;

    Note: The registry$history will not contain the row showing the PSU Post Install step was executed unless catbundle.sql is executed.

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

 
%d bloggers like this: