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 !