Invalid objects check
Job details
| Name: |
Invalid objects check |
| Platform: |
Oracle |
| Category: |
Maintenance |
| Description: |
Detecting invalid objects in the database. |
| Long description: |
This check detects invalid objects in the database. |
| Version: |
1.9 |
| Default schedule: |
40 7,17 * * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & ten_and_above = ‘1′ & _priv_read_dba_objects = ‘1′] |
Parameters
| Name |
Default value |
Description |
| ignore object name |
|
object name excluded from being checked (comma separated). The format is OWNER.OBJECT example SYS.LOCKING |
| ignore owner |
|
owner excluded from being checked (comma separated). |
| return status on invalid objects |
1 |
Return status value (ALARM – 2, WARNING – 1, or OK – 0) when an invalid object appears (and are not in “ignore object name” list). |
Job Summary
- Purpose: The purpose of this job is to detect and log invalid objects within an Oracle database system.
- Why: Monitoring invalid objects is crucial as they may indicate deeper issues such as incomplete or failed updates, or corruption that can affect database integrity and performance.
- Manual checking: You can manually check for invalid objects in Oracle by executing the following SQL command:
SELECT owner, object_name FROM dba_objects WHERE status = 'INVALID';
Job Operation
- Execution Frequency: The job is configured to run automatically twice a day at 7:40 AM and 5:40 PM.
- Dependencies: This job depends on itself and a historic table to function properly. Any failure in these dependencies triggers a cleanup operation.
- Reporting: It generates a report titled “Invalid object statistics” providing a detailed statistics about invalid objects detected during the checks.
Report Details
- Report Content: The report lists invalid objects along with owner, name, creation date, and the date of the last DDL operation.
- The SQL query used for generating the report is:
select owner as "Owner", object_name as "Name", created as "Created", last_ddl_time as "Last DDL", histr_date as "Detected" from dbw_invalid_hist order by 5,1,2
Implementation Details
- Table Creation: A specific table “dbw_invalid_hist” is created to log the history of all invalid objects detected.
- Procedure Execution: A procedure named “invalid_objects_check” dynamically checks for invalid objects, enforces ignore lists for owners and objects (configurable via parameters), and logs findings into the historical table.
- Error Handling: The process includes error handling that logs any exceptions into a DBW_ERRORS table with a detailed error message and error code.
Additional Configurations
- Compatibility: This job is specifically tailored for Oracle databases (instances with databasetype ‘oracle’).
- Installation Preferences: Upon installation, the task updates its package name in the DBW_TASKS table to maintain consistency with any operational adjustments made since versions 1.7 and 1.8.
This configuration and execution strategy ensure a robust check on the health and integrity of Oracle database objects, thereby safeguarding the database against potential failures and performance issues related to invalid objects.