| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: No one could logon to productio database for a while
DA Morgan wrote:
> dbaplusplus_at_hotmail.com wrote:
> > 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.
>
That was a typo when preparing the message for this newsgroup.Trigger clearly was compiled and I can see trigger as valid by looking in status in dba_triggers. There is no prolbem after that process was killed nor before for close to a year when trigger was installed. Nothing was changed in that trigger.If there was problem with compilation, then Oracle gives another message when logging, logon does not get hung it immediately exists with some error.
It is something really unexplainable. How one process can not let anyone logon and after process is killed everyone can logon. Hung means there is some kind of lock everyone is waiting for to be released. Since sys could not login it is some internal disctionary lock and not application lock. Received on Fri Sep 22 2006 - 21:32:31 CDT
 |
 |