Skip navigation
orachk_logo.jpg

Do you struggle with some of these problems?

  • If you have an outage the impact to on your business can be huge
  • How can you detect risks early to minimize business impact?
  • There are so many different problems, how do you know which ones occur most often and will impact you most?
  • You have a lot of systems and products, how do you manage risks effectively across your enterprise?

 

ORAchk has been designed to address these challenges, with the following capabilities:

  • Automated risk identification and proactive notification before business is impacted
  • Health Checks based on most impactful reoccurring problems across Oracle customer base
  • Lightweight & non intrusive Health Check framework for the Oracle stack
  • Runs in your environment - no need to send anything to Oracle
  • Scheduled email Health Check reports with drill down capability

 

The rest of this article will give you a quick overview of how to use ORAchk to reduce your risk. This was also covered in the My Oracle Support Web Cast How To Use ORAchk to Reduce Your Risk (Recording | pdf )

 

Topics covered:

 

 

Products & Environments Covered by ORAchk

 

ORAchk is supported on the following platforms:

 

  • Linux x86-64 (zLinux, OEL, RedHat and SuSE 9, 10, 11)
  • Oracle Solaris (SPARC and x86-64)
  • IBM AIX
  • HP-UX
  • MS Windows x64 (2008 and 2012, Cygwin Required)

 

ORAchk is written in BASH and requires BASH 3.2 or higher installed, which is not installed by default on AIX or HP-UX, but is available for download for free. Windows requires Cygwin Linux emulator.

The aim of ORAchk is to provide one Health Check tool for the entire Oracle stack.

 

As of release 12.1.0.2.4 ORAchk has checks for:

                                                                    

Oracle Database (versions 10gR2, 11gR1, 11gR2 & 12cR1)

  • Standalone Database
  • Upgrade Readiness Validation
  • Grid Infrastructure & RAC
  • Maximum Availability Architecture (MAA) Validation
  • Golden Gate
    

Enterprise Manager Cloud Control (12c only)

  • Repository
  • Agents
  • OMS (version 12.1.0.1 and above on Linux only)
  

Oracle Hardware Systems

  • Oracle Solaris
  • Oracle Systems configuration for Oracle Database, Oracle Middleware & Oracle Applications

E-Business Suite

  • Oracle Payables (R12 only)
  • Oracle Workflow
  • Oracle Purchasing (R12 only)
  • Oracle Order Management (R12 only)
  • Oracle Process Manufacturing (R12 only)
  • Oracle Fixed Assets (R12 only)
  • Oracle Human Resources (R12 only)
  • Oracle Receivables (R12 only)
  

Oracle Siebel

  • Oracle Siebel verification of the database configuration for stability, best practices and performance optimization (Siebel 8.1.1.11 connecting to Oracle Database 11.2.0.4.)

 

If you have an Oracle Engineered System, the ORAchk functionality is also available via the Exachk Script.

          

Where to get ORAchk

 

ORAchk is available from a number of different locations and bundles:

  • Shipped with the Database from versions 11.2.0.4 and 12.1.0.2 (Default location is $ORACLE_HOME/suptools/orachk)                      
  • Shipped with Database PSUs from Jan 2015                      
  • Included within TFA / Support tools bundle                     
  • Main ORAchk Document 1268927.2                 

 

 

ORAchk Installation

 

Installation is simple, all you need to do is unzip the orachk.zip file, it takes less than a minute.

ORAchk evolved from RACcheck and as such is RAC aware. If you are installing on a RAC system you only need to put it on one node, ORAChk will discover the other nodes and diagnose them remotely.

For standalone database instances ORAchk needs one install per host, or for ease of deployment mount it on a shared file system accessible to each host.

 

 

ORAchk Auto Update

 

ORAchk provides automatic update options to help you stay up to date with the latest health checks.

 

Option 1: If you have connection from your environment to My Oracle Support

  • When ORAchk is older than 120 days it will prompt you to let it automatically download newer version from My Oracle Support
  • Can also be specifically triggered with:     ./orachk -download

 

Option 2: If you have no direct connection from your environment to My Oracle Support

  1. Download the latest orachk.zip to a shared network staging location
  2. Set environment variable RAT_UPGRADE_LOC to point to staging location
  3. Next time orachk script is started it will prompt to allow it to upgrade itself

 

Sample Problem Scenario

 

Take an example problem scenario without ORAchk:

prob-example-no-orachk.jpg

  1. The DB_FILE_MULTIBLOCK_READ_COUNT parameter is not set correctly
  2. This leads to the optimizer favoring large I/Os and having a negative effect of the performance of queries
  3. You see the slow query performance and open a Service Request with Oracle Support
  4. It takes time for Oracle Support and you to work together to troubleshoot, find the cause and resolve the issues
  5. This can all have a negative impact on the rest of your business

 

Now look at the same example problem scenario with ORAchk installed:

prob-example-with-orachk.jpg

 

  1. ORAchk proactively sends you regular reports of issues found, which would include warning you about the DB_FILE_MULTIBLOCK_READ_COUNT parameter
  2. So you read the recommendation in the report and the supporting knowledge documents on My Oracle Support and understand how to resolve the problems
  3. You are able to proactively resolve the root cause of potential performance issues before they have had a negative impact
  4. The business continues as normal with no complaints

 

 

Recommended ORAchk Usage Methodology

 

The recommended ORAchk usage is:

  1. Schedule ORAchk to run in daemon mode on specified interval and email report
  2. Identify actions easily by viewing reports and automated comparison of previous runs
  3. Act on recommendations by following the My Oracle Support links

 

How to Schedule ORAchk

schedule-orachk.jpg

 

This shows a simple example of how to schedule monthly ORAchk reports.

You set the AUTORUN_SCHEDULE parameter, telling the ORAchk daemon when the checks should be run:

  • Hour (from 0 to 23)
  • Day of the month
  • Month of the year
  • Day of the week

Wild cards tell it to use every value, so in this first example we tell ORAchk to run at 3am on the 28th of the month every month, no matter what day of the week.

 

You can set a more complex schedule, specifying multiple values for each of the time fields if necessary, for example:
  orachk -set `AUTORUN_SCHEDULE=8,20 * * 2,5'

This tells ORAchk to run on the 8th and 20th hour, every day, every month on day 2 and 5 - meaning it will run every Tue and Fri at 8am and 8pm.

 

Use the NOTIFICATION_EMAIL to tell ORAchk the email address where to send the report, this can be multiple comma separated addresses if needed.

 

Then we start the daemon with:
  ./orachk -d start

 

 

Viewing the ORAchk Report

report-summary.png

When ORAchk runs at the scheduled time it will send you through an html report of the output. This will contain:

  • A High level health score based on the number of checks which passed or failed
  • A summary of the run, showing things such as where and when it was run, which version was used, how long it took, which user it was run as etc
  • The table of contents, which provides easy access to findings. This table of contents will change based on the environment, as ORAchk is smart enough to figure out which Oracle products you have installed and will only run checks for products installed.
  • You will also see the Findings themselves and the recommendations for how to resolve any issues found.

 

The findings section of the report shows you a table with a row corresponding to each check:

report-findings.jpg

 

In the above example, the first finding shows we got a warning based on an SQL check which tells us we should consider unsetting the database parameter DB_FILE_MULTIBLOCK_READ_COUNT, this was found on all databases tested.

 

We can drill down into view the details, which will show us something similar to this:

report-recommendation.png

This gives:

  • A more detailed explanation of the problem.
  • Recommendation for what you should do next.
  • Links to supporting documents, such as those on My Oracle Support Knowledge.
  • It shows where the check failed and anywhere it passed.
  • Then it will also show you why the check failed.

 

ORAchk will only run checks relevant to what you have installed on the machine.

 

How to Exclude Checks

You should aim to address all issues which are reported by ORAchk. Problems should stand out very clearly in the report, you should not have a situation where your team says "yes we know about that, we ignore that part of the report", as this would make it much easier to miss important problems.

Although the checks in ORAchk are based on the top impactful problems seen to Oracle Customer Support and recommendations are based on what is generally the best setting. It may be that in your particular situation you know the issue described is not a problem, in which case it’s best practice to exclude these checks from being run. See the FAQ "How to exclude certain checks from ORAchk" on the ORAchk Document 1268927.2

 

 

Profiles and Adhoc Usage of ORAchk

 

If you want to run just a subset of the checks, for example if you have an environment with both the Database and EM on the same host but you only want to run the EM checks you can use ORAchk profiles.

Profiles are logical groupings of checks that are about similar problem areas such as em, dba, sysadmin or clusterware etc.

You can run orachk with only checks in a particular profile by using:
  ./orachk -profile <profile_name>

 

New profiles get added as and when new groups of checks are added, so the full list may change with each release, however you can see a full list of available profiles by simply using the –h (for help) option:
  ./orachk -h

 

If you’re planning to upgrade the database you can run
  ./orachk -u -o pre

 

ORAchk will prompt you for which version of the database you're going to upgrade to and will then run checks specific to problems you might encounter doing that upgrade.

 

Once you’ve done the database upgrade you can again use ORAchk to verify everything, by doing:
  ./orachk -u -o post

 

 

ORAchk Collection Manager

 

ORAchk Collection Manager is a companion Application Express (APEX) application. When you have many systems it can be difficult to manage these on a system by system basis.

The Collection Manager provides a dashboard in which you can track your ORAchk collection data in one easy to use interface.

 

You can use the Collection Manager to:

  • Monitor by Business Unit
  • Monitor systems within Business Units, by DBA Manager and DBA
  • Spot trends by finding the most frequently Failings or Warnings
  • View Results for automatic comparison between most recent and last collections per system
  • See incidents created for tracking correction of issues
  • Browse the collections by various filter criteria

 

The ORAchk Collection Manager is installed on a single system but serves as a enterprise-wide repository of all ORAchk collections.  The ORAchk collections can be configured to upload to the repository automatically.

ORAchkCM.png

 

 

ORAchk Community

 

ORAchk typically has a quarterly release cycle, which allows us to quickly get out new checks as top problems are found by Oracle Support. It also allows us to quickly fix any bugs found in ORAchk and build in new features. The ORAchk development team have a particular focus on trying to accommodate features requested by customers.

So any time you have any feedback on the content or features of ORAchk, we'd love to hear from you with any problems or suggestion you have via the ORAchk community.

 

 

More Information about ORAchk

 

For further information about ORAchk

 

 

Gareth-Chapman-Oracle

You've probably come across and/or was asked by Oracle Support to set
      EVENT 10046
to enable SQL statement tracing while investigating a SQL related issue with your Oracle Database.
 
The 10046 trace is the equivalent of setting SQL_TRACE=TRUE. The advantage of using the event is that additional details may be written to the trace file depending on the level specified with the event. This event is often requested to be set by Oracle Support when gathering information for specific SQL issues and particular when investigating and/or diagnosing performance issues.
 
For Query Performance issues the typical requirement is to record wait and bind variable information for queries. This is achieved using 10046 with trace level 12.
 
The following document outlines various methods to gather 10046 trace in order to investigate Query Performance issues for a serial query.

  • Document 376442.1 How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues
    The document covers enabling the event for
    • session tracing
    • tracing a process after it started
    • adding the event to init.ora
    • collecting trace using SQLT
    • and more ...


For diagnosing performance issues with a parallel query, most of these instructions from the above document are also valid, however there are some differences. Those differences are outlined in


To learn more about Event 10046 and how to interpret the output, refer to

 

10046 trace is collected by certain methods of SQLT that execute the problem query. SQLT is an Oracle database support tool to diagnose SQL statements performing poorly. You can download SQLT individually or as part of the Support Tools Bundle Trace File Analyzer Collector (TFA):

 

    Document 215187.1 SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly

    Document 1513912.2 TFA Collector - Tool for Enhanced Diagnostic Gathering

 

Event10046.png

Welcome to the My Oracle Support Community! We highly encourage you to personalize your community display name to make your activity more memorable. Please see https://community.oracle.com/docs/DOC-1022508 for instructions.