Closure
Closure
Home » Databases » Oracle Database » How do we apply the latest patch (PSU) to an Oracle database

How do we apply the latest patch (PSU) to an Oracle database

How do we apply the latest patch (PSU) to an Oracle database
, , Leave a comment
How do we apply the latest patch (PSU) to an Oracle database
Latest posts by Stratos Matzouranis (see all)

In the article we will analyze its process patch set update (PSU) in Oracle Database. The process is done through the two Oracle tools OPatch and Datapatch. The OPatch is a tool that allows applying and uninstalling patches to software (binaries) of Oracle. On the other hand Datapatch applies these updates to the database and which specifically to data dictionary (system views and tables).

How to download the patch set update

Through the Oracle support portal, we connect to the link below.

*We need to have an account that has the right to download PSU (paid), if the version has been released in Extended Support e.g. 12.1 has a special cost.

https://support.oracle.com/portal/

We select Patches & Updates, Advanced, Product Oracle Database Enterprise and the Release we have.

How do we apply the latest patch (PSU) to an Oracle database

There it will load a list, we order it with the updated column and look for the latest patch set update for the operating system we have.

How do we apply the latest patch (PSU) to an Oracle database

On the next screen we can find information about the patch which contains cumulative and all previous ones.

We choose it functional and Download:

How do we apply the latest patch (PSU) to an Oracle database

We can either download it and ftp it to the server or use wget.sh which will download it directly to the server using ssh.

How do we apply the latest patch (PSU) to an Oracle database

The preparation

We have downloaded the PSU in the “Downloads” folder:

[oracle@dp-gr Downloads]$ ls -ltr
total 2155088
drwxrwxr-x+ 7 oracle oracle       4096 Jul  7  2014 database
-rw-rw-r--. 1 oracle oracle 2206794758 Jul  8 15:14 p31113348_121020_Linux-x86-64.zip
-rwxrwxr-x. 1 opc    opc          1949 Sep 21  2020 wget.sh

We check what version the OPatch tool has, it is not possible to apply patches with an old version:

[oracle@dp-gr Downloads]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.1.0.1.3

We see that it is old so we will delete its folder and download the latest.

Move the PSU .zip to ORACLE_HOME and unzip it.

At the same time, we also delete the OPatch folder located in ORACLE_HOME since it will be replaced:

[oracle@dp-gr Downloads]$ cp p31* $ORACLE_HOME/
[oracle@dp-gr Downloads]$ cd $ORACLE_HOME
[oracle@dp-gr db_1]$ rm -rf OPatch
[oracle@dp-gr db_1]$ unzip p31113348_121020_Linux-x86-64.zip

How to install the latest OPatch

We connect to the link below:

https://updates.oracle.com/download/6880880.html

We choose the correct release and operating system (careful with 32 and 64bit):

How do we apply the latest patch (PSU) to an Oracle database

After it is downloaded, copy it to ORACLE_HOME and unzip it:

[oracle@dp-gr Downloads]$ cp p688* $ORACLE_HOME/
[oracle@dp-gr Downloads]$ cd $ORACLE_HOME
[oracle@dp-gr db_1]$ unzip p6880880_121010_LINUX.zip

Delete the zips we no longer need:

[oracle@dp-gr db_1]$ rm -rf *.zip

Now we will see that we have the new version of OPatch:

[oracle@dp-gr db_1]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.21

OPatch succeeded

The process

Go to the folder with the PSU id located in ORACLE_HOME:

[oracle@dp-gr db_1]$ cd 31113348

We check if there is any conflict with other patches:

[oracle@dp-gr 31113348]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.21
Copyright (c) 2020, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /home/oracle/oracle/app/oracle/product/12.1.0.2/db_1
Central Inventory : /home/oracle/oracle/app/oraInventory
   from           : /home/oracle/oracle/app/oracle/product/12.1.0.2/db_1/oraInst.loc
OPatch version    : 12.2.0.1.21
OUI version       : 12.1.0.2.0
Log file location : /home/oracle/oracle/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatch/opatch2020-09-21_13-27-13PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

It's time to stop him listener so that no other external user can connect and then to let's close the base:

[oracle@dp-gr 31113348]$ lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 21-SEP-2020 13:29:22

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dp-gr)(PORT=1521)))
The command completed successfully
[oracle@dp-gr 31113348]$ sqlplus / as sysdba

SQL> shutdown immediate;

We do one last check on the files:

** If it fails with /sbin/fuser and the name of the file we find which process uses it and we do it kill.

[oracle@dp-gr 31113348]$ $ORACLE_HOME/OPatch/opatch prereq CheckActiveFilesAndExecutables -ph ./

Oracle Interim Patch Installer version 12.2.0.1.21
Copyright (c) 2020, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /home/oracle/oracle/app/oracle/product/12.1.0.2/db_1
Central Inventory : /home/oracle/oracle/app/oraInventory
   from           : /home/oracle/oracle/app/oracle/product/12.1.0.2/db_1/oraInst.loc
OPatch version    : 12.2.0.1.21
OUI version       : 12.1.0.2.0
Log file location : /home/oracle/oracle/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatch/opatch2020-09-21_13-32-03PM_1.log

Invoking prereq "checkactivefilesandexecutables"

Prereq "checkActiveFilesAndExecutables" for patch 20299023 passed.

.
.
.
Prereq "checkActiveFilesAndExecutables" for patch 31113348 passed.

OPatch succeeded.

By command opatch apply patching finally begins:

[oracle@dp-gr 31113348]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.21
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


Oracle Home       : /home/oracle/oracle/app/oracle/product/12.1.0.2/db_1
Central Inventory : /home/oracle/oracle/app/oraInventory
   from           : /home/oracle/oracle/app/oracle/product/12.1.0.2/db_1/oraInst.loc
OPatch version    : 12.2.0.1.21
OUI version       : 12.1.0.2.0
Log file location : /home/oracle/oracle/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatch/opatch2020-09-21_13-41-15PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   19769480  20299023  20831110  21359755  21948354  22291127  23054246  24006101  24732082  25171037  25755742  26609783  26713565  26925311  27338041  27547329  28259833  28729169  29141015  29494060  29918340  30340202  30700212  31113348

Do you want to proceed? [y|n]
Composite patch 31113348 successfully applied.
Log file location: /home/oracle/oracle/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatch/opatch2020-09-21_13-41-15PM_1.log

OPatch succeeded.

To see which patches were applied:

[oracle@dp-gr 31113348]$ $ORACLE_HOME/OPatch/opatch lsinventory

Oracle Interim Patch Installer version 12.2.0.1.21
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


Oracle Home       : /home/oracle/oracle/app/oracle/product/12.1.0.2/db_1
Central Inventory : /home/oracle/oracle/app/oraInventory
   from           : /home/oracle/oracle/app/oracle/product/12.1.0.2/db_1/oraInst.loc
OPatch version    : 12.2.0.1.21
OUI version       : 12.1.0.2.0
Log file location : /home/oracle/oracle/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatch/opatch2020-09-21_15-03-14PM_1.log

Lsinventory Output file location : /home/oracle/oracle/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatch/lsinv/lsinventory2020-09-21_15-03-14PM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: dp-gr.subnet.vcn.oraclevcn.com
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 12c                                                  12.1.0.2.0
There are 1 products installed in this Oracle Home.


Interim patches (1) :

Patch  31113348     : applied on Mon Sep 21 14:44:47 GMT 2020
Unique Patch ID:  23682224
Patch description:  "Database Patch Set Update : 12.1.0.2.200714 (31113348)"
   Created on 7 Jul 2020, 20:50:51 hrs PST8PDT

After the upgrade to the software binaries is complete, with the datapatch we will also update the database.

But first, let's start with the basics:

[oracle@dp-gr admin]$ sqlplus / as sysdba

SQL> startup;

SQL> exit;

And then we run it datapatch:

[oracle@dp-gr admin]$ $ORACLE_HOME/OPatch/datapatch -verbose

SQL Patching tool version 12.1.0.2.0 Production on Mon Sep 21 15:09:55 2020
Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /home/oracle/oracle/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_29130_2020_09_21_15_09_55/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 PSU:
  ID 200714 in the binary registry and not installed 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:
    31113348 (DATABASE PATCH SET UPDATE 12.1.0.2.200714)

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

Validating logfiles...
Patch 31113348 apply: SUCCESS

We can now bring up the listener again so that other users can connect:

[oracle@dp-gr db_1]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 21-SEP-2020 18:21:03

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dp-gr)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                21-SEP-2020 18:10:08
Uptime                    0 days 0 hr. 10 min. 55 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/oracle/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File         /home/oracle/oracle/app/oracle/diag/tnslsnr/dp-gr/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dp-gr.subnet.vcn.oraclevcn.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=dp-gr.subnet.vcn.oraclevcn.com)(PORT=5500))(Security=(my_wallet_directory=/home/oracle/oracle/app/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...

After the listener is up, we can also connect to an external application.

With the following query we can see the PSU passed and when:

select patch_id,status,action_time,description from dba_registry_sqlpatch order by action_time;
How do we apply the latest patch (PSU) to an Oracle database

To delete the garbage left from the installation, run it opatch cleanup (we will still be able to rollback the patches):

[oracle@dp-gr db_1]$ $ORACLE_HOME/OPatch/opatch util cleanup
Oracle Interim Patch Installer version 12.2.0.1.21
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


Oracle Home       : /home/oracle/oracle/app/oracle/product/12.1.0.2/db_1
Central Inventory : /home/oracle/oracle/app/oraInventory
   from           : /home/oracle/oracle/app/oracle/product/12.1.0.2/db_1/oraInst.loc
OPatch version    : 12.2.0.1.21
OUI version       : 12.1.0.2.0
Log file location : /home/oracle/oracle/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatch/opatch2020-09-21_18-11-50PM_1.log

Invoking utility "cleanup"
OPatch will clean up 'restore.sh,make.txt' files and 'scratch,backup' directories.
You will be still able to rollback patches after this cleanup.
Do you want to proceed? [y|n]
Y
User Responded with: Y

Backup area for restore has been cleaned up. For a complete list of files/directories
deleted, Please refer log file.

OPatch succeeded.

If we want to rollback the patch...

In the event that we want to rollback the patch, we can, while we have downloaded the database, run the following command stating the patch id that we want to remove:

[oracle@dp-gr db_1]$ $ORACLE_HOME/OPatch/opatch rollback -id 31113348

However, after the rollback to the Oracle binaries is completed, we should run datapatch again (with the database uploaded) so that the changes to the database tables will also be passed:

[oracle@dp-gr db_1]$ $ORACLE_HOME/OPatch/datapatch -verbose

Sources:

Share it

Leave a reply