Re: What to do if a logon trigger get invalidated ?

Pete Sharman <peter.sharman_at_oracle.com> wrote in message news:<aprrjl02eio_at_drn.newsguy.com>...
> In article <hg32su8adhreu8tq0o8hmmmvct03vorj4l_at_4ax.com>, Hans says...
> >
> >I have experimented a bit with on logon triggers, and it seems such a
> >trigger can create a single point of failure that can bring down the
> >whole database for good.
> >If the trigger becomes invalidated (a table it relies on becomes
> >corrupted for instance), it is not possible for anyone to log on, not
> >even sys as sysdba !
> >In effect this prevents any rescue operations.
> >Or have I overlooked something ?
> >Oracle 9.2 on W2K
> >
> >p.s. Not an acute problem, it was a test database
> >
> >Hans Erik Busk
>
> You should ALWAYS be able to log on as SYSDBA to fix this. Can you post the
> results of trying to connect as SYSDBA with an invalid logon trigger?
>
> HTH. Additions and corrections welcome.
>
> Pete
>
> SELECT standard_disclaimer, witty_remark FROM company_requirements;

Pete is right as I have had this happen. I unset TWO_TASK and logged on to sqlplus using /nolog then connected / as sysdba. I then issued "alter trigger owner.trigger_name disable;" command to allow connections. After disabling it I recompiled the trigger: "alter trigger owner.trigger_name compile;" and followed this with an "alter trigger owner.trigger_name enable;" and everything was fine.

There should be no need to bounce the db as another user suggested.

HTH -- Mark D Powell -- Received on Fri Nov 01 2002 - 08:26:57 CST