How to find patches applied on Oracle database ?

In my previous articles I have given the query to find the size of oracle database in multiple ways. There are so many questions still in mind about the oracle patches. Sometimes users are facing the issue in applying the patches on Oracle database and sometimes user will have to find patches applied on Oracle database. In this article I would like to give how to find patches applied on Oracle database with multiple queries or commands.

Multiple ways to find patches applied on Oracle database :

In this section I would like to give the ways to find patches applied on Oracle database with multiple queries or commands. Now a days in latest version of oracle the patches application will be automated but if you are using previous versions you require to find out the patches already applied.

Finding patches applied on Oracle 11 G version :

1. Opatch Utility :

Oracle Patch download and installation is the basic task for every database administrator. DBA can use Oracle opatch utility to know all the Oracle Patch applied to the database. It will also show you the list of all the bug fixed during patching.

User can use the Opatch Utility to find out the patches already applied.

Command used :

cd $ORACLE_HOME/OPatch

opatch lsinventory

Find Patch description on Linux :

$ORACLE_HOME/OPatch/opatch lsinventory|grep “Patch description”

2. Using History table

User can also find out the patches applied using history table.

select * from sys.registry$history;

3. Check the patch details from SQL Plus tool :

SQL plus is mostly used tool for SQL development. Kindly check the following query useful to find out details of the patch on SQL PLUS tool.

SELECT TO_CHAR(action_time, ‘DD-MON-YYYY HH24:MI:SS’) AS Timeofaction, action, version, id, comments, bundle_series FROM sys.registry$history ORDER by action_time;

4.Is_patch_installed procedure

You can use the is_patch_installed procedure for finding out the patch information.

select xmltransform(dbms_qopatch.is_patch_installed(‘21023030’),dbms_qopatch.get_opatch_xslt) “Patch installed?” from dual;

User can check the patch number is installed or not with the procedure.

5.Check all applied patches

User can check all applied patches with the SQL.

 set serverout on;
 exec dbms_qopatch.get_sqlpatch_status;

Finding patches applied 12 C version :

1. Same using OPatch version

cd $ORACLE_HOME/OPatch

opatch lsinventory

2. using dba_registry_sqlpatch view:

SQL> select * from dba_registry_sqlpatch;

3. using package dbms_qopatch

SQL> set serverout on

SQL> exec dbms_qopatch.get_sqlpatch_status;

4.Finding out the details of Patch using SQL PLUS

select patch_id, version, status, Action,Action_time from dba_registry_sqlpatch order by action_time;

These are above ways to find out the applied patches on Oracle. With using above queries user can find out the details of the patch.