Exadata: Automating Compliance Checks

I wanted to share a brief summary–a cheat sheet–to highlight the Autonomous Health Framework (AHF) setting and how we use this great tool in database installations. AHF is an out-of-box solution that provides automated monitoring and proactive issues detection. If you’re not familiar with it, read on.

AHF consolidates several main tools and performs the following functions:

• Cluster Health Monitor (CHM)
• Cluster Health Advisor (CHA)
• Cluster Verification Utility (CVU)
• Hang Manager
• Memory Guard
• QoS Management
• Compliance Validation (ORAchk & EXAchk)
• Pre/Post Upgrade Validation
• Incident Detection & Notification
• Self-Service Incident Resolution
• Diagnostic Collection (TFA)
  (including OSWatcher, Procwatcher, Strace, oratop and other tools installed with TFA)

It also supports a range of products used in checking best practices:

• Oracle Database (SI and RAC)
• Grid Infrastructure
• Oracle Engineered Systems
• Oracle GoldenGate
• Oracle Database Appliance
• Oracle Siebel
• Oracle Peoplesoft
• Enterprise Manager CC (12c)
• Identity and Access Management
• Oracle Hardware Systems (Not only Exadata)

Awesome, right?

Recently, I talked about this topic at a couple of conferences, covering the machine learning piece of this whole universe, but this is a topic for another post. This post will focus on running compliance checks automatically, specifically for the Exadata environment.

So, how does it start? Let’s start with a quick summary:

1. Download the latest AHF Version at MOS:

Autonomous Health Framework (AHF) – Including TFA and ORAchk/EXAchk (Doc ID 2550798.1)

2. Unzip and install AHF:

[root@exa01dbadm01 boesing]# unzip AHF-LINUX_v21.1.3.zip
Archive: AHF-LINUX_v21.1.3.zip
inflating: README.txt
inflating: ahf_setup
extracting: ahf_setup.dat
inflating: oracle-tfa.pub
[root@exa01dbadm01 boesing]# ./ahf_setup
-- common locations of ahf_loc is /opt/oracle/dcs/oracle.ahf or /u01/app/grid/oracle.ahf

3. Synchronize your RAC nodes and Cell nodes (Exadata):
(Also to be used when a new node is added.)

[root@exa01dbam01]$ tfactl syncnodes
[root@exa01dbam01]$ tfactl cell sync

4. Run check over the Cell nodes:

[root@exa01dbam01]$ tfactl cell status

Once done, we’re up and running with AHF/TFA for all the nodes. At this point, we have the above list of tools running 24×7 and integrated on our cluster.

Of course, we could go over each and every tool’s specs and even over the AHF management, but again, let’s keep our focus on automating the compliance checks for our Exadata environment. How do we do this?

We all know the compliance checker tool we want is the EXAchk. The trick is knowing how to automate it. The good news is that since version 21.1.x automation only requires that you add the email for the reports if you want to access them from your inbox. Otherwise, you’ll need to access the server to retrieve them.

There are a few other things you might also want to change in the automatic runs. Let’s check these out next.

1. Checking status of EXAchck:

[root@exa01dbadm01 ~]# exachk -d info
------------------------------------------------------------

Master node = exa01dbadm01

exachk daemon version = 211300

Install location = /opt/oracle.ahf/exachk

Started at = Wed Jun 16 11:58:03 MDT 2021

Scheduler type = TFA Scheduler


[root@exa01dbadm01 ~]# exachk -d status
exachk is using TFA Scheduler. TFA PID: 369350

2. Checking for weekly and daily jobs:

[root@exa01dbadm01 ~]# exachk -get NOTIFICATION_EMAIL,AUTORUN_SCHEDULE,COLLECTION_RETENTION
------------------------------------------------------------
ID: exachk.autostart_client_exatier1
------------------------------------------------------------
COLLECTION_RETENTION = 7
AUTORUN_SCHEDULE = 3 2 * * 1,2,3,4,5,6
------------------------------------------------------------
------------------------------------------------------------
ID: exachk.autostart_client
------------------------------------------------------------
COLLECTION_RETENTION = 14
AUTORUN_SCHEDULE = 3 3 * * 0
------------------------------------------------------------

This follows the pattern:

AUTORUN_SCHEDULE=minute hour day month day_of_week

Where:

• minute is 0-59 (Optional. If omitted, then 0 is used)
• hour is 0–23
• day is 1–31
• month is 1–12
• day_of_week is 0–6, where 0=Sunday and 6=Saturday

Use the asterisk (*) as a wildcard to specify multiple values separated by commas.

Some examples:

• Runs every 15 minutes: “AUTORUN_SCHEDULE=0,15,30,45 * * * *”
• Runs every hour: “AUTORUN_SCHEDULE=* * * *”
• Runs at 3 AM every Sunday: “AUTORUN_SCHEDULE=3 * * 0”
• Runs at 2 AM on Monday, Wednesday, and Friday: “AUTORUN_SCHEDULE=2 * * 1, 3, 5”
• Runs at 4 AM on the first day of every month: “AUTORUN_SCHEDULE=4 1 * *”
• Runs at 8 AM and 8 PM every Monday, Tuesday, Wednesday, Thursday, and Friday: “AUTORUN_SCHEDULE=8,20 * * 1, 2, 3, 4, 5”

3. Gather Exachk Next Automated Run:

[root@exa01dbadm01 ~]# exachk -d nextautorun

Next auto run starts on Jun 17, 2021 02:03:00

ID:exachk.AUTOSTART_CLIENT_EXATIER1

4. Changing Exacheck Notification Addresses:
(Using and assigning individual IDs from item 2.)

[root@exa01dbadm01 ~]# exachk -id autostart_client -set NOTIFICATION_EMAIL=boesing@pythian.com

Updated attribute ['NOTIFICATION_EMAIL=boesing@pythian.com'] for Id[exachk.AUTOSTART_CLIENT]

Successfully copied Daemon Store to Remote Nodes


[root@exa01dbadm01 ~]# exachk -get NOTIFICATION_EMAIL,AUTORUN_SCHEDULE,COLLECTION_RETENTION
------------------------------------------------------------
ID: exachk.autostart_client_exatier1
------------------------------------------------------------
COLLECTION_RETENTION = 7
AUTORUN_SCHEDULE = 3 2 * * 1,2,3,4,5,6
------------------------------------------------------------
------------------------------------------------------------
ID: exachk.autostart_client
------------------------------------------------------------
NOTIFICATION_EMAIL = boesing@pythian.com
COLLECTION_RETENTION = 14
AUTORUN_SCHEDULE = 3 3 * * 0
------------------------------------------------------------

[root@exa01dbadm01 ~]# exachk -id autostart_client_exatier1 -set NOTIFICATION_EMAIL=boesing@pythian.com
Updated attribute ['NOTIFICATION_EMAIL=boesing@pythian.com'] for Id[exachk.AUTOSTART_CLIENT_EXATIER1]

Successfully copied Daemon Store to Remote Nodes


[root@exa01dbadm01 ~]# exachk -get NOTIFICATION_EMAIL,AUTORUN_SCHEDULE,COLLECTION_RETENTION
------------------------------------------------------------
ID: exachk.autostart_client_exatier1
------------------------------------------------------------
NOTIFICATION_EMAIL = boesing@pythian.com
COLLECTION_RETENTION = 7
AUTORUN_SCHEDULE = 3 2 * * 1,2,3,4,5,6
------------------------------------------------------------
------------------------------------------------------------
ID: exachk.autostart_client
------------------------------------------------------------
NOTIFICATION_EMAIL = boesing@pythian.com
COLLECTION_RETENTION = 14
AUTORUN_SCHEDULE = 3 3 * * 0
------------------------------------------------------------

5. Change Exacheck schedule and retention:

[root@exa01dbadm01 ~]# exachk -id autostart_client –set "AUTORUN_SCHEDULE=0 3 * * *" -> Time= 3 AM daily
[root@exa01dbadm01 ~]# exachk -id autostart_client –set "collection_retention=90"

6. Testing email sending and running the Exacheck ad-hoc report:

[root@exa01dbadm01 ~]# exachk -testemail notification_email=boesing@pythian.com
Email Successfully sent to ['boesing@pythian.com'] from 'root@exa01dbadm01
[root@exa01dbadm01 ~]# exachk -sendemail notification_email=boesing@pythian.com


Searching for running databases . . . . .

. . . . . . . . . . . .
List of running databases registered in OCR

1. xxxxx
2. yyyyy
3. All of above
4. None of above

Select databases from list for checking best practices. For multiple databases, select 3 for All or comma separated number like 1,2 etc [1-3][3].
[...]
Detailed report (html) - /u01/app/oracle/oracle.ahf/data/exa01dbadm01/exachk/user_root/output/exachk_exa01dbadm01_xxxxxxx_061621_134748/exachk_exa01dbadm01_xxxxxx_061621_134748.html

UPLOAD [if required] - /u01/app/oracle/oracle.ahf/data/exa01dbadm01/exachk/user_root/output/exachk_exa01dbadm01_xxxxxxx_061621_134748.zip
Email Successfully sent to ('boesing@pythian.com',) from 'root@exa01dbadm01' with attachment

7. Example: Running Exacheck for Peoplesoft best practices

[root@exa01dbadm01 ~]# exachk -profile peoplesoft


Searching for running databases . . . . .

. . . . . . . . . . . .
List of running databases registered in OCR

1. xxxxx
2. yyyyy
3. All of above
4. None of above

Select databases from list for checking best practices. For multiple databases, select 3 for All or comma separated number like 1,2 etc [1-3][3].
[...]

8. Bonus: Different Exacheck Options:

[root@exa01dbadm01 ~]# exachk -h
-nopass --- Does not show passed checks.
-show_critical --- Show Critical checks in the orachk report by default
-localonly --- only on the local node.
-dbnames db_names --- Specify a list of DB names to run only on a subset of DBs.
-dbnone --- skips dbs
-dball --- all Dbs
-b --- Runs only the best practice checks. No recommended patch checks.
-p --- Runs only the patch checks.
-m --- Excludes Maximum Availability Architecture (MAA) checks.
-diff --- compare two orachk reports
-clusternodes --- To specify teh list of node names
-nordbms --- Runs in Grid environments with no Oracle Database.
-cvuonly --- Runs only Cluster Verification Utility (oposite= -nocvu)
-failedchecks <previous_run.html> --- Runs only checks from the presious_result,that failed
-profile profile/-excludeprofile --- peoplesoft,seibel,storage,dba,asm,control_VM, preinstall,prepatch,hardware,goldengate,oratier1, virtual_infra,obiee,ebs,control_VM,..
-showrepair <check_id> --- Show repair command for given check id..
-repair all| <check_id list> --- Repair check(s).
-nordbms --- Runs in Grid environments with no Oracle Database.
-cvuonly --- Runs only Cluster Verification Utility
-showrepair <check_id> --- Show repair command for given check id.
-pre/postupgrade --- run pre/post-upgrade best practice checks
-ibswitches --- switches
-cells --- cells

With configurations for 1-5 done and with options 7 and 8 demonstrated, we can accomplish everything in matters of automating Oracle compliance checks.

But how to use those options? Use AUTORUN_FLAGS.

The AUTORUN_FLAGS daemon option determines how compliance checks are run. The syntax is very simple:

AUTORUN_FLAGS=flags

Here are a few examples of how to use it:

• Runs only the dba profile checks: “AUTORUN_FLAGS=-profile dba”
• Runs only the dba profile checks and tags the output with the value sysadmin: “AUTORUN_FLAGS=-profile sysadmin –tag syadmin”
• Runs all checks except the checks in the ebs profile: -excludeprofile ebs

And now, set it to the automated jobs:

$ exachk –set "AUTORUN_FLAGS=-profile sysadmin –tag sysadmin"

If you’re still reading, here’s another bonus. Are you familiar with Oracle Health Check Collections Manager? If not, it’s a great tool and it’s free.

Oracle Health Check Collections Manager is an APEX companion application to Oracle EXAchk that gives you an enterprise-wide view of your health check collection data. All you need is an APEX 4.2 or 5 version and to deploy the tool. The idea is that you can consolidate all your reports in one place and, as a plus, manage your EXAchk reports across a timeline and get a view of the items regression you might have.

I highly recommend it if you want to organize your automated reports.

• For More on AHF: Autonomous Health Framework (AHF) – Including TFA and ORAchk/EXAchk (Doc ID 2550798.1)
• For More on Running Compliance Checks Automatically
• For More on Oracle Health Check Collections Manager

I hope you find this post useful. Don’t forget to sign up for more updates here.