Bypass the execution of the AFTER LOGON trigger

Problem:

You might have AFTER LOGON trigger for auditing and because of faulty logic (infinite loop) or the space in auditing table is full or some other problem connected to trigger, no users are able to connect to the database including sys users.

Solution:

This solution is applied to version 10.2.0.4 or later [Release 10.2 or later]

1. Kill the database process manually to bring the database down.

2. Modify the init.ora parameter

Set parameter “_system_trig_enabled”=false

3. sqlplus /nolog

conn / as sysdba

startup restrict

drop the offending trigger

shutdown immediate

4. Modify the “_system_trig_enabled” parameter to old value (Default value is true). Remove the parameter if the parameter was absence previously in init.ora file.

5. Startup database normally