Patching my databases with the April 2018 PSU, BP and RU

Patching is fun, isn’t it? And you may have heard already that the April 2018 patch bundles got released on April 17, 2018. And I thought I share a little bit of fun with you with a quick guide to patching my databases with the April 2018 PSU, BP and RU. For this exercise I use our Hands-On Lab with Oracle 11.2.0.4, Oracle 12.1.0.2 and Oracle 12.2.0.1 installed.

Prerequisites for patching my databases with the April 2018 PSU, BP and RU

First of all, in my VBox environment, I shutdown the databases for each release before patching them – and the central listener I use from within the Oracle 12.2.0.1 environment:

. cdb2
lsnrctl stop

In addition I will update OPatch in my 12.2 home and use this version for all 12c patch activities on the system. Please download the newest OPatch version 12.2.0.1.13 via patch 6880880 from MyOracle Support. Choose “12.2.0.1.0” in the drop-down list.

Patching my databases with the April 2018 PSU, BP and RU

Download OPatch 12.2.0.1.13 via MyOracle Support’s patch 6880880

The readme explains that the previous OPatch directory should be backed up and the new OPatch should be unpacked from the destination’s $ORACLE_HOME. I just heard from Kay Liesenfeld that “removing” the old directory may not be a good idea as he had trouble with the most recent GI patches then.

For the 11g database patching I will use my current OPatch version 11.2.0.3.12.

Furthermore, to identify the correct patches for each release I used MOS Note: 2353306.1 (Critical Patch Update (CPU) Program April 2018 Patch Availability Document (PAD)) as neither the Availability and Known Issues notes nor the Patch Download Assistant were updated two days after patch release. Go to bullet point 3.1.4 in MOS Note: 2353306.1 for Oracle Database Patch Availability. The other notes should be updated already when you read this blog post.

MOS Note: 2118136.2 – Assistant: Download Reference for Oracle Database/GI RU, BP, PSU was not updated by the time I wrote this blog post but is now.

Patching Oracle 11.2.0.4 with the April 2018 Patch Set Update

To patch my Oracle 11.2.0.4 databases I downloaded the Patch Set Update April 2018 as Bundle Patches in Oracle 11g are meant for Engineered Systems only:

First steps after changing into the patches directory are the checks:

cd /media/sf_TEMP/p27338049_112040_Linux-x86-64/27338049

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2018, Oracle Corporation.  All rights reserved.

PREREQ session
Oracle Home       : /u01/app/oracle/product/11.2.0.4
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.4/oraInst.loc
OPatch version    : 11.2.0.3.12
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2018-04-20_14-50-41PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.

Once the check is passed I can apply the patch – and of course, I must ensure to have shutdown my databases (UPGR and FTEX in the hands-on lab) first:

$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2018, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0.4
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.4/oraInst.loc
OPatch version    : 11.2.0.3.12
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2018-04-20_14-54-30PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   27338049  
...
Backing up files...
Applying sub-patch '27338049' to OH '/u01/app/oracle/product/11.2.0.4'
...
Patching component oracle.rdbms, 11.2.0.4.0...
Patching component oracle.rdbms.rman, 11.2.0.4.0...
...
Composite patch 27338049 successfully applied.
Log file location: /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2018-04-20_14-54-30PM_1.log
OPatch succeeded.

Finally I will have to execute catbundle.sql in all my 11.2.0.4 databases (UPGR and FTEX):

. upgr
cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba

@catbundle.sql psu apply
exit

Done!

Patching Oracle 12.1.0.2 with the April 2018 Bundle Patch

For Oracle Database 12.1.0.2 I downloaded the Database Proactive Bundle Patch April 2018:

This patch bundle contains also Clusterware and Client patches – but the database patch is actually 27338029. According to the REAME.html I execute the conflict check – but in my case using the 12.2.0.1.12 OPatch previously installed:

/u01/app/oracle/product/12.2.0.1/OPatch/opatch  prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_TEMP/p27486326_121020_Linux-x86-64/27486326/27338029

Both checks succeed.Afterwards I initiate the space check:

/u01/app/oracle/product/12.2.0.1/OPatch/opatch  prereq CheckSystemSpace --phBaseDir /media/sf_TEMP/p27486326_121020_Linux-x86-64/27486326/27338029

Now I am applying the patch:

$ cd /media/sf_TEMP/p27486326_121020_Linux-x86-64/27486326/27338029
$ /u01/app/oracle/product/12.2.0.1/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.13
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.1.0.2
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0.2/oraInst.loc
OPatch version    : 12.2.0.1.13
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2018-04-19_20-51-40PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   27338029  
...
Backing up files...
Applying sub-patch '27338029' to OH '/u01/app/oracle/product/12.1.0.2'
ApplySession: Optional component(s) [ oracle.has.crs, 12.1.0.2.0 ] , [ oracle.assistants.asm, 12.1.0.2.0 ]  not present in the Oracle Home or a higher version is found.
...
Composite patch 27338029 successfully applied.
Log file location: /u01/app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2018-04-19_20-51-40PM_1.log

OPatch succeeded.

In addition, datapatch needs to be executed now in both existing databases. Hence I’m starting up both databases, make sure the environment is set correctly (in the Lab: . db121 and . cdb1) and invoke datapatch:

$ /u01/app/oracle/product/12.2.0.1/OPatch/datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Thu Apr 19 21:00:31 2018
Copyright (c) 2012, 2017, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_10973_2018_04_19_21_00_31/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBBP:
  ID 180417 in the binary registry and ID 180116 in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied:
    27338029 (DATABASE BUNDLE PATCH 12.1.0.2.180417)

Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...
Patch 27338029 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/27338029/22055339/27338029_apply_DB12_2018Apr19_21_00_56.log (no errors)
SQL Patching tool complete on Thu Apr 19 21:01:02 2018

Once I repeated this for the second database, patching for both 12.1.0.2 databases done as well.

Patching Oracle 12.2.0.1 with the April 2018 Update

As you may know already, there are no PSUs anymore in Oracle 12.2 and higher. You should patch with the Updates. Therefore I downloaded the Database April 2018 Update first. As my favorite note to download the correct patch just gets updated overnight I used MOS Note:2239820.1 – 12.2.0.1 Base Release – Availability and Known Issues to access the patch.

It contains the Database 12.2.0.1 Update April 2018 and the OJVM (which I won’t need). I unpack it to a share: /media/sf_TEMP and install it from there.

$ cd /media/sf_TEMP/p27726453_122010_Linux-x86-64/27726453/27674384
[CDB2] oracle@localhost:/media/sf_TEMP/p27726453_122010_Linux-x86-64/27726453/27674384

Then I call opatch for a conflict check:

$ /u01/app/oracle/product/12.2.0.1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.13
Copyright (c) 2018, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home : /u01/app/oracle/product/12.2.0.1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12.2.0.1/oraInst.loc
OPatch version : 12.2.0.1.13
OUI version : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/opatch2018-04-19_14-51-43PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.

And finally I apply the patch – I’m still in /media/sf_TEMP/p27726453_122010_Linux-x86-64/27726453/27674384, the directory where the Database 2018 Update is located in.

$ /u01/app/oracle/product/12.2.0.1/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.13
Copyright (c) 2018, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/12.2.0.1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12.2.0.1/oraInst.loc
OPatch version : 12.2.0.1.13
OUI version : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/opatch2018-04-19_14-52-04PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 27674384
...
Backing up files...
Applying interim patch '27674384' to OH '/u01/app/oracle/product/12.2.0.1'
ApplySession: Optional component(s) [ oracle.has.crs, 12.2.0.1.0 ] , [ oracle.oid.client, 12.2.0.1.0 ] , [ oracle.ons.daemon, 12.2.0.1.0 ] , [ oracle.network.cman, 12.2.0.1.0 ] not present in the Oracle Home or a higher version is found.

Patching component oracle.network.rsf, 12.2.0.1.0...
...
Patching component oracle.sdo, 12.2.0.1.0...
Patch 27674384 successfully applied.
Sub-set patch [27105253] has become inactive due to the application of a super-set patch [27674384].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/opatch2018-04-19_14-52-04PM_1.log
OPatch succeeded.

To finalize the patch application I need to start my database(s) and all PDBs:

SQL> startup
SQL> alter pluggable database all open;
SQL> exit

And then run datapatch:

$ /u01/app/oracle/product/12.2.0.1/OPatch/datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Thu Apr 19 14:55:05 2018
Copyright (c) 2012, 2018, Oracle. All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_7777_2018_04_19_14_55_05/sqlpatch_invocation.log

...
For the following PDBs: CDB$ROOT PDB$SEED
The following patches will be rolled back:
25862693 (DATABASE BUNDLE PATCH 12.2.0.1.170516)
The following patches will be applied:
27674384 (DATABASE APR 2018 RELEASE UPDATE 12.2.0.1.180417)

Installing patches...
Patch installation complete. Total patches installed: 4

Validating logfiles...

That’s it.

At the end of the entire exercise I can restart my listener finally:

. cdb2
lsnrctl start

–Mike

18 thoughts on “Patching my databases with the April 2018 PSU, BP and RU

  1. Hi Mike,
    “Kay Liesenfeld that “removing” the old directory may not be a good idea as he had trouble with the most recent GI patches then”.
    What kind of trouble he had while GI-Patching ?
    Thank you, Peter

    • Peter,

      I can’t say it – but Kay mentioned explicitly to me: Don’t remove the old OPATCH directory but install the new OPATCH into the existing directory.

      Cheers,
      Mike

      • Hi Peter, Mike,
        gridSetup.sh Fails with this error message:
        [INS-42505] The installer has detected that the Oracle Grid Infrastructure home software at (/u01/app/12.2.0.1/grid) is not complete.
        This issue hits us while trying to install GI 12.2.0.1 with RU 01/2018 already applied.
        MOS Note:
        12.2 gridSetup.sh: INS-42505 File missing GRID_HOME/OPatch/opatchdiag (Doc ID 2321749.1)
        Regards
        Kay.

  2. Hi Mike,
    we did patch 12.2 with April RU, now I noticed that the description for OJVM in dba_registry_sqlpatch is missing:

    select patch_id, action, status, action_time, DESCRIPTION from dba_registry_sqlpatch order by 4 desc;

    PATCH_ID ACTION STATUS ACTION_TIME DESCRIPTION
    ———- ————— ————— ————————————————————————— —————————————————————————————————-
    27475613 APPLY SUCCESS 20-APR-18 02.44.04.245488 PM
    27674384 APPLY SUCCESS 20-APR-18 02.43.07.598686 PM DATABASE APR 2018 RELEASE UPDATE 12.2.0.1.180417
    27105253 ROLLBACK SUCCESS 20-APR-18 11.15.08.705247 AM DATABASE RELEASE UPDATE 12.2.0.1.180116
    27001739 ROLLBACK SUCCESS 20-APR-18 11.15.06.859815 AM OJVM RELEASE UPDATE: 12.2.0.1.180116 (27001739)
    27105253 APPLY SUCCESS 14-APR-18 11.01.32.817759 AM DATABASE RELEASE UPDATE 12.2.0.1.180116
    27001739 APPLY SUCCESS 14-APR-18 11.01.31.754178 AM OJVM RELEASE UPDATE: 12.2.0.1.180116 (27001739)

    Same problem with two other patched databases. Bug?

    Best Regards, Mika

  3. Sorry Mike, found the reason why the description was missing on the OJVM patch.

    We have always had the OPatch within the same folder where the upgrade is. Now when we patched we did have to install the patch with -force, else we got an error. With -force tha patches installed successfully but the description was missing.

    Did troubleshoot it yesterday on my labbmachines and found that the OPatch should be in OH folder, and that the patches should be run from that destination. Now I was able to install patches without the -force and the description is also there for OJVM.

    Maybe we have Always done the wrong way with the OPatch but it has worked great and this is the first time we got an problem.

    Still, big thanx for the bug# but you can clear that one as resolved, our mistake.

    We got a Little bit confued since we got also another error. In a container where we got 8 databases one of the databases did not patch correctly. We did at last manage to install the patches but then we were not able to startup the database. So, to be short of several hours of troubleshooting, my colleague found that this database did not have the ctssys schema. After we installed the ctxsys, all worked perfectly. Phew, now all is OK.

    // Mika

  4. I tried to apply APR 2018 RU and datapatch failed with
    DBD::Oracle::db prepare failed: ORA-00942: table or view does not exist (DBD ERROR: error possibly near indicator at char 50 in ‘SELECT patch_id, patch_uid, rowid
    FROM sys.dba_registry_sqlpatch_ru_info’) [for Statement “SELECT patch_id, patch_uid, rowid
    FROM sys.dba_registry_sqlpatch_ru_info”] at /opt/oracle/product/18c/racdb18.1.0/sqlpatch/sqlpatch.pm line 2788.
    Line 2788 from sqlpatch.pm
    my $ru_info_query =
    “SELECT patch_id, patch_uid, rowid
    FROM sys.dba_registry_sqlpatch_ru_info”;
    my $ru_info_stmt = $dbh->prepare($ru_info_query);
    The DB does not have this view as this is first time its getting patched after it was created in March under the base release.

  5. 12.2.0.1.13.

    What I have found is sys.dba_registry_sqlpatch_ru_info is missing and therefore datapatch is failing.

    • Hi Sreedhar,

      OMG … and yes, this MUST fail as there’s neither a view called DBA_REGISTRY_SQLPATCH_RU_INFO nor a column in DBA_REGISTRY_SQLPATCH called “RU INFO”.

      Did you log an SR? Then I would send it to the appropriate person to take care on it.

      Thanks!
      Mike

      • Sreedhar,

        we spoke to the person who’s responsible for datapatch. The table should have been created during datapatch boostrap.

        Can we see the contents of the invocation directory if possible.
        The output from datapatch will show the invocation log. We would need the entire directory.

        Thanks,
        Mike

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree