Wednesday, February 27, 2019

How to Apply Oracle Latest patch set, OJVM and security patch...etc

When planning for Oracle patches make sure all pre-requisites are met and required backups are in place. Please refer below link to find out the required patches for 11g and 12c.

Oracle 11g and 12c Latest Database or OJVM Patch Set Update or Release Update


1. Before you apply patch make sure you are using latest Opatch version.

$./opatch version
OPatch Version:
OPatch succeeded.

Downloaded the latest opatch for 12.2 version using the link -

To apply latest Opatch follow the instructions
OPatch - Where Can I Find the Latest Version of OPatch(6880880)? [Video] (Doc ID 224346.1)

2. Download the required PSU or JVN or intern patch and unzip

3. Make sure there are no conflicts and run below script to find out the conflicts. If you see any conflicts the make sure you fix the reported conflicts.

$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

In case of any conflicts you will see the message like below

There are no patches that can be applied now.
Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :xxxxxxx, xxxxxx

In case if there are no conflicts then you will see the message like below

Invoking prereq "checkconflictagainstohwithdetail"
Prereq” CheckConflictAgainstOHWithDetail” passed.
Opatch Succeeded.

4. Using the -report option to simulate the application of the interim patch or PSU

Using “-report” option with "opatch apply" to simulate the apply without updating any files. Using this option you no need to shutdown the databases and it can be up and running.

$ opatch apply -report
Skip patching component oracle.rdbms, and its actions.
The actions are reported here, but are not performed.

ApplySession skipping inventory update.

Verifying the update...
Inventory and System verification is performed here.
Patch xxxxxx successfully applied
Log file location: /oracle/product/

OPatch succeeded

5. Run utlrp.sql to recompile any invalid objects

6. Check Oracle Components are VALID or not. Please make sure you fix INVALID component.
If NOT required then you might have to remove INVALID components.

SQL> select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
comp_id,substr(version,1,12) version,status from dba_registry; 
COMP_NAME                                                    COMP_ID              VERSION         STATUS
------------------------------------------------------------ -------------------- --------------- ----------
Oracle Multimedia                                            ORDIM            VALID
Oracle XML Database                                          XDB              VALID
Oracle Expression Filter                                     EXF              VALID
Oracle Rules Manager                                         RUL              VALID
Oracle Workspace Manager                                     OWM              VALID
Oracle Database Catalog Views                                CATALOG          VALID
Oracle Database Packages and T                               CATPROC          VALID
JServer JAVA Virtual Machine                                 JAVAVM           VALID
Oracle XDK                                                   XML              VALID
Oracle Database Java Packages                                CATJAVA          VALID

7. Make sure you have taken TAR of the ORACLE_HOME, in case of any issues

tar -cvf oracle_home_12c.tar

Applying interim patch or PSU or JVM

1. Stop the databases and listener

Sqlplus / as sysdba
Shutdown immediate
$lsnrctl stop LISTENER

2. Make sure you are in the patch directory and run the below command to apply the patch

$cd <patch number>
$ORACLE_HOME/OPatch/opatch apply

3. Run post patch script against each database

Sqlplus /as sysdba
@catbundle.sql psu apply

Starting version 12.1 we need to use datapatch to complete post patch actions upon any 12c or later database restart. The datapatch contains the logic to identify if any post-patch SQL actions are pending.

Sqlplus / as sysdba
Startup upgrade

$ cd $ORACLE_HOME/Opatch
./datapatch -verbose

Output additional information used for debugging

After datapatch restart the database.
Sqlplus / as sysdba
Shutdown immediate

1. After patching check the inventory, to make sure patch has been updated or not
$ORACLE_HOME/OPatch/opatch lsinventory

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

Oracle Home : /oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /oracle/product/11.2.0/db_1/oraInst.loc
OPatch version :
OUI version :

Log file location : /oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2019-05-02_09-41-09AM_1.log
Lsinventory Output file location : /oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2019-05-02_09-41-09AM.txt
OPatch succeeded.

2. Start the database and Listener, in case if they are still down

Sqlplus / as sysdba

$lsnrctl start LISTENER

3. Run “utlrp.sql” to re-compile all invalid object


4. Verify the patch history using from dba_registry_history using below query

SQL>select action_time,action from dba_registry_history;


-------------------------------------------------- ------------------------------

5. Please make sure all Oracle COMPONENTS are VALID

select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
comp_id,substr(version,1,12) version,status from dba_registry;

Satishbabu Gunukula

Monday, February 18, 2019

Webinar: Data Guard Physical Standby Setup in Oracle?

This Webinar helps you to understand the benefits of Oracle Data Guard, available methods and how to setup and common issues.

Date and time: Feb 28th 2019 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.
· Oracle Data Guard and its Benefits
· Overview of different Data Guard Options
· Difference between RAC vs DG
· Prerequisites
· DataGuard Demonstration
· Common Issues
· Post verification Steps
· References
· Contact Info
· Q&A

To register for this Webinar, please send an email to

Note that registrations are limited and first come and first serve basis.
You will receive an email confirmation with meeting session link.

For presentation link Click here

Satishbabu Gunukula, Oracle ACE