Problems logging in to database 2

[jrrob]

Recently installed 10g release 2 and as of the last two days, users are getting the following error messages when trying to connect:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20021: Logon Trigger code = -30511
ORA-06512: at line 21

I have tried looking up these error messages and can find some information on the 604 and the 6512 errors, but the 20021 error seems to be the key and for that I can find no information. Is anyone familiar with this error?

 

[jrrob]

Just an update to this, anyone with the DBA role can connect successfully.

 

[SantaMufasa]

jrrob,

Your instance has one or more LOGON triggers in force that apparently is causing you trouble. To determine the identity of triggers to inspect, connect to Oracle either as a DBA or with privileges to query DBA_* views. Next, run the following two queries and inspect the results:

Query 1 -- Identify LOGON triggers:

select owner, trigger_name,substr(triggering_event,1,10)event
from dba_triggers
where triggering_event like 'LOGON%';

OWNER                          TRIGGER_NAME                   EVENT
------------------------------ ------------------------------ ----------
SYSTEM                         LOGONTRIGGER                   LOGON

Notice in the above results, I have one such trigger on my database.

Query 2 -- Inspect the code in the TRIGGER_BODY of the LOGON trigger(s) that you discovered in Query 1:

set long 50000
select trigger_body from dba_triggers
where owner = '<owner_name>'
  and trigger_name = '<trigger_name from above>';

TRIGGER_BODY
--------------------------------
DECLARE
        ...
BEGIN
        ...
end;

Let us know your findings. We will be glad to help troubleshoot the code, as well.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[jrrob]

Mufasa,
Thanks for your help. Now that I know where its coming from I think I'll make the vendor who says "We haven't made any changes to the database recently" fix the problem that they obviously created, as the owner of the offending trigger and their login id match up beautifully.

In the meantime, since it looks like all it is doing is some audit fuctions, I have disabled it and the users are able to once again log in.

Thanks again for your help

Jim