How It Works
This couldn't be simpler; the table SYSCHECKLIST contains many small SQL queries. Each of these returns "OK" (a single row) when it is successfully run against the Oracle database.
It's probably easiest to look at an example. If we take Profile Options. This is one area of Oracle that can be quite difficult to monitor. We can all see what they are now but what were they last week?! In the e-Business Suite profile options are stored in the two tables FND_PROFILE_OPTIONS and FND_PROFILE_OPTION_VALUES. Taking as an example the SITENAME profile option. If your site happened to be called "Production System" then you could write some SQL that would check this;
select 'OK'
from applsys.fnd_profile_option_values fpov
where fpov.application_id = 0
and fpov.profile_option_id = 125
and fpov.level_id = 10001
and fpov.level_value = 0
and replace(nvl(fpov.profile_option_value, '
') = 'Production System'
If your system had it's SITENAME set to "Production System" the query would return "OK". Otherwise it will either return nothing (if the site is called something else), multiple records if there are multiple entries (should be impossible, but you never know!), or an error if the SQL is invalid (such as Oracle dropping either of the tables in a patch - let's hope that never happens!).
By running this test on a daily basis if someone changes the profile option you will be notified.
By stringing together a group of these queries we can check multiple parts of the system. The table SYSTESTRESULT contains the results of previous runs (for those space-conscious DBA's the data in this table is cleared down after a month - you can adjust this in the SQL below).
Setting Up The Database
The database component of this monitoring suite comprises of two tables (SYSCHECKLIST, and SYSCHECKRESULT) and a package (SYSCHECK). The SQL to create each of the tables is;
create table SYSCHECKLIST
(
TEST_REF VARCHAR2(10) not null,
TEST_DESC VARCHAR2(80) not null,
TEST_SQL CLOB not null
);
create table SYSCHECKRESULT
(
TEST_REF VARCHAR2(10) not null,
TEST_DATE DATE not null,
TEST_RESULT VARCHAR2(255) not null
);
It should be noted that I'm not saying anything here about schemas and permissions here. I created the tables under our APPS schema but then I work for a small company and that's out policy. Your company will probably be different - I know some (most?) companies can be very strict about creating objects in the APPS schema and it's not exactly something Oracle recommends!
Of course using the APPS schema means you don't have permissions problems with your queries (i.e. your schema owner will need to be granted select for everything it's wants to check).
The SQL to create the package (and package body) is;
Package Specification
Package Body
It's quite long so I've moved it to my Google Documents pages - any problems add a comment!
The functions in the package check either one test, all tests, or all tests (run as a concurrent request). Setting up a concurrent request is a lot more work so I'll cover that as a separate Knol when I get the chance but you do just need to create an executable pointing at the SYSCHECK.RUNALLTESTSCR procedure, and then a program pointing at the executable (not forgetting to create an incompatibility that will prevent the checks being run simultaneously) and you're there.
NOTE: I've updated the source code associated with this Knol as it was taking too long to churn through 500,000 checks. It now only logs a result if the result is something other than "OK". Which I guess is kind of what you want.
Running The Tests as a Report
A useful feature of the way the information is setup is that you can write a report that will actually run the tests. The SQL needed to do this is;
SYSCheck Report SQL
This SQL checks to see if the first test has been run today. If it has then it simply presents the results from that test, if it hasn't then it runs the tests. The SQL is split into three parts separated by the "UNION" statements. The first part will run the report if the number of records found in SYSCHECKRESULT for today is zero (the +1 will make the WHERE clause "rownum = 1" which will return one row, which will then trigger calling the report. "rownum > 1" will never return anything - the joys of oracle!).
The second part looks for failed records (where TEST_RESULT <> 'OK'), it does this by getting a count of all records for today and if this count is greater than zero it displays the with the failure total.
The final part looks for successes (hopefully this will be the most common part). It has two conditions to the WHERE clause the first checks to make sure there are no failures, and second checks to make sure there are successes (otherwise parts 1 and 3 will display at the same time).
Sample Tests
Checking System and Application Profile Options
This is an example of when you want to check the same thing again and again. So it's easier, rather than just taking the options one at a time and creating a test for each one, to write a script that will go through the profile options tables (FND_PROFILE_OPTIONS and FND_PROFILE_OPTION_VALUES) and build the tests for you. Looking at our production system we have approximately 4,000 profile options so one-at-a-time was never going to work for us!
Here is the script;
SYSCheck Demo - Profile Options
As you can see from the script a "Template" test is stored in the variable v_SQLTemplate, this is then updated and written into the SYSCHECKLIST table for each profile option returned by the query.
I have added some additional code to the comparison against PROFILE_OPTION_VALUE and the actual value so that quotes and null values are correctly compared. This will lead to a very slight performance hit. If you're worried about this (I'm not) then you can fix it by creating different types of rules based on the actual profile option value (null, not null, contains quotes, etc) - that's a lot of work for not much benefit if you ask me, but I'm a "getting it to work" person not a SQL purist!
I have restricted the profile options I'm checking to those are the SITE and APPLICATION level rather than just checking all options as, to be honest, these are the ones I'm worried about.
Checking the text of messages in FND_NEW_MESSAGES
This is another example of using a script to generate tests form existing database records.
This script looks at the text of messages in the table and checks to see if the message has changed.
Here is the script;
SYSCheck Demo - FND New Messages
The script is only checking US language messages but it should be fairly simple to change this to check messages for other languages.
Monitoring Table/Column Changes
This script allows you to monitor the datatypes and sizes of all the database tables Tables and columns. Needless to say on a large database this can be quite a substantual number: on our Oracle e-Business Suite implementation this is a little under 600,000 checks.
Here is the script;
SYSCheck Demo - Table/Column Changes
Needless to say if you're adding 600,000 checks to the routine then it will take substantually longer to complete - especially if you are logging successes as well as failures!
