Tuesday, September 17, 2013

Oracle 11g Database and Capturing Error Logging During a Noetix Regeneration

During the Noetix Views regeneration process, spool files are created for almost all SQL scripts invoked. Consequently, if one wanted to examined errors during a regeneration process, one could perform a search of these files to assess the errors being thrown and their risk to "messing up" the regeneration process.

Noetix's approach to finding these errors is to invoke during the regeneration process the fnderr.* scripts (the exact script called is dependent on the operating system being used on the server which has the Noetix Administrator installed).  This is an effective approach.

It is important to know that they do not search for Sql*Plus errors (e.g. of the form 'SP-[0-9]\+'), yet it is helpful to be cognizant of these errors and address them (at least in NVA 6.01).

There would be great value in submitting an enhancement request to capture these errors using the fnderr.* scripts.  In any event, I thought it would be fun to use the new SQL*Plus environment variable, errorlogging, and set it to "on" to capture these errors using a different approach.  This environment variable is new to the Oracle 11g database (one can read about this in the SQL*Plus® User's Guide and Reference). The value of this is that the errors are then logged to the SPERRORLOG table (ownership of this table is dependent on the database user account)

Method to track these errors:

1. Update the "set errorlogging on" with the login.sql script. Of course, I am referring to login.sql script in the Noetix Views script home.

2.  Run the Noetix Views regeneration process.

3. Query the table in the NOETIX_SYS account, SPERRORLOG after the regeneration.

This is just another tool to find errors in a regeneration and performs almost identical function to the existing fnderr.* scripts with the exception of the SQL*Plus error searching (e.g. of the form 'SP-[0-9]\+').

Of course, I do all these modifications in non-production environment.