NoCOUG Journal Column – February 2016 Issue

In the February 2016 issue of the NoCOUG Journal, for the Many Things Oracle column, I discuss some of the v$ views that are lesser known or not widely used. I also touch up on the MGMTDB cluster repository database in Oracle Clusterware 12c.

Read here…

This is a Collector’s Edition, and includes the several good articles from:

  • Shyam Varan Nath – The Internet of “Things”
  • Brian Hitchcock – “Oracle Database 12c New Features” book review
  • Iggy Fernandez – The rise and fall of the NoSQL Empire
  • Lothar Flatz – Raiders of the Data Dictionary III: The Quest for the Missing Index

This is sixth installment of Many Things Oracle column in NoCOUG journal. The past articles are:

NoCOUG Journal 2015 NOV: Oracle Database 12c New Processes and SQL Developer [Magazine]

NoCOUG Journal 2015 AUG: Trace File Analyzer [Magazine]

NoCOUG Journal 2015 MAY: Listener IFILE, LREG process [Magazine]

NoCOUG Journal 2015 FEB: Oracle Database 12c new parameters [Magazine]

NoCOUG Journal 2014 NOV: Miscellaneous 12c new features [Magazine]

Thank you NoCOUG for the opportunity…

Apply DB OVJM PSU + Queryable Patch Inventory Demo

Oracle started releasing OVJM component of  DB PSU in October 2014, and recommend applying OVJM PSU along with (or after) DB PSU. I will demo applying DB PSU, and OVJM PSU on 12.1.0.2 database. Use this opportunity to introduce you to Queryable Patch Inventory (DBMS_QOPATCH), where you can query OPatch information directly from the database.

Download the combo patch from MOS.

Patch 21520444:COMBO OF OJVM COMPONENT 12.1.0.2.5 DB PSU + DB PSU 12.1.0.2.5 (OCT2015)

The 21520444 patch in fact contains two patches: 21359755 (DB PSU) and 21555660 (OVJM PSU).

Download and unzip the patch combo.

[oracle@localhost 21520444]$ pwd
/shared/oraclesw/21520444
[oracle@localhost 21520444]$ ls -l
total 24
drwxrwx---. 1 root vboxsf   272 Oct 26 10:38 21359755
drwxrwx---. 1 root vboxsf   238 Oct 26 10:38 21555660
-rwxrwx---. 1 root vboxsf 18312 Oct 26 10:41 README.html
-rwxrwx---. 1 root vboxsf    25 Oct 26 10:38 README.txt
[oracle@localhost 21520444]$

Download the latest OPatch, and copy to Oracle Home.

-- Current version of OPatch
$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.1.0.1.3
OPatch succeeded.
-- Unzip OPatch zip file to Oracle Home
$ cd $ORACLE_HOME
[oracle@localhost 12.1.0.2]$ mv OPatch OPatch.old
[oracle@localhost 12.1.0.2]$ unzip /media/sf_Shared/oraclesw/p6880880_121010_LINUX.zip

-- New version
$ ./OPatch/opatch version
OPatch Version: 12.1.0.1.10
OPatch succeeded.

Use DBMS_QOPATCH to check which patches are installed. First let’s find the Oracle Home location and the inventory location.

SQL> select xmltransform(dbms_qopatch.get_opatch_install_info, dbms_qopatch.get_opatch_xslt) x from dual;

X
----------------------------------------------------------------
Oracle Home	  : /home/oracle/app/oracle/db/12.1.0.2
Inventory	  : /home/oracle/app/oraInventory

SQL>

dbms_qopatch.get_opatch_xslt is the style sheet. The documentation says “This function returns the style-sheet for the opatch XML inventory presentation. You can use the return type of this subprogram to perform XMLTRANSFORM and the transformed result has the same appearance as opatch text output.”

Example below shows all the products and patches installed in the database.

SQL> select xmltransform(dbms_qopatch.get_opatch_lsinventory, dbms_qopatch.get_opatch_xslt) X from dual;

X
--------------------------------------------------------------------------------
Oracle Querayable Patch Interface 1.0
--------------------------------------------------------------------------------
Oracle Home	  : /home/oracle/app/oracle/db/12.1.0.2
Inventory	  : /home/oracle/app/oraInventory
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 12c				       12.1.0.2.0
Installed Products ( 135)

Oracle Database 12c					    12.1.0.2.0
Java Development Kit					    1.6.0.75.0
oracle.swd.oui.core.min 				    12.1.0.2.0
Installer SDK Component 				    12.1.0.2.0
Oracle One-Off Patch Installer				    12.1.0.1.2
Oracle Universal Installer				    12.1.0.2.0
Oracle USM Deconfiguration				    12.1.0.2.0
Oracle Configuration Manager Deconfiguration		    10.3.1.0.0
Oracle RAC Deconfiguration				    12.1.0.2.0
Oracle DBCA Deconfiguration				    12.1.0.2.0
Oracle Database Plugin for Oracle Virtual Assembly Builder  12.1.0.2.0
Oracle Configuration Manager Client			    10.3.2.1.0
Oracle Configuration Manager				    10.3.8.1.1
Oracle ODBC Driverfor Instant Client			    12.1.0.2.0
LDAP Required Support Files				    12.1.0.2.0
Expat libraries 					    2.0.1.0.2
Oracle Bali Share					    11.1.1.6.0
SSL Required Support Files for InstantClient		    12.1.0.2.0
Oracle Net Required Support Files			    12.1.0.2.0
Oracle R Enterprise Server Files			    12.1.0.2.0
Oracle Real Application Testing 			    12.1.0.2.0
Oracle Label Security					    12.1.0.2.0
Oracle Data Mining RDBMS Files				    12.1.0.2.0
Oracle OLAP RDBMS Files 				    12.1.0.2.0
Oracle OLAP API 					    12.1.0.2.0
SQL*Plus Required Support Files 			    12.1.0.2.0
Oracle Database Vault option				    12.1.0.2.0
XDK Required Support Files				    12.1.0.2.0
Oracle Notification Service for Instant Client		    12.1.0.2.0
Platform Required Support Files 			    12.1.0.2.0
Oracle RAC Required Support Files-HAS			    12.1.0.2.0
Oracle JDBC Server Support Package			    12.1.0.2.0
Oracle SQL Developer					    12.1.0.2.0
Oracle Application Express				    12.1.0.2.0
RDBMS Required Support Files Runtime			    12.1.0.2.0
RDBMS Required Support Files for Instant Client 	    12.1.0.2.0
SQLJ Runtime						    12.1.0.2.0
Database Workspace Manager				    12.1.0.2.0
Precompiler Common Files for Core			    12.1.0.2.0
Parser Generator Required Support Files 		    12.1.0.2.0
Oracle Globalization Support				    12.1.0.2.0
Oracle Notification Service (eONS)			    12.1.0.2.0
Oracle Context Companion				    12.1.0.2.0
Oracle Text ATG Language Support Files			    12.1.0.2.0
Oracle Text for Core					    12.1.0.2.0
Oracle Text Required Support Files			    12.1.0.2.0
Precompiler Required Support Files			    12.1.0.2.0
Hadoopcore Component					    12.1.0.2.0
Oracle Database 12c Multimedia Files			    12.1.0.2.0
Oracle Multimedia Java Advanced Imaging 		    12.1.0.2.0
Oracle JDBC/OCI Instant Client				    12.1.0.2.0
Oracle Multimedia Locator Java Required Support Files	    12.1.0.2.0
Oracle Multimedia Locator RDBMS Files			    12.1.0.2.0
Oracle Core Required Support Files for Core DB		    12.1.0.2.0
Oracle Core Required Support Files			    12.1.0.2.0
Sample Schema Data					    12.1.0.2.0
Oracle Starter Database 				    12.1.0.2.0
Oracle Message Gateway Common Files			    12.1.0.2.0
Oracle XML Query					    12.1.0.2.0
Oracle Core XML Development Kit 			    12.1.0.2.0
XML Parser for Oracle JVM				    12.1.0.2.0
Oracle JFC Extended Windowing Toolkit			    11.1.1.6.0
Oracle Globalization Support For Core			    12.1.0.2.0
DB TOOLS Listener					    12.1.0.2.0
Tracle File Analyzer					    12.1.0.2.0
Oracle Database Deconfiguration 			    12.1.0.2.0
Oracle Quality of Service Management (Client)		    12.1.0.2.0
Oracle Locale Builder					    12.1.0.2.0
JAccelerator (COMPANION)				    12.1.0.2.0
Oracle Containers for Java				    12.1.0.2.0
Oracle JVM For Core					    12.1.0.2.0
Perl Modules						    5.14.1.0.0
RDBMS Required Support Files				    12.1.0.2.0
Secure Socket Layer					    12.1.0.2.0
Oracle Universal Connection Pool			    12.1.0.2.0
Oracle JDBC/THIN Interfaces				    12.1.0.2.0
Oracle Multimedia Client Option 			    12.1.0.2.0
Oracle Java Client					    12.1.0.2.0
Database Migration Assistant for Unicode		    12.1.0.2.0
Perl Interpreter					    5.14.1.0.0
PL/SQL Embedded Gateway 				    12.1.0.2.0
OLAP SQL Scripts					    12.1.0.2.0
Database SQL Scripts					    12.1.0.2.0
Oracle Globalization Support				    12.1.0.2.0
Oracle Extended Windowing Toolkit			    11.1.1.6.0
SQL*Plus Files for Instant Client			    12.1.0.2.0
Required Support Files					    12.1.0.2.0
Oracle ODBC Driver					    12.1.0.2.0
Precompilers						    12.1.0.2.0
Precompiler Common Files				    12.1.0.2.0
Deinstallation Tool					    12.1.0.2.0
Oracle Recovery Manager 				    12.1.0.2.0
Oracle Net Java Required Support Files			    12.1.0.2.0
XML Parser for Java					    12.1.0.2.0
Oracle Security Developer Tools 			    12.1.0.2.0
Oracle Wallet Manager					    12.1.0.2.0
Cluster Verification Utility Common Files		    12.1.0.2.0
Oracle Clusterware RDBMS Files				    12.1.0.2.0
Oracle Ice Browser					    11.1.1.7.0
Installation Plugin Files				    12.1.0.2.0
Installation Common Files				    12.1.0.2.0
Oracle LDAP administration				    12.1.0.2.0
Buildtools Common Files 				    12.1.0.2.0
Oracle Java Layout Engine				    11.0.0.0.0
Oracle Database User Interface				    11.0.0.0.0
Oracle Help Share Library				    11.1.1.7.0
Oracle Help for Java					    11.1.1.7.0
HAS Common Files					    12.1.0.2.0
PL/SQL							    12.1.0.2.0
Assistant Common Files					    12.1.0.2.0
Oracle Notification Service				    12.1.0.2.0
Oracle Net						    12.1.0.2.0
Oracle Database Utilities				    12.1.0.2.0
Generic Connectivity Common Files			    12.1.0.2.0
Oracle Database Gateway for ODBC			    12.1.0.2.0
Oracle Programmer					    12.1.0.2.0
Oracle Netca Client					    12.1.0.2.0
SQL*Plus						    12.1.0.2.0
Oracle Multimedia Locator				    12.1.0.2.0
Oracle Multimedia					    12.1.0.2.0
Oracle Call Interface (OCI)				    12.1.0.2.0
Oracle XML Development Kit				    12.1.0.2.0
Database Configuration and Upgrade Assistants		    12.1.0.2.0
Oracle JVM						    12.1.0.2.0
Oracle Advanced Security				    12.1.0.2.0
Oracle Internet Directory Client			    12.1.0.2.0
Oracle Net Listener					    12.1.0.2.0
HAS Files for DB					    12.1.0.2.0
Oracle Text						    12.1.0.2.0
Oracle Net Services					    12.1.0.2.0
Oracle Database 12c					    12.1.0.2.0
Oracle OLAP						    12.1.0.2.0
Oracle Spatial and Graph				    12.1.0.2.0
Oracle Partitioning					    12.1.0.2.0
Enterprise Edition Options				    12.1.0.2.0

Interim patches:

SQL> 

Nothing under “Interim Patches”, so nothing installed. Now, apply PSU.

Shutdown database and listener before applying DB PSU (21359755).

Go to the patch download directory, and apply PSU using opatch.

[oracle@localhost 21359755]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.1.0.1.10
Copyright (c) 2015, Oracle Corporation.  All rights reserved.

Oracle Home       : /home/oracle/app/oracle/db/12.1.0.2
Central Inventory : /home/oracle/app/oraInventory
   from           : /home/oracle/app/oracle/db/12.1.0.2/oraInst.loc
OPatch version    : 12.1.0.1.10
OUI version       : 12.1.0.2.0
Log file location : /home/oracle/app/oracle/db/12.1.0.2/cfgtoollogs/opatch/opatch2015-11-29_19-45-39PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   19769480  20299023  20831110  21359755  

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name: 

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/home/oracle/app/oracle/db/12.1.0.2')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying sub-patch '19769480' to OH '/home/oracle/app/oracle/db/12.1.0.2'

Patching component oracle.rdbms.deconfig, 12.1.0.2.0...
Patching component oracle.xdk, 12.1.0.2.0...
Patching component oracle.tfa, 12.1.0.2.0...
Patching component oracle.rdbms.util, 12.1.0.2.0...
Patching component oracle.rdbms, 12.1.0.2.0...
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0...
Patching component oracle.xdk.parser.java, 12.1.0.2.0...
Patching component oracle.oraolap, 12.1.0.2.0...
Patching component oracle.xdk.rsf, 12.1.0.2.0...
Patching component oracle.rdbms.rsf, 12.1.0.2.0...
Patching component oracle.rdbms.rman, 12.1.0.2.0...
Patching component oracle.ldap.rsf, 12.1.0.2.0...
Patching component oracle.ldap.rsf.ic, 12.1.0.2.0...
Applying sub-patch '20299023' to OH '/home/oracle/app/oracle/db/12.1.0.2'
ApplySession: Optional component(s) [ oracle.has.crs, 12.1.0.2.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.tfa, 12.1.0.2.0...
Patching component oracle.rdbms.deconfig, 12.1.0.2.0...
Patching component oracle.rdbms.rsf, 12.1.0.2.0...
Patching component oracle.rdbms, 12.1.0.2.0...
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0...
Patching component oracle.rdbms.rsf.ic, 12.1.0.2.0...
Patching component oracle.ldap.rsf, 12.1.0.2.0...
Patching component oracle.ldap.rsf.ic, 12.1.0.2.0...
Applying sub-patch '20831110' to OH '/home/oracle/app/oracle/db/12.1.0.2'

Patching component oracle.rdbms, 12.1.0.2.0...
Patching component oracle.oraolap.dbscripts, 12.1.0.2.0...
Patching component oracle.ldap.rsf, 12.1.0.2.0...
Patching component oracle.tfa, 12.1.0.2.0...
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0...
Patching component oracle.rdbms.rsf, 12.1.0.2.0...
Applying sub-patch '21359755' to OH '/home/oracle/app/oracle/db/12.1.0.2'

Patching component oracle.assistants.server, 12.1.0.2.0...
Patching component oracle.rdbms.rsf, 12.1.0.2.0...
Patching component oracle.rdbms, 12.1.0.2.0...
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0...
Patching component oracle.tfa, 12.1.0.2.0...
Composite patch 21359755 successfully applied.
Log file location: /home/oracle/app/oracle/db/12.1.0.2/cfgtoollogs/opatch/opatch2015-11-29_19-45-39PM_1.log

OPatch succeeded.
[oracle@localhost 21359755]$ 

If you had any other patches applied before the PSU, you would need to check for conflicts. Let’s check if OVJM PSU (21555660) has any conflict. Change working directory to OVJM patch directory.

[oracle@localhost 21555660]$ pwd
/shared/oraclesw/21520444/21555660
[oracle@localhost 21555660]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgain-stOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.1.0.1.10
Copyright (c) 2015, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /home/oracle/app/oracle/db/12.1.0.2
Central Inventory : /home/oracle/app/oraInventory
   from           : /home/oracle/app/oracle/db/12.1.0.2/oraInst.loc
OPatch version    : 12.1.0.1.10
OUI version       : 12.1.0.2.0
Log file location : /home/oracle/app/oracle/db/12.1.0.2/cfgtoollogs/opatch/opatch2015-11-29_21-24-57PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
[oracle@localhost 21555660]$ 

Now, apply the OVJM PSU.

[oracle@localhost 21555660]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.1.0.1.10
Copyright (c) 2015, Oracle Corporation.  All rights reserved.

Oracle Home       : /home/oracle/app/oracle/db/12.1.0.2
Central Inventory : /home/oracle/app/oraInventory
   from           : /home/oracle/app/oracle/db/12.1.0.2/oraInst.loc
OPatch version    : 12.1.0.1.10
OUI version       : 12.1.0.2.0
Log file location : /home/oracle/app/oracle/db/12.1.0.2/cfgtoollogs/opatch/21555660_Nov_29_2015_21_26_03/apply2015-11-29_21-26-03PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   21555660  

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name: 

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/home/oracle/app/oracle/db/12.1.0.2')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '21555660' to OH '/home/oracle/app/oracle/db/12.1.0.2'
ApplySession: Optional component(s) [ oracle.sqlj, 12.1.0.2.0 ]  not present in the Oracle Home or a higher version is found.
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0...
Patching component oracle.javavm.client, 12.1.0.2.0...
Patching component oracle.rdbms, 12.1.0.2.0...
Patching component oracle.javavm.server.core, 12.1.0.2.0...
Patching component oracle.javavm.server, 12.1.0.2.0...
--------------------------------------------------------------------------------
***************************************************************************************************
***************************************************************************************************
**                                ATTENTION                                                      **
**                                                                                               **
** It is critical and essential to complete the post installation (if you applying the patch) or **
** post deinstallation (if you are doing rollback of the patch) as per Readme to have a working  **
** and functional Oracle JavaVM Component.                                                       **
** Failure to follow the instructions could lead to inconsistencies in the database.             **
**                                                                                               **
***************************************************************************************************
***************************************************************************************************

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

Patch 21555660 successfully applied.
Log file location: /home/oracle/app/oracle/db/12.1.0.2/cfgtoollogs/opatch/21555660_Nov_29_2015_21_26_03/apply2015-11-29_21-26-03PM_1.log

OPatch succeeded.
[oracle@localhost 21555660]$ 

If the OVJM patch is not applied, you can do normal STARTUP. For OVJM, need to start the database with STARTUP UPGRADE.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size		    2929936 bytes
Variable Size		  570428144 bytes
Database Buffers	  260046848 bytes
Redo Buffers		    5455872 bytes
Database mounted.
Database opened.
SQL> 
SQL> alter pluggable database all open upgrade;

Pluggable database altered.

SQL> 

Then run the datapatch script. It will automatically run the post patch SQL scripts for both patches, and take care of all OPEN pluggable databases.

[oracle@localhost OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 on Sun Nov 29 21:59:47 2015
Copyright (c) 2015, Oracle.  All rights reserved.

Log file for this invocation: /home/oracle/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_26796_2015_11_29_21_59_47/sqlpatch_invocation.log

Connecting to database...OK
Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Patch 21555660 (Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)):
  Installed in the binary registry only
Bundle series PSU:
  ID 5 in the binary registry and not installed in any PDB

Adding patches to installation queue and performing prereq checks...
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED PDB1 PDB2
    Nothing to roll back
    The following patches will be applied:
      21555660 (Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015))
      21359755 (Database Patch Set Update : 12.1.0.2.5 (21359755))

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

Validating logfiles...
Patch 21555660 apply (pdb CDB$ROOT): SUCCESS
  logfile: /home/oracle/app/oracle/cfgtoollogs/sqlpatch/21555660/19361790/21555660_apply_CDB12C_CDBROOT_2015Nov29_22_00_20.log (no errors)
Patch 21359755 apply (pdb CDB$ROOT): SUCCESS
  logfile: /home/oracle/app/oracle/cfgtoollogs/sqlpatch/21359755/19194568/21359755_apply_CDB12C_CDBROOT_2015Nov29_22_00_56.log (no errors)
Patch 21555660 apply (pdb PDB$SEED): SUCCESS
  logfile: /home/oracle/app/oracle/cfgtoollogs/sqlpatch/21555660/19361790/21555660_apply_CDB12C_PDBSEED_2015Nov29_22_01_04.log (no errors)
Patch 21359755 apply (pdb PDB$SEED): SUCCESS
  logfile: /home/oracle/app/oracle/cfgtoollogs/sqlpatch/21359755/19194568/21359755_apply_CDB12C_PDBSEED_2015Nov29_22_01_51.log (no errors)
Patch 21555660 apply (pdb PDB1): SUCCESS
  logfile: /home/oracle/app/oracle/cfgtoollogs/sqlpatch/21555660/19361790/21555660_apply_CDB12C_PDB1_2015Nov29_22_01_04.log (no errors)
Patch 21359755 apply (pdb PDB1): SUCCESS
  logfile: /home/oracle/app/oracle/cfgtoollogs/sqlpatch/21359755/19194568/21359755_apply_CDB12C_PDB1_2015Nov29_22_01_51.log (no errors)
Patch 21555660 apply (pdb PDB2): SUCCESS
  logfile: /home/oracle/app/oracle/cfgtoollogs/sqlpatch/21555660/19361790/21555660_apply_CDB12C_PDB2_2015Nov29_22_01_04.log (no errors)
Patch 21359755 apply (pdb PDB2): SUCCESS
  logfile: /home/oracle/app/oracle/cfgtoollogs/sqlpatch/21359755/19194568/21359755_apply_CDB12C_PDB2_2015Nov29_22_01_51.log (no errors)
SQL Patching tool complete on Sun Nov 29 22:02:01 2015
[oracle@localhost OPatch]$ 

Restart the database in normal mode.

Now, let’s do some DBMS_QOPATCH queries.

Following shows the patches installed. Notice that along with patches, it also shows the files updated. You also get the names of the patch apply and patch rollback scripts.

SQL> set pages 9999
SQL> set long 32000
SQL> select xmltransform(dbms_qopatch.get_opatch_list, dbms_qopatch.get_opatch_xslt) X from dual;

X
--------------------------------------------------------------------------------
Patch Details:

Patch(sqlpatch) 21555660:   applied on 2015-11-29T21:26:24-08:00
Unique Patch ID: 19361790
  Patch Description: Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)
  Created on	 : 18 Sep 2015, 02:55:45 hrs PST8PDT
  Bugs fixed:
	 21047803  19699946  19245191  19223010  21047766  20408829  19231857  2
1566944  21566993  21068507  19176885  20408866  19877336  19895326  21811517  1
9153980  20415564  19909862  21555660  19855285  19895362  19623450
  Files Touched:

    21555660_apply.sql
    21555660_rollback.sql
    jvmpsu.sql
    jvmpsupdii.sql
    jvmpsupdi.sql
    jvmpsui.sql
    jvmpsupii.sql
    jvmpsupi.sql
    aurora.zip
    ins_rdbms.mk
    libjavavm12.a
    libjavavm12.a
    jce.jar
    jce.jar
    cacerts
    cacerts
    sunjce_provider.jar
    sunjce_provider.jar
    classes.bin
    classes.bin
    jvmpsupii.sql
    jvmpsupi.sql
    jvmpsu.sql
    jvmpsupdii.sql
    jvmpsupdi.sql
    jvmpsui.sql

Patch(sqlpatch) 21359755:   applied on 2015-11-29T19:46:36-08:00
Unique Patch ID: 19194568
  Patch Description: Database Patch Set Update : 12.1.0.2.5 (21359755)
  Created on	 : 21 Oct 2015, 02:52:58 hrs PST8PDT
  Bugs fixed:
	 19243521  20048359  19571367  20397490  20476175  20925795  21442094  2
0245930  20134339  21281532  21421886  20413820  18966843  20441797  20281121  2
1620471  19791273  20361671  18411216  19587324  19032777  19468991  17365043  2
2062026  20471920  18743542  20688221  20122715
  Files Touched:

    /oracle/sysman/assistants/dbca/dbcx/XMLHost.class
    sjsex.o
    ins_rdbms.mk
    ins_rdbms.mk
    /kcb.o
    /kcbz.o
    /kcl.o
    /kjfm.o
    /kjgcr.o
    /kxes.o
    /kql.o
    /kokt.o
    /kwqmn.o
    /kpdba.o
    /rpi.o
    /kfd.o
    /kfdva.o
    /kff.o
    /krbc.o
    /kfns.o
    /ktsk.o
    /kfvx.o
    /kdc.o
    /kdt.o
    /kcrfw.o
    /krb.o
    /krbm.o
    /knlogc.o
    /kzam.o
    /kzan.o
    /ktsp.o
    /ktsplb.o
    /ktspsrch.o
    /qksbg.o
    /kspt.o
    /qmixo.o
    bundledata_PSU.xml
    ins_rdbms.mk
    21359755_apply.sql
    21359755_rollback.sql
    bug20876308_rollback.sql
    prvtpckl.plb
    bug19532017_downgrade.sql
    prvtlmd.plb
    prvtpexei.plb
    prvtilm.plb
    utluppkg.sql
    bug19587324.sql
    bug18909599.sql
    bug19587324.sql
    prvtsqlpatch.plb
    prvtqopi.plb
    sqlpatch.pm
    prvtpexei.plb
    prvtlmd.plb
    prvtilm.plb
    tfa_setup

Patch(sqlpatch) 20831110:   applied on 2015-11-29T19:46:32-08:00
Unique Patch ID: 18977826
  Patch Description: Database Patch Set Update : 12.1.0.2.4 (20831110)
  Created on	 : 11 Jul 2015, 00:45:40 hrs PST8PDT
  Bugs fixed:
	 20424899  19307662  20746251  20919320  20331945  20657441  20387265  2
0294666  19703301  20165574  20899461  20558005  19399918  20402832  21225209  1
9284031  20474192  19699191  20093776  19989009
  Files Touched:

    /kcbk.o
    /kcl.o
    /kxs.o
    /kks1.o
    /rfrdb.o
    /rfrxpt.o
    /koklv.o
    /kokm.o
    /krsd.o
    /kzaf.o
    /ktslj.o
    /qksvc.o
    /prsc.o
    /kzia.o
    /ksk.o
    asmcmdshare.pm
    asmcmdug.pm
    bundledata_PSU.xml
    ins_rdbms.mk
    ins_rdbms.mk
    bug20876308_apply.sql
    bug20876308_rollback.sql
    bug20876308_rollback.sql
    prvtpckl.plb
    utluppkg.sql
    bug19532017_downgrade.sql
    bug18909599.sql
    20831110_apply.sql
    20831110_rollback.sql
    xoqclnup.sql
    xoqu112.sql
    bug20558005.sql
    libccme_base_non_fips.so
    libccme_ecc_accel_non_fips.so
    libccme_ecc_non_fips.so
    tfa_setup
    catfga.sql
    catschv.sql
    catctl.pl
    ins_net_client.mk

Patch(sqlpatch) 20299023:   applied on 2015-11-29T19:46:29-08:00
Unique Patch ID: 18703022
  Patch Description: Database Patch Set Update : 12.1.0.2.3 (20299023)
  Created on	 : 16 Mar 2015, 22:21:54 hrs PST8PDT
  Bugs fixed:
	 20440930  18306996  19865345  19536415  18607546  19487147  19035573  1
9873610  19081128  19606174  19272708  17274537  19358317  19291380  19012119  1
9687159  19547370  20348653  20425790  19524384  19385656  19928926  18681056  1
9512341  18856999  16619249  19018447  19393542  20235511  19627012  19649152  1
9670108  19684504  18417036  20347562  19658708  19896336  19805359  19637186  1
8940497  19183343  19978542  19644859  18202441  19023822  19791377  18909599  1
9335438  19238590  19841800  19619732  19315691  19730508  19597583  19065677  1
9915271
  Files Touched:

    tfa_setup
    ins_rdbms.mk
    /kgfm.o
    /kgfm.o
    /dbgrup.o
    /dbgrupt.o
    /dbgtc.o
    /dbgtfd.o
    /dbgtfm.o
    /qcs.o
    /qmx0.o
    ins_net_client.mk
    ins_rdbms.mk
    /kcbz.o
    /kkdl.o
    /kkm2.o
    /kxs.o
    /kql.o
    /kqlb.o
    /kks1.o
    /kkae.o
    /kkp.o
    /rfmrole.o
    /rfrdb.o
    /jskr.o
    /kwra.o
    /kpdb.o
    /kpdba.o
    /kpdbc.o
    /kpdbe.o
    /kpdbf.o
    /kpdbicd.o
    /kpdbutl.o
    /kpdbxmlt.o
    /kpbf.o
    /opiexe.o
    /opiosq.o
    /kf.o
    /kfd.o
    /kfdp.o
    /kfg.o
    /kdbl.o
    /kdblc.o
    /kdt.o
    /kdu.o
    /kcv.o
    /knanr.o
    /knl.o
    /kzft.o
    /kzrt.o
    /kkfd.o
    /kxfp.o
    /kxfpt.o
    /kxfr.o
    /kxft.o
    /kxfx.o
    /qerpx.o
    /qerwn.o
    /qkadrv.o
    /qkexr.o
    /qksop.o
    /qkswc.o
    /dbsmig.o
    /qsmqutl.o
    /ktur.o
    asmcmdsys.pm
    bundledata_PSU.xml
    ins_rdbms.mk
    ins_rdbms.mk
    ins_rdbms.mk
    bug18909599.sql
    bug19532017_downgrade.sql
    bug19532017_upgrade.sql
    dbmssqlpatch.sql
    prvtsqlpatch.plb
    prvtqopi.plb
    sqlpatch_bootstrap.sql
    sqlpatch.pm
    sqlpatch.pl
    prvtpckl.plb
    bug19532017_downgrade.sql
    utluppkg.sql
    bug18909599.sql
    20299023_apply.sql
    20299023_rollback.sql
    c1201000.sql
    catadvtb.sql
    catdpb.sql
    catmetx.sql
    catsqlreg.sql
    catupstr.sql
    e1201000.sql
    utluppkg.sql
    prvtbsat.plb
    prvtdrep.plb
    ins_rdbms.mk
    libnnz12.a
    libnnzst12.a
    libnnz12.so

Patch(sqlpatch) 19769480:   applied on 2015-11-29T19:46:22-08:00
Unique Patch ID: 18350083
  Patch Description: Database Patch Set Update : 12.1.0.2.2 (19769480)
  Created on	 : 15 Dec 2014, 06:54:52 hrs PST8PDT
  Bugs fixed:
	 20284155  19157754  18885870  19303936  19708632  19371175  18618122  1
9329654  19075256  19074147  19044962  19289642  19068610  18988834  19028800  1
9561643  19058490  19390567  18967382  19174942  19174521  19176223  19501299  1
9178851  18948177  18674047  19723336  19189525  19001390  19176326  19280225  1
9143550  18250893  19180770  19155797  19016730  19185876  18354830  19067244  1
8845653  18849537  18964978  19065556  19440586  19439759  19024808  18952989  1
8990693  19052488  19189317  19409212  19124589  19154375  19279273  19468347  1
9054077  19048007  19248799  19018206  18921743  14643995  18456643  16870214  1
9434529  19706965  17835294  20074391  18791688  19197175  19134173  19174430  1
9050649  19769480  19077215  19577410  18288842  18436647  19520602  19149990  1
9076343  19195895  18610915  19068970  19518079  19304354  19001359  19676905  1
9309466  19382851  18964939  16359751  19022470  19532017  19597439  18674024  1
9430401
  Files Touched:

    ins_rdbms.mk
    xmlpatch
    xmldiff
    xsl
    xmlcg
    xvm
    orachk.zip
    tfa_setup
    diaus.msg
    /kcb.o
    /kcbb.o
    /kcbw.o
    /kcbz.o
    /kcl.o
    /kjxgm.o
    /kjfc.o
    /dbkifw.o
    /kkm.o
    /qcis.o
    /kql.o
    /rfmon.o
    /krvg.o
    /krvt.o
    /kpdb.o
    /kpdba.o
    /kpdbc.o
    /kpdbcv.o
    /kpdbd.o
    /kpdbe.o
    /kpdbf.o
    /kpdbicd.o
    /kpdbs.o
    /kpdbutl.o
    /kpodp.o
    /kpolon.o
    /kfd.o
    /kfdp.o
    /kfds.o
    /kff.o
    /kffm.o
    /kfg.o
    /kfgb.o
    /kfiof.o
    /kfn.o
    /kfnm.o
    /kfnp.o
    /kfns.o
    /kfpkg.o
    /kfrc.o
    /kfsp.o
    /kdd.o
    /kdlxdup.o
    /kcffo.o
    /kcv.o
    /kcvfdb.o
    /krsd.o
    /gra.o
    /kzp.o
    /kzr.o
    /kzsr.o
    /kzu.o
    /kzup.o
    /updexe.o
    /kerutl.o
    /ksfd.o
    /ksp.o
    /kspt.o
    /ksu.o
    /qmxstr.o
    bundledata_PSU.xml
    ins_rdbms.mk
    ins_rdbms.mk
    ins_rdbms.mk
    ins_rdbms.mk
    bug19532017_downgrade.sql
    bug19532017_upgrade.sql
    catbundle.sql
    dbmsqopi.sql
    prvtqopi.plb
    dbmssqlpatch.sql
    prvtsqlpatch.plb
    sqlpatch_bootstrap.sql
    sqlpatch.pm
    sqlpatch.pl
    sqlpatch
    bug19532017_downgrade.sql
    utluppkg.sql
    prvtpckl.plb
    19769480_apply.sql
    19769480_rollback.sql
    catctl.pl
    catlmnr.sql
    catmmig.sql
    catupstr.sql
    noncdb_to_pdb.sql
    utluppkg.sql
    utlusts.sql
    prvtpckl.plb
    xml
    /xsfisop.o
    /xsiosec.o
    /lpxfsmbuf.o
    /kfmu.o
    /kgfz.o
    /kfmu.o
    /kgfz.o
    /qcdl.o
    /kgl.o
    /kgl2.o
    /kgsc.o
    /qci.o
    /qcs.o
    /qcs2.o
    /qcsvctx.o
    /qmxsax.o
    diaus.msb
    diaus.msg
    oraus.msb
    oraus.msg
    libzt12.a
    ins_net_client.mk
    ins_rdbms.mk
    ins_rdbms.mk
    libzt12.a
    libnnzst12.a
    libnnz12.a
    libcryptocme.sig
    libcryptocme.so
    libccme_asym.so
    libccme_base.so
    libccme_ecc.so
    libccme_ecc_accel_fips.so
    libnnz12.so


SQL> 

If you are not interested in the patch details, but only want to know the count of patches, use this query:

SQL> select xmltransform(dbms_qopatch.get_opatch_count, dbms_qopatch.get_opatch_xslt) X from dual;

X
--------------------------------------------------------
Interim patches: (5)

SQL> 

I want to apply patch 21153266, but want to verify if the patch is applied.

SQL> set pages 999 long 32000
SQL> select xmltransform(dbms_qopatch.is_patch_installed(21153266), dbms_qopatch.get_opatch_xslt) X from dual;

X
----------------------------------------------------------

SQL> 

No results, so patch not installed.

Download and install the patch using OPatch.

[oracle@localhost 21153266]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.1.0.1.10
Copyright (c) 2015, Oracle Corporation.  All rights reserved.

Oracle Home       : /home/oracle/app/oracle/db/12.1.0.2
Central Inventory : /home/oracle/app/oraInventory
   from           : /home/oracle/app/oracle/db/12.1.0.2/oraInst.loc
OPatch version    : 12.1.0.1.10
OUI version       : 12.1.0.2.0
Log file location : /home/oracle/app/oracle/db/12.1.0.2/cfgtoollogs/opatch/21153266_Dec_01_2015_12_53_25/apply2015-12-01_12-53-25PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   21153266  

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name: 

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/home/oracle/app/oracle/db/12.1.0.2')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '21153266' to OH '/home/oracle/app/oracle/db/12.1.0.2'

Patching component oracle.rdbms, 12.1.0.2.0...
Patch 21153266 successfully applied.
Log file location: /home/oracle/app/oracle/db/12.1.0.2/cfgtoollogs/opatch/21153266_Dec_01_2015_12_53_25/apply2015-12-01_12-53-25PM_1.log

OPatch succeeded.
[oracle@localhost 21153266]$ 

Now, let us query the database again for patch installed.

SQL> set long 32000 pages 9999
SQL> select xmltransform(dbms_qopatch.is_patch_installed(21153266), dbms_qopatch.get_opatch_xslt) X from dual;

X
--------------------------------------------------------------------------------
Patch Information:
	 21153266:   applied on 2015-12-01T12:54:08-08:00
SQL> 

To find out which files were updated by a patch, use:

SQL> select xmltransform(dbms_qopatch.get_opatch_files(21153266), dbms_qopatch.get_opatch_xslt) patch_status from dual;

PATCH_STATUS
--------------------------------------------------------------------------------
Patch Id :	   21153266
    /kkm.o
    ins_rdbms.mk

SQL> 

To find out which bug fixes are applied to the database, use:

SQL> set pages 999 long 32000
SQL> select xmltransform(dbms_qopatch.get_opatch_bugs, dbms_qopatch.get_opatch_xslt) X from dual;

X
--------------------------------------------------------------------------------
  Bugs fixed:
	 21153266
  Bugs fixed:
	 21047803  19699946  19245191  19223010  21047766  20408829  19231857  2
1566944  21566993  21068507  19176885  20408866  19877336  19895326  21811517  1
9153980  20415564  19909862  21555660  19855285  19895362  19623450
  Bugs fixed:
	 19243521  20048359  19571367  20397490  20476175  20925795  21442094  2
0245930  20134339  21281532  21421886  20413820  18966843  20441797  20281121  2
1620471  19791273  20361671  18411216  19587324  19032777  19468991  17365043  2
2062026  20471920  18743542  20688221  20122715
  Bugs fixed:
	 20424899  19307662  20746251  20919320  20331945  20657441  20387265  2
0294666  19703301  20165574  20899461  20558005  19399918  20402832  21225209  1
9284031  20474192  19699191  20093776  19989009
  Bugs fixed:
	 20440930  18306996  19865345  19536415  18607546  19487147  19035573  1
9873610  19081128  19606174  19272708  17274537  19358317  19291380  19012119  1
9687159  19547370  20348653  20425790  19524384  19385656  19928926  18681056  1
9512341  18856999  16619249  19018447  19393542  20235511  19627012  19649152  1
9670108  19684504  18417036  20347562  19658708  19896336  19805359  19637186  1
8940497  19183343  19978542  19644859  18202441  19023822  19791377  18909599  1
9335438  19238590  19841800  19619732  19315691  19730508  19597583  19065677  1
9915271
  Bugs fixed:
	 20284155  19157754  18885870  19303936  19708632  19371175  18618122  1
9329654  19075256  19074147  19044962  19289642  19068610  18988834  19028800  1
9561643  19058490  19390567  18967382  19174942  19174521  19176223  19501299  1
9178851  18948177  18674047  19723336  19189525  19001390  19176326  19280225  1
9143550  18250893  19180770  19155797  19016730  19185876  18354830  19067244  1
8845653  18849537  18964978  19065556  19440586  19439759  19024808  18952989  1
8990693  19052488  19189317  19409212  19124589  19154375  19279273  19468347  1
9054077  19048007  19248799  19018206  18921743  14643995  18456643  16870214  1
9434529  19706965  17835294  20074391  18791688  19197175  19134173  19174430  1
9050649  19769480  19077215  19577410  18288842  18436647  19520602  19149990  1
9076343  19195895  18610915  19068970  19518079  19304354  19001359  19676905  1
9309466  19382851  18964939  16359751  19022470  19532017  19597439  18674024  1
9430401

SQL> 

Using Queryable Patch Inventory, you need not be on the database sever to find out the patches applied. You can connect to the database remotely and run these queries. This also helps to build reporting and may be a patch repository.