2 Scheduled reports as alerts
3 The new reports
4 Other WhatScripts checks in Blitz Report
5 About OATC
1 OATC’s monitoring scripts
OATC (Oracle Applications & Technology Consulting) supports Oracle E-Business Suite customers with WhatScripts, a collection of monitoring scripts that its consultants have built over decades of EBS operations. The scripts check database health, space, sessions and locks, concurrent managers and requests, workflow, patching and security, so that problems are found before users notice them.
OATC shared WhatScripts with us, and we added its checks to the Blitz Report library as 18 new reports that run from Excel or as scheduled concurrent requests on EBS 11i, R12.1 and R12.2 (AD Code Levels on R12, AD ADOP Sessions on R12.2).
2 Scheduled reports as alerts
A monitoring check only helps if someone sees the result when it matters. Most of the new reports have a parameter that restricts the output to the problem state, for example Not Running only, Errors only or a threshold such as Max Used Percent From.
Schedule such a report with email delivery, and Blitz Report sends the email only when the report finds something. A scheduled report that retrieves no rows sends no email. This makes a scheduled Blitz Report a simple alternative to Oracle Alert, and the email contains the full detail as an Excel file instead of a text message. See scheduling a report and delivery options in the user guide.
Examples:
- WF Service Components with Not Running only, every 15 minutes: an email when the notification mailer or an agent listener stops.
- WF Queue Backlog with Oldest Message Hours From 4: an email when workflow or business event messages are no longer processed.
- DBA Data Files with Max Used Percent From 90, daily: an email before a tablespace runs full, taking autoextend into account.
- DBA Sessions with Active only and Status Duration Minutes From 60, hourly: long running calls with their user, form or concurrent request and SQL.
- FND Debug and Trace Settings, weekly: debug or trace settings left switched on in production.
3 The new reports
| Report | What it shows | Alert parameter | Example |
|---|---|---|---|
| DBA Sessions | Current database sessions with EBS user, responsibility, form or concurrent request, SQL, wait event, blocker, PGA, temp and undo usage | Status Duration Minutes From, Blocked Sessions only | xlsx |
| DBA Undo Statistics | Undo generation, longest query and ORA-01555 (snapshot too old) errors per 10-minute interval | Errors only, Hours Back | xlsx |
| DBA Data Files | Data and temp files with size, free space, autoextend headroom and hot backup mode | Max Used Percent From | xlsx |
| DBA ASM Disk Groups | ASM disk group capacity, usable space after mirroring, offline disks | Used Percent From | |
| DBA Resource Limits | Processes, sessions and other resources against their limits | Max Percent From | xlsx |
| DBA SYSAUX Occupants | SYSAUX space by component, with the optimizer statistics history retention | xlsx | |
| DBA Registry | Installed database components, version and status | Invalid only | xlsx |
| DBA Users | Database accounts with status, expiry, profile, quotas, EBS applications and default password check | Expiring within Days, Default Password only | xlsx |
| DBA Database Links | Database links with owner, remote user and host | xlsx | |
| DBA Directories | Directory objects with path and grants | xlsx | |
| FND Gather Statistics History | Gather Schema and Table Statistics runs per object, with duration | Duration Minutes From | xlsx |
| FND Debug and Trace Settings | Debug, trace and initialization SQL profile values at any level, and programs with SQL trace enabled | Any row | xlsx |
| FND OAF Personalizations | OA Framework page personalizations by level, with the personalized page | Last Update Date From | xlsx |
| AD Patch Task Timing | Run time of each job of a patch or upgrade session, to find the slowest jobs | xlsx | |
| AD ADOP Sessions | R12.2 online patching sessions per node and phase, with status and duration | Incomplete only | xlsx |
| AD Code Levels | Product code levels and baselines, as used in patch prerequisites | xlsx | |
| WF Service Components | Notification mailer, agent listeners and other service components with status and parameters | Not Running only | xlsx |
| WF Queue Backlog | Messages waiting in the workflow and business event queues, with the age of the oldest | Message Count From, Oldest Message Hours From | xlsx |
4 Other WhatScripts checks in Blitz Report
The remaining WhatScripts checks are available in these existing reports:
- Concurrent requests (running, pending, long running, history, schedules) and managers: FND Concurrent Requests, FND Concurrent Requests Summary, FND Concurrent Managers
- Users, responsibilities and logins: FND User Responsibilities, FND User Login History
- Patches: AD Applied Patches, AD Applied Patches R12.2
- Profile options: FND Profile Option Values
- Blocking locks: DBA Blocking Sessions
- Tablespaces and segments: DBA Tablespace Usage, DBA Segments
- Invalid objects: DBA Objects
- SQL performance and waits: DBA SGA SQL Performance Summary, DBA SGA Active Session History
- Archive logs: DBA Archive / Redo Log Rate
- Workflow notifications and activities: WF Notifications, WF Activity Status Summary
- Forms personalizations: FND Forms Personalizations
Checks at operating system level (CPU, memory, file systems, processes, RPM packages) stay with OS monitoring tools, as Blitz Report runs SQL against the database.
5 About OATC
OATC is an Oracle consulting firm based in Charlotte, North Carolina, specializing in Oracle E-Business Suite and Oracle Cloud applications, database and infrastructure services, including EBS migrations to OCI and Azure. OATC’s DBA services include proactive monitoring of Oracle Database, Linux and EBS environments with custom thresholds and alerts. Thanks to OATC for sharing WhatScripts.
If you would like to see the reports on your own EBS instance, please contact us.