ORACLE DATABASE CRITICAL PATCH – JANUARY 2021

Description:

In this article we are going to see Oracle12c Critical Patch Update for January 2021

High Level steps for applying the Patch:

Check current version of Opatch Tool
Upgrade the Opatch utility
Take the backup of ORACLE_HOME
Shutdown the Database and Listener
Apply PSU patch on ORACLE_HOME
Execute Post installation Scripts (datapatch)
Startup the Database and Listener
Check the dba_registry_sqlpatch .

Search and download patch from oracle support site.

Go to Dashboard and then Patches and Updates.

Once window will open and it will ask for the product, release and the platform as shown below.

CLICK 32228578 PATCH

Click on Download

Check the OPatch Version using Below Command

Move the Downloaded Patch to the ORACLE HOME location and Unzip the Patch.

RUN THE PRE REQ BEFORE APPLYING THE PATCH

[oracle@trichy PATCH2021]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.19

PREREQ session

Oracle Home : /u01/app/oracle/product/12.2.0.1/db_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/12.2.0.1/db_1/oraInst.loc

OPatch version : 12.2.0.1.19

OUI version : 12.2.0.1.4

Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-20_10-21-26AM_1.log

Invoking prereq “checkconflictagainstohwithdetail”

Prereq “checkConflictAgainstOHWithDetail” passed.

OPatch succeeded.

[oracle@trichy 32228578]$ $ORACLE_HOME/OPatch/opatch prereq CheckActiveFilesAndExecutables -ph ./

Oracle Interim Patch Installer version 12.2.0.1.19

PREREQ session

Oracle Home : /u01/app/oracle/product/12.2.0.1/db_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/12.2.0.1/db_1/oraInst.loc

OPatch version : 12.2.0.1.19

OUI version : 12.2.0.1.4

Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-20_10-23-55AM_1.log

Invoking prereq “checkactivefilesandexecutables”

Prereq “checkActiveFilesAndExecutables” for patch 32228578 passed.

OPatch succeeded.

SHUTDOWN DATABASE AND THE LISTENER.

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@trichy PATCH2021]$ lsnrctl stop

LSNRCTL for Linux: Version 12.2.0.1.0 – Production on 20-JAN-2021 10:53:22

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=####)(PORT=1537)))

The command completed successfully.

TAKE BACKUP OF ORAINVENTORY

[oracle@trichy u01]$ cd app/

[oracle@trichy app]$ ls

oracle oraInventory

[oracle@trichy app]$ ll

total 12

drwxr-xr-x. 5 oracle oinstall 4096 Jan 20 10:59 old_oraInventory

drwxrwxr-x. 10 oracle oinstall 4096 Sep 28 23:01 oracle

drwxrwxr-x. 5 oracle oinstall 4096 Sep 24 02:22 oraInventory

GO TO THE PATCH FOLDER AND APPLY THE PATCH.

BEFORE APPLYING PATCH EXPORT THE PATH OF OPATCH FOLDER.

[oracle@trichy ~]$ cd /u01/PATCH2021/32228578/

[oracle@trichy 32228578]$ export PATH=/u01/app/oracle/product/12.2.0.1/db_1/OPatch:$PATH

[oracle@trichy 32228578]$ opatch version

OPatch Version: 12.2.0.1.19

OPatch succeeded.

[oracle@trichy 32228578]$ opatch apply

Oracle Interim Patch Installer version 12.2.0.1.19

Oracle Home : /u01/app/oracle/product/12.2.0.1/db_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/12.2.0.1/db_1/oraInst.loc

OPatch version : 12.2.0.1.19

OUI version : 12.2.0.1.4

Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-20_11-45-30AM_1.log

Verifying environment and performing prerequisite checks…

Prerequisite check “CheckMinimumOPatchVersion” failed.

The details are:

The OPatch being used has version 12.2.0.1.19 while the following patch(es) require higher versions:

“Patch 32228578 requires OPatch version 12.2.0.1.23.”

Please download latest OPatch from My Oracle Support.

UtilSession failed: Prerequisite check “CheckMinimumOPatchVersion” failed.

Log file location: /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-20_11-45-30AM_1.log

OPatch failed with error code 73

DOWNLOAD LATEST VERSION OF OPATCH TO APPLY THE RECENT PATCH

[oracle@trichy OPATCH]$ ll

total 119552

drwxr-x—. 15 oracle oinstall 4096 Nov 6 01:20 OPatch

-rwxrw-rw-. 1 oracle oinstall 122415188 Jan 20 11:58 p6880880_200000_LINUX.zip

UNZIP THE OPATCH

[oracle@trichy OPATCH]$ unzip p6880880_200000_LINUX.zip

inflating: OPatch/auto/core/modules/legacyoui/com.oracle.cie.comdev_7.8.5.0.jar

inflating:

….……….

inflating: OPatch/private/commons-compress-1.19.jar

inflating: OPatch/opatch_env.sh

inflating: OPatch/opatchauto

inflating: OPatch/operr

inflating: OPatch/opatchauto.cmd

[oracle@trichy OPatch]$ $ORACLE_HOME/OPatch/opatch version

OPatch Version: 12.2.0.1.23

OPatch succeeded.

CHECK AND VERIFY THE INVENTORY FOR THE LATEST APPLIED PATCH. IT WILL SHOW THE LATEST APPLIED PATCH

[oracle@trichy PATCH2021]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.23

PREREQ session

Oracle Home : /u01/app/oracle/product/12.2.0.1/db_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/12.2.0.1/db_1/oraInst.loc

OPatch version : 12.2.0.1.23

OUI version : 12.2.0.1.4

Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-20_14-21-26PM_1.log

Invoking prereq “checkconflictagainstohwithdetail”

Prereq “checkConflictAgainstOHWithDetail” passed.

OPatch succeeded.

[oracle@trichy 32228578]$ $ORACLE_HOME/OPatch/opatch prereq CheckActiveFilesAndExecutables -ph ./

Oracle Interim Patch Installer version 12.2.0.1.23

PREREQ session

Oracle Home : /u01/app/oracle/product/12.2.0.1/db_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/12.2.0.1/db_1/oraInst.loc

OPatch version : 12.2.0.1.23

OUI version : 12.2.0.1.4

Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-20_14-23-55PM_1.log

Invoking prereq “checkactivefilesandexecutables”

Prereq “checkActiveFilesAndExecutables” for patch 32228578 passed.

OPatch succeeded.

EXPORT THE PATH OF OPATCH FOLDER

[oracle@trichy32228578]$ export PATH=/u01/app/oracle/product/12.2.0.1/db_1/OPatch:$PATH

[oracle@trichy32228578]$ opatch version

OPatch Version: 12.2.0.1.23

OPatch succeeded.

ONCE OPATCH IS VERIFIED PROCEED START APPLY PATCH 32228578: DATABASE JAN 2021 RELEASE UPDATE 12.2.0.1.23

[oracle@trichy 32228578]$ opatch apply

Oracle Interim Patch Installer version 12.2.0.1.23

Oracle Home : /u01/app/oracle/product/12.2.0.1/db_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/12.2.0.1/db_1/oraInst.loc

OPatch version : 12.2.0.1.23

OUI version : 12.2.0.1.4

Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-20_14-27-38PM_1.log

Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 32228578

Do you want to proceed? [y|n]

User Responded with: Y

All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local

system.

(Oracle Home = ‘/u01/app/oracle/product/12.2.0/db_1_2’)

Is the local system ready for patching? [y|n]

User Responded with: Y

Backing up files…

Applying interim patch ‘32228578’to OH ‘/u01/app/oracle/product/12.2.0/db_1_2’

ApplySession: Optional component(s) [ oracle.swd.oui, 12.2.0.1.0 ] , [ oracle.oid.client,

12.2.0.1.0 ] , [ oracle.has.crs, 12.2.0.1.0 ] , [ oracle.rdbms.drdaas, 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.rdbms.rsf, 12.2.0.1.0…

Patching component oracle.rdbms, 12.2.0.1.0…

Patching component oracle.rdbms.util, 12.2.0.1.0…

Patching component oracle.network.rsf, 12.2.0.1.0…

Patching component oracle.ctx, 12.2.0.1.0…

Patching component oracle.ctx.rsf, 12.2.0.1.0…

Patching component oracle.has.common.cvu, 12.2.0.1.0…

Patching component oracle.ldap.rsf, 12.2.0.1.0…

Patching component oracle.rdbms.dbscripts, 12.2.0.1.0…

Patching component oracle.rdbms.deconfig, 12.2.0.1.0…

Patching component oracle.rdbms.rsf.ic, 12.2.0.1.0…Patching component oracle.sdo, 12.2.0.1.0…

Patching component oracle.sdo.locator, 12.2.0.1.0…

Patching component oracle.tfa, 12.2.0.1.0…

Patching component oracle.xdk.parser.java, 12.2.0.1.0…

Patching component oracle.rdbms.dv, 12.2.0.1.0…

Patching component oracle.precomp.rsf, 12.2.0.1.0…

Patching component oracle.rdbms.crs, 12.2.0.1.0…

Patching component oracle.rdbms.rman, 12.2.0.1.0…

Patching component oracle.rdbms.install.plugins, 12.2.0.1.0…

Patching component oracle.sqlplus, 12.2.0.1.0…

Patching component oracle.ons, 12.2.0.1.0…

Patching component oracle.oraolap, 12.2.0.1.0…

Patching component oracle.xdk.rsf, 12.2.0.1.0…

Patching component oracle.xdk, 12.2.0.1.0…

Patching component oracle.rdbms.lbac, 12.2.0.1.0…

Patching component oracle.sqlplus.ic, 12.2.0.1.0…

Patching component oracle.assistants.server, 12.2.0.1.0…

Patching component oracle.nlsrtl.rsf, 12.2.0.1.0…

Patching component oracle.assistants.deconfig, 12.2.0.1.0…

Patching component oracle.ldap.rsf.ic, 12.2.0.1.0…

Patching component oracle.ldap.client, 12.2.0.1.0…

Patching component oracle.assistants.acf, 12.2.0.1.0…

Patching component oracle.rdbms.oci, 12.2.0.1.0…

Patching component oracle.install.deinstalltool, 12.2.0.1.0…

Patching component oracle.oracore.rsf, 12.2.0.1.0…

Patching component oracle.precomp.lang, 12.2.0.1.0…

Patching component oracle.precomp.common, 12.2.0.1.0…

Patching component oracle.jdk, 1.8.0.91.0…

Patch 32228578successfully applied.

Log file location: /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-20_14-27-38PM_1.log

OPatch succeeded.

CHECK AND VERIFY THE INVENTORY FOR THE LATEST APPLIED PATCH.

IT WILL SHOW YOU THE LATEST APPLIED PATCH

[oracle@trichy32228578]$ opatch lsinventory|grep “Patch description”

Patch description: “Database Jan 2021 Release Update : 12.2.0.1.210119 (32228578)”

GET DETAILED INFO USING OPATCH LSINVENTORY

[oracle@trichy32228578]$ opatch lsinventory

Oracle Interim Patch Installer version 12.2.0.1.23

Oracle Home : /u01/app/oracle/product/12.2.0.1/db_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/12.2.0.1/db_1/oraInst.loc

OPatch version : 12.2.0.1.23

OUI version : 12.2.0.1.4

Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-20_14-49-13PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/lsinv/lsinventory2021-01-20_14-49-13PM.txt

——————————————————————————–

Local Machine Information::

Hostname: localhost

ARU platform id: 226

ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 12c 12.2.0.1.0

There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch 32228578 : applied on Wed Jan 20 14:47:29 IST 2021

Unique Patch ID: 24008225

Patch description: “Database Jan 2021 Release Update : 12.2.0.1.210119 (32228578)”

Created on 9 Jan 2021, 06:42:05 hrs PST8PDT

Bugs fixed:

7391838, 8480838, 8932139, 8975044, 12763598, 13554903, 14221306

14690846, 15931756, 16002385, 16438495, 16727454, 16942578, 17027695

17533661, 17947871, 17958365, 18308268, 18521691, 18594510, 18774543

18878420, 18986501, 19072655, 19211433, 19285025, 19327292, 19526548

19614243, 19647894, 19649997, 19702201, 19721304, 20003668, 20087519

20118035, 20120236, 20324049, 20436508, 20532077, 20549013, 20588486

20591151, 20617383, 20620169, 20736227, 20756305, 20866970, 20917487

Continue

31315876, 31326608, 31331354, 31341859, 31343752, 31347532, 31377487

31393600, 31408636, 31430722, 31501139, 31508450, 31525783, 31544097

31570161, 31600023, 31637680, 31658464, 31668061, 31711889, 31718134

31749740, 31771858, 31786838, 31816631, 31867037, 31905033, 31986836

32089820, 32186646, 32234161, 32296941

——————————————————————————–

OPatch succeeded.

POSTPATCH STEPS (START LISTENER AND DATABASE).

[oracle@trichy32228578]$ lsnrctl start

LSNRCTL for Linux: Version 12.2.0.1.0 – Production on 20-JAN-2021 14:49:27

Starting /u01/app/oracle/product/12.2.0.1/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 12.2.0.1.0 – Production

System parameter file is /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/asa1/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=####)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=####)(PORT=1521)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 12.2.0.1.0 – Production

Start Date 20-JAN-2021 14:49:29

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/Biju1/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=####)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary…

Service “asa1” has 1 instance(s).

Instance “asa1”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

[oracle@trichyOPatch]$ export ORACLE_SID=asa1

[oracle@trichyOPatch]$ ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1

[oracle@trichyOPatch]$ export ORACLE_HOME

[oracle@trichyOPatch]$ PATH=$PATH:$ORACLE_HOME/bin

[oracle@trichyOPatch]$ export PATH

[oracle@trichyOPatch]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 20 14:53:07 2021

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 2483027968 bytes

Fixed Size 8623736 bytes

Variable Size 671091080 bytes

Database Buffers 1795162112 bytes

Redo Buffers 8151040 bytes

Database mounted.

Database opened.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

EXECUTE DATAPATCH FOR UPDATE THE PATCH DETAILS IN DATABASE SIDE.

[oracle@trichyOPatch]$ cd $ORACLE_HOME/OPatch

[oracle@trichyOPatch]$ ./datapatch -verbose

SQL Patching tool version 12.2.0.1.0 Production on Wed Jan 20 14:54:12 2021

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_16800_2021_01_20_14_54_12/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 DBRU:

ID 210119 in the binary registry and ID 200414 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:

32228578 (DATABASE JAN 2021 RELEASE UPDATE 12.2.0.1.210119)

Installing patches…

Patch installation complete. Total patches installed: 1

Validating logfiles…

Patch 32228578 apply: SUCCESS

logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/32228578/24008225/32228578_apply_asa1_2021Jan20_14_54_32.log (no errors)

SQL Patching tool complete on Wed Jan 20 14:55:10 2021

After installing the SQL portion of the patch, some packages could become INVALID. This will get recompiled upon access or you can run utlrp.sql to get them back into a VALID state.

[oracle@trichyOPatch]$ cd $ORACLE_HOME/rdbms/admin

[oracle@trichyadmin]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 20 15:00:48 2021

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> @utlrp.sql

TIMESTAMP

——————————————————————————–

COMP_TIMESTAMP UTLRP_BGN 2021-01-20 15:00:56

DOC> 2. Query showing UTL_RECOMP jobs that are running

DOC> SELECT job_name FROM dba_scheduler_running_jobs

DOC> WHERE job_name like ‘UTL_RECOMP_SLAVE_%’;

DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP

——————————————————————————–

COMP_TIMESTAMP UTLRP_END 2021-01-20 15:00:58

DOC> The following query reports the number of invalid objects.

DOC>

DOC> If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#

OBJECTS WITH ERRORS

——————-

DOC> The following query reports the number of exceptions caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC> Note: Typical compilation errors (due to coding errors) are not

DOC> logged into this table: they go into DBA_ERRORS instead.

DOC>#

ERRORS DURING RECOMPILATION

—————————

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.

SQL> SELECT owner, object_name, object_type FROM all_objects WHERE status =’INVALID’;

no rows selected

CHECK PATCH INFORMATION IN DATABASE LEVEL

SQL> select DESCRIPTION,ACTION,ACTION_TIME,VERSION from dba_registry_sqlpatch;

DESCRIPTION ACTION ACTION_TIME VERSION

————————- –———— ———————–————— ——————

DATABASE APR 2020 RELEASE APPLY 03-JAN-21 03.19.30.031233 PM 12.2.0.1

12.2.0.1.200414

DATABASE JAN 2021 RELEASE APPLY 20-JAN-21 02.55.10.831438 PM 12.2.0.1

UPDATE 12.2.0.1.210119

Biju Muthusamy