Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: No one could logon to productio database for a while

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

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 22 Sep 2006 17:24:07 -0700
Message-ID: <1158971045.176517@bubbleator.drizzle.com>


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.

My first instinct would be to believe you have a problem with your trigger. The first problem I see is: 'GGOSPP'' so I am not sure how it could compile successfully.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group
Received on Fri Sep 22 2006 - 19:24:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US