Twitter

lspatches.sh : an Oracle patch reporting tool

Edit May 24th 2021: this script is now deprecated, you may want to use the more robust list-ohpatches.sh.

Like any other product, Oracle database, ClusterWare (RAC, GI, CRS whatever you name it), ASM, . . . require patches for updates, bug fixing or security purpose and no one would argue that it is not fairly easy to use opatch to apply them.

But opatch's reporting option has room for improvement.

Oh yes for sure there is an opatch lspatches option
[oracle@anexadata_db01 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
27475603;Database PSU 12.1.0.2.180417, Oracle JavaVM Component (APR2018)
21923026;
27338020;OCW PATCH SET UPDATE 12.1.0.2.180417 (27338020)
27338029;Database Bundle Patch : 12.1.0.2.180417 (27338029)
OPatch succeeded.
[oracle@anexadata_db01 ~]$

but this option is not able to provide the patches installed on the remote nodes in case of RAC environment.
Then yes there is the lsinventory -all_nodes option to achieve this (at least for now) :
[oracle@anexadata_db01 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory -all_nodes | wc -l
472
[oracle@anexadata_db01 ~]$

In this example, opatch lsinventory -all_nodes provides with 472 lines for 1 Home over 2 nodes (this has been taken on an Exadata 1/4 RAC) so we could expect getting around 1000 lines with a 4 nodes RAC and then around 2000 lines for a 8 nodes RAC for a single Home (and may be more depending on the number of patches installed). And you may have a dozen of Homes installed so a dozen times more lines to find in the answer of the really important questions any DBA has to answer :
  • Is the patch 123456 well installed on all the nodes ?
  • Is there a missing patch on a Home ?


As a DBA, I already spent hours in these opatch outputs, grepping, copying and pasting information to double check that a patch was well installed on all the homes of a system , etc . . . I spent so much time in these non-added value tasks that something had to changed and I then wrote lspatches.sh ! And my first screenshot will show a missing patch on a Home I found when testing this script ! (despite all the efforts we made to check, this one was missed):


Then here you can easily see that patch 17974457 is missing on Node 2, 3 and 4 !


How that works ?

Basically lspatches.sh will do a lsinvenrory (-all_nodes if RAC), anaylze the output and show if a patch is Missing somewhere. Please have a look at the below example taken on a full Exadata rack.

Note : this output has been truncated to the 2 first Homes for visibility.


Grep only the Homes you want

As you may not want to check the patches on every Home every time, you can choose one or few Homes using the -g and/or the -v option which act as a grep and as a grep -v. Let me show some examples :

# To analyze only the /u01/app/oracle/product/12.1.0.2/dbhome_1 Home :
[oracle@anexadata_db01 ~]$ ./lspatches.sh -g /u01/app/oracle/product/12.1.0.2/dbhome_1

# To analyze the Homes that contain "12" (ie. the 12c Homes) :
[oracle@anexadata_db01 ~]$ ./lspatches.sh -g 12

# To analyze the Homes that contain "12" but that do not contain "oap" :
[oracle@anexadata_db01 ~]$ ./lspatches.sh -g 12 -v oap

# To analyze the "dbhome_2 "Homes but not the 12c ones :
[oracle@anexadata_db01 ~]$ ./lspatches.sh -g dbhome_2 -v 12


Show before proceeding

Unsure about the result of your -g and -v combination ? use the -s option to just show what Homes would be considered before proceeding :
[oracle@anexadata_db01 ~]$ ./lspatches.sh -g 12 -s
ORACLE_HOMEs that would be considered (/etc/oratab) :
        /u01/app/12.1.0.2/grid
        /u01/app/oracle/product/12.1.0.2/dbhome_1
        /u01/app/oracle/product/12.1.0.2/dbhome_2
        /u01/oapdb/12.1.0.2
[oracle@anexadata_db01 ~]$ ./lspatches.sh -g 12 -v oap -s
ORACLE_HOMEs that would be considered (/etc/oratab) :
        /u01/app/12.1.0.2/grid
        /u01/app/oracle/product/12.1.0.2/dbhome_1
        /u01/app/oracle/product/12.1.0.2/dbhome_2
[oracle@anexadata_db01 ~]$ 


And ultimately you can also use the -s option with no other argument just to show the Homes that are installed on you system :
[oracle@anexadata_db01 ~]$ ./lspatches.sh -s
ORACLE_HOMEs that would be considered (/etc/oratab) :
        /u01/app/12.1.0.2/grid
        /u01/app/oracle/product/11.2.0.4/dbhome_1
        /u01/app/oracle/product/11.2.0.4/dbhome_2
        /u01/app/oracle/product/12.1.0.2/dbhome_1
        /u01/app/oracle/product/12.1.0.2/dbhome_2
        /u01/oapdb/12.1.0.2
[oracle@anexadata_db01 ~]$ 


Input and output files are supported

Have you noticed the (/etc/oratab) in the above outputs ? This is because we can also use pre generated opatch outputs files with the -f option as well as just generating an opatch output with -o ! (and also for the Solaris users where oratab is not located in /etc -- more on that later). Please have a look at the below example :
# Generate an opatch output for the 12c Homes
# but not the "oap" ones as I do not want to deal with the EBS DBAs :)

[oracle@anexadata_db01 ~]$  ./lspatches.sh -g 12 -v oap -o opatch_output
Proceeding with /u01/app/12.1.0.2/grid . . .                                            OK
Proceeding with /u01/app/oracle/product/12.1.0.2/dbhome_1 . . .                         OK
Proceeding with /u01/app/oracle/product/12.1.0.2/dbhome_dr2 . . .                       OK

[oracle@anexadata_db01 ~]$ ls -ltr opatch_output
-rw-r--r-- 1 oracle oinstall 578261 Jul  6 00:06 opatch_output

[oracle@anexadata_db01 ~]$ wc -l opatch_output
8139 opatch_output           # 3 Homes across 8 nodes is indeed a lot of lines

[oracle@anexadata_db01 ~]$ 


And now let's use this file as an input for the lspatches.sh script to see for which Homes we have some patch information in :
[oracle@anexadata_db01 ~]$ ./lspatches.sh -f opatch_output -s
ORACLE_HOMEs that would be considered (opatch_output) :
         /u01/app/12.1.0.2/grid
         /u01/app/oracle/product/12.1.0.2/dbhome_1
         /u01/app/oracle/product/12.1.0.2/dbhome_dr2
[oracle@anexadata_db01 ~]$ 


And then analyze this file (without the grid Home) :


 
Few things are interesting in the above screenshot:
  • The /u01/app/oracle/product/12.1.0.2/dbhome_2 Home is only installed on 4 nodes (when this happens the script only shows the nodes where the Home is installed)
  • You can see that there're some Missing patches (I manually modified the input file before taking the screenshot)


Local and remote

As one may not have a RAC installation, the script with start a local opatch session (with no -all_nodes) if the olsnodes command is not here or returns 0 row then you do not have to worry about, lspatches.sh will take care of this for you.

In the case of a RAC configuration, you may not want the remote patches information so you may want to use the -l option to just start a local patch reporting (you can also use the -g and the -v options to grep or ungrep the Homes you want or doesn't want to analyze)
[oracle@anexadata_db01 ~]$ ./lspatches.sh -l

[oracle@anexadata_db01 ~]$ ./lspatches.sh -l -g 12 -v grid


opatchauto report from opatch 12.2.0.1.13 / 11.2.0.3.18

As I wrote about in the past, starting from versions 12.2.0.1.13 and 11.2.0.3.18, opatch does not provide any remote patch information any more meaning the -all_nodes option is discontinued and we now have to use opatchauto report to have such information.
lspatches.sh manages this by itself, using opatchauto report instead of opatch lsinventory -all_nodes if the opatch versions are > 12.2.0.1.13 or > 11.2.0.3.18 for opatch 11g. It is almost transparent for you.
"Almost" transparent ? yes, almost. Indeed, opatch lsinventory only reports the latest installed base patches whereas opatchauto report shows all the installed patches and subpatches. In the below example, opatch lsinventory would only show the most current Bundle patch whereas opatchauto report will also show the previous database Bundle patches.
patch id="27338029" isSqlpatch="true" unique_patch_id="22055339"    --- this is the most current Database Bundle Patch that is installed 

the following are the "sub-patches, i.e. the previous bundle patch versions": 
patch id="26925263" isSqlpatch="true" unique_patch_id="21857460"    --- DATABASE BUNDLE PATCH 12.1.0.2.180116 
patch id="26717470" isSqlpatch="true" unique_patch_id="21611145"    --- DATABASE BUNDLE PATCH 12.1.0.2.171017 
patch id="26609798" isSqlpatch="true" unique_patch_id="21481992"    --- DATABASE BUNDLE PATCH 12.1.0.2.180116 


Non Linux support

I develop and test my scripts on Linux because 99% of my clients are running Linux and sometimes they may not work under other OS specially Solaris. For this one, I really tried hard to accommodate Solaris with /etc/oratab, awk, etc . . . it may not be perfect as I just found an old Solaris 10 to test but it should not be bad. Let me know Solaris users if I missed something here !

-h for help

As lspatches.sh has quite a few options, you may want to use the -h one to show a summary of all the options with some examples.
[oracle@anexadata_db01 ~]$ ./lspatches.sh -h

opatchauto report limitations

As of now, it seems that the below opatchauto report limitations are:
  • It does not provide a description of a base patch like DATABASE BUNDLE PATCH 12.1.0.2.180116
  • There is no option to behave like opatch lsinventory meaning to only show the latest base patch installed and not show all the subpatches
I'll keep an eye on it and update lspatches.sh if Oracle releases such option in a near future.


Thanks for reading, you can download lspatches.sh here, I hope you'll be enjoying this script as much as I enjoy using it on a daily basis !

24 comments:

  1. Hello, thanks for sharing these nice scripts. But, did you worked out how to list patches applied across all the servers (Oracle homes) from an Enterprise Manager repository?

    ReplyDelete
    Replies
    1. Hi Fayyaz,

      I never thought about that but it is indeed an excellent idea ! I'll have a look and let you know.

      Thanks,

      Delete
  2. Hi,

    Could you please share rac-status script for NON exdata environments .

    Also let me know from which user we need to tun ls-patches.sh for RAC env.

    ReplyDelete
  3. Getting error while running the script

    ReplyDelete
  4. i am getting following error :

    [root@myrac1 u01]# sh lspatches.sh
    Proceeding with /u01/app/oracle/product/19.3.0/db_home2 . . . Error 2
    Proceeding with /u02/app/19.3.0/grid . . . Error 2

    ReplyDelete
    Replies
    1. Could you please do patch lsinventory manually on this home ?

      Delete
  5. Hello!

    I'am getting this errors in GI and Oracle Home:

    [oracle@msk-dm-dbadm01 EXADATA_SCRIPTS %+ASM1]$ ./lspatches.sh -g /u01/app/12.2.0.1/grid
    Proceeding with /u01/app/12.2.0.1/grid . . . Error 245

    [oracle@msk-dm-dbadm01 EXADATA_SCRIPTS %+ASM1]$


    [oracle@msk-dm-dbadm01 EXADATA_SCRIPTS %dwxcdb1]$ ./lspatches.sh -g /u01/app/oracle/product/12.2.0.1/dbhome_1
    Proceeding with /u01/app/oracle/product/12.2.0.1/dbhome_1 . . . Error 41

    [oracle@msk-dm-dbadm01 EXADATA_SCRIPTS %dwxcdb1]$

    In both homes opatch lsinventory works fine.

    ReplyDelete
    Replies
    1. Hello,

      Could you please paste the manual opatch lsinventory ? How do you day you environment ? Is it RAC ? What is your opatch version ?

      Thanks,

      Delete
    2. Hi, same error here,
      (hope is ok to hijack this thread)

      2 nodes RAC system (no Exadata)
      Opatch is 12.2.0.1.21

      -- Full error output:

      [oracle@rac1-node1 scripts]$ ./lspatches.sh
      Proceeding with /u01/app/19.3.0/grid . . . Error 41
      Proceeding with /u01/app/oracle/product/11.2.0/dbhome_1 . . . Error 41print() on closed filehandle $FileHandler at /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/auto/database/bin/module/DBUtilServices.pm line 508.
      [.....]
      [oracle@rac1-node1 scripts]$

      -- If I force local, works correctly

      -- ASM inventory example

      [oracle@rac1-node1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory
      Oracle Interim Patch Installer version 12.2.0.1.21
      Copyright (c) 2020, Oracle Corporation. All rights reserved.


      Oracle Home : /u01/app/19.3.0/grid
      Central Inventory : /u01/app/oraInventory
      from : /u01/app/19.3.0/grid/oraInst.loc
      OPatch version : 12.2.0.1.21
      OUI version : 12.2.0.7.0
      Log file location : /u01/app/19.3.0/grid/cfgtoollogs/opatch/opatch2020-08-12_18-25-47PM_1.log

      Lsinventory Output file location : /u01/app/19.3.0/grid/cfgtoollogs/opatch/lsinv/lsinventory2020-08-12_18-25-47PM.txt
      --------------------------------------------------------------------------------
      Local Machine Information::
      Hostname: rac1-node1.raclab.local
      ARU platform id: 226
      ARU platform description:: Linux x86-64

      Installed Top-level Products (1):

      Oracle Grid Infrastructure 19c 19.0.0.0.0
      There are 1 products installed in this Oracle Home.


      Interim patches (4) :

      Patch 29585399 : applied on Thu Apr 18 08:36:24 BST 2019
      Unique Patch ID: 22840393
      Patch description: "OCW RELEASE UPDATE 19.3.0.0.0 (29585399)"
      Created on 9 Apr 2019, 19:12:47 hrs PST8PDT
      Bugs fixed:
      27222128, 27572040, 27604329, 27760043, 27877830, 28302580, 28470673
      28621543, 28642469, 28699321, 28710663, 28755846, 28772816, 28785321
      28800508, 28808652, 28815557, 28847541, 28847572, 28870496, 28871040
      28874416, 28877252, 28881191, 28881848, 28888083, 28911140, 28925250

      29242906, 29243749, 29244495, 29244766, 29244968, 29248723, 29249583
      29251564, 29255616, 29260224, 29261695, 29271019, 29273360, 29282090
      29282666, 29285453, 29285621, 29290235, 29292232, 29293806, 29294753
      29299830, 29307090, 29307109, 29311336, 29329675, 29330791, 29339299
      29357821, 29360467, 29360775, 29367971, 29368725, 29379299, 29379381
      29380527, 29381000, 29382296, 29391301, 29393649, 29402110, 29411931
      29413360, 29457319, 29465047

      Patch 29517247 : applied on Thu Apr 18 08:36:02 BST 2019
      Unique Patch ID: 22840392
      Patch description: "ACFS RELEASE UPDATE 19.3.0.0.0 (29517247)"
      Created on 1 Apr 2019, 15:08:20 hrs PST8PDT
      Bugs fixed:
      28611527, 28687713, 28701011, 28740425, 28844788, 28860451, 28960047
      29031452, 29039918, 29115917, 29198743, 29264772

      Patch 29517242 : applied on Thu Apr 18 08:35:39 BST 2019
      Unique Patch ID: 22862832
      Patch description: "Database Release Update : 19.3.0.0.190416 (29517242)"
      Created on 17 Apr 2019, 23:27:10 hrs PST8PDT
      Bugs fixed:
      29515240, 14735102, 19697993, 20313356, 21965541, 25806201, 25883179
      25986062, 26476244, 26611353, 26872233, 27369515, 27423500, 27666312
      27710072, 27846298, 27957203, 28064977, 28072567, 28129791, 28181021
      [.....]
      29452576, 29452953, 29457807, 29460252, 29462957, 29486181, 29507616
      29521748, 29530812, 29531654, 29557336, 29558975, 29601461

      Patch 29401763 : applied on Thu Apr 18 08:35:16 BST 2019
      Unique Patch ID: 22759421
      Patch description: "TOMCAT RELEASE UPDATE 19.0.0.0.0 (29401763)"
      Created on 11 Apr 2019, 22:26:25 hrs PST8PDT
      Bugs fixed:
      29286300



      --------------------------------------------------------------------------------

      OPatch succeeded.
      [oracle@rac1-node1 ~]$

      Delete
    3. Hi,

      Could you please do:

      lspatches.sh -o forfred.txt

      and send me this forfred.txt file by email ? and I'll have a look. My email address is on top of the script.

      Thanks,

      Delete
  6. Hello,

    lsinventory sent to the email.
    Our environment its Exadata X5 FullRack.
    OPatch version : 12.2.0.1.19

    Now the script output has changed - I do not get an error, but I see information on only one cluster node (I cut the list of patches a bit):

    [oracle@msk-dm-dbadm01 EXADATA_SCRIPTS %dwxcdb1]$ ./lspatches.sh -g /u01/app/oracle/product/12.2.0.1/dbhome_1

    Proceeding with /u01/app/oracle/product/12.2.0.1/dbhome_1 . . . OK

    /u01/app/oracle/product/12.2.0.1/dbhome_1 (opatch version 12.2.0.1.19)
    ------------------------------
    Patch ID | node_name |
    ------------------------------
    14840737 | - |
    .
    .
    .
    ------------------------------


    Regards, Vyacheslav

    ReplyDelete
    Replies
    1. It is most likely because your inventory is wrong and does not set the home as a RAC home with the list of nodes.

      Delete
  7. I receive this error.

    [oracle@hostname ~]$ ./lspatches.sh
    Proceeding with /u01/app/18.1.0.0/grid . . . Error 2
    Proceeding with /u02/app/oracle/product/11.2.0/dbhome_2 . . . Error 127./lspatches.sh: line 272: /u02/app/oracle/product/11.2.0/dbhome_2/OPatch/opatchauto: No such file or directory

    Proceeding with /u02/app/oracle/product/12.1.0/dbhome_2 . . . Error 41
    Proceeding with /u02/app/oracle/product/12.2.0/dbhome_2 . . . Cannot find /u02/app/oracle/product/12.2.0/dbhome_2/OPatch/opatch
    Proceeding with /u02/app/oracle/product/12.2.0/dbhome_3 . . . Error 41
    Proceeding with /u02/app/oracle/product/18.0.0.0/dbhome_2 . . . Cannot find /u02/app/oracle/product/18.0.0.0/dbhome_2/OPatch/opatch
    Proceeding with /u02/app/oracle/product/18.0.0.0/dbhome_3 . . . Error 41

    [oracle@hostname ~]$

    ReplyDelete
    Replies
    1. Pleas try opatch lsinventory manually -- this is most likely an opatch error.

      Delete
    2. Yes, Fred! Thank you for replying!
      Apparently in this Oracle Cloud@Customer setup, each DB has separate env file in it.
      So I tried provoking each env file for each DB versio, but still erroring out. :(

      Delete
    3. What do you mean by "each DB has different env file ?", Do you mean there is no /etc/oratab nor oraenv to set up the environment ?

      Delete
    4. In this current setup, this Customer has deployed multiple DBs with multiple versions, like 11g, 12c (12.1, 12.2), 18c, 19c etc.
      Each version DB, would've its own environment variable file: "DB.env", which when DBAs access any particular DB, source it, and then access.
      Yes! There exists /etc/oratab file, and yes, the oraenv option works only on crs/grid user: "grid".
      Maybe, that's why, I'm unable to make this script: "lspatches.sh", get those parms.

      Delete
    5. Could you please do:
      bash -xv lspatches.sh

      And send me the output by email ? (my email is on top of the script)

      Delete
  8. Hi oh my master!!

    I run the shell but I generate the same errors, with your option (to debug) i can see:


    Oracle Interim Patch Installer version 12.2.0.1.13

    OPatchauto session is initiated at Sun Mar 14 02:28:58 2021

    System initialization log file is /u01/app/12.2.0.1/grid/cfgtoollogs/opatchautodb/systemconfig2021-03-14_02-28-58AM.log.

    Session log file is /u01/app/12.2.0.1/grid/cfgtoollogs/opatchauto/opatchauto2021-03-14_02-29-07AM.log
    The id for this session is 8GW9
    OPatchAuto failed.

    OPatchauto session completed at Sun Mar 14 02:29:09 2021
    Time taken to complete the session 0 minute, 12 seconds

    opatchauto failed with error code 42
    Oracle Interim Patch Installer version 11.2.0.3.19

    Unsupported Java version 1.6.
    Java version should be 1.7 or higher.

    No valid java found for patching.opatchauto cannot proceed!
    opatchauto returns with error code = 2
    Oracle Interim Patch Installer version 11.2.0.3.19

    Unsupported Java version 1.6.
    Java version should be 1.7 or higher.

    No valid java found for patching.opatchauto cannot proceed!
    opatchauto returns with error code = 2
    ~

    Thanks a lot!

    ReplyDelete
  9. Hi,

    opatchauto returns with error code = 2
    Oracle Interim Patch Installer version 11.2.0.3.19

    Unsupported Java version 1.6.
    Java version should be 1.7 or higher.

    You should try to upgrade opatch. Is this home a 12 or an 11 ? I can see (old) opatch 12 mentioned and also an opatch 11. Or is your GI running 12+ and DB home 11 ?

    I would Upgrade the opatches and try again.

    ReplyDelete
  10. Dear Fred, following proposal. Maybe under all your blog posts, about scripts, matters to ask your readers (all of us), so that instead of posting errors in comments below each post - instead should be opened Issues on GitHub, in each particular project (for each particular script). That way you will have proper history of commits (of fixes/changes), normal syntax highlighting - and all other benefits and features, provided by GitHub. Matters to explain in advance proper way, how to formulate each Issue - which info to provide (versions of DB/GI Homes, of opatch etc.), which commands should be executed to provide necessary diagnostics info - and so on. That way you will eliminate your "first standard answer" - like "please provide output of 'bash - xv , output from -o ' et." - and so on. Just as idea!

    ReplyDelete

Load 2 billion rows with SQL LOADER

I worked on few Extract data / transform data (nor not) / load data into an Oracle database projects during my professional life and SQL ...