Re: No one could logon to productio database for a while

dbaplusplus_at_hotmail.com wrote:
> I am using 9.2.0.5 on HP UNIX. I have an after logon trigger which
> executes quickly - all it does it set some stored outline parameters
> (trigger is provided below).
>
> While this logon trigger is being executed, if someone else tries to
> connect, will Oracle wait for logon trigger to be completed before they
> can connect or will Oracle execute another "instance" of logon
> trigger? There is only one logon trigger in database. For example if I
> put a sleep in logon trigger for 5 minutes, will no one will be able to
> logon during this 5 minutes, I do not think so but want o check.
>
> Today for some reason, no one could logon to my production database
> including sys.
> When sys tried to logon from sqlplus, session was just hung.
>
> We have no of process set to 1200 and there were only 383 oracle shadow
> processes,
> But then I killed a time-consuming oracle shadow process, I see a
> message from this trigger in my alert.log:
>
> ORA-1013: user requested cancellation of operation, *** Error in login
> trigger.
>
> Not sure whether this error message came from this long running killed
> process or some other process which was causing execution of logon
> trigger.
>
> Aftre killng a process, people could connect. I logged on to system. My
> trigger is valid and now everything works. All I did was killed an
> Oracle shadow process which somehow will not let any one logon. This
> sounds like some Oracle quirk/bug. I am not sure whether logon trigger
> is the culprit or something else, but genrally if logon trigger has
> some issue it can hose everyone (but not sys). I have no clue how to
> find root cause f the problem. I have already looked in alert.log and
> trace files
>
> Below is trigger. It is a simple trigger, it calls a function and
> function simply sets use_stored_outlines for some users.
>
>
> CREATE OR REPLACE TRIGGER my_logon AFTER LOGON ON database
> BEGIN
> wmuser.my_outlnfun;
> exception
> when others then
> sys.dbms_system.ksdwrt(2, SUBSTR (SQLERRM, 1, 100) || ' *** Error
> in logon trigger');
> END;
> /
>
> create or replace procedure my_outlnfun is
> begin
>
> if (user = 'JJPROS' or user = 'READGGPROS' or user = 'GGOSPP'') then
> execute immediate 'alter session set
> use_stored_outlines=my_custom';
>
> end if;
> end;

Listener.log shows that people were connecting to database, i.e., connections were being made, they appear to be hung. Received on Fri Sep 22 2006 - 14:11:16 CDT