otnlogoThanks Tim for coming up with this idea! Pretty neat… And THANK YOU OTN!!

There are quite a few features I like in the Oracle Database, but today I choose ORAchk (and EXAchk). Troubleshooting and diagnostics are my favorite area and I was amazed by the enhancements Oracle made in integrating the various diagnostics tools and preserving history. And ORA/EXAchk is a no-cost tool, provided by Oracle, enhanced every quarter with more and more health checks…

ORAchk and EXAchk tools started as just another configuration verification tool (used to be called RACcheck). ORAchk (for non-Engineered systems) and EXAchk (for engineered systems) grown into a “product” from a “tool”. This is evident in 12.2, as the documentation from a simple MOS note grown into a full document User Guide.

What’s cool is that ORAchk and EXAchk can be daemonized to run these checks periodically. The daemon can also purge old reports based on the retention policy you set. The most favorite enhancement I like is the ability to compare two reports. Oracle ORAchk and Oracle EXAchk automatically compare the two most recent HTML reports and generate a difference report, when run in an automated daemon mode.

To manually compare two reports, orachk (or exachk) can be invoked with the -diff option.

The comparison report’s sections are very appropriate.

  • Difference between report 1 and report 2 (status change of checks)
  • Unique findings in report 1 (issues that were fixed)
  • Unique findings in report 2 (new issues after report 1)
  • Common findings in report 1 and 2 (issues that were not fixed)

Oracle Health Check Collections Manager is another cool application that comes with these tools, where the ORAchk (and EXAchk) results can be uploaded to an Oracle database APEX workspace for analysis and reporting. ORAchk (and EXAchk) can be configured to automatically upload check results to the Oracle Health Check Collections Manager database. Specify the connection string and the password to connect to the database to upload collection results. Oracle Health Check Collections Manager stores the connection details in an encrypted wallet.

If you have not run ORAchk recently, it’s time to download and run the check today. And, don’t forget to schedule it! Forgot to mention, since version you have the ability to Create User Defined Checks too!

Download ORAchk – My Oracle Support Note 1268927.2

While you are there, check out TFA (Trace File Analyzer: MOS 1513912.2) and CHM (Cluster Health Monitor: MOS 1328466.1) as well!

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…

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 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.


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

Download and unzip the patch combo.

Download the latest OPatch, and copy to Oracle Home.

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

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.

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.

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.

Now, apply the OVJM PSU.

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

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.

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.

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

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

No results, so patch not installed.

Download and install the patch using OPatch.

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

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

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

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.




%d bloggers like this: