Keeping OPatch updated with a simple SQL query

I tweeted out this pic yesterday

image

which suggested that by running a SQL query, I could magically keep my OPatch version up to date.

This is indeed true, but in reality, I wanted to post this as another example of how you can leverage the features of external tables to perform non-database style operations from within the database. Taking a look at another picture from the tweet, you can start to get a glimpse of how this opatch check is being tackled

image

It is an external table that contains the PREPROCESSOR option to run a script and use the output of that script as the input data to the external table. As long as the script outputs data to STDOUT in a well-formatted fashion, we’ll be able to query the output like any table.

This is a great for Sys Admins who would like to make a subset of OS commands available to DBAs, or for DBAs that would like to make some OS facilities available to Developers. Let us explore how OPatch can be kept dynamically up to date using the run_os_patch.bat file above, and thus the external table.

A couple of things to note:

  • I did this for Windows because it fit the needs of the client I assisted. I think it would even easier in Unix because of the ease of using shell scripting, and

  • Don’t flame me for my terrible MS-DOS batch skills. This was a script built with chicken wire, straw, Google and StackOverflow

    Smile

    but it gets the job done.

Patches on MOS have an XML description which describe details of the patch and where it fits in the overall scheme of patches for the database. We can download that to discover the version of OPatch on MOS and the full download URL for the database platform we are working with.

Then it is just a case of comparing that with our own OPatch version, which you can get with “opatch version” and if they match up, then the task is complete. If not, then we go ahead and download the new version and replace OPatch on your Oracle software installation. Thus here is the script to do all of that

@echo off

REM
REM Stuff you will need to set
REM

set TMP=x:\temp
set PATH=x:\bin;x:\bin\usr\local\wbin;%PATH%
set ORACLE_HOME=c:\oracle\product\19
cd /d %TMP%

REM
REM Temp files that are used
REM

del /q patch.xml  >nul
del /q patch.aru  >nul
del /q patch.current >nul
del /q patch.mos >nul
del /q opatch.zip >nul

REM
REM Get the XML details for opatch (patch 6880880) and 
REM   1 - find the download URL in case we need it
REM   2 - find its version number
REM

wget --quiet --http-user=CSI_USERNAME --http-password=CSI_PASSWORD --no-check-certificate --output-document=patch.xml "https://updates.oracle.com/Orion/Services/search?bug=6880880"
cat patch.xml | grep download_url | grep 6880880 | grep MSWIN | sort | tail -1 | awk --field-separator=^[ "{print $3}" | awk --field-separator=^] "{print $1}" > patch.aru
head -50 patch.xml | grep abstract | head -1 | awk --field-separator=^[ "{print $3}" | awk --field-separator=^] "{print $1}" | awk "{print $2}" > patch.mos

REM
REM Get the current opatch version in your Oracle Home
REM

cd /d %ORACLE_HOME%/OPatch
opatch version | awk  "{print $3}" > %TMP%\patch.current
cd /d %TMP%

REM
REM Send them out to the external table, and set them as variables
REM

for /F "delims=" %%i in (patch.current) do ( echo CURRENT,%%i )
for /F "delims=" %%i in (patch.mos) do ( echo AVAILABLE,%%i )

for /F "delims=" %%i in (patch.current) do ( set PCURRENT=%%i )
for /F "delims=" %%i in (patch.mos) do ( set PAVAILABLE=%%i )

REM
REM If they match we are done
REM

if "%PCURRENT%" == "%PAVAILABLE%" ( goto :done )

REM
REM Otherwise we download using the download URL we found, relocate existing OPatch and install the new one
REM

echo DOWNLOADING,%PAVAILABLE%
for /F "delims=" %%i in (patch.aru) do ( wget --quiet --http-user=CSI_USERNAME --http-password=CSI_PASSWORD --no-check-certificate --output-document=opatch.zip "https://updates.oracle.com%%i" )

cd /d %ORACLE_HOME%
echo RELOCATING,OH/OPatch
move OPatch OPatch.old
unzip -qq %TMP%\opatch.zip
echo INSTALLING,%PAVAILABLE%

:done

Now with just a simple SQL statement OPatch is always up to date

SQL> select * from opatch_check;

TAG                              VERSION
-------------------------------- -----------------
CURRENT                          12.2.0.1.23
AVAILABLE                        12.2.0.1.25
DOWNLOADING                      12.2.0.1.25
INSTALLING                       12.2.0.1.25

Be aware that you probably want to take some additional care here than what the script contains, for example, ensuring that no-one is using OPatch at the time, or perhaps just warning about a version mis-match rather than directly upgrading it etc. But the script should be enough to get you on the journey. My personal hope is that an update OPatch always got shipped with every patch you download, and applying any database patch would automatically take care of OPatch as part of the process. Maybe one day that will come to fruition.

If you are tempted take this further, build something yourself that instead of looking the OPatch version, uses OPatch itself list out the patches that have been applied, then we’ve saved you the trouble. That functionality is already in the database for you!

SQL> select description from DBA_REGISTRY_SQLPATCH;

DESCRIPTION
----------------------------------------------------------------
Windows Database Bundle Patch : 19.5.0.0.191015 (30151705)
Windows Database Bundle Patch : 19.6.0.0.200114 (30445947)
OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)
Windows Database Bundle Patch : 19.7.0.0.200414 (30901317)
OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)
OJVM RELEASE UPDATE: 19.8.0.0.200714 (31219897)
Windows Database Bundle Patch : 19.8.0.0.200714 (31247621)
OJVM RELEASE UPDATE: 19.8.0.0.200714 (31219897)
OJVM RELEASE UPDATE 19.10.0.0.0 (32067171)
Windows Database Bundle Patch : 19.10.0.0.210119 (32062765)
OJVM RELEASE UPDATE 19.10.0.0.0 (32067171)
OJVM RELEASE UPDATE 19.11.0.0.0 (32399816)
Windows Database Bundle Patch : 19.11.0.0.210420 (32409154)

Enjoy!