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 184.108.40.206.180417, Oracle JavaVM Component (APR2018) 21923026; 27338020;OCW PATCH SET UPDATE 220.127.116.11.180417 (27338020) 27338029;Database Bundle Patch : 18.104.22.168.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/22.214.171.124/dbhome_1 Home : [oracle@anexadata_db01 ~]$ ./lspatches.sh -g /u01/app/oracle/product/126.96.36.199/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/188.8.131.52/grid /u01/app/oracle/product/184.108.40.206/dbhome_1 /u01/app/oracle/product/220.127.116.11/dbhome_2 /u01/oapdb/18.104.22.168 [oracle@anexadata_db01 ~]$ ./lspatches.sh -g 12 -v oap -s ORACLE_HOMEs that would be considered (/etc/oratab) : /u01/app/22.214.171.124/grid /u01/app/oracle/product/126.96.36.199/dbhome_1 /u01/app/oracle/product/188.8.131.52/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/184.108.40.206/grid /u01/app/oracle/product/220.127.116.11/dbhome_1 /u01/app/oracle/product/18.104.22.168/dbhome_2 /u01/app/oracle/product/22.214.171.124/dbhome_1 /u01/app/oracle/product/126.96.36.199/dbhome_2 /u01/oapdb/188.8.131.52 [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/184.108.40.206/grid . . . OK Proceeding with /u01/app/oracle/product/220.127.116.11/dbhome_1 . . . OK Proceeding with /u01/app/oracle/product/18.104.22.168/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/22.214.171.124/grid /u01/app/oracle/product/126.96.36.199/dbhome_1 /u01/app/oracle/product/188.8.131.52/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/184.108.40.206/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
The "220.127.116.11.13 gate" / "18.104.22.168.1 gate"
As I wrote about in this post, opatch 22.214.171.124.13 and 126.96.36.199.1 do not provide any remote patch information and a new -remote option will be released with 188.8.131.52.14 / 184.108.40.206.20 but these versions are not released yet so here is a summary on how the script reacts to this :
- Versions < 220.127.116.11.13 / 18.104.22.168.1 : This is the known behavior and as the -all_nodes provides the remote patch information, lspatches.sh shows all the patches information as expected.
- Version = 22.214.171.124.13 / 126.96.36.199.1 : These versions show a warning about not using the -all_nodes option and give no remote patch information so the script acts a local execution and shows an informative warning about this
Warning : Versions 188.8.131.52.13 and 184.108.40.206.1 have no -all_nodes options then cannot get any remote patch information.
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
Finally, 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
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 !