Sunday, 26 July 2020

Oracle CPU downloader

Every quarter I have to go through and download numerous patches for the Oracle CPU (Critical Patch Update). You have to view the CPU document on MOS, click on numerous links to get to the correct software, version, OS etc. and then click the links for each patch you require click the link to download and then click the link to actually download., and so it goes on.

So as per usual, you get to the point when you've had enough. Each application now requires multiple downloads, WebLogic Server, 4 patches, Portal 4-5 patches, database 3 patches, you get the picture.

So I decided to at least automate the downloading of patches.

You can download my script here, and use it as your own risk.

CSV File

You start by editing the CSV file and entering certain details about the patch.

Patch ID

Enter the Patch ID from the CPU page


This is the CPU date or the old style patch version or can be left blank. I personally only use the YYMM format when using the CPU style. If you don't want the CPU to be used to define the output folder name then leave blank. This is used to create the folder name for each Group, see below.


This has two purposes. First is to make it easier to see which Patch ID you need to update later on in the next CPU cycle. Second, the script uses it for a message when the patch isn't found in the patch recommendations XML file.


This is used to group patches together, for example, a group name of Portal will create a Portal folder and download multiple patches to the same folder. See example below.

OS (Linux-x86-64 | MSWIN-x86-64) for Generic leave blank

A lot of the patches are Generic, i.e. not OS specific, and some patches are OS specific. In this case you can define an optional 5th field too define which OS version of the patch you wish to download.

My sample CSV file, for the July 2020 CPU, looks like this...

# Patch ID, CPU|Version|blank, Description, Group, OS (Linux-x86-64 | MSWIN-x86-64) for Generic leave blank
# Portal,,,,
31390302,2007,WebCenter - Content,Portal,
31481845,2007,WebCenter - Portal,Portal,
31441160,2007,WebCenter - ADF,Portal,
31403376,2007,WebCenter - Core,Portal,
# WLS,,,,
31384951,2007,WLS - SPU,WLS,
31470751,2007,WLS - Coherence,WLS,
31544340,2007,WLS - ADR,WLS,Linux-x86-64
31544340,2007,WLS - ADR,WLS,MSWIN-x86-64
# WLS,,,,
31384959,2007,WLS - SPU,WLS,
31470730,2007,WLS - Coherence,WLS,
31544353,2007,WLS - ADR,WLS,Linux-x86-64
31544353,2007,WLS - ADR,WLS,MSWIN-x86-64
# Primavera,,,,
31541012,20,P6 18.8 EPPM,P6,
31618417,20,P6 18.8 Pro,P6_Pro,
31497173,7,P6 19.12 EPPM,P6,
31618148,7,P6 19.12 Pro,P6_Pro,
# RDBMS 11g,,,,
31326405,2007,DB 11g PSU OJVM Combo,RDBMS,Linux-x86-64
31302572,2007,DB 11g OH JDK,RDBMS,Linux-x86-64
30508206,2007,DB 11g OH Perl,RDBMS,Linux-x86-64
# RDBMS 12.1,,,,
31326396,2007,DB 12.1 PSU OJVM Combo,RDBMS,Linux-x86-64
31302525,2007,DB 12.1 OH JDK,RDBMS,Linux-x86-64
30508171,2007,DB 12.1 OH Perl,RDBMS,Linux-x86-64
# RDBMS 12.1 Windows,,,,
31211574,2007,DB 12.1 PSU,RDBMS_WIN,MSWIN-x86-64
31465095,2007,DB 12.1 OJVM,RDBMS_WIN,MSWIN-x86-64
# RDBMS 12.2,,,,
31326379,2007,DB 12.2 PSU OJVM Combo,RDBMS,Linux-x86-64
31302499,2007,DB 12.2 OH JDK,RDBMS,Linux-x86-64
30508161,2007,DB 12.2 OH Perl,RDBMS,Linux-x86-64
# RDBMS 19c,,,,
31326362,2007,DB 19 RU OJVM Combo,RDBMS,Linux-x86-64
31301460,2007,DB 19 OH JDK,RDBMS,Linux-x86-64
29511771,2007,DB 19 OH Perl,RDBMS,Linux-x86-64

As you can see I group a number of different patches together so that they all download into the same folder. My Portal Group downloads Portal, Content, ADF and Core into the same folder.

Folder Names

Talking of folder names, I use the information in the CSV file to create the folders.

If CPU field is not empty I create the folder as Group_Version.CPU, if the Group field is empty I just use the Group to create the folder.

So a line like this: 31390302,2007,WebCenter - Content,Portal,

Will create the folder: Portal_12.

The version is retrieved from the recommendations file for each patch.

Configuration File

There is a configuration file with two options, one is optional.


This defines where the script will create the patch folders under.

From the above example, if you were to set outputFolderBase to OraMedia/Patches the script would download the Portal patches into OraMedia/Patches/Portal_12.


The script will prompt you for your MOS username each time you run it. You can define this setting in the configuration file and it will stop you from being prompted every time. useful if it's only you that ever runs the script.

Running the script

So we are finally set, we have defined outputFolderBase and optionally, oraEmail in the configuration file.

This script can be run from the patch-downloader folder or from elsewhere, it will find its configuration and CSV files etc.

The script will firstly ask you to authenticate to MOS. Assuming that was successful, it will then under two conditions download the patch recommendations XML file it uses to get the URLs for the patches.

Either the patch recommendations XML file doesn't exist or it is over 30 days old. It does this by downloading the OEM catalog zip file into outoutFolderBase/OEM/Catalog and extracting the files/data it requires to work.

Iterating over your CSV file, the script will attempt to work out the download URL, version and bundle name and then download the file into the correct patch directory.

Not all patches are listed in the patch recommendations XML file. For these missing patches the script will search for the patch number and download its individual XML fie to get the same information and download the patch as usual.

If the patch file already exists it will not attempt to download it again. So if a patch fails you will need to delete the file to force the script to download it again.

At the moment this script will not download OPatch or Java files as they are not part of the patch recommendations file and don't have individual patch XML files. Maybe in the next version of the script.

Friday, 3 July 2020

Oracle 19c / as sysdba to PDB

Up until now I, like most others, have when attempting to connect as sys to a PDB have used either:

. oraenv -s

sqlplus / as sysdba

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 TEST                           READ WRITE NO
         6 TEST2                          READ WRITE NO

SQL> alter session set container=TEST;
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 TEST                           READ WRITE NO
or this, ensuring I have a TNSNames.ora alias set for TEST:

sqlplus sys/sys-pwd@test

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 TEST                           READ WRITE NO

Well I've just learnt another, easier, way to connect as sys directly to a PDB. This seems to be new to 19c, I've tried with 12c and it doesn't work.

. oraenv -s


sqlplus / as sysdba

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 TEST                           READ WRITE NO

It's this new variable ORACLE_PDB_SID that does the magic.

You are now connected directly to the PDB from the command line and not having to enter the sys password.

I haven't tried this on Windows but I assume that it will work as well.

Oracle CPU downloader

Every quarter I have to go through and download numerous patches for the Oracle CPU (Critical Patch Update). You have to view the CPU docume...