Simulating ORA-errors

      No Comments on Simulating ORA-errors

Sometimes it can be useful to simulate a problem in your database by simulating an ORA-error. After we had a nasty bug with Grid Control (GC suddenly wasn’t able to monitor the alertlog of 10.2.0.5 databases anymore, see my post about this problem) where we had to discover this bug by having a problem in a database which was not alerted by Grid Control.

It would be nice to have way of testing some basic functionality (as I think monitoring the alertlog for errors is) before updating/patching an agent, grid control or (in the example mentioned above) database. As of Oracle database version 11g Release 1, with the introduction of the ADR (Automatic Diagnostic Repository) the way Grid Control monitors for errors has changed. Until ADR, simply adding 2 lines to the alertlog did the trick but with the ADR it is rather hard to “generate” an ORA-error that will be picked up by Grid Control (the agent).

Fortunately there is a trick to have an 11g database generate an ORA-error which will (should) be picked up by Grid Control. As mentioned before, in 10g you could fake an ORA-error just by adding two lines to the alertlog (first a line with the date and a second line with the actual ORA-error – an ORA-7445 for example) or by using a procedure of the DBMS_SYSTEM package.

For version 10g (and most likely 9i):

exec sys.dbms_system.ksdwrt(2, 'ORA-07445: SIMULATED ERROR, JUST FOR TESTING, IGNORE!!!');

If you would like to add the error to the alertlog and generate an tracefile for it, use:

exec sys.dbms_system.ksdwrt(3, 'ORA-07445: SIMULATED ERROR, JUST FOR TESTING, IGNORE!!!');

For version 11g (and above):

alter session set events '942 incident(SIMULATED_ERROR)';
drop table tablethatdoesnotexist;
alter session set events '942 trace name context off';